星期五, 7月 13, 2018

如何採集(AWR/ ASH) report

1.先得知ADDM snapshot 的時間區間
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot
where
begin_interval_time  between to_timestamp('2018-08-04 12:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2018-08-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
order by begin_interval_time ;

2.
如何使用command line產出AWR報告了
登入sqlplus後執行 @?/rdbms/admin/ashrpt.sql

ref:
https://oraclespin.com/2009/09/27/how-to-generate-awr-report/

3.如何使用command line產出ASH(Active Session History)報告
[oracle@db01 ~]$ cd /home/oracle/
[oracle@db01 ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle
[oracle@db01 ~]$ export ORACLE_SID=orcl1
[oracle@db01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 12 15:52:53 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productio
n
With the Partitioning, Real Application Clusters, Automatic Storage Managem
ent, OLAP,
Data Mining and Real Application Testing options

SQL>
SQL> @?/rdbms/admin/ashrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
xxxxxxxxx ORCL              1 orcl1


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  xxxxxxxxx    2 ORCL       orcl2      db02
                                                

* xxxxxxxxx   1 ORCL       orcl1      db01
                                               


Defaults to current database

Using database id: xxxxxxxxxx

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 1

ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  03-Aug-18 23:59:28   [  12469 mins in the pas
t]
Latest ASH sample available:  12-Aug-18 15:48:15   [      0 mins in the pas
t]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        mples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        mples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time: 07/05/18 14:00:00
Report begin time specified: 07/05/18 14:00:00

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 120 如果要收集連續兩小時區間的information, 這邊就指定為120 min
Report duration specified:   120

Using 05-Aug-18 14:00:00 as report begin time
Using 05-Aug-18 16:00:00 as report end time


Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Explanation:
--   In the 'Activity Over Time' section of the ASH report,
--   the analysis period is divided into smaller slots
--   and top wait events are reported in each of those slots.

-- Default:
--   The analysis period will be automatically split upto 10 slots
--   complying to a minimum slot width of
--     1 minute,  if the source is V$ACTIVE_SESSION_HISTORY or
--     5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.


Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:


Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Explanation:
--   ASH Report can accept "Report Targets",
--   like a particular SQL statement, or a particular SESSION,
--   to generate the report on. If one or more report targets are
--   specified, then the data used to generate the report will only be
--   the ASH samples that pertain to ALL the specified report targets.

-- Default:
--   If none of the report targets are specified,
--   then the target defaults to all activity in the database instance.


Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:


Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:


Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:


Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:


Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:


Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:


Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:


Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0705_1600.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: ashrpt_orcl1_0705_1600.html <--輸入想要產出的檔案名稱

Using the report name ashrpt_orcl1_0705_1600.html

Summary of All User Input
-------------------------
Format         : HTML
DB Id          : xxxxxxxxx 
Inst num       : 1
Begin time     : 05-Aug-18 14:00:00
End time       : 05-Aug-18 16:00:00
Slot width     : Default
Report targets : 0
Report name    : ashrpt_orcl1_0705_1600.html



星期一, 7月 02, 2018

[FWD] Oracle DataGuard Broker re-initiate primary步驟

參考link link

由於Oracle DataGard Broker已經建置, 可在Standby failover開啟之後, 以下列流程re-initiate primary
----------------------------------------------------------------------
DGMGRL> connect sys/*********@STANDBY
DGMGRL> failover to 'standby'

Check the configuration again:
DGMGRL> show configuration
Configuration - broker1
  Protection Mode: MaxPerformance
  Members:
  standby - Primary database
    PRIMARY - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 53 seconds ago)

--Set Old Primary DB to mount status
SQL> startup mount
SQL> select db_unique_name,database_role,flashback_on from v$database;
DB_UNIQUE_NAME DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
PRIMARY PRIMARY          YES
With the database in mount try a reinstate again:

PRIMARY>dgmgrl
DGMGRL> connect sys/*********@STANDBY
DGMGRL> reinstate database 'PRIMARY'

DGMGRL> validate database 'PRIMARY'
  Database Role:     Physical standby database
  Primary Database:  standby
  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups Standby Redo Log Groups Status
    (standby) (PRIMARY)
1 4 3 Insufficient SRLs
  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups Standby Redo Log Groups Status
(PRIMARY) (standby)
1 4 3 Insufficient SRLs

--如果validate database看到 Insufficient SRLs訊息代表沒有建立足夠的standby logfile
alter database add standby logfile thread 1 group 4 size 536870912;
alter database add standby logfile thread 1 group 5 size 536870912;
alter database add standby logfile thread 1 group 6 size 536870912;
alter database add standby logfile thread 1 group 7 size 536870912;
----------------------------------------------------------------------

延伸閱讀
Oracle 11g DG Broker and Observer Health Conditions 設定步驟整理
https://jaychu649.blogspot.com/2013/03/oracle-dg-broker-and-observer.html

Oracle DataGuard停止同步步驟(10g+)
https://jaychu649.blogspot.com/2018/05/oracle-dataguard10g.html

星期五, 6月 29, 2018

Oracle 11g+ RAC CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time.

0.確認CRSS daemon是處於Observer mode or Active mode (如果config ntpd 就會變成observer mode)
[grid@db1 ~]$  crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

[grid@db2 ~]$  crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.


1. db1 crs alert log 有看到以下訊息, 兩台主機的時間應該還是不太一致, 光是有時間差就會造成RAC的單台DB被重啟

2018-06-xx xx:xx:xx.xx [OCTSSD(9974)]CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time. Details in /u01/app/grid/diag/crs/db1/crs/trace/octssd.trc.
2018-06-xx xx:xx:xx.xx [OCTSSD(9974)]CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time. Details in /u01/app/grid/diag/crs/db1/crs/trace/octssd.trc.

2.建議確認DB1, DB2主機上的時間, 確定是否有時間差
#At Both DB Servers
date

3.如有時間差, 先disable chronyd服務, 再ntpd 服務帶起

3.1 Disable chronyd service
#chrony 是 RHEL/CentOS 7 預設的校時 dameon, 如果您的作業系統是RHEL6 請忽略相關步驟
#In order to use ntpd the default user space daemon, chronyd, must be stopped and disabled
systemctl stop chronyd
systemctl disable chronyd
systemctl status chronyd

3.2 NTP Client Configuration
yum install ntp
-------------------------------------------------------------------------
## cat /etc/ntp.conf
restrict default kod nomodify notrap nopeer noquery
restrict -6 default kod nomodify notrap nopeer noquery
restrict 127.0.0.1
restrict -6 ::1
server 0.pool.ntp.org
server 1.pool.ntp.org
server 2.pool.ntp.org
server 3.pool.ntp.org
restrict #ntp_ip_address mask 255.255.255.0 nomodify notrap
driftfile /var/lib/ntp/drift
keys /etc/ntp/keys
-------------------------------------------------------------------------
#systemctl list-unit-files --state=enabled
systemctl enable ntpd.service
systemctl start ntpd.service

3.3 Configure firewall to enable NTP port 123
# firewall-cmd --zone=public --add-port=123/udp --permanent
success

# firewall-cmd --reload
success


不可使用ntpdate 的指令放到cron table去更新時間
#手動更新較快
ntpdate -d xxx.xxx.xxx.xxx #這裡為實際ntp serverip address

3.4 Verify ntpd service
#At Both DB Servers, verify /etc/ntp.conf寫法是否正確
service ntpd status
service ntpd start

#Verify
ntpq -p

3.5 開啟"微調模式"
[root@ ~]# vi /etc/sysconfig/ntpd
#The following item added by Robinson
#Set to 'yes' to sycn hw clock after successful ntpdate
SYNC_HWCLOCK=yes #開啟同步更新硬體時間
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid -g" #加-x 參數,開啟微調模式

4.手動把時間從系統回寫到bios
#update date to bios
hwclock --systohc

5.確認是否關閉numa, transparent_hugepages  (Disable Transparent_Hugepages以避免node eviction問題)
[root@oraracx1 ~]# cat /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never intel_idle.max_cstate=0 processor.max_cstate=0"
GRUB_DISABLE_RECOVERY="true"

Ref:
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)

ref:
http://www.voidcn.com/article/p-rwdsexfj-bcp.html
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/system_administrators_guide/sect-configuring_the_date_and_time-hwclock

FWD: Disable and Remove Unwanted Services on RHEL/CentOS 7 Minimal Installation
https://jaychu649.blogspot.com/2018/05/fwd-disable-and-remove-unwanted.html

星期二, 6月 26, 2018

前一陣子在專案上的心得

前一陣子在專案上的心得(督促自己也督促工程師) 1.專案經驗是別人竊取不走的,心態轉換很重要! (Self-motivation) 2.花一樣的時間在專案上,就要盡全力想辦法把不足的領域學習好。 3.Project collaboration is as important as Project Managent , which is crucial to success. 4.心中無須劃分一道牆,間接告訴大家我什麼不做,我只做什麼(客戶與專案成功至上) 5.自我心理建設與學習態度很重要,相信自己能做到,勇於嘗試永不放棄。 #projectmanagement #lessons_learned

星期四, 6月 07, 2018

MySQL 5.6 install sys schema from Github

繼前一篇
已經把MySQL 5.6安裝完畢了...接著來安裝sys schema...

安裝sys schema主要是為了監控並觀察效能相關的view
5.7以上就有內附sys schema...(還是推薦使用5.7)

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 100   |
| performance_schema_digests_size                        | 10000 |
| performance_schema_events_stages_history_long_size     | 10000 |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |  -> 設定為15000
| performance_schema_events_waits_history_size           | 10    |  -> 設定為20
| performance_schema_hosts_size                          | 100   |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 3504  |
| performance_schema_max_digest_length                   | 1024  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 7693  |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 15906 |
| performance_schema_max_rwlock_classes                  | 40    |
| performance_schema_max_rwlock_instances                | 9102  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 322   |
| performance_schema_max_stage_classes                   | 150   |
| performance_schema_max_statement_classes               | 168   |
| performance_schema_max_table_handles                   | 4000  |
| performance_schema_max_table_instances                 | 12500 |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 402   |
| performance_schema_session_connect_attrs_size          | 512   |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
| performance_schema_users_size                          | 100   |
+--------------------------------------------------------+-------+
32 rows in set (0.00 sec)

#ref: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-startup-configuration.html
#Initial Setup for my.cnf
performance_schema=ON
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000
max_connections
open_files_limit
table_definition_cache
table_open_cache

#MySQL 5.7 default parameter check
mysql -u root -p <<MYEOF
show variables like 'max_connections';
show variables like 'open_files_limit';
show variables like 'table_definition_cache';
show variables like 'table_open_cache';
MYEOF
Variable_name   Value
max_connections 151
Variable_name   Value
open_files_limit        5000
Variable_name   Value
table_definition_cache  1400
Variable_name   Value
table_open_cache        2000


#ref: https://github.com/mysql/mysql-sys
#ref: https://blog.csdn.net/leonpenn/article/details/78223549

安裝MySQLsys database from github

[root@centos7 tmp]# yum -y install git

[root@centos7 tmp]# git clone https://github.com/mysql/mysql-sys.git
Cloning into 'mysql-sys'...
remote: Counting objects: 3009, done.
remote: Total 3009 (delta 0), reused 0 (delta 0), pack-reused 3009
Receiving objects: 100% (3009/3009), 1.17 MiB | 611.00 KiB/s, done.
Resolving deltas: 100% (1770/1770), done.


[root@centos7 mysql-sys]# mysql -u root -p < ./sys_56.sql
Enter password:
[root@centos7 mysql-sys]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.6.40-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| metrics                                       |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_auto_increment_columns                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_redundant_indexes                      |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| session                                       |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_flattened_keys                       |
| x$schema_index_statistics                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$session                                     |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
88 rows in set (0.00 sec)

SELECT file, count_write cw,
total_written tw
FROM sys.io_global_by_file_by_bytes
WHERE count_write>0 ORDER BY count_write DESC;

mysql> SELECT file, count_write cw,
    ->  total_written tw
    ->  FROM sys.io_global_by_file_by_bytes
    ->  WHERE count_write>0 ORDER BY count_write DESC;
+-------------------------------------------------------------------------------------+------+------------+
| file                                                                                | cw   | tw         |
+-------------------------------------------------------------------------------------+------+------------+
| @@datadir/mysql/#sql-4e4e_14.MYI                                                    | 1352 | 28.01 KiB  |
| @@datadir/mysql/proc.MYD                                                            |  508 | 885.72 KiB |
| @@datadir/mysql/#sql-4e4e_14.frm                                                    |  269 | 45.09 KiB  |
| @@datadir/mysql/proc.MYI                                                            |  173 | 24.58 KiB  |
| @@datadir/performance_schema/table_lock_waits_summary_by_table.frm                  |  161 | 10.50 KiB  |
| @@datadir/ibdata1                                                                   |  101 | 3.31 MiB   |
| @@datadir/performance_schema/events_statements_current.frm                          |   96 | 8.48 KiB   |
| @@datadir/performance_schema/events_statements_history.frm                          |   96 | 8.48 KiB   |
| @@datadir/performance_schema/events_statements_history_long.frm                     |   96 | 8.48 KiB   |
| @@datadir/performance_schema/table_io_waits_summary_by_index_usage.frm              |   93 | 7.51 KiB   |
| @@datadir/performance_schema/table_io_waits_summary_by_table.frm                    |   91 | 7.23 KiB   |
| @@datadir/performance_schema/host_cache.frm                                         |   74 | 7.51 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_digest.frm                |   73 | 6.50 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_account_by_event_name.frm |   69 | 6.69 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_host_by_event_name.frm    |   67 | 6.62 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_user_by_event_name.frm    |   67 | 6.49 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_thread_by_event_name.frm  |   67 | 6.46 KiB   |
| @@datadir/performance_schema/file_summary_by_instance.frm                           |   65 | 7.81 KiB   |
| @@datadir/performance_schema/events_statements_summary_global_by_event_name.frm     |   65 | 6.41 KiB   |
| @@datadir/performance_schema/socket_summary_by_instance.frm                         |   63 | 6.27 KiB   |
| @@datadir/mysql/#sql-4e4e_14.MYD                                                    |   61 | 392.29 KiB |
| @@datadir/performance_schema/file_summary_by_event_name.frm                         |   61 | 6.20 KiB   |
| @@datadir/performance_schema/socket_summary_by_event_name.frm                       |   61 | 6.20 KiB   |
| @@datadir/performance_schema/events_waits_current.frm                               |   54 | 8.11 KiB   |
| @@datadir/performance_schema/events_waits_history_long.frm                          |   54 | 8.11 KiB   |
| @@datadir/performance_schema/events_waits_history.frm                               |   54 | 8.11 KiB   |
| @@datadir/performance_schema/threads.frm                                            |   44 | 6.40 KiB   |
| @@datadir/performance_schema/events_stages_current.frm                              |   36 | 5.53 KiB   |
| @@datadir/performance_schema/events_stages_history.frm                              |   36 | 5.53 KiB   |
| @@datadir/performance_schema/events_stages_history_long.frm                         |   36 | 5.53 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_account_by_event_name.frm      |   31 | 5.43 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_account_by_event_name.frm     |   31 | 5.43 KiB   |
| @@datadir/performance_schema/objects_summary_global_by_type.frm                     |   31 | 5.43 KiB   |
| @@datadir/performance_schema/socket_instances.frm                                   |   30 | 5.32 KiB   |
| @@datadir/ib_logfile0                                                               |   29 | 43.50 KiB  |
| @@datadir/performance_schema/events_waits_summary_by_host_by_event_name.frm         |   29 | 5.35 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_host_by_event_name.frm        |   29 | 5.35 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_user_by_event_name.frm         |   29 | 5.22 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_user_by_event_name.frm        |   29 | 5.22 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_thread_by_event_name.frm       |   29 | 5.19 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_instance.frm                   |   29 | 5.21 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_thread_by_event_name.frm      |   29 | 5.19 KiB   |
| @@datadir/performance_schema/events_waits_summary_global_by_event_name.frm          |   27 | 5.14 KiB   |
| @@datadir/performance_schema/events_stages_summary_global_by_event_name.frm         |   27 | 5.14 KiB   |
| @@datadir/performance_schema/setup_objects.frm                                      |   26 | 5.04 KiB   |
| @@datadir/performance_schema/performance_timers.frm                                 |   24 | 4.71 KiB   |
| @@datadir/performance_schema/session_connect_attrs.frm                              |   23 | 7.73 KiB   |
| @@datadir/sys/sys_config.frm                                                        |   23 | 5.73 KiB   |
| @@datadir/performance_schema/session_account_connect_attrs.frm                      |   23 | 7.73 KiB   |
| @@datadir/performance_schema/accounts.frm                                           |   23 | 4.86 KiB   |
| @@datadir/performance_schema/rwlock_instances.frm                                   |   23 | 5.07 KiB   |
| @@datadir/performance_schema/setup_instruments.frm                                  |   22 | 4.93 KiB   |
| @@datadir/performance_schema/file_instances.frm                                     |   21 | 6.45 KiB   |
| @@datadir/performance_schema/mutex_instances.frm                                    |   21 | 4.99 KiB   |
| @@datadir/performance_schema/setup_actors.frm                                       |   21 | 4.80 KiB   |
| @@datadir/performance_schema/hosts.frm                                              |   21 | 4.78 KiB   |
| @@datadir/performance_schema/users.frm                                              |   21 | 4.65 KiB   |
| @@datadir/performance_schema/setup_timers.frm                                       |   20 | 4.75 KiB   |
| @@datadir/performance_schema/setup_consumers.frm                                    |   20 | 4.71 KiB   |
| @@datadir/performance_schema/cond_instances.frm                                     |   19 | 4.92 KiB   |
| @@datadir/sys/sys_config.TRG~                                                       |   10 | 5.61 KiB   |
| @@datadir/sys/sys_config.ibd                                                        |    7 | 176.00 KiB |
| @@datadir/mysql/innodb_index_stats.ibd                                              |    5 | 80.00 KiB  |
| @@datadir/mysql/innodb_table_stats.ibd                                              |    5 | 80.00 KiB  |
| @@datadir/sys/schema_auto_increment_columns.frm~                                    |    4 | 25.12 KiB  |
| @@datadir/sys/innodb_lock_waits.frm~                                                |    4 | 21.47 KiB  |
| @@datadir/sys/schema_redundant_indexes.frm~                                         |    4 | 21.21 KiB  |
| @@datadir/sys/x@0024innodb_lock_waits.frm~                                          |    4 | 20.89 KiB  |
| @@datadir/sys/schema_object_overview.frm~                                           |    4 | 14.58 KiB  |
| @@datadir/sys/innodb_buffer_stats_by_table.frm~                                     |    4 | 11.10 KiB  |
| @@datadir/sys/x@0024innodb_buffer_stats_by_table.frm~                               |    4 | 10.86 KiB  |
| @@datadir/sys/innodb_buffer_stats_by_schema.frm~                                    |    4 | 9.94 KiB   |
| @@datadir/sys/x@0024schema_flattened_keys.frm~                                      |    4 | 9.84 KiB   |
| @@datadir/sys/x@0024innodb_buffer_stats_by_schema.frm~                              |    4 | 9.70 KiB   |
| @@datadir/sys/version.frm~                                                          |    4 | 1.82 KiB   |
| @@datadir/mysql/#sql-4e4e_14.CSM                                                    |    4 | 140 bytes  |
| @@datadir/sys/sys_config_insert_set_user.TRN~                                       |    4 | 168 bytes  |
| @@datadir/sys/sys_config_update_set_user.TRN~                                       |    4 | 168 bytes  |
| @@datadir/sys/db.opt                                                                |    3 | 183 bytes  |
| @@datadir/sys/statement_analysis.frm~                                               |    2 | 14.11 KiB  |
| @@datadir/sys/x@0024statement_analysis.frm~                                         |    2 | 13.50 KiB  |
| @@datadir/sys/statements_with_full_table_scans.frm~                                 |    2 | 11.27 KiB  |
| @@datadir/sys/schema_table_statistics_with_buffer.frm~                              |    2 | 10.97 KiB  |
| @@datadir/sys/x@0024statements_with_full_table_scans.frm~                           |    2 | 10.93 KiB  |
| @@datadir/sys/metrics.frm~                                                          |    2 | 10.91 KiB  |
| @@datadir/sys/processlist.frm~                                                      |    2 | 10.43 KiB  |
| @@datadir/sys/io_global_by_wait_by_bytes.frm~                                       |    2 | 10.29 KiB  |
| @@datadir/sys/io_global_by_wait_by_latency.frm~                                     |    2 | 9.95 KiB   |
| @@datadir/sys/x@0024processlist.frm~                                                |    2 | 9.68 KiB   |
| @@datadir/sys/x@0024schema_table_statistics_with_buffer.frm~                        |    2 | 9.56 KiB   |
| @@datadir/sys/x@0024io_global_by_wait_by_bytes.frm~                                 |    2 | 9.23 KiB   |
| @@datadir/sys/host_summary_by_statement_type.frm~                                   |    2 | 8.85 KiB   |
| @@datadir/sys/user_summary_by_statement_type.frm~                                   |    2 | 8.79 KiB   |
| @@datadir/sys/io_by_thread_by_latency.frm~                                          |    2 | 8.78 KiB   |
| @@datadir/sys/x@0024io_global_by_wait_by_latency.frm~                               |    2 | 8.77 KiB   |
| @@datadir/sys/statements_with_temp_tables.frm~                                      |    2 | 8.61 KiB   |
| @@datadir/sys/statements_with_sorting.frm~                                          |    2 | 8.60 KiB   |
| @@datadir/sys/x@0024host_summary_by_statement_type.frm~                             |    2 | 8.51 KiB   |
| @@datadir/sys/x@0024user_summary_by_statement_type.frm~                             |    2 | 8.44 KiB   |
| @@datadir/sys/x@0024statements_with_temp_tables.frm~                                |    2 | 8.35 KiB   |
| @@datadir/sys/x@0024statements_with_sorting.frm~                                    |    2 | 8.34 KiB   |
| @@datadir/sys/x@0024io_by_thread_by_latency.frm~                                    |    2 | 8.32 KiB   |
| @@datadir/sys/io_global_by_file_by_bytes.frm~                                       |    2 | 8.27 KiB   |
| @@datadir/sys/host_summary_by_statement_latency.frm~                                |    2 | 8.14 KiB   |
| @@datadir/sys/user_summary_by_statement_latency.frm~                                |    2 | 8.14 KiB   |
| @@datadir/sys/schema_table_statistics.frm~                                          |    2 | 8.09 KiB   |
| @@datadir/sys/x@0024host_summary_by_statement_latency.frm~                          |    2 | 7.79 KiB   |
| @@datadir/sys/x@0024user_summary_by_statement_latency.frm~                          |    2 | 7.79 KiB   |
| @@datadir/sys/x@0024io_global_by_file_by_bytes.frm~                                 |    2 | 7.55 KiB   |
| @@datadir/sys/statements_with_errors_or_warnings.frm~                               |    2 | 7.49 KiB   |
| @@datadir/sys/x@0024statements_with_errors_or_warnings.frm~                         |    2 | 7.35 KiB   |
| @@datadir/sys/user_summary.frm~                                                     |    2 | 7.27 KiB   |
| @@datadir/sys/schema_index_statistics.frm~                                          |    2 | 7.20 KiB   |
| @@datadir/sys/wait_classes_global_by_avg_latency.frm~                               |    2 | 7.09 KiB   |
| @@datadir/sys/statements_with_runtimes_in_95th_percentile.frm~                      |    2 | 7.04 KiB   |
| @@datadir/sys/x@0024schema_table_statistics.frm~                                    |    2 | 6.93 KiB   |
| @@datadir/sys/x@0024user_summary.frm~                                               |    2 | 6.92 KiB   |
| @@datadir/sys/latest_file_io.frm~                                                   |    2 | 6.88 KiB   |
| @@datadir/sys/wait_classes_global_by_latency.frm~                                   |    2 | 6.77 KiB   |
| @@datadir/sys/waits_by_user_by_latency.frm~                                         |    2 | 6.76 KiB   |
| @@datadir/sys/x@0024schema_index_statistics.frm~                                    |    2 | 6.74 KiB   |
| @@datadir/sys/host_summary.frm~                                                     |    2 | 6.60 KiB   |
| @@datadir/sys/x@0024statements_with_runtimes_in_95th_percentile.frm~                |    2 | 6.55 KiB   |
| @@datadir/sys/x@0024latest_file_io.frm~                                             |    2 | 6.53 KiB   |
| @@datadir/sys/x@0024waits_by_user_by_latency.frm~                                   |    2 | 6.42 KiB   |
| @@datadir/sys/x@0024wait_classes_global_by_avg_latency.frm~                         |    2 | 6.41 KiB   |
| @@datadir/sys/waits_by_host_by_latency.frm~                                         |    2 | 6.36 KiB   |
| @@datadir/sys/x@0024wait_classes_global_by_latency.frm~                             |    2 | 6.32 KiB   |
| @@datadir/sys/x@0024host_summary.frm~                                               |    2 | 6.13 KiB   |
| @@datadir/sys/x@0024waits_by_host_by_latency.frm~                                   |    2 | 6.01 KiB   |
| @@datadir/sys/host_summary_by_file_io_type.frm~                                     |    2 | 5.89 KiB   |
| @@datadir/sys/x@0024ps_schema_table_statistics_io.frm~                              |    2 | 5.86 KiB   |
| @@datadir/sys/user_summary_by_file_io_type.frm~                                     |    2 | 5.79 KiB   |
| @@datadir/sys/x@0024host_summary_by_file_io_type.frm~                               |    2 | 5.66 KiB   |
| @@datadir/sys/x@0024user_summary_by_file_io_type.frm~                               |    2 | 5.55 KiB   |
| @@datadir/sys/host_summary_by_stages.frm~                                           |    2 | 5.45 KiB   |
| @@datadir/sys/user_summary_by_stages.frm~                                           |    2 | 5.39 KiB   |
| @@datadir/sys/x@0024host_summary_by_stages.frm~                                     |    2 | 5.22 KiB   |
| @@datadir/sys/x@0024user_summary_by_stages.frm~                                     |    2 | 5.16 KiB   |
| @@datadir/sys/io_global_by_file_by_latency.frm~                                     |    2 | 5.13 KiB   |
| @@datadir/sys/x@0024session.frm~                                                    |    2 | 4.95 KiB   |
| @@datadir/sys/waits_global_by_latency.frm~                                          |    2 | 4.81 KiB   |
| @@datadir/sys/session.frm~                                                          |    2 | 4.75 KiB   |
| @@datadir/sys/host_summary_by_file_io.frm~                                          |    2 | 4.62 KiB   |
| @@datadir/sys/user_summary_by_file_io.frm~                                          |    2 | 4.62 KiB   |
| @@datadir/sys/x@0024io_global_by_file_by_latency.frm~                               |    2 | 4.55 KiB   |
| @@datadir/sys/schema_unused_indexes.frm~                                            |    2 | 4.54 KiB   |
| @@datadir/sys/x@0024host_summary_by_file_io.frm~                                    |    2 | 4.50 KiB   |
| @@datadir/sys/x@0024user_summary_by_file_io.frm~                                    |    2 | 4.50 KiB   |
| @@datadir/sys/x@0024waits_global_by_latency.frm~                                    |    2 | 4.47 KiB   |
| @@datadir/sys/schema_tables_with_full_table_scans.frm~                              |    2 | 3.93 KiB   |
| @@datadir/sys/x@0024schema_tables_with_full_table_scans.frm~                        |    2 | 3.81 KiB   |
| @@datadir/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm~                        |    2 | 3.45 KiB   |
| @@datadir/sys/ps_check_lost_instrumentation.frm~                                    |    2 | 2.14 KiB   |
| @@datadir/sys/x@0024ps_digest_avg_latency_distribution.frm~                         |    2 | 1.66 KiB   |
| @@datadir/mysql/slow_log.CSM                                                        |    2 | 70 bytes   |
| @@datadir/mysql/general_log.CSM                                                     |    2 | 70 bytes   |
| @@datadir/performance_schema/db.opt                                                 |    1 | 61 bytes   |
| @@datadir/centos7.pid                                                               |    1 | 6 bytes    |
+-------------------------------------------------------------------------------------+------+------------+
159 rows in set (0.06 sec)

--5.7 才查的到
mysql> SELECT * FROM sys.memory_global_total;
ERROR 1146 (42S02): Table 'sys.memory_global_total' doesn't exist
mysql>


底下放一些例子
更多實際案例直接參考MySQL sys schema github上面的example









































查詢沒有使用到索引或者索引效率低下的語句:
SELECT OBJECT_SCHEMA,  THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS, ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED FROM performance_schema.events_statements_history WHERE (NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1) and sql_text  NOT LIKE '%performance_schema%'\G

查看哪些索引沒有被使用過

SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;


查看SQL語句在哪個階段消耗最大

SELECT eshl.event_name, sql_text, eshl.timer_wait/1000000000000 w_s
FROM performance_schema.events_stages_history_long eshl
JOIN performance_schema.events_statements_history_long esthl
ON (eshl.nesting_event_id = esthl.event_id)
WHERE eshl.timer_wait > 1*10000000000\G

ref:
Sys Schema for MySQL 5.6 and MySQL 5.7
  https://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/

Performance Schema簡介
  http://m.blog.itpub.net/28218939/viewspace-2141605/


LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...