为什么MySql有时候会变得非常慢
之前曾经介绍过,WAL机制,InnoDB在处理更新语句的时候,只会进行写日志, 也就是redo log
也就是 先记在redolog上,然后找一个合适的时间存取到磁盘中,这个操作称为flush操作
而且,当内存的数据页和磁盘的数据页内容不一致的时候,称这个内存页为脏页,内存数据写入到磁盘中,才可以称为干净页
简述一下整个的流程
对于MySql的卡顿问题,可能就是由于flush的原因
什么时候会触发flush的过程呢
最紧急的状态
1.如果redolog满了,首先记得 redolog是一个环形的结构,如果这个环形的结构满了,必须停下来所有的更新操作,将checkpoint进行刷新,然后redolog才能继续写
刷入了多少的数据,就可以往前推进多少的行数
2.就是频繁刷新,首先说,MySql会将读取出来的数据放在内存中,但是内存毕竟是一个有上限的空间,在内存不够的时候,就需要淘汰一些不常使用的内存页,如果淘汰的内存页上有刷进去的数据,就需要将脏页写到磁盘中
3.就是在Mysql很空闲的时候,认为是个刷脏页的好时机,就将脏页刷进去
4.MySql正常关闭的时候,为了保证数据不丢失,会将脏页刷到磁盘中
对于上面的四种情况,首先排除了3,4的分析过程,因为正常关闭时候是不会在意内存状态的
而且MySql认为系统空闲了,也是没啥性能问题的
对于1,2情况
1.这种情况是MySql要尽量避免的,因为这种刷新是MySql被动刷新的,而且需要将更新堵住才行
2.内存不够了,这是由于MySql提供了缓存池来管理内存,缓冲池中的内存页具有三种状态
尚未使用
使用但未修改
修改过的
常见的下面两种情况,总结下来的整体流程为
当读入的数据页没有在内存中的时候,会在磁盘中找到这个数据页,然后读到内存中去,这时候可能会触发缓冲池的淘汰机制
而这个淘汰机制中,能够保证不动redo log文件中,而且在重放的过程中,如果一个数据页已经刷过了,会自动跳过的
在淘汰机制中,会将最久不用的数据页从内存中淘汰掉,如果这页正好是一个脏页,那么就进行刷页的操作
但是在刷新的时候,如果脏页次数过多,会导致查询的响应时间边长
所以InnoDB为了避免内存不够 和 日志写满的问题,会进行脏页比例的控制
InnoDB刷脏页的控制策略
首先InnoDB需要知道整个主机的IO能力,才能确定刷新脏页的时候,能刷多块
这时候可以设置innodb_io_capacity这个参数,往常设置为IOPS的值
如果一个主机本身的性能很好,但是这个值设置的很低的话,会导致InnoDB刷新脏页的速度变的很慢
设置好了全力刷新脏页的能力了,但不能是一直全力刷新的,如何在恰当的时候刷新是下一个问题
首先确定一个问题,InnoDB刷盘速度和什么相关,
1.脏页的比例
2.redo log的写盘速度
那么会根据这两个因素来计算出一个数字
innodb_max_dirty_pages_pct是脏页的比例上限,默认是75% InnoDB会根据当前的脏页比例,来算出一个0-100的数字
然后每次写入的日志都有一个自己的序号,当前写入的序号和checkpoint之间的序号差值,设为N,InnoDB会根据差值算出一个0-100的数字
然后比较上面两个值,取出来比较大的记为R,然后就可以根据innodb_io_capacity来乘以R%来控制刷新脏页的速度了
这就需要准确的设置innodb_io_capacity的值,来让MySql合理的刷新
如何去查询脏页比例
可以通过 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;
最后还有一个策略
类似环链表的一个策略,在一个查询请求在执行过程中讲一个脏页flush掉的时候,这个flush可能触发旁边的数据页也是脏页,将邻居一起刷掉,然后继续蔓延,一直刷到不是脏页的问题
当然这个值是可以控制的,利用innodb_flush_neighbors来控制这个参数,如果值为1的话,会触发上面的连坐机制,值为0的话,自己只刷自己的
但是如果是SSD这种刷盘速度非常的情况下,没必要开着
当然,在MySQL 8.0后 innodb_flush_neighbors已经设置为0了
这也就是刷盘效率很高的机器,如果redo log设置的太小
可能导致环很容易就被写满了
导致了强制更新
这就是磁盘压力很小,数据库性能间隔性下降