针对大事务问题对业务存储过程改造

针对大事务问题对业务存储过程改造

一、问题描述

1. 问题现象

业务调用存储过程没有成功，发现存在大事务，单独拿出来执行发现问题。

greatsql> INSERT INTO
 `ywdb1`.`t1`(
 `TIMEKEY`,
 `zbbh`,
 `zcbl`,
 `zcblms`,
 `zjzh`,
 `zjzhms`,
 `cbzh`,
 `ljzjzh`,
 `xmbh`,
 `xmmc`,
 `sfgj`,
 `dd`,
 `ddsm`,
 `cb`,
 `cz`,
 `bqzje`,
 `bnzje`,
 `ljzje`,
 `zcjz`,
 `zcje`,
 `bqzzb`,
 `bnjzzb`,
 `bfqj`
)
SELECT
 `TIMEKEY`,
 `zbbh`,
 `zcbl`,
 `zcblms`,
 `zjzh`,
 `zjzhms`,
 `cbzh`,
 `ljzjzh`,
 `xmbh`,
 `xmmc`,
 `sfgj`,
 `dd`,
 `ddsm`,
 `cb`,
 `cz`,
 `bqzje`,
 `bnzje`,
 `ljzje`,
 `zcjz`,
 `zcje`,
 `bqzzb`,
 `bnjzzb`,
 `bfqj`
FROM ywdb2.t2
WHERE
 TIMEKEY = concat(substr(_sj, 1, 4), '-', substr(_sj, 5, 2));
 
ERROR 8532 (HY000): Execute backend SQL on node failed with 1197: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again.

错误日志中体现为：

2025-02-20T00:36:01.969610+08:00 O [Note] [MY-011953] [InnoDB] Page cleaner took 10448ms to flush 2000 pages 
2025-02-20T00:38:26.020404+08:00 O [Note] [MY-011953] [InnoDB] Page cleaner took 8427ms to flush 560 pages 
2025-02-20T00:38:39.690478+08:00 3478 [ERROR] [MY-011735] [Rep] Plugin group_replication reported: '[GCS] gcs_packet's payload is too big. Only packets smaller than 2113929216 bytes can be compressed. Payload size is 3823615463.' 
2025-02-20T00:38:39.690504+08:00 3478 [ERROR] [MY-011735] [Rep] Plugin group_replication reported: '[GCS] Error preparing the message for sending.' 
2025-02-20T00:38:40.092629+08:00 3478 [ERROR] [MY-011614] [Rep] Plugin group_replication reported: 'Error while broadcasting the transaction to the group on session 3478' 
2025-02-20T00:38:41.047249+08:00 3478 [ERROR] [MY-011207] [Rep] Run function 'before_commit' in plugin 'group_replication' failed

二、问题分析

1.错误信息分析

  • ERROR 8532 (HY000)错误信息说明，执行的操作涉及multi-statement transaction，并且所需的存储超出了 max_binlog_cache_size 的限制。
  • 日志（[Error]）：提到 group_replication 报告的消息，具有GCS_packet's payload is too big 的报错。来自 group_replication，强调负载太大，只有小于 2113929216 字节的包可以发送。出现了 before_commit 的运行错误，以至于无法在group_replication 中完成事务。

2.分析参数设置

默认情况下，不建议超过2G以上的大事务。这也是参数层面的限制。

greatsql> SHOW variables WHERE variable_name IN ('group_replication_transaction_size_limit','group_replication_compression_threshold');
+------------------------------------------+------------+
| Variable_name | Value |
+------------------------------------------+------------+
| group_replication_compression_threshold | 1000000 |
| group_replication_transaction_size_limit | 2147483647 |
+------------------------------------------+------------+
2 rows in set (0.00 sec)
  1. group_replication_transaction_size_limit：
    1. 值：2147483647
    2. 含义：这个参数定义了可以被复制的事务的最大大小，单位是字节（bytes）。2147483647 是 2G。
  2. group_replication_compression_threshold：
    1. 值：1000000
    2. 含义：这个参数表示在进行组复制时，事务大小超过该阈值（在这里是 1,000,000 字节，即约 1MB）会被考虑进行压缩。换句话说，只有当事务大小超过 1MB 时，数据才会被压缩以减少网络传输的负担。

查看max_binlog_cache_size的值为4G，max_binlog_cache_size 参数，它限制了在 binlog（binary log）缓存中存储的最大字节数。

greatsql> SHOW variables LIKE '%max_binlog%';
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| max_binlog_cache_size | 4294967296 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 268435456 |
+----------------------------+------------+
3 rows in set (0.02 sec)

3.分析存储过程信息

查看此存储过程相关信息(INFORMATION_SCHEMA_ROUTINES)：

greatsql> SELECT * FROM ROUTINES WHERE ROUTINE_DEFINITION LIKE '%t1%' \G 
*************************** 1. row *************************** 
 SPECIFIC_NAME: sp_t1 
 ROUTINE_CATALOG: def 
 ROUTINE_SCHEMA: ywdb1 
 ROUTINE_NAME: sp_t1 
 ROUTINE_TYPE: PROCEDURE 
 DTD_IDENTIFIER: NULL 
 CHARACTER_MAXIMUM_LENGTH: NULL 
 CHARACTER_OCTET_LENGTH: NULL 
 NUMERIC_PRECISION: NULL 
 NUMERIC_SCALE: NULL 
 DATETIME_PRECISION: NULL 
 CHARACTER_SET_NAME: NULL 
 COLLATION_NAME: NULL 
 ROUTINE_BODY: SQL 
 ROUTINE_DEFINITION: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- 发生错误时回滚事务 
ROLLBACK; 
-- 这里可以添加错误处理逻辑 
END; 
START TRANSACTION;
DELETE FROM t1 WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2));
INSERT INTO `ywdb1`.`t1` (
 `zbbh`,
 `zclb`,
 `zcbl`,
 `zcblms`,
 `zjzh`,
 `zjzhms`,
 `cbzh`,
 `ljzjzh`,
 `xmbh`,
 `xmmc`,
 `sfgj`,
 `dd`,
 `ddsm`,
 `cb`,
 `cz`,
 `bqzje`,
 `bnzje`,
 `ljzje`,
 `zcjz`,
 `zcje`,
 `bqzzb`,
 `bnjzzb`,
 `bfqj`
)
SELECT
 `TIMEKEY`,
 `zbbh`,
 `zclb`,
 `zcbl`,
 `zcblms`,
 `zjzh`,
 `zjzhms`,
 `cbzh`,
 `ljzjzh`,
 `xmbh`,
 `xmmc`,
 `sfgj`,
 `dd`,
 `ddsm`,
 `cb`,
 `cz`,
 `bqzje`,
 `bnzje`,
 `ljzje`,
 `zcjz`,
 `zcje`,
 `bqzzb`,
 `bnjzzb`,
 `bfqj`
FROM ywdb2.t2
WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2))
GROUP BY `TIMEKEY`, `zbbh`, `zclb`, `zcbl`, `zcblms`, `zjzh`, `zjzhms`, `cbzh`, `ljzjzh`, `xmbh`, `xmmc`, `sfgj`, `dd`, `ddsm`, `cb`, `cz`, `bqzje`, `bnzje`, `ljzje`, `zcjz`, `zcje`, `bqzzb`, `bnjzzb`, `bfqj`;
--提交事务
COMMIT;
END
 EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: SQL
 PARAMETER_STYLE: SQL
 IS_DETERMINISTIC: NO
 SQL_DATA_ACCESS: CONTAINS SQL
 SQL_PATH: NULL
 SECURITY_TYPE: DEFINER
 CREATED: 2025-02-17 15:05:54
 LAST_ALTERED: 2025-02-17 15:05:54
 SQL_MODE: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_
ENGINE_SUBSTITUTION
 ROUTINE_COMMENT: 
 DEFINER: root@%
 CHARACTER_SET_CLIENT: utf8mb4
 COLLATION_CONNECTION: utf8mb4_0900_ai_ci
 DATABASE_COLLATION: utf8mb4_0900_bin

而业务上，对ETL表后续的清洗操作，确实涉及大事务的动作。

4.查看涉及表大小

greatsql> SELECT count(*) FROM ywdb1.t1;
+-------------+
| count(*) | 
+-------------+
| 3663246 |
+------------ +
1 rows in set (47.42 sec)
greatsql> SELECT count(*) FROM ywdb2.t2;
+-------------+
| count(*) | 
+-------------+
| 71580710 |
+------------ +
1 rows in set (34 min 54.12 sec)

因为ywdb2.t2长时间没有跑出结果，先直接查看ywdb2.t2的ibd文件大小(单表大小161G)。

$ du -sh *
161G t2.ibd

可以看到不管是delete ，还是依据ywdb2.t2这个大表来做insert ... select ... ,都是比较大体量的大事务，事务行数超过500W条以上 （单月账期的数据量）。

三、存储过程改造方法

1.改造思路

改写应用程序，利用频繁提交等方式，将大事务变为小事务。

2.改造方式

INSERT...SELECT...语句，存储过程内改成循环，并一段10000条提交一次。（使用游标）

以存储过程ywdb1.sp_t1 的修改为例：

greatsql> USE test_db1;
greatsql> CREATE TABLE test_t111 LIKE ywdb1.t1;
greatsql> CREATE TABLE test_t222 LIKE ywdb2.t2;
greatsql>
delimiter //
CREATE DEFINER=`root`@`%` PROCEDURE `test_sp_t1`(_sj varchar(10))
BEGIN 
 -- 声明变量
 DECLARE done int DEFAULT FALSE; 
 
 DECLARE v_timekey varchar(50);
 DECLARE v_zbbh varchar(100) ;
 DECLARE v_zclb varchar(100);
 DECLARE v_zclbms varchar(100);
 DECLARE v_zjzh varchar(100);
 DECLARE v_zjzhms varchar(500);
 DECLARE v_cbzh varchar(100);
 DECLARE v_cbzhms varchar(100);
 DECLARE v_ljzjzh varchar(100);
 DECLARE v_ljzjzhms varchar(100);
 DECLARE v_xmbh varchar(100);
 DECLARE v_xmmc varchar(100);
 DECLARE v_sfgj varchar(100);
 DECLARE v_dd varchar(100);
 DECLARE v_ddsm varchar(100);
 DECLARE v_cb decimal(18,4);
 DECLARE v_cz decimal(18,4);
 DECLARE v_bqzje decimal(18,4);
 DECLARE v_bnzje decimal(18,4);
 DECLARE v_ljzje decimal(18,4);
 DECLARE v_zcjz decimal(18,4);
 DECLARE v_zcje decimal(18,4);
 DECLARE v_bqjzzb decimal(18,4);
 DECLARE v_bnjzzb decimal(18,4);
 DECLARE v_ljjzzb decimal(18,4);
 DECLARE v_bfqj varchar(20);
 
 DECLARE count_num int DEFAULT 0; -- 计数器，用于分批提交
 -- 声明游标，用于从源表中选择数据
 DECLARE cur_sel CURSOR FOR SELECT 
 `TIMEKEY`, 
 `zbbh`, 
 `zclb`, 
 `zclbms`, 
 `zjzh`, 
 `zjzhms`, 
 `cbzh`, 
 `cbzhms`, 
 `ljzjzh`,
 `ljzjzhms`,
 `xmbh`,
 `xmmc`,
 `sfgj`,
 `dd`,
 `ddsm`,
 `cb`,
 `cz`,
 `bqzje`,
 `bnzje`,
 `ljzje`,
 `zcjz`,
 `zcje`,
 `bqjzzb`,
 `bnjzzb`,
 `ljjzzb`,
 `bfqj`
FROM test_db1.test_t222
WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2)); -- 根据传入的日期参数过滤数据
-- 声明游标读取结束时的处理程序 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
-- 删除目标表中符合条件的数据 
DELETE FROM test_t111 WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2)); 
-- 开始事务 
START transaction;
-- 打开游标
 OPEN cur_sel;
 
-- 循环读取游标数据
 read_loop:LOOP
 FETCH cur_sel INTO v_timekey, v_zbbh,v_zclb,v_zclbms,v_zjzh,v_zjzhms,v_cbzh,v_cbzhms,v_ljzjzh,v_ljzjzhms,v_xmbh,v_xmmc,v_sfgj,v_dd,v_ddsm, v_cb,v_cz,v_bqzje,v_bnzje,v_ljzje,v_zcjz,v_zcje,v_bqjzzb, v_bnjzzb,v_ljjzzb,v_bfqj;
 
-- 如果游标读取结束，退出循环 
 IF done THEN
 leave read_loop;
 END IF;
 
-- 计数器加 1 
SET count_num=count_num+1;
-- 将读取的数据插入目标表 
INSERT INTO `test_db1`.`test_t111`(
 `TIMEKEY`,
 `zbbh`,
 `zclb`,
 `zclbms`,
 `zjzh`,
 `zjzhms`,
 `cbzh`,
 `cbzhms`,
 `ljzjzh`,
 `ljzjzhms`,
 `xmbh`,
 `xmmc`,
 `sfgj`,
 `dd`,
 `ddsm`,
 `cb`,
 `cz`,
 `bqzje`,
 `bnzje`,
 `ljzje`,
 `zcjz`,
 `zcje`,
 `bqjzzb`,
 `bnjzzb`,
 `ljjzzb`,
 `bfqj`) VALUES (v_timekey, v_zbbh,v_zclb,v_zclbms,v_zjzh,v_zjzhms,v_cbzh,v_cbzhms,v_ljzjzh,v_ljzjzhms,v_xmbh,v_xmmc,v_sfgj,v_dd,v_ddsm, v_cb,v_cz,v_bqzje,v_bnzje,v_ljzje,v_zcjz,v_zcje,v_bqjzzb, v_bnjzzb,v_ljjzzb,v_bfqj
 );
 
 -- 如果计数器达到 10,000，提交事务并重新开始新事务
 IF count_num=10000 THEN
 COMMIT;
 START transaction;
 SET count_num=0;
 END IF;
 END LOOP read_loop;
 
 -- 提交剩余的事务
 COMMIT;
 
 -- 关闭游标
 CLOSE cur_sel;
END
//
 
delimiter ;
greatsql>
greatsql> CALL test_sp_t1(20250301);

四、总结

改造后的存储过程相较于原存储过程有以下几个优势：

1. 分批次提交事务

  • 原存储过程：在整个操作完成后才提交事务，这意味着在处理大量数据时，事务会占用大量资源，可能导致锁争用、内存占用过高等问题。
  • 改造后存储过程：每处理10000条记录后提交一次事务，减少了单次事务的数据量，降低了锁争用和内存占用，提高了系统的并发性和稳定性。

2. 游标的使用

  • 原存储过程：使用简单的INSERT INTO ... SELECT语句一次性插入所有数据，如果数据量非常大，可能会导致内存溢出或性能下降。
  • 改造后存储过程：使用游标逐条处理数据，适合处理大数据量的场景，能够更好地控制内存使用和性能。

3. 灵活性

  • 改造后存储过程：通过游标和分批次提交的方式，可以更灵活地处理不同规模的数据集，避免了单次操作数据量过大带来的问题。

4. 性能优化

  • 改造后存储过程：通过减少单次事务的数据量和分批提交，减少了数据库的锁争用和内存占用，从而提高了整体性能，特别是在高并发环境下。

5. 可维护性

  • 改造后存储过程：通过使用游标和分批次提交，代码结构更加清晰，便于后续的维护和优化。

​

总之，改造后的存储过程在处理大数据量时具有更好的性能、稳定性和可维护性。通过分批次提交事务、使用游标逐条处理数据以及优化事务提交策略，能够有效降低系统资源的占用，提高并发处理能力，特别适合在高并发、大数据量的场景下使用。

Enjoy GreatSQL ?

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区GiteeGitHubBilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交流群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

作者:GreatSQL原文地址:https://www.cnblogs.com/greatsql/p/18879544
0
分享 2025-05-16

%s 个评论

要回复文章请先登录或注册

关于我们 • 赞助与捐赠 • 站点地图 • 联系我们 • 网站合作 • 免责声明
友情链接： 乐读文化 • 开发者 • MySQL Workbench • 大牛教程 • 交换链接
本站为个人网站，部分内容来自于互联网，版权归原作者所有。如本站内容有侵权，敬请来信联系我们，我们将及时删除清理。
© 2025 大牛教程