MySQL 5.7中如何定位DDL被阻塞的難題

2024-07-24 12:31:48

字體：大中小

來源：轉載

供稿：網友

在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 = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';
永久生效

但需要注意的是，owner_thread_id給出的只是線程ID，并不是show processlist中的ID。如果要查找線程對應的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)
將這兩張表結合，借鑒sys.innodb_lock _waits的輸出，實際上我們也可以直觀地呈現MDL的等待關系。

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

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)
具體分析下官方的輸出，

只有一個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，對于已經執行過的，實際上是沒辦法看到。

（編輯：武林網）

上一篇：MySQL內的derived table

下一篇：將數據從文本導入至mysql