mysql(五):数据库偶尔响应慢以及InnoDB刷脏页的策略

场景:
一条SQL语句，正常执行的时候特别快，但是有时不知道怎么回事，就会变得特别慢，并且这样的场景很难复现，不只是随机，而且持续时间还很短。

原因:
只要了解WAL机制，就知道InnoDB在处理更新语句的时候，只做了写日志这个磁盘操作，这个日志叫作redo log(重做日志)，在更新内存写完redo log后，就返回更新成功。
所以总需要时间去将内存中的数据写入到磁盘中，这个过程的术语就叫作flush。
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。所以有时SQL很慢的情况可能就是在刷脏页(flush)。
那什么时候会引发数据库的flush过程呢？
① InnoDB的redo log 写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log 留出空间才可以继续写。这时候就需要将推进对应的redo log 都flush到磁盘上。
② 系统内存不足，当需要新的内存页时，发现内存不够用，就需要淘汰一些数据页，如果淘汰的是"脏页"。就要先将脏页写到磁盘。
③ MySQL系统认为系统"空闲"的时候，刷新脏页。
④ MySQL正常关闭的时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
四种情况对性能的影响:
首先 第三种和第四种不会有太大的性能问题。
主要是第一种和第二种:
第一种是 “redo log 写满了，要刷新脏页”。这种情况是InnoDB要尽量避免的，因为出现这种情况，整个系统都不能再接受更新了，所有的更新都必须堵住，从监控上看，这时候更新数会跌为0。
第二种是"内存不够用了，要先将脏页写到磁盘"。这种情况是常态，InnoDB用缓冲池(buffer pool)管理内存，缓冲池中的内存页有三种状态:
① 还没有使用的内存页。
② 使用了并且是干净页。
③ 使用了并且是脏页。
InnoDB 的策略本身就是 尽量使用内存，对于一个长时间运行的数据库来说，未被使用的页面很少。

而当要读入的数据页没有内存的时候，就必须到缓冲池中申请一个数据页。这时候只能将最久不被使用的数据页从内存中淘汰掉:
如果淘汰的是干净页，就直接复用。
如果是脏页，就必须将脏页先刷到磁盘，变成干净页才能复用。

所以，刷脏页是常态，但是出现下面两种情况会明显影响到性能:
① 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长。
② 日志写满，更新全部堵住。
所以InnoDB需要控制脏页比例的机制，来尽量避免这两种情况。
InnoDB刷脏页的控制策略:
首先，InnoDB 需要知道所在主机的IO能力，这样InnoDB才能知道可以刷多块。参数innodb_io_capacity 会告诉InnoDB 你的磁盘能力，这个值建议设置成磁盘的IOPS,磁盘的IOPS可以通过fio这个工具来测试。
没有正确设置innodb_io_capacity 这个参数而导致的性能问题比比皆是。
有时候MySQL 写入速度很慢，TPS很低，但是主机的IO压力并不大。可能就是这个参数设置出了问题。

设计策略控制刷脏页的速度，会考虑哪些因素呢?
如果刷太慢，会导致内存脏页太多，可能redo log 写满。
InnoDB刷盘速度最主要考虑: ① 脏页比例， ② redo log 写盘速度。
参数innodb_max_dirty_pages_pct是脏页比例上限。默认值是75%。
平时要多关注脏页比例，不要让它经常接近75%。
脏页比例可以通过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 掉一个脏页的时候，如果这个数据页的旁边的数据页刚好也是脏页，就会把这个"邻居"页一起刷掉。而且可以继续蔓延。
在InnoDB中，innodb_flush_neighbors 参数可以控制这个行为。值为1的时候回有“连带”机制，值为0时只刷自己的。
连带刷脏页这个优化在机械硬盘中，可以减少很多随机IO。
而如果使用的是SSD这类IOPS比较高的设备，可以将innodb_flush_neighbors的值设置为0.因为这时候IOPS往往不是瓶颈。而"只刷自己"可以更快的执行完必要的刷脏页操作，减少SQL语句的响应时间。
在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。