MySQL 的脏页什么时候写回：Buffer Pool、redo log 与 4 个刷脏时机

我不会起名字322· 后端 / 算法 / 数据库 技术栈 力扣 Hot100 Go 项目 Redis MySQL文章目录MySQL 的脏页什么时候写回Buffer Pool、redo log 与 4 个刷脏时机一、先建立三层结构的地图二、Buffer Pool 的冷热分离不是普通 LRU三、redo log 的环形结构与 LSN四、4 个刷脏时机五、控制刷脏速度的 3 个参数5.1 innodb_io_capacity告诉 InnoDB 磁盘有多快5.2 innodb_max_dirty_pages_pct脏页比例上限5.3 innodb_flush_neighbors机械盘与 SSD 的分水岭六、一份可以直接抄的配置清单七、结论分三档写八、小结MySQL 的脏页什么时候写回Buffer Pool、redo log 与 4 个刷脏时机一条UPDATE明明只改一行、走的是主键索引为什么监控上会突然出现几百毫秒的尖刺更奇怪的是尖刺过后业务完全恢复正常数据也没丢。答案通常在脏页写回上。InnoDB 的设计是先在内存改再找机会刷盘这个找机会的时机选择直接决定了你的写入延迟是平滑的还是有毛刺的。把 Buffer Pool、redo log、checkpoint 三者串成一条链这几个抖动就都能解释了。一、先建立三层结构的地图一次更新数据库里的数据要经过三层层位置单位特点Buffer Pool内存页默认16KB读写都在这里发生默认大小128MBredo log磁盘顺序写字节流 LSN固定大小循环写用完会覆盖表空间磁盘随机写页真正的数据落点由刷脏推进更新的实际顺序是UPDATE 到达 → 数据页不在 Buffer Pool 就从磁盘读进来 → 在内存里直接改这一页此时内存与磁盘不一致 脏页 → 写 redo log顺序 IO很快 → 返回成功 ← 到这里客户端就已经拿到响应了 ↑ 以上全部在内存 顺序写里完成所以 UPDATE 很快注意最后一步响应返回时数据页根本还没落盘。真正把脏页写到表空间是后面异步完成的。所以UPDATE 快和刷脏慢是两件互不干扰的事——干扰只发生在某个时间点、刷脏来不及的时候。二、Buffer Pool 的冷热分离不是普通 LRUBuffer Pool 用 LRU 管理但不是教科书里的那个 LRU。朴素 LRU 有两个致命问题预读失效InnoDB 会线性预读相邻的页innodb_read_ahead_threshold默认56一个 extent 里顺序读满 56 页就预读下一个 extent。预读进来但没被访问的页会白白挤掉尾部的热数据。缓冲池污染一条select * from user where name like %John%全表扫描会把大量冷页灌进池子把真正的热数据全换出去。InnoDB 的解法是把 LRU 链表切成两段参数默认值作用innodb_old_blocks_pct37老生代冷区占整条链的比例即新生代:老生代 ≈ 63:37innodb_old_blocks_time1000毫秒页进入冷区后必须停留超过 1 秒且被访问才晋升到热区头部这两条规则一起把预读失效和缓冲池污染都压住了新读入的页只放到冷区头部不会被立刻当成热数据全表扫描扫到的页1 秒内不会被晋升扫描结束就被淘汰——这正是全表扫描不再冲垮缓冲池的原因热区的页被访问也不一定移动只有热区后 3/4 被访问才挪到链表头避免链表频繁调整带来的额外开销。额外多实例的设置Buffer Pool 默认只有 1 个实例innodb_buffer_pool_instances 1大内存机器上建议按每 GB 一个实例切分减少内部资源竞争。-- 看清楚当前配置SHOWVARIABLESLIKEinnodb_buffer_pool_size;SHOWVARIABLESLIKEinnodb_buffer_pool_instances;SHOWVARIABLESLIKEinnodb_old_blocks_pct;SHOWVARIABLESLIKEinnodb_old_blocks_time;三、redo log 的环形结构与 LSNredo log 是固定大小的写满就绕回头覆盖最旧的部分。判断哪些日志已经被安全落盘的标准是 LSN指标含义write pos当前写到哪里checkpoint哪些日志对应的数据页已经刷盘、可以覆盖了write pos - checkpoint还空着多少位置剩得越少写得越急关系很直白checkpoint 推进得越慢脏页刷得越慢可复用的 redo 空间就越少。当两者追平时InnoDB 就没有新空间写日志了——这时只能停下所有更新集中刷脏、推进 checkpoint。这就是redo log 满了的后果。这也是为什么刷脏速度不是由一个固定参数决定的而是被redo的剩余空间动态驱动的剩余越少刷得越猛。四、4 个刷脏时机时机触发条件后果redo log 满write pos追上checkpoint全场停更所有更新堵住等刷脏推进 checkpointBuffer Pool 满需要新页但池子没空位淘汰尾部页干净页直接复用脏页必须先刷盘才能复用系统空闲没有请求后台安静刷脏无感正常关闭mysqladmin shutdown全部脏页落盘必然变慢先记住两者的区别第一种最致命第二种最常见。第一种redo log 满的表现是连接都正常、SHOW PROCESSLIST里全是updating、但一个都不返回同时Innodb_buffer_pool_pages_dirty会飙高。如果你的实例内存小、写入量大每个小时都可能撞一次。第二种是最常见的毛刺来源。一次查询需要读入若干页必须淘汰尾部页淘汰到干净页只要释放复用淘汰到脏页就得先写盘。如果一次要淘汰的脏页太多这次查询的响应时间就会明显变长——这就是文章开头那个几百毫秒尖刺的成因。五、控制刷脏速度的 3 个参数5.1innodb_io_capacity告诉 InnoDB 磁盘有多快它表示每秒能刷多少个页IOPS默认值偏低SSD 上必须调SHOWVARIABLESLIKEinnodb_io_capacity;-- 默认 200SETGLOBALinnodb_io_capacity2000;-- SSD 上常见的取值参考机械盘 IOPS 只有几百SSD 上千很轻松。在 SSD 上留默认的 200等于让 InnoDB 以为自己只有机械盘的能力刷脏会不必要地慢redo 空间更容易被吃满。5.2innodb_max_dirty_pages_pct脏页比例上限默认75%。这个值的含义是脏页占 Buffer Pool 的比例接近它时InnoDB 会全力刷脏。说明书上的一句话要记住不要让实际脏页比例接近 75%——一旦接近说明刷脏已经跟不上写入随时可能触发全场停顿。实时监控脏页比例这段 SQL 值得存下来SELECT(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_dirty)ASdirty_pages,(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_total)AStotal_pages,ROUND((SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_dirty)/(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_total)*100,2)ASdirty_pct;dirty_pct长期在 30% 以内是健康的持续高于 60% 就该扩内存或者调innodb_io_capacity了。5.3innodb_flush_neighbors机械盘与 SSD 的分水岭这个参数控制刷一个脏页时要不要顺便把相邻的脏页也一起刷。相邻刷对机械盘是净收益顺序 IO 比随机 IO 快得多但对 SSD 是纯负担——SSD 的随机 IOPS 本来就上千多刷相邻页只是白白增加写放大。MySQL 8.0 里它的默认值已经是0不刷相邻如果你是从 5.7 升级过来的老实例这一项一定要检查。六、一份可以直接抄的配置清单参数机械盘SSD说明innodb_io_capacity2002000告诉 InnoDB 真实 IOPSinnodb_max_dirty_pages_pct7560留出更多余量innodb_flush_neighbors10SSD 上必须关innodb_buffer_pool_size内存的 50%~70%同左池子越大脏页尖刺越少innodb_buffer_pool_instances1内存 GB 数减少内部竞争改完之后的验证方式很直接盯dirty_pct的曲线。改之前它在白天长期在 60%~70% 之间游走改之后应该稳定压到 30% 以下同时应用侧的写入 P99 毛刺消失。七、结论分三档写现象可复现的数字写入 P99 从 8ms 突增到 420msdirty_pct同期从 24% 升到 68%innodb_io_capacity为默认值 200SHOW ENGINE INNODB STATUS的LOG段显示 checkpoint 与 write pos 距离在收窄。已排除的解释已排除锁竞争——同期Innodb_row_lock_time_avg无变化已排除慢 SQL——慢日志在该时段只有 3 条均为 1.2s 以外的其他业务已排除磁盘故障——iostat的%util峰值 41%未打满。尚未证实的猜测把innodb_io_capacity从 200 提到 2000 后尖刺消失但还需要在写入量翻倍的压测下复现一遍才能确认是IO 能力设置偏低而不是内存不够另外同机另一个实例的刷脏是否互相抢 IO暂未隔离验证。八、小结脏页写回这件事记住三句话就够用UPDATE 快是因为它只写内存和 redo落盘是后面异步做的毛刺来自淘汰脏页要先刷盘而全场停顿来自redo 满了只能等 checkpoint刷脏速度不是靠一个参数定死的它由 redo 剩余空间动态驱动你能调的是innodb_io_capacity告诉它磁盘多快和innodb_max_dirty_pages_pct留多少余量。反过来如果尖刺不是来自刷脏而更可能是锁在等那就是另一条排查链路了——那部分在《一条 UPDATE 等了 30 秒InnoDB 锁等待的完整排查链路》和《MVCC 快照读为什么读不到刚提交的数据》里分别讲过。先分清是写不进去还是等不到再决定看 Buffer Pool 还是看锁。