【结论】
- 通过 performance_schema.events_waits_history_current + sys.schema_processlist 关联可拆解单条查询的 Buffer Pool IO/锁/CPU 时间
- P99 抖动优先排查 Buffer Pool IO 瓶颈(使用 sys.schema_buffer_pool_stats)
- 附 SQL 脚本:SELECT * FROM (SELECT ... ) AS io JOIN (SELECT ... ) AS lock ON ...
【依据与步骤】
-
启用 instrumentation 点: SET GLOBAL performance_schema_events_waits_history_long_size = 1000; SET GLOBAL performance_schema_events_waits_history_current_size = 1000;
-
关键表关联: SELECT p.processlist_id, p.user, p.host, p.db, p.command, p.time, p.state, w.event_name, w.timer_wait AS total_time, w.count_star FROM sys.schema_processlist p JOIN performance_schema.events_waits_history_current w ON p.processlist_id = w.thread_id WHERE p.command = 'Query';
-
三维度拆解:
- IO:SELECT * FROM sys.schema_buffer_pool_stats WHERE ...
- 锁:SELECT * FROM sys.schema_lock_waits WHERE ...
- CPU:SELECT * FROM sys.schema_processlist WHERE ...
【验证】
- 通过 sys.schema_processlist.time 字段监控单条查询耗时
- 使用 sys.schema_buffer_pool_stats.memory_used / buffer_pool_size 计算 Buffer Pool 使用率
- 检查 events_waits_history_current 的 timer_wait 总和与实际 P99 对比
补充:使用 sys.schema_buffer_pool_stats.memory_used / buffer_pool_size 可实时监控 Buffer Pool 使用率,当该值突增至 95%+ 且 P99 抖动时,优先检查 innodb_io_capacity 是否与磁盘性能匹配(建议设置为 2000-5000 之间)