포스트

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

Tech Blog Review
게시 수정 작성자 August
좋아요 조회 13 분 읽는 시간
시리즈 빅테크 기술 블로그 리뷰 32편 중 5편 빅테크 기술 블로그 리뷰
  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, 그리고 큐를 나누는 순간 순서를 잃는 문제
  32. 32 Airbnb 「Avoiding double payments in a distributed payments system」 리뷰 — 네트워크와 DB 트랜잭션을 섞지 않는 세 단계, 재시도 가능 여부의 분류, 그리고 복제본을 읽으면 이중 결제가 나는 이유

원문: 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)를 따라가며 어느 시점에 어떤 메타데이터 락(MDL, 테이블 구조를 보호하는 락)을 잡는지를 비교한다. 원문이 확인한 것은 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로 시작하니 그 단계까지는 다른 세션에 영향이 없다. 아래 그림은 원문의 단계별 설명을 내가 락 전이 순서로 옮긴 것이다.

flowchart TD
    subgraph IP["In-Place"]
        A1["MDL_SHARED_UPGRADABLE"] --> A2["MDL_EXCLUSIVE<br/>prepare"]
        A2 --> A3["SHARED_UPGRADABLE 또는<br/>SHARED_NO_WRITE<br/>inplace_alter"]
        A3 --> A4["MDL_EXCLUSIVE<br/>commit"]
        A4 --> A5["MDL_SHARED_READ<br/>Final"]
    end
    subgraph INS["Instant"]
        B1["MDL_SHARED_UPGRADABLE<br/>prepare, inplace_alter 건너뜀"] --> B4["MDL_EXCLUSIVE<br/>commit"]
        B4 --> B5["MDL_SHARED_READ<br/>Final"]
    end

단계별 차이는 다음이다.

  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 구문을 명시하라는 것이 원문의 첫 권고다. 그래야 예기치 않게 느린 알고리즘으로 대체되는 것을 막는다. 원문은 이를 방어적 수행 방식이라고 부른다. 공식 문서에 따르면 ALGORITHM을 지정했는데 그 알고리즘을 지원하지 않는 작업이면 오류로 실패한다(ALTER TABLE Statement). 두 번째 권고는 실행 전 장기 실행 트랜잭션이 없는지 확인하라는 것이다. 어떤 DDL이든 Exclusive 메타 락이 짧게라도 들어가기 때문이다. 공식 문서는 그 결과를 이렇게 적는다. “Additionally, a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table.”(Online DDL이 요청해 대기 중인 Exclusive 메타데이터 락은 그 테이블의 뒤이은 트랜잭션을 막는다.) (Online DDL Performance and Concurrency) 그래서 긴 트랜잭션 하나가 있으면 그 뒤의 세션이 줄줄이 대기한다.

읽고 남는 질문

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

한 줄로 가져가기

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

참고한 자료외부 출처 3

외부 출처

TechBlog, Kakao
Tech Blog Review Kakao MySQL InnoDB DDL Online DDL Metadata Lock Database
데이터베이스 내부와 트랜잭션
이 글은 저작권자의 CC BY 4.0 라이선스를 따릅니다.

변경이력

2번 수정

  1. docs(posts): separate the sections of every post with a thematic break
  2. docs(techblog): cite sources and ease reading in kakao-mysql-alter-ddl-algorithms, add a diagram

댓글

아직 댓글이 없습니다