MySQL抖动浅析
一、引言
平时的工作中，不知道你有没有遇到过这样的场景，一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短，这就像是数据库“抖”了一下。MySQL抖动指的是数据库性能出现不稳定的现象，表现为查询响应时间波动、系统负载突然增加、连接数激增等，抖动可能导致应用程序响应变慢、用户体验下降，甚至引发系统崩溃。导致数据库抖动最常见的原因就是InnoDB 刷脏页，接下来我们就看看这个问题是如何产生的，以及该如何解决这个问题。
二、InnoDB 刷脏页
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写 入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。通过前面的学习（MySQL缓冲池Buffer Pool）我们知道了，不论是脏页还是干净页，都在内存中，平时执行很快的更新操作，其实就是在写内存和日志， 而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。那么，什么情况会引发数据库的 flush 过程呢？
2.1 InnoDB 的 redo log 写满
导致我们刷脏页（flush）第一种情况就是InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作， 把 checkpoint 往前推进，redo log 留出空间可以继续写。
“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为 出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果此刻我们从监控上看，这时候更新数会跌为 0。
什么是Checkpoint？
Checkpoint（检查点）是InnoDB用于标记Redo Log中哪些修改已经被写入磁盘的机制。
它通过定期将脏页（Dirty Page）刷新到磁盘，减少崩溃恢复时需要重放Redo Log的量。
2.2 内存不够用
第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓存池（Buffer Pool）管理内存，缓冲池中的内存页有三种状态：
- 还没有使用的；
- 使用了并且是干净页；
- 使用了并且是脏页；
InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把 最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用； 但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
- 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
- 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的；
所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。
三、解决方案
知道了产生性能抖动的原因，那么该如何去解决呢？常见的方案有两种：
- 尽量减少缓存页flush到磁盘的频率
- 尽量提升缓存页flush到磁盘的速度
对于以上两种方案主要倾向于第二种，如果flush那批缓存页到磁盘需要1s，SQL自已查询需要200ms ，那么这条SQL执行完需要1.2s，因此要减少flush缓存页到磁盘的时间开销，这样我们对于数据库部署的机器一定要采用SSD固态硬盘，不采用机械硬盘，因为SSD固态硬盘的最大优点就是他的随机写IO性能非常高。知道了解决问题的方案，接下看看具体的操作。
3.1 InnoDB 刷脏页的控制策略
首先，我们要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏 页的时候，可以刷多快。 这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我 建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试。
此时我们试想一下如果我们来设计策略控制刷脏页的速度，会参考哪些因素呢？如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是 redo log 写满。
所以，InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写 盘速度。
InnoDB使用两个因素来进行计算刷盘速度。一个是脏页比例，一个是redo log的写盘速度。参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例M，算出一个范围在0到100之间的数字。InnoDB每次写入日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N，同样，InnoDB会根据这个N算出一个0到100之间的数字。N越大这个计算出来的值就越大。
F1(M)
{
if M>=innodb_max_dirty_pages_pct then
return 100;
return 100*M/innodb_max_dirty_pages_pct;
}然后，根据上述两个计算出的数据f(M)和f(N),取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度了。
因此要合理的设置innodb_io_capacity的值，平时要多关注脏页的比例，不要让它经常接近75%。
四、小结
在实际生产中，掌握如何解决数据库抖动是不可缺少的能力，本文介绍了常见的数据库抖动原因以及相应的解决方案。