[MySQL] Metadata Lock

회사에서 겪었던 이슈를 해결하면서 공부도 할 겸 포스팅해보았다.

포스팅 흐름을 간단하게 요약하면 다음과 같다.

• metadata lock이란?
• metadata lock이 왜 발생하는지
• 어떻게 해결했는지
  • 휴먼 에러
  • interacvtive_timeout
  • wait_timeout

 

 

 

Metadata lock이란?

회사에서 작업을 하다가 의도치 않은 Lock이 발생하였다.

현재 진행 중인 프로세스들의 상태를 확인해보자.

SHOW FULL PROCESSLIST;

현재 프로세스들

현재 프로세스 상태를 보면 다음과 같은 문장을 볼 수 있다.

Waiting for table metadata lock

자세한 정보를 확인해보니 개발환경에서 DDL을 날리면서 발생한 문제였다.

Lock이 걸린 프로세스를 kill 하면 그만이지만 원인을 알아야 의도하지 않은 Lock 발생 문제를 근본적으로 해결할 수 있다.

 

메타데이터 락은 데이터베이스 객체 (테이블, 뷰 등 메타데이터)의 이름이나 구조를 변경하는 경우에 획득하는 Lock이다.

 

 

 

Metadata lock이 왜 발생하는지

메타데이터 락은 보통 2가지 경우에 발생을 한다.

1. 말 그대로 현재 metadata가 이미 lock이 걸려있는 상태에서 추가 DDL 작업이 수행되지 못하고 있는 상태
2. 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용하는 경우

1번 같은 경우는

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.metadata_locks;

SHOW ENGINE innodb STATUS;

위와 같은 쿼리들을 통해 Lock 정보를 조회해봤는데 앞단에서 다른 metadata lock이 없음을 확인하였다.

그렇다면 2번이 제일 유력하다.

 

맨 위 사진을 자세히 보면 프로세스가 Sleep인 상태가 많다.

Sleep은 스레드의 상태인데 공식 문서에서 다음과 같이 표현하고 있다.

MySQL :: MySQL 8.0 Reference Manual :: 8.14.2 Thread Command Values

The thread is waiting for the client to send a new statement to it.

스레드의 새로운 상태를 클라이언트에게 보내기 위해 스레드가 대기하는 것을 말한다.

즉, Sleep이란 상태는 client - mysql 서버 간 연결 후 대기 중인 상태로써 다음 query 수행까지 계속 유지되고 있음을 뜻한다.

 

Sleep 상태가 생기는 이유는 트랜잭션이 진행중이거나 정상적인 종료를 하지 않았을 경우이다.

즉, 진행중인 트랜잭션이 있는지, 사용 후 언제나 close를 제대로 해주면 된다.

 

commit 되지 않은 트랜잭션이 존재한다면, MySQL InnoDB는 데이터를 읽어오기 위해 시간이 지날수록 느려지고 다른 쿼리 또한 lock으로 인해 대기될 수 있다.

 

현재 Sleep 상태로 대기하고 있는 애들 중에 트랜잭션이 아직 진행 중인지 확인을 위해 상태를 확인해보자.

SELECT * FROM information_schema.INNODB_TRX;

2개의 어플리케이션에서 트랜잭션이 진행 중이다..!

해당 스레드가 어떤 쿼리를 실행했는지만 파악한다면 원인을 쉽게 찾을 수 있을 것이다.

 

 

 

어떻게 해결했는가?

이유는 사소한 포인트에서 발생했다.

특정 쿼리에서 하나의 트랜잭션이 실행되었을 때 성공적으로 쿼리가 종료되면 commit을 수행하도록 했지만

실패 시 롤백해야 하는 여러 케이스가 있었는데 그중 하나에 rollback()을 따로 수행하지 않아서

요청이 끝나도 계속 트랜잭션이 RUNNING 상태였었다. 해당 부분을 수정해주고 나서 해결되었다.

 

하지만 끝이 아니다. 만약 내가 부재였던 상태에서 계속 Lock이 걸린 상태였다면 어땠을까?

Lock이 걸려서 대기 중일 때 커넥션을 끊을 수 있도록 한다면 조금의 지연시간이 있겠지만

다른 요청들을 수행할 수 있을 것이다. 그 방법 중 하나는 timeout을 설정하는 것이다.

 

 

 

interacvtive_timeout 와 wait_timeout

interacvtive_timeout과 wait_timeout 을 조절하여 sleep 상태에 빠진 스레드들을 종료할 수 있도록 하자.

두  timeout에 대한 설명은 다음과 같다.

 

[interacvtive_timeout]

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

 

The number of seconds the server waits for activity on an interactive connection before closing it.

 

• interactive 모드에서 커넥션을 맺은 후 커넥션을 종료하기 전에 서버가 쿼리 요청을 대기하는 시간
• interactive 모드 : $ mysql> 과 같은 프롬프트 있는 콘솔이나 터미널 모드

 

[wait_timeout]

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

 

The number of seconds the server waits for activity on a noninteractive connection before closing it.

 

• interactive 모드가 아닌 경우에 해당
• mysqld 혹은 mysql client에서 커넥션을 맺은 후 커넥션을 종료하기 전에 서버가 쿼리 요청을 대기하는 시간
• mysql client : PHP 나 C, PERL, python 등등의 API를 이용한 client 프로그램

 

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client

 

• 스레드 시작 시, session wait_timeout 값은 클라이언트 유형에 따라 글로벌 wait_timeout 값 혹은 interactive_timeout 값으로 초기화가 된다.

 

해당 쿼리로 현재 환경의 타임아웃 설정을 조회할 수 있다.

SHOW GLOBAL variables LIKE '%timeout%'

현재 환경에서는 두 설정 값이 28,800초로 되어있는데 이 말은 즉슨 스레드가 소멸될 때까지 모든 클라이언트 스레드 버퍼는 기본적으로 MySQL에서 8시간 동안 메모리에 저장된다.

 

요청이 많은 서비스에서 timeout 값을 낮춰 sleep 상태로 커넥션만 유지하고 있는 클라이언트들의 접속을 빠르게 끊어준다면 성능을 향상할 수 있을 것이다.

 

RDS라면 파라미터 그룹으로 조정이 가능하다.

글로벌 변수를 수정하는 경우 MySQL 서버를 재부팅해야 하는 점 참고하자.

 

 

 

---

ref

• MySQL 공식 사이트
• 리눅스 채널