12 | 为什么我的MySQL会”抖”一下？
一条 SQL 语句，正常执行的时候特别快，但是 有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。
你的 SQL 语句为什么变”慢”了
内存里的数据写入磁盘的过程，叫做 flush。在这个 flush 操作执行之前，内存里的数据和磁盘里的是不一致的。
当 内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为” 脏页“。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为” 干净页“。
平时执行很快的更新操作，其实就是在 写内存和日志，而 MySQL 偶尔”抖”一下的那个瞬间，可能就是在 刷脏页（flush）
什么情况会引发数据库的 flush 过程
第一种场景是InnoDB 的redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
把 checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都 flush 到磁盘上。之后，图中从 write pos 到 CP’之间就是可以再写入的 redo log 的区域。
第二种情况是系统内存不足。当需要新的内存页，但内存不足时，需要去掉一些数据页，释放内存供其他数据页使用。如果清除了“脏页”，则首先将脏页写入磁盘。
[En]
The second scenario is that the system is out of memory. When new memory pages are needed and there is not enough memory, it is necessary to * eliminate some data pages * and free memory for other data pages to use. If the “dirty page” is eliminated, write the dirty page to disk first.
Q：如果直接淘汰内存，下次需要请求该页时，直接从磁盘读入数据页，然后拿 redo log 出来应用效率不好嘛？
A：
如果 刷脏页一定会写盘， 就保证了每个数据页有两种状态：
- 一种是内存里存在， 内存里就肯定是正确的结果，直接返回；
- 另一种是，如果内存中没有数据，可以确定数据文件是正确的结果，并将其读入内存后返回。
[En]
the other is that if there is no data in memory, you can be sure that the data file is the correct result and return it after reading it into memory.*
这样的效率最高。
第三种场景是 MySQL 认为系统”空闲”的时候
第四种场景是 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
上面四种场景对性能的影响
后两种情景不会太关注”性能”问题，主要来分析前两种场景下的性能问题。
第一种是”redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要 尽量避免的。因为出现这种情况的时候， 整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看， 这时候更新数会跌为 0。
第二种是”内存不够用了，要先将脏页写到磁盘”，这种情况其实是 常态。
InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
- 第一个是尚未使用。
[En]
the first is that it has not been used yet.*
- 第二个是页面被使用且干净
[En]
the second is that the page is used and is clean*
- 第三个是页面被使用和肮脏。
[En]
the third is that the page is used and dirty.*
InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
当需要读取的数据页不在内存中时，必须在缓冲池中申请数据页。此时只能从内存中删除最长的未使用数据页：
[En]
When the data page to be read is not in memory, you must apply for a data page in the buffer pool. * only the longest unused data pages can be eliminated from memory at this time:*
如果你想消除一个干净的页面，直接释放它并重新使用它。
[En]
If you want to eliminate a clean page, release it directly and reuse it.
但如果是脏页， 就必须将脏页先刷到磁盘，变成干净页后才能复用。
因此，虽然刷新脏页是正常的，但如果查询中需要清除的脏页太多，则查询的响应时间将显著延长，性能将受到显著影响。
[En]
Therefore, although it is normal to brush dirty pages, if there are too many dirty pages to be eliminated in a query, the response time of the query will be significantly longer and the performance will be significantly affected.
所以，InnoDB 需要有 控制脏页比例的机制，来尽量避免上面的这两种情况。
InnoDB 刷脏页的控制策略
首先，要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要 全力刷脏页的时候，可以刷多快。通过设置 innodb_io_capacity 这个参数为磁盘的 IOPS 来避免浪费磁盘性能。
毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。所以接下来，我们就一起看看 InnoDB 怎么控制引擎 按照”全力”的百分比来刷脏页。
Q： 如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？
A：
如果你刷牙太慢会发生什么？
[En]
What happens if you brush too slowly?
首先是内存脏页太多，其次是 redo log 写满。
所以，InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。
参数 innodb_max_dirty_pages_pct 是 脏页比例上限，默认值是 75%。
F1：InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的F1
F2：InnoDB 每次写入的 日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的 差值为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字F2
根据上述算得的 F1 和 F2两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。
总结：
合理地设置 innodb_io_capacity 的值，并且 平时要多关注脏页比例，不要让它经常接近 75%。
一个有趣的策略
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：
当准备浏览脏页时，如果数据页旁边的数据页恰好是脏页，则会与其一起抹掉邻居。不仅如此，拖拽“邻居”下水的逻辑还能继续蔓延，也就是说，对于每一个邻居的数据页，如果它旁边的数据页还脏，也会被刷到一起。
[En]
When preparing to browse a dirty page, * if the data page next to the data page happens to be a dirty page * , the “neighbor” will be wiped out with it * . Moreover, the logic of dragging the “neighbor” into the water can continue to spread, that is, for each neighbor data page, if the data page adjacent to it is still dirty, it will also be brushed together.*
在 InnoDB 中， innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的”连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值是 0
Q：一个内存配置为 128GB、innodb_io_capacity 设置为 20000 的大规格实例，正常会建议你将 redo log 设置成 4 个 1GB 的文件。
但如果你在配置的时候不慎将 redo log 设置成了 1 个 100M 的文件，会发生什么情况呢？又为什么会出现这样的情况呢？
A：
每次事务提交都要写 redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这个”环”将很快被写满， write pos 一直追着 CP。
这时候系统不得不停止所有更新，去推进 checkpoint。
这时，看到的现象是 磁盘压力很小，但是数据库出现间歇性的性能下跌。
Original: https://www.cnblogs.com/ydssx7/p/16515461.html
Author: ydssx
Title: MySQL实战45讲 12
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505154/
转载文章受原作者版权保护。转载请注明原作者出处！