mysql 大事物commit慢造成全库堵塞难题
发布时间：2022-03-29 09:21:02 所属栏目：MySql教程 来源：互联网
导读：原创转载请注明出处 本文使用引擎INNODB版本MYSQL5.7.13 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 也就是双1设置， 1、问题由来 在线上环境一次同事删除了1000W的数据，在commit的时候，整个数据库 大约有几十秒的全部hang住的情况，本文就是
|
原创转载请注明出处 本文使用引擎INNODB版本MYSQL5.7.13 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 也就是双1设置， 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的时候做了什么以及为什么要上锁 在<>一文中有如下的图 mysql 大事物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) 5、使用infobin 对binlog文件中进行验证 使用./infobin test.000211>log.log 进行分析 (可以使用mysqlbinlog但是不直观 工具可以参考 http://blog.itpub.net/7728585/viewspace-2133534/ 解析MYSQL BINLOG二进制格式(9)--infobin解析binlog帮助文档 获得) -----delete from test; 的binlog event >Gtid Event:Pos:368030739(0X15efb413) N_pos:368030804(0X15efb454) Time:1487197597 Event_size:65(bytes) Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1158184 -->Query Event:Pos:368030804(0X15efb454) N_Pos:368030876(0X15efb49c) Time:1487197355 Event_size:72(bytes) Exe_time:0 Use_db:test Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1158184 ---->Map Event:Pos368030876(0X15efb49c) N_pos:368031047(0X15efb547) Time:1487197355 Event_size:171(bytes) TABLE_ID:129 DB_NAME:test TABLE_NAME:test Gno:1158184 ------>Delete Event:Pos:368031047(0X15efb547) N_pos:368039196(0X15efd51c) Time:1487197355 Event_size:8149(bytes) Dml on table: test.test table_id:129 Gno:1158184 ....(中间省略) ------>Delete Event:Pos:736052116(0X2bdf4394) N_pos:736054135(0X2bdf4b77) Time:1487197355 Event_size:2019(bytes) Dml on table: test.test table_id:129 Gno:1158184 >Xid Event:Pos:736054135(0X2bdf4b77) N_Pos:736054166(0X2bdf4b96) Time:1487197597 Event_size:31(bytes) COMMIT; /*!Trx end*/ Gno:1158184 ------insert into testsort2 values(1);的binlog >Gtid Event:Pos:736054441(0X2bdf4ca9) N_pos:736054506(0X2bdf4cea) Time:1487197600 Event_size:65(bytes) Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1158186 -->Query Event:Pos:736054506(0X2bdf4cea) N_Pos:736054578(0X2bdf4d32) Time:1487197600 Event_size:72(bytes) Exe_time:0 Use_db:test Statment(35b-trun):BEGIN /*!Trx begin!*/ Gno:1158186 ---->Map Event:Pos736054578(0X2bdf4d32) N_pos:736054634(0X2bdf4d6a) Time:1487197600 Event_size:56(bytes) TABLE_ID:188 DB_NAME:test TABLE_NAME:testsort2 Gno:1158186 ------>Insert Event:Pos:736054634(0X2bdf4d6a) N_pos:736054686(0X2bdf4d9e) Time:1487197600 Event_size:52(bytes) Dml on table: test.testsort2 table_id:188 Gno:1158186 >Xid Event:Pos:736054686(0X2bdf4d9e) N_Pos:736054717(0X2bdf4dbd) Time:1487197600 Event_size:31(bytes) COMMIT; /*!Trx end*/ Gno:1158186 可以看到确实insert into testsort values(1)和insert into testsort2 values(1);的event确实在delete后才进入 binlog file 6、分析等待点。 那么到底在等待什么呢？这是另外一次测试的sys.session输出。 （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号