MySQL 온라인 DDL, INSTANT부터 gh-ost까지 고르는 법
정산 내역 테이블에 payout_channel 컬럼 하나를 붙이던 날, 배포 시간을 새벽 두 시로 잡았다. 행이 4천만 건쯤 되는 테이블이었다. MySQL에서 큰 테이블에 컬럼을 더하면 테이블을 통째로 복사하느라 한참 걸린다고 알고 있었다. 커피를 타 놓고 ALTER 문을 실행했는데 0.03초 만에 끝났다. 잘못 친 줄 알고 SHOW CREATE TABLE을 두 번이나 열어 봤다. 컬럼은 제대로 붙어 있었다.
서버는 MySQL 8.0이었고, 그 ALTER는 ALGORITHM=INSTANT로 돌았다. 데이터는 손대지 않고 데이터 사전의 메타데이터만 고치는 방식이다. 그 뒤로 한동안은 MySQL DDL이 다 빠른 줄 알았다. 몇 달 뒤 VARCHAR 길이 하나 늘리는 문장 때문에 API 전체가 멈추고 나서야 생각이 바뀌었다.
온라인 DDL에서 고를 수 있는 건 ALGORITHM 세 가지와 외부 도구 두 개다. 내가 부딪힌 순서대로 적는다.
INSTANT, INPLACE, COPY는 무엇이 다른가
ALTER TABLE은 ALGORITHM 절로 처리 방식을 고른다. 셋은 데이터를 얼마나 건드리느냐에서 갈린다.
| 방식 | 하는 일 | 작업 중 쓰기 |
|---|---|---|
| INSTANT | 데이터 사전의 메타데이터만 바꾼다 | 허용 |
| INPLACE | 엔진 안에서 처리하고, 작업에 따라 테이블을 다시 짓는다 | 대부분 허용 |
| COPY | 새 테이블을 만들어 행을 하나씩 옮긴다 | 막힘 |
컬럼 추가는 MySQL 8.0.12부터 INSTANT가 기본이고, 그 전에는 INPLACE였다. 같은 문장이 버전에 따라 0.03초에 끝나기도 하고 몇 시간이 걸리기도 하는 이유다. PostgreSQL 쪽 갈림길은 PostgreSQL ALTER TABLE, 다시 쓰는 DDL과 안 쓰는 DDL에 따로 적어 두었다.
INSTANT 컬럼 추가가 되는 경우와 안 되는 경우
INSTANT는 조건이 맞을 때만 된다. 공식 문서의 제약 가운데 내가 실제로 부딪힌 것만 추리면 이렇다.
- 8.0.29 이전에는 맨 끝에만 추가할 수 있다.
AFTER로 중간에 끼우려면 8.0.29 이상이어야 한다. ROW_FORMAT=COMPRESSED테이블,FULLTEXT인덱스가 있는 테이블, 임시 테이블에는 쓸 수 없다.- INSTANT를 지원하지 않는 다른 변경과 한 문장으로 묶으면 안 된다.
- 테이블마다 행 버전은 64개까지다. INSTANT로 컬럼을 더하거나 뺄 때마다 하나씩 늘고, 다 차면
ERROR 4092가 난다.
FULLTEXT 제약은 직접 겪었다. 게시글 테이블에 컬럼을 붙이는데 ALTER가 몇 분째 끝나지 않았다. 본문 검색용 FULLTEXT 인덱스 때문이었다. ALGORITHM을 적지 않으면 MySQL은 되는 방식 중에 알아서 고르고, INSTANT가 안 되면 조용히 다음 방식으로 넘어간다. 그 뒤로는 반드시 방식을 적는다. 안 되면 바로 에러를 내고 멈추기 때문이다.
ALTER TABLE posts
ADD COLUMN pinned_at DATETIME NULL,
ALGORITHM=INSTANT;
-- 행 버전이 얼마나 쌓였는지 확인 (8.0.29 이상)
SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'shop/posts';버전도 하나 짚어 둔다. Percona XtraBackup 문서에 따르면 8.0.29는 redo 로그 형식 결함 때문에 INSTANT로 컬럼을 추가하거나 삭제한 테이블의 백업이 안전하지 않다. 8.0.30 이상으로 올린 뒤 OPTIMIZE TABLE 등으로 테이블을 다시 지으라고 안내한다. 8.0.29를 쓰고 있다면 INSTANT 전에 백업 도구부터 확인하는 편이 낫다.
INPLACE인데도 서비스가 멈춘 날
가맹점 메모 컬럼을 VARCHAR(100)에서 VARCHAR(150)으로 늘리는 작업이었다. utf8mb4에서는 둘 다 길이 바이트가 2바이트라 문서상 INPLACE로 된다. 오후 네 시에 아무렇지 않게 실행했는데, 1분쯤 지나자 응답 시간 알림이 쏟아졌고 커넥션 풀이 바닥났다.
SHOW PROCESSLIST를 열어 보니 ALTER가 Waiting for table metadata lock 상태로 서 있었다. 그 뒤로 같은 테이블을 읽는 SELECT 수십 개가 같은 상태로 줄을 서 있었다. ALTER를 KILL하자 몇 초 만에 모두 풀렸다. 등에 땀이 났다.
원인은 메타데이터 락(MDL)이었다. 온라인 DDL도 마지막에 새 정의를 반영하려면 배타 MDL을 잡아야 한다. 그 테이블을 건드린 트랜잭션이 공유 MDL을 쥔 채 끝나지 않으면 ALTER는 기다린다. 문제는 그다음이다. 대기 중인 배타 락 요청이 뒤에 들어오는 쿼리까지 막는다. 그날 공유 MDL을 쥔 건 누군가 DB 클라이언트로 SELECT를 돌리고 커밋하지 않은 채 열어 둔 세션이었다. INSTANT도 이 락을 잡으니 예외가 아니다.
그 뒤로는 DDL 전에 오래 열린 트랜잭션부터 보고, 세션의 락 대기 시간을 줄인다. lock_wait_timeout 기본값은 31536000초, 1년이라 그대로 두면 ALTER가 하염없이 줄을 막고 선다.
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;
SET SESSION lock_wait_timeout = 5;
ALTER TABLE merchants
MODIFY memo VARCHAR(150),
ALGORITHM=INPLACE, LOCK=NONE;5초 안에 락을 못 잡으면 에러로 끝난다. 다시 돌리면 그만이고, 서비스를 세우는 것보다 훨씬 싸다.
COPY밖에 안 될 때: gh-ost
컬럼 타입 변경은 COPY로만 된다. 주문 테이블의 INT 기본 키가 상한에 다가가 BIGINT로 바꿔야 했을 때가 그랬다. 복사 내내 쓰기를 막는 COPY는 1억 건이 넘는 테이블에서 고를 수 없었다.
그래서 gh-ost를 썼다. 같은 구조의 고스트 테이블에 ALTER를 적용하고, 기존 행을 조금씩 복사하면서 그사이 바뀐 내용은 바이너리 로그를 읽어 반영한다. 트리거를 쓰지 않아 원본 쓰기 부담이 적고, 부하가 오르면 스로틀로 멈춘다. 대신 바이너리 로그가 ROW 형식이어야 하고, 두 테이블이 같은 기본 키나 유니크 키를 공유해야 하며, 외래 키와 트리거가 있는 테이블은 다루지 못한다.
gh-ost \
--host=127.0.0.1 --user=migrator --password='...' \
--database=shop --table=orders \
--alter="MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" \
--chunk-size=1000 \
--max-load=Threads_running=30 \
--critical-load=Threads_running=80 \
--postpone-cut-over-flag-file=/tmp/ghost.postpone \
--execute복사는 이틀 가까이 걸렸다. 가장 쓸모 있던 건 --postpone-cut-over-flag-file이다. 이 파일이 있는 동안 gh-ost는 복사를 마치고도 테이블을 맞바꾸지 않는다. 복사는 일요일 새벽에 끝났고, 맞바꾸기는 월요일 오전 열 시 팀이 모두 자리에 있을 때 파일을 지워 진행했다. 몇 초 만에 끝났고 그제야 숨을 내쉬었다.
외래 키가 걸린 테이블: pt-online-schema-change
같은 해 주문 상품 테이블도 손봐야 했다. 주문 테이블을 가리키는 외래 키가 있었고, gh-ost 문서는 외래 키를 지원하지 않는다고 분명히 적는다. 그래서 pt-online-schema-change를 꺼냈다.
이 도구도 빈 복사본을 만들어 행을 옮기지만, 변경분은 원본에 건 트리거로 따라잡는다. 다 옮기면 RENAME TABLE로 두 테이블을 한 번에 맞바꾼다. DELETE 트리거 때문에 기본 키나 유니크 인덱스가 필요하다. 외래 키는 --alter-foreign-keys-method로 처리 방식을 고르는데, 자식 테이블이 아주 크지 않으면 rebuild_constraints가 무난하다. 트리거가 쓰기마다 한 번씩 더 일하니 쓰기가 많은 시간대는 피했다.
pt-online-schema-change \
--alter "ADD INDEX idx_product_created (product_id, created_at)" \
--alter-foreign-keys-method=rebuild_constraints \
--max-load Threads_running=25 \
--critical-load Threads_running=50 \
--dry-run \
D=shop,t=order_items--dry-run은 새 테이블만 만들어 변경을 검사하고 트리거 생성이나 복사는 하지 않는다. 출력이 깨끗하면 --execute로 바꿔 다시 돌린다.
지금 내가 고르는 순서
요즘은 이렇게 고른다. ALGORITHM=INSTANT를 적어 먼저 시도하고, 에러가 나면 INPLACE, LOCK=NONE로 내려간다. 어느 쪽이든 그 전에 innodb_trx로 오래 열린 트랜잭션을 보고 lock_wait_timeout을 몇 초로 줄여 둔다. COPY만 되거나, 테이블을 다시 짓느라 오래 걸리는 큰 테이블이면 gh-ost를 쓴다. 외래 키나 기존 트리거 때문에 gh-ost가 안 되면 pt-online-schema-change로 간다.
돌아보면 나를 곤란하게 만든 건 늘 느린 복사가 아니라 락을 기다리는 몇 분이었다. 4천만 건짜리 테이블에 컬럼을 붙이는 데는 0.03초가 걸렸고, 150자로 늘리는 문장이 서비스를 세웠다. 그래서 이제는 DDL을 돌리기 전에 이 테이블을 누가 붙잡고 있는지부터 확인한다.