MYSQL性能突然下降的原因分析

迷南。 2024-03-23 21:27 47阅读 0赞

![5aebd5b1a7ab2f42ed8ba431656c1ceb.jpeg][]

这篇文章主要介绍了MySQL性能突然下降的原因，帮助大家更好的了解和维护数据库，感兴趣的朋友可以了解下

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

**SQL 执行突然变慢的原因**

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

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

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

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

**引起 flush 操作的原因**

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

![c96cd1c7df7f62f0ce128984a445b328.png][]

这里假设 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 这个工具来测试：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest</code></p> </td> 
  </tr> 
 </tbody> 
</table>

由于`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）

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> </td> 
   <td> <p><code># M 脏页比例</code></p> <p><code>F1(M)</code></p> <p><code>{ </code></p> <p><code>&nbsp;</code><code>if</code> <code>M&gt;=innodb_max_dirty_pages_pct </code><code>then</code></p> <p><code>&nbsp;&nbsp;</code><code>return</code> <code>100;</code></p> <p><code>&nbsp;</code><code>return</code> <code>100*M</code><code>/innodb_max_dirty_pages_pct</code><code>;</code></p> <p><code>}</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>mysql&gt; use performance_schema;</code></p> <p><code>mysql&gt; </code><code>select</code> <code>VARIABLE_VALUE </code><code>into</code> <code>@a </code><code>from</code> <code>global_status </code><code>where</code> <code>VARIABLE_NAME = </code><code>'Innodb_buffer_pool_pages_dirty'</code><code>;</code></p> <p><code>select</code> <code>VARIABLE_VALUE </code><code>into</code> <code>@b </code><code>from</code> <code>global_status </code><code>where</code> <code>VARIABLE_NAME = </code><code>'Innodb_buffer_pool_pages_total'</code><code>;</code></p> <p><code>select</code> <code>@a/@b;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

初次之外，在一个查询操作进行时，如果需要 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性能突然下降的原因的详细内容，希望可以帮到你。

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[5aebd5b1a7ab2f42ed8ba431656c1ceb.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/36048aeecf534a1ca08d39a02aa43273.png
[MySQL Redo log]: https://www.weidianyuedu.com/content/1220866989542.html
[c96cd1c7df7f62f0ce128984a445b328.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/60e6aed4874446619868154ff38d6c66.png
[Link 1]: https://m.weidianyuedu.com/
[https_www.weidianyuedu.com]: https://www.weidianyuedu.com