12 | 为什么我的MySQL会“抖”一下？

---

MySQL45讲

实践篇

12 | 为什么我的MySQL会“抖”一下？

场景描述：一条 SQL 语句，正常执行的时候特别快，但是有时候会变得特别慢，而且很难复现，随机且持续时间很短。

SQL 语句为什么变“慢”了？

当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为 “脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为 “干净页”。

数据库“抖动”的原因：刷“脏页”（flush）。

场景1：redo log 满了

处理过程：系统停止所有更新操作，把 checkpoint 往前推进，把前后两点之间的“脏页” flush 到磁盘。

性能分析：
InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。

场景2：内存满了

处理过程：淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。（没有要读入的数据页时，清除该数据页空出内存；有要读入的数据页时，变成干净页后直接复用）

性能分析：
InnoDB 用缓冲池（buffer pool） 管理内存，缓冲池中的内存页有三种状态：

• 第一种是，还没有使用的；
• 第二种是，使用了并且是干净页；
• 第三种是，使用了并且是脏页。

InnoDB 的策略是尽量使用内存。当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

场景3：系统“空闲”的时候，见缝插针地刷“脏页”。

场景4：MySQL 正常关闭，会把内存的“脏页”都 flush 到磁盘上。

刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
• 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

InnoDB 刷脏页的控制策略

通过设置 innodb_io_capacity 参数告知 InnoDB 当前磁盘 IO 能力。这个值建议设置成磁盘的 IOPS。

磁盘的 IOPS 可以通过 fio 工具来测试，命令如下：

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

如果 innodb_io_capacity 设置太小，InnoDB 认为当前磁盘 IO 能力很差，所以刷“脏页”刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了“脏页”累积，影响了查询和更新性能。

磁盘能力不是只用来刷脏页，还需要服务用户请求。

控制刷“脏页”的速度，有哪些参考因素？

InnoDB 的刷盘速度要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

InnoDB 会根据这两个因素先单独算出两个数字，取其中较大的值记为 R，然后按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字，计算这个数字的伪代码类似这样：

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N)（N 越大，F2(N)越大）。

MySQL 刷“脏页”有“连坐”机制。 在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉，而且还可以继续蔓延。

在 InnoDB 中，innodb_flush_neighbors 参数用来控制“连坐”机制。值为 1 时会有“连坐”机制，值为 0 时表示只刷自己的。

如果使用的是 SSD 这类 IOPS 比较高的设备，建议把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS（每秒的输入输出量） 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值为 0 。