MySQL5.6PERFORMANCE_SCHEMA说明_MySQL
背景: MySQL 5.5开始新增一个数据库:PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA,而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的,需要手动开启,在配置文件里添加: view sourceprint?
view sourceprint?1.mysql>show variables like 'performance_schema';2.+--------------------+-------+3.| Variable_name | Value |4.+--------------------+-------+5.| performance_schema | ON |6.+--------------------+-------+
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.+----------------------------------------+
view sourceprint?1.zjy@performance_schema 10:19:11>select * from setup_actors;2.+------+------+------+3.| HOST | USER | ROLE |4.+------+------+------+5.| % | % | % |6.+------+------+------+
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.+--------------------------------+---------+
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_schema3.performance_schema_consumer_events_waits_current=on4.performance_schema_consumer_events_stages_current=on5.performance_schema_consumer_events_statements_current=on6.performance_schema_consumer_events_waits_history=on7.performance_schema_consumer_events_stages_history=on8.performance_schema_consumer_events_statements_history=on
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
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.+---------------------------------+----------+
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.+-------------+--------------------+-------------+---------+-------+ |