Online DDL과 스키마 변경: instant/inplace/copy, gh-ost, pt-osc

스키마 변경은 SQL 한 줄이 아니라 운영 이벤트다

ALTER TABLE users ADD COLUMN ... 같은 문장은 보기에는 간단하다. 하지만 운영 DB에서 이 한 줄은 metadata lock, 테이블 재구성, 임시 디스크 사용량, replication lag, 애플리케이션 호환성까지 한꺼번에 건드린다. 그래서 DBA나 데이터 플랫폼 엔지니어에게 스키마 변경은 개발 요청을 그대로 실행하는 작업이 아니라, 서비스가 계속 쓰는 테이블의 모양을 안전하게 바꾸는 배포 절차다.

MySQL 8.4의 InnoDB online DDL은 많은 변경을 INSTANT나 INPLACE로 처리해 읽기와 쓰기 중단을 줄여 준다. 하지만 online이라는 단어를 무중단으로 번역하면 위험하다. 온라인 DDL도 시작과 종료 시점에 metadata lock을 잡고, 일부 작업은 테이블을 rebuild하며, 큰 테이블에서는 임시 공간과 복제 지연을 만들 수 있다. MySQL 공식 문서도 in-place online DDL이 끝나기 전에 metadata lock을 가진 트랜잭션을 기다려야 하며, 큰 rebuild 작업은 pause나 throttling 메커니즘이 없고 replication lag을 만들 수 있다고 설명한다.

이번 장의 목표는 ALTER TABLE 알고리즘 이름을 외우는 것이 아니다. 변경 요청이 들어왔을 때 다음 질문에 답하는 운영 판단법을 만드는 것이다.

• 이 변경은 metadata만 바꾸는가, 테이블을 다시 쓰는가?
• LOCK=NONE으로 실제 쓰기를 허용할 수 있는가?
• native online DDL로 충분한가, 아니면 gh-ost나 pt-online-schema-change가 필요한가?
• cut-over 순간에 어떤 세션이 막힐 수 있는가?
• 실패하면 원본 테이블과 애플리케이션은 어떤 상태로 남는가?

한 장으로 보는 MySQL 스키마 변경 선택지

그림에서 핵심은 INSTANT, INPLACE, COPY가 단순히 빠름과 느림의 순서가 아니라는 점이다. 같은 INPLACE라도 secondary index 추가처럼 concurrent DML이 가능한 작업이 있고, primary key 변경처럼 테이블을 크게 재구성하는 작업이 있다. 외부 도구는 긴 copy 구간을 더 통제 가능하게 만들지만, 마지막 table rename 또는 cut-over에는 여전히 짧은 metadata lock이 필요하다.

ALGORITHM=INSTANT: 빠르지만 만능은 아니다

ALGORITHM=INSTANT는 가능한 경우 테이블 데이터나 인덱스 데이터를 다시 쓰지 않고 data dictionary의 metadata 중심으로 변경을 반영한다. 운영자 입장에서는 가장 선호되는 경로다. 대형 테이블에서 몇 시간 걸릴 수 있는 rebuild를 피하고, buffer pool과 디스크에 주는 충격도 작다.

하지만 INSTANT를 쓸 수 있다는 사실과 항상 안전하다는 말은 다르다.

• MySQL은 instant 작업에도 metadata lock을 잡는다.
• instant가 가능한 column 변경 범위는 버전과 테이블 상태에 따라 제한된다.
• 일부 instant add/drop column은 테이블의 row version 정보를 증가시킬 수 있고, 한계에 닿으면 rebuild가 필요해질 수 있다.
• 애플리케이션 호환성 문제는 DDL 알고리즘과 별개다. 새 column을 넣는 순서, 코드 배포 순서, rollback 시 nullable/default 정책을 같이 봐야 한다.

따라서 운영에서는 INSTANT를 기대하더라도 SQL에 명시적으로 알고리즘을 적어 두는 편이 안전하다.

ALTER TABLE orders
  ADD COLUMN risk_score INT NULL,
  ALGORITHM=INSTANT;

이렇게 쓰면 MySQL이 조용히 더 무거운 알고리즘으로 fallback하는 일을 막을 수 있다. 실행이 실패하면 운영자는 왜 instant가 안 되는지를 확인하고, 더 무거운 변경 절차로 승격할지 결정한다.

ALGORITHM=INPLACE: 온라인이어도 rebuild와 MDL을 피하지 못할 수 있다

INPLACE는 이름 때문에 데이터를 전혀 복사하지 않는다고 오해하기 쉽다. 실제로는 MySQL이 table-copy 방식보다 덜 disruptive한 경로를 쓰는 것이지, 모든 작업이 metadata-only라는 뜻은 아니다. MySQL 공식 online DDL 표에서도 primary key 추가는 INPLACE가 가능하지만 table rebuild가 필요하다고 분류한다.

운영 판단은 다음처럼 나누면 좋다.

LOCK clause는 이 판단을 명시화하는 도구다.

ALTER TABLE orders
  ADD INDEX idx_orders_created_status (created_at, status),
  ALGORITHM=INPLACE,
  LOCK=NONE;

LOCK=NONE은 읽기와 쓰기를 허용해야 한다는 요구사항이다. 만약 해당 변경이 그 수준의 동시성을 지원하지 않으면 statement가 실패한다. 이것은 장애가 아니라 안전장치다. 반대로 LOCK을 생략하면 MySQL이 가능한 가장 낮은 잠금을 선택하긴 하지만, 운영자가 기대한 수준이 명확히 드러나지 않는다.

Metadata lock: 짧게 잡아도 기다리는 줄은 길어질 수 있다

스키마 변경 사고에서 가장 흔한 패턴은 online DDL인데 왜 SELECT가 막혔나다. 이유는 metadata lock이다.

MySQL online DDL은 대략 세 단계로 진행된다.

1. 초기화: 현재 table definition을 보호하기 위해 shared upgradeable metadata lock을 잡고, 가능한 알고리즘과 동시성 수준을 결정한다.
2. 실행: index build나 table rebuild 같은 실제 작업을 수행한다. 작업 종류에 따라 잠금 수준이 달라진다.
3. commit table definition: 새 table definition을 반영하기 위해 exclusive metadata lock이 필요하다. 이 lock 자체는 보통 짧지만, 앞선 transaction이 table metadata lock을 들고 있으면 기다린다.

문제는 exclusive metadata lock 요청이 대기열에 들어간 뒤다. 오래 열린 transaction 하나가 DDL을 막고, 그 뒤에 들어오는 새 SELECT나 DML도 pending DDL 뒤에서 같이 막힐 수 있다. 그래서 online DDL 전에는 ALTER 문장만 보는 것이 아니라 target table을 잡고 있는 오래된 세션을 봐야 한다.

기본 점검 쿼리 예시는 다음과 같다.

SHOW FULL PROCESSLIST;

SELECT
    p.ID,
    p.USER,
    p.HOST,
    p.DB,
    p.TIME,
    p.STATE,
    p.INFO
FROM information_schema.PROCESSLIST AS p
WHERE p.STATE LIKE '%metadata lock%'
   OR p.INFO LIKE 'ALTER TABLE%';

Performance Schema를 켜 둔 환경이라면 performance_schema.metadata_locks와 thread 정보를 조인해 holder와 waiter를 더 정확히 볼 수 있다. 운영 runbook에는 누가 lock을 잡고 있는지 확인한다, kill 기준은 무엇인지, DDL을 중단할지 기다릴지가 있어야 한다.

Native Online DDL의 한계: throttle과 pause가 없다

MySQL native online DDL의 장점은 단순함이다. 별도 도구를 설치하지 않아도 되고, MySQL이 지원하는 범위 안에서는 문법도 명확하다. 하지만 큰 테이블에서는 다음 한계가 운영상 크게 드러난다.

• rebuild 작업에는 table 크기와 index 크기에 비례하는 임시 공간이 필요할 수 있다.
• concurrent DML이 많은 동안 online alter log가 커질 수 있고, 한계를 넘으면 실패할 수 있다.
• MySQL 자체 online DDL에는 긴 작업을 중간에 pause하거나 load에 맞춰 throttle하는 일반 메커니즘이 없다.
• source에서 DDL이 오래 걸리면 replica에서도 DDL 적용이 오래 걸리고, 그 사이 뒤따르는 DML 적용이 밀릴 수 있다.
• 실패 시 rollback 비용이 클 수 있다.

그래서 native DDL은 다음 조건에서 우선 고려한다.

1. INSTANT로 확실히 가능한 변경이다.
2. INPLACE LOCK=NONE이 지원되고, 예상 실행 시간이 짧거나 운영 부하가 낮다.
3. table 크기, write rate, replica lag 예산이 충분히 작다.
4. 실패해도 rollback과 재시도가 서비스에 큰 영향을 주지 않는다.

반대로 수십 GB 이상 대형 테이블, 쓰기 많은 핵심 테이블, strict한 replication lag 예산, 변경 시간이 길게 예측되는 작업은 외부 online schema change 도구를 검토한다.

gh-ost: binlog를 읽어 ghost table을 따라잡게 하는 방식

gh-ost는 GitHub이 만든 MySQL online schema migration 도구다. 공식 README는 gh-ost를 triggerless online schema migration solution이라고 설명한다. 기본 아이디어는 다음과 같다.

1. 원본 테이블과 비슷한 ghost table을 만든다.
2. ghost table에 원하는 schema 변경을 적용한다.
3. 원본 데이터를 chunk 단위로 ghost table에 복사한다.
4. 원본 테이블에 들어오는 변경은 trigger가 아니라 binlog stream을 읽어 ghost table에 반영한다.
5. 준비가 끝나면 cut-over에서 ghost table과 원본 테이블을 바꾼다.

gh-ost의 운영상 장점은 제어 가능성이다. migration을 pause하거나 throttle할 수 있고, socket이나 TCP로 상태를 확인하고 동적으로 제어할 수 있다. GitHub 문서의 throttling 설명에 따르면, gh-ost가 throttle 상태가 되면 row copy와 binlog event 처리를 멈춰 source에 주는 쓰기 부하를 거의 제거한다. trigger 기반 도구는 원본 테이블의 사용자 DML이 발생할 때마다 trigger가 계속 실행되므로 같은 방식의 완전한 pause가 어렵다.

하지만 gh-ost도 마법은 아니다.

• binlog와 replication topology 이해가 필요하다.
• foreign key가 얽힌 테이블에는 제약이 있다.
• 마지막 cut-over에는 metadata lock이 필요하다.
• tool user 권한, row-based event, replica lag 관측, 실패 시 cleanup 절차가 필요하다.
• 운영팀이 socket control, postpone cut-over, panic flag 같은 도구 동작을 숙지해야 한다.

운영에서는 보통 --execute 전에 --test-on-replica나 no-op 성격의 검증으로 대상 테이블, 권한, lag 관측, 예상 row count를 확인한다. 핵심 테이블에서는 cut-over를 자동으로 바로 진행하지 않고 operator가 확인한 뒤 풀어 주는 방식이 더 안전하다.

pt-online-schema-change: trigger 기반이지만 성숙한 안전장치가 많다

pt-online-schema-change는 Percona Toolkit의 대표적인 online schema change 도구다. 문서 설명처럼 원본 테이블을 잠그지 않고 변경하기 위해 새 테이블을 만들고, 데이터를 chunk 단위로 복사하고, 원본 테이블의 INSERT, UPDATE, DELETE를 trigger로 새 테이블에 반영한 뒤 atomic RENAME TABLE로 교체한다.

기본 흐름은 gh-ost와 비슷하지만 변경 추적 방식이 다르다.

Percona 문서는 이 도구를 쓰기 전에 문서를 읽고, non-production에서 테스트하고, production 백업을 확인하라고 강하게 말한다. 또한 실제 변경에는 --execute가 필요하고, 대부분의 경우 PRIMARY KEY나 UNIQUE INDEX가 필요하며, replica lag과 server load 기준에 따라 pause하거나 abort하는 안전장치를 제공한다.

pt-osc는 다음 상황에서 여전히 유용하다.

• 조직이 이미 Percona Toolkit 운영 경험과 runbook을 갖고 있다.
• 대상 테이블과 topology가 도구 제약에 잘 맞는다.
• trigger overhead를 감당할 수 있고, 기존 trigger/foreign key 문제가 정리되어 있다.
• gh-ost의 binlog 기반 운영 요건을 만족시키기 어렵다.

중요한 것은 어느 도구가 항상 우월하다고 외우는 것이 아니다. native DDL, gh-ost, pt-osc 각각의 failure mode가 다르므로 테이블 특성과 조직의 운영 숙련도에 맞춰 고르는 것이다.

운영 절차: DDL을 배포처럼 다루기

안전한 스키마 변경은 실행 명령보다 앞단과 뒷단이 더 중요하다.

1. 변경 분류

먼저 요청을 분류한다.

• column add/drop/rename인가?
• index add/drop인가?
• type, charset, collation 변경인가?
• primary key나 foreign key를 건드리는가?
• 애플리케이션 코드가 새 schema와 구 schema를 동시에 견딜 수 있는가?

이 단계에서 EXPLAIN만큼 중요한 것은 SHOW CREATE TABLE이다. 현재 primary key, secondary index, foreign key, trigger, partition, row format을 확인해야 한다.

SHOW CREATE TABLE orders\G
SHOW TABLE STATUS LIKE 'orders'\G
SELECT COUNT(*) FROM orders;

2. 알고리즘 강제와 dry run

가능하면 native DDL은 알고리즘과 lock 수준을 명시한다.

ALTER TABLE orders
  ADD INDEX idx_orders_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

외부 도구는 dry run 또는 replica test를 먼저 수행한다. 실제 실행 옵션과 dry run 옵션의 차이를 runbook에 남기고, production에서는 임의로 옵션을 바꾸지 않는다.

3. 실행 전 guardrail

실행 직전에는 다음을 본다.

4. 실행 중 관측

DDL 실행 중에는 성공 여부만 기다리지 않는다.

• Threads_running, CPU, disk I/O, row lock wait
• metadata lock waiter
• replica lag과 applier 오류
• ghost/copy table 증가량
• tool heartbeat, throttle 상태, cut-over 대기 여부

5. cut-over와 사후 검증

가장 위험한 순간은 끝이다. long transaction이 있으면 cut-over가 기다리고, pending exclusive MDL이 뒤의 쿼리를 막을 수 있다. cut-over 전에는 불필요한 긴 SELECT나 batch를 멈추고, 실패 시 tool cleanup 방법을 알고 있어야 한다.

사후 검증은 최소한 다음을 포함한다.

SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
SELECT COUNT(*) FROM orders;

count만으로 데이터 동등성을 증명할 수는 없지만, 빠른 sanity check로는 유용하다. 중요한 테이블은 checksum, row sampling, 애플리케이션 오류율, slow query 변화, replica lag 정상화까지 확인한다.

실무 판단 기준

마지막으로 선택 기준을 간단히 정리하면 다음과 같다.

운영자가 피해야 할 가장 위험한 문장은 온라인 DDL이니까 괜찮다다. 더 안전한 문장은 이 변경은 어떤 알고리즘으로 실행되고, 어떤 lock을 요구하며, 실패하면 어디서 멈추는가다. 스키마 변경은 결국 데이터베이스 배포다. 배포처럼 사전 검증, 관측, rollback, 사후 확인이 있어야 한다.

References

• MySQL 8.4 Reference Manual, "InnoDB and Online DDL": https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl.html
• MySQL 8.4 Reference Manual, "Online DDL Operations": https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html
• MySQL 8.4 Reference Manual, "Online DDL Performance and Concurrency": https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-performance.html
• MySQL 8.4 Reference Manual, "Online DDL Space Requirements": https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-space-requirements.html
• MySQL 8.4 Reference Manual, "Online DDL Limitations": https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-limitations.html
• MySQL 8.4 Reference Manual, "Metadata Locking": https://dev.mysql.com/doc/refman/8.4/en/metadata-locking.html
• GitHub gh-ost README: https://github.com/github/gh-ost
• GitHub gh-ost throttling documentation: https://github.com/github/gh-ost/blob/master/doc/throttle.md
• Percona Toolkit documentation, pt-online-schema-change: https://docs.percona.com/percona-toolkit/pt-online-schema-change.html