MySQL运行偶发卡顿？深入探究原因

MySQL运行时偶发卡顿？深入探寻缘由

有这样一种情况，某条SQL语句正常执行时速度极快，但有时却会变得十分迟缓，而且这种状况很难重现，是随机出现的，持续时间还很短，就好像突然“卡顿”了一下。

你的SQL语句为何变“慢”

在MySQL相关知识里，讲到了WAL机制。InnoDB处理更新语句时，先把内存更新好，并且写完redo log后，就会把结果返回给客户端，此次更新就算完成了。而内存里的数据最终得写到磁盘上，这个操作叫做flush。当内存的数据页和磁盘的数据页内容不一样的时候，这个内存页就被称作脏页；当内存数据写入磁盘后两者内容一致了，就称为干净页。不管是脏页还是干净页，说的都是内存里的数据页。

对于开头提到的那种场景，平常执行很快的更新操作，其实就是在往内存和日志里写数据，而那“卡顿”的瞬间，很有可能就是在进行flush脏页的操作。

有几种情况会引发数据库的flush过程：

• 当InnoDB的redo log写满了，这时候系统就会停止所有的更新操作，通过flush来把redo log中的checkpoint往前推进，好让redo log有空间继续写；

• 系统内存不够用了。当需要新的内存页，而内存又不够的时候，就得淘汰一些数据页，腾出内存给别的数据页用。要是要淘汰的是脏页，那就得先把脏页刷到磁盘里。或许有人会问，为啥不直接淘汰内存，下次请求的时候再从磁盘把数据页读进来，然后应用redo log呢？这是从性能方面考虑的，采用刷脏页写盘的办法，能让每个数据页有两种状态：

  • 内存里有这个数据页，那肯定是正确的结果，能直接返回；

  • 内存里没有这个数据页，那磁盘里肯定是正确的结果，把它读入内存后就能返回。

• MySQL觉得系统“空闲”的时候，就会找机会进行flush；

• MySQL正常关闭的时候，会把内存里的脏页都flush到磁盘，这样下次MySQL启动的时候，就能直接从磁盘读数据，启动速度会很快。

分析这四种情况对性能的影响：

• 第一种情况得尽量避免，因为这种情况下整个系统都不能再更新了，从监控上看更新数会变成0；

• 第二种情况比较常见。在InnoDB里，用缓冲池（buffer pool）来管理内存，缓冲池中的内存页有三种状态：

  • 还没有被使用；

  • 使用了而且是干净页；

  • 使用了而且是脏页；

由于InnoDB的策略是尽量用内存，所以对于长时间运行的数据库来说，没被使用的页面很少。要是要读入的数据页不在内存里，就得在缓冲池里申请一个数据页，把内存中最久没被使用的数据页淘汰掉。要是淘汰的是脏页，那就得先flush。

要是一个查询要淘汰的脏页个数太多，就会让查询的响应时间明显变长，会明显影响性能。

• 第三种情况，系统没什么压力；

• 第四种情况，数据库本身要关闭了，不用太关注性能问题。

InnoDB刷脏页的控制策略

接下来讲讲控制策略以及相关参数。

首先，得正确告诉InnoDB所在主机的IO能力，这样InnoDB才能知道全力刷脏页的时候能刷多快。这得用到innodb_io_capacity参数，它会告诉InnoDB系统的磁盘能力，建议把这个值设置成磁盘的IOPS，而磁盘的IOPS可以用fio工具来测试。

知道了“全力刷脏页”的能力，但不能一直全力刷，毕竟磁盘能力不能光用来刷脏页，还要服务用户请求。所以得让InnoDB控制引擎按照“全力”的一定百分比来刷页。

那么，设计控制刷脏页速度的策略时，会考虑哪些因素呢？考虑到如果刷得太慢，内存里的脏页数会太多，而且redo log可能会写满，所以主要参考的因素就是：

• 脏页比例；

• redo log写盘速度。

InnoDB会根据这两个因素分别算出两个数字：

• 参数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每次写入的日志都有一个序号，假设当前写入的序号和checkpoint对应的序号之间的差值是( N )。InnoDB会根据( N )算出一个0到100之间的数字，( N )越大算出来的( F2(N) )就越大。

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

上述过程的流程如图：

说到这里，你应该明白，不管是查询语句在需要内存的时候可能得淘汰一个脏页，还是因为刷脏页会占用IO资源影响更新语句，都可能让你感觉到MySQL“卡顿”了一下。要尽量避免这种情况，就得合理设置innodb_io_capacity的值，而且平时要多关注脏页比例，别让它经常接近75%。

其中，脏页比例的计算方法是Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total。

再来看一个策略。

一旦一个查询请求在执行过程中得先flush掉一个脏页，这个查询就可能比平时慢。MySQL有个机制，可能会让查询更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页也是脏页，就会把那个“邻居”也一起刷掉，这样可能会不断往后顺延。

在InnoDB里，用innodb_flush_neighbors参数来控制这个行为，值为1的时候就会有上述机制。

这类优化在机械磁盘时代比较有用，可以减少很多随机IO。而对于IOPS比较高的设备比如SSD，建议把这个值设为0，因为这时候IOPS往往不是瓶颈，只刷自己能更快完成必要的刷脏页操作，减少SQL语句的响应时间。

在MySQL 8.0中，这个参数的默认值已经是0了。