刷脏页抖动

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

SQL语句变慢原因

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。数据库将随机写转换成了顺序写，大大提升了数据库的性能。

随机写：每次更新，都直接将数据写到磁盘。

InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫做redo log（重做日志），在更新内存写完redo log后，就返回给客户端本次更新成功。

redo log其实是“磁盘页到内存页的diff”，记录了“这是哪个页面的修改”。
undo log,每一行存了一个位置可以直接找到undo log，是MVCC的基础。

把内存里的数据写入磁盘的过程，称为flush。在这个flush操作执行之前内存里的数据和磁盘上数据文件记录的数据是不一致的。

flush 一般是说刷脏页；
purge一般是指清undo log；
merge一般是指应用change buffer。（change buffer只对非唯一索引有效）

当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为“脏页”。内存数据写入磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。(checkpoint往前推进只是把脏页变成干净页，干净页还在内存，只有淘汰时才会逐出内存)

平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是再刷脏页（flush）。
刷脏页的时候要将内存页写入磁盘(直接将脏页数据覆盖到对应磁盘上的数据)。无论是查询语句在需要内存的时候要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源影响到了更新语句，都可能是业务端感知到MySQL抖一下的原因。

刷脏页不用动redo log，redo log在“重放”的时候，如果一个数据页已经是刷过的，会识别出来并跳过。（每个数据页头部有LSN，8字节，每次修改都会变大(更新这个page的最后一个lab)。对比这个LSN跟redo log的checkpoint的LSN，比checkpoint小的一定是干净页。）

redolog buffer在事务执行过程中，先把要写的内容在内存中存起来，在commit阶段，一次性写入redolog file（磁盘上固定空间循环使用）。 innodb_flush_log_at_trx_commit=1;（默认值）表示每次事务提交时，redo log都直接持久化到磁盘。

引发数据库flush的场景

• 场景一：InnoDB的redo log写满了。此时系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。 checkpoint往前推进时，需要将推进过程中的日志，对应的所有脏页都flush到磁盘上。之后write pos到checkpoint新位置之间就可以再写入redo log。

每次事务提交都要写redo log，如果设置太小，很快就会被写满，write pos一直追着CP。这时系统不得不停止所有更新，去推进checkpoint。磁盘压力很小，但是数据库出现间歇性的性能下跌。

• 场景二：系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰掉的是脏页，就要先将脏页写到磁盘。刷脏页一定会写盘，保证每个数据页有两种状态：

  - 内存里存在，内存里就肯定是正确的结果，直接返回；  
  - 内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。  

• 场景三：MySQL认为系统空闲的时候。当然MySQL一直很忙时，也要合理的安排时间，即使是忙的时候，也要见缝插针的找时间，只要有机会就刷一点“脏页”。

• 场景四：MySQL正常关闭的情况。这时，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

不同flush场景对性能影响

• 情况三，属于MySQL空闲时的操作。这时系统没什么压力。

• 情况四，属于数据库本来就要关闭了。不太关注性能问题。

• 情况一，是InnoDB要尽量避免的。出现这种情况时，整个系统就不能再接受更新了，所有的更新都必须堵住。从监控上看，此时更新数会跌为0。

• 情况二，是常态。InnoDB用缓冲池buffer pool管理内存（查询缓存innodb buffer pool是server层的，存储的是SQL语句的查询结果），缓冲池中的内存页有三种状态：

  - 第一种，还没有使用的；
  - 第二种，使用了并且是干净页；
  - 第三种，使用了并且是脏页。  

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少（第一种）。
当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。此时只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用（第二种）；但是如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用（第三种）。

明显影响性能的两种情况：

1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2. 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

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

InnoDB刷脏页的控制策略

正确的告诉InnoDB所在主机的IO能力

这样InnoDB才能知道需要全力刷脏页的时候，可以刷多块。

通过innodb_io_capacity参数告诉InnoDB磁盘能力。这个值设置成磁盘的IOPS。磁盘的IOPS可以通过fio工具测试(磁盘随机读写)：（机械硬盘的随机 IOPS 一般只有几百）

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

设置太小，则InnoDB认为这个系统的能力差，所以刷脏页刷的特别慢（频率慢），甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。（要读取的数据不在内存中，但是脏页过多导致新页必须等待脏页刷新盘后，淘汰掉再加载到内存）

磁盘能力不能只用来刷脏页，还需要服务用户请求。

InnoDB控制引擎按照“全力”的百分比来刷脏页

InnoDB的刷盘速度参考两个因素：脏页比例、redo log写盘速度。

脏页比例
参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字。

F1(M)
{
      if M>=innodb_max_dirty_pages_pct then
            return 100;
      return 100*M/innodb_max_dirty_pages_pct;
}

脏页比例是通过innodb_buffer_pool_pages_dirty/innodb_buffer_pool_pages_tital得到的：

user information_schema;
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;

平时要多关注脏页比例，不要让它经常接近75%（max值）。

写盘速度
InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以即为F2(N)。N越大，算出来的值越大。

根据上述算的的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

脏页连坐

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

innodb_flush_neighbors=1;时会有连坐机制；

（适合机械硬盘，可以减少随机IO，提升系统性能）

innodb_flush_neighbors=0;时不找邻居，自己刷自己的（随机IO）。(MySQL8.0默认值)

（适合固态硬盘，IOPS不是瓶颈，只刷自己，能更快的执行完必要的刷脏页操作，减少SQL语句相应时间）