12.为什么我的MYSQL会“抖”一下?
有时候我们会发现平时执行很快的SQL语句,突然变的很慢,这有可能是MYSQL在flush脏页。
MYSQL flush脏页有四种场景:
1.redo log写满了,write pos将要追上check point。
2.内存不够用了,需要淘汰一部分脏页。
3.MYSQL后台线程认为系统有足够的IO能力时,刷一部分脏页到磁盘。
4.MYSQL正常关闭时,会将所有的脏页刷新到磁盘。
这四种场景中,后两种场景对于性能没有太大影响,我们重点关注前两种场景。
1.redo log写满了,innodb会强制刷新脏页到磁盘,这时候系统不能再接受更新了,所有的更新都会被阻塞。这是innodb需要尽量避免的一种情况。
2.内存不够用了,需要淘汰一部分脏页,这是innodb的常态。
innodb使用buffer pool(缓冲池)来管理内存,内存中的页分为三种状态:
1)未被使用的页,
2)干净页
3)脏页
未被使用的页,在内存中一般不会存在。
干净页指的是内存中的数据和磁盘保持一致,
脏页指的是内存中的数据和磁盘不一致,内存中的数据做了更新,还未flush到磁盘。
当内存不够用了,必须要淘汰掉最久没有使用的页,(这里使用LRU算法),如果被淘汰的页是干净页,那么可以直接释放后使用;如果淘汰的页是脏页,那么必须将脏页flush到磁盘,使脏页变成干净页后,才可使用。
如果一个查询语句导致刷新非常多的脏页到磁盘,那么会导致查询的响应时间变长。
上述两种场景都会导致性能的降低,那么他们有什么异同点呢?
1.redo log 写满,会导致写阻塞,写能力为0;
2.内存不够导致刷脏页过多,会影响查询性能。
MYSQL刷新脏页的控制策略
首先我们要告诉MYSQL我们系统的IO能力是多少,这样MYSQL才知道flush脏页的时候应该刷多快,设置参数innodb_io_capacity,这个值建议设置为磁盘的iops。
innodb刷新脏页的速度和两个因素有关,一个是脏页比例,一个是redo log 写盘速度。
我们要时常关注脏页比例,正常情况下脏页比例不应该超过百分之75.
脏页比例可以通过参数 innodb_buffer_pool_pages_dirty / innodb_buffer_pool_pages_total来计算得出。
innodb刷新脏页时还有一个连坐机制,如果参数innodb_flush_neighbors的值为1,那么在刷新脏页时,会去检查这个数据页旁边的数据页是不是也是脏页,如果是,那么会一起刷新,这样可能导致查询语句响应时间更长。这个机制在机械硬盘时代是很有意义的,但是对于现在,SSD硬盘存储通常有很高的iops,所以现在MYSQL8.0版本中,innodb_flush_neighbors的默认值已经是0了。
自我总结:
本节学习的实在有些糊涂,尤其是innodb计算刷新磁盘速度那里的知识点,后续还需复习。