MySQL 5.7中如何定位DDL被阻塞的问题_mysql

概述在上篇文章《MySQL表结构变更，不可不知的Metadata Lock》中，我们介绍了MDL引入的背景，及基本概念，从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

在上篇文章《MysqL表结构变更，不可不知的Metadata Lock》中，我们介绍了MDL引入的背景，及基本概念，从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

在MysqL 5.7中，针对MDL，引入了一张新表performance_schema.Metadata_locks，该表可对外展示MDL的相关信息，包括其作用对象，类型及持有等待情况。

开启MDL的instrument

但是相关instrument并没有开启（MysqL 8.0是默认开启的），其可通过如下两种方式开启，

临时生效

修改performance_schema.setup_instrume nts表，但实例重启后，又会恢复为默认值。

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES',TIMED 'WHERE name wait/lock/Metadata/sql/mdl';

永久生效在配置文件中设置

[MysqLd]performance-schema-instrument=wait/lock/Metadata/sql/mdl=ON'

测试场景

下面结合一个简单的Demo，来看看在MysqL 5.7中如何定位DDL *** 作的阻塞问题。

session1> begin;query OK,0 rows affected (0.00 sec)session1delete from slowtech.t1 where ID=21 row affected (select * from slowtech.t1;+------+------+| ID   | name ||    1 | a    1 row in set (update slowtech.t1 set namec' 1 sec)Rows matched: 1  Changed: 1  Warnings: 0session2alter table slowtech.t1 add c1 int; ##被阻塞session3> show processList;--+------+-----------+------+---------+------+---------------------------------+------------------------------------+| ID | User | Host      | db   | Command | Time | State                           | Info                               |  2 | root | localhost | NulL | Sleep   |   51 |                                 NulL                               3 | query   0 | starting                        | show processList                   4 9 | Waiting for table Metadata lock int 3 rows  sec)session3select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_ID  performance_schema.Metadata_locks;-----------+--------------------+----------------+---------------------+---------------+-------------+-----------------+| object_type | object_schema      object_name    | lock_type           | lock_duration | lock_status | owner_thread_ID table       | slowtech           | t1             | SHARED_WRITE        TRANSACTION   | GRANTED     |              27 | GLOBAL      NulL               NulL           | INTENTION_EXCLUSIVE | STATEMENT     29 SCHEMA      | SHARED_UPGRADABLE   | EXCLUSIVE           | PENDING     | performance_schema | Metadata_locks | SHARED_READ         28 6 rows 0.00 sec)

这里，重点关注lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。

如何找出引起阻塞的会话

结合owner_thread_ID，可以可到，是29号线程在等待27号线程的MDL，此时，可kill掉52号线程。

但需要注意的是，owner_thread_ID给出的只是线程ID，并不是show processList中的ID。如果要查找线程对应的processList ID，需查询performance_schema.threads表。

session3from performance_schema.threads where thread_ID in (27,29)\G*************************** 1. row ***************************          THREAD_ID: 27               name: thread/sql/one_connection               TYPE: FOREGROUND     PROCESSList_ID:    PROCESSList_USER: root   PROCESSList_HOST: localhost     PROCESSList_DB: NulLPROCESSList_COMMAND: Sleep   PROCESSList_TIME: 214  PROCESSList_STATE:    PROCESSList_INFO:    PARENT_THREAD_ID:                RolE:        INSTRUMENTED: YES            HISTORY: YES    CONNECTION_TYPE: Socket       THREAD_OS_ID: 98002. row 4PROCESSList_COMMAND: query   PROCESSList_TIME: 172  PROCESSList_STATE: Waiting table Metadata lock   PROCESSList_INFO: 99072 rows 0.00 sec)

将这两张表结合，借鉴sys.innodb_lock _waits的输出，实际上我们也可以直观地呈现MDL的等待关系。

SELECT    a.OBJECT_SCHEMA AS locked_schema,a.OBJECT_name  locked_table,"Metadata Lock"  locked_type,c.PROCESSList_ID  waiting_processList_ID,c.PROCESSList_TIME  waiting_age,c.PROCESSList_INFO  waiting_query,c.PROCESSList_STATE  waiting_state,d.PROCESSList_ID  blocking_processList_ID,d.PROCESSList_TIME  blocking_age,d.PROCESSList_INFO  blocking_query,concat(KILL  sql_kill_blocking_connectionFROM    performance_schema.Metadata_locks aJOIN performance_schema.Metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMAAND a.OBJECT_name = b.OBJECT_nameAND a.lock_status PENDING'AND b.lock_status GRANTEDAND a.OWNER_THREAD_ID <> b.OWNER_THREAD_IDAND a.lock_type EXCLUSIVEJOIN performance_schema.threads c ON a.OWNER_THREAD_ID  c.THREAD_IDJOIN performance_schema.threads d ON b.OWNER_THREAD_ID  d.THREAD_ID\G               locked_schema: slowtech                locked_table: t1                 locked_type: Metadata Lock      waiting_processList_ID:                  waiting_age: 259               waiting_query:                waiting_state: Waiting  Metadata lock     blocking_processList_ID:                 blocking_age: 301              blocking_query: sql_kill_blocking_connection: KILL 20.00 sec)

输出一目了然，DDL *** 作如果要获得MDL，执行kill 2即可。

官方的sys.schematablelock_waits

实际上，MysqL 5.7在sys库中也集成了类似功能，同样的场景，其输出如下，

MysqL sys.schema_table_lock_waits\G               object_schema: slowtech                 object_name: t1           waiting_thread_ID:                  waiting_pID:              waiting_account: root@localhost           waiting_lock_type: EXCLUSIVE       waiting_lock_duration: TRANSACTION          waiting_query_secs: 446 waiting_query_rows_affected:  waiting_query_rows_examined:           blocking_thread_ID:                 blocking_pID:             blocking_account: root          blocking_lock_type: SHARED_READ      blocking_lock_duration:      sql_kill_blocking_query: KILL query           blocking_lock_type: SHARED_UPGRADABLE      blocking_lock_duration: 40.00 sec)

具体分析下官方的输出，

只有一个alter table *** 作，却产生了两条记录，而且两条记录的kill对象竟然还不一样，对表结构不熟悉及不仔细看记录内容的话，难免会kill错对象。

不仅如此，如果有N个查询被DDL *** 作堵塞，则会产生N*2条记录。在阻塞 *** 作较多的情况下，这N*2条记录完全是个噪音。

而之前的sql，无论有多少 *** 作被阻塞，一个alter table *** 作，就只会输出一条记录。

如何查看阻塞会话已经执行过的 *** 作

但上面这个sql也有遗憾，其blocking_query为NulL，而在会话1中，其明明已经执行了三个sql。

这个与performance_schema.threads（类似于show processList）有关，其只会输出当前正在运行的sql，对于已经执行过的，实际上是没办法看到。

但在线上，kill是一个需要谨慎的 *** 作，毕竟你很难知道kill的是不是业务关键 *** 作？又或者，是个批量update *** 作？那么，有没有办法抓到该事务之前的 *** 作呢？

答案，有。

即Performance Schema中记录Statement Event（ *** 作事件）的表，具体包括events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三个。

三者的表结构完全一致，其中，events_statements_history又包含了events_statements_current的 *** 作，所以我们这里会使用events_statements_history。

终极sql如下，

    locked_schema,locked_table,locked_type,waiting_processList_ID,waiting_age,waiting_query,waiting_state,blocking_processList_ID,blocking_age,substring_index(sql_text,"transaction_begin;",1)">-1)     (                    b.OWNER_THREAD_ID  granted_thread_ID,a.OBJECT_SCHEMA  sql_kill_blocking_connection                    performance_schema.Metadata_locks a         b.OBJECT_SCHEMA        OBJECT_name        '         b.OWNER_THREAD_ID         c.THREAD_ID         d.THREAD_ID    ) t1,(                    thread_ID,group_concat(   CASE WHEN EVENT_name statement/sql/beginTHEN "transaction_begin" ELSE sql_text END ORDER BY event_ID SEParaTOR ";" )  sql_text                    performance_schema.events_statements_history        GROUP BY thread_ID    ) t2WHERE    t1.granted_thread_ID  t2.thread_ID \G2943362;from slowtech.t1;set,1); Font-weight: bold">1 warning (0.00 sec)