mysql 慢_mysql 大事物commit慢造成全库堵塞问题

原创转载请注明出处

本文使用引擎INNODB版本MYSQL5.7.13

sync_binlog = 1

innodb_flush_log_at_trx_commit = 1

也就是双1设置，

本文使用通过对mysql 进行trace，gdb 自制工具infobin，以及

select * from sys.session where command<>'SLEEP' \G完成。

这里只是涉及binlog的写入问题和innodb层无关

1、问题由来

在线上环境一次同事删除了1000W的数据，在commit的时候，整个数据库

大约有几十秒的全部hang住的情况，本文就是为了深入剖析这个问题。

2、可能需要的知识

http://blog.itpub.net/7728585/viewspace-2133589/ MYSQL 什么时候同步binlog文件以及它的临时文件在哪里？

http://blog.itpub.net/7728585/viewspace-2133188/ 解析MYSQL BINLOG 二进制格式(1)--准备工作

http://blog.itpub.net/7728585/viewspace-2133189/ 解析MYSQL BINLOG 二进制格式(2)--FORMAT_DESCRIPTION_EVENT

http://blog.itpub.net/7728585/viewspace-2133321/ 解析MYSQL BINLOG 二进制格式(3)--QUERY_EVENT

http://blog.itpub.net/7728585/viewspace-2133429/ 解析MYSQL BINLOG 二进制格式(4)--TABLE_MAP_EVENT

http://blog.itpub.net/7728585/viewspace-2133463/ 解析MYSQL BINLOG 二进制格式(5)--WRITE_ROW_EVENT

http://blog.itpub.net/7728585/viewspace-2133469/ 解析MYSQL BINLOG 二进制格式(6)--UPDATE_ROW_EVENT/DELETE_ROW_EVENT

http://blog.itpub.net/7728585/viewspace-2133502/ 解析MYSQL BINLOG 二进制格式(7)--Xid_log_event/XID_EVENT

http://blog.itpub.net/7728585/viewspace-2133506/ 解析MYSQL BINLOG二进制格式(8)--GTID_LOG_EVENT/ANONYMOUS_GTID_LOG_EVENT及其他

http://blog.itpub.net/7728585/viewspace-2133534/ 解析MYSQL BINLOG二进制格式(9)--infobin解析binlog帮助文档

http://blog.itpub.net/7728585/viewspace-2133537/ 解析MYSQL BINLOG二进制格式(10)--问题解答

3、简单说明commit的时候做了什么以及为什么要上锁

在<>一文中有如下的图

其实这幅图中我已经写了需要一把锁，但是没仔细说，这把锁影响的是全部当前数据库的需要写binlog的全部语句,我们已经知道对于大事物(超过)的commit期间需要将临时

文件的中的binlog event 全部拷贝到binlog中，并且是一个事物的event必须是连续的，也就是一个事物的event中不能包含其他事物的event那么这期间需要一把mutex锁，为什

么要这把锁，这是在多线程/进程并发对同一个文件进行修改，也就是这里的binlog文件修改的，一种保护临界资源的方法，如果没有这把锁就会出现binlog中的文件出现错乱的情况。

注意这个锁是mysql层的而非innodb层。

4、模拟方法

对表test 进行删除不提交，观察临时文件大小。

关于生成临时文件的原理参考：

(http://blog.itpub.net/7728585/viewspace-2133589/ MYSQL 什么时候同步binlog文件以及它的临时文件在哪里？)

对删除delete test表commit期间马上对testsort和testsort2表插入一条数据，

这个时候可以观察到这两个插入，会被堵塞知道delete test的事物提交完成

才完成。

mysql> select count(*) from test;

+----------+

| count(*) |

+----------+

|  2621440 |

+----------+

1 row in set (3.14 sec)

及如下：