MySQL实战 | 12 为什么我的MySQL会突然变慢?

日常中,也许会遇到这种场景,一条 SQL 语句,正常执行速度很快。但是,有时却变得很慢,而且很难复现,随机性比较高,并且持续时间短,到底是什么情况?

之前的文章中,我们说过,MySQL 的每一次更新并没有每次都写入磁盘,InnoDB 引擎会先将记录写到 redo log 里,然后在适当的时候,再把这个记录更新到磁盘。

当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。

那么,有理由怀疑,是 redo log 写入磁盘时导致的。

redo log 何时写入磁盘?

1、redo log 写满了,此时系统会停止所有更新操作,把脏页刷到磁盘;
2、系统内存不足,需要淘汰脏页,就要把脏页写到磁盘;
3、系统空闲时,会进行脏页清除;
4、系统异常关闭时;

这几种场景对系统性能的影响:

场景三是在空闲时操作的,对系统没什么影响,场景四是系统关闭时,也不需要考虑。

场景一,此时 redo log 写满,系统直接不再接受更新,所有的更新都被阻塞,需要尽量避免这种情况。

场景二,内存不足,由于 InnoDB 的策略是尽量使用内存,当要读入的数据没有在内存页时,需要申请新的数据页,这时就需要从已占用的内存中淘汰最久不使用的数据页。若要淘汰的是一个干净页,则直接淘汰;若要淘汰的是一个脏页,则需要先刷到磁盘,再淘汰。

所以,以下两种情况会导致查询突然变慢:
1、该查询需要淘汰的脏页较多;
2、redo log 写满,更新全部堵住;

InnoDB 需要有控制脏页比例的机制,来尽量避免上面的这两种情况。

InnoDB 刷脏页的控制策略

innodb_io_capacity

通过设置该参数,告诉 InnoDB 主机的磁盘能力,这样 InnoDB 才能知道需要全力刷脏页的时候,可以刷多快。

要尽量避免这种情况,你就要合理地设置 innodb_io_capacity 的值,并且平时要多关注脏页比例,不要让它经常接近 75%。

其中,脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的,具体的命令参考下面的代码:

1
2
3
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;

innodb_flush_neighbors

该参数表示,刷脏页时,是否连同“邻居”一起刷掉。

值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。

如果是机械硬盘,可以设置为 1,从而减少随机 IO;若是 SSD 等高 IOPS 的设备,可以设置为 0。

hoxis wechat
一个脱离了高级趣味的程序员,关注回复1024有惊喜~
赞赏一杯咖啡
0%