Metadata Locking in MySQL Online DDL: Compared with pt-osc

0. 개요

MySQL Native Online DDL 은 Inplace/Instant 알고리즘을 지원합니다. 몇몇 명령은 즉시 완료되지만 어떤 것들은 아주 오래 수행되며 문제를 일으키기도 하는데요.

문제의 대부분은 메타데이터 잠금(MDL)이 쓰기 혹은 읽기 서비스 쿼리들을 차단(Blocking)하는 현상입니다.

이와 관련하여 저는 오래된 기술 (궁금) 부채(?)가 있었습니다.

Inplace/Instant Online DDL vs pt-online-schema-change : 어떤 것이 MDL 리스크가 더 적은가?

이 글은 위 의문을 해소하고, 워크로드가 아주 높은 테이블 구조를 변경할 때 어떤 도구를 선택해야 할 지 정리해보고자 합니다.

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

MySQL 8.0 ≤기준, Online DDL은 세 가지 알고리즘을 제공합니다.

INSTANT, INPLACE, COPY.

이 중 서비스 영향을 최소화하기 위해 주로 사용하는 INSTANT와 INPLACE의 실행 흐름을 살펴보겠습니다.

INSTANT

테이블 데이터에 접근하지 않고 메타데이터만 변경하는 방식입니다. 칼럼 추가 등 일부 작업에서만 사용할 수 있습니다.

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

테이블 크기와 무관하게 수 밀리초 이내에 완료되며, 실질적으로 서비스에 미치는 영향이 아주 적습니다. (🤔과연? )

INPLACE

테이들 데이터를 별도 작업 공간에서 직접 변경하되, 진행 중에도 DML을 허용하는 방식입니다. 인덱스 추가/삭제, 칼럼 타입 변경 등 대부분 DDL에서 사용됩니다.

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

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

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

핵심은 시작과 끝 두 시점에서 잠금 전환이 발생한다는 점 입니다. 특히 마지막 Commit 단계에서 Exclusive MDL 업그레이드 시 심각한 차단 현상이 발생할 수도 있습니다.

INPLACE 의 모드 : 테이블 리빌드 / 인덱스 빌드(테이블 No리빌드)

같은 INPLACE라도 내부 동작은 크게 두 가지로 나뉩니다. 아래 코드에서 이를 명확히 구분하고 있습니다.

// 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;
...

테이블 No리빌드(INNOBASE_ONLINE_CREATE, INNOBASE_ALTER_NOREBUILD)에 속한 인덱스 삭제, 이름 변경 등 작업은 테이블 리빌드없이 메타데이터만 수정합니다. 인덱스 생성도 포함됩니다.

테이블 리빌드가 필요한 작업은 내부적으로

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

과정을 거칩니다.

위 “원본 테이블과 구조 교체” 작업은 InnoDB 엔진 레벨의 File Operation 이며, pt-osc 에서 사용하는 RENAME TABLE 과 다른 메커니즘입니다.

“인덱스 빌드 vs 테이블 리빌드” 의 차이는 DDL 실행 결과로 확인할 수 있습니다.

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 ← 리빌드 발생

물론 어떤 모드던 Commit 단계에서 Exclusive MDL 이 발생합니다. 차이는 이 단계에서 소비하는 리소스와 시간입니다.

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

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

하나의 ALTER 안에 여러 operation이 섞이면 가장 제한적인 알고리즘을 선택합니다.

위 예는 칼럼 추가가 INSTANT 진행이 가능하더라도 인덱스 변경이 INPLACE를 요구하므로, 전체가 INPLACE로 수행되어 INSTANT의 이점을 잃게 됩니다.

따라서 테이블 워크로드가 높은 데 INSTANT 진행 가능한 작업이 있다면, 따로 분리하여 테이블 리빌드를 회피하는 것이 유리할 수 있습니다.

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

MySQL의 MDL은 다양한 유형이 있으며, DDL 작업에서 중요한 것들은 다음과 같습니다.

잠금 유형들 간 호환성이 중요합니다. X(Exclusive) 요청이 큐에 있으면 새로운 SR이나 SW이 Grant되지 않습니다. 즉 모든 읽기/쓰기 쿼리 차단이 발생합니다.

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

INSTANT DDL 의 MDL

테이블 리빌드없이 매우 빠르게 실행되고, 데이터 변경 사항(DML)을 병합하지 않기 때문에 Commit 단계의 Exclusive MDL 이 아주 짧습니다. Shared Upgradable 에서 Exclusive 로의 업그레이드 요청도 매우 빠르게 이루어집니다.

그러나 서비스에 영향을 미칠 수 있는 시나리오가 존재합니다.

• Long-running 트랜잭션 : INSTANT가 아무리 빨라도 Exclusive 업그레이드 시점에 Shared MDL 보유자가 있으면 대기해야 합니다. 이 때 심각한 Blocking-chain 이 발생할 수 있습니다. Bug #106480 에서 보고된 것처럼, 미완료 트랜잭션이 있는 상태에서 로 칼럼을 추가하면 해당 트랜잭션이 끝날 때까지 블로킹이 일어납니다. MySQL 개발자도 이를 “expected behaviour"로 확인했으며, 초기 공식 문서의 "No exclusive metadata locks are taken" 설명은 잘못된 것이었다고 인정하여 문서가 수정되었습니다.
• Partition 이 많은 테이블 : Bug #83435 리포팅과 같이 파티션 수가 많은 테이블의 Commit 단계에서 더 높은 내부 처리 비용이 발생합니다. Exclusive MDL 이 비정상적으로 길어질 수 있습니다. Bug #94610 에서는 이 문제가 InnoDB의 dict_sys mutex를 장시간 점유하면서 해당 테이블과 무관한 다른 테이블의 DML까지 차단하는 현상으로 이어졌습니다.

이러한 사례들을 감안하면, INSTANT DDL 도 프로덕션 환경에서 주의를 기울일 필요가 있습니다.

INPLACE DDL의 MDL

Preparation 단계:

• SU(Shared Upgradable) 획득 시도
• SR(SELECT)와 호환 → 읽기 허용
• SW(DML)와 호환 → 쓰기 허용
• 다른 SU/X와는 비호환 → 동시에 다른 DDL은 불가
• 테이블 메타데이터 스냅샷 및 새 인덱스/임시 테이블 구조 준비
• 이 단계에서 열린 장시간 트랜잭션이 있으면 SU 획득 자체가 대기할 수 있음 (lock_wait_timeout 적용)

Execution 단계:

• SU 유지한 채 장시간 작업 수행 (인덱스 빌드 또는 테이블 리빌드)
• DML/SELECT 전부 정상 진행 (SR/SW가 SU와 호환)
• 이 기간 동안 발생하는 모든 DML은 online alter log에 기록됨 (row_log_online_op())

Commit 단계:

• SU → X(Exclusive) 로 업그레이드 요청
• 기존 SR/SW 홀더가 전부 끝나야 X 획득 가능 — long-running 트랜잭션 하나가 전체 서비스를 막는 지점
• X 획득 후:

1. Online alter log에 쌓인 DML Replay (row_log_apply() → InnoDB 내부 B-tree 연산으로 직접 적용)
2. Data dictionary 업데이트 (이때 dict_sys->mutex 점유)
3. Table definition cache 무효화(구 버전 제거)

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

여기서 주의할 점은 Online alter log의 Replay가 X lock 보유 중에 이루어진다는 것입니다. 공식 문서의 innodb_online_alter_log_max_size 설명에서도 "extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log"라고 명시하고 있습니다.

Online alter log 의 Replay 구조

InnoDB는 SQL 문장이 아닌 변경된 row의 물리적 데이터(operation type, index key value, row data)를 Alter log 에 기록합니다.

이 Log replay는 SQL문을 재실행하는 방식이 아닙니다. Commit 단계에서 row_log(_table)_apply 가 각 entry의 operation type에 따라 InnoDB 내부의 B-tree 연산 함수(row_ins_clust_index_entry 등)를 직접 호출하여 INSERT/UPDATE/DELETE를 적용합니다. SQL 파서, 옵티마이저, 핸들러 인터페이스를 전혀 거치지 않는 엔진 내부 연산이므로 개별 entry 처리 속도가 향상됩니다.

물론 일반 SQL 보다는 빠르지만, Execution 단계동안 DML이 많이 발생할수록 Alter log가 커지고, 그만큼 X lock 보유 시간이 길어집니다. “Commit 단계는 수 밀리초”라는 설명은 DML이 적은 경우에만 성립하며, 워크로드가 높은 테이블에서는 Log replay 시간만큼 X lock이 유지되어 서비스 영향이 커집니다

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

MySQL은 Data dictionary와 Table definition cache에서 테이블 구조 상태가 다름(다중 버전)을 허용하지 않습니다.

DDL이 완료되면 기존 테이블 정의를 무효화하고 새 정의로 교체해야 하는데, 이 작업 중에 다른 세션이 구 테이블 정의를 참조하고 있으면 불일치가 발생합니다. 이를 방지하기 위해 Cache를 무효화하는 순간에 반드시 Exclusive MDL로 다른 세션의 접근을 차단해야 합니다.

CREATE INDEX 문도 내부적으로 ALTER TABLE 문으로 매핑되어 동일한 단계를 따릅니다. 즉 Secondary Index 추가 작업도 Preparation → Execution → Commit(Exclusive MDL) 단계를 거치게 됩니다.

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

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

위 “2. 메타데이터 잠금의 종류” 에 언급했듯이, 모든 SHARED MDL 뿐만 아니라 또다른 Exclusive MDL 요청을 차단합니다.

MySQL 은 MDL 요청에 대해 Granted queue 와 Pending queue 를 관리합니다. Exclusive MDL을 요청하면 Pending queue 에 입장하고 이때부터 블로킹 체인이 시작됩니다(끔찍).

아래 MDL Queue 규칙이 존재하기 때문입니다.

Pending queue에 X가 있으면 새로운 SR/SW 등 모두 Grant 하지 않는다.

MDL 유형별 호환 규칙 때문이기도 하고, Grant 코드에도 명시되어 있습니다.

// 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 체크로 진입
    ...

핵심은 위 명시된 “There are no waiting requests which have higher priority than this request” 조건입니다. m_waiting.bitmap은 현재 pending queue에 있는 lock type들의 bitmap이고, waiting_incompat_map은 요청한 type과 비호환인 pending type들의 bitmap입니다.

그런데 X는 SR/SW의 비호환 대상으로 포함되어 있어 pending X 하나만으로도 이후 들어오는 모든 SR/SW가 이 if 조건에서 막혀 granted queue 체크 단계로 내려가지도 못합니다.

아래는 예시 시나리오입니다.

(1) DDL이 X lock 요청 → Pending queue 진입

• A,B,C,D 는 이미 Granted이므로 계속 실행 가능
• DDL은 위 4개가 끝나기를 대기

(2) 새 SELECT(E), INSERT(F) 도착

• 새 요청들이 모두 DDL 뒤로 Pending 에 갇힘

(3) 새 쿼리 계속 도착, Pending 증가

• A,C,D 가 종료되고 B 만 남은 상태
• 새 요청들이 대규모로 Pending 누적

(4) DDL이 X Grant 후 실행 완료

• B 종료 후 드디어 DDL 이 X 획득 후 실행 후 X 해제한 상태
• 이제야 후속 요청들이 Granted 되어 실행 가능 상태가 됨

위 모습처럼 Exclusive MDL 은 Pending queue 에 입장하여 기다릴 뿐임에도 수많은 세션의 후속 MDL 요청을 차단합니다.

다른 테이블 쿼리까지 차단 가능

한 가지 더 주의할 점이 있습니다. Online DDL Commit 단계에서 DDL 대상 테이블이 아닌 다른 테이블 쿼리까지 대기하는 현상이 나타날 때가 있습니다.

MDL은 테이블 단위이므로 테이블 A에 X lock이 걸려도 테이블 B의 MDL에는 영향이 없어야 하는데, 왜 이런 일이 발생할까요?

이는 MDL이 아닌 InnoDB 내부의 dict_sys Mutex 때문입니다. Data dictionary를 업데이트할 때 InnoDB는 dict_sys->mutex를 잡는데, 이 Mutex는 테이블 단위가 아니라 글로벌 범위입니다. 이 Mutex가 점유된 동안 다른 세션이 새 테이블을 열거나, 임시 테이블을 생성하거나, Dictionary에 접근하는 모든 작업이 대기합니다.

위 Mutex 대기 때문에 대용량 & 대규모 환경의 테이블 작업 시 SHOW PROCESSLIST 명령조차 응답이 지연되는 일을 겪을 수 있습니다.

일반적으로는 이 Mutex 보유 시간이 매우 짧아서 체감되지 않지만, Online alter log replay가 길어지거나 파티션 테이블에서 각 파티션에 대한 Dictionary 작업이 반복되면 Mutex 점유가 비정상적으로 길어질 수 있습니다.

즉, DDL 대상 테이블만 영향을 받는다는 것은 MDL 관점에서만 맞는 말이고, InnoDB 내부에서는 dict_sys Mutex를 통해 서버 전체에 영향을 줄 수 있다는 점을 인지해야 합니다. (최근 버전에서는 이 글로벌 병목 문제가 해결되었을까요? 🤔)

INPLACE 인덱스 생성의 특수성

위에서 INPLACE 는 인덱스 빌드 vs 테이블 리빌드의 두 가지 모드를 설명하였었습니다.

Secondary 인덱스 생성은 테이블을 재구성하는 INPLACE와 비교하여 상대적으로 Commit 단계가 가볍습니다.

• Execution 단계 자체가 짧음
• 새 인덱스 B-tree 빌드만 하므로 테이블 전체 데이터 복사가 없음
• 데이터 Scan 을 병렬로 수행함
• 따라서 쌓이는 Online alter log 양이 적음
• Commit 단계의 Data dictionary 업데이트가 단순
• 인덱스 메타데이터 추가만 진행
• 테이블 리빌드는 파일 레벨 전환을 수반

하지만 테이블 재구성과 비교하여 가벼울 뿐 Commit 단계가 안전하다는 뜻은 아닙니다. Exclusive MDL를 요청하고 차단을 일으키는 양상은 동일합니다.

참고: PostgreSQL의 Concurrently 인덱스 생성과 비교

[embed]PostgreSQL, Internal Concurrently Index Creation Concurrently 인덱스 생성 중에는 세 번의 트랜잭션이 시작되고 커밋되며, 두 번째와 세 번째 트랜잭션에서 테이블을 스캔이 발생합니다. 두 번째 트랜잭션에서는 하위 병렬 프로세스들이 실행됩니다. 각…medium.com

MySQL은 메타데이터 잠금 큐에서 강력한 잠금 모드(Exclusive MDL) 를 존중하여 새 SR/SW(읽기/쓰기)를 모두 차단하는 구조입니다.

PostgreSQL의 CREATE INDEX CONCURRENTLY는 MySQL과 근본적으로 다른 접근을 취합니다.

Phase 1: 카탈로그 등록

• ShareUpdateExclusiveLock 획득 (DML 전부 허용, 다른 DDL만 차단)
• 카탈로그에 인덱스 등록, indisvalid = false
• 커밋 → lock 해제
• WaitForLockers: 이 시점 기준 스냅샷에 있던 트랜잭션만 대기 (새 트랜잭션은 차단 없음)

Phase 2: 첫 번째 스캔 — 인덱스 빌드

• ShareUpdateExclusiveLock 재획득 (DML 여전히 허용)
• 테이블 스캔하며 인덱스 빌드
• 커밋 → lock 해제
• WaitForLockers: Phase 2 시작 시점 스냅샷만 대기

Phase 3: 두 번째 스캔 — 변경분 반영

• ShareUpdateExclusiveLock 재획득
• Phase 2 중 발생한 DML을 인덱스에 반영, 검증 후 indisvalid = true
• 커밋 → 인덱스 사용 가능
• 전 단계에 걸쳐 SELECT/DML 한 번도 차단되지 않음

핵심은 WaitForLockers의 동작 방식입니다.

1. “이 시점 이전에 시작된 활성 트랜잭션” 목록을 스냅샷으로 찍는다
2. 그 목록의 트랜잭션들이 끝나길 기다린다
3. 대기 중에 새로 시작되는 트랜잭션은 완전히 무관하다 → 차단 없음
4. 목록이 비면 다음 phase로 진행합니다

트랜잭션 스냅샷(Virtual trx id)를 기반으로 대기하는 구조이기 때문에 Lock queue 에 영향을 주지 않고, 새 트랜잭션의 Lock 획득을 방해하지도 않습니다.

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

pt-osc 각 단계의 MDL

pt-online-schema-change는 원본 테이블의 빈 복사본을 만들고, 트리거로 변경분을 동기화하면서 데이터를 옮긴 뒤, 테이블을 스왑하는 방식입니다. 이 과정에서 MDL이 필요한 시점은 크게 세 곳입니다.

1단계 : CREATE TRIGGER (x 3회)

• SNW(Shared No Write) 획득 시도
• SELECT(SR)와 호환 → 읽기 허용
• DML(SW)와 비호환 → 쓰기 차단
• 기존 SW 홀더가 끝나길 대기 (lock_wait_timeout 내에 획득 실패 시 트리거 생성 실패로 전체 작업 롤백)
• 트리거 생성 완료 후 SNW 해제, 이후 일반 DML은 다시 진행 가능 . 단, 이 시점부터 원본 DML이 트리거를 통해 신규 테이블로 전파됨

2단계: 테이블 복사

• INSERT-SELECT 문으로 Chunk 단위 테이블 복사 진행
• 원본 테이블에 SELECT … [LOCK IN SHARE MODE] 로 SR 획득
• 신규(작업) 테이블에 INSERT INTO … 로 SW 획득
• 청크 단위로 SR/SW가 잡혔다 풀렸다 반복되므로 장시간 보유되는 MDL은 없음
• 동시에 사용자 DML도 원본에 SW로 진입 가능하고, 트리거 실행으로 신규 테이블에도 SW를 동반 획득 (SW끼리 호환이므로 pt-osc의 복사 SW와 공존)

3단계: RENAME TABLE (테이블 스왑)

• RENAME TABLE t TO _t_old, _new_t TO t 을 원자적 단일 구문으로 실행
• 두 테이블 모두에 Exclusive(X) MDL 획득
• SELECT(SR)/DML(SW)/다른 DDL 모두 차단 — 이 시점이 pt-osc의 유일한 전역 블로킹 구간
• 보유 시간은 매우 짧지만(메타데이터 교체만 수행), 기존 장시간 트랜잭션이 원본의 SR/SW를 들고 있으면 X가 pending queue에 들어가 앞서 설명한 블로킹 체인을 유발
• 스왑 완료 즉시 X 해제

4단계: DROP TRIGGER (× 3회)

• 이제 _t_old가 된 구 원본 테이블에 대해 트리거 삭제
• DROP TRIGGER도 X MDL을 잠깐 획득
• 여기서 차단되는 대상은 이미 rename되어 사용자 접근 경로에서 사라진 _t_old뿐이므로 서비스 영향은 사실상 없음

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

pt-osc 또한 시작과 끝 시점에 MDL 을 획득하고 특히 끝단계에 Exclusive MDL 을요구합니다.

큰 장점은 Exclusive MDL 보유 시간이 워크로드와 무관하게 일정하다는 점입니다.

트리거가 DML을 실시간으로 새 테이블에 반영하기 때문에 마지막 단계에 “밀린 데이터 변경분" 이라는 개념이 없습니다. RENAME 시점에만 짧은 Exclusive MDL을 요청합니다.

INPLACE DDL은 테이블에 대한 평소 DML 트래픽, 파티션 테이블 여부에 따라 Exclusive MDL 보유 시간이 길어지고 다른 테이블 액세스에 영향을 미치기도 합니다.

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

INPLACE DDL은 Commit 단계에서 Exclusive MDL 획득에 실패할 가능성이 있고 (물론 Preparation 단계에서도 Lock timeout 가능합니다), 실패하면 지금까지 진행한 테이블 재구성 작업이 수포로 돌아갑니다.

반면 pt-osc는 MDL 획득 실패 시의 운영적 유연성에서 유리한 면이 있습니다.

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

위 예시와 같이 Lock timeout 을 2초로 설정하고 MDL을 요구하는 각 작업의 재시도 횟수/간격을 지정할 수 있습니다!

또한 트리거를 생성하는 단계는 SR(SELECT)을 차단하지 않는 SNW MDL을 획득하기 때문에, 시작 단계 영향도가 상대적으로 더 적습니다.

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

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

pt-osc가 MDL 영향 면에서 리스크가 적은 것은 분명합니다. Exclusive MDL 보유 시간이 RENAME 한 번의 수 ms로 고정되고, 이 시간이 DML 워크로드와 무관하게 예측 가능하다는 점은 큰 장점입니다. 하지만 그 대가가 가볍지 않습니다.

pt-osc’s cons :

• 속도: INSTANT DDL이 수 ms~수 초, INPLACE DDL이 분 단위로 끝나는 작업을 pt-osc는 테이블 크기에 비례해 시간 단위로 처리합니다. 청크 단위로 INSERT-SELECT를 반복하며 전체 테이블을 복사하므로 구조적으로 느릴 수밖에 없습니다.
• 디스크 공간: 원본과 같은 크기의 shadow table이 작업 내내 유지되므로 해당 테이블 크기만큼의 여유 공간이 추가로 필요합니다. 수백 GB 규모 테이블에서는 이 조건 자체가 제약이 됩니다.
• 트리거 오버헤드: 작업이 끝날 때까지 원본 테이블의 모든 DML이 트리거를 통해 shadow table로 동시에 쓰여지므로, 쓰기 지연시간(write latency)이 평시보다 늘어납니다. 복제 지연(replication lag)이 누적되는 원인이 되기도 합니다.
• Foreign key 처리의 까다로움: FK가 걸린 테이블은 --alter-foreign-keys-method 옵션으로 재구성 방식을 명시해야 하며, 각 방식마다 고유한 주의점이 있습니다.
• 중단 시 정리 부담: 작업이 비정상 종료되면 shadow table과 트리거가 남아 수동으로 정리해야 합니다.

하지만 정말 중요한 서비스의, 단 한순간도 트랜잭션이 끊기지 않는 바쁜 테이블이라면… 저는 pt-osc 실행을 준비할 것입니다.

INSTANT와 INPLACE를 고려해야 할 이유

INSTANT DDL은 칼럼 추가/삭제처럼 실무에서 가장 빈번한 구조 변경을 메타데이터 수정만으로 거의 즉시 처리합니다.

앞서 살펴본 long transaction과 many-partitions 케이스만 의식하면서 트래픽이 낮은 시간대를 선택하면, pt-osc의 비용을 치르지 않고도 충분히 안전하게 적용할 수 있는 전략적 선택을 할 수 있습니다.

INPLACE DDL도 마찬가지이며, 테이블을 재구성하지 않는 즉시 완료 가능한 명령들(예: 인덱스 삭제, DEFAUL 값 변경 등)도 존재합니다. 특히 일반적인 Secondary 인덱스 생성은 pt-osc와 큰 차이를 보일 정도로 속도가 빠릅니다.

즉 초당 수백/수천 건의 액세스가 발생하는 무거운 워크로드 테이블이 아니라면?

비즈니스 서비스 피크 시점을 피하여 INPLACE와 INSTANT를 먼저 고려해보는 것이 어떨까 하는 개인적인 의견입니다.

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

현대의 데이터베이스 시스템은 대부분 마이크로서비스로 잘게 쪼개져 있고, 캐시와 이벤트 큐 등 데이터베이스 부하를 상당히 덜어내는 아키텍처로 구성되어 있습니다.

정말 주의해야 할 무거운 워크로드 포인트는 시스템 전체에서 일부분이고, 모든 데이터베이스의 모든 테이블이 대용량이고 대규모 액세스하지는 않는다는 뜻입니다.

중요한 것은 프로덕션 데이터베이스의 부하를 잘 관측하는 것입니다.

테이블 레벨 통계를 잘 수집하고 모니터링하여,

어떤 테이블이 DDL 방식 선택에 있어 주의해야 할 “뜨거운” 테이블인지, 혹은 INSTANT/INPLACE로 충분히 처리 가능한지

데이터로 판단할 수 있는 가시성 기반을 갖추는 것이 중요합니다.