MySQL InnoDB Cluster如何定位或找出超过事务大小的SQL？

在MySQL InnoDB Cluster中，有一个系统变量/参数group_replication_transaction_size_limit控制着事务的大小，如下所示

mysql> select @@global.group_replication_transaction_size_limit;
+---------------------------------------------------+
| @@global.group_replication_transaction_size_limit |
+---------------------------------------------------+
|                                         150000000 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql>

如果数据库中的事务大小超过了系统变量group_replication_transaction_size_limit指定的阈值，事务就会回滚,MySQL的错误日志中会出现类似下面这样的报错信息

2024-05-21T05:20:07.438958+08:00 3726581 [ERROR] [MY-011608] [Repl] Plugin group_replication reported: 'Error on session 3726581. Transaction of size 588366005 exceeds specified limit 150000000. To increase the limit please adjust group_replication_transaction_size_limit option.'
2024-05-21T05:20:07.439097+08:00 3726581 [ERROR] [MY-010207] [Repl] Run function 'before_commit' in plugin 'group_replication' failed

关于系统变量group_replication_transaction_size_limit，官方文档的介绍如下：

Use the system variable group_replication_transaction_size_limit to specify a maximum transaction size that the group accepts. In MySQL 8.0, this system variable defaults to a maximum transaction size of 150000000 bytes (approximately 143 MB). Transactions above this size are rolled back and are not sent to Group Replication s Group Communication System (GCS) for distribution to the group. Adjust the value of this variable depending on the maximum message size that you need the group to tolerate, bearing in mind that the time taken to process a transaction is proportional to its size.

简单翻译如下所示：

使用系统变量 group_replication_transaction_size_limit 指定(MGR)组所能接受的最大事务大小。在 MySQL 8.0 中，此系统变量默认最大事务大小为 150000000 字节（约 143 MB）。超过此大小的事务将会被回滚，并且不会通过组复制的组通信系统 （GCS）分发到其他组成员。 根据您需要组容忍的最大消息大小调整此变量的值，请记住，处理事务所需的时间与其大小成正比。

那么MySQL InnoDB Cluster出现这个错误，怎么定位是哪一个事务的SQL超过大小限制而回滚了呢？ 因为找到问题的根源才是解决问题的前提。下面总结一下如何定位超过事务大小的SQL语句。

在展开话题前，我们先来了解一下“事务大小（Transaction of size）”这个概念。一般而言，其他数据库一般会有大事务或小事务，长事务与短事务的概念，其实它们都是通过事务执行了多长时间来衡量判断一个事务的大小与长短，（长事务与短事务，大事务与小事务）其实只是不同的名词而已，个人觉得它们本质上就是一回事。但是很少数据库有“事务大小（Transaction of size）”这个概念，那么MySQL中事务的大小到底是指啥呢？ 说白了，其实它指的是事务生成的binlog的大小。有兴趣就参考一下Query and Transaction size in MySQL^[1]这篇文章。

那么有没有一个系统表或视图能够找出事务的大小呢？很遗憾，至少到目前为止，还没有哪一个系统表或视图包含事务的大小信息，我们计算事务大小的唯一方法就是通过binlog来分析。在MySQL 8.0.2之前，我们通过解析binlog获取事务开始与结束时binlog的大小来计算。而从MySQL 8.0.2 起，GTID 事件带有一个新字段：transaction_length。其值表示以字节为单位的完整事务大小，从 GTID 事件本身的开始到事务的最后一个事件的结束。

那么在开始解析binlog前先回答一个问题： “回滚事务的相关信息会记录到binlog中吗？”。答案是回滚事务的信息不回记录到binlog中。其实这里要分两种情况：

1:事务里面都是事务表的话，那么如果事务回滚了，它是不会写到binlog中去。

2：事务里面修改了非事务表(nontransactional tables)的话,即使事务回滚了，它依然会写binlog。

这里就不展开了，有兴趣，自己构建一个简单的实验，然后解析一下binlog就能验证一些你的看法。那接下来就麻烦了，因为回滚事务的相关信息没有记录到binlog，那就没法展开分析了。关于这个问题，基本上有下面几种方案解决：

1： 临时调整生产环境中系统变量group_replication_transaction_size_limit的大小，让这些大事务写入binlog中，那么就可以展开后续分析工作了。完成采集后，将系统变量group_replication_transaction_size_limit调整回原来的值。

2：如果UAT环境也能重现这个问题，那么在UAT环境调整系统变量。 这个方案比较稳妥可靠一些。

接下来，我们可以使用博客技术分享 | 如何通过 binlog 定位大事务？^[2]中的脚本来找出事务大小，如下所示

mysqlbinlog /data/mysql/bin_logs/mysql_binlog.000102 | grep "GTID$(printf '\t')last_committed" -B 1 \
 | grep -E '^# at' | awk '{print $3}' \
 | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
 | sort -n -r | head -n 10

但是这个脚本只获取了事务的大小，没有获取事务的其他信息，那么我们要怎么才能获取更多一点的详细信息呢？我们改写一下脚本，

mysqlbinlog /data/mysql/bin_logs/mysql_binlog.000102 | grep "GTID$(printf '\t')last_committed" -B 1 \
       | grep -E '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp,tmp);tmp=$1}'  \
       | sort -n -r -k 1 | head -n 10

如下截图所示，第二列值为binlog中的pos

如上所示， 我们获取了事务开始时的位置信息。然后我们就开始将binlog解析出来，然后在kerry_bigtrans.log中搜索关键字“at 591932661”

$ mysqlbinlog --base64-output=DECODE-ROWS  -vv /data/mysql/bin_logs/mysql_binlog.000102 > kerry_bigtrans.log

如下所示，这个案例搜索到下面内容

# at 591932661
#240527  5:00:06 server id 1  end_log_pos 591932746 CRC32 0x46f449e8    Anonymous_GTID  last_committed=96110    sequence_number=96111   rbr_only=yes    original_committed_timestamp=1716757206761059   immediate_commit_timestamp=1716757206761059     transaction_length=198288631

这里面有几个信息，红色标记部分，binlog中事务的开始位置591932661，last_committed=96110 transaction_length=198288631 ，那么我们只需要从这里开始分析，到last_committed=96111结束部分的内容即可。如下所示

........................................................
# at 790221261
#240527  5:00:06 server id 1  end_log_pos 790221292 CRC32 0x760d2231    Xid = 44754468
COMMIT/*!*/;
# at 790221292
#240527  5:00:09 server id 1  end_log_pos 790221371 CRC32 0xbdf9ddcf    Anonymous_GTID  last_committed=96111    sequence_number=96112   rbr_only=yes    original_committed_timestamp=1716757209269202   immediate_commit_timestamp=1716757209269202     transaction_length=429

由于解析出来的SQL不是原始的SQL语句，如果可以的话，可以临时开启MySQL通用查询日志，结合起来就容易定位到超过事务大小限制的原始SQL语句了。关于我这个案例中，其实就是一些简单的UPDATE语句造成的。具体原因是因为这些表中存储了图片，从而导致事务大小变得很大，其实UPDATE语句中并不涉及图片相关字段，但是因为系统变量binlog_row_image值是默认值FULL，所以binlog会记录列的所有修改，即使字段没有发生变更也会记录。这样，如果表中存储了图片，那么事务大小就变得很大了。这个也是 反对将图片存储到数据库中的缘由之一。至于为什么这个数据库会存储图片呢？ 这个系统是公司从供应商购买的一个系统，我们DBA的话语权也非常小。即使我们早期邮件指出过这个问题，给出过建议，也不了了之。总之是一个曲折的故事。有些话也不便挑明。

那么这里还有种解决方法是调整系统变量group_replication_transaction_size_limit的大小。当然可以调整其大小来解决这个问题，但是这个是有风险的。官方给出这个默认值也肯定是经过大量的测试验证的。所以不建议通过调整系统变量group_replication_transaction_size_limit的大小。这里不展开讨论这个话题。其实最根本的原因还是在于这个问题的根源是设计不合理，为什么不从根源上解决问题？而要去修改系统变量去弥补一个错误的设计问题呢？

SQL语句找到了，解决方案也有几个：

• 1：修改设计，将图片存储到文件系统，不要存储到数据库。那么自然而然事务的大小就变得很小了。
• 2：修改业务逻辑，一次更新少量记录，避免事务大小超过阈值。
• 3：修改系统binlog_row_image为MINIMAL，让binlog只记录修改列的值。这样减少binlog记录信息，减小事务大小。

其实修改系统变量binlog_row_image为MINIMAL，查了一下资料，似乎也没有问题，但是也不敢保证就不会带来什么问题。就这几种方案来说，最好的方案还是方案1.这个方案测试上上策。

参考资料

[1]

Query and Transaction size in MySQL: https://mp.weixin.qq.com/s?__biz=Mzg2OTAwMTE3NQ==&mid=2247488703&idx=1&sn=7faa397616c52b663517d73fa844a1e4&chksm=cea2e607f9d56f116abf2499f656ff13ed744e5afeda9c490364fbf6b7ad3ebdee1f33fdc924&token=211971234&lang=zh_CN#rd

[2]

技术分享 | 如何通过 binlog 定位大事务？: https://mp.weixin.qq.com/s/lR3CZyM8_Mz0nGC53MxcKg