Online DDL — 운영 중 스키마를 안전하게 변경하는 방법

운영 중인 테이블에 컬럼을 하나 추가해야 합니다. 데이터가 1억 건인데 — ALTER TABLE을 그냥 실행해도 될까요?

Online DDL이란

서비스 중단 없이 테이블 구조를 변경하는 MySQL의 기능 입니다.

MySQL 5.6부터 InnoDB에 도입되었고, 8.0에서 INSTANT 알고리즘이 추가되면서 더 강력해졌습니다. 예전에는 ALTER TABLE을 실행하면 테이블 전체에 락이 걸려서 읽기/쓰기가 모두 차단됐는데, 이제는 대부분의 DDL 작업을 서비스 중에 수행할 수 있습니다.

다만 "Online"이라는 이름이 붙어 있다고 해서 모든 DDL이 무중단은 아닙니다. 어떤 알고리즘으로 실행되느냐에 따라 서비스 영향이 완전히 달라집니다.

DDL 알고리즘 3가지

MySQL Online DDL은 변경 내용에 따라 세 가지 알고리즘 중 하나를 선택합니다. INSTANT → INPLACE → COPY 순서로 시도하고, 가장 가벼운 방식부터 사용합니다.

INSTANT

가장 빠른 방식입니다. ** 데이터 파일을 전혀 건드리지 않고, 데이터 딕셔너리의 메타데이터만 변경 **합니다.

SQL

-- 컬럼을 테이블 끝에 추가
ALTER TABLE orders ADD COLUMN memo VARCHAR(255) DEFAULT NULL, ALGORITHM=INSTANT;

-- 컬럼 기본값 변경
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending', ALGORITHM=INSTANT;

테이블이 1억 건이든 10억 건이든 상관없습니다. 메타데이터만 바꾸니까 ** 즉시 완료 **됩니다. 1초도 안 걸립니다.

INSTANT로 가능한 작업:

테이블 끝에 컬럼 추가 (MySQL 8.0.12+)
컬럼 기본값 변경/삭제
ENUM/SET에 값 추가 (끝에 추가하는 경우)
가상 생성 컬럼(Virtual Generated Column) 추가/삭제
컬럼 이름 변경 (MySQL 8.0.28+)

** 제한사항:**

컬럼을 ** 중간에** 추가하는 것은 MySQL 8.0.29+부터 INSTANT 가능 (그 이전은 INPLACE)
컬럼 삭제는 MySQL 8.0.29+부터 INSTANT 지원
ROW_FORMAT=COMPRESSED 테이블은 INSTANT 불가
풀텍스트 인덱스가 있는 테이블은 일부 제한

새 컬럼을 추가할 때 "테이블 끝에 추가"하면 INSTANT가 되고, "특정 컬럼 뒤에 추가"하면 INPLACE로 바뀔 수 있습니다. AFTER 절 하나 차이로 알고리즘이 달라지니 주의해야 합니다.

INPLACE

데이터를 ** 원본 테이블 위에서 재구성 **합니다. 새 테이블을 만들지 않고, 기존 테이블의 데이터를 직접 수정합니다.

SQL

-- 인덱스 추가
ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;

-- 컬럼 타입 변경 (VARCHAR 길이 늘리기)
ALTER TABLE orders MODIFY COLUMN memo VARCHAR(500), ALGORITHM=INPLACE, LOCK=NONE;

핵심은 LOCK=NONE입니다. DDL이 실행되는 동안에도 SELECT, INSERT, UPDATE, DELETE가 모두 가능 합니다.

동작 원리:

DDL 시작 시 짧은 ** 메타데이터 락(MDL)** 획득
변경 작업 수행 (인덱스 빌드, 데이터 재구성 등)
동시에 발생하는 DML 변경은 Row Log 에 기록
작업 완료 후 Row Log의 변경 사항을 반영
다시 짧은 메타데이터 락 → DDL 완료

Row Log가 중요합니다. DDL이 수행되는 동안 들어오는 INSERT, UPDATE, DELETE를 별도 로그에 기록해뒀다가, DDL 완료 직전에 한꺼번에 반영합니다. 그래서 동시 DML이 가능한 것입니다.

INPLACE로 가능한 주요 작업:

인덱스 추가/삭제
AUTO_INCREMENT 값 변경
VARCHAR 컬럼 길이 늘리기 (256바이트 경계를 넘지 않는 경우)
컬럼 NULL/NOT NULL 변경
테이블 최적화 (OPTIMIZE TABLE)

** 주의할 점:**

"INPLACE"라고 해서 디스크 공간을 안 쓰는 건 아닙니다. 인덱스를 새로 빌드하거나 테이블을 재구성할 때 ** 임시 공간이 필요 **합니다
Row Log 크기는 innodb_online_alter_log_max_size로 제한 (기본 128MB). DML이 너무 많으면 Row Log가 가득 차서 DDL이 실패할 수 있습니다
시작과 끝의 메타데이터 락 획득 시 ** 대기 중인 트랜잭션이 있으면 블로킹** 발생 가능

COPY

가장 오래된 방식이고, 가장 느립니다. 새 테이블을 만들고 → 데이터를 전부 복사하고 → 원본과 교체합니다.

SQL

-- 컬럼 타입을 INT → BIGINT로 변경 (COPY가 필요한 경우)
ALTER TABLE orders MODIFY COLUMN id BIGINT, ALGORITHM=COPY;

** 동작 과정:**

새로운 구조의 임시 테이블 생성
원본 테이블의 모든 행을 새 테이블로 복사
복사 중 DML은 차단 (LOCK=SHARED 이상)
복사 완료 후 테이블 교체 (RENAME)

1억 건 테이블이면 1억 건을 전부 복사합니다. ** 시간도 오래 걸리고, 디스크 공간도 2배 필요하고, I/O 부하도 큽니다 **. 게다가 복사하는 동안 쓰기가 차단되니 서비스에 직접적인 영향이 갑니다.

COPY가 필요한 경우:

컬럼 데이터 타입 변경 (INT → BIGINT, VARCHAR → TEXT 등)
문자셋 변경 (utf8 → utf8mb4)
스토리지 엔진 변경
일부 파티션 관련 변경

운영 중 COPY 알고리즘이 필요한 DDL을 해야 한다면, 뒤에서 설명할 pt-online-schema-change나 gh-ost를 사용하는 것이 훨씬 안전합니다.

알고리즘 비교

항목	INSTANT	INPLACE	COPY
속도	즉시 (밀리초)	데이터 크기에 비례	가장 느림
DML 동시 수행	O	O (LOCK=NONE)	X (쓰기 차단)
추가 디스크	불필요	일부 필요	테이블 크기만큼
I/O 부하	없음	보통	매우 높음
적용 범위	제한적	대부분의 DDL	모든 DDL
도입 버전	8.0.12	5.6	전통적 방식

정리하면: INSTANT가 가능하면 INSTANT, 아니면 INPLACE, 그것도 안 되면 COPY. MySQL이 자동으로 가장 가벼운 알고리즘을 선택하지만, 명시적으로 지정해서 확인하는 습관이 중요합니다.

어떤 알고리즘이 사용되는지 확인하는 방법

같은 ALTER TABLE이라도 ** 변경 내용에 따라 알고리즘이 다릅니다 **. "컬럼 추가"는 INSTANT인데 "컬럼 타입 변경"은 COPY일 수 있습니다. 그래서 실행 전에 반드시 확인해야 합니다.

방법 1: ALGORITHM 옵션 명시

SQL

-- INSTANT로 가능한지 확인
ALTER TABLE orders ADD COLUMN memo VARCHAR(255), ALGORITHM=INSTANT;
-- 성공하면 INSTANT로 실행된 것

-- 실패하면 INPLACE 시도
ALTER TABLE orders ADD COLUMN memo VARCHAR(255), ALGORITHM=INPLACE;

ALGORITHM=INSTANT를 지정했는데 INSTANT로 실행할 수 없으면 에러가 발생합니다. 이걸 이용해서 "이 ALTER가 어떤 알고리즘으로 되는지" 사전에 확인할 수 있습니다.

방법 2: MySQL 공식 매뉴얼 참조

MySQL 매뉴얼의 Online DDL Operations 페이지에 작업별 알고리즘, 테이블 재구성 여부, DML 허용 여부가 표로 정리되어 있습니다.

예를 들어:

작업	INSTANT	INPLACE	테이블 재구성	DML 허용
컬럼 끝에 추가	O	O	X	O
인덱스 추가	X	O	X	O
컬럼 타입 변경	X	X	O	X
문자셋 변경	X	X	O	X

방법 3: 스테이징에서 테스트

** 가장 확실한 방법 **입니다.

SQL

-- 스테이징 환경에서 실행
ALTER TABLE orders ADD COLUMN memo VARCHAR(255), ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0.01 sec)  ← INSTANT 성공

ALTER TABLE orders MODIFY COLUMN memo TEXT, ALGORITHM=INSTANT;
-- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.
-- → INPLACE 또는 COPY로 실행됨

운영 환경에서 실행하기 전에, 반드시 스테이징(또는 개발 DB)에서 동일한 구조의 테이블로 먼저 테스트하세요.

pt-online-schema-change

Online DDL이 있는데 왜 외부 도구가 필요할까요?

왜 필요한가

COPY 알고리즘이 필요한 변경을 ** 서비스 중단 없이** 해야 할 때
INPLACE DDL의 ** 메타데이터 락 대기 **가 부담될 때
DDL 진행률을 모니터링하고, ** 중간에 중단/재개 **하고 싶을 때
** 레플리카 지연 **을 모니터링하면서 속도를 조절하고 싶을 때

동작 원리

Percona Toolkit의 pt-online-schema-change는 다음 순서로 동작합니다.

PLAINTEXT

1. 새 구조의 빈 테이블 생성 (_orders_new)
2. 원본 테이블에 INSERT/UPDATE/DELETE 트리거 설치
3. 원본 데이터를 청크(chunk) 단위로 새 테이블에 복사
   (트리거가 동시 변경 사항을 새 테이블에도 반영)
4. 복사 완료 후 RENAME TABLE로 교체
   orders → _orders_old, _orders_new → orders
5. 트리거 삭제, 임시 테이블 삭제

트리거를 이용해서 "복사하는 동안 생긴 변경"을 놓치지 않는 것이 핵심입니다.

사용 예시

BASH

# 컬럼 추가
pt-online-schema-change \
  --alter "ADD COLUMN memo VARCHAR(255) DEFAULT NULL" \
  --execute \
  D=mydb,t=orders \
  --host=localhost \
  --user=admin \
  --ask-pass

# 컬럼 타입 변경 + 레플리카 지연 감시
pt-online-schema-change \
  --alter "MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT" \
  --execute \
  --max-lag=5 \              # 레플리카 지연이 5초 넘으면 일시 중지
  --chunk-size=1000 \        # 한 번에 복사할 행 수
  --check-interval=1 \       # 레플리카 지연 체크 간격(초)
  D=mydb,t=orders

장단점

** 장점:**

어떤 DDL이든 서비스 중단 없이 수행 가능
--max-lag 옵션으로 레플리카 지연을 모니터링하며 속도 조절
--dry-run으로 사전 테스트 가능
중간에 중단해도 원본 테이블은 안전

** 단점:**

트리거 기반이라 ** 쓰기 성능에 약간의 오버헤드** 발생
트리거가 이미 있는 테이블에는 사용 불가
외래 키가 있는 테이블은 추가 설정 필요 (--alter-foreign-keys-method)
원본 테이블 크기만큼의 디스크 공간 필요

gh-ost 간략 비교

GitHub에서 만든 gh-ost는 트리거 대신 ** 바이너리 로그(binlog)를 읽어서** 변경 사항을 추적합니다.

항목	pt-online-schema-change	gh-ost
변경 추적	트리거	binlog
쓰기 오버헤드	있음 (트리거)	적음
설정 복잡도	낮음	보통 (binlog 설정 필요)
중단/재개	가능	가능
외래 키	제한적 지원	미지원

트리거가 부담되는 쓰기 집약적 환경이라면 gh-ost가 더 나은 선택일 수 있습니다. 하지만 pt-online-schema-change가 더 오래되고 검증된 도구라서, 대부분의 상황에서는 pt-osc를 먼저 고려합니다.

실무 체크리스트

DDL을 운영 환경에서 실행하기 전에 꼭 확인해야 할 것들입니다.

1. 스테이징 테스트 필수

SQL

-- 스테이징에서 알고리즘 확인
ALTER TABLE orders ADD COLUMN memo VARCHAR(255), ALGORITHM=INSTANT;
-- 성공 여부와 소요 시간 확인

운영 DB와 동일한 구조(가능하면 비슷한 데이터량)에서 먼저 실행해보세요. 실행 시간, 알고리즘, 락 수준을 미리 파악할 수 있습니다.

2. 외래 키 확인

SQL

-- 이 테이블을 참조하는 외래 키가 있는지 확인
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'orders';

외래 키가 있으면 Online DDL이 COPY로 떨어질 수 있고, pt-osc도 추가 설정이 필요합니다.

3. 디스크 여유 공간 확인

SQL

-- 테이블 크기 확인
SELECT
  TABLE_NAME,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

INPLACE라도 인덱스 빌드 시 임시 공간이 필요하고, COPY나 pt-osc는 테이블 크기만큼 필요합니다. ** 최소 테이블 크기의 2배** 여유를 확보하세요.

4. 피크 타임 피하기

INSTANT가 아닌 이상, DDL은 I/O와 CPU를 소모합니다. 트래픽이 적은 시간대에 실행하는 것이 안전합니다. 특히 INPLACE DDL의 메타데이터 락은 ** 장시간 트랜잭션이 있으면 대기 **하게 되므로, 피크 타임에는 리스크가 커집니다.

5. 롤백 계획

INSTANT DDL: 취소가 간단합니다. 반대 DDL(DROP COLUMN 등)을 실행하면 됩니다
INPLACE DDL: 실행 중 취소(KILL)하면 롤백되지만, 이미 완료된 것은 반대 DDL 필요
pt-osc / gh-ost: 원본 테이블은 건드리지 않으므로, 교체(RENAME) 전까지는 안전합니다. 교체 후에도 _old 테이블이 남아 있어 복구 가능

SQL

-- pt-osc 실행 후 문제 발생 시 롤백
RENAME TABLE orders TO orders_failed, _orders_old TO orders;

6. 장시간 트랜잭션 확인

SQL

-- 오래 실행 중인 트랜잭션 확인
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
       trx_query
FROM INFORMATION_SCHEMA.INNODB_TRX
ORDER BY trx_started ASC;

INPLACE DDL의 시작과 끝에서 메타데이터 락을 잡는데, 이때 장시간 트랜잭션이 있으면 DDL이 대기합니다. DDL이 대기하면 그 뒤의 모든 쿼리도 함께 대기하면서 ** 연쇄 블로킹 **이 발생할 수 있습니다.

정리

Online DDL 은 MySQL 5.6+에서 제공하는 서비스 중단 없는 스키마 변경 기능
알고리즘은 INSTANT → INPLACE → COPY 순서로, 가벼운 것부터 시도됨
INSTANT: 메타데이터만 변경, 즉시 완료. 컬럼 추가 같은 단순 작업에 사용
INPLACE: 원본 테이블 위에서 작업, DML 동시 수행 가능. 인덱스 추가 등에 사용
COPY: 전체 복사. 느리고 부하 큼. 가능하면 피하고 pt-osc/gh-ost 사용
실행 전에 ** 어떤 알고리즘으로 실행되는지 반드시 확인** (ALGORITHM=INSTANT 시도)
대용량 테이블의 위험한 DDL은 pt-online-schema-change 나 gh-ost 로 안전하게 수행
스테이징 테스트, 디스크 확인, 피크 타임 회피, 롤백 계획은 항상 챙기기