本篇文章为大家展示了MySQL性能下降的原因有哪些,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
<强> SQL执行突然变慢的原因
在之前讲解MySQL重做日志时,说到了细胞膜机制,为了保证MySQL更新的速度,在进行更新操作时,先将更新内容写入重做日志,后续系统空闲时,再将重做日志的内容应用到磁盘。
在内存数据写入磁盘时,这个过程称为冲过程. SQL突然执行变得很慢,性能下降。原因就可能和冲洗操作有关。
因为在进行冲操作时,更新操作会等待重做日志的写入。
<强>引起冲洗操作的原因
场景一:重做日志日志已经记满。这时系统会停止更新操作,将核对点向前推进,让重做日志留出空间可以继续写。
这里假设CP到CP的间隙已经写入到磁盘,这部分就变成了干净页,此时写pos就可以写入这部分区域了。
场景二:系统内存不足,需要新的内存页时,发现内存不够用了,就需要淘汰一些数据页。如果淘汰时,这时数据页时脏页,就要将脏页写到磁盘。
这时有个问题是,命名重做日志中的内容已经被记录到日志中了,假如内存满了,直接删除不就可以吗?下次读入时,再把重做日志日志中的内容应用到磁盘。
没有选择直接清空内存,是从性能考虑的,因为在查询数据时,有两种情况:
- <李>首先数据页在内存中,内存是就是正确的结果,直接返回李 <李>内存里没有数据,从数据文件上读入内存。
所以这样效率比较高。
场景三:MySQL会在系统空闲时,进入冲洗操作。
场景四:在MySQL正常关闭时,会把内存脏页刷新到磁盘上。
<强>引起冲洗对性能的影响
对于第三,四场景来说,是比较正常的情况,不需要考虑性能问题。
对于第一种场景,InnoDB会尽量避免,因为在这种情况下,整个系统不再接受更新。
但有时出现人为的配置错误,比如内存为128 GB, innodb_io_capacity设置为20000的实例。通常建议将重做日志设置成4个1 GB的文件。但由于配置错误,设置成100的文件。
这里由于重做日志设置的太小,很快就会被写满.write pos一直追着。这时,系统只能停止所有更新,推进检查站。
表现就是,磁盘IO很小,但是出现间歇性的性能下降。
对于第二种场景,内存不够用的情况,
内存页在缓冲池中会有三种状态:
- <李>没用使用的数据页 <李>使用了,但是是干净页 <李>使用了,是脏页
李,
每个数据页头部有LSN, 8字节,每次修改都会变大。
对比这个LSN跟检查点的LSN,比小检查站的一定是干净页
由于InnoDB的策略是尽可能使用内存,所以对于长时间运行的库来说,未被使用的页面很少。
当发现想读入的数据页没有在内存中时,必须到缓冲池申请数据页。并会把最久不用得数据页从内存中淘汰
如果是干净页,直接释放使用
如果是脏页,必须先刷盘,变成干净页才能复用
当时,如果在下面的情况进行刷脏页,会明显影响性能:要淘汰的脏页太多,导致查询响应时间较长。
日志写满,更新被阻塞。
为了解决这个问题,InnoDB使用控制脏页比例的机制,来避免上面的情况。<强> InooDB控制刷脏页的策略
在InnoDB中,通过innodb_io_capacity参数,来告诉InnoDB目前主机的磁盘能力是多少,这个值建议设置成磁盘的IOPS。
可以通过fio这个工具来测试:
fio -文件名=$文件名直接=1 -iodepth 1线程- r=randrw -ioengine=psync b=16 k造=500 -numjobs=10运行时=10 -group_reporting - name=mytest由于<代码> innodb_io_capacity> innodb_io_capacity>
InnoDB在刷盘时主要考虑两个因素:
<李>脏页的比例
李,李 <>重做日志写盘速度
李,会通过这两个因素单独先算出两个数字。
<代码> innodb_max_dirty_pages_pct 脏页比例上限,默认75%。
InnoDB会根据脏页的比例(M),算出范围在0 - 100的数字,过程称为F1 (M)
MySQL性能下降的原因有哪些
