平时执行的更新语句,都是从磁盘上加载数据页到DB内存的缓存页,接着就直接更新内存里的缓存页,同时还更新对应的redolog写入一个buffer中。
既然更新了BP里的缓存页,缓存页就会变成脏页,就得有时机把那脏页给刷到磁盘文件,脏页刷盘机制,是维护了一个LRU链表。
后续若要加载磁盘文件的数据页到BP,但此时并无空闲缓存页,就得将部分脏缓存页刷入到磁盘,此时就会根据LRU刷盘。
万一你执行查询,需查大量数据到缓存页,可能导致内存里大量脏页需淘汰刷盘,才能腾出足够内存执行这条查询SQL。这时可能发现突然莫名线上DB执行某查询SQL就突然性能出现抖动,平时只要几十ms查询,这次一下子要几s,毕竟你要等待大量脏页flush磁盘,然后语句才能执行。
还有一种脏页刷盘时机,redologbuffer里的redolog本身也会随各种条件刷入磁盘上的日志文件,比如:
redologbuffer里的数据超过容量的一定比例或事务提交
都会强制buffer里的redolog刷入磁盘上的日志文件。磁盘上有多个日志文件,他会依次不停写,若写满所有日志文件,此时会重新回到第一个日志文件再次写入,这些日志文件是不停的循环写入的,所以其实在日志文件都被写满的情况下,也会触发一次脏页的刷新。因为假设你的第一个日志文件的一些redolog对应内存里的缓存页的数据都没被刷新到磁盘数据页,那一旦你把第一个日志文件里的这部分redolog覆盖写了别的日志,若此时DB崩溃,有些你之前更新过的数据不就彻底丢了?所以一旦你将写满所有日志文件,此时重新从第一个日志文件开始写时,会判断,若要是你第一个日志文件里的一些redolog对应之前更新过的缓存页,至今都没刷盘,则此时得将那些马上要被覆盖的redolog更新的缓存页都刷盘。
在这种刷脏页情况下,因为redolog所有日志文件都写满,会导致DB夯死,无法处理任何更新请求,因为执行任何一个更新请求都必须要写redolog!此时就得将一些脏页刷盘,才能继续执行更新语句,把更新语句的redolog从第一个日志文件开始覆盖写。
所以此时假设你在执行大量更新语句,可能突然发现线上DB莫名很多更新语句短时间内性能都抖动了,可能很多更新语句平时就几ms执行完,这次要等待1s才能执行完。
解决方案
必须要等待第一个日志文件里部分redolog对应的脏页都刷盘,才能继续执行更新语句,这势必导致更新语句的性能很差。
综上,导致线上DB的查询和更新语句莫名出现性能抖动,很可能就是上述两种情况导致的执行语句时大量脏缓存页刷入磁盘,你要等待他们刷完磁盘才能继续执行。
在DB里执行查询或更新语句时,可能SQL语句性能会莫名抖动,根本原因:
BP缓存页都满了,此时执行一个SQL查询大量数据,一下将大量缓存页flush到磁盘,刷磁盘太慢,导致你的查询语句执行就很慢因为你必须等大量缓存页都flush到磁盘,才能执行查询从磁盘将你所需数据页加载到BP缓存页执行更新语句时,redolog在磁盘上的所有文件都写满了此时需要回到第一个redolog文件覆盖写,覆盖写时可能涉及到第一个redolog文件里有很多redolog日志对应的更新操作改动了缓存页,那些缓存页还没刷盘,就必须把它们刷盘了,才能执行更新语句,而你这一等待,必然会导致更新执行的很慢
所以上述两个场景导致的大量缓存页flush到磁盘,就会导致莫名SQL性能抖动。
MySQL调优,降低缓存页刷盘对性能的影响
要达此目的,关键如下:
减少缓存页刷盘频率
很难!因为平时你的缓存页就是正常的在被使用,终究会被填满。
一旦填满,必然你执行下个SQL就会导致一批缓存页刷盘,这很难控制,除非你给你的数据库采用大内存机器,给BP分配的内存空间大些,则其缓存页填满的速率低些,刷盘频率也就较低。
提升缓存页刷盘速度
所以关键就是如何尽量提升缓存页的刷盘速度。
假设现在要执行一个查询,需等待flush一批缓存页,接着才能加载查询出来的数据到缓存页。
那么若flush那批缓存页到磁盘需1s,然后查询SQL本身执行ms,此时你这条SQL执行完毕总时间就得1.2s。
但若将那批缓存页刷盘的时间优化到ms,该SQL总执行时间就只需ms,性能提升很多。所以关键之一就是尽可能减少缓存页刷盘的时间开销到min。
为此:
推荐采用SSD,因其随机I/O性能非常高。而flush缓存页到磁盘,就是典型随机I/O,得在磁盘上找到各缓存页所在的随机位置,把数据写入磁盘还得设置DB的innodb_io_capacity参数,告诉DB采用多大的I/O速率刷盘
假设你SSD能承载的随机I/O次/s,结果你把数据库的innodb_io_capacity就设为,即刷盘时随机I/O最多执行次/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%,一般不动它,另外该比例也可能会变化,该比例同时会参考你的redolog日志来计算的。关于这比例,这里的优化其实不用