12 | 为什么我的MySQL会“抖”一下?

一下内容出自 《MySQL 实战 45 讲》

12 | 为什么我的MySQL会“抖”一下?

一条 SQL 语句,正常执行的时候特别快,但是有时也不知道怎么回事,它就会变得特别慢,并且这样的场景很难复现,它不只随机,而且持续时间还很短。看上去,这就像是数据库“抖”了一下。

这个时候,MySQL 可能是在刷脏页(flush)。

引发数据库 flush 的情况

1、InnoDB redo log 写满了。这时候系统会停止所有的更新操作,把 checkpoint 往前推进,redo log 留出空间可以继续写。

2、系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是 “脏页”,就要先将脏页写到磁盘。

3、MySQL 认为系统空闲的时候。

4、MySQL 正常关闭。MySQL 会把内存的脏页都 flush 到磁盘上。

刷脏页虽然是常态,但是出现以下这两种情况,都是会明显影响性能的:

1、一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;

2、日志写满,更新全部堵住,写性能跌为 0,这种情况对敏感业务来说,是不能接受的。

InnoDB 刷脏页的控制策略

1、参数innodb_io_capacity: InooDB 根据这值判断磁盘能力,建议你设置成磁盘的 IOPS。

innodb_io_capacity 的默认值为 200,所以如果主机磁盘用的是 SSD,建议改为大,如 20000.

如果设置的过低,InnoDB 认为系统的能力差, 刷脏页刷得特别慢,甚至比脏页生成的速度还慢,这样就造成了脏页累积,影响了查询和更新性能。

2、参数 innodb_max_dirty_pages_pct 是脏页比例上限,默认值是 90%。

脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的。

select VARIABLE_VALUE into @a from performance_schema.global_status  where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from  performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

3、参数 innodb_flush_neighbors 控制是否刷邻居的脏页,默认值为 0。

即:在准备刷一个脏页的时候,如果这个数据页旁边的数据页刚好是脏页,就会把这个“邻居”也带着一起刷掉;而且这个把“邻居”拖下水的逻辑还可以继续蔓延,也就是对于每个邻居数据页,如果跟它相邻的数据页也还是脏页的话,也会被放到一起刷。

这个参数在机械盘时代比较有用,可以减少很对随机 IO。

对于 SSD 这类 IOPS 比较高的设备,建议设置为 0,因为这时候 IOPS 往往不是瓶颈。

posted @ 2023-06-29 22:30  LionelYee  阅读(37)  评论(0编辑  收藏  举报