首先要知道抖动是指什么？就是一条SQL语句，正常执行的时候很快，但是有时不知道怎么回事就会变得非常慢，并且这样的场景很难浮现，不仅随机，而且持续时间还短。

接下来便是分析原因了

SQL语句为什么变慢了

InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作，也就是redo log，更新内存写完redo log之后，就可以返回客户端了，更新就成功了。

内存中的数据总是要同步到磁盘上的，在同步之前，内存数据页与磁盘数据页内容不一致的时候，这个内存页我们称之为“脏页”，写入磁盘之后，两只保持一致了，成为“干净页”。

所以回到开头的问题，其实就不难猜到，平时执行很快的更新操作，其实就是在写内存和日志，而mysql偶尔抖动的瞬间，可能就是在刷脏页。

那么接下来我们就要看看什么情况会引发数据库的flush过程。

flush的触发场景

第一种场景就是redo log写满了，系统会停止所有更新操作将checkpoint往前推进，以留出空间继续写redo log。要把checkpoint从cp推进到cp’，就需要将它们之间的所有脏页都flush到磁盘上，然后空出的绿色部分就可以继续写redo log了。

第二种场景是系统内存不足，需要加载新的内存页的时候就要淘汰一些数据页，而淘汰的是脏页的话就要先将脏页写到磁盘上。之所以刷脏页一定要写磁盘是出于性能考虑的，如果直接将内存淘汰掉，那么下次从磁盘中读取页之后还要用redo log判断这个页是否有更新。而刷脏页写盘的话，就可以保证：

内存中存在的就是正确结果
内存中不存在，那么磁盘中的就是正确的结果。这样从内存中加载数据页不需要额外的处理就能够直接读取数据。

第三种情况是，mysql”空闲“时候，就会刷脏页。

第四种场景是，mysql正常关闭的时候，会先把内存中的脏页都flush到磁盘上。

flush对性能的影响

分别对前面介绍的四种场景的影响。

第一种场景是redo log写满了，要flush脏页。这是Innodb要尽量避免的，因为出现这种情况，整个系统就不能接受更新了，所有的更新都必须堵住。

第二种场景是内存不够用了，要将脏页写到磁盘。这种情况其实是常态，Innodb用缓存池管理内存，缓存池中的内存页有三种状态：

还没有使用的
使用了但是干净页的
使用了并且是脏页的。 InnoDB的策略是尽量使用内存，当要读入的数据页没有在内存的时候，就必须从缓存池中申请一个数据页，内存不足的时候就需要淘汰掉旧的内存页，并且这个旧的页如果是脏页的话，就必须先刷到磁盘中。虽然刷脏页是常态，但是出现下面的情况，会明显影响性能：

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

第三种情况下空闲情况下系统是没有什么压力的，至于第四种情况关闭的时候也不会关注性能。

所以InnoDB需要有控制脏页比例的机制，来避免第一和第二两种场景的出现。

InnoDB刷脏页的控制策略

设置正确的磁盘IO能力

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

可以通过fio这个工具来测试磁盘的IOPS，下面是用来测试磁盘随机读写能力的命令：

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

如果磁盘是一个SSD盘，但是innodb_io_capacity的值设置的是300，导致innodb认为这个系统的能力很差，所以刷脏页的速度特别慢，甚至比脏页生成的速度还慢，这就导致了脏页累计，影响了查询和更新性能。

典型现象就是，数据库主机的IO压力不大，但是Mysql的写入速度非常慢。

不过虽然我们告诉了innodb全力刷脏页的能力，但是innodb总不可能一直全力刷脏页，接下来就看一下，innodb如果控制引擎按照全力的百分比来刷脏页。

刷脏页速度的控制参数

脏页刷的过慢就会导致内存中存在大量的脏页和redo log被写满的情况出现，对于mysql的读写性能都会有较大的影响。

因此最为关键的两个因素就是：脏页的比例和redo log写盘速度。

参数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越大，算出来的值越大就好了。

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

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

要合理设置innodb_io_capacity的值，并多多关注脏页的比例，不要让它经常接近75%。脏页比例可以通过下面的语句获得：

user performance_schema;
 
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

还有一个有趣的连坐策略。如果一个查询语句在执行的过程中需要先flush一个脏页，而这个脏页的”邻居“刚好也是脏页，那么它就可以连带着邻居一起刷掉，并且这个”邻居“的逻辑还可以蔓延。这个机制在机械硬盘时代非常有意义，可以减少很多随机IO，但是可能造成查询变慢。

这个参数可以通过innodb_flush_neighbors参数来控制，1表示开启”连坐机制“，0表示不找邻居，自己刷自己的。使用的如果是SSD这类IOPS比较高的设备，还是将这个参数设置为0，这样可以更快完成必要的刷脏页的操作，减少SQL语句的响应时间。8.0版本中，这个参数的默认值已经是0了。

Smarticen Notes

Explorer

mysql抖动问题