线上MySQL频繁抖动的性能优化实战

平常执行的升级语句，都是从磁盘上加载数据页到DB内存的缓存页，接着就直接升级内存里的缓存页，同时还升级对应的redo log写入一个buffer中。

既然升级了BP里的缓存页，缓存页就会变成脏页，就得有时机把那脏页给刷到磁盘文件，脏页刷盘机制，是维护了一个LRU链表。

后续若要加载磁盘文件的数据页到BP，但此时并无空闲缓存页，就得将部分脏缓存页刷入到磁盘，此时就会根据LRU刷盘。

万一你执行查询，需查大量数据到缓存页，可能导致内存里大量脏页需淘汰刷盘，才能腾出足够内存执行这条查询SQL。这时可能发现忽然莫名线上DB执行某查询SQL就忽然性能出现抖动，平常只需几十ms查询，这次一下子要几s，毕竟你要等待大量脏页flush磁盘，而后语句才能执行。

还有一种脏页刷盘时机，redo log buffer里的redo log本身也会随各种条件刷入磁盘上的日志文件，比方：

• redo log buffer里的数据超过容量的肯定比例

• 或者事务提交

都会强制buffer里的redo log刷入磁盘上的日志文件。磁盘上有多个日志文件，他会依次不停写，若写满所有日志文件，此时会重新回到第一个日志文件再次写入，这些日志文件是不停的循环写入的，所以其实在日志文件都被写满的情况下，也会触发一次脏页的刷新。由于假设你的第一个日志文件的少量redo log对应内存里的缓存页的数据都没被刷新到磁盘数据页，那一旦你把第一个日志文件里的这部分redo log覆盖写了别的日志，若此时DB崩溃， 有些你之前升级过的数据不就彻底丢了？所以一旦你将写满所有日志文件，此时重新从第一个日志文件开始写时，会判断，若要是你第一个日志文件里的少量redo log对应之前升级过的缓存页，至今都没刷盘，则此时得将那些马上要被覆盖的redo log升级的缓存页都刷盘。

在这种刷脏页情况下，由于redo log所有日志文件都写满，会导致DB 夯死，无法解决任何升级请求，由于执行任何一个升级请求都必需要写redo log！此时就得将少量脏页刷盘，才能继续执行升级语句，把升级语句的redo log从第一个日志文件开始覆盖写。

所以此时假设你在执行大量升级语句，可能忽然发现线上DB莫名很多升级语句短时间内性能都抖动了，可能很多升级语句平常就几ms执行完，这次要等待1s才能执行完。

处理方案

必需要等待第一个日志文件里部分redo log对应的脏页都刷盘，才能继续执行升级语句，这势必导致升级语句的性能很差。

综上，导致线上DB的查询和升级语句莫名出现性能抖动，很可能就是上述两种情况导致的执行语句时大量脏缓存页刷入磁盘，你要等待他们刷完磁盘才能继续执行。

在DB里执行查询或者升级语句时，可能SQL语句性能会莫名抖动，根本起因：

• BP缓存页都满了，此时执行一个SQL查询大量数据，一下将大量缓存页flush到磁盘，刷磁盘太慢，导致你的查询语句执行就很慢

  由于你必需等大量缓存页都flush到磁盘，才能执行查询从磁盘将你所需数据页加载到BP缓存页

• 执行升级语句时，redo log在磁盘上的所有文件都写满了

  此时需要回到第一个redo log文件覆盖写，覆盖写时可能涉及到第一个redo log文件里有很多redo log日志对应的升级操作改动了缓存页，那些缓存页还没刷盘，就必需把它们刷盘了，才能执行升级语句，而你这一等待，必然会导致升级执行的很慢

所以上述两个场景导致的大量缓存页flush到磁盘，就会导致莫名SQL性能抖动。

MySQL调优，降低缓存页刷盘对性能的影响

要达此目的，关键如下：

减少缓存页刷盘频率

很难！由于平常你的缓存页就是正常的在被使用，终究会被填满。

一旦填满，必然你执行下个SQL就会导致一批缓存页刷盘，这很难控制，除非你给你的数据库采用大内存机器，给BP分配的内存空间大些，则其缓存页填满的速率低些，刷盘频率也就较低。

提升缓存页刷盘速度

所以关键就是如何尽量提升缓存页的刷盘速度。

假设现在要执行一个查询，需等待flush一批缓存页，接着才能加载查询出来的数据到缓存页。

那么若flush那批缓存页到磁盘需1s，而后查询SQL本身执行200ms，此时你这条SQL执行完毕总时间就得1.2s。

但若将那批缓存页刷盘的时间优化到100ms，该SQL总执行时间就只要300ms，性能提升很多。所以关键之一就是尽可能减少缓存页刷盘的时间开销到min。

为此：

• 推荐采用SSD，因其随机I/O性能非常高。而flush缓存页到磁盘，就是典型随机I/O，得在磁盘上找到各缓存页所在的随机位置，把数据写入磁盘

• 还得设置DB的innodb_io_capacity参数，告诉DB采用多大的I/O速率刷盘

  image

假设你SSD能承载的随机I/O 600次/s，结果你把数据库的innodb_io_capacity就设为300，即刷盘时随机I/O最多执行300次/s，那你速度就还是很慢啊，根本没压榨完SSD随机I/O性能

所以推荐对DB部署机器的SSD能承载的最大随机I/O速率做个测试，fio工具常用来测试磁盘最大随机I/O速率。这样就知道他可执行多少次随机I/O / s，而后将该数值设置给DB的innodb_io_capacity。

但实际flush时，其实会按innodb_io_capacity乘以一个百分比来进行刷盘，就是脏页比

例，innodb_max_dirty_pages_pct参数控制，默认75%，一般不动它，另外该比例也可能会变化，该比例同时会参考你的redo log日志来计算的。关于这比例，这里的优化其实不用关注，核心就是把innodb_io_capacity设为SSD的IOPS，即随机I/O速率。

innodb_flush_neighbors

在缓存页刷盘时，可能会控制把缓存页临近的其余缓存页也刷盘。

但这有时会导致flush缓存页太多。实际上若你用SSD，并无必要让他同时刷邻近缓存页，可将innodb_flush_neighbors设为0，禁止刷临近缓存页，这样就把每次刷新的缓存页数量降低到min。

所以针对本文案例，即MySQL性能随机抖动问题，关键就是：

• 将innodb_io_capacity设为SSD 固态硬盘的IOPS，让他刷缓存页尽量快

• 同时设置innodb_flush_neighbors为0，让他每次别刷临近缓存页，减少要刷缓存页的数量

这样即可以把刷缓存页的性能提升到最高，也尽可能降低每次刷缓存页对执行SQL语句的影响。