mysql info commit_MySQL慢日志中COMMIT事件

问题描述

某MySQL服务器出现大量慢日志，慢日志中SQL语句部分为COMMIT，执行时间在100秒至400秒。

由于定期对该服务器进行SHOW PROCESSLIST快照， 通过PROCESSLIST日志查看服务器执行情况。

在1:02至1:32期间,业务进行数据归档操作，归档SQL支持超过1800秒：

IdUserHost dbCommandTimeState Info

8487369xxx_rw172.16.16.26:28148xxxQuery1807Sending dataINSERT INTO xxx_async_task_20200301 SELECT * FROM xxx_async_task_backTable_17533 WHERE created_at < "20200301"

在1:33，归档SQL状态从Sending data切换为query end：

IdUserHost dbCommandTimeState Info

8487369xxx_rw172.16.16.26:28148xxxQuery1807query endINSERT INTO xxx_async_task_20200301 SELECT * FROM xxx_async_task_backTable_17533 WHERE created_at < "20200301"

在1:33至1:36, 该SQL执行状态仍为：query end，开始出现COMMIT命令等待(starting)，活跃连接数从1暴涨至200+：

IdUserHost dbCommandTimeState Info

8487369xxx_rw172.16.16.26:28148xxxQuery1807query endINSERT INTO xxx_async_task_20200301 SELECT * FROM xxx_async_task_backTable_17533 WHERE created_at < "20200301"

8487410xxx_rw172.16.16.89:31590xxxQuery7startingcommit

8487465xxx_rw172.16.16.91:58612xxxQuery10startingcommit

3942982aurora11.223.143.134:42889NULLQuery130query end/* rds internal mark */ CREATE TABLE IF NOT EXISTS mysql.ha_health_check (\n id BIGINT DEFAULT 0,\n type CHAR(1) DEFAULT '0',\n PRIMARY KEY (type)\n)\n ENGINE = InnoDB

PS: 阿里云的内部账号进行ha_health_check的CREATE TABLE 命令也被阻塞

在1:36，归档SQL执行完成，处于starting状态的COMMIT命令也随之消失，MySQL生成单个文件超过20G的BINLOG，MySQL慢日志中记录大量包含COMMIT操作的慢日志。

在1:37至1：40，网络流出量暴涨，大量BINLOG数据被推送至从库进行数据同步。

线程执行状态

*Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client.

Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

*query end

This state occurs after processing a query but before the freeing items state.

*starting

未在MySQL官网找到解释

问题原因

导致COMMIT操作出现在慢日志原因：

1、在ROW复制模式下，事务修改大量数据，在COMMIT时需要生成BINLOG事件并刷新至BINLOG文件中，由于BINLOG文件只能顺序写入，导致其他事务COMMIT时被阻塞。

2、当存储系统出现IO瓶颈或出现故障时，如当RAID卡充放电过程中，RAID系统从Write Back切换为Write Through后，存储IO使用率暴涨，通用会出现COMMIT操作"长时间执行"情况。