MySQL性能突然下降的原因

有时会碰到这样的情况，一条 SQL 在平时执行没问题，很快。但是突然某个时间执行的就会很慢，而且这种场景并不能复现，只能随机发送的。

SQL 执行突然变慢的原因

在之前讲解 MySQL Redo log 时，说到了 WAL 机制，为了保证 MySQL 更新的速度，在进行更新操作时，先将更新内容写入 redo log，后续系统空闲时，再将 redo log 的内容应用到磁盘。

当内存数据页（redo log）和磁盘数据页内容不一致时，将该内存也称为 “脏页”。将内存数据写入到磁盘后，数据一致，内存页称为 "干净页"。

在内存数据写入磁盘时，这个过程称为 flush 过程。SQL 突然执行变得很慢，性能下降。原因就可能和 flush 操作有关。

因为在进行 flush 操作时，更新操作会等待 redo log 的写入。

引起 flush 操作的原因

场景一：redo log 日志已经记满。这时系统会停止更新操作，将 check point 向前推进，让 redo log 留出空间可以继续写。

这里假设 CP 到 CP‘ 间隙已经写入到磁盘，这部分就变成了干净页，此时 write pos 就可以写入这部分区域了。

场景二：系统内存不足，需要新的内存页时，发现内存不够用了，就需要淘汰一些数据页。如果淘汰时，这时数据页时脏页，就要将脏页写到磁盘。

这时有个问题是，命名 redo log 中的内容已经被记录到日志中了，假如内存满了，直接删除不就可以吗？下次读入时，再把 redo log 日志中的内容应用到磁盘。

没有选择直接清空内存，是从性能考虑的，因为在查询数据时，有两种情况：

• 首先数据页在内存中，内存是就是正确的结果，直接返回
• 内存里没有数据，从数据文件上读入内存。

所以这样效率比较高。

场景三：MySQL 会在系统空闲时，进入 flush 操作。

场景四：在 MySQL 正常关闭时，会把内存脏页 flush 到磁盘上。

引起 flush 对性能的影响

对于第三，四场景来说，是比较正常的情况，不需要考虑性能问题。

对于第一种场景，InnoDB 会尽量避免，因为在这种情况下，整个系统不再接受更新。

但有时出现人为的配置错误，比如内存为 128 GB，innodb_io_capacity 设置为 20000 的实例。通常建议将 redo log 设置成 4 个 1GB 的文件。但由于配置错误，设置成 100M 的文件。

这里由于 redo log 设置的太小，很快就会被写满。write pos 一直追着 check point. 这时，系统只能停止所有更新，推进 checkpoint.

表现就是，磁盘 IO 很小，但是出现间歇性的性能下降。

对于第二种场景，内存不够用的情况，InnoDB 会用缓冲池（buffer pool）管理内存

内存页在缓冲池中会有三种状态：

• 没用使用的数据页
• 使用了，但是是干净页
• 使用了，是脏页
  

每个数据页头部有LSN，8字节，每次修改都会变大。

对比这个 LSN 跟 checkpoint 的 LSN，比checkpoint小的一定是干净页

由于 InnoDB 的策略是尽可能使用内存，所以对于长时间运行的库来说，未被使用的页面很少。

当发现想读入的数据页没有在内存中时，必须到缓冲池申请数据页。并会把最久不用得数据页从内存中淘汰

如果是干净页，直接释放使用
如果是脏页，必须先刷盘，变成干净页才能复用
当时，如果在下面的情况进行刷脏页，会明显影响性能：

要淘汰的脏页太多，导致查询响应时间较长。
日志写满，更新被阻塞。
为了解决这个问题，InnoDB 使用控制脏页比例的机制，来避免上面的情况。

InooDB 控制刷脏页的策略

在 InnoDB 中，通过 innodb_io_capacity 参数，来告诉 InnoDB 目前主机的磁盘能力是多少，这个值建议设置成磁盘的 IOPS.

可以通过 fio 这个工具来测试：

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 导致的性能问题很常见，比如有时系统吞吐量（TPS）很低，写入很慢，但是磁盘 IO 并不高。就有可能是该参数设置的不正确。例如，innodb_io_capacity 的值设置的很低，但是磁盘用的 SSD，导致 InooDB 认为系统能力很差，所以刷脏页特别慢。造成脏页累计，影响查询和更新性能。

InnoDB 在刷盘时主要考虑两个因素：

1. 脏页的比例
   
2. redo log 写盘速度
   

会通过这两个因素单独先算出两个数字。

innodb_max_dirty_pages_pct 脏页比例上限，默认 75%.

InnoDB 会根据脏页的比例（M），算出范围在 0 - 100 的数字。，过程称为 F1（M）

# M 脏页比例
F1(M)
{
 if M>=innodb_max_dirty_pages_pct then
  return 100;
 return 100*M/innodb_max_dirty_pages_pct;
}

除此之外，InnoDB 每次写入日志都会有一个序号 N. 然后根据 N 再算出一个 0 到 100 的数字，这个计算过程称为 F2（N）

N: 当前写入的序号和 checkpoint 对应序号之间的差值。

最后，根据 F1（M）和 F2（N）两个值，取其中较大的值为 R，之后引擎就可以按照 innodb_io_capacity * R 来控制刷脏页的速度。

所以无论是在查询，需要加载数据到内存数据页，而淘汰脏页。还是更新时，导致刷盘操作都有可能造成 MySQL 的性能下降。

为了避免这种情况，要合理的设置 innodb_io_capacity 的值，平时要多关注脏页比例，不让其接近 75%.

其中脏页比例可以通过下面的方式获取：

mysql> use performance_schema;
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 脏页的话，如果这个该脏页的邻居也是脏页的话，就会把这个邻居一起刷掉，如果恰好旁边还是脏页的话，就会一直连坐。这时导致 flush 过慢的原因。

可以通过 innodb_flush_neighbors 来控制该行为，值为 1 打开上述机制，为 0 则关闭。

对于机械硬盘来说，是可以减少很多随机 IO ，因为机械硬盘 IOPS 一般就几百，减少随机 IO 就意味着性能提升。

但如果用 SSD 这类 IOPS 较高的设备，IOPS 往往不是瓶颈，关闭就好，减少 SQL 语句的响应时间。

在 8.0 中，已经默认是 0 了.

总结

这篇中，主要介绍了 WAL 时的 flush 操作可能会造成 MySQL 突然的性能下降。

引起的原因一般是由于内存不够导致的，进而可以通过设置合适的 innodb_io_capacity 参数，来控制 InnoDB flush 的过程。

以上就是MySQL性能突然下降的原因的详细内容，更多关于MySQL性能下降的资料请关注脚本之家其它相关文章！