mysql日志——mysql偶尔抖了一下

发表于 | 分类于

平时的sql执行中，偶尔会遇到这样的情况：一条sql正常执行会非常快，但是不知道为什么，会变得特别慢。并且这种情况难以复现。

为什么sql变慢了

在前面的章节mysql日志——更新sql如何执行中，你已经知道了mysql的WAL机制，一条更新语句只需要做一次顺序写redo log日志这个磁盘操作即可，然后直接返回给客户端更新成功。这种快速的更新方式就对应了——平时一条sql正常执行会非常快。
虽然返回给客户端更新成功，但是实际上sql只应用在了内存中，并记录了redo log，并没有将磁盘上的数据修改掉。我们将内存和磁盘上数据不一致的页称为脏页，内存数据写入到磁盘后，内存和磁盘上的数据就一致了，这样的页称为干净页。有时候sql执行之所以会慢，可能是遇到mysql要刷脏页——flush。

哪些场景会flush

  • redo log写不下了。在mysql日志——更新sql如何执行中提到过，如果write pos超过了checkpoint，这个时候mysql会停止一切更新，将checkpoint往前推进一点。此时，mysql会将redo log对应所在的脏页都flush到磁盘上。
  • 内存不够用了，即buffer pool不够使用了。一条查询sql发现要查询的内容不在内存中，需要到buffer pool申请新的内存页，buffer pool没有多余的空间，就会淘汰最久未使用的页供新页使用，如果淘汰的刚好是脏页，就要先flush到磁盘上，再释放空间。
  • mysql后台线程默默地刷脏页。
  • mysql正常关闭，会将内存中所有的脏页flush。

画外音：第二种情况，为什么不直接释放脏页，下次查询的时候拿redo log应用不就行了吗？这主要是从性能考虑，保证数据页只有两种状态：1.在内存中，那么内存中一定是最新的，直接返回；2.不在内存中，那么磁盘上数据一定是最新的，读入内存后返回。这样效率最高。

从上面的4种场景分析，第3和第4种场景并不会影响我们线上的业务。
第1种情况，redo log写满堵塞了后续所有的更新操作，业务上是不可接受的。第2种情况，内存不够用，要先flush脏页是常见的情况，但是如果一个查询导致淘汰的脏页太多，会导致执行时间变得太长，也是不可接受的。

画外音：mysql8.0以前的版本，在flush脏页时有连坐机制，如果flush的脏页旁边也是脏页会一并flush，并且这种逻辑还是蔓延的，所以flush一个脏页可能会连带一堆脏页flush。通过命令：show variables like ‘innodb_flush_neighbors’;查询当前版本mysql是否有连带机制（1表示会连带）。

flush控制策略

首先mysql得知道所在服务器的IO能力，可以通过fio工具来测试：

1
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_io_capacity来告诉InnoDB你的磁盘IO能力，一般建议设置成磁盘的IOPS，即全力flush时的值。

虽然innodb_io_capacity设置的是全力flush的值，但是磁盘并不是只为mysql一个服务的，所以InnoDB会按照全力的百分比flush脏页。
根据上面的分析，InnoDB要防止redo log写满，也要防止内存脏页太多产生连带flush，所以在计算这个百分比时考虑了两个因素：1.脏页占内存的百分比，2.redo log的写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认是75%。InnoDB会根据当前的脏页比例——M（这个比例是实时变化的），计算出一个范围在0～100的值，伪代码如下：

1
2
3
4
5
6
F(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB每次写redo log都会有一个序号——LSN（log sequence number），当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F(N)。F(N)算法比较复杂，你只要知道N越大，算出来的值越大就好了。
最后，根据上述算得的F(M)和F(N)取其中最大的值记为R，之前InnoDB按照innodb_io_capacity*R%来控制flush脏页的速度。示意图如下：
flush比例计算

为了避免mysql异常的抖动，即频繁地flush脏页，我们要合理地设置innodb_io_capacity，平时多关注脏页比例，避免频繁地接近75%。
画外音：脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，具体的命令参考下面的代码（要去mysql的performance_schema中执行）：

1
2
3
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;

小结

今天讲了由于WAL机制，导致了内存中存在脏页，mysql在flush脏页时会产生不可复现且短时间的抖动，最直观的感受就是平常执行很快的sql变慢了好多。因为脏页会被后台线程自动flush，也会由于数据页淘汰而触发flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。
为了避免频繁地flush脏页，我们要避免redo log经常写满，也要避免脏页占内存的比例频繁接近配置上限。

显示 Gitment 评论