【Mysql】MySQL 5.7中如何定位DDL被阻塞的問題

原文地址：https://mp.weixin.qq.com/s/lD2gjyUgt4pmWdVXqqXk3w

在上篇文章《 MySQL表結(jié)構(gòu)變更，不可不知的Metadata Lock 》中，我們介紹了MDL引入的背景，及基本概念，從“道”的層面知道了什么是MDL。下面就從“術(shù)”的層面看看如何定位MDL的相關(guān)問題。

在MySQL 5.7中，針對MDL，引入了一張新表performance_schema.metadata_locks，該表可對外展示MDL的相關(guān)信息，包括其作用對象，類型及持有等待情況。

開啟MDL的instrument

但是相關(guān)instrument并沒有開啟（MySQL 8.0是默認(rèn)開啟的），其可通過如下兩種方式開啟，

臨時(shí)生效

修改performance_schema.setup_instrume nts表，但實(shí)例重啟后，又會(huì)恢復(fù)為默認(rèn)值。

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

永久生效

在配置文件中設(shè)置

[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

測試場景

下面結(jié)合一個(gè)簡單的Demo，來看看在MySQL 5.7中如何定位DDL操作的阻塞問題。

session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> delete from slowtech.t1 where id=2;
Query OK, 1 row affected (0.00 sec)
session1> select * from slowtech.t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)
session1> update slowtech.t1 set name='c' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session2> alter 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 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
|  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
3 rows in set (0.00 sec)
session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from 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     | GRANTED     |              29 |
| SCHEMA      | slowtech           | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |              29 |
| TABLE       | slowtech           | t1             | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |              29 |
| TABLE       | slowtech           | t1             | EXCLUSIVE           | TRANSACTION   | PENDING     |              29 |
| TABLE       | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   | GRANTED     |              28 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
6 rows in set (0.00 sec)

這里，重點(diǎn)關(guān)注lock_status，"PENDING"代表線程在等待MDL，而"GRANTED"則代表線程持有MDL。

如何找出引起阻塞的會(huì)話

結(jié)合owner_thread_id，可以可到，是29號(hào)線程在等待27號(hào)線程的MDL，此時(shí)，可kill掉52號(hào)線程。

但需要注意的是，owner_thread_id給出的只是線程ID，并不是show processlist中的ID。如果要查找線程對應(yīng)的processlist id，需查詢performance_schema.threads表。

session3> select * from performance_schema.threads where thread_id in (27,29)\G
*************************** 1. row ***************************
          THREAD_ID: 27
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 2
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 214
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 9800
*************************** 2. row ***************************
          THREAD_ID: 29
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 4
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 172
  PROCESSLIST_STATE: Waiting for table metadata lock
   PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 9907
2 rows in set (0.00 sec)

將這兩張表結(jié)合，借鑒sys.innodb_lock _waits的輸出，實(shí)際上我們也可以直觀地呈現(xiàn)MDL的等待關(guān)系。

SELECT
    a.OBJECT_SCHEMA AS locked_schema,
    a.OBJECT_NAME AS locked_table,
    "Metadata Lock" AS locked_type,
    c.PROCESSLIST_ID AS waiting_processlist_id,
    c.PROCESSLIST_TIME AS waiting_age,
    c.PROCESSLIST_INFO AS waiting_query,
    c.PROCESSLIST_STATE AS waiting_state,
    d.PROCESSLIST_ID AS blocking_processlist_id,
    d.PROCESSLIST_TIME AS blocking_age,
    d.PROCESSLIST_INFO AS blocking_query,
    concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
    performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G
*************************** 1. row ***************************
               locked_schema: slowtech
                locked_table: t1
                 locked_type: Metadata Lock
      waiting_processlist_id: 4
                 waiting_age: 259
               waiting_query: alter table slowtech.t1 add c1 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 2
                blocking_age: 301
              blocking_query: NULL
sql_kill_blocking_connection: KILL 2
1 row in set (0.00 sec)

輸出一目了然，DDL操作如果要獲得MDL，執(zhí)行kill 2即可。

官方的sys.schema table lock_waits

實(shí)際上，MySQL 5.7在sys庫中也集成了類似功能，同樣的場景，其輸出如下，

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: slowtech
                 object_name: t1
           waiting_thread_id: 29
                 waiting_pid: 4
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table slowtech.t1 add c1 int
          waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 27
                blocking_pid: 2
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
*************************** 2. row ***************************
               object_schema: slowtech
                 object_name: t1
           waiting_thread_id: 29
                 waiting_pid: 4
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table slowtech.t1 add c1 int
          waiting_query_secs: 446
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 29
                blocking_pid: 4
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
2 rows in set (0.00 sec)

具體分析下官方的輸出，

只有一個(gè)alter table操作，卻產(chǎn)生了兩條記錄，而且兩條記錄的kill對象竟然還不一樣，對表結(jié)構(gòu)不熟悉及不仔細(xì)看記錄內(nèi)容的話，難免會(huì)kill錯(cuò)對象。

不僅如此，如果有N個(gè)查詢被DDL操作堵塞，則會(huì)產(chǎn)生N*2條記錄。在阻塞操作較多的情況下，這N*2條記錄完全是個(gè)噪音。

而之前的SQL，無論有多少操作被阻塞，一個(gè)alter table操作，就只會(huì)輸出一條記錄。

如何查看阻塞會(huì)話已經(jīng)執(zhí)行過的操作

但上面這個(gè)SQL也有遺憾，其blocking_query為NULL，而在會(huì)話1中，其明明已經(jīng)執(zhí)行了三個(gè)SQL。

這個(gè)與performance_schema.threads（類似于show processlist）有關(guān)，其只會(huì)輸出當(dāng)前正在運(yùn)行的SQL，對于已經(jīng)執(zhí)行過的，實(shí)際上是沒辦法看到。

但在線上，kill是一個(gè)需要謹(jǐn)慎的操作，畢竟你很難知道kill的是不是業(yè)務(wù)關(guān)鍵操作？又或者，是個(gè)批量update操作？那么，有沒有辦法抓到該事務(wù)之前的操作呢？

答案，有。

即Performance Schema中記錄Statement Event（操作事件）的表，具體包括events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三個(gè)。

三者的表結(jié)構(gòu)完全一致，其中，events_ statements_ history又包含了events_ statements_ current的操作，所以我們這里會(huì)使用events_ statements_ history。

終極SQL如下，

SELECT
    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) AS blocking_query,
    sql_kill_blocking_connection
FROM
    (
        SELECT
            b.OWNER_THREAD_ID AS granted_thread_id,
            a.OBJECT_SCHEMA AS locked_schema,
            a.OBJECT_NAME AS locked_table,
            "Metadata Lock" AS locked_type,
            c.PROCESSLIST_ID AS waiting_processlist_id,
            c.PROCESSLIST_TIME AS waiting_age,
            c.PROCESSLIST_INFO AS waiting_query,
            c.PROCESSLIST_STATE AS waiting_state,
            d.PROCESSLIST_ID AS blocking_processlist_id,
            d.PROCESSLIST_TIME AS blocking_age,
            d.PROCESSLIST_INFO AS blocking_query,
            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
        FROM
            performance_schema.metadata_locks a
        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
        AND a.OBJECT_NAME = b.OBJECT_NAME
        AND a.lock_status = 'PENDING'
        AND b.lock_status = 'GRANTED'
        AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
        AND a.lock_type = 'EXCLUSIVE'
        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
    ) t1,
    (
        SELECT
            thread_id,
            group_concat(    CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
        FROM
            performance_schema.events_statements_history
        GROUP BY thread_id
    ) t2
WHERE
    t1.granted_thread_id = t2.thread_id \G
*************************** 1. row ***************************
               locked_schema: slowtech
                locked_table: t1
                 locked_type: Metadata Lock
      waiting_processlist_id: 4
                 waiting_age: 294
               waiting_query: alter table slowtech.t1 add c1 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 2
                blocking_age: 336
              blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
sql_kill_blocking_connection: KILL 2
1 row in set, 1 warning (0.00 sec)

從上面的輸出可以看到，blocking_query中包含了會(huì)話1中當(dāng)前事務(wù)的所有操作，按執(zhí)行的先后順序輸出。

需要注意的是，默認(rèn)情況下，events_statements_history只會(huì)保留每個(gè)線程最近的10個(gè)操作，如果事務(wù)中進(jìn)行的操作較多，實(shí)際上也是沒辦法抓全的。

Anyway, it is better than nothing！