mysql 抖动

sql 为什么变慢了

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志），也就是《孔乙己》里咸亨酒店掌柜用来记账的粉板，在更新内存写完 redo log 后，就返回给客户端，本次更新成功。

做下类比的话，掌柜记账的账本是数据文件，记账用的粉板是日志文件（redo log），掌柜的记忆就是内存。

掌柜总要找时间把账本更新一下，这对应的就是把内存里的数据写入磁盘的过程，术语就是 flush。在这个 flush 操作执行之前，孔乙己的赊账总额，其实跟掌柜手中账本里面的记录 是不一致的。因为孔乙己今天的赊账金额还只在粉板上，而账本里的记录(磁盘)是老的，还没把今天的赊账算进去。当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写 入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。不论是脏页还是干净页，都在内存中。在这个例子里，内存对应的就是掌柜的记忆。

平时执行很快的更新操作，其实就是在写内存和日志， 而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

什么情况会引入刷脏页呢？

第一种场景 是就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作， 把 checkpoint 往前推进，redo log 留出空间可以继续写。

第二种场景就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘 汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到 磁盘。

第三种场景是MySQL 认为系统“空闲”的时候。当然，MySQL“这家酒 店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要合理地安排时间，即使 是“生意好”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

第四种情况就是 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度 会很快。

下面主要讨论第一种 第二种的性能问题

第一种是redo log 写满了，要flush脏页，这种情况是 InnoDB 要尽量避免的。因为 出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监 控上看，这时候更新数会跌为 0。

第二种是内存不够用了，要先将脏页写到磁盘，InnoDB用缓冲池来管理内存，缓冲池中的内存页有三种状态：

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

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很 少。

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

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

  1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；

  2. 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略

首先，要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 主机的磁盘能力。这个值建议设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是用来测试磁盘随机读写的命令：

1
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 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写 盘速度。InnoDB 会根据这两个因素先单独算出两个数字。参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当 前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字。InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的 差值，我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个 计算公式可以记为 F2(N)。F2(N) 算法比较复杂，你只要知道 N 越大，算出来的值越大就 好了。

然后，根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以 按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

总结

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以， 无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

有趣的策略

在准备刷一个脏页的时候，如果 这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个 把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数 据页也还是脏页的话，也会被放到一起刷。在8.0中，这个功能的默认值是关闭的。