MySQL:由USE DB堵塞故障引发的思考

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 遇到故障,我们往往想的是如何解决这个故障,而不是从故障的根本去思考出现这个故障的原因?这样的结果,只能使我们得到了鱼,失去了渔。今天,我们就来分享一个由USE DB堵塞故障引发的思考案例。 故障描述今天一个朋友遇到数据库遇到一个严重的故障,故障环境如下: MYSQL 5.

遇到故障,我们往往想的是如何解决这个故障,而不是从故障的根本去思考出现这个故障的原因?这样的结果,只能使我们得到了鱼,失去了渔。今天,我们就来分享一个由USE DB堵塞故障引发的思考案例。

故障描述

今天一个朋友遇到数据库遇到一个严重的故障,故障环境如下:

  • MYSQL 5.6.16
  • RR隔离级别
  • GITD关闭

表现如下:

  • use db不能进入数据库
  • show table status不能查询到表信息
  • schema.processlist来看有大量的 Waiting for table metadata lock

情急之下他杀掉了一大堆线程后发现还是不能恢复,最后杀掉了一个没有及时提交的事物才恢复正常。也仅仅留下了如下图的一个截图:

8913f001a7450cf0a261ebef6b800236c2bec233

故障信息提取

还是回到上图,我们可以归纳一下语句类型如下:

1、CREATE TABLE A AS SELECT B
其STATE为 sending data

2、DROP TABLE A
其STATE为 Waiting for table metadata lock

3、SELECT * FROM A
其STATE为 Waiting for table metadata lock

4、 SHOW TABLE STATUS[like 'A']
其STATE为 Waiting for table metadata lock

信息分析

要分析出这个案列其实不太容易因为他是MYSQL层MDL LOCK和RR模式innodb row lock的一个综合案列,并且我们要对schema.processlist的STATE比较敏感才行。

建议先阅读我的如下文章来学习MDL LOCK:
http://blog.itpub.net/7728585/viewspace-2143093/

本节关于MDL LOCK的验证使用下面两种方式:

方式一:笔者在MDL LOCK源码加锁函数处加日志输出,如果要分析各种语句加MDL LOCK的类型还只能用这种方式,因为MDL LOCK加锁往往一闪而过,performance_schema.metadata_locks 没有办法观察到。

方式二:处于堵塞情况下使用5.7版本的performance_schema.metadata_locks观察。

在P_S中打开mdl监测方法如下:

8c24826e9aec84c3c776e2205ffd2cb9a6642db5

一、关于CREATE TABLE A AS SELECT B 对B表sending data的分析

关于sending data这个状态其实可以代表很多含义,从我现有的对的了解,这是MYSQL上层对SELECT类型语句的这类语句在INNODB层和MYSQL层进行数据交互的时候一个统称,所以出现它的可能包含:

  • 确实需要访问数据量特别大,可能需要优化。
  • 由于INNODB 层的获取row lock需要等待,比如我们常见的SELECT FOR UPDATE。

同时我们还需要注意在RR模式下SELECT B这一部分加锁方式和INSERT...SELECT是一致的参考不再赘述:
http://blog.itpub.net/7728585/viewspace-2146183/

从他反应的情况因为他在最后杀掉了一个长期的未提交的事物所以他因为是情况2。并且整个CREATE TABLE A AS SELECT B语句由于B表上某些数据库被上了锁而不能获取,导致整个语句处于sending data状态下。

二、关于SHOW TABLE STATUS[like 'A'] Waiting for table metadata lock的分析

这是本案例中最重要的一环,SHOW TABLE STATUS[like 'A']居然被堵塞其STATE为Waiting for table metadata lock并且注意这里是table因为MDL LOCK类型分为很多。我在MDL介绍的那篇文章中提到了desc 一个表的时候会上MDL_SHARED_HIGH_PRIO(SH),其实在SHOW TABLE STATUS的时候也会对本表上MDL_SHARED_HIGH_PRIO(SH)。

b81518973a9c6844418c454b05d19104f98169c0

两种方式都能观察到MDL_SHARED_HIGH_PRIO(SH)的存在并且我模拟的是处于堵塞情况下的。

但是MDL_SHARED_HIGH_PRIO(SH) 是一个优先级非常高的一个MDL LOCK类型表现如下:

  • 兼容性:

f337cd083c84e9f356f73819165ed885236d6e43

  • 阻塞队列优先级:

01369103167c731dc509d74d4520acdbfc076f59

其被堵塞的条件除了被MDL_EXCLUSIVE(X)堵塞没有其他的可能。那么这就是一个非常重要的突破口。

三、关于CREATE TABLE A AS SELECT B 对A表的加MDL LOCK的分析

这一点也是我以前不知道的,也是本案列中花时间最多的地方,前文已经分析过要让SHOW TABLE STATUS[like 'A']这种只会上MDL_SHARED_HIGH_PRIO(SH) MDL LOCK的语句堵塞在MDL LOCK上只有一种可能那就是A表上了MDL_EXCLUSIVE(X)。

那么我开始怀疑这个DDL语句在语句结束之前会对A表上MDL_EXCLUSIVE(X) ,然后进行实际测试不出所料确实是这样的如下:

4a39edcd76f3f0df295227e2873ef3dc2837437b

这里比较遗憾在performance_schema.metadata_locks中并没有显示出MDL_EXCLUSIVE(X),而显示为MDL_SHARED(S) 但是我们在我输出的日志中可以看到这里做了升级操作将MDL_SHARED(S) 升级为了MDL_EXCLUSIVE(X)。并且由前面的兼容性列表来看,只有MDL_EXCLUSIVE(X)会堵塞MDL_SHARED_HIGH_PRIO(SH)。所以我们应该能够确认这里确实做了升级操作,否则SHOW TABLE STATUS[like 'A'] 是不会被堵塞的。

四、关于SELECT * FROM A Waiting for table metadata lock的分析

也许大家认为SELECT不会上锁,但是那是在innodb 层次,在MYSQL层会上MDL_SHARED_READ(SR) 如下:

d9e00f6f74b7ab4ea1b73ee71bc4950ceac43c01

可以看到确实有MDL_SHARED_READ(SR)的存在,当前处于堵塞状态

其兼容性如下:

e827b8cdaea0e44276903bd6e6dce55f1a076603

显然MDL_SHARED_READ(SR) 和MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。

五、关于DROP TABLE A Waiting for table metadata lock的分析

这一点很好分析因为A表上了X锁而DROP TABLE A必然上MDL_EXCLUSIVE(X)锁它当然和MDL_EXCLUSIVE(X)不兼容。如下:

ff7073beffa197f3fe2f497aad3407ddb869ba21

其中EXCLUSIVE就是我们说的MDL_EXCLUSIVE(X)它确实存在当前处于堵塞

六、为何use db也会堵塞?

如果使用mysql客户端不使用-A选项(或者 no-auto-rehash)在USE DB的时候至少要做如下事情:

1、 对db下每个表上MDL (SH) lock如下(调用MDL_context::acquire_lock 这里给出堵塞时候的信息)

f6fd4bd8aaa8ec673fd300b8a7070dc4387667f4

可以看到USE DB确实也因为MDL_SHARED_HIGH_PRIO(SH) 发生了堵塞。

2、对每个表加入到table cache,并且打开表(调用open_table_from_share())

那么这种情况就和SHOW TABLE STATUS[like 'A']被堵塞的情况一模一样了,也是由于MDL 锁不兼容造成的。

分析梳理

有了前面的分析那么我们可以梳理这个故障发生的原因如下:

有一个在B表上长期未提交的DML
语句会在innodb层对B表某些数据加innodb row lock。
  • 由步骤1引起了CREATE TABLE A AS SELECT B的堵塞
因为RR模式下SELECT B必然对B表上满足的数据上锁,因为步骤1已经加锁所以触发等待,STATE为sending data。
  • 由步骤2引起了其他语句的堵塞
因为CRATE TABLE A AS SELECT B在A表建立完成之前会上MDL_EXCLUSIVE(X),这把锁会堵塞其他全部的关于A表的语句,包括DESC/SHOW TABLE STATUS/USE DB(非-A) 这种只上MDL_SHARED_HIGH_PRIO(SH)MDL LOCK 的语句。STATE统一为Waiting for table metadata lock。
模拟测试

测试环境:

5.7.14
GITD关闭
RR隔离级别

使用脚本:

8902ab1888b71d3b158a7dc523d628afbaa77a11


步骤如下:

ee96d7862331174e6d84891032bb35faf5491d9e

最后我们看到的等待状态如下:

b5e11fc43ed9cb7ee3d11254c4fff04e485edebe

这样我们就完美的模拟出线上的状态,如果我们杀掉session1中的事物,自然就全部解锁了,让我们再来看一下performance_schema.metadata_locks中的输出:

e4deb910723ea510f98065a738fe8fe1f6f4a469

我们可以看到如上的输出,但是需要注意LOCK_TYPE: SHARED它不可能堵塞LOCK_TYPE: SHARED_HIGH_PRIO(可以参考附录或者我以前写的MDL LOCK分析的文章)如上文分析这里实际上是做了升级操作升级为了MDL_EXCLUSIVE(X)。

总结
  • RC模式下虽然CREATE TABLE A SELECT B中B表不会上任何INNODB ROW LOCK但是如果B表非常大那么A表也会处于MDL_EXCLUSIVE(X)保护下,因此也会触发USE DB\SHOW TABLE STATUS等待的情况。
  • 如果打开GTID不能使用CREATE TABLE A SELECT B这样的语句。
  • 对于DML/DDL混用的系统一定要注意并发,就像本例中如果注意到高并发下的情况可以想办法避免。
  • 这个案列再次说明了长期不提交的事物可能引发悲剧,所以建议监控超过N秒没结束的事务。
附录

MDL LOCK TYPE

1cad9f1d0874c2811b7abd76d9e56f38a5c64206

兼容性矩阵

2bb6cdeaaf12c940d443ce544009c4a0263f4179

等待队列优先级矩阵

c6aaacc5191ea4919171bd7f04ac23fae09fb305


原文发布时间为:2017-12-22本文作者:高鹏本文来自云栖社区合作伙伴“ 数据和云”,了解相关信息可以关注“ 数据和云”微信公众号
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
缓存 NoSQL 关系型数据库
MySQL缓存策略(一致性问题、数据同步以及缓存故障)
MySQL缓存策略(一致性问题、数据同步以及缓存故障)
37 1
|
2月前
|
存储 SQL 关系型数据库
⑩⑤【DB】详解MySQL存储过程:变量、游标、存储函数、循环,判断语句、参数传递..
⑩⑤【DB】详解MySQL存储过程:变量、游标、存储函数、循环,判断语句、参数传递..
36 0
|
5月前
|
存储 关系型数据库 分布式数据库
云原生关系型数据库Polar DB MySQL版(二)
云原生关系型数据库Polar DB MySQL版(二)
147 0
|
5月前
|
存储 关系型数据库 MySQL
云原生关系型数据库Polar DB MySQL版(一)
云原生关系型数据库Polar DB MySQL版(一)
198 0
|
6月前
|
SQL 关系型数据库 MySQL
check the manual that corresponds to your MySQL server version for the right syntax to use near lin
注意这种一般情况下是语法问题,说明mysql的各种语句还是不够熟练, (1)看表名是不是使用了Mysql的关键字,如果是,请立马改掉; (2)语句给出哪里错误,如我的题目中给出“near”,说明在那附近有问题。 (3)检查mybatis中mapper里sql语句占位符是否写对,我的问题是将#{name}写成了#(name),尴尬。
123 0
|
8月前
|
存储 Prometheus 监控
记一次MySQL DB实例磁盘告警的处理过程
记一次MySQL DB实例磁盘告警的处理过程
113 0
记一次MySQL DB实例磁盘告警的处理过程
|
9月前
|
SQL 缓存 关系型数据库
故障案例:MySQL唯一索引有重复值,官方却说This is not a bug
故障案例:MySQL唯一索引有重复值,官方却说This is not a bug
118 0
|
监控 算法 安全
MySQL:5.6 大事务show engine innodb status故障一例
MySQL:5.6 大事务show engine innodb status故障一例
162 0
MySQL:5.6 大事务show engine innodb status故障一例
|
SQL 存储 SpringCloudAlibaba
MySQL 千万数据量深分页优化, 拒绝线上故障!
MySQL 千万数据量深分页优化, 拒绝线上故障!
506 0
MySQL 千万数据量深分页优化, 拒绝线上故障!