ITPub博客

首页 > 数据库 > MySQL > MySQL5.6 PERFORMANCE_SCHEMA 说明

MySQL5.6 PERFORMANCE_SCHEMA 说明

MySQL 作者:yuanqc 时间:2018-09-19 16:53:15 0 删除 编辑

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

转载

背景:

       MySQL  5.5开始新增一个数据库:PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA,而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。 MySQL 5.5默认是关闭的,需要手动开启,在配置文件里添加:

view source print ?

1. [mysqld]

2. performance_schema=ON

查看是否开启:

view source print ?

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)表:

view source print ?

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 :配置用户纬度的监控,默认监控所有用户。

view source print ?

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

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

3. | HOST | USER | ROLE |

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

5. | %    | %    | %    |

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

2,setup_consumers :配置events的消费者类型,即收集的events写入到哪些统计表中。

view source print ?

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。如:

view source print ?

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)

更新完后立即生效,但是服务器重启之后又会变回默认值,要永久生效需要在配置文件里添加:

view source print ?

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里面的值有个层级关系:

view source print ?

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表。长度通过控制参数:

view source print ?

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:

view source print ?

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的所有表都监控。

view source print ?

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皮秒)

view source print ?

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 显示当前文件打开的数目,如果重来没有打开过,不会出现在表中。

view source print ?

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个事件

表结构定义如下:

view source print ?

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条历史记录

表结构定义如下:

view source print ?

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

表结构定义如下:

view source print ?

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 :记录了用户主机连接数信息

加载中... 加载中...

view source print ?

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 :按等待事件类型聚合,每个事件一条记录

view source print ?

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唯一确定一条记录。

view source print ?

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唯一确定一条记录。

view source print ?

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 :按照事件的语句进行聚合。

view source print ?

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执行最多:

view source print ?

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平均响应时间最多:

view source print ?

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最多(热数据):

view source print ?

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,哪个索引使用最多:

view source print ?

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,哪个索引没有使用过:

view source print ?

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,哪个等待事件消耗的时间最多:

view source print ?

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进行关联。如:

加载中... 加载中...

view source print ?

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+