왜 스키마 변경이 문제가 되는가
운영 중인 MySQL에서 ALTER TABLE은 단순해 보이지만, 대용량 테이블에서는 서비스 장애의 직접 원인이 된다. 예전 방식(COPY 알고리즘)은 전체 테이블을 복사하면서 메타데이터 락(MDL)을 잡아 읽기·쓰기를 모두 막았다. 수천만 행 테이블이라면 이 락이 수십 분간 유지되고, 그동안 커넥션 풀이 포화되어 애플리케이션 전체가 멈춘다.
해결책은 두 갈래다. MySQL이 자체 제공하는 Online DDL과, 별도 툴인 pt-online-schema-change(pt-osc). 둘 다 "무중단"을 표방하지만 동작 원리와 실패 지점이 완전히 다르다.
Online DDL: 서버 내장 방식
MySQL 5.6+의 InnoDB Online DDL은 ALGORITHM과 LOCK 옵션으로 동작을 제어한다. 핵심은 INPLACE 알고리즘이 테이블 복사 없이 인덱스만 재구성하고, 그동안 발생한 DML을 온라인 로그에 모았다가 마지막에 적용한다는 점이다.
-- INPLACE + 동시 DML 허용, 락이 필요하면 즉시 실패시켜 사고 방지
ALTER TABLE orders
ADD COLUMN memo VARCHAR(255) NULL,
ALGORITHM=INPLACE, LOCK=NONE;
-- INPLACE 불가능한 작업이면 에러로 알려줌 (묵시적 COPY 방지)
-- ERROR 1846: ALGORITHM=INPLACE is not supported. Reason: ...
주의할 점은 모든 변경이 INPLACE로 되지 않는다는 것이다. 컬럼 타입 변경, PRIMARY KEY 추가 등은 COPY로 떨어진다. 따라서 반드시 ALGORITHM=INPLACE, LOCK=NONE을 명시해 서버가 묵시적으로 COPY 알고리즘을 쓰는 것을 막아야 한다. 또한 8.0의 INSTANT 알고리즘은 컬럼 추가를 메타데이터만 바꿔 초 단위로 끝내지만, 제약 조건이 많아 사용 전 검증이 필수다.
pt-osc: 트리거 기반 복사 방식
pt-osc는 원본 테이블 구조를 복제한 새 테이블(_orders_new)을 만들고, 원본에 INSERT/UPDATE/DELETE 트리거를 걸어 변경분을 새 테이블에 흘려보낸다. 동시에 기존 데이터를 작은 청크로 나눠 복사한 뒤, 마지막에 원자적으로 테이블 이름을 교체한다.
pt-online-schema-change \
--alter "ADD COLUMN memo VARCHAR(255) NULL" \
--max-load "Threads_running=50" \
--critical-load "Threads_running=100" \
--chunk-size=1000 \
--max-lag=2 \
--recursion-method=hosts \
--execute \
D=shop,t=orders,h=localhost,u=admin--max-load로 부하가 높으면 복사를 자동으로 멈추고, --max-lag로 복제 지연을 감시한다. 이 서버 부하 인지 능력이 pt-osc의 가장 큰 강점이다. 반면 트리거를 사용하므로 이미 트리거가 걸린 테이블에는 쓸 수 없고, 외래 키가 있으면 별도 처리(--alter-foreign-keys-method)가 필요하다.
두 방식의 비교
| 항목 | Online DDL | pt-osc |
|---|---|---|
| 추가 디스크 | 대부분 불필요(INPLACE) | 원본 크기만큼 필요 |
| 부하 조절 | 불가(시작하면 못 멈춤) | 가능(max-load/lag 감시) |
| 복제 지연 | 제어 어려움 | 청크 단위로 완화 |
| 외래 키/트리거 | 영향 없음 | 제약 많음 |
| 중단/롤백 | 어려움 | 중단 시 새 테이블만 삭제 |
어떻게 선택할 것인가
실무 기준은 명확하다. 테이블이 작거나(수백만 행 이하) INPLACE로 처리 가능한 변경이라면 Online DDL이 빠르고 안전하다. 추가 디스크도, 외부 툴도 필요 없다.
반대로 수천만~수억 행의 대형 테이블이거나, 복제 지연을 반드시 통제해야 하는 환경이라면 pt-osc가 낫다. 특히 변경 도중 부하가 튀어도 자동으로 속도를 늦추므로 운영 안정성이 높다. 단, 원본 크기만큼의 여유 디스크와 충분한 실행 시간을 확보해야 한다.
공통 주의점
- 디스크 여유 확인: pt-osc는 테이블 2배, INPLACE도 임시 정렬 공간이 필요하다. 실행 전 남은 용량을 반드시 점검한다.
- 긴 트랜잭션 확인: 두 방식 모두 마지막 교체·적용 단계에서 짧은 락이 필요하다. 장시간 열린 트랜잭션이 있으면 이 락이 대기하며 전체가 지연된다.
- 먼저 리허설: 스테이징에서 동일 데이터 규모로
--dry-run(pt-osc) 또는 실제 실행 시간을 측정한 뒤 프로덕션에 적용한다. - 트래픽 낮은 시간대: 무중단이라도 리소스를 소모하므로, 가능하면 피크 타임을 피한다.
정리하면, 무중단 변경에 만능은 없다. 테이블 규모·변경 종류·복제 구성·디스크 여유를 함께 보고, 두 방식의 실패 지점을 이해한 상태로 선택하는 것이 핵심이다.