MySQL Waiting for Table Metadata Lock 문제 해결 방법

이 포스트는 MySQL InnoDB에서 Waiting for Table Metadata Lock으로 인해 데이터베이스(DB)의 성능저하가 발생할경우에 대한 대처 방법을 안내 합니다.

Metadata Lock이 발생하는 경우

1. DDL 문장 실행: 데이터베이스의 구조를 변경하는 DDL(Data Definition Language) 문장을 실행할 때 Metadata lock이 발생할 수 있습니다.

2. 트랜잭션 처리: 트랜잭션 처리를 할 때 Metadata lock이 발생할 수 있습니다. 트랜잭션 처리를 하면, MySQL은 트랜잭션 동안 사용하는 데이터베이스 객체에 대한 Metadata lock을 설정합니다.

3. 레코드 락: MySQL에서 레코드 락을 설정할 때 Metadata lock이 발생할 수 있습니다. 레코드 락은 특정 레코드에 대한 접근을 제어하는 락으로, MySQL은 레코드 락을 설정할 때 Metadata lock을 사용합니다.

4. 인덱스 생성: INSERT나 UPDATE 쿼리를 실행할 때, MySQL에서는 인덱스를 생성하거나 수정하기 위해 Metadata lock을 사용합니다.

5. 테이블 잠금: INSERT나 UPDATE 쿼리를 실행할 때, MySQL에서는 테이블 잠금을 사용하여 다른 세션의 접근을 제한합니다. 이때, 테이블 잠금은 Metadata lock과 관련이 있습니다.

솔루션

Lock을 유발 한 프로세스 종료

MySQL Console에서 SHOW FULL PROCESSLIST를 실행하여 Waiting for Table Metadata Lock의 프로세스 및 정보(Info)를 확인 한다.

mysql> SHOW FULL PROCESSLIST
     Id  User       Host                  db      Command    Time  State                            Info                   
-------  ---------  --------------------  ------  -------  ------  -------------------------------  -------------------------------
1894823  dbuser     172.30.3.31:32982     dbname  Query         0  Waiting for table metadata lock  INSERT INTO table_name SET ...

또는 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE [Condition...] 을 통해 특정 데이터를 조회 할 수 있다.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 30 AND STATE LIKE '%Waiting for table metadata lock%'
     ID  USER       HOST               DB      COMMAND    TIME  STATE                            INFO                     
-------  ---------  -----------------  ------  -------  ------  -------------------------------  --------------------------------
1920735  dbuser     172.30.3.31:32982  dbname  Query        35  Waiting for table metadata lock  INSERT INTO table_name SET ...
1920755  dbuser     172.30.3.31:49340  dbname  Query        35  Waiting for table metadata lock  INSERT INTO table_name SET ...
1921446  dbuser     172.30.3.14:38118  dbname  Query        49  Waiting for table metadata lock  UPDATE table_name SET ...
1921339  dbuser     172.30.3.14:37524  dbname  Query        34  Waiting for table metadata lock  INSERT INTO ...
1921181  dbuser     172.30.3.73:56050  dbname  Query        42  Waiting for table metadata lock  UPDATE table_name SET ...

Lock을 유발 시킨 ID가 가장 오래된 1921446이라고 할 경우 KILL 명령어로 해당 프로세스를 종료 시킨다.

mysql> KILL 1921446