一、问题描述

业务开发团队在程序中实现了一个分表操作：从 1 张大表读取数据，经过计算后写入 100 个分表。

这一过程产生了涉及多表写入的大事务，导致主从复制延迟显著增加。初步观察发现，从库的 relay log 大小为 1.6GB（超过默认值 1.1GB），且从库的 INFORMATION_SCHEMA.INNODB_TRX 表显示存在插入行数较多的大事务，插入行数量动态变化，表明从库正面临较大的复制压力。

二、主从延迟状态

从库落后于主库 37325 秒，主从相差 4 个 binlog。

三、排查过程

1、检查从库事务

首先查询了从库的 INFORMATION_SCHEMA.INNODB_TRX 表，以查看当前活跃事务的状态。结果显示存在插入行数较多的大事务，且事务数量不断变化，初步确认大事务是导致延迟的潜在原因。

2、检查从库relay log大小

检查从库的 relay log，发现其大小为 1.6GB，大于默认的 1.1GB。

3、解析binlog以查看数据更改行数

shell> mysqlbinlog --base64-output=decode-rows -vv mysql-bin.003731 | awk \
'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \
{if(match($0, /^#.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \
else if (match($0, /(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT"; s_count=s_count+1;} \
else if (match($0, /(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE"; s_count=s_count+1;} \
else if (match($0, /(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE"; s_count=s_count+1;} \
else if (match($0, /^(# at) /) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type=""; s_count=0; } \
else if (match($0, /^(COMMIT)/)) {print count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0} } ' > 003731.txt

分析输出文件 003731.txt，发现事务大多数都是插入、更新或删除行均为 1 行、2 行、3 行、4 行。

这并非是一次性插入、更新或删除几十上百万行的大事务，统计修改行数的结果如下：

4、解析binlog以评估事务大小

# 
shell> mysqlbinlog   mysql-bin.003731 | grep "^BEGIN" -A 2 |grep -E '^# at' |awk '{print $3}' |awk 'NR==1 {at=$1} NR > 1 {print ($1-at);at=$1}' |sort -n -r |head -n 20 > 003731_top_20.txt

可以看到 binlog 中有 2 个大事务，每个大事务 700 多 MB，与先前的 relay log 大小 1.6G 相吻合

5、使用mysql2sql分析大事务

为了更直观地分析大事务的细节，我们使用了 my2sql[1] 工具对 binlog 进行解析。

#
shell> ./my2sql -user repuser -password repuserpassword -host 10.235.98.18 -port 3306 -work-type stats -start-file mysql-bin.003731 -stop-file mysql-bin.003735 -big-trx-row-limit 5000 -output-dir /tmp/log/my2sql_output