这是2022年发生的一起线上事故，现在有空记录一下。

线上使用的是5.6版本的，大部分业务的逻辑都是先delete 全表，再insert，所以大事务一直是个问题，但是未发生过阻塞业务的情况。

现象

MySQL服务器的负载在一分钟内急剧上升，后又迅速恢复正常。查看binlog文件发现当时有个3G的binlog文件生成，因此判断是大事务写入，并且MySQL这个时候需要生成新的binlog文件导致的。

复现

把日志切分的大小改成10M

查看代码

mysql> show variables like '%max_binlog_size%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| max_binlog_size | 10485760 |
+-----------------+----------+
1 row in set (0.00 sec)

开启大事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from bkpm001;
Query OK, 1674248 rows affected (7 min 29.94 sec)

再开一个连接做写入，commit之后立即去写入数据

trx1：

mysql> commit;
Query OK, 0 rows affected (15.55 sec)

trx2：

mysql> insert into yonghu values (null,2,2,2,2,2);
Query OK, 1 row affected (14.68 sec)

事务2被阻塞，事务1提交之后，事务2也提交。

探究

查看mysql链接对应os线程的调用栈

读取文件句柄为23的文件，在写入文件句柄为25的文件，每次写入8192字节（8k）

23句柄是事务生成的binlog临时文件，使用之后会清空，25句柄是对应当前的binlog文件，在提交的时候需要将临时文件拷贝到binlog文件，并且binglog是临界资源，在拷贝过程当中阻塞其他事务写入binlog导致无法提交

write从开始到结束花费了6s时间

解决

解铃还须系铃人，大事务最好的解决办法就是大事化小，但是领导说，业务没时间改，要数据库出方案。

这里（http://hzhcontrols.com/new-312960.html）描述了这个8192是event的大小，官网描述的event大小与实验的结果一致，所以要优化可以调整event的大小，提高每次从临时文件读取写入binlog的大小。

binlog_order_commits=off

大事务的Event会在提交时刻一次性的写入到binary log。如果COMMIT队列中包含了大事务，那么必然堵塞本队列中的其它事务提交，后续的提交操作也不能完成。

各个 session 自行进入 InnoDB commit 阶段，这样不会保证 binlog 和事务 commit 的顺序一致。小事务不必等到大事务提交在提交

设置大一点的binlog文件。从原来的500M改为了1G。

更换更好的io性能磁盘，这里线上的硬盘从原来的机械盘(什么年代了数据库的硬盘还用机械盘)全部换为SSD 。