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

0. 개요

1. MySQL Online DDL 알고리즘: INSTANT와 INPLACE

INSTANT

ALTER TABLE 시작
 ➡ 메타데이터 변경 (Data Dictionary만 업데이트)
 ➡ 완료

INPLACE

Preparation 단계: ALTER TABLE 시작
 ➡ Shared Upgradable MDL 획득
 ➡ 테이블 정의 검증, 임시 구조 준비

Execution 단계:
 ➡ 인덱스 빌드 또는 데이터 재구성
 ➡ 그동안 유입된 DML은 메모리에 저장
 ➡ 데이터(테이블) 크기에 비례해 작업 시간 소요

Commit 단계:
 ➡ Exclusive MDL로 업그레이드
 ➡ 메타데이터 최종 반영
 ➡ Exclusive MDL 해제

INPLACE의 두 모드: 테이블 리빌드 vs 인덱스 빌드(리빌드 없음)

// storage/innobase/handler/handler0alter.cc (mysql-8.0.28)
...
/** Operations for creating secondary indexes (no rebuild needed) */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ONLINE_CREATE =
    Alter_inplace_info::ADD_INDEX | Alter_inplace_info::ADD_UNIQUE_INDEX |
    Alter_inplace_info::ADD_SPATIAL_INDEX;
...
/** Operations that InnoDB cares about and can perform without rebuild */
static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_NOREBUILD =
    INNOBASE_ONLINE_CREATE | INNOBASE_FOREIGN_OPERATIONS |
    Alter_inplace_info::DROP_INDEX | Alter_inplace_info::DROP_UNIQUE_INDEX |
    Alter_inplace_info::RENAME_INDEX | Alter_inplace_info::ALTER_COLUMN_NAME |
    Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH |
    Alter_inplace_info::ALTER_INDEX_COMMENT |
    Alter_inplace_info::ADD_VIRTUAL_COLUMN |
    Alter_inplace_info::DROP_VIRTUAL_COLUMN |
    Alter_inplace_info::ALTER_VIRTUAL_COLUMN_ORDER |
    Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;
...

임시 테이블 파일 생성
 ➡ 데이터 복사 & 구조 변경 (Execution 단계)
 ➡ 데이터 변경 병합 & 원본과 교체 (Commit 단계)

ALTER TABLE t ADD INDEX idx(col), ALGORITHM=INPLACE, LOCK=NONE;
-- Query OK, 0 rows affected (30.52 sec)
-- Records: 0  Duplicates: 0  Warnings: 0        ← 인덱스 빌드만

ALTER TABLE t MODIFY COLUMN c BIGINT, ALGORITHM=INPLACE, LOCK=NONE;
-- Query OK, 0 rows affected (45.12 sec)
-- Records: 10000000  Duplicates: 0  Warnings: 0 ← 리빌드 발생

하나의 ALTER 문에 여러 작업을 묶는다면?

ALTER TABLE t
  ADD COLUMN c INT,           -- INSTANT 가능
  DROP INDEX uk_old,          -- INPLACE 필요
  ADD UNIQUE INDEX uk_new();  -- INPLACE 필요

워크로드가 높은 테이블에 INSTANT로 가능한 작업이 있다면, 따로 분리해 테이블 리빌드를 피하는 편이 유리할 수 있습니다.

2. 메타데이터 잠금의 종류

3. INSTANT/INPLACE의 메타데이터 잠금 양상

INSTANT DDL의 MDL

• Long-running 트랜잭션: INSTANT가 아무리 빨라도 Exclusive 업그레이드 시점에 Shared MDL 보유자가 있으면 대기해야 합니다. 이때 심각한 blocking chain이 발생할 수 있습니다. Bug #106480에서 보고됐듯, 미완료 트랜잭션이 있는 상태에서 컬럼을 추가하면 그 트랜잭션이 끝날 때까지 blocking이 일어납니다. MySQL 개발자도 이를 expected behaviour 로 확인했고, 초기 공식 문서의 No exclusive metadata locks are taken 설명이 잘못됐음을 인정해 문서가 수정됐습니다.

• 파티션이 많은 테이블: Bug #83435 보고처럼 파티션 수가 많으면 Commit 단계의 내부 처리 비용이 커져 Exclusive MDL이 비정상적으로 길어질 수 있습니다. Bug #94610에서는 이 문제가 InnoDB의 dict_sys mutex를 장시간 점유하며, 대상과 무관한 다른 테이블의 DML까지 차단하는 현상으로 이어졌습니다.

INPLACE DDL의 MDL

• SU(Shared Upgradable) 획득 시도

• SR(SELECT)와 호환 → 읽기 허용

• SW(DML)와 호환 → 쓰기 허용

• 다른 SU/X와는 비호환 → 동시에 다른 DDL 불가

• 테이블 메타데이터 스냅샷, 새 인덱스/임시 테이블 구조 준비

• 이 단계에서 열린 장시간 트랜잭션이 있으면 SU 획득 자체가 대기 가능 (lock_wait_timeout 적용)

• SU를 유지한 채 장시간 작업 수행 (인덱스 빌드 또는 테이블 리빌드)

• DML/SELECT 전부 정상 진행 (SR/SW가 SU와 호환)

• 이 기간의 모든 DML은 online alter log에 기록됨 (row_log_online_op())

• SU → X(Exclusive)로 업그레이드 요청

• 기존 SR/SW 홀더가 전부 끝나야 X 획득 가능 — long-running 트랜잭션 하나가 전체 서비스를 막는 지점

• X 획득 후: ① online alter log에 쌓인 DML replay (row_log_apply() → InnoDB 내부 B-tree 연산으로 직접 적용) ② Data dictionary 업데이트(이때 dict_sys->mutex 점유) ③ Table definition cache 무효화(구 버전 제거)

• X 해제 → pending queue의 SR/SW가 일제히 grant됨 (thundering herd)

Online alter log의 Replay 구조

왜 모든 ALTER TABLE은 반드시 Exclusive MDL을 획득하는가?

4. Exclusive 메타데이터 잠금의 영향

모든 메타데이터 잠금 요청을 차단

// sql/mdl.cc (mysql-8.0.28)
...
bool MDL_lock::can_grant_lock(enum_mdl_type type_arg,
                              const MDL_context *requestor_ctx) const {
  bool can_grant = false;
  bitmap_t waiting_incompat_map = incompatible_waiting_types_bitmap()[type_arg];
  bitmap_t granted_incompat_map = incompatible_granted_types_bitmap()[type_arg];

  /*
    New lock request can be satisfied iff:
    - There are no incompatible types of satisfied requests
    in other contexts
    - There are no waiting requests which have higher priority
    than this request.
  */
  if (!(m_waiting.bitmap() & waiting_incompat_map)) {
    // ... granted queue 체크로 진입
    ...

다른 테이블 쿼리까지 차단할 수 있다

INPLACE 인덱스 생성의 특수성

• Execution 단계 자체가 짧음 — 새 인덱스 B-tree 빌드만 하므로 테이블 전체 데이터 복사가 없고, 데이터 scan을 병렬로 수행하며, 그만큼 쌓이는 online alter log 양이 적음

• Commit 단계의 Data dictionary 업데이트가 단순 — 인덱스 메타데이터 추가만 하면 됨 (테이블 리빌드는 파일 레벨 전환을 수반)

참고: PostgreSQL의 CREATE INDEX CONCURRENTLY와 비교

🔗 관련 글: PostgreSQL, Internal Concurrently Index Creation

5. INPLACE DDL vs pt-osc: 메타데이터 잠금 비교

pt-osc 각 단계의 MDL

pt-osc 장점 1: X lock 보유 시간의 예측 가능성

pt-osc 장점 2: 재시도 가능성

pt-online-schema-change \
  --alter "..." \
  --set-vars lock_wait_timeout=2 \
  --tries create_triggers:10:1,drop_triggers:10:1,swap_tables:10:1 \
  ...

6. 결론: 어떤 DDL 도구를 선택할 것인가?

모든 DDL을 pt-osc로 실행해야 할까?

• 속도: INSTANT가 수 ms~수 초, INPLACE가 분 단위로 끝낼 작업을 pt-osc는 테이블 크기에 비례해 시간 단위로 처리합니다. chunk 단위 INSERT … SELECT로 전체 테이블을 복사하므로 구조적으로 느립니다.

• 디스크 공간: 원본과 같은 크기의 shadow table이 작업 내내 유지되어, 테이블 크기만큼의 여유 공간이 추가로 필요합니다. 수백 GB 규모 테이블에선 이 조건 자체가 제약입니다.

• 트리거 오버헤드: 작업이 끝날 때까지 원본의 모든 DML이 트리거를 통해 shadow table로 동시에 쓰이므로 write latency가 평시보다 늘고, replication lag이 누적되는 원인이 되기도 합니다.

• Foreign key 처리의 까다로움: FK가 걸린 테이블은 -alter-foreign-keys-method 옵션으로 재구성 방식을 명시해야 하고, 방식마다 고유한 주의점이 있습니다.

• 중단 시 정리 부담: 비정상 종료되면 shadow table과 트리거가 남아 수동으로 정리해야 합니다.

INSTANT와 INPLACE를 고려해야 할 이유

모든 테이블이 다 무겁고 바쁘지는 않다