记一次备库延迟的案例-阿里云开发者社区

开发者社区> 数据库> 正文

记一次备库延迟的案例

简介:  造成主主从复制延迟的原因有很多种,这次我们就讲一个大查询导致主从复制延迟不断增大的案例。

一、问题描述

   造成主主从复制延迟的原因有很多种,这次我们就讲一个大查询导致主从复制延迟不断增大的案例。

二、问题处理

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执行效率
   
   
   

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章