Bong's Log

단일 테이블의 데이터가 수천만 건을 넘어 수억 건에 도달하면, 아무리 인덱스를 정교하게 설계하더라도 물리적인 I/O 한계에 부딪히게 됩니다. B-Tree 인덱스의 깊이(Depth)가 깊어지고, 워킹 셋(Working Set)의 크기가 innodb_buffer_pool_size를 초과하면서 디스크 스왑(Swap)이 빈번해지기 때문입니다.

이때 샤딩(Sharding)이라는 무거운 아키텍처 변경 전에 반드시 검토해야 할 엔지니어링 기법이 바로 MySQL 파티셔닝(Partitioning)입니다. 이번 포스팅에서는 InnoDB 스토리지 엔진 관점에서의 파티셔닝 동작 원리와 각 파티션의 종류, 특히 데이터 균등 분산의 핵심인 HASH 파티션의 장단점과 실무 관리 방법을 심도 있게 다루어 보겠습니다.

1. MySQL 파티셔닝의 내부 작동 원리 (Architecture)

파티셔닝은 "논리적으로는 하나의 테이블이지만, 물리적으로는 여러 개의 독립된 테이블스페이스(.ibd)로 분할하여 저장하는 기법"입니다.

과거 MySQL 5.7 이전에는 스토리지 엔진 상위의 핸들러(ha_partition)가 파티셔닝을 제어했지만, MySQL 8.0부터는 네이티브 파티셔닝(Native Partitioning)이 완전히 정착되어 InnoDB 스토리지 엔진 자체가 파티션을 직접 제어합니다.

핵심 메커니즘 2가지

• 독립적인 B-Tree 구조: 파티션을 4개로 나누면, 클러스터드 인덱스(PK)와 세컨더리 인덱스 모두 4개의 독립된 B-Tree로 쪼개집니다. 인덱스 트리의 Depth가 낮아져 탐색 비용이 감소하고, 자주 쓰이는 파티션의 인덱스만 버퍼 풀(Buffer Pool)에 적재하여 메모리 효율을 극대화할 수 있습니다.
• 파티션 프루닝 (Partition Pruning): 옵티마이저가 쿼리의 WHERE 절을 분석하여, 읽지 않아도 되는 물리적 파티션을 실행 계획 수립 단계에서 아예 배제해 버리는 최적화 기법입니다.

실무 제약사항 (Primary Key와 파티션 키의 관계)

MySQL 파티셔닝에서 가장 중요한 규칙은 "파티션 키는 반드시 테이블의 모든 Unique Key(Primary Key 포함)의 일부여야 한다"는 점입니다. InnoDB는 유니크 제약을 검증할 때 단일 파티션 내부만 스캔하도록 설계되어 있습니다. 만약 파티션 키가 PK에 포함되지 않으면, 데이터 INSERT 시 중복 검사를 위해 모든 파티션을 뒤져야 하므로 아키텍처 레벨에서 이를 금지하고 있습니다.

2. MySQL 4대 파티션 종류 및 특징 비교

데이터의 분포 특성과 애플리케이션의 조회 패턴(Access Pattern)에 따라 아래 4가지 파티션 중 하나를 선택해야 합니다.

① RANGE 파티션

실무에서 가장 많이 사용되는 형태입니다. 날짜 컬럼을 기준으로 월별 또는 연도별로 파티션을 나누며, 보관 주기가 지난 과거 데이터를 삭제할 때 DELETE 대신 ALTER TABLE ... DROP PARTITION을 사용하여 Undo Log 발생 없이 즉각적으로 디스크 공간을 회수할 수 있습니다.

② LIST 파티션

특정 코드값이나 지역 구분처럼 이산적인(Discrete) 값들을 그룹핑할 때 사용합니다. 단, 정의되지 않은 값이 INSERT 될 경우 에러가 발생하므로(MySQL 8.0 기준 DEFAULT 파티션 미지원), 애플리케이션 레벨에서 들어오는 값의 종류가 명확히 통제되어야 합니다.

③ KEY 파티션

HASH 파티션과 목적은 같지만, 사용자가 해시 함수를 정의하지 않고 MySQL 서버가 내부적으로 해싱 알고리즘을 수행합니다. 정수형 표현식만 허용하는 일반 HASH와 달리 문자열이나 다중 컬럼을 그대로 지정할 수 있다는 장점이 있습니다.

3. HASH 파티션 심층 분석: 작동 원리와 장단점

시간의 흐름에 따라 쌓이는 로그성 데이터가 아니라, user_id나 device_id처럼 특정 범위로 묶기 애매하지만 데이터 크기가 비대해지는 마스터성 테이블에는 HASH 파티션이 사용됩니다.

작동 원리: 일반 HASH vs LINEAR HASH

• 일반 HASH 파티션: N = MOD(expr, num_partitions) 공식을 사용합니다. 예를 들어 파티션이 4개이고 user_id가 10이면 10 % 4 = 2번 파티션에 저장됩니다. 데이터 분산도가 매우 균일하지만, 파티션 개수를 늘리거나 줄일 때 전체 데이터의 나머지 연산 결과가 바뀌므로 거의 모든 파티션의 데이터를 재구축(Rebuild)해야 합니다.
• LINEAR HASH 파티션: 거듭제곱 기반의 리니어 해싱(Linear Hashing) 알고리즘(비트 연산)을 사용합니다. 파티션 개수를 변경할 때 전체가 아닌 일부 파티션만 병합/분할하므로 관리 속도가 빠르지만, 파티션 개수가 2의 거듭제곱($2^n$)이 아닐 경우 데이터 쏠림(Skewness) 현상이 발생할 수 있습니다.

HASH 파티션의 장점 

1. 데이터의 완벽한 균등 분산: RANGE 파티션은 특정 시기(예: 블랙프라이데이)에 데이터가 몰리면 특정 파티션만 비대해지는 현상이 발생하지만, HASH 파티션은 모든 파티션의 크기를 거의 동일하게 유지합니다.
2. 특정 파티션의 I/O 병목(Hotspot) 방지: 최근 데이터에만 쓰기가 집중되는 시계열 파티션과 달리, INSERT/UPDATE 트랜잭션이 모든 파티션 파일에 고르게 분산되어 버퍼 풀 경합을 줄여줍니다.
3. 파티션 관리 자동화 불필요: RANGE 파티션처럼 매달 새로운 파티션을 미리 생성(ADD PARTITION)해 주는 배치 스크립트를 운영할 필요가 없습니다.

HASH 파티션의 단점 - 실무 주의사항

1. 범위 검색(Range Scan) 시 파티션 프루닝 불가: 해시 함수 특성상 연속된 값들이 서로 다른 파티션으로 흩어집니다. 따라서 WHERE user_id BETWEEN 100 AND 200 같은 범위 조건 쿼리를 실행하면 옵티마이저는 어느 파티션에 데이터가 있는지 알 수 없어 모든 파티션을 뒤지는 Full Partition Scan을 수행합니다.
2. 오래된 데이터 일괄 삭제 불가: 데이터가 전 파티션에 골고루 섞여 있으므로 DROP PARTITION을 통한 빠른 데이터 아카이빙이 불가능합니다.
3. 파티션 리사이징 비용: 데이터가 커져서 파티션을 4개에서 8개로 늘릴 때, 기존 데이터의 해시 버킷 위치가 재계산되면서 막대한 디스크 I/O와 테이블 잠금(또는 Online DDL 부하)이 발생합니다.

4. HASH 파티션 설정 및 실무 관리 방법

실무에서 HASH 파티션을 생성하고, 실행 계획을 검증하며, 운영 중 파티션을 확장/축소하는 구체적인 방법을 살펴보겠습니다.

① HASH 파티션 테이블 생성 (복합 PK 구성)

파티션 키(user_id)를 반드시 Primary Key에 포함시켜야 합니다. MySQL 8.0부터는 UNIX_TIMESTAMP() 등 일부 함수를 제외한 복잡한 표현식보다 순수 정수형 컬럼 자체를 해시 키로 쓰는 것이 프루닝에 유리합니다.

CREATE TABLE tb_user_activity (
    activity_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 제약사항: 파티션 키인 user_id가 반드시 PK에 포함되어야 함
    PRIMARY KEY (activity_id, user_id),
    KEY idx_user_created (user_id, created_at)
) ENGINE=InnoDB
PARTITION BY HASH(user_id)
PARTITIONS 8;

② 실행 계획을 통한 파티션 프루닝(Pruning) 검증

쿼리가 의도한 대로 단일 파티션만 스캔하는지 EXPLAIN의 partitions 항목을 통해 반드시 확인해야 합니다.

-- 1. 동등 비교 (=) 또는 IN 조건: 파티션 프루닝 정상 작동
EXPLAIN SELECT * FROM tb_user_activity WHERE user_id = 1024;

1024 % 8 = 0이므로 옵티마이저가 정확히 p0 파티션 하나만 접근한 것을 확인할 수 있습니다.

-- 2. 범위 비교 (BETWEEN, >, <): 파티션 프루닝 실패 (주의!)
EXPLAIN SELECT * FROM tb_user_activity WHERE user_id BETWEEN 1000 AND 1010;

범위는 해시 결과를 특정할 수 없으므로 p0부터 p7까지 8개 파티션의 인덱스 트리를 모두 탐색합니다.

이 경우 파티셔닝을 하지 않은 단일 테이블보다 오히려 성능이 저하됩니다.

③ 파티션 개수 조정 (확장 및 축소)

RANGE 파티션과 달리 HASH 파티션은 DROP PARTITION 구문을 사용할 수 없으며, 아래의 전용 구문을 통해 크기를 조절하고 데이터를 재배치해야 합니다.

-- 1. 파티션 개수 늘리기 (8개 -> 12개로 4개 추가)
ALTER TABLE tb_user_activity ADD PARTITION PARTITIONS 4;

-- 2. 파티션 개수 줄이기 (12개 -> 8개로 4개 병합/축소)
ALTER TABLE tb_user_activity COALESCE PARTITION 4;

운영 중 DDL 실행 시 주의점

ADD PARTITION이나 COALESCE PARTITION은 내부적으로 데이터를 다시 읽고 쓰는 리빌드(Rebuild) 작업을 동반합니다. 수억 건의 테이블에 바로 실행하면 마스터-슬레이브 복제 지연(Replication Lag)과 디스크 I/O 포화를 유발하므로, 실무에서는 트래픽이 적은 새벽 시간대에 수행하거나 pt-online-schema-change 또는 gh-ost 같은 Online Schema Change 도구를 활용하여 무중단으로 마이그레이션해야 합니다.

④ 파티션별 데이터 분포도 모니터링

HASH 파티션이 실제로 데이터를 고르게 분산시키고 있는지 확인하려면 information_schema.PARTITIONS 뷰를 조회합니다.

SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / (1024 * 1024), 2) AS DATA_SIZE_MB,
    ROUND(INDEX_LENGTH / (1024 * 1024), 2) AS INDEX_SIZE_MB
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'tb_user_activity';

5. 마무리 :실무에서 마주하는 함정과 해결책

파티셔닝은 무조건 정답은 아닙니다. 잘못 적용하면 오히려 쿼리 응답 속도가 수 배 느려지는 장애를 겪게 됩니다. 도입 전 아래 두 가지를 반드시 점검하시기 바랍니다.

1. 파티션 키가 없는 세컨더리 인덱스 조회의 비효율성 (Scatter-Gather) 위 예시 테이블에서 user_id 조건 없이 WHERE action_type = 'LOGIN'으로 조회한다면 어떻게 될까요? 옵티마이저는 파티션 프루닝을 포기하고 8개의 파티션을 모두 열어 각각의 B-Tree를 탐색한 뒤 결과를 병합합니다. 파티션 개수가 100개라면 1번의 인덱스 탐색으로 끝날 일을 100번의 인덱스 루트-리프 탐색으로 늘리는 셈입니다. 전체 조회 쿼리의 90% 이상이 파티션 키를 동반할 때만 파티셔닝을 도입해야 합니다.
2. open_files_limit 및 테이블 캐시 관리 파티션 1개는 물리적으로 1개의 .ibd 파일입니다. 테이블 100개에 각각 100개의 파티션을 만들면 MySQL 프로세스가 열어야 하는 파일 디스크립터 수가 순식간에 10,000개로 늘어납니다. 따라서 파티션을 적극적으로 사용하는 환경에서는 my.cnf의 open_files_limit과 table_open_cache, innodb_open_files 파라미터를 충분히 높여주어야 파일 핸들 부족으로 인한 에러를 예방할 수 있습니다.

앞선 포스팅에서 ALGORITHM=INSTANT를 활용한 Native Online DDL과 gh-ost를 통한 무중단 스키마 변경 전략을 살펴보았습니다. 하지만 "0.1초 만에 끝나는 INSTANT 컬럼 추가니까 낮에 바로 실행해도 되겠지?", 혹은 "gh-ost로 데이터 복사를 다 끝내고 마지막 Cut-over(테이블 교체)만 남았으니 안전하겠지?"라고 방심하는 순간, 예상치 못한 대형 장애가 터지곤 합니다.

모니터링 화면에 갑자기 Waiting for table metadata lock 상태의 세션이 수백 개씩 쌓이더니, 불과 몇 초 만에 애플리케이션의 DB 커넥션 풀(HikariCP 등)이 고갈되어 서비스 전체가 응답 불능(Hang)에 빠지는 현상입니다.

이번 글에서는 무중단 스키마 변경의 가장 마지막이자 치명적인 함정인 메타데이터 락(Metadata Lock, 이하 MDL)의 내부 동작 아키텍처를 파헤쳐 보고, 작업 전/중/후에 이를 사전에 감지하고 방어하는 실무 모니터링 쿼리 및 핵심 파라미터 설정 가이드를 정리해 보겠습니다.

1. Metadata Lock(MDL)의 내부 작동 원리와 도미노 장애 시나리오

InnoDB 스토리지 엔진이 레코드 단위의 락(Record Lock, Gap Lock)을 관리한다면, Metadata Lock(MDL)은 그보다 상위 계층인 MySQL Server 엔진(SQL Layer)에서 테이블의 구조(스키마 정의)를 보호하기 위해 관리하는 테이블 레벨의 락입니다.

트랜잭션이 특정 테이블을 조회하고 있는 도중에 다른 세션에서 그 테이블의 컬럼을 지우거나 이름을 바꿔버리면 데이터 정합성이 깨지기 때문에, MySQL은 모든 쿼리에 암묵적으로 MDL을 부여합니다.

① MDL 잠금 모드와 호환성 매트릭스

• 핵심 규칙 1: MDL은 단일 쿼리가 끝날 때 해제되는 것이 아니라, 트랜잭션이 완전히 종료(COMMIT 또는 ROLLBACK)될 때까지 유지됩니다.
• 핵심 규칙 2 (장애의 근본 원인): MDL 대기 큐(Wait Queue)는 단순한 선입선출(FIFO)이 아니라 잠금 요청의 우선순위(Priority)를 따릅니다. MDL_EXCLUSIVE는 MDL_SHARED_READ보다 우선순위가 높습니다.

② 공포의 도미노 현상 (Convoy Effect) 발생 과정

ALGORITHM=INSTANT로 컬럼을 하나 추가하는 데 왜 서버가 죽을까요? 시간순으로 재구성해 보겠습니다.

[정상 상태] ──> [Session A: Long Query/Tx 시작] ──> [Session B: ALTER/RENAME 실행] ──> [Session C, D, E...: 일반 SELECT 유입]
                 (MDL_SHARED_READ 점유 중)           (MDL_EXCLUSIVE 요청 -> 대기!)       (Session B에 막혀 도미노 대기 -> 장애!)

1. 빌런 트랜잭션 등장 (Session A): 배치 서버나 어드민 페이지에서 orders 테이블을 조회하는 무거운 슬로우 쿼리가 실행되었거나, 애플리케이션 코드에서 @Transactional을 열어두고 외부 API를 호출하느라 커밋을 안 하고 버티고 있습니다. 이 세션은 orders 테이블에 대해 MDL_SHARED_READ를 쥐고 놓지 않는 상태입니다.
2. 스키마 변경 시도 (Session B): DBA가 ALTER TABLE orders ADD COLUMN ... ALGORITHM=INSTANT를 실행(또는 gh-ost가 마지막 RENAME Cut-over를 시도)합니다. 이 작업을 완료하려면 단 1밀리초라도 MDL_EXCLUSIVE를 획득해야 합니다. 하지만 Session A가 Shared Lock을 쥐고 있으므로 Session B는 큐에서 대기(Waiting for table metadata lock)합니다.
3. [14:00:06 ~] 일반 트래픽 전면 차단 (Session C, D, E...): 일반 유저들이 주문 내역을 보기 위해 0.001초짜리 가벼운 SELECT * FROM orders WHERE id = 100을 날립니다. 평소 같으면 바로 통과했겠지만, 앞에서 우선순위가 높은 Session B(MDL_EXCLUSIVE)가 대기 중이기 때문에, 뒤따라오는 모든 단순 SELECT 쿼리들까지 Session B 뒤로 줄을 서며 전부 블로킹됩니다.
4. 서비스 장애: 초당 1,000개씩 들어오던 조회 쿼리가 단 5초 만에 5,000개의 대기 스레드를 만들고, DB의 max_connections가 꽉 차며 서비스가 완전히 다운됩니다.

2. 왜 gh-ost의 Cut-over는 일반 DDL보다 안전할까? (내부 알고리즘)

pt-osc나 일반 DDL은 마지막에 RENAME TABLE을 던져놓고 블로킹되면 사람이 수동으로 죽이기 전까지 마냥 기다리지만, gh-ost는 독자적인 Atomic Two-Step Cut-over 알고리즘을 통해 MDL 대기로 인한 장애를 방어하도록 설계되어 있습니다.

1. Session 1 (가짜 파수꾼): gh-ost가 먼저 원본 테이블에 LOCK TABLES orders WRITE를 겁니다. 이때 만약 롱 트랜잭션이 있다면 이 요청은 대기하지만, 뒤이어 들어오는 일반 SELECT를 막기 시작합니다.
2. Session 2 (교체 집행자): gh-ost의 다른 커넥션이 들어와 RENAME TABLE orders TO _orders_del, _orders_gho TO orders를 실행합니다. Session 1이 WRITE 락을 쥐고(또는 대기하고) 있으므로 Session 2도 대기 큐에 들어갑니다.
3. 타임아웃 감지 및 즉각 롤백: gh-ost는 이 과정에 매우 짧은 타임아웃(--cut-over-lock-timeout-seconds, 기본값 3초)을 겁니다. 만약 롱 쿼리 때문에 3초 안에 RENAME이 성공하지 못하면, gh-ost는 즉시 Session 1과 2의 커넥션을 스스로 끊어버립니다.
4. 자동 재시도: 락 요청이 사라졌으므로 막혀있던 일반 서비스 쿼리들은 즉시 정상 처리되어 장애로 번지지 않습니다. gh-ost는 다시 Binlog를 따라가며 잠시 후 Cut-over를 자동으로 재시도합니다.

주의: gh-ost가 아무리 안전하게 빠져나온다 해도, 재시도하는 3초 동안은 순간적인 쿼리 적체(Spike)가 발생할 수 있습니다. 따라서 어떤 도구를 쓰든 작업 직전 MDL을 유발할 수 있는 롱 트랜잭션을 찾아 제거하는 것이 정석입니다.

3. 실무 방어 가이드 1: 필수 안전핀 파라미터 설정 (lock_wait_timeout)

Native Online DDL이나 수동 RENAME, DROP을 실행할 때 도미노 장애를 막는 가장 확실하고 간단한 방법은 세션 레벨에서 lock_wait_timeout을 극단적으로 짧게 설정하는 것입니다.

많은 개발자가 헷갈려하는 두 가지 타임아웃 파라미터의 차이를 명확히 알아야 합니다.

보시다시피 lock_wait_timeout의 기본값은 무려 1년입니다. 즉, 설정 없이 ALTER TABLE을 날렸다가 롱 쿼리에 걸리면 1년 동안 취소되지 않고 모든 트래픽을 막아버립니다.

실무 DDL 실행 표준 패턴

운영 DB에서 DDL을 직접 실행할 때는 반드시 아래와 같이 세트로 묶어서 실행하는 습관을 들여야 합니다.

-- 1. 현재 세션의 MDL 대기 타임아웃을 3초로 제한 (3초 내 락 획득 실패 시 즉시 에러 반환 후 포기)
SET SESSION lock_wait_timeout = 3;

-- 2. InnoDB 레코드 락 타임아웃도 함께 축소
SET SESSION innodb_lock_wait_timeout = 3;

-- 3. 안전하게 Online DDL 실행
ALTER TABLE orders 
ADD COLUMN delivery_memo VARCHAR(255) DEFAULT NULL, 
ALGORITHM=INSTANT, LOCK=NONE;

이렇게 하면 3초 안에 MDL을 얻지 못할 경우 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 에러를 뱉고 DDL만 깔끔하게 실패합니다. 서비스 장애는 전혀 발생하지 않으며, 잠시 후 다시 시도하면 됩니다.

4. 실무 방어 가이드 2: 사전 감지 및 범인 색출 모니터링 쿼리

스키마 변경 작업을 시작하기 직전, 그리고 gh-ost의 Cut-over 플래그를 해제하기 직전에는 반드시 아래 쿼리들을 통해 대상 테이블을 물고 있는 미커밋 트랜잭션이나 롱 쿼리가 없는지 확인해야 합니다.

① [사전 점검] 현재 실행 중인 모든 장기 트랜잭션(Long Transaction) 조회

단순 SHOW PROCESSLIST만으로는 트랜잭션이 열린 채 아무 쿼리도 안 날리고 대기 중인 Sleep 상태의 좀비 트랜잭션을 찾을 수 없습니다. 반드시 information_schema.innodb_trx를 조회해야 합니다.

SELECT 
    trx.trx_id,
    trx.trx_mysql_thread_id AS thread_id,
    trx.trx_state,
    trx.trx_started,
    TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS duration_sec,
    p.USER,
    p.HOST,
    p.DB,
    p.COMMAND,
    p.STATE,
    trx.trx_query AS current_query
FROM information_schema.innodb_trx trx
JOIN information_schema.processlist p 
  ON trx.trx_mysql_thread_id = p.ID
WHERE TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) > 5
ORDER BY duration_sec DESC;

• 해석: COMMAND가 Sleep인데 duration_sec이 수십 초 이상인 세션이 있다면, 애플리케이션에서 트랜잭션을 열고 COMMIT을 안 한 채 외부 통신 등을 하고 있는 상태입니다. 이 세션이 대상 테이블을 한 번이라도 조회했다면 보이지 않는 MDL_SHARED_READ를 쥐고 있는 상태이므로 끝날 때까지 기다리거나 강제 종료(KILL)해야 합니다.

② [장애 대응] MDL 대기를 유발하고 있는 '진짜 범인(Blocker)' 스레드 정확히 찾아내기

만약 이미 Waiting for table metadata lock 현상이 발생했다면, 수백 개의 대기 세션 중 누가 맨 앞에서 락을 쥐고 안 놔주는지 1초 만에 찾아내어 KILL 해야 합니다. (MySQL 8.0 기준 performance_schema.metadata_locks 활용)

참고: MySQL 8.0에서는 기본 활성화되어 있으나, 만약 조회되지 않는다면 performance_schema.setup_instruments에서 wait/lock/metadata/sql/mdl 항목이 ENABLED='YES'인지 확인해야 합니다.

SELECT 
    locked_Scroll.OBJECT_SCHEMA AS db_name,
    locked_Scroll.OBJECT_NAME AS table_name,
    waiting_trx.PROCESSLIST_ID AS waiting_thread_id,
    waiting_trx.PROCESSLIST_INFO AS waiting_query,
    blocking_trx.PROCESSLIST_ID AS blocking_thread_id,
    blocking_trx.PROCESSLIST_COMMAND AS blocker_command,
    blocking_trx.PROCESSLIST_TIME AS blocker_idle_time_sec,
    blocking_trx.PROCESSLIST_INFO AS blocking_current_query,
    CONCAT('KILL ', blocking_trx.PROCESSLIST_ID, ';') AS kill_command
FROM performance_schema.metadata_locks waiting_lock
JOIN performance_schema.metadata_locks locked_Scroll 
  ON waiting_lock.OBJECT_TYPE = locked_Scroll.OBJECT_TYPE
  AND waiting_lock.OBJECT_SCHEMA = locked_Scroll.OBJECT_SCHEMA
  AND waiting_lock.OBJECT_NAME = locked_Scroll.OBJECT_NAME
JOIN performance_schema.threads waiting_trx 
  ON waiting_lock.OWNER_THREAD_ID = waiting_trx.THREAD_ID
JOIN performance_schema.threads blocking_trx 
  ON locked_Scroll.OWNER_THREAD_ID = blocking_trx.THREAD_ID
WHERE waiting_lock.LOCK_STATUS = 'PENDING'
  AND locked_Scroll.LOCK_STATUS = 'GRANTED'
  AND waiting_lock.OWNER_THREAD_ID != locked_Scroll.OWNER_THREAD_ID;

쿼리 결과 활용법:

1. 위 쿼리를 실행하면 현재 MDL을 기다리는 쿼리(waiting_query, 보통 ALTER나 RENAME)와 이를 가로막고 있는 범인 스레드 ID(blocking_thread_id), 그리고 즉시 실행할 수 있는 kill_command(KILL 12345;)가 출력됩니다.
2. 긴급 상황에서는 고민할 것 없이 두 가지 중 하나를 즉시 선택해 실행합니다:
   • 선택 A (권장): 대기 중인 DDL 세션(waiting_thread_id)을 KILL하여 스키마 변경을 취소하고 서비스 트래픽부터 즉시 정상화한다.
   • 선택 B: 만약 범인(blocking_thread_id)이 단순 배치 조회나 어드민 슬로우 쿼리임이 확실하다면, 범인 세션을 KILL하여 DDL이 즉시 완료되게 만든다.

5. 마무리: 무중단 스키마 변경 최종 안전 수칙 4계명

1. INSTANT도 MDL 앞에서는 평등하다: 아무리 0.1초 만에 끝나는 ALGORITHM=INSTANT라도 시작과 끝에는 MDL_EXCLUSIVE가 필요합니다. 방심하지 말고 작업 전 반드시 innodb_trx를 확인하세요.
2. 타임아웃 없는 수동 DDL은 자살 행위다: 직접 콘솔에서 DDL을 실행할 때는 무조건 SET SESSION lock_wait_timeout = 3;을 먼저 선언하여 도미노 블로킹을 원천 차단하세요.
3. gh-ost의 Cut-over는 눈으로 보며 수동으로 제어하라: 대용량 테이블 작업 시 --postpone-cut-over-flag-file 옵션을 사용해 복사는 밤새 돌려두고, 마지막 테이블 교체 순간에는 엔지니어가 직접 롱 쿼리가 없는 타이밍을 확인한 뒤 플래그를 풀어 마무리하세요.
4. 애플리케이션의 트랜잭션 범위를 다이어트하라: 근본적으로 MDL 장애가 잦은 팀은 애플리케이션 코드 내에 @Transactional 범위가 너무 넓거나(외부 API 호출 포함 등), 인덱스 없는 슬로우 쿼리가 방치되어 있는 경우가 많습니다. DB 작업 전후로 슬로우 쿼리 튜닝이 병행되어야 진정한 무중단 아키텍처가 완성됩니다.

앞선 글에서 수억 건의 대용량 테이블 데이터를 안전하게 삭제(Purge)하는 전략과, 그 과정에서 필연적으로 발생하는 테이블스페이스 단편화(DATA_FREE) 문제를 살펴보았습니다. 데이터를 다 지웠더라도 물리적인 디스크 공간을 OS로 반환받거나, 운영 중인 핵심 테이블에 새로운 컬럼 및 인덱스를 추가해야 할 때 DBA와 백엔드 엔지니어는 또 다른 거대한 벽에 부딪힙니다. 바로 '무중단 스키마 변경(Online Schema Change)'입니다.

트래픽이 24시간 쏟아지는 프로덕션 환경에서 수백 GB에 달하는 테이블에 무심코 ALTER TABLE을 실행하는 것은 서비스 전체에 사망 선고를 내리는 것과 같습니다.

이번 글에서는 MySQL이 자체 제공하는 Native Online DDL(INSTANT, INPLACE, COPY)의 내부 아키텍처와 한계를 짚어보고, 이를 극복하기 위해 실무에서 표준으로 자리 잡은 외부 도구인 Percona의 pt-online-schema-change(pt-osc)와 GitHub의 gh-ost의 작동 원리, 장단점, 그리고 상황별 최적의 선택 기준을 깊이 있게 파헤쳐 보겠습니다.

1. MySQL Native Online DDL의 진화와 한계

과거 MySQL 5.5 이하 시절에는 ALTER TABLE을 실행하면 무조건 원본 테이블에 잠금을 걸고 임시 테이블로 데이터를 통째로 복사하는 방식만 존재했습니다. 하지만 MySQL 5.6에서 Online DDL(INPLACE)이 도입되고, MySQL 8.0.12에서 INSTANT 알고리즘이 추가(8.0.29에서 한 단계 더 진화)되면서 자체 기능만으로도 상당 부분 무중단 작업이 가능해졌습니다.

작업 방식을 명시할 때는 아래와 같이 ALGORITHM과 LOCK 옵션을 지정하는 것이 안전합니다. (원하는 알고리즘으로 실행 불가능할 경우 즉시 에러를 발생시켜 장애를 막아줍니다.)

ALTER TABLE orders 
ADD COLUMN delivery_memo VARCHAR(255) DEFAULT NULL, 
ALGORITHM=INSTANT, LOCK=NONE;

① 3가지 알고리즘 내부 작동 원리 비교

② "Online"이라는 이름을 맹신하면 안 되는 4가지 이유 (Native DDL의 한계)

ALGORITHM=INSTANT가 지원되는 작업이라면 고민할 필요 없이 Native DDL을 쓰는 것이 정답입니다. 문제는 인덱스 추가, 컬럼 타입 변경, 디스크 공간 회수(OPTIMIZE) 등 테이블 리빌드(Rebuild)가 수반되는 INPLACE나 COPY 작업을 수행할 때입니다.

1) 시작과 끝의 배타적 메타데이터 락 (Exclusive MDL)

INPLACE 알고리즘이라도 작업을 시작하는 준비 단계(Prepare)와 마지막에 변경을 확정하는 단계(Commit)에는 아주 잠깐 Exclusive Metadata Lock(MDL)을 획득해야 합니다.

• 만약 해당 테이블에 30초 걸리는 무거운 조회 쿼리나 트랜잭션이 열려 있다면, DDL은 MDL을 얻기 위해 대기합니다.
• 진짜 재앙은 그다음입니다. Exclusive MDL은 우선순위가 높기 때문에, DDL이 대기하기 시작하는 순간 그 뒤로 들어오는 모든 단순 SELECT, INSERT 쿼리들까지 전부 MDL 대기 큐(Waiting for table metadata lock)에 갇혀버립니다. 순식간에 DB 커넥션 풀이 고갈되며 서비스 전체가 마비됩니다.

2) Online Alter Log 버퍼 초과 에러 (DB_ONLINE_LOG_TOO_BIG)

INPLACE 작업 도중 발생하는 애플리케이션의 DML(INSERT/UPDATE/DELETE)은 메모리/디스크 버퍼인 Row Log에 임시로 기록되었다가, 테이블 리빌드가 끝나는 마지막 순간에 일괄 반영됩니다.

• 이 버퍼의 최대 크기는 innodb_online_alter_log_max_size(기본값 128MB) 파라미터로 제한됩니다.
• 수백 GB 테이블을 리빌드하는 데 3시간이 걸리는데, 그사이 쓰기 트래픽이 몰려 변경 로그가 128MB를 초과하면? 3시간 동안 돌던 DDL이 막판에 실패하며 롤백됩니다. (작업 전 해당 파라미터를 1G~4G로 동적 상향 조정해야 합니다.)

3) 스로틀링(Throttling) 불가능과 I/O 폭주

Native DDL은 InnoDB 엔진 내부에서 최고 속도로 디스크를 읽고 씁니다. 서버의 CPU나 디스크 I/O가 100%를 치더라도 작업 속도를 늦추거나 잠시 멈추는(Pause) 제어 기능이 전혀 없습니다.

4) 치명적인 복제 지연 (Replication Lag)

가장 큰 문제입니다. Source(Master) DB에서 3시간 동안 Online DDL이 수행되었다고 해봅시다. MySQL의 복제 구조상 DDL은 Source에서 완전히 끝난 직후에야 Binlog에 기록되어 Replica(Slave)로 전달됩니다.

• 즉, Source에서는 3시간 동안 온라인으로 잘 처리되었더라도, 그 직후 Replica 서버에서도 똑같이 3시간 동안 해당 DDL이 실행됩니다.
• Replica에서 DDL이 도는 동안 복제 스레드(SQL Thread)가 블로킹되거나 극심한 부하를 겪으면서 수 시간 단위의 Replication Lag(복제 지연)이 발생하여 읽기 분산(Read Scale-out) 아키텍처가 무너지게 됩니다.

2. 외부 OSC(Online Schema Change) 양대 산맥: pt-osc vs gh-ost

Native DDL의 폭주하는 I/O와 복제 지연 문제를 해결하기 위해 등장한 것이 외부 OSC 도구들입니다. 핵심 아이디어는 동일합니다. "원본 테이블과 똑같은 모양의 유령(Ghost/Shadow) 테이블을 옆에 만들고, 데이터를 아주 작은 청크(Chunk) 단위로 살살 복사하면서, 동시에 실시간으로 발생하는 변경분만 동기화해 준 뒤 마지막에 테이블 이름만 바꿔치기(Swap)하자!"

하지만 그 '실시간 변경분을 어떻게 동기화하느냐'에 따라 트리거(Trigger) 방식의 pt-osc와 Binlog 파싱 방식의 gh-ost로 완전히 갈리게 됩니다.

① Percona pt-online-schema-change (pt-osc): 검증된 전통의 강자

pt-osc는 MySQL의 트리거(Trigger) 기능을 활용합니다.

내부 작동 아키텍처

1. Shadow 테이블 생성: 원본 테이블(orders)과 동일한 구조의 _orders_new 테이블을 생성하고, 여기에 원하는 ALTER 문을 적용합니다. (빈 테이블이므로 0.1초 만에 끝납니다.)
2. 3개의 트리거(Trigger) 설치: 원본 orders 테이블에 AFTER INSERT, AFTER UPDATE, AFTER DELETE 트리거를 생성합니다. 이제부터 원본 테이블에 들어오는 모든 DML은 트리거에 의해 _orders_new 테이블에도 REPLACE INTO / DELETE 형태로 똑같이 반영됩니다.
3. 청크 단위 데이터 복사 (Row Copy): PK 범위를 기준으로 1,000건 등 작은 단위로 끊어서 INSERT IGNORE INTO _orders_new SELECT ... FROM orders LOCK IN SHARE MODE를 수행합니다. 이때 Replica 서버의 지연 상태를 계속 체크하며 속도를 조절합니다.
4. 테이블 교체 (Atomic Rename): 복사가 100% 완료되면 RENAME TABLE orders TO _orders_old, _orders_new TO orders를 실행하여 원자적으로 테이블을 바꿔치기하고 _orders_old를 삭제합니다.

pt-osc의 치명적인 단점 (왜 트리거가 위험한가?)

• 트랜잭션 결합과 성능 저하: 트리거는 원본 쿼리와 동일한 트랜잭션 공간에서 실행됩니다. 즉, 사용자가 orders에 INSERT를 한 번 할 때마다 _orders_new에도 쓰기 작업이 동기적으로 일어나므로, 작업 시간 내내 모든 쓰기 쿼리의 응답 속도(Latency)가 거의 2배로 느려지고 락 경합이 심화됩니다.
• 중단 및 속도 제어의 한계: 만약 작업 도중 DB 부하가 너무 높아져서 pt-osc 프로세스를 강제로 죽이더라도, 원본 테이블에 걸려있는 트리거는 그대로 남아 계속 동작합니다. 트리거를 지우려면 다시 Exclusive MDL을 걸고 DROP TRIGGER를 해야 하는데, 이미 부하가 꽉 찬 상태에서는 이마저도 또 다른 장애의 불씨가 됩니다.

② GitHub gh-ost (GitHub's Online Schema Transmogrifier): 현대적 표준

GitHub 엔지니어링 팀은 pt-osc의 트리거가 유발하는 프로덕션 부하를 해결하기 위해 트리거를 전혀 사용하지 않는(Triggerless) gh-ost를 개발했습니다.

내부 작동 아키텍처

1. Ghost 테이블 및 로그 테이블 생성: 변경할 스키마가 적용된 _orders_gho 테이블과, 작업 진행 상태 및 하트비트를 기록할 _orders_ghc 테이블을 생성합니다.
2. 가짜 Replica로 위장 (Binlog Streamer): gh-ost 프로세스 자체가 대상 DB(또는 Replica DB)에 붙어 마치 자신이 또 하나의 Replica 서버인 것처럼 Binlog 스트림을 실시간으로 읽어 들입니다. (따라서 반드시 binlog_format=ROW여야 합니다.)
3. 비동기 이벤트 적용 & Row Copy:
   • 백그라운드에서 PK 단위로 원본 데이터를 _orders_gho로 복사(Row Copy)합니다.
   • 동시에 원본 테이블에 발생한 INSERT/UPDATE/DELETE 내역을 Binlog에서 파싱하여 비동기적으로 _orders_gho에 반영합니다.
4. 안전한 Cut-over (교체): 복사가 모두 끝나면 독자적인 2단계 잠금 및 원자적 RENAME 알고리즘을 통해 안전하게 테이블을 교체합니다.

gh-ost가 압도적으로 우월한 이유

• 원본 트랜잭션과 완벽한 분리 (Decoupling): 트리거가 없으므로 애플리케이션이 수행하는 INSERT/UPDATE 트랜잭션에 추가적인 오버헤드나 락 경합을 전혀 주지 않습니다.
• 진정한 의미의 일시 정지(Throttling): DB에 부하가 감지되어 gh-ost를 일시 정지(Throttle)시키면, Row Copy뿐만 아니라 Binlog 반영까지 즉시 멈춥니다. 그 순간 DB에는 아무런 부하도 가해지지 않는 순정 상태로 돌아갑니다. 부하가 풀리면 멈췄던 Binlog 포지션부터 다시 이어서 읽으면 됩니다.
• 동적 제어 (Dynamic Control): 작업이 도는 도중에도 유닉스 소켓(Socket) 통신을 통해 실시간으로 청크 사이즈(chunk-size)나 최대 허용 지연 시간(max-lag-millis)을 재시작 없이 즉석에서 변경할 수 있습니다.

3. 한눈에 보는 3대 방식 완벽 비교표

실무 아키텍트 관점에서 세 가지 도구의 특성을 비교 정리한 표입니다.

4. 실무 마이그레이션 명령어 레시피 및 꿀팁

실제 운영 환경에서 테이블 리빌드(공간 회수)나 인덱스/컬럼 추가 시 바로 복사해서 수정해 쓸 수 있는 실전 커맨드입니다.

① pt-osc 실무 실행 템플릿 (공간 회수 / OPTIMIZE 대체 예시)

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=prod_db,t=service_access_log,h=10.0.0.10,u=admin,p='password' \
  --chunk-size=1000 \
  --chunk-time=0.5 \
  --max-lag=2 \
  --check-interval=1 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --lock-wait-timeout=5 \
  --no-drop-old-table \
  --execute

• --alter "ENGINE=InnoDB": 스키마 변경 없이 테이블만 새로 복사해서 단편화된 디스크 공간을 회수(Rebuild)할 때 사용하는 기법입니다.
• --max-load / --critical-load: DB의 활성 스레드(Threads_running)가 50개를 넘으면 복사를 일시 중지하고, 100개를 넘으면 장애 예방을 위해 작업을 즉시 안전하게 취소(Abort)합니다.
• --no-drop-old-table: (핵심 팁) 수백 GB짜리 구버전 테이블(_old)을 마지막에 DROP TABLE 할 때도 파일 시스템 부하가 걸릴 수 있습니다. 일단 삭제하지 않고 남겨둔 뒤, 새벽 시간대에 안전하게 지우거나 만일의 롤백 사태에 대비하는 것이 좋습니다.

② gh-ost 실무 실행 템플릿 (컬럼 및 인덱스 추가 예시)

gh-ost는 기본적으로 Replica에 연결하여 Binlog를 읽고 테스트를 수행할 수 있지만, AWS RDS나 Aurora 환경 등에서는 Source(Master)에 직접 연결하여 실행하는 --allow-on-master 모드를 많이 사용합니다.

gh-ost \
  --host="10.0.0.10" \
  --user="admin" \
  --password="password" \
  --database="prod_db" \
  --table="orders" \
  --alter="ADD COLUMN delivery_memo VARCHAR(255) NULL, ADD INDEX idx_created_at (created_at)" \
  --allow-on-master \
  --aliyun-rds=false \
  --assume-rbr \
  --chunk-size=1000 \
  --max-lag-millis=1500 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --postpone-cut-over-flag-file="/tmp/ghost.postpone.flag" \
  --serve-socket-file="/tmp/ghost.orders.sock" \
  --execute

gh-ost 운영 꿀팁 2가지:

1. --postpone-cut-over-flag-file 활용: 이 옵션을 주면 데이터 복사가 100% 끝났더라도 마지막 테이블 교체(Cut-over, RENAME)를 자동으로 하지 않고 대기합니다. 계속 Binlog만 따라가며 동기화 상태를 유지하고 있다가, 담당 엔지니어가 모니터링 화면을 켜놓고 /tmp/ghost.postpone.flag 파일을 지우는(rm) 바로 그 순간에 테이블을 교체합니다. 새벽에 복사를 걸어두고 출근해서 눈으로 보며 안전하게 마무리할 수 있는 최고의 기능입니다.
2. 작업 중 실시간 속도 조절: 작업 도중 트래픽이 몰려 속도를 줄이고 싶다면, 프로세스를 끌 필요 없이 터미널에서 아래 명령어로 즉시 청크 사이즈를 줄일 수 있습니다.

   # 실행 중인 gh-ost의 청크 사이즈를 1000에서 200으로 즉시 축소
   echo "chunk-size=200" | nc -U /tmp/ghost.orders.sock
   
   # 현재 진행률 및 예상 남은 시간 상태 확인
   echo "status" | nc -U /tmp/ghost.orders.sock
   

5. 결론: 상황별 최적의 의사결정 가이드

현업에서 스키마 변경이나 테이블 최적화 요청이 들어왔을 때, 아래의 순서대로 판단하시면 장애 없는 완벽한 작업이 가능합니다.

1. MySQL 8.0 이상이며 단순 컬럼 추가/삭제인가?
   • 무조건 Native Online DDL (ALGORITHM=INSTANT)을 사용하세요. 테이블 크기가 10TB라도 0.1초 만에 부하 없이 끝납니다.
2. 테이블 크기가 10GB 미만이고 트래픽이 적은 새벽 시간대인가?
   • Native Online DDL (ALGORITHM=INPLACE, LOCK=NONE)로도 충분히 빠르고 안전하게 처리 가능합니다. 단, 실행 전 반드시 processlist를 확인해 롱 쿼리(장기 실행 트랜잭션)가 없는지 체크하세요.
3. 수천만 건 이상(수십~수백 GB)의 핵심 테이블에 인덱스를 추가하거나, 컬럼 타입을 바꾸거나, 단편화된 공간을 회수(OPTIMIZE)해야 하는가?
   • 절대 Native DDL이나 OPTIMIZE TABLE을 직접 날리지 마세요.
   • 1순위 선택은 gh-ost입니다. 트리거 오버헤드가 없고 완벽한 스로틀링과 Cut-over 시점 제어가 가능하여 대규모 트래픽 환경에서 가장 안전합니다.
   • 만약 해당 테이블에 외래키(Foreign Key)가 걸려 있거나 Binlog 포맷이 STATEMENT라서 바꿀 수 없는 환경이라면, 그때 2순위로 pt-online-schema-change를 선택하시면 됩니다.