针对大事务问题对业务存储过程改造
<h1 id="针对大事务问题对业务存储过程改造">针对大事务问题对业务存储过程改造</h1><h2 id="一问题描述">一、问题描述</h2>
<h3 id="1-问题现象">1. 问题现象</h3>
<p>业务调用存储过程没有成功，发现存在大事务，单独拿出来执行发现问题。</p>
<pre><code class="language-SQL">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.
</code></pre>
<p>错误日志中体现为：</p>
<pre><code class="language-yaml">2025-02-20T00:36:01.969610+08:00 O Page cleaner took 10448ms to flush 2000 pages
2025-02-20T00:38:26.020404+08:00 O Page cleaner took 8427ms to flush 560 pages
2025-02-20T00:38:39.690478+08:00 3478 Plugin group_replication reported: ' 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 Plugin group_replication reported: ' Error preparing the message for sending.'
2025-02-20T00:38:40.092629+08:00 3478 Plugin group_replication reported: 'Error while broadcasting the transaction to the group on session 3478'
2025-02-20T00:38:41.047249+08:00 3478 Run function 'before_commit' in plugin 'group_replication' failed
</code></pre>
<h2 id="二问题分析">二、问题分析</h2>
<h3 id="1错误信息分析">1.错误信息分析</h3>
<ul>
<li><strong>ERROR 8532</strong> (HY000)错误信息说明，执行的操作涉及multi-statement transaction，并且所需的存储超出了 max_binlog_cache_size 的限制。</li>
<li><strong>日志</strong>（<code></code>）：提到 <code>group_replication</code> 报告的消息，具有<code>GCS_packet's payload is too big</code> 的报错。来自 <code>group_replication</code>，强调负载太大，只有小于 2113929216 字节的包可以发送。出现了 <code>before_commit</code> 的运行错误，以至于无法在<code>group_replication</code> 中完成事务。</li>
</ul>
<h3 id="2分析参数设置">2.分析参数设置</h3>
<p>默认情况下，不建议超过2G以上的大事务。这也是参数层面的限制。</p>
<pre><code class="language-sql">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)
</code></pre>
<ol>
<li><strong>group_replication_transaction_size_limit</strong>：
<ol>
<li><strong>值</strong>：2147483647</li>
<li><strong>含义</strong>：这个参数定义了可以被复制的事务的最大大小，单位是字节（bytes）。2147483647 是 2G。</li>
</ol>
</li>
<li><strong>group_replication_compression_threshold</strong>：
<ol>
<li><strong>值</strong>：1000000</li>
<li><strong>含义</strong>：这个参数表示在进行组复制时，事务大小超过该阈值（在这里是 1,000,000 字节，即约 1MB）会被考虑进行压缩。换句话说，只有当事务大小超过 1MB 时，数据才会被压缩以减少网络传输的负担。</li>
</ol>
</li>
</ol>
<p>查看<code>max_binlog_cache_size</code>的值为4G，<code>max_binlog_cache_size</code> 参数，它限制了在 binlog（binary log）缓存中存储的最大字节数。</p>
<pre><code class="language-sql">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)
</code></pre>
<h3 id="3分析存储过程信息">3.分析存储过程信息</h3>
<p>查看此存储过程相关信息(INFORMATION_SCHEMA_ROUTINES)：</p>
<pre><code class="language-SQL">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
</code></pre>
<p>而业务上，对ETL表后续的清洗操作，确实涉及大事务的动作。</p>
<h3 id="4查看涉及表大小">4.查看涉及表大小</h3>
<pre><code class="language-sql">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)
</code></pre>
<p>因为ywdb2.t2长时间没有跑出结果，先直接查看ywdb2.t2的ibd文件大小(单表大小161G)。</p>
<pre><code class="language-sql">$ du -sh *
161G t2.ibd
</code></pre>
<p>可以看到不管是delete ，还是依据ywdb2.t2这个大表来做insert ... select ...,都是比较大体量的大事务，事务行数超过500W条以上 （单月账期的数据量）。</p>
<h2 id="三存储过程改造方法">三、存储过程改造方法</h2>
<h3 id="1改造思路">1.改造思路</h3>
<p>改写应用程序，利用频繁提交等方式，将大事务变为小事务。</p>
<h3 id="2改造方式">2.改造方式</h3>
<p><code>INSERT...SELECT...</code>语句，存储过程内改成循环，并一段10000条提交一次。（使用游标）</p>
<p>以存储过程ywdb1.sp_t1 的修改为例：</p>
<pre><code class="language-SQL">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);
</code></pre>
<h2 id="四总结">四、总结</h2>
<p>改造后的存储过程相较于原存储过程有以下几个优势：</p>
<h3 id="1-分批次提交事务">1. <strong>分批次提交事务</strong></h3>
<ul>
<li><strong>原存储过程</strong>：在整个操作完成后才提交事务，这意味着在处理大量数据时，事务会占用大量资源，可能导致锁争用、内存占用过高等问题。</li>
<li><strong>改造后存储过程</strong>：每处理10000条记录后提交一次事务，减少了单次事务的数据量，降低了锁争用和内存占用，提高了系统的并发性和稳定性。</li>
</ul>
<h3 id="2-游标的使用">2. <strong>游标的使用</strong></h3>
<ul>
<li><strong>原存储过程</strong>：使用简单的<code>INSERT INTO ... SELECT</code>语句一次性插入所有数据，如果数据量非常大，可能会导致内存溢出或性能下降。</li>
<li><strong>改造后存储过程</strong>：使用游标逐条处理数据，适合处理大数据量的场景，能够更好地控制内存使用和性能。</li>
</ul>
<h3 id="3-灵活性">3. <strong>灵活性</strong></h3>
<ul>
<li><strong>改造后存储过程</strong>：通过游标和分批次提交的方式，可以更灵活地处理不同规模的数据集，避免了单次操作数据量过大带来的问题。</li>
</ul>
<h3 id="4-性能优化">4. <strong>性能优化</strong></h3>
<ul>
<li><strong>改造后存储过程</strong>：通过减少单次事务的数据量和分批提交，减少了数据库的锁争用和内存占用，从而提高了整体性能，特别是在高并发环境下。</li>
</ul>
<h3 id="5-可维护性">5. <strong>可维护性</strong></h3>
<ul>
<li><strong>改造后存储过程</strong>：通过使用游标和分批次提交，代码结构更加清晰，便于后续的维护和优化。</li>
</ul>
<p></p>
<p>总之，改造后的存储过程在处理大数据量时具有更好的性能、稳定性和可维护性。通过分批次提交事务、使用游标逐条处理数据以及优化事务提交策略，能够有效降低系统资源的占用，提高并发处理能力，特别适合在高并发、大数据量的场景下使用。</p>
<hr>
<p>Enjoy GreatSQL 😃</p>
<h2 id="关于-greatsql">关于 GreatSQL</h2>
<p>GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。</p>
<p>相关链接： GreatSQL社区 Gitee GitHub Bilibili</p>
<h2 id="greatsql社区">GreatSQL社区：</h2>
<blockquote>
<p>社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202505/2630741-20250516095641023-1749108546.png"></p>
<h2 id="技术交流群">技术交流群：</h2>
<blockquote>
<p>微信：扫码添加<code>GreatSQL社区助手</code>微信好友，发送验证信息<code>加群</code>。</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202505/2630741-20250516095641320-2124838194.png"></p><br><br>
来源：https://www.cnblogs.com/greatsql/p/18879544
頁:
[1]