MySQL Online DDL 무중단 전략

글쓴이 /

MySQL Online DDL이란?

Online DDL은 테이블 구조를 변경하면서도 읽기와 쓰기를 동시에 허용하는 MySQL의 스키마 변경 메커니즘입니다. MySQL 8.0에서 대폭 개선되어 대부분의 ALTER TABLE이 ALGORITHM=INPLACE 또는 INSTANT로 실행 가능해졌습니다. 수억 건 테이블에서도 다운타임 없이 스키마를 변경할 수 있습니다.

DDL 알고리즘 3가지

알고리즘 동작 방식 잠금 속도
INSTANT 메타데이터만 수정 메타데이터 락만 (ms) 즉시
INPLACE InnoDB 내부에서 변환 시작/끝 메타데이터 락 테이블 크기 비례
COPY 새 테이블 복사 전체 테이블 잠금 가능 가장 느림

INSTANT DDL: 즉시 실행 가능한 작업

MySQL 8.0.12+에서 테이블 크기와 무관하게 즉시 실행되는 작업들입니다.

-- 컬럼 추가 (마지막 위치, 8.0.12+)
ALTER TABLE orders ADD COLUMN notes TEXT, ALGORITHM=INSTANT;

-- 컬럼 추가 (임의 위치, 8.0.29+)
ALTER TABLE orders ADD COLUMN priority INT DEFAULT 0 AFTER status, ALGORITHM=INSTANT;

-- 컬럼 삭제 (8.0.29+)
ALTER TABLE orders DROP COLUMN legacy_field, ALGORITHM=INSTANT;

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

-- ENUM/SET 값 추가 (끝에 추가하는 경우)
ALTER TABLE orders MODIFY COLUMN status 
  ENUM('draft','pending','confirmed','shipped','cancelled'), ALGORITHM=INSTANT;

-- 테이블 이름 변경
RENAME TABLE orders TO customer_orders;

-- INSTANT 불가능한 경우 자동 폴백 방지
ALTER TABLE orders ADD COLUMN data JSON, ALGORITHM=INSTANT;
-- ERROR: ALGORITHM=INSTANT is not supported. → INPLACE 필요

INPLACE DDL: 온라인 실행 가능한 작업

-- 인덱스 추가 (읽기+쓰기 허용)
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 인덱스 삭제
ALTER TABLE orders DROP INDEX idx_old_index,
  ALGORITHM=INPLACE, LOCK=NONE;

-- 컬럼 타입 변경 (일부만 INPLACE 가능)
-- VARCHAR 길이 증가 (255 이하 → 이하, 또는 이상 → 이상)
ALTER TABLE orders MODIFY COLUMN name VARCHAR(200),
  ALGORITHM=INPLACE, LOCK=NONE;

-- FULLTEXT 인덱스 추가
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 외래키 추가
ALTER TABLE order_items ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id),
  ALGORITHM=INPLACE, LOCK=NONE;

-- AUTO_INCREMENT 값 변경
ALTER TABLE orders AUTO_INCREMENT = 1000000,
  ALGORITHM=INPLACE, LOCK=NONE;

위험한 DDL: COPY가 필요한 작업

다음 작업은 테이블 전체를 복사하며, 대형 테이블에서는 수 시간이 걸릴 수 있습니다.

-- ⚠️ 컬럼 타입 변경 (INT → BIGINT)
ALTER TABLE orders MODIFY COLUMN id BIGINT;
-- → 전체 테이블 재구축 필요

-- ⚠️ 문자셋 변경
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4;

-- ⚠️ 컬럼 순서 변경 (MySQL 8.0.28 이전)
ALTER TABLE orders MODIFY COLUMN email VARCHAR(255) FIRST;

-- ⚠️ PRIMARY KEY 변경
ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, tenant_id);

-- ⚠️ ROW_FORMAT 변경
ALTER TABLE orders ROW_FORMAT=COMPRESSED;

pt-online-schema-change: 대형 테이블 안전 변경

COPY 알고리즘이 필요한 작업은 Percona pt-online-schema-change를 사용합니다.

# 설치
apt-get install percona-toolkit

# INT → BIGINT 변환 (10억 행 테이블)
pt-online-schema-change 
  --alter "MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" 
  --host=127.0.0.1 
  --port=3306 
  --user=admin 
  --ask-pass 
  --chunk-size=1000 
  --max-lag=1s 
  --check-interval=1 
  --max-load="Threads_running=50" 
  --critical-load="Threads_running=200" 
  --set-vars="lock_wait_timeout=5" 
  --progress=time,30 
  --execute 
  D=mydb,t=orders

# 동작 원리:
# 1. _orders_new 임시 테이블 생성 (새 스키마)
# 2. orders에 TRIGGER 생성 (INSERT/UPDATE/DELETE를 _orders_new에도 반영)
# 3. 청크 단위로 데이터 복사 (--chunk-size)
# 4. 복제 지연 모니터링 (--max-lag)
# 5. 서버 부하 모니터링 (--max-load)
# 6. 완료 후 RENAME TABLE로 원자적 교체

# 복합 변경
pt-online-schema-change 
  --alter "ADD COLUMN tenant_id INT NOT NULL DEFAULT 1, ADD INDEX idx_tenant (tenant_id)" 
  --chunk-size=2000 
  --max-lag=2s 
  --execute 
  D=mydb,t=orders

gh-ost: GitHub의 Online Schema Migration

gh-ost는 트리거 대신 바이너리 로그(binlog)를 이용해 스키마를 변경합니다. 트리거의 성능 영향이 우려될 때 사용합니다.

# gh-ost 실행
gh-ost 
  --host=127.0.0.1 
  --port=3306 
  --user=admin 
  --password=secret 
  --database=mydb 
  --table=orders 
  --alter="MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" 
  --chunk-size=1000 
  --max-lag-millis=1500 
  --throttle-query="SELECT IF(COUNT(*)>100, 1, 0) FROM information_schema.processlist WHERE command='Query'" 
  --cut-over=default 
  --exact-rowcount 
  --concurrent-rowcount 
  --ok-to-drop-table 
  --initially-drop-ghost-table 
  --panic-flag-file=/tmp/gh-ost.panic 
  --execute

# gh-ost vs pt-osc 비교:
# gh-ost: binlog 기반, 트리거 없음, 일시 정지/재개 가능
# pt-osc: 트리거 기반, 더 넓은 호환성, 외래키 지원

# 실행 중 제어
echo "throttle" > /tmp/gh-ost.sock  # 일시 정지
echo "no-throttle" > /tmp/gh-ost.sock  # 재개
touch /tmp/gh-ost.panic  # 긴급 중단

안전한 DDL 실행 체크리스트

-- 1. DDL 알고리즘 사전 확인 (DRY RUN)
ALTER TABLE orders ADD COLUMN test INT, ALGORITHM=INSTANT;
-- ERROR이면 INSTANT 불가 → INPLACE 또는 pt-osc 필요

-- 2. 테이블 크기 확인
SELECT 
  table_name,
  table_rows,
  ROUND(data_length / 1024 / 1024) AS data_mb,
  ROUND(index_length / 1024 / 1024) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'orders';

-- 3. 실행 중인 DDL 모니터링
SELECT * FROM performance_schema.events_stages_current
WHERE event_name LIKE '%alter%';

-- 4. 메타데이터 락 대기 확인
SELECT * FROM performance_schema.metadata_locks
WHERE object_schema = 'mydb' AND object_name = 'orders';

-- 5. 장시간 트랜잭션 확인 (MDL 대기 원인)
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;

-- 6. lock_wait_timeout 설정 (DDL 대기 제한)
SET SESSION lock_wait_timeout = 5;
-- 5초 내 메타데이터 락 획득 못하면 실패 → 재시도

메타데이터 락(MDL) 함정

Online DDL에서 가장 흔한 장애 원인은 메타데이터 락 대기입니다.

-- 시나리오: INPLACE DDL이 "Waiting for table metadata lock"에서 멈춤
-- 원인: 장시간 트랜잭션이 테이블을 참조 중

-- 1. 원인 트랜잭션 찾기
SELECT p.id, p.user, p.host, p.time, p.state, p.info,
       t.trx_started, t.trx_state
FROM information_schema.processlist p
JOIN information_schema.innodb_trx t ON p.id = t.trx_mysql_thread_id
WHERE t.trx_started < NOW() - INTERVAL 30 SECOND
ORDER BY t.trx_started;

-- 2. 문제: DDL이 MDL을 기다리면, 이후 모든 SELECT도 대기
-- SELECT → DDL(MDL 대기) → 기존 트랜잭션
-- → 연쇄 대기로 전체 테이블 접근 차단!

-- 해결: DDL 전에 장시간 트랜잭션 정리
-- 또는 lock_wait_timeout을 짧게 설정하고 재시도
SET SESSION lock_wait_timeout = 3;
ALTER TABLE orders ADD INDEX idx_new (column), ALGORITHM=INPLACE, LOCK=NONE;
-- 실패 시 잠시 후 재시도

프로덕션 DDL 운영 전략

  1. INSTANT 우선: 가능하면 ALGORITHM=INSTANT 사용. 불가능 시 INPLACE → pt-osc/gh-ost 순서
  2. 피크 시간 회피: 트래픽이 낮은 시간대에 실행
  3. lock_wait_timeout 설정: DDL 전 3~5초로 설정하여 무한 대기 방지
  4. 장시간 트랜잭션 정리: DDL 전 30초 이상 실행 중인 트랜잭션 확인
  5. 레플리카 먼저: Read Replica에서 DDL 실행 후 문제 없으면 Primary에 적용
  6. 롤백 계획: pt-osc는 원본 테이블을 _old 접미사로 보존. 문제 시 RENAME으로 복구
  7. 모니터링: Threads_running, Seconds_behind_master, lock wait 실시간 확인

MySQL Online DDL은 운영 중 스키마 변경의 핵심 기술입니다. INSTANT → INPLACE → pt-osc/gh-ost 순서로 접근하면 대부분의 변경을 무중단으로 처리할 수 있습니다. MySQL 인덱스 최적화는 MySQL 인덱스 최적화 가이드를, 파티셔닝 전략은 MySQL Partitioning 글을 참고하세요.

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux