故障分析 | MySQL 主从延时值反复跳动

戏说数据那点事 2023-02-10

313

问题现象

某天早上，正在搬砖，客户发来消息，反馈某个实例主从延时值反复在一万多到0之间来回跳动，如下:

手动执行 show slave status\G 命令查看 Seconds_Behind_Master 延时值，结果如下：

问题定位

接到问题，作为一个认真工作的我，立马行动起来。首先确认了下客户的数据库版本，客户反馈是 5.7.31 ，紧接着找客户确认了下复制方式，如下图：

客户现场的 slave_parallel_type 值为 DATABASE ，slave_parallel_workers 值为0，此时主从同步使用的是单 SQL 线程方式，在遇到大事务时产生延迟的可能性更大。推荐客户换成 MTS 方式，但是客户反馈之前一直使用的是该种方式，之前未发生此种现象，需要排查原因。

于是和客户确认异常期间是否有业务变动，客户反馈发生问题之前有新业务上线，qps相对平时大很多，同时存在大量insert和update批量写操作，另外，客户服务器使用的云服务器，配置不高。出现问题后，新业务已经临时下线。

了解背景后，就有了排查的方向了。从根源进行分析，second-behind-master 值与三个值有关，

1. 当前从服务器的系统时间。

2. 从库服务器和主库服务器的系统时间的差值。

3. mi->last_timestamp。

对于单线程模式下的dml操作，记录在 binlog 中，query_event 的 ev->exec_time 基本为0，可以忽略，因为query_event的exec_time只记录第一条数据更改消耗的时间，且我们一般看到是 begin 。所以 last_master_timestamp 就基本等于各个 event 中 header 的 timestamp 。一个事务中，GTID_EVENT 和 XID_EVENT 中记录的是提交时刻的时间，而对于其他 event 都是命令发起时刻的时间，此时 second-behind-master 的计算方式为从库服务器时间-各个 event 中 header 中 time stamp 的时间-主从服务器时间差，因此如果存在长时间未提交的事务或者存在大事务在 SQL 线程应用的时候可能会观察到 seconds_behind_master 的瞬间跳动。

由于目前新业务已经下线，业务量已经渐渐恢复到正常状态，故暂未做其他处理，建议客户观察一段时间，一段时间后客户反馈恢复正常，到此，问题解决了。

问题思考

问题解决了，但是爱琢磨的我却陷入了思考。脑海中浮现出几个问题：

第一，怎样尽可能避免这种现象？

第二，怎么确定是否有长时间未提交的事务和大事务呢？

第三，发现这种问题如何挽救呢?

其实从事务发展历程来看，这三个问题也恰恰对应着问题处理过程中的预防，诊治，治疗三个阶段。

对于预防，即第一个问题，可以从以下几个点出发：

1. 生产环境条件允许的情况下建议开启并行复制。

2. 在业务上线前进行业务量评估和 SQL 审核，避免某些不规范 SQL 或业务逻辑导致出现上述问题。

对于排查，即第二个问题，排查长时间未提交的事务或者大事务可以通过 show processlist 命令或查看 information_schema.innodb_trx 表进行排查，但是这个只能查询当前的事务，对于历史的则无法进行查找，此时可以通过 mysqlbinlog 或者 my2sql 工具解析 binlog 日志，但是结果往往不直观，咨询了一些前辈，推荐了一款 infobin 工具，自己测试了下还是挺好用的，使用示例：

执行命令 infobin mysql-bin.000005 20 2000000 10 -t >/root/result.log

其中，mysql-bin.000005 表示需要解析的 binlog 文件名，20表示是分片数量,将 binlog 分为大小相等的片段，生成时间越短则这段时间生成 binlog 量越大，则事务越频繁，2000000表示将大于2M左右的事务定义为大事务，10表示将大于10秒未提交的事务定义为长期未提交的事务，-t 表示不做详细 event 解析输出，仅仅获取相应的结果。

输出结果如下：

#表示是小端平台
Check is Little_endian
[Author]: gaopeng [QQ]:22389860 [blog]:http://blog.itpub.net/7728585/
Warning: This tool only Little_endian platform!
Little_endian check ok!!!
-------------Now begin--------------
#MySQL的版本
Check Mysql Version is:5.7.25-log
#binlog格式版本
Check Mysql binlog format ver is:V4
#binlog不在写入
Check This binlog is closed!
#binlog文件总大小，单位字节
Check This binlog total size:399899873(bytes)
#load data infile场景不做检查
Note:load data infile not check!
-------------Total now--------------
#事务总数
Trx total[counts]:1345
#event总数
Event total[counts]:58072
#最大的事务大小
Max trx event size:7986(bytes) Pos:560221[0X88C5D]
#平均每秒写binlog大小
Avg binlog size(/sec):610534.125(bytes)[596.225(kb)]
#平均每分钟写binlog大小
Avg binlog size(/min):36632048.000(bytes)[35773.484(kb)]
#binlog分配大小
--Piece view:
(1)Time:1671419439-1671420094(655(s)) piece:19994993(bytes)[19526.359(kb)]
(2)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(3)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(4)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(5)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(6)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(7)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(8)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(9)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(10)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(11)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(12)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(13)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(14)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(15)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(16)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(17)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(18)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(19)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
(20)Time:1671420094-1671420094(0(s)) piece:19994993(bytes)[19526.359(kb)]
#超过大事务规定的事务
--Large than 2000000(bytes) trx:
(1)Trx_size:13310235(bytes)[12998.276(kb)] trx_begin_p:560029[0X88B9D] trx_end_p:13870264[0XD3A4B8]
(2)Trx_size:385990249(bytes)[376943.594(kb)] trx_begin_p:13909131[0XD43C8B] trx_end_p:399899380[0X17D5FAF4]
Total large trx count size(kb):#389941.870(kb)
#超过规定长时间未提交的事务
--Large than 10(secs) trx:
No trx find!
#每张表执行对应操作的binlog大小和次数
--Every Table binlog size(bytes) and times:
Note:size unit is bytes
---(1)Current Table:test.sbtest2::
Insert:binlog size(0(Bytes)) times(0)
Update:binlog size(107440(Bytes)) times(1343)
Delete:binlog size(0(Bytes)) times(0)
Total:binlog size(107440(Bytes)) times(1343)
---(2)Current Table:test.sbtest1::
Insert:binlog size(0(Bytes)) times(0)
Update:binlog size(399300036(Bytes)) times(50001)
Delete:binlog size(0(Bytes)) times(0)
Total:binlog size(399300036(Bytes)) times(50001)
---Total binlog dml event size:399407476(Bytes) times(51344)