Sql语句执行偶尔抖动问题分析

1、背景原因

这是在公司实习时，对Mysql数据库进行操作时，偶然间发现突然一条SQL操作变慢，由此引发的思考，特此学习记录

2、原因分析

​ 在正常的Sql更新的流程是，数据更新的操作会先记录到redo log 里，然后再等mysql空闲时flush到磁盘上，在更新内存写完 redo log 后，就返回给客户端，本次更新成功。

​ 但是在这个操作中，肯定就会产生内存和磁盘上的数据不一致的数据，那么这种一致的内存页我们称作干净页，不一致的称作脏页

有了这个前提，其实不难想象，你的SQL执行过程中抖动，很有可能就是遇上刷脏页了

3、四个刷脏页的场景

1. redo log满了，记不下了需要刷脏页腾出空间
2. 系统内存不足，就要淘汰一些数据页，空出内存给别的数据页使用，如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3. MySQL 认为系统“空闲”的时候，会自动开启刷脏页
4. Mysql被关闭的时候

对于第一种情况，当redo log 写满的时候，系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。如图所示：

checkpoint 可不是随便往前修改一下位置就可以的。比如图 2 中，把 checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都 flush 到磁盘上。之后，图中从 write pos 到 CP’之间就是可以再写入的 redo log 的区域。

对于第二种情况，我们知道InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

• 还没使用的内存页
• 使用了并且是干净页
• 使用了并且是脏页

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

当读入的数据页没有在内存的时候，buffer pool就需要新申请一个数据页，这样就会将最近不是用的数据页淘汰掉，如果要淘汰的数据页是干净页，这里就会直接淘汰掉，如果是脏页，则需要flush后才会淘汰

4、刷脏页带来的影响

刷脏页虽然是常态，但是出现这两种情况会造成明显性能下降：

1. 淘汰脏页数过多，导致查询的响应时间明显变长
2. 日志写满，更新全部堵住，写性能跌为 0

因此，迫切需要一些控制刷脏页手段防止Mysql随意刷脏页

5、InnoDB 刷脏页的控制策略

InnoDB 引擎有脏页的控制策略，以及和这些策略相关的参数来防止mysql刷脏页过度。

既然你要控制InnoDB刷脏页速度，那么你就要告诉mysql你到底能刷多快，你的IOPS极限是多少。可以通过innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力

这里我们可以使用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

测试结果表明：

我的硬盘iops平均读为avg=41.99，写为avg=43.58

但是如果设置innodb_io_capacity参数导致性能下降的问题普遍存在:

如果参数设置的很小，这样就会导致刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。

如果你设置很大，就会导致刷脏页速度大于了你IOPS极限，这样会导致你磁盘IOPS一直被打满，甚至影响到其他业务运行

6、对于刷脏页速度的思考

这里试想刷脏页速度过慢，会出现什么？

​ 首先是内存脏页太多，其次是 redo log 写满。

所以我们需要思考两个因素：

​ 1、脏页比例
​ 2、 redo log 写盘速度

对于脏页比例innodb提供了一个参数可以定义脏页比例上限： innodb_max_dirty_pages_pct ，默认值是 75%

对于实际的脏页比例是通过以下参数算出来的

Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total

这里建议最好不要让脏页的比例接近75%

此外，mysql刷脏页还会有一个有趣的策略：

​ 一旦你在Mysql开始刷脏页是进行Sql操作，那么性能必定下降，但是Mysql还有一个能让你性能变得更差的机制，那就是在你刷脏页的时候，如果这个脏页旁边刚好也是脏页，那么就会把这个脏页也连同刷掉，这种操作可以持续蔓延。

始刷脏页是进行Sql操作，那么性能必定下降，但是Mysql还有一个能让你性能变得更差的机制，那就是在你刷脏页的时候，如果这个脏页旁边刚好也是脏页，那么就会把这个脏页也连同刷掉，这种操作可以持续蔓延。

在Innodb中innodb_flush_neighbors 参数就是用来控制这个行为,，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。这个操作其实在机械硬盘上是很有意义的，可以减少很多的随机IO,但是在SSD上面就没必要了，因此在上SSD的机器上IOPS往往不是瓶颈所在