针对大事务问题对业务存储过程改造
一、问题描述
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)
- group_replication_transaction_size_limit：
- 值：2147483647
- 含义：这个参数定义了可以被复制的事务的最大大小，单位是字节（bytes）。2147483647 是 2G。
- group_replication_compression_threshold：
- 值：1000000
- 含义：这个参数表示在进行组复制时，事务大小超过该阈值（在这里是 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. 可维护性
- 改造后存储过程：通过使用游标和分批次提交，代码结构更加清晰，便于后续的维护和优化。
总之，改造后的存储过程在处理大数据量时具有更好的性能、稳定性和可维护性。通过分批次提交事务、使用游标逐条处理数据以及优化事务提交策略，能够有效降低系统资源的占用，提高并发处理能力，特别适合在高并发、大数据量的场景下使用。
7万+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
