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 往往不是瓶颈。