MySQL5.6PERFORMANCE_SCHEMA说明_MySQL
程序员文章站
2022-05-07 15:42:39
...
背景：
MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的，需要手动开启，在配置文件里添加：
view
sourceprint?1.[mysqld]2.performance_schema=ON
查看是否开启：
view
sourceprint?
1.
mysql>show
variables like
'performance_schema'
;
2.
+--------------------+-------+
3.
|
Variable_name | Value |
4.
+--------------------+-------+
5.
|
performance_schema |
ON
|
6.
+--------------------+-------+
从MySQL5.6开始，默认打开，本文就从MySQL5.6来说明，在数据库使用当中PERFORMANCE_SCHEMA的一些比较常用的功能。具体的信息可以查看官方文档。
相关表信息：
一：配置（setup）表：
view sourceprint? 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 sourceprint? 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 sourceprint? 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 sourceprint? 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 sourceprint? 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 sourceprint? 1. global_instrumentation > thread_instrumentation = statements_digest > events_stages_ current = events_statements_current = events_waits_current > events_stages_ history = events_statements_history = events_waits_history > events_stages_ history_long = 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 sourceprint? 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 sourceprint? 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
sourceprint?
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 | % | % |
YES
|
YES
|
09.
+-------------+--------------------+-------------+---------+-------+
5，setup_timers：配置每种类型指令的统计时间单位。MICROSECOND表示统计单位是微妙，CYCLE表示统计单位是时钟周期，时间度量与CPU的主频有关，NANOSECOND表示统计单位是纳秒。但无论采用哪种度量单位，最终统计表中统计的时间都会装换到皮秒。（1秒＝1000000000000皮秒）
view sourceprint? 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. +-----------+-------------+