# mysql commit 引起的性能问题

> 作者/来源: UCloud 运营管理员
> 发布时间: 2023-01-11T05:20:00.000Z
> 分类: 其他
> 标签: 数据库
> 原文链接: http://117.50.162.249:3000/yun/articles/657

---

# mysql commit 引起的性能问题

> 来源: https://www.ucloud.cn/yun/130192.html
> 作者: IT那活儿
> 发布日期: 发布于2023-01-11 13:20

mysql commit 引起的性能问题

概述

# 

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

案例分析

**慢日志分析**

|  |
| --- |
| ![](https://ucloud-blog.cn-bj.ufileos.com/articles/130192/images/130192_000.png) |

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

**MySQL commit机制**

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

- prepare阶段

innodb write/sync事务redo、undolog，binlog不做任何操作。

- commit阶段

mysql层write/syncbinlog，innodb写 commit 至redolog。

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

binlog组提交的基本思想是，引入队列机制保证innodb commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。

binlog提交将提交分为了3个阶段，FLUSH阶段，SYNC阶段和COMMIT阶段。基本流程如下：

**FLUSH 阶段**

1. 持有Lock\_log mutex [leader持有，follower等待]
2. 获取队列中的一组binlog(队列中的所有事务)
3. 将binlog buffer到I/O cache
4. 通知dump线程dump binlog

**SYNC阶段**

1. 释放Lock\_log mutex，持有Lock\_sync mutex[leader持有，follower等待]
2. 将一组binlog 落盘(sync动作，最耗时，假设sync\_binlog为1)

**COMMIT阶段**

1. 释放Lock\_sync mutex，持有Lock\_commit mutex[leader持有，follower等待]
2. 遍历队列中的事务，逐一进行innodb commit
3. 释放Lock\_commit mutex
4. 唤醒队列中等待的线程

组提交就是每次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的设置

数据库参数分析

|  |  |
| --- | --- |
| 参数名 | 参数值 |
| 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参数在组提交机制下其意义也发生了变化，官方文档如下：

|  |
| --- |
| ![](https://ucloud-blog.cn-bj.ufileos.com/articles/130192/images/130192_001.png) |

当sync\_binlog设置为0或1时，binlog\_group\_commit\_sync\_delay时间后sync一组binlog；当sync\_binlog设置N(N>1)时binlog\_group\_commit\_sync\_delay时间后syncN组binlog。

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

总结

由于数据库设置为1微妙sync一组binlog，在大并发dml操作时对IO造成很大压力，造成commit阻塞和其它dml操作执行延长。由于binlog的sync操作代价相对较高，因此可以增加每组binlog的数量、每次syncbinlog组的数量，减轻对IO的压力。

设置sync\_binlog为1，则commit操作需要等待binlog\_group\_commit\_sync\_delay时间才能完成，阻塞了commit操作，设置太大的值（如：1000），则一次syncbinlog量比较大，很容易造成IO波动，如果存大大事务，则IO波动会更大。

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

binlog\_group\_commit\_sync\_delay最好设置为10的倍数，不然会引起如下bug：

![](https://ucloud-blog.cn-bj.ufileos.com/articles/130192/images/130192_002.png)