포스트

카카오 「MySQL ALTER DDL 수행 방식에 대한 이해」 리뷰 — Copy·In-Place·Instant는 '무엇을 복사하느냐'보다 '언제 Exclusive 메타 락을 잡느냐'로 구분된다

Tech Blog Review
게시
By August
좋아요 조회 11 분 읽는 시간

원문: MySQL ALTER DDL 수행 방식에 대한 이해 — kakao tech, 2025-05-14

한 줄 요약

MySQL의 ALTER는 세 알고리즘으로 돈다. Copy(새 테이블에 전부 복사, 쓰기 차단), In-Place(5.6+, 임시 구조와 DML 로그로 읽기·쓰기 허용, 필요 시 리빌드), Instant(8.0+, 메타데이터만 수정). 원문은 소스 코드 수준에서 ALTER의 3단계(Initialization → Execution → Final)와 세 핵심 함수(ha_prepare_inplace_alter_table, ha_inplace_alter_table, ha_commit_inplace_alter_table)를 따라가며 어느 시점에 어떤 메타데이터 락을 잡는지를 비교한다. 핵심 발견은 In-Place가 prepare 전과 commit 전 두 번 Exclusive 락을 잡고, Instant는 commit 전 한 번만 잡는다는 것, 그리고 메타데이터만 바꾸는 작업이라도 In-Place로 지정하면 두 번 잡는다는 것이다. 결론은 습관 둘: ALGORITHM을 명시하라, 실행 전 장기 트랜잭션을 확인하라.

배경

MySQL은 서비스가 커질수록 운영이 어려웠고, 그중 하나가 서비스 중 Online DDL이다. 5.6에서 In-Place가 추가돼 가용성이 올라갔지만, 시작과 끝에 메타 락이 필요하고 작업에 따라 임시 테이블과 복사도 필요해 편하지는 않았다. 8.0의 Instant까지 셋을 이해해야 운영에서 사고를 피한다.

ALTER의 3단계

Initialization. 사용자가 지정한 알고리즘으로 가능한지 확인하고(컬럼명 중복 등 메타데이터만으로 알 수 있는 것), handler::check_if_supported_inplace_alter()로 필요한 락 모드를 얻어 사용자가 지정한 LOCK=과 충돌하면 오류로 중단한다.

Execution. 실제 작업. 메타데이터 락 네 가지를 알아야 한다.

락의미
MDL_SHARED_UPGRADABLE승격 가능한 공유 락. 다른 세션의 읽기·쓰기 허용
MDL_SHARED_READ읽기용 공유 락
MDL_SHARED_NO_WRITE다른 세션의 읽기는 허용, 쓰기는 차단
MDL_EXCLUSIVE모든 접근 차단

순서는 이렇다. ① MDL_SHARED_UPGRADABLE 획득. ② In-Place면 MDL_EXCLUSIVE로 승격. ③ ha_prepare_inplace_alter_table()로 제약 검사, 인덱스·외래키 메타데이터 갱신(Instant는 건너뜀). ④ In-Place면 다시 강등. DDL 중 DML을 막아야 하면 SHARED_NO_WRITE, 아니면 SHARED_UPGRADABLE. ⑤ ha_inplace_alter_table()로 실제 변경(Instant는 건너뜀). ⑥ MDL_EXCLUSIVE로 승격 후 ha_commit_inplace_alter_table()로 메타데이터 변경.

Final. Data Dictionary 갱신과 커밋. atomic DDL을 지원하는 엔진은 테이블 이름 교체를 먼저 하고 DD를 갱신·커밋하며, 아닌 엔진은 반대다. 마지막에 MDL_SHARED_READ로 메타데이터를 확인하고 정리한다.

세 함수

  • prepare: 인덱스 이름·컬럼 이름 제약을 검사하고 인덱스·외래키 메타데이터를 갱신. Instant는 아무것도 안 하고 빠져나온다.
  • inplace_alter: 테이블 리빌드, DML 로그 적용, 인덱스 구성. Instant이거나, In-Place라도 인덱스 구성이 아니고 리빌드도 필요 없으면 아무것도 안 한다.
  • commit: 임시 이름 생성, 통계 스레드 접근 차단, 원본과 새 테이블 메타데이터 동기화. Instant는 dd_add_instant_columns로 row_version을 올리고 DEFAULT 값을 설정한다.

세 알고리즘

Copy. 가장 오래되고 단순하다. DDL이 적용된 새 테이블을 만들고 → 데이터를 복사하고 → 이름을 바꾸고 옛 테이블을 지운다. 읽기는 되지만 쓰기는 끝날 때까지 막힌다. 8.0 기준 PK 삭제, 컬럼 타입 변경, 문자셋 변환은 Copy로만 된다. 디스크 공간·I/O·시간이 테이블 크기에 비례하고 복제 지연을 만들며 롤백도 오래 걸린다.

In-Place. 5.6에서 추가. 대부분의 작업에서 읽기·쓰기가 되고 필요하면 리빌드한다. 리빌드 기준으로 임시 테이블 생성 → 데이터 복사(타입 변환 포함) → 복사 중 유입된 쓰기는 별도 버퍼에 DML 로그로 적재 → 복사가 끝나면 DML 로그 적용 → 이름 교체. 원문의 내부 테스트로는 이 임시 테이블 작업이 Copy의 전체 복사와는 다른 것으로 추정되며 더 빠르다. 단점은 여전히 크기에 비례하는 시간, 일부 작업의 쓰기 차단, 높은 I/O, 복제 지연, Exclusive 메타 락 2회, innodb_online_alter_log_max_size(DML 로그 상한)를 잘못 잡으면 실패한다는 것.

Instant. 8.0.12에서 추가. 메타데이터만 바꾸고 row_version을 올린다. 컬럼 추가/삭제, DEFAULT 설정, ENUM 명세 변경을 지원하며 확대 중이다(컬럼 추가 외는 8.0.29부터). 단점은 지원 작업이 적고, 64번까지만 가능하며 그 뒤에는 리빌드가 필요하고, FULLTEXT 인덱스나 ROW_FORMAT=COMPRESSED 테이블에서는 컬럼 추가/삭제가 안 되며, Exclusive 락이 짧게 1회 있다는 것.

 CopyIn-PlaceInstant
테이블 복사예부분적(내부 중간 구조)아니오
동시 DML아니오작업에 따라예
디스크 오버헤드높음중간(임시 파일·로그)매우 낮음
시간가장 느림작업·크기에 따라가장 빠름
버전모두5.6+8.0.12+

핵심: 메타 락 비교

두 알고리즘 모두 MDL_SHARED_UPGRADABLE로 시작하니 그 단계까지는 다른 세션에 영향이 없다. 차이는 다음이다.

  1. prepare 전: In-Place만 MDL_EXCLUSIVE로 승격한다. 이때부터 다른 세션이 메타 락 대기에 빠질 수 있다. prepare가 끝나면 다시 강등한다.
  2. inplace_alter 중: 둘 다 SHARED_UPGRADABLE. Instant이거나 In-Place 중 리빌드·인덱스가 아니면 이 안에서 하는 일이 없다.
  3. commit 전: 둘 다 MDL_EXCLUSIVE로 승격하고 commit 함수가 끝날 때까지 다른 세션을 막는다.
  4. Final: MDL_SHARED_READ.

예외로 AUTO_INCREMENT INT 컬럼 추가는 In-Place에서 SHARED_UPGRADABLE이 아니라 SHARED_NO_WRITE로 강등한다.

그리고 궁금할 만한 실험 하나. In-Place로도 메타데이터만 수정하는 작업이 있는데, 그러면 Instant와 같지 않을까? 디버깅해 보니 아니다. 소스가 알고리즘만 보고 승격을 결정하므로 메타데이터만 바꾸는 작업이라도 In-Place면 prepare 전에 MDL_EXCLUSIVE를 잡는다. 즉 Exclusive 락 횟수는 작업 내용이 아니라 지정한 알고리즘이 정한다.

결론: 두 가지 습관

작업에 따라 쓸 수 있는 알고리즘이 정해져 있어 선택의 폭은 좁다. 그래도 어떤 경우든 ALGORITHM 구문을 명시하라. 그래야 예기치 않게 느린 알고리즘으로 대체되는 것을 막는다(지정한 알고리즘이 불가능하면 오류로 멈추므로 방어적이다). 그리고 어떤 DDL이든 Exclusive 메타 락이 짧게라도 들어가므로 실행 전 장기 실행 트랜잭션·쿼리가 없는지 확인하라. 하나라도 있으면 그 뒤의 모든 세션이 줄줄이 대기한다.

읽고 남는 질문

  • Instant의 64회 제한이 실제 운영에서 얼마나 빨리 닿는지, 닿았을 때의 리빌드를 어떻게 스케줄하는지가 궁금하다. 컬럼 추가가 잦은 테이블은 생각보다 빨리 닿을 수 있다.
  • In-Place의 “임시 테이블 작업이 Copy의 전체 복사와 다른 것으로 추정”은 추정에 그친다. 크기가 큰 테이블에서 실제 소요 시간과 디스크 사용량을 비교한 수치가 있으면 좋겠다.
  • LOCK=NONE을 명시하는 것도 ALGORITHM 명시만큼 중요한 방어인데(지원 안 되면 오류로 멈춤), 결론에 같이 언급됐으면 완결성이 있었을 것이다.

한 줄로 가져가기

Online DDL의 위험은 복사 시간이 아니라 승격되는 Exclusive 메타 락의 순간이다. In-Place는 두 번, Instant는 한 번이고, 그 순간에 긴 트랜잭션이 하나라도 있으면 서비스 전체가 그 뒤에 줄을 선다.

시리즈

빅테크 기술 블로그 리뷰

31편 중 5편

이전 시리즈 글 네이버 D2 「6개월 만에 연간 수십조를 처리하는 DB CDC 복제 도구 무중단/무장애 교체하기」 리뷰 — 복제·검증·복구를 셋으로 나누고, 옛 도구와 새 도구를 서로 모르게 같이 돌린 전환 다음 시리즈 글 카카오 「MySQL Orchestrator 기반의 새로운 HA 표준 개발기」 리뷰 — 10년 멈춘 Perl 도구를 떠나 Raft 클러스터로, 그리고 slave_net_timeout 한 줄
  1. 1 카카오 「실시간 메시징 시스템 개발기」(3편) 리뷰 — Redis에 몰린 부하를 서버로 옮기고, 그 서버를 pprof로 세 번 깎은 이야기
  2. 2 카카오 「추가배포 없이 API의 case 통일시키기」 리뷰 — 받는 쪽이 자기 케이스에 맞춰 알아서 읽게 하면 배포 순서가 사라진다
  3. 3 카카오 「MySQL DATETIME, TIMESTAMP 데이터 타입에 대한 분석」 리뷰 — 바이트 단위 저장 구조부터 아직 안 고쳐진 Y2K38까지
  4. 4 네이버 D2 「6개월 만에 연간 수십조를 처리하는 DB CDC 복제 도구 무중단/무장애 교체하기」 리뷰 — 복제·검증·복구를 셋으로 나누고, 옛 도구와 새 도구를 서로 모르게 같이 돌린 전환
  5. 5 카카오 「MySQL ALTER DDL 수행 방식에 대한 이해」 리뷰 — Copy·In-Place·Instant는 '무엇을 복사하느냐'보다 '언제 Exclusive 메타 락을 잡느냐'로 구분된다
  6. 6 카카오 「MySQL Orchestrator 기반의 새로운 HA 표준 개발기」 리뷰 — 10년 멈춘 Perl 도구를 떠나 Raft 클러스터로, 그리고 slave_net_timeout 한 줄
  7. 7 카카오 「MySQL Ver. 8.0 New Feature: Instant DDL Algorithm에 대한 이해」 리뷰 — 컬럼을 0.01초에 추가하는 대가는 '읽을 때마다 버전을 대조하는 것'이다
  8. 8 네이버 D2 「CDC 복제 이후 오라클이 느려졌다? child cursor 폭증이 만든 예상치 못한 문제」 리뷰 — 같은 SQL인데 바인딩 타입이 다르면 Oracle은 다른 쿼리로 본다
  9. 9 카카오 「MySQL InnoDB Log에 대한 이해 - (1)」 리뷰 — 트랜잭션 하나가 어떻게 MTR 여러 개로 쪼개져 Redo Log Buffer에 들어가는가
  10. 10 카카오 「PostgreSQL to ES: Kafka Connect CDC 파이프라인」 1·2편 리뷰 — 변경이 없어서 디스크가 차고, LSN이 사라져서 스냅샷을 다시 짜야 했던 CDC의 실제 운영 비용
  11. 11 LINE 「기획서 없이 내재화하기: 검증 로직으로 동일함을 증명하다」 리뷰 — 블랙박스는 입력과 출력만 정의하면 통계로 같음을 증명할 수 있다
  12. 12 뱅크샐러드 「게임을 만들 때 데이터 정합성을 유지하는 법 (feat. 낙관적 락)」 리뷰 — 같은 WHERE version 조건인데, 충돌한 요청을 어떻게 하는가에서 갈리는 두 설계
  13. 13 우아한형제들 「Spring Batch와 Querydsl」 리뷰 — offset을 버린 Reader가 21분을 4분으로 만든 이유, 그리고 그 Reader가 답하지 않는 두 가지
  14. 14 네이버 D2 「테스트는 어떻게 좋은 코드를 만드는가(feat. 험블 객체 패턴)」 리뷰 — 목이 많아지는 것은 테스트의 문제가 아니라 설계의 신호
  15. 15 당근 「QR을 찍으면 무슨 일이 벌어질까? 당근페이 현장 결제의 모든 것」 리뷰 — 카드망을 빌려 7주 만에 낸 결제와, 그 글이 다루지 않은 승인 응답이 사라지는 순간
  16. 16 네이버 D2 「스마트스토어센터 Oracle에서 MySQL로의 무중단 전환기」 리뷰 — 두 DB에 동시에 쓰되 한쪽 실패는 무시하고, 읽기 트래픽을 복제해 성능을 재고, 6개월간 불일치를 0으로 만든 과정
  17. 17 야놀자 「RESTful API validation 자동화 하기」 리뷰 — 인터페이스 하나에서 검증·문서·타입을 뽑는 이유, 그리고 자동화가 없으면 누락이 필연이라는 문장
  18. 18 카카오 「MySQL Json 데이터 타입의 저장 구조와 성능 비교」 리뷰 — 통째로 넣고 통째로 꺼내면 TEXT, 키로 파고들면 JSON
  19. 19 LINE 「도메인에 의존하지 않는 채팅 플랫폼은 어떻게 만들었을까?」 리뷰 — 사용자를 모르는 채팅 플랫폼, 웹으로 만든 클라이언트, SOFT STOP으로 갈아 끼우는 챗봇 시나리오
  20. 20 카카오페이 「MSA 환경에서 네트워크 예외를 잘 다루는 방법」 리뷰 — Unknown을 타입으로 만든 글과, Unknown을 상태로 저장한 프로젝트가 갈리는 지점
  21. 21 카카오 「메시징 서버의 스트레스 테스트 노하우와 AI가 덜어 준 부분」 리뷰 — 지표를 네 층으로 내려가 읽는 법, 그리고 LLM에게 맡긴 것과 맡기지 못한 것
  22. 22 네이버 D2 「일 3,000만 건의 네이버페이 주문 메시지를 처리하는 Kafka 시스템의 무중단 전환 사례」 리뷰 — 두 벌로 발행해 대조한 검증기와, 발행 제어 키를 파티션 키와 같게 둔 이유
  23. 23 카카오 「잃어버린 리포트를 찾아서: 카카오 메시징 시스템의 경쟁 조건 문제와 안티 패턴 제거 과정」 리뷰 — 벤더가 8ms 만에 리포트를 보냈고, 우리는 101ms짜리 트랜잭션 안에 있었다
  24. 24 컬리 「컬리의 입고 시스템이 외부 인입 데이터를 안전하게 동기화하는 방법」 리뷰 — 145회 재시도가 맞는 도메인과 재시도를 금지한 도메인, 그리고 발행부와 수신부가 각자 책임지는 구조
  25. 25 네이버 D2 「@RequestCache: HTTP 요청 범위 캐싱을 위한 커스텀 애너테이션 개발기」 리뷰 — 캐시의 수명을 '요청 하나'로 맞추면 TTL 고민이 사라진다, 그리고 @RequestScope가 안 되는 이유
  26. 26 무신사 「Kafka와 Strimzi를 이용하여 6개의 도메인을 하나의 도메인으로 합쳐보았습니다」 리뷰 — 배치 없이 CDC와 Kafka Streams로 옮긴 결정, 그리고 통합 모델이 원본과 같다는 것을 누가 확인하는가
  27. 27 쿠팡 「대용량 트래픽 처리를 위한 쿠팡의 백엔드 전략」 리뷰 — 캐시 두 겹과 '분 단위 99.99% 동일'이라는 문장, 그리고 그 0.01%를 누가 어떻게 세는가
  28. 28 LINE 「LINE에서 Kafka를 사용하는 방법 - 1편」 리뷰 — 초당 4GB 클러스터가 바이트가 아니라 요청 수를 제한하는 이유, 그리고 2,000틱짜리 파이프라인에서 같은 것을 본 기록
  29. 29 카카오 「MySQL 인증 플러그인 caching_sha2_password에 대한 이해」 리뷰 — 비밀번호 해시가 바뀌는 것보다 '평문이 서버까지 가야 한다'는 점이 전환의 진짜 비용
  30. 30 LINE 「초당 100만 건, LINE 앱에 Apache Kafka 종단 간 암호화 적용기」 리뷰 — 인터셉터와 시리얼라이저만으로 브로커에 평문을 남기지 않는 법
  31. 31 카카오뱅크 「하루 N억 건의 알림 시스템 구축기 (1)」 리뷰 — P99의 80%가 대기였다는 진단, Age 기반 Work-Stealing, 그리고 큐를 나누는 순간 순서를 잃는 문제
TechBlog, Kakao
Tech Blog Review Kakao MySQL InnoDB DDL Online DDL Metadata Lock Database
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.

댓글

아직 댓글이 없습니다