这里有新鲜出炉的 Mysql 教程, 程序狗速度看过来!
Mysql 关系型数据库管理系统
MySQL 是一个开放源码的小型关联式数据库管理系统, 开发者为瑞典 MySQL AB 公司 MySQL 被广泛地应用在 Internet 上的中小型网站中由于其体积小速度快总体拥有成本低, 尤其是开放源码这一特点, 许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库
这篇文章通过实例给大家分析了在 MYSQL 中出现 USE DB 堵塞的处理办法以及思考思路, 有兴趣的朋友学习下吧
遇到故障, 我们往往想的是如何解决这个故障, 而不是从故障的根本去思考出现这个故障的原因? 这样的结果, 只能使我们得到了鱼, 失去了渔今天, 我们就来分享一个由 USE DB 堵塞故障引发的思考案例
故障描述
今天一个朋友遇到数据库遇到一个严重的故障, 故障环境如下:
MYSQL 5.6.16
RR 隔离级别
GITD 关闭
表现如下:
use db 不能进入数据库
show table status 不能查询到表信息
schema.processlist 来看有大量的 Waiting for table metadata lock
情急之下他杀掉了一大堆线程后发现还是不能恢复, 最后杀掉了一个没有及时提交的事物才恢复正常也仅仅留下了如下图的一个截图:
故障信息提取
还是回到上图, 我们可以归纳一下语句类型如下:
1CREATE TABLE A AS SELECT B
其 STATE 为 sending data
2DROP TABLE A
其 STATE 为 Waiting for table metadata lock
3SELECT * 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:
/article/18/0126/362508.html
本节关于 MDL LOCK 的验证使用下面两种方式:
方式一: 笔者在 MDL LOCK 源码加锁函数处加日志输出, 如果要分析各种语句加 MDL LOCK 的类型还只能用这种方式, 因为 MDL LOCK 加锁往往一闪而过, performance_schema.metadata_locks 没有办法观察到
方式二: 处于堵塞情况下使用 5.7 版本的 performance_schema.metadata_locks 观察
在 P_S 中打开 mdl 监测方法如下:
一关于 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 是一致的参考不再赘述:
从他反应的情况因为他在最后杀掉了一个长期的未提交的事物所以他因为是情况 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)
方式一
方式二
两种方式都能观察到 MDL_SHARED_HIGH_PRIO(SH)的存在并且我模拟的是处于堵塞情况下的
但是 MDL_SHARED_HIGH_PRIO(SH) 是一个优先级非常高的一个 MDL LOCK 类型表现如下:
兼容性:
阻塞队列优先级:
其被堵塞的条件除了被 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) , 然后进行实际测试不出所料确实是这样的如下:
方式一
方式二
这里比较遗憾在 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) 如下:
方式一
方式二
可以看到确实有 MDL_SHARED_READ(SR)的存在, 当前处于堵塞状态
其兼容性如下:
显然 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)不兼容如下:
方式一
方式二
其中 EXCLUSIVE 就是我们说的 MDL_EXCLUSIVE(X)它确实存在当前处于堵塞
六为何 use db 也会堵塞?
如果使用 mysql 客户端不使用 - A 选项 (或者 no-auto-rehash) 在 USE DB 的时候至少要做如下事情:
1 对 db 下每个表上 MDL (SH) lock 如下(调用 MDL_context::acquire_lock 这里给出堵塞时候的信息)
方式一
方式二
可以看到 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 隔离级别
使用脚本:
步骤如下:
session1 session2 session3 session4------use test;---use test;begin; delete from b;------------use test;create table a asselect * from b;(由于 b 表 innodb row lock 堵塞)------------show table status like 'a';(由于 a 表 MDL LOCK 堵塞)------------use test(由于 a 表 MDL LOCK 堵塞)
最后我们看到的等待状态如下:
这样我们就完美的模拟出线上的状态, 如果我们杀掉 session1 中的事物, 自然就全部解锁了, 让我们再来看一下 performance_schema.metadata_locks 中的输出:
我们可以看到如上的输出, 但是需要注意 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
兼容性矩阵
等待队列优先级矩阵
来源: http://www.phperz.com/article/18/0224/362509.html