MySQL MDL(metadata_locks): Observability 확보하기

MySQL MDL(metadata_locks): insert 쿼리가 병목 상태에 빠지는 이유 글에서 이어지는 내용입니다.
이 글은 MySQL InnoDB 엔진 기준으로 작성됩니다.

이전 글에서 MySQL MDL(metadata_locks)로 인해서 의도치 않게 insert 쿼리가 병목 상태에 빠졌었는데요 🥲
이번 글에서는 재발 방지를 위해서 Observability를 추가하는 방법에 대해 기록해보려고 해요.

먼저, MDL에 대해서 정확히 이해해야 적절한 metric을 뽑아서 효율적으로 Observability를 확보할 수 있을 거예요.
그래서 Metadata Lock(MDL)에 대해서 좀 더 알아보려고 합니다.

 

MySQL Metadata Lock(MDL) 이해하기

MySQL은 데이터의 일관성을 유지하고 동시 접근을 안전하게 관리하기 위해 다양한 유형의 락을 사용해요.
그중 메타데이터 락(Metadata Lock, MDL)은 테이블이나 스키마 구조의 무결성을 보호하는 역할을 합니다.
MDL은 테이블을 읽거나 쓰는 경우, 테이블 구조를 변경할 때 자동으로 획득됩니다.

fyi; 위에서 이야기했듯이 MDL은 MySQL에서  테이블이나 스키마 구조의 무결성을 보호하기 위한 역할을 합니다.
이런 Lock 개념은 여러 요청이 동시에 들어올 수 있는 시스템(=동시 처리 시스템)에서 활용되는 경우가 많은데요,
동시 처리 시스템에서 무결성이 중요한 경우, 항상 경쟁 조건(=race condition)을 고려해야 하고 이 때 lock 개념을 활용하는 경우가 꽤 있습니다.

 

Metadata Lock 유형

MySQL InnoDB에서 Lock은 여러 유형으로 구성되며 계층적으로 나뉘어져 있습니다.
그리고 Metadata Lock에도 같은 메커니즘이 활용돼요.

일반적으로 Lock은 점유하는 개념이기 때문에 과도하게 생성되면 성능 병목이 발생할 수 있어요. 그래서 동시성이 높은 환경에서는 이를 적절하게 활용하는 것이 중요합니다. 

MySQL에서는 성능을 최대한으로 끌어내기 위해 MDL을 계층적으로 분류해서 활용해요.

Metadata Lock의 유형과 특징은 다음과 같습니다.

• MDL_SHARED(=Shared Lock)
  • Shared Lock은 기본적으로 여러 트랜잭션이 동시에 획득할 수 있고 동시성 확보가 가능해요. 
  • 주로 SELECT, INSERT 와 같이 테이블 구조를 변경하지 않고 데이터를 수정하는 MDL에 경우 Shared Lock을 획득하게 돼요.
  • 세부적으로는 각각 액션에 따라 MDL_SHARED_READ, MDL_SHARED_WRITE 등 으로 나뉘어져 있어요.

• MDL_EXCLUSIVE(=Exclusive Lock)
  • Exclusive Lock은 하나의 트랜잭션에만 획득 가능한 Lock이에요. 여러 트랜잭션에서 해당 Lock을 획득하려고 한다면, 단 하나의 트랜잭션만 Lock 획득에 성공하고 나머지는 Lock이 반환될 때까지 대기 상태에 빠져요.
  • 또한, Exclusive Lock은 테이블에 대해 어떠한 Lock도 걸려있지 않아야 획득 가능해요. 그래서 만약 Shared Lock을 오래 잡고 있는 트랜잭션이 있다면, 해당 트랜잭션이 종료될 때까지 Exclusive Lock을 획득하지 못하고 대기하게 돼요.
    • MySQL MDL(metadata_locks): insert 쿼리가 병목 상태에 빠지는 이유 에서 문제가 되었던 부분이 이 부분입니다.
  • ALTER TABLE과 같은 테이블 구조 변경하는 DDL 작업 시 획득하게 돼요.
• MDL_INTENTION(=Intention Lock)
  • Intention Lock은 잠금을 걸기 전에 먼저 의도(intent)를 표시하여 다른 트랜잭션과의 충돌을 방지하기 위해 InnoDB에서 사용하는 잠금 메커니즘이에요.
  • 트랜잭션 격리 및 다중 트랜잭션 동시 실행을 최적화하는 효과도 있어요.
  • InnoDB 엔진이 자동으로 관리하므로 사용자가 직접 제어할 일은 거의 없어요.

 

MDL의 유형과 병목이 될만한 부분을 살펴봤으니 이제 Observability 관련 내용을 알아볼게요.

Observability 확보하기

현재 요구사항으로는 metadata lock을 획득하는 PENDING 상태가 비정상적으로 길게 유지되는 경우를 감지해야 합니다.
이 목표를 가지고 Observability 확보를 위한 방법을 알아볼게요.

기본적으로 MySQL InnoDB 엔진에서 metadata lock 정보는 performance_schema.metadata_locks 테이블을 통해 확인할 수 있습니다.

metadata_locks 테이블의 대표 컬럼은 다음과 같습니다.
하지만 아래 테이블 컬럼을 보면 알 수 있듯이 제일 중요한 PENDING 시간을 알기 위해서는 실행 시점 시간이 필요한데, 해당 테이블만으로는 알 수 없습니다. 
그래서 우리는 performance_schema.threads 테이블을 추가로 join해서 활용해야해요.
(performance_schema.threads 테이블 컬럼은 자세하게 다루지 않습니다)

컬럼명	설명
OBJECT_TYPE	락이 걸린 OBJECT 유형 (TABLE, SCHEMA, FUNCTION 등)
OBJECT_SCHEMA	해당 OBJECT가 포함된 스키마(=테이블) 이름
OBJECT_NAME	대상 OBJECT 이름
LOCK_TYPE	획득한 락 유형 (SHARED, SHARED_READ, SHARED_WRITE, , EXCLUSIVE 등)
LOCK_STATUS	락의 상태 (PENDING, GRANTED, TIMEOUT, KILLED 등)
OWNER_THREAD_ID	metadata lock을 요청한 thread ID
OWNER_EVENT_ID	metadata lock을 요청한 이벤트 ID

 

결론적으로 아래 쿼리를 사용하면 PENDING 상태인 쓰레드를 확인할 수 있고
PROCESSLIST_TIME 컬럼을 통해서 과도하게 PENDING 상태가 지연되는 프로세스를 확인할 수 있습니다.
조금 고민되는 점은 PROCESSLIST_TIME은 여러 쿼리를 실행하는 프로세스의 경우 "PENDING이라는 상태가 잠깐 지속되는 동안에 데이터를 갭쳐해서 메트릭으로 남겼다면, 이 PENDING 상태가 오래 걸린 것처럼 보일 수 있다"는 인데요,
이 경우는 모니터링 툴(e.g. datadog) 을 통해서 메트릭을 적절하게 그룹핑하면 원하는 옵저버빌리티를 충족할 수 있을 것 같아요.

SELECT
    ml.OBJECT_TYPE,
    ml.OBJECT_SCHEMA,
    ml.OBJECT_NAME,
    ml.LOCK_TYPE,
    ml.LOCK_STATUS,
    t.THREAD_ID,
    t.PROCESSLIST_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.PROCESSLIST_DB,
    t.PROCESSLIST_TIME,
    t.PROCESSLIST_STATE,
    t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks ml
LEFT JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID = t.THREAD_ID
WHERE ml.LOCK_STATUS = 'PENDING'

 

다음 글은 실제로 이 쿼리로 옵저버빌리티를 확보해보고 경험이 어땠는지 공유해보면 좋을 것 같네요 ㅎㅎ
긴 글 읽어주셔서 감사합니다 :)

 

References

• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
• https://dev.mysql.com/doc/refman/8.4/en/performance-schema-metadata-locks-table.html