12 | 为什么我的MySQL会“抖”一下?
SQL语句为什么变“慢”了
- InnoDB在处理更新语句的时候,只做了写日志这一个磁盘操作。这个日志叫作redo log(重做日志)
- 当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。
- 内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。
刷脏页(flush)
平时执行很快的更新操作,其实就是在写内存和日志,而 MySQL偶尔“抖”一下的那个瞬间,可能就是在刷脏页(flush)
什么情况会引发数据库的 flush 过程呢?
- InnoDB的redo log写满了。这时候系统会停止所有更新操作,把 checkpoint往前推进,redo log留出空间可以继续写。
- 系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰 一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘。
- MySQL认为系统“空闲”的时候。 只要有机会就刷一点“脏页”。
- MySQL正常关闭的情况。这时候,MySQL会把内存的脏页都flush到磁盘上,这样下次MySQL启动的时候,就可以直接从磁盘上读数据,启动速度会很快。
上面四种场景对性能的影响
第三种情况是属于MySQL空闲时的操作,这时系统没什么压力,而第四种场景是数据库 本来就要关闭了。这两种情况下,你不会太关注“性能”问题。所以这里,我们主要来分析一下前 两种场景下的性能。
-
第一种是“redo log写满了,要flush脏页”,这种情况是InnoDB要尽量避免的。出现这种情况 的时候,整个系统就不能再接受更新了,所有的更新都必须堵住。如果你从监控上看,这时候更 新数会跌为0。
-
第二种是“内存不够用了,要先将脏页写到磁盘”,这种情况其实是常态。InnoDB用缓冲池 (buffer pool)管理内存,缓冲池中的内存页有三种状态:没有使用的;使用且是干净页;使用且是脏页。
-
InnoDB的策略是尽量使用内存,因此对于一个长时间运行的库来说,未被使用的页面很少。
当要读入的数据页没有在内存的时候,就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:(1)要淘汰的是一个干净页,就直接释放出来复用;(2)是 脏页,就必须将脏页先刷到磁盘,变成干净页后才能复用。
- 所以,刷脏页虽然是常态,但是出现以下这两种情况,都是会明显影响性能的:
- 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
- 日志写满,更新全部堵住,写性能跌为0,这种情况对敏感业务来说,是不能接受的。
InnoDB刷脏页的控制策略
- 正确地告诉InnoDB所在主机的IO能力, 用到
innodb_io_capacity
这个参数, 它会告诉InnoDB的磁盘能力。这个值建议设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试。
InnoDB的刷盘速度就是要参考这两个因素:一个是脏页比例,一个是redo log写盘速度。
- 参数
innodb_max_dirty_pages_pct
是脏页比例上限,默认值是75%。InnoDB会根据当前的脏页 比例(假设为M),算出一个范围在0到100之间的数字 F1(M)。 - InnoDB每次写入的日志都有一个序号,当前写入的序号跟checkpoint对应的序号之间的差值, 我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字,这个计算公式可以记为 F2(N)。
根据上述算得的F1(M)和F2(N)两个值,取其中较大的值记为R,之后引擎就可以按照innodb_io_capacity
定义的能力乘以R%来控制刷脏页的速度。
InnoDB会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。
所以,无论是查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句,都可能是造成你从业务端感知到MySQL“抖”了一下的原因。
-
就要合理地设置
innodb_io_capacity
的值,并且平时要多关注脏页比例,不要让它经常接近75%。脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
得到的。 -
在InnoDB中,
innodb_flush_neighbors
参数,值为1的时候会有“连坐”的机制,值为0时表示不找邻居,自己刷自己的。 -
在MySQL 8.0中,
innodb_flush_neighbors
参数的默认值已经是0了。
小结
- 刷脏页操作 和 执行时机
- 脏页会被后台线程自动flush,也会由于数据页淘汰而触发flush
- 刷脏页的过程由于会占用资源,可能会让更新和查询语句的响应时间长一些
- 控制刷脏页的方法和对应的监控方式