当前位置：首页 > ops >正文

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

ops 2025/8/23 15:23:39

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

一、问题描述

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
WHERETIMEKEY = 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：
1. 值：2147483647
2. 含义：这个参数定义了可以被复制的事务的最大大小，单位是字节（bytes）。2147483647 是 2G。
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;
ENDEXTERNAL_NAME: NULLEXTERNAL_LANGUAGE: SQLPARAMETER_STYLE: SQLIS_DETERMINISTIC: NOSQL_DATA_ACCESS: CONTAINS SQLSQL_PATH: NULLSECURITY_TYPE: DEFINERCREATED: 2025-02-17 15:05:54LAST_ALTERED: 2025-02-17 15:05:54SQL_MODE: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_
ENGINE_SUBSTITUTIONROUTINE_COMMENT: DEFINER: root@%CHARACTER_SET_CLIENT: utf8mb4COLLATION_CONNECTION: utf8mb4_0900_ai_ciDATABASE_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:LOOPFETCH 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 THENleave 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 THENCOMMIT;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);