利用等待(wait)事件排查MySQL性能问题.md

整理自《MySQL性能优化金字塔法则》P93 - 96

启用等待（wait）事件的采集与统计

开启 采集所有等待事件的信息 的功能

use performance_schema;
update setup_instruments set enabled='yes', timed='yes' where name like 'wait/%';

开启 等待事件信息 的统计功能

update setup_consumers set enabled='yes' where name like '%wait%';

模拟压力

[root@db30 ~]# sysbench --db-driver=mysql --time=320 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=3 --table-size=2000000 oltp_read_write --db-ps-mode=disable run

查看操作系统各项指标

通过top，iostat命令查看此时的系统性能情况

查看MySQL等待事件信息

为了方便查询等待事件的统计信息，创建视图，用于实时统计当前等待事件（非历史数据）

create view sys.test_waits as 
select 
sum(timer_wait) as TIMER_WAIT,
sum(NUMBER_OF_BYTES) as NUMBER_OF_BYTES,
EVENT_NAME,OPERATION 
from performance_schema.events_waits_current
where EVENT_NAME != 'idle' 
group by EVENT_NAME,OPERATION;

从上方视图中查询当前时间开销较大的等待事件

select sys.format_time(TIMER_WAIT),sys.format_bytes(NUMBER_OF_BYTES),EVENT_NAME,OPERATION 
from sys.test_waits 
where sys.format_time(TIMER_WAIT) not regexp 'ns|us'
order by TIMER_WAIT desc;

+-----------------------------+-----------------------------------+----------------------------------------------+-----------+
| sys.format_time(TIMER_WAIT) | sys.format_bytes(NUMBER_OF_BYTES) | EVENT_NAME                                   | OPERATION |
+-----------------------------+-----------------------------------+----------------------------------------------+-----------+
| 18.69 m                     | NULL                              | wait/synch/cond/sql/COND_compress_gtid_table | wait      |
+-----------------------------+-----------------------------------+----------------------------------------------+-----------+

或者直接从event_waits_current表查找（返回的数据行数可能比较多，且对查询结果没有做分组聚合，是逐行的事件记录数据

select *
from
performance_schema.events_waits_current
where EVENT_NAME != 'idle'
order by TIMER_WAIT DESC;

另求助：
wait/synch/cond/sql/COND_compress_gtid_table
好像是压缩gtid_executed表，但为什么会等这么久？求助