数据库commit-景观大数据素材库

概述

对于支持事务的关系型数据库，事务的完成需要执行commit命令，commit命令用于保存事务操作的相关日志，标记事务完成，保证数据的一致性。 通常commit命令的执行速度非常快，但是commit可能会成为性能瓶颈，影响数据库的整体性能。

案例分析

慢日志分析

根据慢日志分析，commit操作平均耗时将近1分钟，不正常。

MySQL 提交机制

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

准备阶段

Innodb write/sync transaction redo, undolog, binlog 什么都不做。

提交阶段

mysql层write/syncbinlog，innodb write commit to redolog。

从这个机制可以看出commit操作包含了binlog的登陆动作。 这会延长提交操作时间并成为性能瓶颈。 为了优化这个性能问题，mysql5.6引入了group commit机制。

binlog分组提交的基本思想是引入队列机制，保证innodb commit的顺序与binlog入库顺序一致，分组事务，分组内的binlog刷盘动作分配给一个事务达到群投的目的。

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

FLUSH阶段

Hold Lock_log mutex [leader持有，follower等待]

获取队列中的一组binlog（队列中的所有事务）

将 binlog 缓冲区放入 I/O 缓存

通知dump线程转储binlog

同步阶段

释放 Lock_log mutex，hold Lock_sync mutex [leader holds, follower waits]

放一组binlog到磁盘（sync动作，最耗时，假设sync_binlog为1）

COMMIT阶段

释放Lock_sync mutex，hold Lock_commit mutex [leader holds, follower waits]

遍历队列中的事务，逐一执行innodb commit

释放 Lock_commit 互斥体

唤醒队列中等待的线程

分组提交就是每次同步一组binlog以提高效率，一组binlog的数量由以下两个参数决定：

binlog_group_commit_sync_delay=N：等待Nμs后，开始事务flush

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微秒内同步一组binlog，对于高并发dml操作的IO压力比较大； sync_binlog 参数在组提交机制中。 意思也变了，官方文档如下：

当sync_binlog设置为0或1时，将在binlog_group_commit_sync_delay时间后同步一组binlog； 当sync_binlog设置为N（N>1）时，经过binlog_group_commit_sync_delay时间后，将同步N组binlog。

所以整体来说，故障时dml操作比较多，而且由于数据库参数设置为1个subtle sync和一组binlog数据库commit，对IO压力很大，select查询比较多同时，此时IO已经达到限制，最终导致commit被阻塞，事务不能及时释放资源，其他dml操作不能及时获取事务锁，io等资源，从而延长执行时间处理时间。

总结

由于数据库设置为1微秒同步一组binlog，在大并发dml操作时对IO造成很大压力，导致commit阻塞，导致其他dml操作超时执行。 由于binlog的sync运行成本比较高，可以通过增加每组的binlog数量和每次sync的binlog组数量来降低IO压力。

如果设置sync_binlog为1，commit操作需要等待binlog_group_commit_sync_delay时间完成，会阻塞commit操作。 如果设置过大的值（如：1000），一次syncbinlog的量比较大，容易造成IO波动。 如果存大笔交易数据库commit，那么IO波动会更大。

如果binlog_group_commit_sync_delay设置的太小，每个group的binlog数量就会少，无法实现group commit带来的性能优化。 如果设置过大，commit操作会阻塞很长时间，binlog事务会过大。

binlog_group_commit_sync_delay最好设置为10的倍数，否则会导致如下bug：