共计 4911 个字符，预计需要花费 13 分钟才能阅读完成。
作者：徐文梁
爱可生 DBA 成员，负责客户我的项目的需要和保护。目前在数据库新手村打怪降级中。喜爱垂钓，如果你也喜爱垂钓，能够约个晴好天气，咱们一边钓鱼一边聊聊数据库，岂不快哉。
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
问题景象
某天早上，正在搬砖，客户发来音讯，反馈某个实例主从延时值重复在一万多到 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)对于解救，也即是第三个问题，当然是隔靴搔痒了，利用排查阶段找出的信息，让业务侧去革新了。如果业务侧固执利落，拒不革新，下次早晨中午收到告警的时候先一个电话打给业务人员，要熬夜一起熬，哈哈，开个玩笑。DBA 同学大多数都像我一样性情温和的。好了，就到这里吧。
