无法复现的“慢”SQL《死磕MySQL系列 八》

@TOC
系列文章 四、S 锁与 X 锁的爱恨情仇《死磕MySQL系列 四》
五、如何选择普通索引和唯一索引《死磕MySQL系列 五》
六、五分钟,让你明白MySQL是怎么选择索引《死磕MySQL系列 六》
七、字符串可以这样加索引,你知吗?《死磕MySQL系列 七》
无法复现的“慢”SQL《死磕MySQL系列 八》
文章图片

项目中将MySQL的报错、异常、执行时间长的都打到了钉钉群中,这样有利于平时及时处理。今天要聊的是无法复现的慢查询。
一、为什会出现无法复现的“慢”SQL 在一生挚友redo log、binlog《死磕MySQL系列 二》中详细的说明了redo log、binlog。此时你知道了在更新时当事务提交后,并非直接修改数据库的数据,而是先更新内存并在 redo log中记录相关的操作。
总归是要把内存的数据刷入磁盘中,也可以称之为刷脏页(flush)。
什么是脏页、干净页
大多数资料都提及到脏页,那么脏页到底是什么呢?脏页时内存数据页的数据跟磁盘数据不一致时,就称这个内存页为脏页。
当内存页写入磁盘后,内存和磁盘的数据页就一致了,此时称这个内存页为干净页。
什么时候脏页会变为干净页
第一种
Innodb的redo log写满了,也就是下图的write pos 追上了check point了,此时系统所有的更新操作都会停止。
直至check point推进了,对应的脏页都flush到磁盘了,redo log才可以继续写。
一般情况下这个redo log日志在开发前期根据innodb_log_file_size参数设置好后就不会出现redo log写满的情况。
无法复现的“慢”SQL《死磕MySQL系列 八》
文章图片

第二种
内存不足导致,更新一条语句会先更新内存再更新到redo log,若内存不足就无法申请新的内存就需要淘汰一些数据页。就需要把脏页flush到磁盘。
有没有想过既然更新操作给内存和redo log都存了一份,那么能不能直接把内存页淘汰掉,再有请求时从磁盘读入数据页再把redo log拿出来应用不行吗?
内存满时不刷脏页而直接淘汰掉,那下次请求磁盘中的干净页到内存时,还需要额外的判断redo log中是否有对该页的修改,有的话还需要对它应用redo log。这个脏页始终都是要刷盘的,但现在缺额外多了应用redo log的操作。所以不能直接淘汰内存,而是内存满时直接flush。
另外,redo log是循环写的,若想应用redo log那么redo log就要一直存在,不能删除。违背了系统设计。
第三种
MySQL在系统低峰期时进行刷脏页
第四种
MySQL正常关闭时会把内存的脏页都刷到磁盘中,重启后从磁盘直接读数据,启动速度会很快。
结论
到这里你就应该明白,莫名其妙的慢SQL就是因为flush造成的,那么这四种情况都是怎么影响MySQL的呢?
二、四种flush对性能的影响 第三、四种情况不会因为flush而导致MySQL执行慢,一个是系统空闲时段,另一个是数据库本来就要关闭了。
redo log写满了,需要flush脏页
这种情况在第二期文章中就已经给了方案,redo log一旦写满整个系统就不再接受更新操作了, 所有的更新操作都得停滞,直到check point推进了。
扩展
在MySQL中提供了innodb_log_file_size参数来优化redo log日志。
对于innodb_log_file_size的设置也是有一些计算规则的,下面将为你介绍。
若innodb_log_file_size设置太小,将导致redo log文件频繁切换,频繁的触发数据库的检查点(check point),导致记录更新到数据文件的次数增加,从而影响IO性能。
同样,如果有一个大的事务,并且所有 redo log日志都已写满,但是还没有完成,将导致日志无法切换,从而导致 MySQL直接堵死。
【无法复现的“慢”SQL《死磕MySQL系列 八》】innodb_log_file_size设置太大,虽然极大地提高了 IO性能,但是在 MySQL重启或宕机时,恢复时间会因为 redo log文件过大而延长。而这种恢复时间通常是无法控制的。
如何合理的设置innodb_log_file_size?
用一个脚本定时执行,记录对应时间的sequenumber再取平均值,计算出的误差将减至最小。sequenumber是当每个 binlog生成时,该值从1开始,然后递增,每增加一个事务, sequenumber就加上1。
系统内存不足,要刷脏页
Innodb中管理内存的是buffer pool,内存页在上文可得知存在三种状态,未使用的、使用了是干净页、使用了是脏页。
对于一个长时间运行的库来说,未被使用的页非常少,当内存不足时,就只能把最久不使用的数据页从内存中淘汰掉。
若淘汰的是一个干净页,就直接释放使用,但如果是脏页就必须先把脏页刷盘,变为干净页进行复用。
查询的数据没有在内存中,就需要把数据从磁盘中读入数据,若读的数据太多就需要淘汰多个脏页,会导致查询时间边长。
redo log日志写满,所有的更新系统都不执行,对于大多数业务来说都不能接受。
为了防止这种情况的发生就需要控制刷脏页的频率。
三、如何设置刷脏页的速度 刷脏页到磁盘的快慢必定跟系统的IO能力有关,在MySQL中innodb_io_capacity是控制刷脏页的速度。
在从缓冲区刷新脏页时(check point),每秒刷新脏页的数量就等于innodb_io_capacity的值。
这个值可以设置成磁盘的IOPS,可以使用fio工具来测试,具体使用这里就不聊了。
刷脏页的速度也要根据脏页比例、redo log写盘速度来决定。
参数innodb_max_dirty_pages_pct是脏页比例上限,在MySQL8.0这个比例默认为90%,MySQL5.6还是75%。
一般情况下对于innodb_io_capacity的值设置为脏页比例上限与写redo log日志时的日志序号减去checkpoint的值,俩个值取最大的即可。
脏页比例的计算公式是Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total,具体执行命令为

mysql> 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;

在这个SQL语句中可以看到使用的是global_status这张表在performance_schema这个库里边。执行命令前需要执行use performance_schema。
当你的MySQL写入速度很慢,TPS很低,IO压力不大时需要排查的地方
出现这个问题时就考虑下一下innodb_io_capacity这个参数值设置是否合理。
在1核2G的服务器默认值是200,在公司服务器上看是2000,也是跟服务器配置有关系的。
四、有趣参数 在MySQL8.0中参数innodb_flush_neighbors默认值为0。
当一个查询需要在执行过程中先flush掉一个脏页时,如果这个数据页旁边的数据页刚好是脏页,就会把这个数据页一同刷掉,而这个连带的逻辑会持续下去。会使SQL的查询变的更慢。
坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见。

    推荐阅读