针对大事务问题对业务存储过程改造
一、问题描述
1. 问题现象
业务调用存储过程没有成功，发现存在大事务，单独拿出来执行发现问题。
错误日志中体现为：
二、问题分析
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 以上的大事务。这也是参数层面的限制。
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）缓存中存储的最大字节数。
3.分析存储过程信息
查看此存储过程相关信息(INFORMATION_SCHEMA_ROUTINES)：
而业务上，对 ETL 表后续的清洗操作，确实涉及大事务的动作。
4.查看涉及表大小
因为 ywdb2.t2 长时间没有跑出结果，先直接查看 ywdb2.t2 的 ibd 文件大小(单表大小 161G)。
可以看到不管是 delete ，还是依据 ywdb2.t2 这个大表来做 insert ... select ... ,都是比较大体量的大事务，事务行数超过 500W 条以上 （单月账期的数据量）。
三、存储过程改造方法
1.改造思路
改写应用程序，利用频繁提交等方式，将大事务变为小事务。
2.改造方式
INSERT...SELECT...语句，存储过程内改成循环，并一段 10000 条提交一次。（使用游标）
以存储过程 ywdb1.sp_t1 的修改为例：
四、总结
改造后的存储过程相较于原存储过程有以下几个优势：
1. 分批次提交事务
原存储过程：在整个操作完成后才提交事务，这意味着在处理大量数据时，事务会占用大量资源，可能导致锁争用、内存占用过高等问题。
改造后存储过程：每处理 10000 条记录后提交一次事务，减少了单次事务的数据量，降低了锁争用和内存占用，提高了系统的并发性和稳定性。
2. 游标的使用
原存储过程：使用简单的
INSERT INTO ... SELECT语句一次性插入所有数据，如果数据量非常大，可能会导致内存溢出或性能下降。改造后存储过程：使用游标逐条处理数据，适合处理大数据量的场景，能够更好地控制内存使用和性能。
3. 灵活性
改造后存储过程：通过游标和分批次提交的方式，可以更灵活地处理不同规模的数据集，避免了单次操作数据量过大带来的问题。
4. 性能优化
改造后存储过程：通过减少单次事务的数据量和分批提交，减少了数据库的锁争用和内存占用，从而提高了整体性能，特别是在高并发环境下。
5. 可维护性
改造后存储过程：通过使用游标和分批次提交，代码结构更加清晰，便于后续的维护和优化。
总之，改造后的存储过程在处理大数据量时具有更好的性能、稳定性和可维护性。通过分批次提交事务、使用游标逐条处理数据以及优化事务提交策略，能够有效降低系统资源的占用，提高并发处理能力，特别适合在高并发、大数据量的场景下使用。
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/c104a020143e8fa3a393d3747】。文章转载请联系作者。
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论