问题

业务反应数据库很卡，说一个sql平时大于执行100ms，突然变成了需要60s才能执行。

分析

查看数据库状态，有很多query end，被阻塞的状态

查看慢日志，确实发现一个执行了60多秒的慢sql,但是该sql本身并不慢，是因为其他因素被影响导致很慢。

日志分析

查看错误日志有这样一行

[Note] InnoDB: page_cleaner: 1000ms intended loop took 20435ms. The settings might not be optimal. (flushed=10000 and evicted=0, during the time.)

page_cleaner_thread:脏页清理线程负责将脏页从内存写到磁盘。

出现该问题的原因：上面提示的信息的含义是,有大量脏页需要刷新，理论上应该在1s内完成，但实际却用了20s的时间将脏页刷新到磁盘，它接受脏页的数量远远大于它每秒能够处理脏页的能力。

硬件方面分析

查看磁盘IO，此时的截图已经过了高峰期，不过仍然发现147次写入只写入了2.17M，同时，w_await变大超过10ms，%util 接近 100% ，就意味着设备饱和

查看磁盘类型是机械盘

处理

从以上的信息可以得知磁盘的IO瓶颈导致影响了数据库的性能，应该想办法降低IO瓶颈带来的影响。

参数调整

innodb_lru_scan_depth

innodb_lru_scan_depth 定义了 LRU 的空闲page 的数量，并且是每个 buffer_pool 的数量。查看该参数的设置

从图中可知该库有8个 buffer_pool , 那么每个 buffer_pool 的尾部都必须保持有 innodb_lru_scan_depth（4096）个空闲buffer page ，如果空闲的buffer_page少于 4096个，那么就会将这些在尾部的4096中的非空闲 buffer 清除掉，这些 buffer page 中有些是可以直接清除的，有些是脏块，那么这些脏块就必须得刷新到磁盘。可以考虑将该值降低，因为innodb_lru_scan_depth * innodb_buffer_pool_instances决定了每秒page cleaner thread处理的工作量，也就是降低了IO的工作量。

innodb_io_capacity

innodb_io_capacity 一次刷脏块的个数

这个时候，innodb刷新的数量就是由参数 innodb_io_capacity 来控制（刷脏的范围肯定不止LRU），该库设置为10000，如果LRU中需要刷新的脏块特别多，8个buffer pool 加起来的脏块超过32768，那么就分几次刷新。如果磁盘IO 比较给力的话，可以提升innodb_io_capacity这个参数的值，可以提升IO性能。但是这里是机械盘，IO比较差，因此需要降低该参数的设置，来减轻IO的压力。

innodb_io_capacity_max

innodb_io_capacity是InnoDB将要求的I/O吞吐量的软限制.但这个限制是灵活的;

如果刷新落后于新脏页面创建的速度,InnoDB将动态增加超出此限制的刷新率.innodb_io_capacity_max选项定义了InnoDB可以提高冲洗率的更严格限制.因此该参数也应该对应调小。

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

脏页比例这么算 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;

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;

innodb_max_dirty_pages_pct_lwm

innodb_max_dirty_pages_pct_lwm 参数定义了一个代表脏页比例的低水位线，当buffer pool里面的脏页比例超过这个低水位线时，能够触发脏页预刷功能，来逐步控制脏页比例。

innodb_max_dirty_pages_pct_lwm 是一个可动态修改的参数，取值范围为0到99.99，默认值为0，当设置为0时，表示禁用低水位预刷脏页功能。

innodb_lru_scan_depth 应该降低

innodb_io_capacity 应该降低

innodb_max_dirty_pages_pct 应该降低

innodb_max_dirty_pages_pct_lwm 如果设置了应该考虑降低

降低的目的在于减少每次刷新的量，让每次刷新块数更加平均。从而避免page clean 线程爆发性的刷新脏数据库，从而堵塞IO通道。

innodb_flush_neighbors

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

机械盘建议打开。

调整后

也可调整innodb_max_dirty_pages_pct_lwm

评估REDO大小

redo太小，调整innodb_log_file_size

redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了从监控上看，这时候更新数会跌为 0。

判断如何配置redo log的大小

一个合理大小的日志文件应该可以容纳数据库在高峰时1到2个小时的数据变化。下面的例子是查询一分钟产生的日志量：

在这60s期间，我们业务系统处于正常的运行状态，此次为实验环境，我做了简单的业务模拟操作。

select round((1167787001- 1159609513)*60/1024/1024) “1 hour log(MB)”;

lsn号从1159609513增长到1167787001

一分钟redo log量：select round((1167787001- 1159609513)/1024/1024) “1 min log(MB)” =8MB

一小时redo log量：select round((1167787001- 1159609513)*60/1024/1024) “1 hour log(MB)”; =468MB

总结

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

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

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

脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

刷脏页时肯定要在那几个 list上加 mutex锁，刷不完也会阻塞正常的sql执行。

对于此案例，因为数据库刷脏页，因为机械盘IO比较差，所以堵塞了IO，占用了资源，导致出现卡顿的现象。

可以通过调整如下参数

innodb_lru_scan_depth 应该降低 innodb_io_capacity 应该降低 innodb_max_dirty_pages_pct 应该降低 innodb_max_dirty_pages_pct_lwm 如果设置了应该考虑降低 innodb_flush_neighbors 打开