云数据库 MySQL 切主后 QPS 从 12k 掉到 4k:`ib_buffer_pool` 压根没生成过,一份预热脚本加 5 条参数修正
先说结论:云托管 MySQL 的主备切换,走的是 VIP 漂移加旧主进程被 OOM killer / HA agent 直接干掉,不是 shutdown。所以 innodb_buffer_pool_dump_at_shutdown 这个参数在主备切换场景里形同虚设——ib_buffer_pool 文件要么是上一次控制台重启留下的旧货,要么干脆不存在。新主起来时 buffer pool 是一片空白,你监控上的命中率 5 分钟均值还在 99%,但秒级毛刺已经把连接池打穿了。
这篇把定位过程和可落地的预热器写完,全是能直接抄的 SQL 和脚本。
一、现场:慢日志里全是单行查询
典型的求救信号是这样的:切换完成后 3 分钟内,QPS 从 12k 掉到 4k,30 秒后缓慢爬升,大约 6~10 分钟才恢复。翻慢日志,罪魁祸首是一堆 Rows_examined=1 的主键点查,SELECT ... WHERE id = ?,执行计划完美,就是用不上索引——因为这本来就是主键查。
单行主键查询进慢日志,只有一种可能:回表走了物理读。buffer pool 里没这个页,云盘 IOPS 再高也扛不住 12k QPS 全变成随机读。
二、三条 SQL 定性,不用登机器
云数据库不给你 shell,别指望 ls -l $datadir/ib_buffer_pool。用状态变量判断:
-- 1. 本次启动到底有没有发生过 load?
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status';
-- 输出 'Buffer pool(s) load completed x%' 才算 load 过
-- 输出空 / 'not started' 说明新主是裸启动
-- 2. 物理读是不是真的在涨(间隔 10 秒取两次差值)
SHOW GLOBAL STATUS WHERE Variable_name IN (
'Innodb_buffer_pool_reads', -- 物理读次数
'Innodb_buffer_pool_read_requests',-- 逻辑读次数
'Innodb_buffer_pool_pages_free',
'Innodb_buffer_pool_pages_dump', -- dump 出去的页数
'Innodb_buffer_pool_pages_load' -- load 进来的页数
);
-- 3. dump 相关参数的真实取值(别信参数模板上的显示)
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME LIKE 'innodb_buffer_pool_dump%'
OR VARIABLE_NAME LIKE 'innodb_buffer_pool_load%';
如果 Innodb_buffer_pool_pages_dump 是 0、Innodb_buffer_pool_pages_load 也是 0,基本可以结案了。顺手对比一下:控制台点"重启实例",dump 和 load 都是正常执行的(走 mysqld 正常 shutdown 路径),只有 HA 切换不走。这个对比可以当成确认手段,一次就够。
三、为什么"把 dump_pct 调大"救不了你
- dump_at_shutdown 是摆设:HA 切换不触发 shutdown handler,页根本没落盘。云厂商的切换逻辑是探活失败 → 提升备库 → VIP 漂移 → 旧主被 kill,全程没有一次干净的
mysqladmin shutdown。 - dump_pct 默认 25:就算你手动触发了 dump,也只保存 LRU 里 25% 的页,剩下 75% 一样冷。
- 备库的 buffer pool 是"回放热",不是"读热":这是最容易被忽略的一点。备库上跑的是 relay log 顺序回放,buffer pool 里堆的是写操作碰过的聚簇索引页和 undo 页;主库上真正热的是一堆二级索引的随机读页。备库升主,load 出来的那份 dump 跟业务访问模式对不上号,命中率照样掉。
结论:托管环境里,唯一的解是切主后自己灌一遍。别指望参数。
四、预热器:从热点表清单到并行扫描
4.1 热点表清单必须在切主前采集
坑在这里:performance_schema 的数据是实例级的,新主起来是一片空表,切完再去查 table_io_waits_summary_by_table 什么都拿不到。所以这个清单要定时采集、落到独立的小库里。
-- 在现主库上跑,按物理读(COUNT_FETCH)排序
SELECT OBJECT_SCHEMA AS db,
OBJECT_NAME AS tbl,
COUNT_FETCH AS phys_fetch,
COUNT_READ AS logical_read,
ROUND(COUNT_FETCH * 100.0 / SUM(COUNT_FETCH) OVER (), 2) AS pct
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY COUNT_FETCH DESC
LIMIT 50;
先确认 instrument 没被云厂商裁掉,不然上一条永远返回空:
SELECT NAME, ENABLED
FROM performance_schema.setup_instruments
WHERE NAME = 'wait/io/table/sql/handler';
-- ENABLED 必须是 YES;如果是 NO,用 pt-query-digest 从慢日志里扒表名兜底
4.2 预热的坑:别让代理把 COUNT 路由走
这条最阴。你的连接串如果指向读写分离代理地址,SELECT COUNT(*) 这种"明显慢"的语句会被代理判定并转发到只读实例,主库一页都没加热,脚本跑完了命中率纹丝不动,你还以为预热无效。预热脚本必须用主库直连地址,绕开 proxy。
4.3 用并行读把预热速度拉起来
MySQL 8.0.14 起有 innodb_parallel_read_threads,只对无 WHERE、无聚合的 SELECT COUNT(*) 全表扫描生效——正好是预热场景。单线程扫 200G 要 10 分钟,8 并行能压到 90 秒内。
SET SESSION innodb_parallel_read_threads = 8;
SELECT COUNT(*) FROM orders FORCE INDEX(PRIMARY);
两点必须注意:
COUNT(*)默认会挑最小的二级索引扫,聚簇索引一页不动。必须加FORCE INDEX(PRIMARY)。- 业务里大量走二级索引点查的表,只热聚簇索引不够。给每个高频二级索引也补一条
SELECT COUNT(*) FROM t FORCE INDEX(idx_name),扫的是该索引自己的叶子页。
4.4 完整脚本
#!/usr/bin/env bash
# prewarm.sh - 切主后预热,必须连主库直连地址
set -euo pipefail
CNF=/etc/mysql/prewarm.cnf # 内含 host=主库直连, user, password
LIST=/tmp/prewarm.list
MYSQL="mysql --defaults-file=${CNF} -N -B"
# 清单格式: db \t tbl \t index_name(PRIMARY 或二级索引名)
# 由切主前的采集任务生成并同步过来
echo "[prewarm] start $(date -Is)"
while IFS=$'\t' read -r db tbl idx; do
[[ -z "${db}" ]] && continue
echo "[prewarm] ${db}.${tbl} via ${idx}"
${MYSQL} -D "${db}" -e "
SET SESSION innodb_parallel_read_threads = 8;
SELECT COUNT(*) FROM \`${tbl}\` FORCE INDEX(\`${idx}\`);
" > /dev/null
sleep 0.2 # 留出 purge 喘息窗口
done < "${LIST}"
echo "[prewarm] done $(date -Is)"
清单里的顺序按热度从高到低,别乱排:buffer pool 是 LRU,先灌最热的表,就算后面灌到一半被业务新流量挤掉,剩下的也是冷表,损失最小。
4.5 并行扫描的副作用
预热是顺序大扫,会立刻推高 History list length(长事务让 purge 追不上 undo),同时吃掉云盘突发 IOPS 的 credit 池。预热完成后 10 分钟内,业务写入的 p99 延迟可能反而抖一下。控制手段:
-- 观察 undo 堆积,超过阈值就暂停预热
SHOW ENGINE INNODB STATUS\G -- 看 History list length
-- 或者
SELECT COUNT(*) FROM information_schema.INNODB_TRX WHERE trx_state = 'RUNNING';
脚本里 sleep 0.2 就是给 purge 线程留窗口,大表(>100G)建议改成每扫完一张 sleep 2。
五、5 条参数修正(含托管环境改不动的)
5.1 innodb_buffer_pool_chunk_size 整除校验
改 innodb_buffer_pool_size 不生效,十有八九是被 chunk 整除规则吃掉了。MySQL 会把你填的值向上取整到 chunk_size × instances 的整数倍,然后静默接受。查一下真实值:
SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS real_gb,
@@innodb_buffer_pool_chunk_size/1024/1024 AS chunk_mb,
@@innodb_buffer_pool_instances AS inst,
@@innodb_buffer_pool_size
% (@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances) AS remainder;
-- remainder 不为 0 说明被取整过
5.2 dump 策略改成"定时快照"
SET GLOBAL innodb_buffer_pool_dump_pct = 100;
SET GLOBAL innodb_buffer_pool_dump_now = ON; -- 手动触发,不走 shutdown 也能落盘
SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_dump_status';
用 cron 每 10 分钟 dump_now 一次,让 ib_buffer_pool 保持新鲜。这对控制台重启有效(重启走 load_at_startup),对 HA 切换无效——但至少能覆盖掉一部分场景。
5.3 innodb_old_blocks_time
默认 1000ms。预热扫描会把页塞进 old sublist,如果业务流量在 1 秒内二次访问同一页,页才会被提升到 young。批量预热时这个值保持默认就行,但如果你把预热脚本改成高频小批量重跑,反而会因为页反复进出 old 区造成额外 latch 争用。一次干净的大扫比三次零碎的重扫好。
5.4 innodb_adaptive_hash_index
切主后 AHI 也是空的,需要重新构建,期间 rw-latch 等待会明显上升。观察:
SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%';
-- 关注 hash_searches / latch_waits
高并发点查场景,如果 AHI 重建期 latch 争用严重,可以考虑 innodb_adaptive_hash_index=OFF,交给业务侧的连接池和本地缓存兜。
5.5 连接池侧的超时要对齐
切主后旧连接被重置,应用侧 aborted_clients 会跳。检查:
SHOW GLOBAL STATUS LIKE 'Aborted_clients';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
连接池的 maxLifetime 必须小于云数据库代理的空闲断连时间,否则切主时会一口气吐出几百个 dead connection,把冷启动的窗口从 6 分钟拉长到 15 分钟。
六、验证:别用平均值骗自己
云监控上的 BufferPoolHitRate 是 5 分钟均值,冷启动那 90 秒的毛刺在曲线上看不出来。用 sysbench 打一轮对比:
sysbench oltp_point_select \
--mysql-host=主库直连地址 --mysql-db=sbtest \
--tables=16 --table-size=10000000 \
--threads=64 --time=120 --report-interval=5 run
预热前后各跑一次,看 95th percentile 和 tps 恢复曲线。我们这边典型数据:不预热时切主后 95th 从 1.2ms 飙到 38ms,持续约 6 分钟;预热(并行 8,约 90 秒跑完)后 95th 最高到 9ms,40 秒内回到基线。
顺便说一句,规格本身的 buffer pool 上限是硬约束。我们有几个 64G 规格的实例,把业务表总量灌进去之后 hit rate 也就 95% 封顶,切主窗口再优化也压不下去。后来把最吃内存的几个库迁到轻云互联的独享型规格上,buffer pool 直接给到 96G,一次预热就能吃下全部热数据,那个切换窗口从 90 秒压到 20 秒出头——不是脚本变强了,是页终于装得下了。
七、踩坑清单
- 预热清单必须切主前采集,切完再查 performance_schema 是空的。
- 预热连接必须绕过读写分离代理,否则 COUNT 全打到只读实例。
SELECT COUNT(*) FORCE INDEX(PRIMARY)里FORCE INDEX不能省,否则走最小二级索引,聚簇索引一页不热。- 大表预热前先看
History list length,undo 堆积会把写入延迟顶上去。 innodb_buffer_pool_size修改后被 chunk 规则静默取整,部署前先算 remainder。- 控制台重启会执行 dump/load,HA 切换不会——两者别混为一谈。
- 连接池
maxLifetime不跟代理的空闲断连对齐,切主窗口会被 dead connection 拖长。