MySQL5.6 PERFORMANCE_SCHEMA 說明

https://blog.csdn.net/isoleo/article/details/51180593

轉載

背景：

        5.5開始新增一個資料庫：PERFORMANCE_SCHEMA，主要用於收集資料庫伺服器效能引數。並且庫裡表的儲存引擎均為PERFORMANCE_SCHEMA，而使用者是不能建立儲存引擎為PERFORMANCE_SCHEMA的表。 5.5預設是關閉的，需要手動開啟，在配置檔案裡新增：

1. [mysqld]

2. performance_schema=ON

檢視是否開啟：

1. mysql>show variables like  'performance_schema' ;

2. +--------------------+-------+

3. | Variable_name      | Value |

4. +--------------------+-------+

5. | performance_schema | <strong>ON</strong>    |

6. +--------------------+-------+

從MySQL5.6開始，預設開啟，本文就從MySQL5.6來說明，在資料庫使用當中PERFORMANCE_SCHEMA的一些比較常用的功能。具體的資訊可以檢視官方文件。

相關表資訊：

一：配置（setup）表：

01. zjy @performance_schema   10 : 16 : 56 >show tables like  '%setup%' ;

02. +----------------------------------------+

03. | Tables_in_performance_schema (%setup%) |

04. +----------------------------------------+

05. | setup_actors                           |

06. | setup_consumers                        |

07. | setup_instruments                      |

08. | setup_objects                          |

09. | setup_timers                           |

10. +----------------------------------------+

1，setup_actors ：配置使用者緯度的監控，預設監控所有使用者。

1. zjy @performance_schema   10 : 19 : 11 >select * from setup_actors;

2. +------+------+------+

3. | HOST | USER | ROLE |

4. +------+------+------+

5. | %    | %    | %    |

6. +------+------+------+

2，setup_consumers ：配置events的消費者型別，即收集的events寫入到哪些統計表中。

01. zjy@: performance_schema  10 : 23 : 35 >select * from setup_consumers;

02. +--------------------------------+---------+

03. | NAME                           | ENABLED |

04. +--------------------------------+---------+

05. | events_stages_current          | NO      |

06. | events_stages_history          | NO      |

07. | events_stages_history_long     | NO      |

08. | events_statements_current      | YES     |

09. | events_statements_history      | NO      |

10. | events_statements_history_long | NO      |

11. | events_waits_current           | NO      |

12. | events_waits_history           | NO      |

13. | events_waits_history_long      | NO      |

14. | global_instrumentation         | YES     |

15. | thread_instrumentation         | YES     |

16. | statements_digest              | YES     |

17. +--------------------------------+---------+

這裡需要說明的是需要檢視哪個就更新其ENABLED列為YES。如：

1. zjy @performance_schema   10 : 25 : 02 >update setup_consumers set ENABLED= 'YES'   where NAME in ( 'events_stages_current' , 'events_waits_current' );

2. Query OK,  2   rows affected ( 0.00   sec)

更新完後立即生效，但是伺服器重啟之後又會變回預設值，要永久生效需要在配置檔案裡新增：

1. [mysqld]

2. #performance_schema

3. performance_schema_consumer_events_waits_current=on

4. performance_schema_consumer_events_stages_current=on

5. performance_schema_consumer_events_statements_current=on

6. performance_schema_consumer_events_waits_history=on

7. performance_schema_consumer_events_stages_history=on

8. performance_schema_consumer_events_statements_history=on

即在這些表的前面加上：performance_schema_consumer_xxx。表setup_consumers裡面的值有個層級關係：

1. <strong>global_instrumentation</strong> > <strong>thread_instrumentation</strong> = <strong>statements_digest</strong> > events_stages_<strong>current</strong> = events_statements_current = events_waits_current > events_stages_<strong>history</strong> = events_statements_history = events_waits_history > events_stages_<strong>history_long</strong> = events_statements_history_long = events_waits_history_long

只有上一層次的為YES，才會繼續檢查該本層為YES or NO。global_instrumentation是最高階別consumer，如果它設定為NO，則所有的consumer都會忽略。其中history和history_long存的是current錶的歷史記錄條數，history表記錄了每個執行緒最近等待的10個事件，而history_long表則記錄了最近所有執行緒產生的10000個事件，這裡的10和10000都是可以配置的。這三個表表結構相同，history和history_long表資料都來源於current表。長度透過控制引數：

01. zjy @performance_schema   11 : 10 : 03 >show variables like  'performance_schema%history%size' ;

02. +--------------------------------------------------------+-------+

03. | Variable_name                                          | Value |

04. +--------------------------------------------------------+-------+

05. | performance_schema_events_stages_history_long_size     |  10000   |

06. | performance_schema_events_stages_history_size          |  10      |

07. | performance_schema_events_statements_history_long_size |  10000   |

08. | performance_schema_events_statements_history_size      |  10      |

09. | performance_schema_events_waits_history_long_size      |  10000   |

10. | performance_schema_events_waits_history_size           |  10      |

11. +--------------------------------------------------------+-------+

3， setup_instruments ：配置具體的instrument，主要包含4大類：idle、stage/xxx、statement/xxx、wait/xxx：

01. zjy @performance_schema   10 : 56 : 35 >select name,count(*) from setup_instruments group by LEFT(name, 5 );

02. +---------------------------------+----------+

03. | name                            | count(*) |

04. +---------------------------------+----------+

05. | idle                            |         1   |

06. | stage/sql/After create          |       111   |

07. | statement/sql/select            |       179   |

08. | wait/synch/mutex/sql/PAGE::lock |       296   |

09. +---------------------------------+----------+

idle表示socket空閒的時間，stage類表示語句的每個執行階段的統計，statement類統計語句維度的資訊，wait類統計各種等待事件，比如IO，mutux，spin_lock,condition等。

4，setup_objects ：配置監控物件，預設對mysql，performance_schema和information_schema中的表都不監控，而其它DB的所有表都監控。

01. zjy @performance_schema   11 : 00 : 18 >select * from setup_objects;

02. +-------------+--------------------+-------------+---------+-------+

03. | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |

04. +-------------+--------------------+-------------+---------+-------+

05. | TABLE       | mysql              | %           | NO      | NO    |

06. | TABLE       | performance_schema | %           | NO      | NO    |

07. | TABLE       | information_schema | %           | NO      | NO    |

08. | TABLE       | %                  | %           | <strong>YES</strong>     | <strong>YES</strong>   |

09. +-------------+--------------------+-------------+---------+-------+

5，setup_timers ：配置每種型別指令的統計時間單位。MICROSECOND表示統計單位是微妙，CYCLE表示統計單位是時鐘週期，時間度量與CPU的主頻有關，NANOSECOND表示統計單位是納秒。但無論採用哪種度量單位，最終統計表中統計的時間都會裝換到皮秒。（1秒＝1000000000000皮秒）

01. zjy @performance_schema   11 : 05 : 12 >select * from setup_timers;

02. +-----------+-------------+

03. | NAME      | TIMER_NAME  |

04. +-----------+-------------+

05. | idle      | MICROSECOND |

06. | wait      | CYCLE       |

07. | stage     | NANOSECOND  |

08. | statement | NANOSECOND  |

09. +-----------+-------------+

二：instance表

1， cond_instances ：條件等待物件例項

表中記錄了系統中使用的條件變數的物件， OBJECT_INSTANCE_BEGIN 為物件的記憶體地址。

2， file_instances ：檔案例項

表中記錄了系統中開啟了檔案的物件，包括ibdata檔案，redo檔案，binlog檔案，使用者的表檔案等， open_count 顯示當前檔案開啟的數目，如果重來沒有開啟過，不會出現在表中。

01. zjy @performance_schema   11 : 20 : 04 >select * from file_instances limit  2 , 5 ;

02. +---------------------------------+--------------------------------------+------------+

03. | FILE_NAME                       | EVENT_NAME                           | <strong>OPEN_COUNT</strong> |

04. +---------------------------------+--------------------------------------+------------+

05. | /var/lib/mysql/mysql/plugin.frm | wait/io/file/sql/FRM                 |             |

06. | /var/lib/mysql/mysql/plugin.MYI | wait/io/file/myisam/kfile            |           1   |

07. | /var/lib/mysql/mysql/plugin.MYD | wait/io/file/myisam/dfile            |           1   |

08. | /var/lib/mysql/ibdata1          | wait/io/file/innodb/innodb_data_file |           2   |

09. | /var/lib/mysql/ib_logfile0      | wait/io/file/innodb/innodb_log_file  |           2   |

10. +---------------------------------+--------------------------------------+------------+

3，mutex_instances： 互斥同步物件例項

表中記錄了系統中使用互斥量物件的所有記錄，其中name為：wait/synch/mutex/*。 LOCKED_BY_THREAD_ID 顯示哪個執行緒正持有mutex，若沒有執行緒持有，則為NULL。

4， rwlock_instances：  讀寫鎖同步物件例項

表中記錄了系統中使用讀寫鎖物件的所有記錄，其中name為 wait/synch/rwlock/*。 WRITE_LOCKED_BY_THREAD_ID 為正在持有該物件的thread_id，若沒有執行緒持有，則為NULL。 READ_LOCKED_BY_COUNT 為記錄了同時有多少個讀者持有讀鎖。（透過 events_waits_current 表可以知道，哪個執行緒在等待鎖；透過rwlock_instances知道哪個執行緒持有鎖。rwlock_instances的缺陷是，只能記錄持有寫鎖的執行緒，對於讀鎖則無能為力）。

5，socket_instances： 活躍會話物件例項
表中記錄了thread_id,socket_id,ip和port，其它表可以透過thread_id與socket_instance進行關聯，獲取IP-PORT資訊，能夠與應用對接起來。
event_name主要包含3類：
wait/io/socket/sql/server_unix_socket，服務端unix監聽socket
wait/io/socket/sql/server_tcpip_socket，服務端tcp監聽socket
wait/io/socket/sql/client_connection，客戶端socket

三：Wait表

1， events_waits_current ：記錄了當前執行緒等待的事件

2， events_waits_history ：記錄了每個執行緒最近等待的10個事件

3， events_waits_history_long ：記錄了最近所有執行緒產生的10000個事件

表結構定義如下：

01. CREATE TABLE `events_waits_current` (

02. `THREAD_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '執行緒ID' ,

03. `EVENT_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '當前執行緒的事件ID，和THREAD_ID確定唯一' ,

04. `END_EVENT_ID` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '當事件開始時，這一列被設定為NULL。當事件結束時，再更新為當前的事件ID' ,

05. `EVENT_NAME` varchar( 128 ) NOT NULL COMMENT  '事件名稱' ,

06. `SOURCE` varchar( 64 ) DEFAULT NULL COMMENT  '該事件產生時的原始碼檔案' ,

07. `TIMER_START` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件開始時間（皮秒）' ,

08. `TIMER_END` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件結束結束時間（皮秒）' ,

09. `TIMER_WAIT` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件等待時間（皮秒）' ,

10. `SPINS`  int ( 10 ) unsigned DEFAULT NULL COMMENT  '' ,

11. `OBJECT_SCHEMA` varchar( 64 ) DEFAULT NULL COMMENT  '庫名' ,

12. `OBJECT_NAME` varchar( 512 ) DEFAULT NULL COMMENT  '檔名、表名、IP:SOCK值' ,

13. `OBJECT_TYPE` varchar( 64 ) DEFAULT NULL COMMENT  'FILE、TABLE、TEMPORARY TABLE' ,

14. `INDEX_NAME` varchar( 64 ) DEFAULT NULL COMMENT  '索引名' ,

15. `OBJECT_INSTANCE_BEGIN` bigint( 20 ) unsigned NOT NULL COMMENT  '記憶體地址' ,

16. `NESTING_EVENT_ID` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '該事件對應的父事件ID' ,

17. `NESTING_EVENT_TYPE`  enum ( 'STATEMENT' , 'STAGE' , 'WAIT' ) DEFAULT NULL COMMENT  '父事件型別(STATEMENT, STAGE, WAIT)' ,

18. `OPERATION` varchar( 32 ) NOT NULL COMMENT  '操作型別（lock, read, write）' ,

19. `NUMBER_OF_BYTES` bigint( 20 ) DEFAULT NULL COMMENT  '' ,

20. `FLAGS`  int ( 10 ) unsigned DEFAULT NULL COMMENT  '標記'

21. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

四：Stage 表 

1，events_stages_current ：記錄了當前執行緒所處的執行階段

2，events_stages_history ：記錄了當前執行緒所處的執行階段10條歷史記錄

3，events_stages_history_long ：記錄了當前執行緒所處的執行階段10000條歷史記錄

表結構定義如下：

01. CREATE TABLE `events_stages_current` (

02. `THREAD_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '執行緒ID' ,

03. `EVENT_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '事件ID' ,

04. `END_EVENT_ID` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '結束事件ID' ,

05. `EVENT_NAME` varchar( 128 ) NOT NULL COMMENT  '事件名稱' ,

06. `SOURCE` varchar( 64 ) DEFAULT NULL COMMENT  '原始碼位置' ,

07. `TIMER_START` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件開始時間（皮秒）' ,

08. `TIMER_END` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件結束結束時間（皮秒）' ,

09. `TIMER_WAIT` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件等待時間（皮秒）' ,

10. `NESTING_EVENT_ID` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '該事件對應的父事件ID' ,

11. `NESTING_EVENT_TYPE`  enum ( 'STATEMENT' , 'STAGE' , 'WAIT' ) DEFAULT NULL COMMENT  '父事件型別(STATEMENT, STAGE, WAIT)'

12. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

五：Statement 表

1，events_statements_current ：透過 thread_id+event_id可以唯一確定一條記錄。Statments表只記錄最頂層的請求，SQL語句或是COMMAND，每條語句一行。event_name形式為statement/sql/*，或statement/com/*

2，events_statements_history

3，events_statements_history_long

表結構定義如下：

01. CREATE TABLE `events_statements_current` (

02. `THREAD_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '執行緒ID' ,

03. `EVENT_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '事件ID' ,

04. `END_EVENT_ID` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '結束事件ID' ,

05. `EVENT_NAME` varchar( 128 ) NOT NULL COMMENT  '事件名稱' ,

06. `SOURCE` varchar( 64 ) DEFAULT NULL COMMENT  '原始碼位置' ,

07. `TIMER_START` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件開始時間（皮秒）' ,

08. `TIMER_END` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件結束結束時間（皮秒）' ,

09. `TIMER_WAIT` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '事件等待時間（皮秒）' ,

10. `LOCK_TIME` bigint( 20 ) unsigned NOT NULL COMMENT  '鎖時間' ,

11. `SQL_TEXT` longtext COMMENT  '記錄SQL語句' ,

12. `DIGEST` varchar( 32 ) DEFAULT NULL COMMENT  '對SQL_TEXT做MD5產生的32位字串' ,

13. `DIGEST_TEXT` longtext COMMENT  '將語句中值部分用問號代替，用於SQL語句歸類' ,

14. `CURRENT_SCHEMA` varchar( 64 ) DEFAULT NULL COMMENT  '預設的資料庫名' ,

15. `OBJECT_TYPE` varchar( 64 ) DEFAULT NULL COMMENT  '保留欄位' ,

16. `OBJECT_SCHEMA` varchar( 64 ) DEFAULT NULL COMMENT  '保留欄位' ,

17. `OBJECT_NAME` varchar( 64 ) DEFAULT NULL COMMENT  '保留欄位' ,

18. `OBJECT_INSTANCE_BEGIN` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '記憶體地址' ,

19. `MYSQL_ERRNO`  int ( 11 ) DEFAULT NULL COMMENT  '' ,

20. `RETURNED_SQLSTATE` varchar( 5 ) DEFAULT NULL COMMENT  '' ,

21. `MESSAGE_TEXT` varchar( 128 ) DEFAULT NULL COMMENT  '資訊' ,

22. `ERRORS` bigint( 20 ) unsigned NOT NULL COMMENT  '錯誤數目' ,

23. `WARNINGS` bigint( 20 ) unsigned NOT NULL COMMENT  '警告數目' ,

24. `ROWS_AFFECTED` bigint( 20 ) unsigned NOT NULL COMMENT  '影響的數目' ,

25. `ROWS_SENT` bigint( 20 ) unsigned NOT NULL COMMENT  '返回的記錄數' ,

26. `ROWS_EXAMINED` bigint( 20 ) unsigned NOT NULL COMMENT  '讀取掃描的記錄數目' ,

27. `CREATED_TMP_DISK_TABLES` bigint( 20 ) unsigned NOT NULL COMMENT  '建立磁碟臨時表數目' ,

28. `CREATED_TMP_TABLES` bigint( 20 ) unsigned NOT NULL COMMENT  '建立臨時表數目' ,

29. `SELECT_FULL_JOIN` bigint( 20 ) unsigned NOT NULL COMMENT  'join時，第一個表為全表掃描的數目' ,

30. `SELECT_FULL_RANGE_JOIN` bigint( 20 ) unsigned NOT NULL COMMENT  '引用表採用range方式掃描的數目' ,

31. `SELECT_RANGE` bigint( 20 ) unsigned NOT NULL COMMENT  'join時，第一個表採用range方式掃描的數目' ,

32. `SELECT_RANGE_CHECK` bigint( 20 ) unsigned NOT NULL COMMENT  '' ,

33. `SELECT_SCAN` bigint( 20 ) unsigned NOT NULL COMMENT  'join時，第一個表位全表掃描的數目' ,

34. `SORT_MERGE_PASSES` bigint( 20 ) unsigned NOT NULL COMMENT  '' ,

35. `SORT_RANGE` bigint( 20 ) unsigned NOT NULL COMMENT  '範圍排序數目' ,

36. `SORT_ROWS` bigint( 20 ) unsigned NOT NULL COMMENT  '排序的記錄數目' ,

37. `SORT_SCAN` bigint( 20 ) unsigned NOT NULL COMMENT  '全表排序數目' ,

38. `NO_INDEX_USED` bigint( 20 ) unsigned NOT NULL COMMENT  '沒有使用索引數目' ,

39. `NO_GOOD_INDEX_USED` bigint( 20 ) unsigned NOT NULL COMMENT  '' ,

40. `NESTING_EVENT_ID` bigint( 20 ) unsigned DEFAULT NULL COMMENT  '該事件對應的父事件ID' ,

41. `NESTING_EVENT_TYPE`  enum ( 'STATEMENT' , 'STAGE' , 'WAIT' ) DEFAULT NULL COMMENT  '父事件型別(STATEMENT, STAGE, WAIT)'

42. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

六：Connection 表

1，users ：記錄使用者連線數資訊

2，hosts ：記錄了主機連線數資訊

3，accounts ：記錄了使用者主機連線數資訊

01. zjy @performance_schema   12 : 03 : 27 >select * from users;

02. +------------------+---------------------+-------------------+

03. | USER             | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

04. +------------------+---------------------+-------------------+

05. | debian-sys-maint |                      |                 36   |

06. | zjy              |                    1   |              22285   |

07. | dchat_php        |                      |              37864   |

08. | dxyslave         |                    2   |                  9   |

09. | nagios           |                      |              10770   |

10. | dchat_data       |                  140   |            2233023   |

11. | NULL             |                      |              15866   |

12. | dchat_api        |                  160   |            2754212   |

13. | mha_data         |                    1   |                 36   |

14. | backup           |                      |                 15   |

15. | cacti            |                      |               4312   |

16. | kol              |                   10   |             172414   |

17. +------------------+---------------------+-------------------+

18. 12   rows in set ( 0.00   sec)

19.  

20. zjy @performance_schema   12 : 03 : 34 >select * from hosts;

21. +-----------------+---------------------+-------------------+

22. | HOST            | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

23. +-----------------+---------------------+-------------------+

24. |  192.168 . 100.218   |                  150   |            2499422   |

25. |  192.168 . 100.240   |                   10   |             172429   |

26. |  192.168 . 100.139   |                      |                698   |

27. |  192.168 . 100.21    |                      |                  2   |

28. |  192.168 . 100.220   |                  150   |            2526136   |

29. |  192.168 . 100.25    |                    1   |                  7   |

30. | NULL            |                      |              15867   |

31. |  192.168 . 100.241   |                      |              21558   |

32. |  192.168 . 100.191   |                    1   |                 34   |

33. | localhost       |                      |              10807   |

34. |  192.168 . 100.118   |                    1   |                  2   |

35. |  192.168 . 100.251   |                      |               4312   |

36. |  192.168 . 100.23    |                    1   |                 31   |

37. |  192.168 . 100.193   |                      |                 15   |

38. +-----------------+---------------------+-------------------+

39. 14   rows in set ( 0.01   sec)

40.  

41. zjy @performance_schema   12 : 05 : 21 >select * from accounts;

42. +------------------+-----------------+---------------------+-------------------+

43. | USER             | HOST            | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

44. +------------------+-----------------+---------------------+-------------------+

45. | cacti            |  192.168 . 100.251   |                      |               4313   |

46. | debian-sys-maint | localhost       |                      |                 36   |

47. | backup           |  192.168 . 100.193   |                      |                 15   |

48. | dchat_api        |  192.168 . 100.220   |                   80   |            1382585   |

49. | dchat_php        |  192.168 . 100.220   |                      |              20292   |

50. | zjy              |  192.168 . 100.139   |                      |                698   |

51. | zjy              |  192.168 . 100.241   |                      |              21558   |

52. | mha_data         |  192.168 . 100.191   |                    1   |                 34   |

53. | dxyslave         |  192.168 . 100.118   |                    1   |                  2   |

54. | kol              |  192.168 . 100.240   |                   10   |             172431   |

55. | dxyslave         |  192.168 . 100.25    |                    1   |                  7   |

56. | dchat_data       |  192.168 . 100.218   |                   70   |            1109974   |

57. | zjy              |  192.168 . 100.23    |                    1   |                 31   |

58. | dchat_php        |  192.168 . 100.218   |                      |              17572   |

59. | dchat_data       |  192.168 . 100.220   |                   70   |            1123306   |

60. | NULL             | NULL            |                      |              15868   |

61. | mha_data         |  192.168 . 100.21    |                      |                  2   |

62. | dchat_api        |  192.168 . 100.218   |                   80   |            1371918   |

63. | nagios           | localhost       |                      |              10771   |

64. +------------------+-----------------+---------------------+-------------------+

七：Summary 表： Summary表聚集了各個維度的統計資訊包括表維度，索引維度，會話維度，語句維度和鎖維度的統計資訊

1，events_waits_summary_global_by_event_name ：按等待事件型別聚合，每個事件一條記錄

1. CREATE TABLE `events_waits_summary_global_by_event_name` (

2. `EVENT_NAME` varchar( 128 ) NOT NULL COMMENT  '事件名稱' ,

3. `COUNT_STAR` bigint( 20 ) unsigned NOT NULL COMMENT  '事件計數' ,

4. `SUM_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '總的等待時間' ,

5. `MIN_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最小等待時間' ,

6. `AVG_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '平均等待時間' ,

7. `MAX_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最大等待時間'

8. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

2，events_waits_summary_by_instance ：按等待事件物件聚合，同一種等待事件，可能有多個例項，每個例項有不同的記憶體地址，因此
event_name+object_instance_begin唯一確定一條記錄。

01. CREATE TABLE `events_waits_summary_by_instance` (

02. `EVENT_NAME` varchar( 128 ) NOT NULL COMMENT  '事件名稱' ,

03. `OBJECT_INSTANCE_BEGIN` bigint( 20 ) unsigned NOT NULL COMMENT  '記憶體地址' ,

04. `COUNT_STAR` bigint( 20 ) unsigned NOT NULL COMMENT  '事件計數' ,

05. `SUM_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '總的等待時間' ,

06. `MIN_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最小等待時間' ,

07. `AVG_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '平均等待時間' ,

08. `MAX_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最大等待時間'

09. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

3，events_waits_summary_by_thread_by_event_name ：按每個執行緒和事件來統計，thread_id+event_name唯一確定一條記錄。

01. CREATE TABLE `events_waits_summary_by_thread_by_event_name` (

02. `THREAD_ID` bigint( 20 ) unsigned NOT NULL COMMENT  '執行緒ID' ,

03. `EVENT_NAME` varchar( 128 ) NOT NULL COMMENT  '事件名稱' ,

04. `COUNT_STAR` bigint( 20 ) unsigned NOT NULL COMMENT  '事件計數' ,

05. `SUM_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '總的等待時間' ,

06. `MIN_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最小等待時間' ,

07. `AVG_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '平均等待時間' ,

08. `MAX_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最大等待時間'

09. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

4，events_stages_summary_global_by_event_name ：按事件階段型別聚合，每個事件一條記錄，表結構同上。

5，events_stages_summary_by_thread_by_event_name ：按每個執行緒和事件來階段統計，表結構同上。

6，events_statements_summary_by_digest ：按照事件的語句進行聚合。

01. CREATE TABLE `events_statements_summary_by_digest` (

02. `SCHEMA_NAME` varchar( 64 ) DEFAULT NULL COMMENT  '庫名' ,

03. `DIGEST` varchar( 32 ) DEFAULT NULL COMMENT  '對SQL_TEXT做MD5產生的32位字串。如果為consumer表中沒有開啟statement_digest選項，則為NULL' ,

04. `DIGEST_TEXT` longtext COMMENT  '將語句中值部分用問號代替，用於SQL語句歸類。如果為consumer表中沒有開啟statement_digest選項，則為NULL。' ,

05. `COUNT_STAR` bigint( 20 ) unsigned NOT NULL COMMENT  '事件計數' ,

06. `SUM_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '總的等待時間' ,

07. `MIN_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最小等待時間' ,

08. `AVG_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '平均等待時間' ,

09. `MAX_TIMER_WAIT` bigint( 20 ) unsigned NOT NULL COMMENT  '最大等待時間' ,

10. `SUM_LOCK_TIME` bigint( 20 ) unsigned NOT NULL COMMENT  '鎖時間總時長' ,

11. `SUM_ERRORS` bigint( 20 ) unsigned NOT NULL COMMENT  '錯誤數的總' ,

12. `SUM_WARNINGS` bigint( 20 ) unsigned NOT NULL COMMENT  '警告的總數' ,

13. `SUM_ROWS_AFFECTED` bigint( 20 ) unsigned NOT NULL COMMENT  '影響的總數目' ,

14. `SUM_ROWS_SENT` bigint( 20 ) unsigned NOT NULL COMMENT  '返回總數目' ,

15. `SUM_ROWS_EXAMINED` bigint( 20 ) unsigned NOT NULL COMMENT  '總的掃描的數目' ,

16. `SUM_CREATED_TMP_DISK_TABLES` bigint( 20 ) unsigned NOT NULL COMMENT  '建立磁碟臨時表的總數目' ,

17. `SUM_CREATED_TMP_TABLES` bigint( 20 ) unsigned NOT NULL COMMENT  '建立臨時表的總數目' ,

18. `SUM_SELECT_FULL_JOIN` bigint( 20 ) unsigned NOT NULL COMMENT  '第一個表全表掃描的總數目' ,

19. `SUM_SELECT_FULL_RANGE_JOIN` bigint( 20 ) unsigned NOT NULL COMMENT  '總的採用range方式掃描的數目' ,

20. `SUM_SELECT_RANGE` bigint( 20 ) unsigned NOT NULL COMMENT  '第一個表採用range方式掃描的總數目' ,

21. `SUM_SELECT_RANGE_CHECK` bigint( 20 ) unsigned NOT NULL COMMENT  '' ,

22. `SUM_SELECT_SCAN` bigint( 20 ) unsigned NOT NULL COMMENT  '第一個表位全表掃描的總數目' ,

23. `SUM_SORT_MERGE_PASSES` bigint( 20 ) unsigned NOT NULL COMMENT  '' ,

24. `SUM_SORT_RANGE` bigint( 20 ) unsigned NOT NULL COMMENT  '範圍排序總數' ,

25. `SUM_SORT_ROWS` bigint( 20 ) unsigned NOT NULL COMMENT  '排序的記錄總數目' ,

26. `SUM_SORT_SCAN` bigint( 20 ) unsigned NOT NULL COMMENT  '第一個表排序掃描總數目' ,

27. `SUM_NO_INDEX_USED` bigint( 20 ) unsigned NOT NULL COMMENT  '沒有使用索引總數' ,

28. `SUM_NO_GOOD_INDEX_USED` bigint( 20 ) unsigned NOT NULL COMMENT  '' ,

29. `FIRST_SEEN` timestamp NOT NULL DEFAULT  '0000-00-00 00:00:00'   COMMENT  '第一次執行時間' ,

30. `LAST_SEEN` timestamp NOT NULL DEFAULT  '0000-00-00 00:00:00'   COMMENT  '最後一次執行時間'

31. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

7，events_statements_summary_global_by_event_name ：按照事件的語句進行聚合。表結構同上。

8，events_statements_summary_by_thread_by_event_name ：按照執行緒和事件的語句進行聚合，表結構同上。

9， file_summary_by_instance ：按事件型別統計（ 物理IO維度 ）

10， file_summary_by_event_name ：具體檔案統計（ 物理IO維度 ）

9和10一起說明：

統計IO操作：COUNT_STAR，SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

統計讀      ：COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ

統計寫      ：COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE

統計其他IO事件，比如create，delete，open，close等：COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC

11，table_io_waits_summary_by_table ：根據wait/io/table/sql/handler，聚合每個表的I/O操作（ 邏輯IO緯度 ）

統計IO操作：COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT 

統計讀      ：COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ

              ：COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH

統計寫      ：COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE

INSERT統計，相應的還有DELETE和UPDATE統計：COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT

12，table_io_waits_summary_by_index_usage： 與table_io_waits_summary_by_table類似，按索引維度統計

13，table_lock_waits_summary_by_table ：聚合了表鎖等待事件，包括internal lock 和 external lock

internal lock透過SQL層函式thr_lock呼叫，OPERATION值為：
read normal、read with shared locks、read high priority、read no insert、write allow write、write concurrent insert、write delayed、write low priority、write normal
external lock則透過介面函式handler::external_lock呼叫儲存引擎層，OPERATION列的值為：read external、write external

14，Connection Summaries表 ：account、user、host

events_waits_summary_by_account_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_by_host_by_event_name 
events_stages_summary_by_account_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_by_host_by_event_name 
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_by_host_by_event_name

15，socket_summary_by_instance、socket_summary_by_event_name ：socket聚合統計表。

八：其他相關表

1，performance_timers ：系統支援的統計時間單位

2，threads ：監視服務端的當前執行的執行緒

統計應用：

      關於SQL維度的統計資訊主要集中在events_statements_summary_by_digest表中，透過將SQL語句抽象出digest，可以統計某類SQL語句在各個維度的統計資訊

1，哪個SQL執行最多：

01. zjy @performance_schema   11 : 36 : 22 ><strong>SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1G

02. </strong>***************************  1 . row ***************************<strong>

03. SCHEMA_NAME</strong>: dchat

04. <strong>DIGEST_TEXT</strong>: SELECT ...

05. <strong>COUNT_STAR</strong>:  1161210102

06. SUM_ROWS_SENT:  1161207842

07. SUM_ROWS_EXAMINED:  <strong>

08. FIRST_SEEN</strong>:  2016 - 02 - 17   00 : 36 : 46 <strong>

09. LAST_SEEN</strong>:  2016 - 03 - 07   11 : 36 : 29

各個欄位的註釋可以看上面的表結構說明：從2月17號到3月7號該SQL執行了1161210102次。

2，哪個SQL平均響應時間最多：

01. zjy @performance_schema   11 : 36 : 28 ><strong>SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1G

02. </strong>***************************  1 . row ***************************<strong>

03. SCHEMA_NAME</strong>: dchat

04. <strong>DIGEST_TEXT</strong>: SELECT ...

05. COUNT_STAR:  1 <strong>

06. AVG_TIMER_WAIT</strong>:  273238183964000

07. SUM_ROWS_SENT:  50208

08. SUM_ROWS_EXAMINED:  5565651 <strong>

09. FIRST_SEEN</strong>:  2016 - 02 - 22   13 : 27 : 33 <strong>

10. LAST_SEEN</strong>:  2016 - 02 - 22   13 : 27 : 33

各個欄位的註釋可以看上面的表結構說明：從2月17號到3月7號該SQL平均響應時間273238183964000皮秒（1000000000000皮秒=1秒）

3，哪個SQL掃描的行數最多：

SUM_ROWS_EXAMINED

4，哪個SQL使用的臨時表最多：

SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES

5，哪個SQL返回的結果集最多：

SUM_ROWS_SENT

6，哪個SQL排序數最多：

SUM_SORT_ROWS

透過上述指標我們可以間接獲得某類SQL的邏輯IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，網路頻寬(SUM_ROWS_SENT)的對比。

透過 file_summary_by_instance 表，可以獲得系統執行到現在，哪個檔案(表)物理IO最多，這可能意味著這個表經常需要訪問磁碟IO。

7，哪個表、檔案邏輯IO最多（熱資料）：

01. zjy @performance_schema   12 : 16 : 18 ><strong>SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2G

02. </strong>***************************  1 . row ***************************

03. FILE_NAME: /var/lib/mysql/<strong>ibdata1  #檔案</strong>

04. EVENT_NAME: wait/io/file/innodb/innodb_data_file

05. COUNT_READ:  544

06. SUM_NUMBER_OF_BYTES_READ:  10977280

07. COUNT_WRITE:  3700729

08. SUM_NUMBER_OF_BYTES_WRITE:  1433734217728

09. ***************************  2 . row ***************************

10. FILE_NAME: /var/lib/mysql/dchat/<strong>fans.ibd   #表</strong>

11. EVENT_NAME: wait/io/file/innodb/innodb_data_file

12. COUNT_READ:  9370680

13. SUM_NUMBER_OF_BYTES_READ:  153529188352

14. COUNT_WRITE:  67576376

15. SUM_NUMBER_OF_BYTES_WRITE:  1107815432192

8，哪個索引使用最多：

1. zjy @performance_schema   12 : 18 : 42 ><strong>SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC limit  1 ;

2. </strong>+-------------+------------+-------------+--------------+--------------+--------------+

3. | OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |

4. +-------------+------------+-------------+--------------+--------------+--------------+

5. | <strong>fans</strong>        | <strong>PRIMARY</strong>    |  29002695158   |             |     296373434   |               |

6. +-------------+------------+-------------+--------------+--------------+--------------+

7. 1   row in set ( 0.29   sec)

透過 table_io_waits_summary_by_index_usage 表，可以獲得系統執行到現在，哪個表的具體哪個索引(包括主鍵索引，二級索引)使用最多。

9，哪個索引沒有使用過：

1. zjy @performance_schema   12 : 23 : 22 ><strong>SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR =    AND OBJECT_SCHEMA <>  'mysql'   ORDER BY OBJECT_SCHEMA,OBJECT_NAME;</strong>

10，哪個等待事件消耗的時間最多：

1. zjy @performance_schema   12 : 25 : 22 ><strong>SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name !=  'idle' ORDER BY SUM_TIMER_WAIT DESC LIMIT  1 ;</strong>

11，類似profiling功能：

分析具體某條SQL，該SQL在執行各個階段的時間消耗，透過events_statements_xxx表和events_stages_xxx表，就可以達到目的。兩個表透過event_id與nesting_event_id關聯，stages表的nesting_event_id為對應statements表的event_id；針對每個stage可能出現的鎖等待，一個stage會對應一個或多個wait，透過stage_xxx表的event_id欄位與waits_xxx表的nesting_event_id進行關聯。如：

001. 比如分析包含count(*)的某條SQL語句，具體如下：

002.  

003. SELECT

004. EVENT_ID,

005. sql_text

006. FROM events_statements_history

007. WHERE sql_text LIKE  '%count(*)%' ;

008. +----------+--------------------------------------+

009. | EVENT_ID | sql_text |

010. +----------+--------------------------------------+

011. |  1690   | select count(*) from chuck.test_slow |

012. +----------+--------------------------------------+

013. 首先得到了語句的event_id為 1690 ，透過查詢events_stages_xxx中nesting_event_id為 1690 的記錄，可以達到目的。

014.  

015. a.檢視每個階段的時間消耗：

016. SELECT

017. event_id,

018. EVENT_NAME,

019. SOURCE,

020. TIMER_END - TIMER_START

021. FROM events_stages_history_long

022. WHERE NESTING_EVENT_ID =  1690 ;

023. +----------+--------------------------------+----------------------+-----------------------+

024. | event_id | EVENT_NAME | SOURCE | TIMER_END-TIMER_START |

025. +----------+--------------------------------+----------------------+-----------------------+

026. |  1691   | stage/sql/init | mysqld.cc: 990   |  316945000   |

027. |  1693   | stage/sql/checking permissions | sql_parse.cc: 5776   |  26774000   |

028. |  1695   | stage/sql/Opening tables | sql_base.cc: 4970   |  41436934000   |

029. |  2638   | stage/sql/init | sql_select.cc: 1050   |  85757000   |

030. |  2639   | stage/sql/System lock | lock.cc: 303   |  40017000   |

031. |  2643   | stage/sql/optimizing | sql_optimizer.cc: 138   |  38562000   |

032. |  2644   | stage/sql/statistics | sql_optimizer.cc: 362   |  52845000   |

033. |  2645   | stage/sql/preparing | sql_optimizer.cc: 485   |  53196000   |

034. |  2646   | stage/sql/executing | sql_executor.cc: 112   |  3153000   |

035. |  2647   | stage/sql/Sending data | sql_executor.cc: 192   |  7369072089000   |

036. |  4304138   | stage/sql/end | sql_select.cc: 1105   |  19920000   |

037. |  4304139   | stage/sql/query end | sql_parse.cc: 5463   |  44721000   |

038. |  4304145   | stage/sql/closing tables | sql_parse.cc: 5524   |  61723000   |

039. |  4304152   | stage/sql/freeing items | sql_parse.cc: 6838   |  455678000   |

040. |  4304155   | stage/sql/logging slow query | sql_parse.cc: 2258   |  83348000   |

041. |  4304159   | stage/sql/cleaning up | sql_parse.cc: 2163   |  4433000   |

042. +----------+--------------------------------+----------------------+-----------------------+

043. 透過間接關聯，我們能分析得到SQL語句在每個階段的時間消耗，時間單位以皮秒錶示。這裡展示的結果很類似profiling功能，有了performance schema，就不再需要profiling這個功能了。另外需要注意的是，由於預設情況下events_stages_history表中只為每個連線記錄了最近 10 條記錄，為了確保獲取所有記錄，需要訪問events_stages_history_long表

044.  

045. b.檢視某個階段的鎖等待情況

046. 針對每個stage可能出現的鎖等待，一個stage會對應一個或多個wait，events_waits_history_long這個表容易爆滿[預設閥值 10000 ]。由於select count(*)需要IO(邏輯IO或者物理IO)，所以在stage/sql/Sending data階段會有io等待的統計。透過stage_xxx表的event_id欄位與waits_xxx表的nesting_event_id進行關聯。

047. SELECT

048. event_id,

049. event_name,

050. source,

051. timer_wait,

052. object_name,

053. index_name,

054. operation,

055. nesting_event_id

056. FROM events_waits_history_long

057. WHERE nesting_event_id =  2647 ;

058. +----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

059. | event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |

060. +----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

061. |  190607   | wait/io/table/sql/handler | handler.cc: 2842   |  1845888   | test_slow | idx_c1 | fetch |  2647   |

062. |  190608   | wait/io/table/sql/handler | handler.cc: 2842   |  1955328   | test_slow | idx_c1 | fetch |  2647   |

063. |  190609   | wait/io/table/sql/handler | handler.cc: 2842   |  1929792   | test_slow | idx_c1 | fetch |  2647   |

064. |  190610   | wait/io/table/sql/handler | handler.cc: 2842   |  1869600   | test_slow | idx_c1 | fetch |  2647   |

065. |  190611   | wait/io/table/sql/handler | handler.cc: 2842   |  1922496   | test_slow | idx_c1 | fetch |  2647   |

066. +----------+---------------------------+-----------------+------------+-------------+------------+-----------+------------------+

067. 透過上面的實驗，我們知道了statement,stage,wait的三級結構，透過nesting_event_id進行關聯，它表示某個事件的父event_id。

068.  

069. ( 2 ).模擬innodb行鎖等待的例子

070. 會話A執行語句update test_icp set y=y+ 1   where x= 1 (x為primary key)，不commit；會話B執行同樣的語句update test_icp set y=y+ 1   where x= 1 ，會話B堵塞，並最終報錯。透過連線連線查詢events_statements_history_long和events_stages_history_long，可以看到在updating階段花了大約60s的時間。這主要因為例項上的innodb_lock_wait_timeout設定為 60 ，等待60s後超時報錯了。

071.  

072. SELECT

073. statement.EVENT_ID,

074. stages.event_id,

075. statement.sql_text,

076. stages.event_name,

077. stages.timer_wait

078. FROM events_statements_history_long statement

079. join events_stages_history_long stages

080. on statement.event_id=stages.nesting_event_id

081. WHERE statement.sql_text =  'update test_icp set y=y+1 where x=1' ;

082. +----------+----------+-------------------------------------+--------------------------------+----------------+

083. | EVENT_ID | event_id | sql_text | event_name | timer_wait |

084. +----------+----------+-------------------------------------+--------------------------------+----------------+

085. |  5816   |  5817   | update test_icp set y=y+ 1   where x= 1   | stage/sql/init |  195543000   |

086. |  5816   |  5819   | update test_icp set y=y+ 1   where x= 1   | stage/sql/checking permissions | 22730000   |

087. |  5816   |  5821   | update test_icp set y=y+ 1   where x= 1   | stage/sql/Opening tables |  66079000   |

088. |  5816   |  5827   | update test_icp set y=y+ 1   where x= 1   | stage/sql/init |  89116000   |

089. |  5816   |  5828   | update test_icp set y=y+ 1   where x= 1   | stage/sql/System lock |  218744000   |

090. |  5816   |  5832   | update test_icp set y=y+ 1   where x= 1   | stage/sql/updating |  6001362045000   |

091. |  5816   |  5968   | update test_icp set y=y+ 1   where x= 1   | stage/sql/end |  10435000   |

092. |  5816   |  5969   | update test_icp set y=y+ 1   where x= 1   | stage/sql/query end |  85979000   |

093. |  5816   |  5983   | update test_icp set y=y+ 1   where x= 1   | stage/sql/closing tables |  56562000   |

094. |  5816   |  5990   | update test_icp set y=y+ 1   where x= 1   | stage/sql/freeing items |  83563000   |

095. |  5816   |  5992   | update test_icp set y=y+ 1   where x= 1   | stage/sql/cleaning up |  4589000   |

096. +----------+----------+-------------------------------------+--------------------------------+----------------+

097. 檢視wait事件：

098. SELECT

099. event_id,

100. event_name,

101. source,

102. timer_wait,

103. object_name,

104. index_name,

105. operation,

106. nesting_event_id

107. FROM events_waits_history_long

108. WHERE nesting_event_id =  5832 ;

109. ***************************  1 . row ***************************

110. event_id:  5832

111. event_name: wait/io/table/sql/handler

112. source: handler.cc: 2782

113. timer_wait:  6005946156624

114. object_name: test_icp

115. index_name: PRIMARY

116. operation: fetch

117. 從結果來看，waits表中記錄了一個fetch等待事件，但並沒有更細的innodb行鎖等待事件統計。

118.  

119. ( 3 ).模擬MDL鎖等待的例子

120. 會話A執行一個大查詢select count(*) from test_slow，會話B執行表結構變更alter table test_slow modify c2 varchar( 152 );透過如下語句可以得到alter語句的執行過程，重點關注“stage/sql/Waiting  for   table metadata lock”階段。

121.  

122. SELECT

123. statement.EVENT_ID,

124. stages.event_id,

125. statement.sql_text,

126. stages.event_name as stage_name,

127. stages.timer_wait as stage_time

128. FROM events_statements_history_long statement

129. left join events_stages_history_long stages

130. on statement.event_id=stages.nesting_event_id

131. WHERE statement.sql_text =  'alter table test_slow modify c2 varchar(152)' ;

132. +-----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+

133. | EVENT_ID | event_id | sql_text | stage_name | stage_time |

134. +-----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+

135. |  326526744   |  326526745   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/init | 216662000   |

136. |  326526744   |  326526747   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/checking permissions |  18183000   |

137. |  326526744   |  326526748   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/checking permissions |  10294000   |

138. |  326526744   |  326526750   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/init | 4783000   |

139. |  326526744   |  326526751   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/Opening tables |  140172000   |

140. |  326526744   |  326526760   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/setup | 157643000   |

141. |  326526744   |  326526769   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/creating table |  8723217000   |

142. |  326526744   |  326526803   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/After create |  257332000   |

143. |  326526744   |  326526832   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/Waiting for   table metadata lock |  1000181831000   |

144. |  326526744   |  326526835   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/After create |  33483000   |

145. |  326526744   |  326526838   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/Waiting for   table metadata lock |  1000091810000   |

146. |  326526744   |  326526841   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/After create |  17187000   |

147. |  326526744   |  326526844   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/Waiting for   table metadata lock |  1000126464000   |

148. |  326526744   |  326526847   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/After create |  27472000   |

149. |  326526744   |  326526850   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/Waiting for   table metadata lock |  561996133000   |

150. |  326526744   |  326526853   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/After create |  124876000   |

151. |  326526744   |  326526877   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/System lock |  30659000   |

152. |  326526744   |  326526881   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/preparing for   alter table |  40246000   |

153. |  326526744   |  326526889   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/altering table |  36628000   |

154. |  326526744   |  326528280   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/end | 43824000   |

155. |  326526744   |  326528281   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/query end |  112557000   |

156. |  326526744   |  326528299   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/closing tables |  27707000   |

157. |  326526744   |  326528305   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/freeing items |  201614000   |

158. |  326526744   |  326528308   | alter table test_slow modify c2 varchar( 152 ) | stage/sql/cleaning up |  3584000   |

159. +-----------+-----------+----------------------------------------------+----------------------------------------------------+---------------+

160. 從結果可以看到，出現了多次stage/sql/Waiting  for   table metadata lock階段，並且間隔1s，說明每隔1s鍾會重試判斷。找一個該階段的event_id,透過nesting_event_id關聯，確定到底在等待哪個wait事件。

161. SELECT

162. event_id,

163. event_name,

164. source,

165. timer_wait,

166. object_name,

167. index_name,

168. operation,

169. nesting_event_id

170. FROM events_waits_history_long

171. WHERE nesting_event_id =  326526850 ;

172. +-----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+

173. | event_id | event_name | source | timer_wait | object_name | index_name | operation | nesting_event_id |

174. +-----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+

175. |  326526851   | wait/synch/cond/sql/MDL_context::COND_wait_status | mdl.cc: 1327   |  562417991328 | NULL | NULL | timed_wait |  326526850   |

176. |  326526852   | wait/synch/mutex/mysys/my_thread_var::mutex | sql_class.h: 3481   |  733248   | NULL | NULL | lock |  326526850   |

177. +-----------+---------------------------------------------------+------------------+--------------+-------------+------------+------------+------------------+

178. 透過結果可以知道，產生阻塞的是條件變數MDL_context::COND_wait_status，並且顯示了程式碼的位置。

總結：

本文透過對Performance Schema資料庫的介紹，主要用於收集資料庫伺服器效能引數：①提供程式等待的詳細資訊，包括鎖、互斥變數、檔案資訊；②儲存歷史的事件彙總資訊，為提供MySQL伺服器效能做出詳細的判斷；③對於新增和刪除監控事件點都非常容易，並可以改變mysql伺服器的監控週期，例如（CYCLE、MICROSECOND）。透過該庫得到資料庫執行的統計資訊，更好分析定位問題和完善監控資訊。類似的監控還有：

1. 開啟標準的innodb監控：

2. CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

3. 開啟innodb的鎖監控：

4. CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

5. 開啟innodb表空間監控：

6. CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

7. 開啟innodb表監控：

8. CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

                       

                                                   

開啟MySQL performance_schema 後對效能的影響

原文地址 先佔坑，還需要進一步測試 接上篇 使用 sysbench 對資料庫進行壓測，觀察開啟performance_schema後，對資料庫效能的影響 壓測計劃 ...