배경

스키마 변경도 무중단으로 해야 합니다

우테코 팀 프로젝트 중 위와 같은 요구사항이 주어졌다.

"가능할까?"

지금까지 스키마 변경으로 인한 서비스 장애를 여러 번 경험했기에 처음에는 의구심이 들었다.
스키마 변경에는 테이블 락이 필요하고, 그 동안 정상적인 데이터 접근이 어려워진다는 것을 알고 있었기 때문이다.
하지만 정말 불가능한 것일까? 이 요구사항을 만족할 수 있는 방법이 없을까?

방법 탐색

임시 테이블을 만들어 스키마 변경을 진행하고 완료 후 테이블 이름을 스왑하여 (거의)무중단으로 변경하는 방법을 찾았다.

MySQL에서 테이블 스키마를 “무중단”으로 변경해보자!!

설명이 매우 친절하고 사용된 코드가 첨부되어 있으며 프로시저를 통해 재사용성까지 챙겼기에 그대로 따라해도 될 것 같았다. 하지만 걸리는 부분이 있었다.

1. 공식 문서가 아닌 블로그를 완전히 신뢰해도 괜찮은 것일까? 이 방법이 정말 최선이라면 왜 공식 기능으로 제공하지 않고 있을까?
2. 포스팅 일자가 2012년(작성일 기준 13년 전)이다. 그 사이에 변경된 것이 많을 수 있다.

역시나, 찾아보니 게시글은 MySQL 5.5 시점에서 쓰여진 것으로, MySQL은 5.6 버전부터 Online DDL과 Offline DDL을 제공하며 효율적인 스키마 변경을 구축했다.

Online DDL과 Offline DDL

MySQL 5.6 이전

MySQL 5.6 이전의 DDL은 다음과 같은 순서로 스키마 변경을 진행한다.

1. 스키마 변경 사항이 반영된 임시 테이블을 생성한다.
2. 대상 테이블에 Read Lock을 걸고 데이터를 복사한다.
3. 임시 테이블에 대상 테이블 데이터를 복제한다.
4. 임시 테이블을 대상 테이블과 교체한다.

MySQL 5.6 이후

반면 MySQL 5.6 이후의 DDL은 가벼운 작업(Online DDL)과 무거운 작업(Offline DDL)으로 나뉜다.
Online DDL은 DDL 적용 과정에서 DML(INSERT, UPDATE, DELETE)를 대체로 허용하며, Offline DDL은 DML을 거부하고 대상 테이블에 Read/Write Lock을 건다.

ALGORITHM

MySQL DDL에 적용되는 알고리즘은 INSTANT, INPLACE, COPY로 나뉜다.

• INSTANT
  - Online DDL(MySQL 8.0.12+ 제공)
  - Data Dictionary에서 메타 데이터만 수정한다.
  - 테이블 데이터의 영향을 받지 않으므로 작업이 즉시 진행된다.
  - 동시 DML을 허용한다.
• INPLACE
  - Online DDL(MySQL 5.6+ 제공)
  - 임시 테이블을 생성하지 않지만, 경우에 따라 테이블 리빌딩이 발생할 수 있다.
  - 대부분 동시 DML을 허용한다.
• COPY
  - Offline DDL
  - 대상 테이블을 복사하여 임시 테이블을 만들어 작업한다.
  - 테이블 데이터는 대상 테이블으로부터 행 단위로 복사된다.
  - 동시 DML을 허용하지 않는다.

MySQL에서는 DDL에 알고리즘을 명시하지 않으면 가장 효율적이고 서비스에 영향이 적은 방식을 우선 선택한다. 그 순서와 특징은 다음과 같다.

1. INSTANT: 거의 즉시 완료되며 동시 DML을 완전히 허용한다.
2. INPLACE: 시간이 걸릴 수 있지만 임시 테이블이 불필요하며 동시 DML을 대부분 허용한다.(백그라운드 작업)
3. COPY: 시간이 오래 걸리고 동시 DML을 차단하며 추가 디스크 공간이 필요하다.

요약하면 사용자 관점에서는 Online DDL이 무중단 스키마 변경, Offline DDL이 중단 스키마 변경인 것이다.

[MySQL Docs]15.1.9 ALTER TABLE Statement - Performance and Space Requirements

특정 DDL에 대해 Online DDL 적용 여부가 궁금하다면 아래 공식문서를 참고하자.

[MySQL Docs]17.12.1 Online DDL Operations

임시 테이블

MySQL에서 기본적으로 제공하는 기능은 분명 훌륭하다. 하지만 Offline DDL의 경우에는 중단 스키마 변경을 해야 한다.
혹시 Offline DDL도 무중단으로 적용할 방법은 없을까?

프로시저와 트리거 기반 스키마 변경

서론(MySQL에서 테이블 스키마를 “무중단”으로 변경해보자!!)에서 언급했던 임시테이블 기반 스키마 변경은 다음과 같은 순서로 이루어진다.

1. 스키마 변경을 적용한 임시 테이블을 생성한다.
2. 스키마 변경 간 적용되는 데이터 변경 내역을 관리할 테이블을 추가로 생성한다.
3. 대상 테이블에서 변경이 일어날 시 변경 내역 테이블을 호출하도록 트리거를 생성한다.
4. 임시 테이블로 데이터를 복사한다.
5. 변경사항을 임시 테이블에 반영하고 테이블 이름을 스왑한다.
6. 변경사항을 다시 임시 테이블에 반영하고 트리거 및 변경 내역 테이블을 제거한다.

매번 이 과정을 직접 쿼리로 작성하는 것이 번거롭기 때문에 해당 글에서는 프로시저를 통해 쿼리 작성을 자동화하여 재사용성을 높였다.

분명 이 방식을 사용한다면 스키마 변경을 무중단으로 진행할 수 있다. 하지만 프로시저를 직접 정의하는 것이 번거롭고, 복잡한 DDL에는 대응이 힘들다는 단점이 있다. 이럴 때 우리는 외부 온라인 스키마 변경 도구에 의존하는 것을 고려해볼 수 있다.

트리거 기반 스키마 변경 도구

pt-online-schema-change는 Percona Toolkit에서 2011년 출시한 MySQL 서드파티 도구로, 테이블 락 없이도 스키마를 변경할 수 있는 기능을 제공한다.(ALTER tables without locking them.)

이 도구는 MySQL 내부의 테이블 변경 방식을 모방하지만, 대상 테이블의 복사본에서 작업함으로써 원본 테이블이 잠기지 않고 Read/Write가 가능하도록 해준다.

동작 과정은 다음과 같다.

1. 대상 테이블을 복사하여 임시 테이블을 생성한다.
2. 임시 테이블에 스키마 변경을 적용한다.
3. 대상 테이블에 대한 트리거를 생성한다.
4. 대상 테이블 데이터를 임시 테이블로 청크 단위로 복사한다.
5. 복사하는 동안 대상 테이블의 변경사항이 트리거에 의해 임시 테이블에 실시간 반영된다.
6. 복사 완료 후 잔여 변경사항을 최종 동기화한다.
7. 대상 테이블 데이터 복사 완료 후 임시 테이블과 교체한다.(RENAME TABLE, 원자성 보장)
8. 대상 테이블을 삭제한다.

앞에서 언급했던 프로시저 및 트리거 기반 스키마 변경과 거의 동일하나, 외부 도구가 거의 다 알아서 해준다는 차이가 있다. 우리는 10년 이상 사용되면서 수차례 검증된 도구를 믿고 맡길 수 있다.

[Percona Toolkit Docs]pt-online-schema-change

바이너리 로그 기반 스키마 변경 도구

gh-ost는 깃허브에서 2016년 출시한 online MySQL 스키마 마이그레이션 도구이다. 앞에서 언급한 도구와 같이 기존 온라인 스키마 변경 도구들은 트리거를 사용하는데, gh-ost는 스키마 대신 바이너리 로그 스트림(binary log stream)을 사용함으로써 트리거로 인한 제약과 위험을 회피한다.

동작 과정은 다음과 같다.

1. 대상 테이블을 복사하여 임시(고스트) 테이블을 생성한다.
2. 대상 테이블 데이터를 임시 테이블로 점진적 복사한다.
3. 바이너리 로그 스트림을 통해 원본 테이블의 변경사항을 캡처하고 임시 테이블에 비동기적으로 반영한다.
4. 데이터 복사 및 변경사항 동기화가 완료되면 대상 테이블과 임시 테이블을 교체한다.

그 외의 여러 특징이나 세부적인 내용에 대해서는 gh-ost 문서를 참고하자.

[Github]gh-ost Repository

pt-online-scheme-change vs gh-ost

두 도구는 어느 한 쪽이 더 좋고 나쁨을 구분할 수 없다. 각각의 특징이 있기 때문에 장단점과 제공하는 기능을 파악하고 상황에 맞는 기술을 사용하는 것이 바람직하다.

장단점

• gh-ost
  - 장점: 마스터에 미치는 영향이 낮음, 트리거 없음, 세밀한 제어, 재개 가능, 높은 트래픽에 최적화.
  - 단점: FK 지원 없음, RBR 필요, MySQL 5.7+만 지원, 복잡한 설정.
• pt-online-schema-change
  - 장점: FK 지원, 구형 MySQL 지원, 개념이 더 단순함, 성숙하고 널리 채택됨.
  - 단점: 트리거가 부하를 추가함, 컷오버 제어가 어려움, 쓰기 부하가 높은 워크로드에서 잠금을 유발할 수 있음.

기능

기능	gh-ost	pt-online-schema-change
트리거	없음 (바이너리 로그 사용)	있음 (DML 작업에 추가)
데이터 동기화	바이너리 로그를 통한 비동기	트리거를 통한 동기
마스터 부하	낮음 (복제본 사용)	높음 (마스터에 트리거 작동)
FK 지원	없음 (드롭 후 재생성 필요)	있음 (--alter-foreign-keys-method 옵션)
복제	RBR 필요, 복제본 선호	SBR/RBR 모두 작동, 마스터에서 실행
안전성	고급 (훅, 스로틀링, 체크)	양호 (스로틀링, 기본 체크)
컷오버	수동, 제어 가능, 짧은 락	즉시 이름 변경, 제어 부족
재개 가능	예	부분적 (아티팩트가 남아있는 경우)
MySQL 버전	5.7 이상만	5.5 이상 (구 버전 지원)
스로틀링	고급 (지연/훅을 통한 일시정지/재개)	기본 (복사 스로틀링만)
제한사항	JSON, 파티션, FK, 트리거	적음; PXC는 일부 제약 사항 있음
복제본 테스트	정확함 (바이너리 로그 기반)	제한적 (트리거가 작동하지 않음)

gh-ost vs pt-online-schema-change in 2025

결론

정리

지금까지 스키마 무중단(온라인) 변경 방법에 대해 알아보았다.
MySQL은 가벼운 DDL의 경우 Online DDL로 무중단 변경을 지원한다. 하지만 Offline DDL의 경우에는 스키마 변경 시 테이블에 Read 락이 걸려 중단 스키마 변경이 되어버린다.

하지만 임시 테이블 방식을 사용하면 이를 해소할 수 있다. 프로시저를 통해 직접 트리거 기반 온라인 스키마 변경을 구축하거나, 트리거 기반 온라인 스키마 변경 도구를 사용하거나, 바이너리 로그 기반 온라인 스키마 변경 도구를 사용할 수 있다.

항상 스키마 무중단 변경이 가능할까?

그렇다면 Offline DDL이면 항상 온라인 스키마 변경 도구를 활용하면 되는 것일까? 아니다. 무작정 도입해서는 안되고 상황에 맞는 선택이 필요하다.

외부 온라인 스키마 변경 도구는 기본적으로 임시 테이블에 기반하여 동작함으로써 무중단을 지원하는데, 이를 위해 임시 테이블에 기존 테이블 데이터를 전부 복제해야 한다. 이 과정에서 상당한 DB 성능 저하가 발생할 수 있으며 저장공간이 일시적으로 기존 테이블 용량만큼 추가로 필요해질 수 있다. 이런 사이드이펙트를 감수할 여유가 있을지, 감수하고서라도 온라인 스키마 변경이 필요한지 다시 한 번 검토해보자.

점검 시간 가지기

Offline DDL 반영이 그렇게 시급한 상황이 아니라면 되도록 점검 시간(Maintenance Window)을 가져보자.
Offline DDL 반영 중에는 예상치 못한 문제가 발생할 수 있고, 롤백에도 큰 위험이 따른다. 여기에 빠르고 유연하게 대응하기 위해서는 점검 시간을 가지는 것이 가장 바람직할 수 있다.

회고

요구사항을 처음 읽어봤을 때는 내 눈을 의심했다. 스키마 변경은 당연히 무중단으로 진행할 수 있다는 듯한 문구였다. 임시 테이블까지 찾아봤을 때는 약간 희망에 부풀었던 것 같다. 이 좋은 걸 왜 안쓰지? 싶었다. 하지만 조금 더 생각해보니 DB 성능을 떨어뜨리고 용량을 많이 요구한다는 것은 당연했다. 이렇게 정리하면서 내린 결론은 Offline DDL에 한해 스키마 중단 배포가 더 바람직할 수 있다.였다.

지금까지는 금방 완료되거나 오래걸리는 DDL의 차이를 잘 몰랐는데, 학습하면서 어떤 DDL을 조심해야 하는지 알 수 있었다. 무중단만을 추구하는 것은 좋은 자세가 아니라고 생각했고, 필요에 따라 점검 시간을 가지는 것은 불가피한 것 같다.

당장 무중단 스키마 변경 도구를 도입하지는 않겠지만 Online DDL과 Offline DDL, 그리고 외부 무중단 스키마 변경 도구의 차이를 알고 쓸 수 있게 되었음에 만족한다.

참고자료

25년 8월 25일 기준 참고 자료

• MySQL에서 테이블 스키마를 “무중단”으로 변경해보자!!
• [MySQL Docs]15.1.9 ALTER TABLE Statement - Performance and Space Requirements
• [MySQL Docs]17.12.1 Online DDL Operations
• [Percona Toolkit Docs]pt-online-schema-change
• [Github]gh-ost Repository
• gh-ost vs pt-online-schema-change in 2025