美女架构师教你如何分析主从延迟问题

发布于 2022-7-14 16:06
浏览
0收藏

 

在我们实际工作过程中,会遇到形形色色的数据库问题,有些问题可以迎刃而解,而有的问题处理起来却比较让人揪心,比如说“主从延迟”问题。今天,有颜更有才的腾讯云数据库美女架构师罗鑫,手把手教你分析造成主从延迟的原因和应对策略。

 

首先,我们先来看一个用户主从延迟的案例:

【下面展示步骤为测试环境复现用户主从延迟场景】

 

一、排查步骤

 

1、查询同步延迟状态:
登录RO(只读实例)执行show slave status\G;查询同步状态。执行如下:

美女架构师教你如何分析主从延迟问题-开源基础软件社区

我们通过重复执行show slave status;查看Seconds_Behind_Master值为1897s肯定是延迟了,多次执行查看Seconds_Behind_Master值仍在不断变大,并且没有自动恢复的迹象。
接下来,我们在RO上执行show full processlist;查看当前mysql链接线程都在做些什么,执行结果如下:
| 50 | root | % | db | Connect | 88 | Sending data | select sum(a.column_name)/100 from 【此处省略200行代码】多次执行show full processlist,我们发现“select sum(a.column_name)/100【此处省略】”查询一直在变。

 

2、分析延迟的原因:
2.1)、首先思考什么情况下会导致延迟:

 

主库执行大事务导致从库应用binlog过程慢
Row格式下主库更新无主键的表
主库高并发写,从库单线程回放
数据库参数配置
网络延迟导致
主和RO硬件配置不同
…….
2.2)、首先要看是不是“主库高并发写,从库单线程回放”,为什么先排查这点呢?因为我们在RO上执行几次show slave status;得到Seconds_Behind_Master值在变大,Relay_Log_File: relay-bin.000004值不变,但是Relay_Log_Pos的值在不断的刷新,说明RO是在不断回放Relay_Log只是一直追不上主库而已。
查询主库监控,主库负载很低,监控信息如下。

美女架构师教你如何分析主从延迟问题-开源基础软件社区
很奇怪的现象,主库的负载很低但是RO却有延迟。RO暂时分析不出来,那么我们是不是会想:RO有延迟那透明备机有延迟吗?


登录透明备机执行show slave status;也有延迟,在透明备机上执行show full processlist;查询发现透明备机上居然也有RO上执行的这个SQL:select sum(a.column_name)/100【此处省略】查询也一直在变。既然RO和透明备机都有延迟,说明就是由主库的更新引起的。同时注意到,同步线程执行的账号是root,host是%。如果是一个普通的语句,同步线程执行的账号应该是XXXX,host是具体IP,这个root 很可疑。因此需要查明下面两个问题:


问题1:分析备机有来自root %账号的查询这种情况的原因;
问题2:主库负载不高,备机和RO均有延迟的原因;

 

先说明三个现象:

1. 普通的客户端查询,host字段是不会显示为%的;

2. 从系统上排查,这个实例透明从机并没有开过从机只读;

3. RO和透明从机都有这个语句;

 

从上面三个现象说明, 这个select… 语句是从主库同步过来的;但是普通的select语句是不会同步到备库的,说明这是一个“特殊”的查询语句。会传到备库的查询语句的一种情况:是这个语句写在存储过程中。


怀疑点:用户调用存储过程
带着疑点去数据库中查询是否有调用存储过程:

(1)、查询 mysql.proc查看到用户有存储过程:

美女架构师教你如何分析主从延迟问题-开源基础软件社区

(2)、查询存储过程中是否有执行的该查询的SQL:

美女架构师教你如何分析主从延迟问题-开源基础软件社区

我们可以得出存储过程execLtv中调用了函数execLtvFun,函数execLtvFun
中执行的就是“select sum(a.column_name)/100 ”。

存储过程比较长,这里简单梳理调用过程:

美女架构师教你如何分析主从延迟问题-开源基础软件社区

用户调用存储过程call execLtv(),存储过程通过逻辑判断,执行update。
【UPDATE db.table set column_name1 = execLtvFun(reqDate, gameVersion, sdkChannel, advIdentification) where column_name2 = ltvId;】调用函数execLtvFun,该函数我们上面已经截图,是一个很大的select【select sum(a.column_name)/100……】返回值。

 

这就验证了我们前面的猜想,这个select语句来源于主库的存储过程,并且传给备库执行。
再深入看这个存储过程的实现,update语句会调用函数execLtvFun,函数execLtvFun是一个非常大的查询,执行也会比较耗时。

 

二、解决方案

 

现在知道原因了,备库在执行这个存储过程的时候,也需要调用函数execLtvFun,很耗时。
一个比较简单的做法,就是把主库的binlog_format 修改成row。这样从库就只会同步“更新后的结果”,而不需要同步“更新的计算过程”,也就是不用调用这个耗时函数了。

(1)、当binlog_format=mixed时,binlog结果如下:

美女架构师教你如何分析主从延迟问题-开源基础软件社区

(2)、binlog_format=row时,binlog结果如下:

美女架构师教你如何分析主从延迟问题-开源基础软件社区

通过修改不binlog_format验证结果。当binlog_format=row时解析binlog记录的是修改后的值,从库回放binlog执行每条update语句不需要再去调用一次函数,再观察备机和RO,也看不到之前的select操作,show slave status查询也观察到没有延迟。

 

三、小结

 

1. 主从延迟原因。

2. 通过分析备机有来自root %账号的查询反推调用存储过程。

3. 存储过程调用会fork一个新线程去执行body的内容。
4. binlog_format格式不同,记录的binlog日志不一样,mixed格式将update转译为具体的sql语句执行,每条update都会调用一次函数。而设置row格式后,binlog记录的是主库修改后的数据,在从库回放也是单纯执行update跟修改后的值。
5. 这次是通过改binlog_format=row格式解决主从延迟问题。
6. 思考:把参数binlog_format设置为row格式就是最佳实践吗?【下次分享一个存储过程中delete分区表数据导致的主从延迟案例】

 

罗鑫,腾讯云数据库产品架构师,主要负责腾讯云MySQL、Redis数据库的架构设计和运维工作。

 

文章转自公众号:腾讯云数据库

分类
标签
已于2022-7-14 16:06:26修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐