MySQL 执行DDL语句 hang住了怎么办?

• MySQL 执行DDL语句 hang住了怎么办? 不要慌，先点支烟，听我娓娓道来！
• 前两天，早上7点多的时候，抓起手机忽然看到了圈内的一则DDL语句 hang住的案例，阅读到文末，发现文中留有一些疑问（当然，文章是7月份的，现在或许作者已经解决了这些疑问），于是一咕噜从床上爬起来，按照文中的复现方法操作了一遍（复现方法见文末），然后，按照自己的思路解决了该疑问，现在将整个过程整理出来分享给大家。

环境

• 数据库版本：MySQL 5.7.27
• 数据信息
  • sysbench模拟2张1000W的表
• 操作系统版本：CentOS Linux release 7.3.1611 (Core)
• 服务器信息
  • CPU：Intel(R) Xeon(R) CPU E5-4627 v2 @ 3.30GHz * 2
  • 内存：248G
  • 磁盘：1.6T LSI Flash卡
  • 网卡：万兆网卡

现象

• 当我们发现一个故障问题时，首先需要做的事情，就是先确认现象，也就是先要自己亲眼瞅见故障长什么样子，而不是直接上去就是一通胡乱排查。尤其是故障是别人反馈过来的时候，一定要首先确认故障现象表现及其真实性（别人反馈过来的问题，很多时候根本就不是问题，而是他自己姿势不对）。
• 在本文中，提到的故障现象是DBA侧自己执行DDL语句修改字段长度时自己发现的（当然，这里是模拟的DBA侧操作），那么，根据上述准则，我们先开启另外一个会话，查看执行DDL语句 hang住具体是什么情况？

1 2 3 4 5 6 7 8 9 10 11

# 使用show processlist语句查看会话状态信息，发现DDL语句的state列值为Waiting for table metadata lock，表示在等待MDL元数据锁。根据MySQL 5.7及其之后的版本中的online ddl特性，该语句应该立即执行完成（它只会修改元数，因为这里只是修改了字段长度，并没有修改字段的其他属性），因此，故障现象确认 admin@localhost : (none) 11:48:22> show processlist; +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------+ | 27 | admin | localhost | sbtest | Sleep | 123 | | NULL | | 28 | admin | localhost | sbtest | Query | 102 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' | | 29 | admin | localhost | NULL | Query | 0 | starting | show processlist | | 30 | admin | localhost | NULL | Sleep | 93 | | NULL | +----+-------+-----------+--------+---------+------+---------------------------------+-------------------------------+ 4 rows in set (0.00 sec)

• 友情提示：确认故障现象的位置不要搞错，在什么位置发现故障问题，首先就在什么位置进行故障确认。举个反例，曾经我碰到一个同学，在一个“应用-->LVS-->分库中间件-->读写分离中间件-->数据库主从集群”的架构层级中，应用侧反馈数据库连接很慢，这位同学直接登录到数据库中去确认现象了，显然，确认故障现象时搞错了位置，在这个反例中确认故障现象的位置应该以应用侧反馈故障现象的位置为准（首先使用LVS的VIP尝试连接），逐层往下确认

分析

• 确认了故障现象，DDL语句hang住的原因是因为在等待MDL元数据锁，但不知道MDL元数据锁被谁持有了。接下来，就要围绕这个现象，推测可能导致该问题的一些原因了，哪些原因可能导致该问题呢？我们可以按照下面的思路进行逐个排除
  • 服务器的主机负载过高（CPU、内存、磁盘吞吐与IOPS、网卡带宽），有没有别的程序挤占了数据库进程的资源
    * 你也许会说，这里的现象很明显是因为在等MDL锁，跟服务器负载无关，肯定是有人加了锁没释放。但我想提醒的是，该现象真的是一个单纯的问题吗？会不会是一个链式反应导致的？等待MDL锁虽然是DDL语句被阻塞的原因，但也许它同时也是服务器高负载的现象与结果
  • 数据库进程的负载过高
    * 数据库中的活跃会话数量及其状态
    * 数据库的QPS/TPS
  • 存在其他会话正在执行DML语句，或执行了某些DML语句之后事务未及时提交、或者其他某个会话也同时在执行某个DDL语句修改sbtest1表的表结构信息

排查

• 有了思路，接下来就按照上文中提到的思路逐个进行排查

• 首先，我们查看主机负载信息，通过下图我们可以看到，主机基本处于空载状态，毫无压力

• 然后，我们查看数据库的活跃会话数量及其状态，我们可以看到数据库中并没有大量会话，也不存在正在执行的DML语句在操作表sbtest1，也不存在同时有其他会话同时使用DDL在操作相同的表，但这里无法确认是否存在未提交的事务

1 2 3 4 5 6 7 8 9 10 11

# 反复多执行几次show processlist语句 admin@localhost : (none) 11:49:10> show processlist; +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+ | 27 | admin | localhost | sbtest | Sleep | 149 | | NULL | | 28 | admin | localhost | sbtest | Query | 128 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' | | 29 | admin | localhost | NULL | Query | 0 | starting | show processlist | | 30 | admin | localhost | NULL | Sleep | 119 | | NULL | +----+-------+-----------+--------+---------+------+---------------------------------+---------------------------------------+ 4 rows in set (0.00 sec)

• 通过上述步骤，已确认在负载层面并没有问题，此时，我们需要重点确认是否存在某个会话执行了某些DML语句之后事务未及时提交，如何确认这些信息呢？我们可以通过performance_schema和information_schema中的锁和事务相关的表进行查询确认

• 先查看information_schema中记录的事务信息

1 2 3 4 5 6 7 8 9 10 11 12 13

# 发现并没有事务存在... admin@localhost : sbtest 05:49:17> select * from information_schema.innodb_trx\G Empty set (0.00 sec) # 也可以顺便使用sys.innodb_lock_waits视图确认是否存在一些事务锁等待 admin@localhost : performance_schema 06:27:35> select * from sys.innodb_lock_waits\G Empty set, 3 warnings (0.00 sec)  # 查询结果为空 查看performance_schema下的MDL元数据锁记录信息  # WTF..居然为空 admin@localhost : sbtest 06:00:21> select * from performance_schema.metadata_locks; Empty set (0.00 sec) #