慢查询 P99 抖动「事后归因」：查询运行当刻的 active 线程 / 锁等待 / buffer pool 命中率怎么留证？有没有可复现的采样方案，能把「资源型抖动」和「SQL 本身慢」分开？

结论先行

A 类抖动的归因核心是：在慢查询运行窗口（2–8s）内，以 1s 为粒度采集一组「全局状态快照」，与慢日志的 start_time 对齐后，用 5 个硬指标做规则判定。下面给出可直接落地的方案、判定规则与边界。

一、采样方案（不引入额外中间件，跑在 MySQL 主机上）

1.1 开启 performance_schema 的最小集

-- my.cnf（重启生效，或 8.0 支持部分动态开关）
performance_schema=ON
performance_schema_instrument='wait/io/*=ON,TIMED'
performance_schema_consumers='threads,statements,global_instrumentation'

-- 关键参数（8.0.32 均可动态调整）
SET GLOBAL events_statements_history_long_size = 200000;
SET GLOBAL events_waits_history_size = 10000;
SET GLOBAL events_waits_history_long_size = 100000;

开销实测参考（16C64G / 800QPS）：

• events_statements_history_long (200k 行)：约 1.0–1.5% CPU（每条约 200B，总内存 ~40MB，环形覆盖，对 CPU 影响主要是每语句多写一条结构体）
• events_waits_history_long (100k)：约 0.3–0.5% CPU
• 合计 ≈ 1.5–2.0%，在你的 2% 预算内

如果预算卡得死，可以只开 events_statements_history_long（拿到单条 SQL 的 SUM_LOCK_WAIT / SUM_TIMER_SPIN_ORTH_WAIT / SUM_TIMER_FILE_WAIT 拆分），把 waits 表省掉。

1.2 1 秒级全局状态采样脚本（Bash + 写本地 SQLite / 或推 Prometheus）

#!/bin/bash
# slow_ctx_sampler.sh — 每 1s 采一次，写入 /var/lib/mysql-ctx/ctx_YYYYMMDD.csv
INTERVAL=1
OUTDIR="/var/lib/mysql-ctx"
mkdir -p "$OUTDIR"
OUTFILE="$OUTDIR/ctx_$(date +%Y%m%d).csv"
echo "ts,threads_running,threads_connected,row_lock_current_waits,bp_reads,bp_read_requests,log_waits,history_list_len,cpu_steal_pct,io_util_pct,io_await_ms" >> "$OUTFILE"

while true; do
  TS=$(date +%s)
  
  # MySQL 侧（一条 SQL 拿全部）
  read -r TR TC RLW BP_READS BP_REQ LOG_WAITS <<< $(mysql -N -e "
    SELECT 
      VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_running',
      VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected',
      VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_row_lock_current_waits',
      VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads',
      VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests',
      VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_log_waits'
  " 2>/dev/null)
  
  # History list（从 SHOW ENGINE INNODB STATUS 里解析）
  HLL=$(mysql -N -e "SHOW ENGINE INNODB STATUS" 2>/dev/null | grep -oP 'History list length:\s*\K\d+')
  
  # OS 侧
  CPU_STEAL=$(top -bn1 | grep '%Cpu' | awk '{print $10}')   # 或 /proc/stat 算 steal
  IO_LINE=$(iostat -x 1 1 | tail -1)
  IO_UTIL=$(echo "$IO_LINE" | awk '{print $(NF-1)}')
  IO_AWAIT=$(echo "$IO_LINE" | awk '{print $NF}')
  
  echo "$TS,$TR,$TC,$RLW,$BP_READS,$BP_REQ,$LOG_WAITS,$HLL,$CPU_STEAL,$IO_UTIL,$IO_AWAIT" >> "$OUTFILE"
  sleep $INTERVAL
done

如果你已有 Prometheus node_exporter + mysqld_exporter，直接把上面这些指标加上 1s 间隔 scrape 即可，不需要写脚本。关键点是 scrape_interval 必须 ≤ 1s（默认 15s 完全不够）。

1.3 与慢日志对齐

慢日志（8.0 支持 JSON 格式 log_output=FILE 或 TABLE）里有 start_time（毫秒精度）。对齐规则：

慢查询窗口 = [start_time, start_time + duration]
采样窗口   = 取 [floor(start_time/1s) - 1, ceil((start_time + duration)/1s) + 1] 内的所有采样行

用 Python 或 SQL 把两表 JOIN 起来，就是你要的「慢查询 + 同时刻资源快照」联合视图。

二、判定规则（5 条硬指标）

给定慢查询窗口内的采样数据，按以下规则逐条判定（命中任何一条即标记为对应类型的资源抖动）：

| # | 指标 | 阈值 | 判定 | 为什么是这个阈值 | |---|------|------|------|-----------------| | 1 | Threads_running | 窗口内任一样本 > CPU核数 × 2（你的场景 > 32） | CPU 争抢型 | 16C 上 32 个活跃线程意味着至少一半线程在等 CPU；Threads_connected 可能 50 但 running 远低于 32 是正常排队 | | 2 | Innodb_row_lock_current_waits | 窗口内连续 ≥ 2 个样本 > 0 | 行锁排队型 | 单点瞬时 >0 可能是正常 lock 竞争；连续 2s 以上说明有事务持锁不放 | | 3 | Innodb_buffer_pool_reads / read_requests（窗口内增量比） | 窗口内物理读增量 > 逻辑读增量的 0.5% | Buffer Pool 冲刷型 | 稳态下这个比值 < 0.01%；0.5% 意味着 ~5000 个 page 被换出，对 6000 万行表的点查影响立竿见影 | | 4 | Innodb_log_waits | 窗口内增量 > 0 | Redo 刷盘瓶颈 | 正常情况下这个值永远不增长；一旦增长说明 redo log 写盘速度跟不上事务提交速度 | | 5 | cpu_steal_pct（OS 侧） | 窗口内任一样本 > 5% | 宿主机超卖型 | 5% steal 意味着 1/20 的 CPU 时间被 hypervisor 拿走，对 P99 影响直接 |

如果 5 条都没命中，再看 I/O：

• io_util_pct > 90% 或 io_await_ms > 10ms（SATA SSD）/ > 3ms（NVMe）→ 磁盘 I/O 瓶颈

如果以上全没命中，大概率是 SQL 本身的 P99 尾巴（如锁等待时间极短但存在、或 buffer pool 内部 page cleaner 刷脏的毛刺），此时需要看 events_statements_history_long 里该条 SQL 的 SUM_TIMER_SPIN_ORTH_WAIT 和 SUM_TIMER_FILE_WAIT 拆分。

阈值怎么定的（方法论）

• 不要用绝对值（如 Threads_running > 50），因为不同 QPS 水平下正常值不同。
• 用「相对基线」+「业务影响拐点」：先跑 7 天稳态数据，取 P99 作为基线；然后人为制造压力（sysbench oltp_read_write 加负载），找到「P99 开始显著劣化」时各指标的拐点值，取拐点值的 80% 作为告警阈值（留 20% 提前量）。
• Innodb_row_lock_current_waits 连续 2 样本 > 0 这个规则来自 InnoDB 锁等待的典型模式：正常 lock 竞争是微秒级（单个样本可能就 >0 然后立即归零），真正的排队是持续多秒。

三、最小落地清单（不想动 P_S 的情况）

如果 2% CPU 预算都嫌紧，最低配置：

1. 1s 采样脚本（上面那个 Bash），只采 6 个指标：Threads_running、Innodb_row_lock_current_waits、Innodb_buffer_pool_reads/read_requests（增量比）、Innodb_log_waits、CPU steal、I/O await
2. SHOW ENGINE INNODB STATUS 每 5s 采一次（解析 history list length + transaction 列表），写本地文件
3. 慢日志 JSON 格式（log_output=FILE + slow_query_log_json=ON，8.0 支持），确保有 start_time 毫秒戳
4. 一个 Python 脚本做 JOIN 和规则判定，输出到 Grafana 或本地 CSV

这套方案 零 P_S 开销（只读 SHOW GLOBAL STATUS），CPU 增量 < 0.1%，但代价是拿不到单条 SQL 的锁等待 / IO 时间拆分（没有 SUM_LOCK_WAIT / SUM_TIMER_FILE_WAIT）。

四、边界条件与失效场景

| 场景 | 为什么失效 | 兜底方案 | |------|-----------|---------| | 云盘 I/O 突发限速（阿里云 ESSD / AWS EBS） | iostat 看到的 %util 可能正常（因为云盘是分布式存储，没有传统意义的 "util"），但延迟会 spike | 看云监控的 IOPS 突发额度 和 延迟 P99；或 pidstat -d 1 -p <mysqld_pid> 看 wait 时间 | | Cgroup CPU 超卖（K8s 场景） | /proc/stat 的 steal 是 0（因为不是 hypervisor 层面的抢占，是 cgroup cfs 带宽限制） | 看 cgroup/cpu.stat 的 nr_throttled / throttled_time；或 pidstat -u 1 看 %usr + %system 是否远超 cpu.cfs_quota_us / cpu.cfs_period_us | | 多主 / 半同步复制 | 主库写入慢是因为等从库 ACK，但主库 CPU / I/O / 锁都正常 | 看 rpl_semi_sync_master_avg_wait_time（8.0 半同步）；或 SHOW SLAVE STATUS 的 Seconds_Behind_Master 突变 | | JVM / 应用侧 GC | DB 侧所有指标都正常，但请求到 DB 的时间被应用侧 GC 拉长 | 这类不是 DB 归因问题；需要应用侧 APM（你已有 HikariCP pending 监控，确认 pending=0 即可排除） | | Buffer Pool 内部 page cleaner 刷脏（8.0 新架构） | 刷脏是后台线程，不体现在 Innodb_buffer_pool_reads（那是读 miss），但会抢 I/O 带宽 | 看 Innodb_buffer_pool_pages_dirty 的增速 + iostat 的 write await；或 P_S 的 wait/io/file/innodb/ib_logfile 事件 |

五、一个容易踩的坑

performance_schema.events_statements_history_long 是按 thread 分片的（每个 thread 有自己的环形 buffer，events_statements_history_long 是全局的）。如果你开了 200k 行，800QPS 下大约 250 秒就会覆盖一轮（800 × 250 = 200k）。这意味着：

• 抖动窗口内的事件大概率还在 buffer 里（因为抖动窗口只有 2–8s），这个没问题；
• 但如果你事后 5 分钟才去查，可能已经被冲掉了。

建议：写一个 1s 的守护脚本，定期把 events_statements_history_long 中 SUM_TIMER_WAIT > 500ms（你的慢阈值）的行 dump 到本地表 / 文件，确保不丢。

-- 每 5s 跑一次，dump 慢语句到本地
INSERT INTO slow_stmt_dump (
  ts, thread_id, query, sum_timer_wait, sum_lock_wait, 
  sum_timer_file_wait, sum_timer_spin_orth_wait, sum_rows_examined
)
SELECT 
  NOW(), THREAD_ID, 
  LEFT(SUBSTRING_INDEX(sql_text, '\n', 1), 500),
  SUM_TIMER_WAIT/1000000000.0,
  SUM_LOCK_WAIT/1000000000.0,
  SUM_TIMER_FILE_WAIT/1000000000.0,
  SUM_TIMER_SPIN_ORTH_WAIT/1000000000.0,
  SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_history_long
WHERE SUM_TIMER_WAIT > 500000000000  -- 500ms in ps
  AND (ts IS NULL OR ts < NOW() - INTERVAL 5 SECOND);  -- 避免重复

这样即使 P_S buffer 被冲掉，你也有本地留证。

六、落地顺序建议

1. 第 1 天：开 1s 采样脚本 + 慢日志 JSON 格式 → 开始积累数据
2. 第 3 天：开 events_statements_history_long（200k）+ dump 脚本 → 补齐单条 SQL 维度
3. 第 7 天：用积累的数据跑一遍规则判定，校准阈值（把误报率压到 < 10%）
4. 第 14 天：Grafana 看板上线，慢查询归因看板（慢查询时间轴 + 同时刻 6 个指标 + 规则判定结果）

这套方案的核心思路是：不要试图在抖动发生时实时做决策，而是持续采集 + 事后对齐 + 规则判定。抖动窗口 2–8s，人反应不过来，但 1s 采样 + 自动规则可以覆盖。