案例实战 - 2、线上数据库莫名其妙的随机性能抖动优化 - 《MySQL必知必会》

今天我们要给大家讲解的第一个生产案例，就是线上数据库时不时莫名其妙的来一次性能抖动的问题，而且造成性能抖动的还不是之前我们讲过的数据库锂电池充放电的问题，而是另外一个新的问题，跟我们之前讲解的原理是息息相关的。大家都知道一件事情，那就是我们平时在数据库里执行的更新语句，实际上都是从磁盘上加载数据页到数据库内存的缓存页里来，接着就直接更新内存里的缓存页，同时还更新对应的redo log写入一个 buffer中，那么大家都知道，既然我们更新了Buffer Pool里的缓存页，缓存页就会变成脏页，之所以说他是脏页，就是因为缓存页里的数据目前跟磁盘文件里的数据页的数据是不一样的，所以此时叫缓存页是脏页。既然是脏页，那么就必然得有一个合适的时机要把那脏页给刷入到磁盘文件里去，之前我们其实就仔细分析过这个脏页刷入磁盘的机制，他是维护了一个lru链表来实现的，通过lru链表，他知道哪些缓存页是最近经常被使用的。那么后续如果你要加载磁盘文件的数据页到buffer pool里去了，但是此时并没有空闲的缓存页了，此时就必须要把部分脏缓存页刷入到磁盘里去，此时就会根据lru链表找那些最近最少被访问的缓存页去刷入磁盘，

那么万一要是你要执行的是一个查询语句，需要查询大量的数据到缓存页里去，此时就可能导致内存里大量的脏页需要淘汰出去刷入磁盘上，才能腾出足够的内存空间来执行这条查询语句。在这种情况下，可能你会发现突然莫名其妙的线上数据库执行某个查询语句就一下子性能出现抖动，平时只要几十毫秒的查询语句，这次一下子要几秒都有可能，毕竟你要等待大量脏页flush到磁盘，然后语句才能执行！另外还有一种脏页刷磁盘的契机，之前我们并没有给大家提到，就是大家都知道redo log buffer里的 redo log本身也是会随着各种条件刷入磁盘上的日志文件的，比如redo log buffer里的数据超过容量的一定比例了，或者是事务提交的时候，都会强制buffer里的redo log刷入磁盘上的日志文件。然后我们也知道，磁盘上是有多个日志文件的，他会依次不停的写，如果所有日志文件都写满了，此时会重新回到第一个日志文件再次写入，这些日志文件是不停的循环写入的，所以其实在日志文件都被写满的情况下，也会触发一次脏页的刷新。为什么呢？因为假设你的第一个日志文件的一些redo log对应的内存里的缓存页的数据都没被刷新到磁盘上的数据页里去，那么我问你，一旦你把第一个日志文件里的这部分redo log覆盖写了别的日志，那么此时万一你数据库崩溃，是不是有些你之前更新过的数据就彻底丢失了？所以一旦你把所有日志文件写满了，此时重新从第一个日志文件开始写的时候，他会判断一下，如果要是你第一个日志文件里的一些redo log对应之前更新过的缓存页，迄今为止都没刷入磁盘，那么此时必然是要把那些马上要被覆盖的redo log更新的缓存页都刷入磁盘的，

尤其是在这一种刷脏页的情况下，因为redo log所有日志文件都写满了，此时会导致数据库直接hang 死，无法处理任何更新请求，因为执行任何一个更新请求都必须要写redo log，此时你需要刷新一些脏页到磁盘，然后才能继续执行更新语句，把更新语句的redo log从第一个日志文件开始覆盖写。所以此时假设你在执行大量的更新语句，可能你突然发现线上数据库莫名其妙的很多更新语句短时间内性能都抖动了，可能很多更新语句平时就几毫秒就执行好了，这次要等待1秒才能执行完毕。因此遇到这种情况，你必须要等待第一个日志文件里部分redo log对应的脏页都刷入磁盘了，才能继续执行更新语句，此时必然会导致更新语句的性能很差。所以综上所述，导致线上数据库的查询和更新语句莫名其妙出现性能抖动，其实就很可能是上述两种情况导致的执行语句时大量脏缓存页刷入磁盘，你要等待他们刷完磁盘才能继续执行导致的。

其实这种莫名其妙的性能抖动，我们在分析过底层的原理之后，就理解的很清楚了，根本原因还是两个。

第一个，可能buffer pool的缓存页都满了，此时你执行一个SQL查询很多数据，一下子要把很多缓存页 flush到磁盘上去，刷磁盘太慢了，就会导致你的查询语句执行的很慢。因为你必须等很多缓存页都flush到磁盘了，你才能执行查询从磁盘把你需要的数据页加载到buffer pool的缓存页里来。

第二个，可能你执行更新语句的时候，redo log在磁盘上的所有文件都写满了，此时需要回到第一个 redo log文件覆盖写，覆盖写的时候可能就涉及到第一个redo log文件里有很多redo log日志对应的更新操作改动了缓存页，那些缓存页还没flush到磁盘，此时就必须把那些缓存页flush到磁盘，才能执行后续的更新语句，那你这么一等待，必然会导致更新执行的很慢了。

所以上述两个场景导致的大量缓存页flush到磁盘，就会导致莫名其妙的SQL语句性能抖动了那今天我们来说说怎么尽可能优化MySQL的一些参数，减少这种缓存页flush到磁盘带来的性能抖动问题。其实大家可以想一下，如果要尽量避免缓存页flush到磁盘可能带来的性能抖动问题，那么核心的就两点

第一个是尽量减少缓存页flush到磁盘的频率，第二个是尽量提升缓存页flush到磁盘的速度。那你想要减少缓存页flush到磁盘的频率，这个是很困难的，因为平时你的缓存页就是正常的在被使用，迟早会被填满，一旦填满，必然你执行下一个SQL会导致一批缓存页flush到磁盘，这个很难控制，除非你给你的数据库采用大内存机器，给buffer pool分配的内存空间大一些，那么他缓存页填满的速率低一些，flush磁盘的频率也会比较低。

所以今天我们主要是给讲解第二个问题的优化，就是如何尽量提升缓存页flush到磁盘的速度给大家举个例子，假设你现在要执行一个SQL查询语句，此时需要等待flush一批缓存页到磁盘，接着才能加载查询出来的数据到缓存页。那么如果flush那批缓存页到磁盘需要1s，然后SQL查询语句自己执行的时间是200ms，此时你这条SQL 执行完毕的总时间就需要1.2s了。但是如果你把那批缓存页flush到磁盘的时间优化到100ms，然后加上SQL查询自己执行的200ms，这条SQL的总执行时间就只要300ms了，性能就提升了很多。所以这里一个关键之一，就是要尽可能减少flush缓存页到磁盘的时间开销到最小。如果要做到这一点，通常给大家的一个建议就是对于数据库部署的机器，一定要采用SSD固态硬盘，而不要使用机械硬盘，因为SSD固态硬盘最强大的地方，就是他的随机IO性能非常高。而flush缓存页到磁盘，就是典型的随机IO，需要在磁盘上找到各个缓存页所在的随机位置，把数据写入到磁盘里去。所以如果你采用的是SSD固态硬盘，那么你flush缓存页到磁盘的性能首先就会提高不少。其次，光是用SSD还不够，因为你还得设置一个很关键的参数，就是数据库的innodb_io_capacity，这个参数是告诉数据库采用多大的IO速率把缓存页flush到磁盘里去的。

举个例子，假设你SSD能承载的每秒随机IO次数是600次，结果呢，你把数据库的innodb_io_capacity 就设置为了300，也就是flush缓存页到磁盘的时候，每秒最多执行300次随机IO，那你不是速度很慢么，而且根本没把你的SSD固态硬盘的随机IO性能发挥出来！所以通常都会建议大家对数据库部署机器的SSD固态硬盘能承载的最大随机IO速率做一个测试，这个可以使用fio工具来测试 fio工具是一种用于测试磁盘最大随机IO速率的linux上的工具，如何使用，大家可以网上搜一下，非常的简单。查出来SSD固态硬盘的最大随机IO速率之后，就知道他每秒可以执行多少次随机IO，此时你把这个数值设置给数据库的innodb_io_capacity这个参数就可以了，尽可能的让数据库用最大速率去flush缓存页到磁盘。但是实际flush的时候，其实他会按照innodb_io_capacity乘以一个百分比来进行刷磁盘，这个百分比就是脏页的比例，是innodb_max_dirty_pages_pct参数控制的，默认是75%，这个一般不用动，另外这个比例也有可能会变化，这个比例同时会参考你的redo log日志来计算，但是这个细节大家不用关注了。其实比例不比例的，我们这里优化不用关注，核心就是把innodb_io_capacity调整为SSD固态硬盘的 IOPS也就是随机IO速率就可以了。另外还有一个参数，是innodb_flush_neighbors，他意思是说，在flush缓存页到磁盘的时候，可能会控制把缓存页临近的其他缓存页也刷到磁盘，但是这样有时候会导致flush的缓存页太多了。实际上如果你用的是SSD固态硬盘，并没有必要让他同时刷邻近的缓存页，可以把 innodb_flush_neighbors参数设置为0，禁止刷临近缓存页，这样就把每次刷新的缓存页数量降低到最少了。所以呢，针对这次讲的这个案例，就是MySQL性能随机抖动的问题，最核心的就是把 innodb_io_capacity设置为SSD固态硬盘的IOPS，让他刷缓存页尽量快，同时设置 innodb_flush_neighbors为0，让他每次别刷临近缓存页，减少要刷缓存页的数量，这样就可以把刷缓存页的性能提升到最高。同时也可以尽可能降低每次刷缓存页对执行SQL语句的影响。