drop table 引起SQL堵塞场景，规避措施及原理分析

drop table 引起SQL堵塞场景，规避措施及原理分析

验证版本：mariadb10.1.9

1      对业务侧影响及规避措施

1.1      Drop期间对SQL请求侧的影响和规避措施

表类型	DDL影响 （ALTER等）	DML（增删改）和DQL（查）影响	规避措施
有主键表	堵塞同SET内其他所有表DDL操作	l  堵塞正在执行DDL操作的表的DML和DQL	l  在Drop表操作期间，尽量避免同SET内的其它DDL操作。 l  如果SET内有自动加减分区操作，DROP操作需要避开这个时间点。 l  避开业务高峰期
		l  同SET内允许没有DDL操表的DML和DQL l  存在小概率引起没有ddl操作的其他表的性能下降
无主键表	堵塞其他所有表DDL	堵塞其他所有表DML	l  添加主键整改

     

1.2 DROP期间机器负载侧的影响和规避措施

1.2.1影响：

l  drop表也极易引起IO高负载：

Ø  每个表对应的的ibd文件，存储在磁盘上。

Ø  表记录越大，ibd越大，目前生产最大已达T级别。

Ø  所以drop表也极易引起IO高负载，高负载又间接会影响sql的响应时长。

1.2.2规避措施：

l  错开业务高峰：

Ø  避免对资源的需求重叠进一步加重资源负载。

l  通过truncate 命令分片删除ibd文件规避：

Ø  tuncate支持对表ibd文件分块平滑限速删除，比如限速10M/S，也就是一次删除一块。

Ø  并可改造支持限速大小支持动态调整，包括支drop在途期间可根据负载情况适时调整。

2      SQL请求侧的原因分析：

2.1      前置知识点：

l  MySQL 分为 Server 层和存储引擎层两部分。

存储引擎层负责数据的存储和提取，其架构模式是插件式，所以两部分有既要协同工作，又各自有自己的运行机制。

我行默认使用的innodb存储引擎。

l  数据字典： 是指数据表结构，字段，索引定义等内容，不包含表记录数据，对字典部分有专门的字典锁管理字典资源。

l  Buffer Pool（BP）

是innodb按照设置大小向操作系统申请的一块内存。

   

增删改查dml和dql的请求，innodb会先把涉及表的磁盘数据加载到BP中，直接访问内存，从而降低磁盘访问;利用内存速度优势，从而大幅提升性能。

磁盘和内存的速度差有多少？答案是单车和跑车能比吗！

2.2      drop期间什么原因导致任何表的DDL堵塞？

2.2.1   首先Drop清理会涉及如下内容：

l  BP------也就是前置知识点介绍的innodb申请的 Buffer Pool中的该表的字典和数据部分清理。

2.2.2   而在清理BP内存时，要访问innodb字典部分，就持有了  SYS_dict->Mutex锁

l  SYS_dict>Mutex为系统字典互斥锁，是innodb层的大锁，针对整个数据库字典的，所以是全局锁,又因为是Mutex锁， drop期间持有该锁，不允许任何请求读写innodb的字典。

l  那和MDL读写字典锁区别是什么呢？

MDL是Server层的针对单表生效的局部锁，。

所以ddl操作涉及字典信息更新，所以既要更新Server层，又要更新INNODB层，也就是既要MDL字典锁，又需要SYS_dict->Mutex。

l  SYS_dict>Mutex如何堵塞DDL的呢？

，当由于drop操作持有SYS_dict>Mutex，而表的DDL则获取不到SYS_dict>Mutex就会被堵塞。

l  大家可模拟做试验验证如下：

，在drop A库的A表后，B库的表B表的alter加分区被堵塞

2.3      Drop期间为什么有主键表有时DML和DQL不堵塞？

1.3.1   drop期间持有该锁，不允许任何请求读写innodb的字典， DML和DQL也需要访问innodb字典去看下表对应的字段是什么信息，才为什么没堵塞呢？

 

Innodb层的字典信息是有缓存的， DML和DQL不需更新innodb层字典信息，就不需要再直接访问innodb字典部分，直接访问缓存的字典信息即可，所以不需要申请SYS_dict>Mutex从而drop期间，有主键表的DML就不会被堵塞。

而DDL是更新字典信息，必须更新对应的innodb字典，所以才一定需要SYS_dict>Mutex。

从大家实操经验也能验证drop期间基本不会有DML和DQL堵塞。

2.4      Drop期间为什么有主键表有时DML和DQL又堵塞？

Ø  是因为drop表期间如果又有其他表触发DDL操作，根据前面分析， 其他表的DDL就由于拿不到SYS_dict->Mutex锁一定会堵塞，从而也会导致拿不到MDL写锁或者已先持有MDL锁不释放，理论上是先访问SERVER，会先拿到MDL写锁，具体后续还要再确认那种情况。

但不管是持有还是拿不到，都会堵塞后边的DML和DQL。

持有，MDL写错和MDL读锁互斥，DML和DQL需要的MDL读锁也就获取不到。

拿不到，由于表MDL锁是队列方式，先到先得，MDL写锁会让后续的读锁也排队获取不到，否则MDL写锁就会一直不能拿到。

所以触发DDL后的表的DML和DQL操作，也会拿不到MDL读锁，只能坐等也被堵塞。

Ø  可模拟如下场景验证：

 

 

drop A库的A表后，B库.B表 的alter加分区被堵塞，进而对B库.B表 的一条简单limit 1查询也被活生生堵塞了。

n  注意：alter加减分区一类的语句就是慢也不记录慢查询文件中，有隐蔽性，所以导致分析dml慢的原因容易漏掉这个因素，所以要确认是否有alter语句需要分析错误日志和biinlog。

2.5      Drop期间为什么无主键表DML和DQL全被堵塞？

Ø  由于无主键表InnoDB会自动创建一个不可见的、长度为6字节的row_id。

Ø  而row_id是InnoDB在字典里维护了一个全局的dictsys.row_id，所有无主键表共用。

Ø  所以当无主键表DML和DQL也是需要访问inndoob字典定位row_id或者申请新row_id,，故也需申请SYS_dict->Mutex锁,从而也会被堵塞。

Ø  可模拟如下场景验证如下：

                            

删除A库.A表，堵塞了无主键表B库.B表。

2.6      Drop期间为什么存在降低所有DML性能的可能性？  

因为drop table，需要清理bp中该表信息时，还会扫描所有的bp，扫描 期间会加buffer pool mutex互斥锁，从而有引起其他事务访问bp遇到锁冲突的概率，从而降低性能。

1.      延伸：Drop整库又是什影响呢？

3.1 模拟如下场景验证：

  删除A库时，B库的任何DDL 操作都会被堵塞。并且由于整库消耗时间长，会存在持续堵塞DDL语句的情况。

1. 1. 规避措施

禁止Drop整库，用逐表删除，表之间加Sleep 一定时间方法代替。

4      总结

所有的影响都是因为drop期间的持有dict->Mutex锁,buffer poll Mutex锁和删除磁盘文件导致，如果能满足如下条件避开该锁影响，drop是相对比较安全的：

Ø    db不存在无主键表，

Ø    drop期间没有其他表的DDL操作。

• 禁止Drop整库，用逐表删除，表之间Sleep 一定时间方法代替。

Ø    不管磁盘删除ibd文件大小，是否有锁，根据安全运维基本常识，drop高危操作都建议选择业务低峰期执行，请务必遵守。