第4章 performance_schema初相识

4.2.1　检查当前数据库版本是否支持

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE ='PERFORMANCE_SCHEMA';
......
1 row in set (0.00 sec)

mysql> show engines;
......
9 rows in set (0.00 sec)

4.2.2　启用performance_schema

[mysqld]
performance_schema = ON  # 注意：该参数为只读参数，需要在实例启动之前设置才生效

mysql> show variables like 'performance_schema';
......
1 row in set (0.00 sec)

mysql>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='performance_ schema' and engine='performance_schema';
......
87 rows in set (0.00 sec)

mysql> use performance_schema
Database changed
mysql> show tables from performance_schema;
......
87 rows in set (0.00 sec)

4.2.3　performance_schema表的分类

mysql> show tables like 'events_statement%';
......
11 rows in set (0.00 sec)

mysql> show tables like 'events_wait%';
......
12 rows in set (0.01 sec)

mysql> show tables like 'events_stage%';
......
8 rows in set (0.00 sec)

mysql> show tables like 'events_transaction%';
......
8 rows in set (0.00 sec)

mysql> show tables like '%file%';
......
3 rows in set (0.01 sec)

mysql> show tables like '%memory%';
......
5 rows in set (0.01 sec)

mysql> show tables like '%setup%';
......
5 rows in set (0.00 sec)

4.2.4　performance_schema简单配置与使用

mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 323  Changed: 0  Warnings: 0

mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM events_waits_current limit 1\G
......
1 row in set (0.02 sec)

# 该事件信息表示线程ID为4的线程正在等待InnoDB存储引擎的log_sys_mutex锁，这是InnoDB存储引擎的一个互斥锁，等待时间为65664皮秒（*_ID列表示事件来自哪个线程、事件编号是多少；EVENT_NAME表示检测到的具体内容；SOURCE表示这个检测代码在哪个源文件中以及行号；计时器字段TIMER_START、TIMER_END、TIMER_WAIT分别表示该事件的开始时间、结束时间和总的花费时间，如果该事件正在运行而没有结束，那么TIMER_END和TIMER_WAIT的值显示为NULL。注：计时器统计的值是近似值，并不是完全精确的）

mysql> SELECT THREAD_ID, EVENT_ID,EVENT_NAME,TIMER_WAIT FROM events_waits_history ORDER BY THREAD_ID limit 21;
......
21 rows in set (0.00 sec) 

mysql> SELECT EVENT_NAME, COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;
......

mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
......

# 这些结果表明，THR_LOCK_malloc互斥事件是最热的。注：THR_LOCK_malloc互斥事件仅在DEBUG版本中存在，在GA版本中不存在

mysql> SELECT * FROM file_instances limit 20;
......
20 rows in set (0.00 sec)

---

上一篇：第3章　MySQL体系结构 | 下一篇：第5章　performance_schema配置详解