MySQL 主从延时值反复跳动案例

问题现象

某天早上，正在搬砖，客户发来消息，反馈某个实例主从延时值反复在一万多到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 解析输出，仅仅获取相应的结果。

输出结果如下：

对于挽救，也即是第三个问题，当然是对症下药了，利用排查阶段找出的信息，让业务侧去改造了。如果业务侧顽固拖拉，拒不改造，下次晚上半夜收到告警的时候先一个电话打给业务人员，要熬夜一起熬，哈哈，开个玩笑。DBA同学大多数都像我一样性格温和的。好了，就到这里吧。

参考

https://mp.weixin.qq.com/s/-ldAo7Q0yymQELMchXvIMg