1、背景原因
这是在公司实习时,对Mysql数据库进行操作时,偶然间发现突然一条SQL操作变慢,由此引发的思考,特此学习记录
2、原因分析
在正常的Sql更新的流程是,数据更新的操作会先记录到redo log 里,然后再等mysql空闲时flush到磁盘上,在更新内存写完 redo log 后,就返回给客户端,本次更新成功。
但是在这个操作中,肯定就会产生内存和磁盘上的数据不一致的数据,那么这种一致的内存页我们称作干净页,不一致的称作脏页
有了这个前提,其实不难想象,你的SQL执行过程中抖动,很有可能就是遇上刷脏页了
3、四个刷脏页的场景
- redo log满了,记不下了需要刷脏页腾出空间
- 系统内存不足,就要淘汰一些数据页,空出内存给别的数据页使用,如果淘汰的是“脏页”,就要先将脏页写到磁盘。
- MySQL 认为系统“空闲”的时候,会自动开启刷脏页
- 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、刷脏页带来的影响
刷脏页虽然是常态,但是出现这两种情况会造成明显性能下降:
- 淘汰脏页数过多,导致查询的响应时间明显变长
- 日志写满,更新全部堵住,写性能跌为 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往往不是瓶颈所在