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

头像
GreatSQL社区
     福建
    阅读 9 分钟

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

    一、问题描述

    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社区 Gitee GitHub Bilibili

    GreatSQL社区：

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

    image-20230105161905827

    技术交流群：

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

    image-20221030163217640

    数据库mysql
    GreatSQL社区
    8 声望6 粉丝

    GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

    « 上一篇
    在Oracle到GreatSQL迁移中排序规则改变引发的乱码问题分析及解决

    引用和评论

    推荐阅读
    头像
    从 12s 到 200ms，MySQL 两千万数据 6 种深度分页优化

    码哥字节赞 4阅读 1.9k

    头像
    Open WebUI：开源AI交互平台的全面解析

    DBLens赞 5阅读 1.8k

    头像
    被 Manus 带火的 MCP 是什么｜一文看懂

    Bytebase赞 3阅读 3.7k

    头像
    MySQL × 向量数据库：大模型时代的黄金组合实战指南

    DBLens赞 5阅读 1.8k

    头像
    53 倍性能提升！TiDB 全局索引如何优化分区表查询？

    PingCAP赞 2阅读 857

    头像
    百万级群聊的设计实践

    vivo互联网技术赞 1阅读 1.2k

    头像
    分布式数据库解析

    风流倜傥的伤痕赞 1阅读 1.1k

    0 条评论
    得票最新