从生产事故看MySQL事务提交的性能陷阱

IT那活儿 2025-07-24

118

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

一

概述

对于支持事务的关系型数据库来说，事务的完成需要执行commit命令，用于保存该事务操作的相关日志、标记该事务已完成，确保数据的一致性。通常情况下，commit命令执行很快，但也会存在commit是性能瓶颈，影响整体数据库性能。

二

案例分析

2.1 慢日志分析

对慢日志分析可见，commit操作平均耗时近1分钟是很不正常的现象。

2.2 MySQL commit机制

mysql数据库为了保证binlog、redolog中事务的一致性，事务采用了两阶段提交（2pc）机制：

从这个机制可以看出，commit操作包括binlog的落地动作。这就延长了commit动作的时间，也成了一个性能瓶颈，为了优化这一性能问题，mysql5.6推出了组提交机制。

binlog组提交的基本思想是，引入队列机制保证innodb commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。binlog提交将提交分为了3个阶段，FLUSH阶段，SYNC阶段和COMMIT阶段。基本流程如下：

FLUSH 阶段
持有Lock_log mutex [leader持有，follower等待]。
获取队列中的一组binlog(队列中的所有事务)。
将binlog buffer到I/O cache。
通知dump线程dump binlog。
SYNC阶段
释放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]。
将一组binlog 落盘(sync动作，最耗时，假设sync_binlog为1)。
COMMIT阶段
释放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]。
遍历队列中的事务，逐一进行innodb commit。
释放Lock_commit mutex。
唤醒队列中等待的线程。

组提交就是每次sync一组binlog，从而提升效率，而一组binlog的数量则由以下两个参数决定：

binlog_group_commit_sync_delay=N
在等待N μs后，开始事务刷盘。
binlog_group_commit_sync_no_delay_count=N
如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置。

2.3 数据库参数分析

参数名	参数值
sync_binlog	1
binlog_group_commit_sync_delay	1
binlog_group_commit_sync_no_delay_count	1000

以上参数为故障发生时数据库的参数设置，可以看binlog_group_commit_sync_delay设置为1微妙，即1微妙内sync一组binlog，这对于高并发的dml操作而言对IO压力相对比较大；sync_binlog参数在组提交机制下其意义也发生了变化，官方文档如下：

所以整体上看，故障发生时有大量的dml操作，且因为数据库的参数设置1微妙sync一组binlog，对IO造成了很大压力，再加上同时有大量的select查询，此刻IO已经达到极限，最终造成commit提交阻塞，事务不能及时释放资源，其它dml操作因不能及时获取事务锁、io等资源而延长执行时间。

三

总结

由于数据库设置为1微妙sync一组binlog，在大并发dml操作时对IO造成很大压力，造成commit阻塞和其它dml操作执行延长。

由于binlog的sync操作代价相对较高，因此可以增加每组binlog的数量、每次sync binlog组的数量，减轻对IO的压力。

binlog_group_commit_sync_delay设置太小，则每组中binlog数量较小，起不到组提交带来的性能优化，设置太大则阻塞了commit操作很长时间且binlog事务太大；

binlog_group_commit_sync_delay最好设置为10的倍数，不然会引起如下bug：

END

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。