MySQL metadata lock 상황 한눈에 확인하기

트래픽이 많아 쿼리가 많이 수행되는 환경에서는 단순 쿼리로는 metadata lock 을 찾기 힘들 때도 있습니다.
percona에서 공유한 metadata lock holder와 waiter를 쉽게 확인할 수 있는 프로시져 관련 글이 있어 공유드립니다.
(https://www.percona.com/blog/2016/12/28/quickly-troubleshooting-metadata-locks-mysql-5-7/)

procedure

USE test;
DROP PROCEDURE IF EXISTS procShowMetadataLockSummary;
delimiter //
CREATE PROCEDURE procShowMetadataLockSummary()
BEGIN
    DECLARE table_schema VARCHAR(64);
    DECLARE table_name VARCHAR(64);
    DECLARE id bigint;
    DECLARE time bigint;
    DECLARE info longtext;
    DECLARE curMdlCount INT DEFAULT 0;
    DECLARE curMdlCtr INT DEFAULT 0;
    DECLARE curMdl CURSOR FOR SELECT * FROM tmp_blocked_metadata;
    DROP TEMPORARY TABLE IF EXISTS tmp_blocked_metadata;
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_blocked_metadata (
       table_schema varchar(64) ,
       table_name varchar(64) ,
       id bigint ,
       time bigint ,
       info longtext ,
       PRIMARY KEY(table_schema, table_name)
    );
    REPLACE tmp_blocked_metadata(table_schema,table_name,id,time,info) SELECT mdl.OBJECT_SCHEMA, mdl.OBJECT_NAME, t.PROCESSLIST_ID, t.PROCESSLIST_TIME, t.PROCESSLIST_INFO FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON mdl.OWNER_THREAD_ID = t.THREAD_ID WHERE mdl.LOCK_STATUS='PENDING' and mdl.LOCK_TYPE='EXCLUSIVE' ORDER BY mdl.OBJECT_SCHEMA,mdl.OBJECT_NAME,t.PROCESSLIST_TIME ASC;
    OPEN curMdl;
    SET curMdlCount = (SELECT FOUND_ROWS());

    WHILE (curMdlCtr < curMdlCount)
    DO
      FETCH curMdl INTO table_schema, table_name, id, time, info;
      SELECT CONCAT_WS(' ','PID',t.PROCESSLIST_ID,'has metadata lock on', CONCAT(mdl.OBJECT_SCHEMA,'.',mdl.OBJECT_NAME), 'with current state', CONCAT_WS('','[',t.PROCESSLIST_STATE,']'), 'for', t.PROCESSLIST_TIME, 'seconds and is currently running', CONCAT_WS('',"[",t.PROCESSLIST_INFO,"]")) AS 'Process(es) that have the metadata lock' FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID WHERE mdl.LOCK_STATUS='GRANTED' AND mdl.OBJECT_SCHEMA collate utf8mb4_general_ci = table_schema and mdl.OBJECT_NAME collate utf8mb4_general_ci = table_name AND mdl.OWNER_THREAD_ID NOT IN(SELECT mdl2.OWNER_THREAD_ID FROM performance_schema.metadata_locks mdl2 WHERE mdl2.LOCK_STATUS='PENDING' AND mdl.OBJECT_SCHEMA = mdl2.OBJECT_SCHEMA and mdl.OBJECT_NAME = mdl2.OBJECT_NAME);  
      SELECT CONCAT_WS(' ','PID', id, 'has been waiting for metadata lock on',CONCAT(table_schema,'.', table_name),'for', time, 'seconds to execute', CONCAT_WS('','[',info,']')) AS 'Oldest process waiting for metadata lock';
      SET curMdlCtr = curMdlCtr + 1;
      SELECT CONCAT_WS(' ','PID', t.PROCESSLIST_ID, 'has been waiting for metadata lock on',CONCAT(table_schema,'.', table_name),'for', t.PROCESSLIST_TIME, 'seconds to execute', CONCAT_WS('','[',t.PROCESSLIST_INFO,']')) AS 'Other queries waiting for metadata lock' FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID WHERE mdl.LOCK_STATUS='PENDING' AND mdl.OBJECT_SCHEMA collate utf8mb4_general_ci = table_schema and mdl.OBJECT_NAME collate utf8mb4_general_ci= table_name AND mdl.OWNER_THREAD_ID AND t.PROCESSLIST_ID <> id ;
    END WHILE;
    CLOSE curMdl;
END//

delimiter ;

=> 프로시져 내 임시테이블 tmp_blocked_metadata 의 값을 읽어 performance_schema의 테이블의 조건으로 넣어줄 때
DB 환경에 따라 collation error 가 발생할 수 있는데 그럴 땐 아래와 같이 collate 를 수정하면 됩니다.

mdl.OBJECT_NAME collate utf8mb4_general_ci = table_name

TEST

  • A session
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from tb_test where id=100000;
    Query OK, 1 row affected (0.01 sec)
  • B session
    mysql> alter table tb_test drop column ghost3;
  • metadata 조회
mysql> call procShowMetadataLockSummary();
+-------------------------------------------------------------------------------------------------------------+
| Process(es) that have the metadatalock                                                                      |
+-------------------------------------------------------------------------------------------------------------+
| PID 811262 has metadata lock on test.tb_test with current state [] for 8 seconds and iscurrently running [] |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------------------------------------------------------------------------------+
| Oldest process waiting for metadatalock                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------+
| PID 811430 has been waiting for metadata lock on test.tb_test for 5 seconds to execute [altertable tb_test drop column ghost3] |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)