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 。