Read Original
#Backend

MySQL Online DDL의 메타데이터 잠금: pt-osc와 무엇이 다른가

MySQL Online DDL의 메타데이터 잠금: pt-osc와 무엇이 다른가
01

Summary

내 DB는 안전할까? MySQL Online DDL과 pt-osc 잠금(MDL) 메커니즘 완전 해부

대규모 서비스 장애를 유발하는 메타데이터 잠금의 실체와 최적의 스키마 마이그레이션 도구 선택법

MySQL 운영 중 누구나 한 번쯤 겪는 DDL 실행 시 쿼리 마비 현상의 원인인 메타데이터 잠금(MDL)을 깊이 있게 분석합니다. Native Online DDL의 두 가지 핵심 알고리즘과 Percona의 pt-osc 도구를 비교해 각 기술의 잠금 획득 시점과 한계를 파악하고 실무 관점의 안전한 마이그레이션 의사결정 기준을 정립해 봅니다.

  • 01INSTANT DDL도 긴 트랜잭션이 존재하면 Exclusive 업그레이드 대기 과정에서 심각한 블로킹 체인을 유발할 수 있음을 규명
  • 02INPLACE DDL 커밋 시점에 쌓인 online alter log를 리플레이하는 과정과 이로 인해 Exclusive MDL 점유가 늘어나는 작동 원리 분석
  • 03InnoDB 글로벌 뮤텍스(dict_sys->mutex) 점유 문제로 인해 DDL 대상이 아닌 무관한 테이블의 쿼리까지 연쇄 차단될 수 있는 원인 제시
  • 04pt-osc의 트리거 동기화 메커니즘을 통해 Exclusive MDL의 보유 시간을 예측 가능하게 최소화하는 구체적인 4단계 프로세스 설명
  • 05트랜잭션 스냅샷 기반으로 락 큐 대기 없이 안전하게 인덱스를 빌드하는 PostgreSQL의 CONCURRENTLY 방식과의 구조적 차이 비교

RECOMMENDATION

실시간 쓰기 작업이 쉴 새 없이 몰아치는 프로덕션 환경의 대용량 테이블에는 재시도 메커니즘이 탑재된 pt-osc를 적극 권장하며, 컬럼 추가나 디폴트 값 변경 등 메타데이터만 건드리는 가벼운 작업은 안전 구역을 확인한 후 Native INSTANT DDL을 활용하는 편이 리소스 절약에 유리합니다.

The Problem

MySQL Native Online DDL(INSTANT, INPLACE) 수행 시 메타데이터 잠금(MDL) 획득 대기 및 데이터베이스 글로벌 뮤텍스 점유로 인해 대규모 트래픽 환경에서 읽기 및 쓰기 쿼리가 차단되는 문제가 발생합니다.

The Solution

MySQL의 INSTANT 및 INPLACE 알고리즘의 MDL 획득 메커니즘과 동작 흐름을 상세히 분석하고, 트리거 기반 스키마 변경 도구인 pt-online-schema-change(pt-osc)의 단계별 잠금 특징과 상호 비교를 제공합니다.

The Result

pt-osc는 락 타임아웃 및 재시도 옵션을 통해 락 대기로 인한 서비스 장애를 막아주고 RENAME 시점에만 매우 짧게 Exclusive MDL을 보유하는 안정성을 보여주는 반면, Native Online DDL은 DML 유입량에 비례해 커밋 시점의 MDL 보유 시간이 길어질 수 있음을 입증했습니다.

Trade-off

pt-osc는 안정적인 MDL 관리가 가능한 대신, 전체 테이블 복사 프로세스로 인해 작업 속도가 매우 느리며 원본 데이터와 동일한 크기의 추가 디스크 공간이 필요하고 트리거 실행에 따른 쓰기 오버헤드와 복제 지연이 발생할 수 있습니다.

03

Key Concepts

Concept · 01

Metadata Lock (MDL)

데이터베이스 내 테이블의 스키마 정의를 보호하여 트랜잭션 도중 다른 세션에 의해 구조가 변경되지 않도록 정합성을 유지해 주는 동기화 잠금 메커니즘입니다.

  • DDL 반영을 위한 Exclusive MDL 요청이 대기 큐(Pending Queue)에 들어가면, 이후 유입되는 모든 읽기 및 쓰기 쿼리가 연쇄적으로 멈추게 됩니다.
Concept · 02

pt-online-schema-change (pt-osc)

원본 테이블 대신 임시 테이블을 생성하고 트리거를 활용해 변경 사항을 실시간 전파하며 백그라운드에서 안전하게 스키마를 이전하는 외부 마이그레이션 유틸리티입니다.

  • 마지막 최종 스왑 단계인 RENAME TABLE 시점에만 전역 Exclusive MDL을 짧게 요청하므로 잠금 대기 시간을 효율적으로 통제할 수 있습니다.
  • 잠금 대기 타임아웃과 재시도 횟수를 직접 파라미터로 세팅할 수 있어 DDL 적용의 운영 유연성을 높여줍니다.
Concept · 03

dict_sys->mutex

InnoDB 엔진 내부에서 데이터 딕셔너리 정보에 동시 참조할 때 정합성을 보호하기 위한 전역 범위의 상호 배제 잠금 장치입니다.

  • 특정 테이블의 DDL 작업 도중 딕셔너리 정보 업데이트가 오래 지연될 경우, 이 전역 뮤텍스가 잠겨 다른 무관한 테이블을 건드리는 쿼리들까지 전부 대기 상태에 빠질 수 있습니다.