登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›针对大事务问题对业务存储过程改造

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

遏筒煽 2025-5-30 13:14:19
针对大事务问题对业务存储过程改造

一、问题描述

1. 问题现象

业务调用存储过程没有成功，发现存在大事务，单独拿出来执行发现问题。
  1. greatsql> INSERT INTO
  2.   `ywdb1`.`t1`(
  3.   `TIMEKEY`,
  4.   `zbbh`,
  5.   `zcbl`,
  6.   `zcblms`,
  7.   `zjzh`,
  8.   `zjzhms`,
  9.   `cbzh`,
  10.   `ljzjzh`,
  11.   `xmbh`,
  12.   `xmmc`,
  13.   `sfgj`,
  14.   `dd`,
  15.   `ddsm`,
  16.   `cb`,
  17.   `cz`,
  18.   `bqzje`,
  19.   `bnzje`,
  20.   `ljzje`,
  21.   `zcjz`,
  22.   `zcje`,
  23.   `bqzzb`,
  24.   `bnjzzb`,
  25.   `bfqj`
  26. )
  27. SELECT
  28.   `TIMEKEY`,
  29.   `zbbh`,
  30.   `zcbl`,
  31.   `zcblms`,
  32.   `zjzh`,
  33.   `zjzhms`,
  34.   `cbzh`,
  35.   `ljzjzh`,
  36.   `xmbh`,
  37.   `xmmc`,
  38.   `sfgj`,
  39.   `dd`,
  40.   `ddsm`,
  41.   `cb`,
  42.   `cz`,
  43.   `bqzje`,
  44.   `bnzje`,
  45.   `ljzje`,
  46.   `zcjz`,
  47.   `zcje`,
  48.   `bqzzb`,
  49.   `bnjzzb`,
  50.   `bfqj`
  51. FROM ywdb2.t2
  52. WHERE
  53.   TIMEKEY = concat(substr(_sj, 1, 4), '-', substr(_sj, 5, 2));
  54.   
  55. 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.
复制代码
错误日志中体现为：
  1. 2025-02-20T00:36:01.969610+08:00 O [Note] [MY-011953] [InnoDB] Page cleaner took 10448ms to flush 2000 pages  
  2. 2025-02-20T00:38:26.020404+08:00 O [Note] [MY-011953] [InnoDB] Page cleaner took 8427ms to flush 560 pages  
  3. 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.'  
  4. 2025-02-20T00:38:39.690504+08:00 3478 [ERROR] [MY-011735] [Rep] Plugin group_replication reported: '[GCS] Error preparing the message for sending.'  
  5. 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'  
  6. 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以上的大事务。这也是参数层面的限制。
  1. greatsql> SHOW variables WHERE variable_name IN ('group_replication_transaction_size_limit','group_replication_compression_threshold');
  2. +------------------------------------------+------------+
  3. | Variable_name                            | Value      |
  4. +------------------------------------------+------------+
  5. | group_replication_compression_threshold  | 1000000    |
  6. | group_replication_transaction_size_limit | 2147483647 |
  7. +------------------------------------------+------------+
  8. 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）缓存中存储的最大字节数。
  1. greatsql> SHOW variables LIKE '%max_binlog%';
  2. +----------------------------+------------+
  3. | Variable_name              | Value      |
  4. +----------------------------+------------+
  5. | max_binlog_cache_size      | 4294967296 |
  6. | max_binlog_size            | 1073741824 |
  7. | max_binlog_stmt_cache_size | 268435456  |
  8. +----------------------------+------------+
  9. 3 rows in set (0.02 sec)
复制代码
3.分析存储过程信息

查看此存储过程相关信息(INFORMATION_SCHEMA_ROUTINES)：
  1. greatsql> SELECT * FROM ROUTINES WHERE ROUTINE_DEFINITION LIKE '%t1%' \G  
  2. *************************** 1. row ***************************  
  3.             SPECIFIC_NAME: sp_t1  
  4.           ROUTINE_CATALOG: def  
  5.            ROUTINE_SCHEMA: ywdb1  
  6.              ROUTINE_NAME: sp_t1  
  7.              ROUTINE_TYPE: PROCEDURE  
  8.            DTD_IDENTIFIER: NULL  
  9. CHARACTER_MAXIMUM_LENGTH: NULL  
  10.    CHARACTER_OCTET_LENGTH: NULL  
  11.         NUMERIC_PRECISION: NULL  
  12.             NUMERIC_SCALE: NULL  
  13.        DATETIME_PRECISION: NULL  
  14.        CHARACTER_SET_NAME: NULL  
  15.            COLLATION_NAME: NULL  
  16.              ROUTINE_BODY: SQL  
  17.        ROUTINE_DEFINITION: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- 发生错误时回滚事务  
  18. ROLLBACK;  
  19. -- 这里可以添加错误处理逻辑  
  20. END;  
  21. START TRANSACTION;
  22. DELETE FROM t1 WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2));
  23. INSERT INTO `ywdb1`.`t1` (
  24.   `zbbh`,
  25.   `zclb`,
  26.   `zcbl`,
  27.   `zcblms`,
  28.   `zjzh`,
  29.   `zjzhms`,
  30.   `cbzh`,
  31.   `ljzjzh`,
  32.   `xmbh`,
  33.   `xmmc`,
  34.   `sfgj`,
  35.   `dd`,
  36.   `ddsm`,
  37.   `cb`,
  38.   `cz`,
  39.   `bqzje`,
  40.   `bnzje`,
  41.   `ljzje`,
  42.   `zcjz`,
  43.   `zcje`,
  44.   `bqzzb`,
  45.   `bnjzzb`,
  46.   `bfqj`
  47. )
  48. SELECT
  49.   `TIMEKEY`,
  50.   `zbbh`,
  51.   `zclb`,
  52.   `zcbl`,
  53.   `zcblms`,
  54.   `zjzh`,
  55.   `zjzhms`,
  56.   `cbzh`,
  57.   `ljzjzh`,
  58.   `xmbh`,
  59.   `xmmc`,
  60.   `sfgj`,
  61.   `dd`,
  62.   `ddsm`,
  63.   `cb`,
  64.   `cz`,
  65.   `bqzje`,
  66.   `bnzje`,
  67.   `ljzje`,
  68.   `zcjz`,
  69.   `zcje`,
  70.   `bqzzb`,
  71.   `bnjzzb`,
  72.   `bfqj`
  73. FROM ywdb2.t2
  74. WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2))
  75. 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`;
  76. --提交事务
  77. COMMIT;
  78. END
  79.            EXTERNAL_NAME: NULL
  80.        EXTERNAL_LANGUAGE: SQL
  81.          PARAMETER_STYLE: SQL
  82.         IS_DETERMINISTIC: NO
  83.          SQL_DATA_ACCESS: CONTAINS SQL
  84.                 SQL_PATH: NULL
  85.            SECURITY_TYPE: DEFINER
  86.                  CREATED: 2025-02-17 15:05:54
  87.             LAST_ALTERED: 2025-02-17 15:05:54
  88.                 SQL_MODE: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_
  89. ENGINE_SUBSTITUTION
  90.          ROUTINE_COMMENT:
  91.                  DEFINER: root@%
  92.     CHARACTER_SET_CLIENT: utf8mb4
  93.     COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  94.       DATABASE_COLLATION: utf8mb4_0900_bin
复制代码
而业务上，对ETL表后续的清洗操作，确实涉及大事务的动作。
4.查看涉及表大小
  1. greatsql> SELECT count(*) FROM ywdb1.t1;
  2. +-------------+
  3. |  count(*)   |
  4. +-------------+
  5. |  3663246   |
  6. +------------ +
  7. 1 rows in set (47.42 sec)
  8. greatsql> SELECT count(*) FROM ywdb2.t2;
  9. +-------------+
  10. |  count(*)   |
  11. +-------------+
  12. |  71580710   |
  13. +------------ +
  14. 1 rows in set (34 min 54.12 sec)
复制代码
因为ywdb2.t2长时间没有跑出结果，先直接查看ywdb2.t2的ibd文件大小(单表大小161G)。
  1. $ du -sh *
  2. 161G t2.ibd
复制代码
可以看到不管是delete ，还是依据ywdb2.t2这个大表来做insert ... select ...  ,都是比较大体量的大事务，事务行数超过500W条以上 （单月账期的数据量）。
三、存储过程改造方法

1.改造思路

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

INSERT...SELECT...语句，存储过程内改成循环，并一段10000条提交一次。（使用游标）
以存储过程ywdb1.sp_t1 的修改为例：
  1. greatsql> USE test_db1;
  2. greatsql> CREATE TABLE test_t111 LIKE ywdb1.t1;
  3. greatsql> CREATE TABLE test_t222 LIKE ywdb2.t2;
  4. greatsql>
  5. delimiter //
  6. CREATE DEFINER=`root`@`%` PROCEDURE `test_sp_t1`(_sj varchar(10))
  7. BEGIN
  8.   -- 声明变量
  9.   DECLARE done int DEFAULT FALSE;
  10.   
  11.   DECLARE v_timekey varchar(50);
  12.   DECLARE v_zbbh varchar(100) ;
  13.   DECLARE v_zclb varchar(100);
  14.   DECLARE v_zclbms varchar(100);
  15.   DECLARE v_zjzh varchar(100);
  16.   DECLARE v_zjzhms varchar(500);
  17.   DECLARE v_cbzh varchar(100);
  18.   DECLARE v_cbzhms varchar(100);
  19.   DECLARE v_ljzjzh varchar(100);
  20.   DECLARE v_ljzjzhms varchar(100);
  21.   DECLARE v_xmbh varchar(100);
  22.   DECLARE v_xmmc varchar(100);
  23.   DECLARE v_sfgj varchar(100);
  24.   DECLARE v_dd varchar(100);
  25.   DECLARE v_ddsm varchar(100);
  26.   DECLARE v_cb decimal(18,4);
  27.   DECLARE v_cz decimal(18,4);
  28.   DECLARE v_bqzje decimal(18,4);
  29.   DECLARE v_bnzje decimal(18,4);
  30.   DECLARE v_ljzje decimal(18,4);
  31.   DECLARE v_zcjz decimal(18,4);
  32.   DECLARE v_zcje decimal(18,4);
  33.   DECLARE v_bqjzzb decimal(18,4);
  34.   DECLARE v_bnjzzb decimal(18,4);
  35.   DECLARE v_ljjzzb decimal(18,4);
  36.   DECLARE v_bfqj varchar(20);
  38.   DECLARE count_num int DEFAULT 0;  -- 计数器，用于分批提交
  39.   -- 声明游标，用于从源表中选择数据
  40.   DECLARE cur_sel CURSOR FOR SELECT   
  41.   `TIMEKEY`,  
  42.   `zbbh`,  
  43.   `zclb`,  
  44.   `zclbms`,  
  45.   `zjzh`,  
  46.   `zjzhms`,  
  47.   `cbzh`,  
  48.   `cbzhms`,  
  49.   `ljzjzh`,
  50.   `ljzjzhms`,
  51.   `xmbh`,
  52.   `xmmc`,
  53.   `sfgj`,
  54.   `dd`,
  55.   `ddsm`,
  56.   `cb`,
  57.   `cz`,
  58.   `bqzje`,
  59.   `bnzje`,
  60.   `ljzje`,
  61.   `zcjz`,
  62.   `zcje`,
  63.   `bqjzzb`,
  64.   `bnjzzb`,
  65.   `ljjzzb`,
  66.   `bfqj`
  67. FROM test_db1.test_t222
  68. WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2)); -- 根据传入的日期参数过滤数据
  70. -- 声明游标读取结束时的处理程序
  71. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  73. -- 删除目标表中符合条件的数据
  74. DELETE FROM test_t111 WHERE TIMEKEY = concat(substr(_sj,1,4),'-',substr(_sj,5,2));
  76. -- 开始事务
  77. START transaction;
  79. -- 打开游标
  80.   OPEN cur_sel;
  81.   
  82. -- 循环读取游标数据
  83. read_loop:LOOP
  84.    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;
  85.    
  86. -- 如果游标读取结束，退出循环
  87. IF done THEN
  88.    leave read_loop;
  89. END IF;
  91. -- 计数器加 1  
  92. SET count_num=count_num+1;
  94. -- 将读取的数据插入目标表  
  95. INSERT INTO `test_db1`.`test_t111`(
  96.   `TIMEKEY`,
  97.   `zbbh`,
  98.   `zclb`,
  99.   `zclbms`,
  100.   `zjzh`,
  101.   `zjzhms`,
  102.   `cbzh`,
  103.   `cbzhms`,
  104.   `ljzjzh`,
  105.   `ljzjzhms`,
  106.   `xmbh`,
  107.   `xmmc`,
  108.   `sfgj`,
  109.   `dd`,
  110.   `ddsm`,
  111.   `cb`,
  112.   `cz`,
  113.   `bqzje`,
  114.   `bnzje`,
  115.   `ljzje`,
  116.   `zcjz`,
  117.   `zcje`,
  118.   `bqjzzb`,
  119.   `bnjzzb`,
  120.   `ljjzzb`,
  121.   `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
  122.   );
  124. -- 如果计数器达到 10,000，提交事务并重新开始新事务
  125.   IF count_num=10000 THEN
  126.      COMMIT;
  127.      START transaction;
  128.      SET count_num=0;
  129.   END IF;
  130.   END LOOP read_loop;
  131.   
  132.   -- 提交剩余的事务
  133.    COMMIT;
  134.    
  135.   -- 关闭游标
  136.     CLOSE cur_sel;
  137. END
  138. //
  140. delimiter ;
  141. greatsql>
  142. greatsql> CALL test_sp_t1(20250301);
复制代码
四、总结

改造后的存储过程相较于原存储过程有以下几个优势：
1. 分批次提交事务


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


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


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


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


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

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
遏筒煽
2025-5-30 13:14:19

0

粉丝关注

17

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫