一、问题描述
造成主主从复制延迟的原因有很多种,这次我们就讲一个大查询导致主从复制延迟不断增大的案例。
二、问题处理
1、发现告警,我们的第一反应去抓取问题现场。我们发现当前会话正在执行几个效率比较差的查询,但当前实例整体负载很低,排除是因为只读实例配置不高,负载大导致的延迟。
2、排除只读实例性能问题后,我们猜测是不是因为主实例有大事务或者消耗较大的DDL操作导致了延迟,告警开始是从17:11开始的,所以我们主要排查这个时间段左右主实例是否存在异常。
1)观察主实例相关性能趋势,发现对应时间段并没有发生什么大事务,排除大事务导致的延迟
2)通过SQL审计,我们寻找对应时间段是否存在消耗较大的DDL,通过SQL审计,只观察到一个对视图修改的alter操作,但是这个alter操作仅仅是修改了一视图的定义,执行消耗很小,从执行时间上我们也可以看到,只需要1.9ms,该DDL的执行消耗应该是挺小的,第一反应认为该DDL不会造成这么大的延迟。
3、在排查问题中,碰到了一个比较诡异的现象。我去查询大查询的执行计划时,会话显示的状态是waiting for table matedta lock,SQL无法获取到MDL锁。所以该MDL锁现在是被谁占用着呢?从该会话界面来看,这些会话显然都不是这个MDL锁阻塞者。
4、仔细查看主实例DDL执行成功的时间,以及备库开始出现延迟的时间,发现时间是基本一致的,再结合我们看到的奇怪的MDL锁等待的现象,问题的原因就浮出水面了。
5、17:51:59 延迟仍然没有恢复,kill掉了会话中很久没有执行完的大查询,17:53只读实例延迟恢复。为什么kill掉大查询后延迟就恢复了呢?
二、问题分析
对整个复制延迟以及恢复的流程我们可以概括如下:
1)17:10 主库执行了ALTER ALGORITHM=UNDEFINED DEFINER=xxx@% SQL SECURITY DEFINER VIEW view_order_logistics_new 的一个操作。
2)只读实例执行了SELECT view_order_logistics_new的查询,但是SQL效率比较慢,一直在Sending data状态,此时主库执行alter操作传输到只读实例,SQL线程应用relaylog复现主实例的alter操作,但是alter操作需要获取MDL写锁,由于大查询一直处于Sending data状态,alter操作无法获取MDL锁,等待锁资源,此时主实例不断有binlog日志传输过来,造成了只读实例的延迟不断增大。
3)17:51:59 只读实例kill掉了view_order_logistics_new的查询,17:53:08 只读实例复制恢复正常。
4)17:51:59 - 17:53:08期间只读实例在应用这个期间的所有binlog日志,可以看到这个时间段TPS很高。
三、总结
从上面的分析流程以及截图来看,造成只读实例延迟的原因就是大查询阻塞了DDL操作,只要大查询不结束,DDL操作就无法获取到MDL锁,复制延迟就会不断增加。从这个案例中我们可以知道,即使一个DDL的执行时间消耗是很小,它可能带来的代价也是蛮大的。那么针对本案例中的情况,我们如何避免这种情况再次发生呢?
1)DDL尽量在业务低峰执行,避免对线上业务的影响
2)对慢SQL进行优化,提升SQL执行效率