delete语句执行过慢
一 现象
某个数据库经常在某个时间点比如凌晨2点或者白天某些时间段发出如下报警
-
[Critical][prod][mysql] - 超200 kill SQL/分钟 -
[P0][PROBLEM][all(#2) db_data.Com_kill db=XXXX[m]:3306 10.53333>=3.3] -
[O1 2019-11-01 03:40:00]
报警的意思是每分钟超过200个sql被kill，是一个严重告警级别，会打电话给DBA。大半夜报警的确令人不爽，那么如何解决这个问题呢？
通过检查日志，我们发现被kill的sql都是delete语句。业务方其实会定时的跑删除任务，这个任务涉及到N 多个表，删除任务持续时间比较长，所以白天和晚上都有一定概率会触发 sql-killer ,然后报警。
在有赞的数据库运维体系中，每个实例都会配置一个 sql-killer 的实时工具，用于kill query 超过指定阈值的sql请求(类似pt-killer)。
二 初步分析
在之前的案例分析过程中，碰到过因为长事务导致特定表上面的查询耗时增加的问题。经分析发现，这次被kill的SQL 是分布在各个表上面，而且查询发现并不存在长事务。
分析问题发生时候的数据库快照信息，QPS 都很低，除了差不多10 TPS 的DELETE和几十的SELECT，没有发现有问题的SQL。
分析当时的show engine innodb status 的信息，发现每次出问题的时候都会出现一些latch的等待，如下图所示。
找到对应的代码行数
看代码锁位置像是在等待各种Buffer Pool的各种latch。为啥会等待在这里呢，又没有DDL相关的SQL，于是百思不得其解。问题诊断一时间陷入困境。
三 抽丝剥茧
由于等待和Buffer Pool的各种latch相关，而且delete操作本身会产生大量脏数据，那会不会跟刷脏页操作相关呢？
我们看下SQL被kill的量和刷脏页的量之间的关系
发现每秒刷脏页的量和SQL被kill的量的曲线有点相近，看着刷脏页的量挺大的，但是每秒DELETE的TPS又不是很高，为啥这么低的TPS会让刷脏页频率抖动以及SQL执行变慢呢？
曾经换过不同批次的机器，发现问题依旧，并没有改善，说明并不是机器本身的问题。
继续浏览buffer pool相关的监控指标，像是发现新大陆一样的发现了一个异常指标
脏页比例达到了快90%！！！太吓人了！！！
为啥脏页比例会达到90%呢，无非就是刷脏页的速度跟不上产生的速度，要么就是IO能力不行，要么就是产生脏页的速度过快，要么就是内存池太小，导致Buffer Pool被脏页占满。
那么这个脏页比例达到快90% 会有什么问题呢？
MySQL有两个关于脏页的参数
-
# yzsql 3306 param dirty -
Variable_name Value -
innodb_max_dirty_pages_pct 75.000000 -
innodb_max_dirty_pages_pct_lwm 50.000000
我们查看下官方定义
innodb_max_dirty_pages_pct :
InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value. The default value is 75.
innodb_max_dirty_pages_pct 是为了避免脏页比例大于75%，改变该参数不会影响刷脏页的速度。
innodb_max_dirty_pages_pct_lwm：
Defines a low water mark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio. The default of 0 disables the pre-flushing behavior entirely.
innodb_max_dirty_pages_pct_lwm 表示的是当脏页比例达到该参数表示的低水位时候，刷脏线程就开始预刷脏来控制脏页比例，避免达到innodb_max_dirty_pages_pct 。刷脏页的最大IO能力是受innodb_io_capacity和 innodb_io_capacity_max 控制。
生产上我们将innodb_max_dirty_pages_pct_lwm设置成了50
当脏页比例大于 innodb_max_dirty_pages_pct 时候，InnoDB 会进行非常激烈的刷脏页操作，但是由于DELETE操作还是在进行，脏页产生的速度还是非常快，刷脏页的速度还是跟不上脏页产生的速度。为了避免脏页比例进一步扩大，更新将会被堵塞，从而导致DELETE 执行变慢，直至被KILL。
发现问题之后，根据我们之前的假设，有三种解决方案：
-
调大 io_capacity ，但是由于主机是多实例部署，IO占用已经比较高，PASS。
-
降低脏页产生速度，也就是调低DELETE速度，因为一天的速度产生很快，为了避免删除跟不上插入的速度，也被PASS
-
调大Buffer Pool，可以容纳更多的脏页。
说干就干，得益于MySQL 5.7的在线调整Buffer Pool，立马将Buffer Pool Size扩了一倍，效果非常显著
脏页比例立马下降，被kill的SQL也下降了。平均SQL rt下降很多。
四 总结
得益于MySQL的开源，很多错误都可以直接确认到对应的代码，大致定位到问题发生的地方，给问题排查带来了很多方便。同时对MySQL buffer pool 的命中率以及脏页比例也要多多关注，对SQL的性能都有很大的影响。
转载自爱可生公众号
