目录
写在前面
理解数据库更新时候的性能抖动优化、各种奇葩的锁导致性能降低的问题以及死锁问题,包括删库跑路、数据丢失等问题。
1、案例实战:线上数据库不确定性的性能抖动优化实践(上)
线上数据库时不时莫名其妙的来一次性能抖动的问题的两个原因:
1、数据库锂电池充放电的问题。
2、查询时脏页刷磁盘耗时问题和在日志文件都被写满的情况下,也会触发一次脏页的刷新。
第一种:我们平时在数据库里执行的更新语句,实际上都是从磁盘上加载数据页到数据库内存的缓存页里来,接着就直接更新内存里的缓存页,同时还更新对应的redo log写入一个buffer中。
既然我们更新了Buffer Pool里的缓存页,缓存页就会变成脏页,因为缓存页里的数据目前跟磁盘文件里的数据页的数据是不一样的,所以此时叫缓存页是脏页。既然是脏页,那么就必然得有一个合适的时机要把那脏页给刷入到磁盘文件里去,他是维护了一个lru链表来实现的,通过lru链表,他知道哪些缓存页是最近经常被使用的。
万一要是你要执行的是一个查询语句,需要查询大量的数据到缓存页里去,此时就可能导致内存里大量的脏页需要淘汰出去刷入磁盘上,才能腾出足够的内存空间来执行这条查询语句。在这种情况下,可能你会发现突然莫名其妙的线上数据库执行某个查询语句就一下子性能出现抖动,平 时只要几十毫秒的查询语句,这次一下子要几秒都有可能,毕竟你要等待大量脏页flush到磁盘,然后语句才能执行!
第二种:在日志文件都被写满的情况下,也会触发一次脏页的刷新。因为redo log buffer里的
因为假设你的第一个日志文件的一些redo log对应的内存里的缓存页的数据都没被刷新到磁 盘上的数据页里去,那么我问你,一旦你把第一个日志文件里的这部分redo log覆盖写了别的日志,那么此时万一你数据库崩溃,是不是有些你之前更新过的数据就彻底丢失了?所以一旦你把所有日志文件写满了,此时重新从第一个日志文件开始写的时候,他会判断一下,如果要是你第一个日志文件里的一些redo log对应之前更新过的缓存页,迄今为止都没刷入磁盘,那么此时必然是要把 那些马上要被覆盖的redo log更新的缓存页都刷入磁盘的。
尤其是在这一种刷脏页的情况下,因为redo log所有日志文件都写满了,此时会导致数据库直接hang死,无法处理任何更新请求,因为执行任何一个更新请求都必须要写redo log,此时你需要刷新一些脏页到磁盘,然后才能继续执行更新语句,把更新语句的redo log从第一个日志文件开始覆盖写。所以此时假设你在执行大量的更新语句,可能你突然发现线上数据库莫名其妙的很多更新语句短时间内性能都抖动了,可能很多更新语句平时就几毫秒就执行好了,这次要等待1秒才能执行完毕。因此遇到这种情况,你必须要等待第一个日志文件里部分redo log对应的脏页都刷入磁盘了,才能继续执行更新语句,此时必然会导致更新语句的性能很差。
2、案例实战:线上数据库不确定性的性能抖动优化实践(下)
两种导致性能抖动的原因:
- 第一个,可能buffer pool的缓存页都满了,此时你执行一个SQL查询很多数据,一下子要把很多缓存页 flush到磁盘上去,刷磁盘太慢了,就会导致你的查询语句执行的很慢。因为你必须等很多缓存页都flush到磁盘了,你才能执行查询从磁盘把你需要的数据页加载到buffer pool的缓存页里来。
- 第二个,可能你执行更新语句的时候,redo log在磁盘上的所有文件都写满了,此时需要回到第一个 redo log文件覆盖写,覆盖写的时候可能就涉及到第一个redo log文件里有很多redo log日志对应的更新操作改动了缓存页,那些缓存页还没flush到磁盘,此时就必须把那些缓存页flush到磁盘,才能执行后续的更新语句,那你这么一等待,必然会导致更新执行的很慢了。
尽可能优化MySQL的一些参数,减少这种缓存页flflush到磁盘带来的性能抖动问题。
- 第一个是尽量减少缓存页flflush到磁盘的频率。
- 第二个是尽量提升缓存页flflush到磁盘的速度。
那你想要减少缓存lflush到磁盘的频率,这个是很困难的,因为平时你的缓存页就是正常的在被使用,迟早会被填满,一旦填满,必然你执行下一个SQL会导致一批缓存页flush到磁盘,这个很难控制,除非你给你的数据库采用大内存机器,给buffer pool分配的内存空间大一些,那么他缓存页填满的速率低一些,flush磁盘的频率也会比较低。
举例:
假设你现在要执行一个SQL查询语句,此时需要等待flush一批缓存页到磁盘,接着才能加载查询出来的数据到缓存页。那么如果flush那批缓存页到磁盘需要1s,然后SQL查询语句自己执行的时间是200ms,此时你这条SQL执行完毕的总时间就需要1.2s了。但是如果你把那批缓存页flush到磁盘的时间优化到100ms,然后加上SQL查询自己执行的200ms,这条SQL的总执行时间就只要300ms了,性能就提升了很多。
- 第一:对于数据库部署的机器,一定要采用SSD固态硬盘,而不要使用机械硬盘,因为SSD固态硬盘最强大的地方,就是他的随机IO性能非常高。而flush缓存页到磁盘,就是典型的随机IO,需要在磁盘上找到各个缓存页所在的随机位置,把数据写入到磁盘里去。所以如果你采用的是SSD固态硬盘,那么你flflush缓存页到磁盘的性能首先就会提高不少。
- 第二:一个很关键的参数就是数据库的innodb_io_capacity,这个参数是告诉数据库采用多大的IO速率把缓存页flush到磁盘里去的。
- 举个例子,假设你SSD能承载的每秒随机IO次数是600次,结果呢,你把数据库的innodb_io_capacity就设置为了300,也就是flush缓存页到磁盘的时候,每秒最多执行300次随机IO,那你不是速度很慢么,而且根本没把你的SSD固态硬盘的随机IO性能发挥出来!所以通常都会建议大家对数据库部署机器的SSD固态硬盘能承载的最大随机IO速率做一个测试,这个可以使用fio工具来测试,fio工具是一种用于测试磁盘最大随机IO速率的linux上的工具。
- 第三:还有一个参数是innodb_flush_neighbors,是说在flush缓存页到磁盘的时候,可能会
控制把缓存页临近的其他缓存页也刷到磁盘 ,但是这样有时候会导致 flush 的缓存页太多了。实际上如果你用的是 SSD 固态硬盘,并没有必要让他同时刷邻近的缓存页,可以把innodb_flush_neighbors 参数设置为 0 ,禁止刷临近缓存页,这样就把每次刷新的缓存页数量降低到最少了。
总结:MySQL性能随机抖动的问题,最核心的就是把 innodb_io_capacity设置为SSD固态硬盘的IOPS,让他刷缓存页尽量快,同时设置 innodb_flflush_neighbors为0,让他每次别刷临近缓存页,减少要刷缓存页的数量,这样就可以把刷缓存页的性能提升到最高。同时也可以尽可能降低每次刷缓存页对执行SQL语句的影响。