MySQL의 무중단 스키마 변경

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

라이브 트래픽이 있는 테이블에 ALTER TABLE을 실행하면 질문이 하나 생깁니다: 이 문이 실행되는 동안 애플리케이션을 차단할까? 이 가이드는 실제 테이블에서, 동시 연결이 내내 지켜보는 가운데, 그것을 직접 측정합니다. MySQL의 스키마 마이그레이션은 마이그레이션 파일을 구성하고 버전 관리하는 방법을 다룹니다. 이 가이드는 그런 마이그레이션 파일 중 하나가 실행되는 동안 라이브 테이블이 실제로 무엇을 하는지 다룹니다.

테스트 설정

이 가이드의 모든 숫자는 같은 테이블에서 나왔습니다: 로컬 VillageSQL 서버에 3,276,802개 행(데이터 168.7 MB)을 적재한 orders 테이블입니다.

CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount_cents INT NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

행은 숫자 테이블 교차 조인으로 생성한 다음, 자기 자신에서 INSERT ... SELECT로 몇 번 두 배로 늘려 수백만 행에 도달했습니다. 정확한 크기는 아래 결론에 중요하지 않지만, 전체 테이블 재구축이 1초의 일부보다 오래 걸릴 만큼 테이블이 커야 합니다. 그렇지 않으면 그것과 동시에 실행되는 것을 아무것도 관찰할 수 없습니다.

온라인 ALTER는 트래픽을 차단하지 않습니다

위의 327만 행 테이블에 ALGORITHM=INPLACE, LOCK=NONE으로 보조 인덱스를 추가하면:

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

처음부터 끝까지 1.003초가 걸렸습니다. 실행 중에 두 번째 연결의 SHOW PROCESSLIST는 그것을 실제로 진행 중인 작업으로 보여주었습니다:

Id   User  Host       db        Command  Time  State           Info
325  root  localhost  zdt_demo  Query    0     altering table  ALTER TABLE orders ADD INDEX idx_customer_id (customer_id), ALGORITHM=INPLACE, LOCK=NONE

information_schema.INNODB_TRX는 같은 테이블에 대한 별도의 ADD INDEX에서 같은 문을 실행 중인 트랜잭션으로 보여줍니다:

trx_id: 6472
trx_state: RUNNING
trx_query: ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE

위의 ALTER가 아직 altering table 상태인 동안 열어서 실행한 세 번째 연결에서, 같은 테이블에 대한 INSERT와 SELECT가 모두 즉시 완료되었습니다:

INSERT INTO orders (customer_id, status, amount_cents, created_at, priority, priority2)
VALUES (999999, 'pending', 500, NOW(), 0, 0);
-- 0.009s
SELECT COUNT(*) FROM orders WHERE status='paid';
-- 0.271s

어느 문도 ALTER가 끝나기를 기다리지 않았습니다. 이것이 LOCK=NONE의 핵심입니다: 테이블 재구축은 InnoDB의 온라인 DDL 로그로 동시 변경을 기록하면서 백그라운드에서 일어나고, 테이블에 대한 DML은 재구축이 따라잡아 교체될 때까지 원래의 데이터 구조에 대해 진행됩니다.

ALGORITHM=INSTANT 대 ALGORITHM=INPLACE

모든 온라인 작업의 비용이 같지는 않습니다. ALGORITHM=INSTANT로 테이블 끝에 열을 추가하는 것은 테이블 메타데이터만 바꿉니다. 행을 하나도 읽거나 다시 쓰지 않습니다:

ALTER TABLE orders ADD COLUMN priority TINYINT NOT NULL DEFAULT 0, ALGORITHM=INSTANT;
-- 0.011s

같은 형태의 변경, 즉 기본값이 있는 열 추가를 대신 ALGORITHM=INPLACE로 강제하면:

ALTER TABLE orders ADD COLUMN priority2 TINYINT NOT NULL DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;
-- 0.244s

이 327만 행 테이블에서, 기능적으로 같은 열 추가에 대해 INSTANT가 INPLACE보다 대략 20배 빨랐습니다. INPLACE는 여전히 테이블의 데이터 딕셔너리 항목을 건드리고 동시 DML과 조정해야 합니다. INSTANT는 둘 다 하지 않습니다. 차이는 테이블 크기와 함께 커집니다: INSTANT는 행 수와 관계없이 일정하지만, INPLACE는 행 수에 비례합니다. INSTANT를 모든 작업에 쓸 수 있는 것은 아닙니다. BLOB/TEXT 기본값이 있는 열을 추가하거나 기존 열의 유형을 바꾸는 것은 INPLACE나 COPY로 돌아갑니다. 테이블 중간을 포함해 어느 위치에든 열을 추가하는 것도 INSTANT를 사용합니다(MySQL 8.0.29+).

온라인 DDL이 도울 수 없는 경우: 열 유형 변경

모든 ALTER TABLE이 잠금을 피할 수 있는 것은 아닙니다. 열의 데이터 유형을 바꾸려면 MySQL이 모든 행을 다시 써야 하고, INPLACE는 그것을 할 수 없습니다:

ALTER TABLE orders MODIFY COLUMN amount_cents BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000) at line 2: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

같은 변경에 ALGORITHM=INSTANT를 요청하면 다른 이유로 같은 방식으로 실패합니다:

ERROR 1846 (0A000) at line 2: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

MySQL이 이 문에 받아들이는 유일한 알고리즘은 COPY이며, 테이블 전체를 재구축하고 그동안 배타적 잠금을 잡습니다. 같은 테이블에 대해 실행하고 재구축 중에 두 번째 연결에서 행을 삽입해 보면 차이가 직접 드러납니다:

ALTER TABLE orders MODIFY COLUMN amount_cents BIGINT NOT NULL, ALGORITHM=COPY;
-- 6.776s

ALTER가 시작하고 0.3초 뒤에 보낸 동시 INSERT는 ALTER가 거의 끝날 때까지 돌아오지 않았습니다:

INSERT INTO orders (customer_id, status, amount_cents, created_at, priority, priority2)
VALUES (1, 'pending', 1, NOW(), 0, 0);
-- 6.466s

이 동안의 SHOW PROCESSLIST는 ALTER를 copy to tmp table 상태로, 그 옆의 차단된 INSERT를 Waiting for table metadata lock 상태로 보여주었습니다. 그 상태가 진단입니다: 어느 문이 멈춰 있는지 짚어 주고, 느리게 실행되는 것이 아니라 잠금 뒤에 줄 서 있다고 알려줍니다. 이 같은 문에 LOCK=NONE으로 빨리 실패하게 해 보는 것이 바로, 프로덕션 테이블에 실행하기 전에, 다시 쓰기가 걸리는 시간만큼 쓰기 작업을 차단할 것임을 알아내는 방법입니다.

외래 키와 온라인 DDL

외래 키는 ALGORITHM과 LOCK과 독립적인 제한을 더합니다. FOREIGN KEY (order_id) REFERENCES orders(id)가 있는 자식 테이블 order_items에서, 평범한 ADD COLUMN은 외래 키가 있어도 여전히 ALGORITHM=INSTANT로 밀리초 안에 완료됩니다. 하지만 2가지 작업은 알고리즘과 관계없이 바로 실패합니다: 외래 키가 의존하는 부모의 기본 키 삭제:

ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, customer_id), ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1553 (HY000) at line 2: Cannot drop index 'PRIMARY': needed in a foreign key constraint

참조하는 부모 열과 독립적으로 자식 열의 유형 변경:

ALTER TABLE order_items MODIFY COLUMN order_id INT NOT NULL, ALGORITHM=COPY;

ERROR 3780 (HY000) at line 2: Referencing column 'order_id' and referenced column 'id' in foreign key constraint 'fk_order' are incompatible.

두 오류 모두 MySQL이 알고리즘을 고르기 전인 구문 분석과 검증 시점에 일어납니다. 마이그레이션이 외래 키에 관련된 열을 바꿔야 한다면, 외래 키를 같은 마이그레이션의 일부로 삭제하고 다시 만들어야 하며, 부모와 자식 열의 유형은 모든 단계에서 양쪽이 호환되어야 합니다.

온라인 DDL이 안전하게 할 수 없는 변경을 위한 gh-ost

위의 MODIFY COLUMN ... ALGORITHM=COPY 경우가 온라인 DDL이 도울 수 없는 경우입니다: 전체 행 다시 쓰기가 필요하지만, 라이브 테이블에서 그동안 잠금을 잡고 싶지는 않습니다. gh-ost는 새 스키마로 테이블의 복사본(“고스트” 테이블)을 만든 다음, 백그라운드에서 기존 행을 거기에 복사하면서 복사 중에 원래 테이블에 이루어진 모든 쓰기를 트리거 대신 바이너리 로그를 스트리밍해 재생하는 방식으로 이것을 처리합니다. 짧은 원자적 이름 바꾸기로 전환하며 끝납니다. gh-ost는 다른 테이블이 외래 키로 참조하는 테이블의 마이그레이션을 거부합니다: 앞 섹션의 order_items 테이블에는 FOREIGN KEY (order_id) REFERENCES orders(id)가 있고, 그 제약 조건이 그대로 있는 채로 orders에 gh-ost를 실행하면 FATAL found 1 parent-side foreign keys on ... orders. Parent-side foreign keys are not supported. Bailing out으로 즉시 실패합니다. 다른 테이블이 참조하는 테이블에 gh-ost를 실행하기 전에 그 외래 키를 먼저 삭제하세요(또는 --discard-foreign-keys를 전달하세요. 이 옵션은 외래 키를 대신 삭제하고 다시 만들지 않습니다). 바로 이런 종류의 변경을 위해, order_items의 외래 키를 삭제한 채로, 같은 3,276,802행 테이블에 대해 이것을 설치하고 실행했습니다:

# macOS
brew install gh-ost

# Linux (Debian/Ubuntu)
curl -LO https://github.com/github/gh-ost/releases/latest/download/gh-ost.amd64.deb
sudo dpkg -i gh-ost.amd64.deb

gh-ost \
  --user=root --password= \
  --host=127.0.0.1 --port=3306 \
  --database=zdt_demo --table=orders \
  --alter="MODIFY COLUMN customer_id BIGINT NOT NULL" \
  --allow-on-master \
  --initially-drop-ghost-table \
  --exact-rowcount \
  --chunk-size=2000 \
  --default-retries=5 \
  --execute

실제 비밀번호가 있다면, 명령줄의 --password 대신 -conf 파일로 전달하세요. 명령줄 비밀번호는 호스트의 다른 모든 사용자에게 ps로 보이고 셸 기록에 남습니다. --execute가 없으면 gh-ost는 드라이런 모드로 실행됩니다: 연결하고, binlog를 스트리밍하고, 고스트 테이블에 사용할 CREATE TABLE 문을 출력하지만, 아무것도 바꾸지 않습니다. --execute를 주면 전체 마이그레이션을 13.112초에 실행했고, 진행하면서 진행 상황을 출력했습니다:

Copy: 596000/3276802 18.2%; Applied: 0; Backlog: 1/1000; Time: 3s(total), 3s(copy); ...; ETA: 8s
Copy: 1762000/3276802 53.8%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 7s(copy); ...; ETA: 5s
Copy: 3276802/3276802 100.0%; Applied: 0; Backlog: 0/1000; Time: 12s(total), 12s(copy); ...; ETA: due

같은 마이그레이션의 두 번째 실행에서 3초가 지났을 때, 라이브 orders 테이블에 대한 동시 INSERT는 0.007초에 돌아왔고, gh-ost 자신의 진행 줄이 그 쓰기를 받아 재생했음을 확인해 주었습니다(Applied: 1):

Copy: 1812000/3276803 55.3%; Applied: 1; Backlog: 1/1000; Time: 7s(total), 7s(copy); ...

전환 후 SHOW CREATE TABLE orders는 customer_id를 bigint로 보여주었고, 행 수는 정확히 일치했으며(3,276,803, 원래 수에 마이그레이션 중 삽입된 행 1개를 더한 값), 마이그레이션 중에 삽입된 행은 올바른 값으로 존재했습니다. gh-ost는 원래 테이블을 삭제하지 않고 _orders_del로 이름을 바꾸었으므로, 전환을 확인한 뒤 비교하거나 수동으로 삭제할 수 있도록 마이그레이션 전 테이블이 여전히 남아 있습니다. 이것은 binlog_format=ROW와 log_bin=ON이 이미 설정된 독립 실행형 서버에 대해 실행했습니다. gh-ost는 변경을 스트리밍하기 위해 이것을 요구하며, 문 기반 복제에서는 동작하지 않습니다. 또한 테이블에 기본 키나 고유 키가 있어야 합니다. 둘 다 흔한 기본값이라서 여기서는 추가 설정이 필요 없었지만, 확인하지 않은 테이블에 gh-ost를 믿고 쓰기 전에 둘 다 확인하세요.

자주 묻는 질문

ALTER TABLE이 테이블을 차단할지 어떻게 미리 알 수 있나요?

실행하기 전에 문에 ALGORITHM=INPLACE, LOCK=NONE(또는 LOCK=NONE만)을 추가하세요. MySQL이 차단 없이 변경할 수 있으면 실행됩니다. 할 수 없으면, 실행하고 차단하는 대신 즉시 오류를 반환합니다. 어느 쪽이든, 추측하거나 소스 코드를 읽지 않고 확정적인 답을 얻습니다.

열 유형 변경에서 ALGORITHM=INSTANT가 왜 실패했나요?

INSTANT는 MySQL이 변경을 메타데이터 업데이트만으로 표현할 수 있을 때만 동작합니다. 열의 저장 유형을 바꾸면 기존 모든 행의 디스크 표현이 새 유형에 맞지 않으므로, 행을 읽고 다시 써야 합니다. 그것이 INSTANT를 배제하고, 유형 변경에서는 특히 INPLACE도 배제합니다. COPY만 할 수 있습니다.

gh-ost는 복제 설정이 필요한가요?

아니요. 마이그레이션하는 서버의 바이너리 로그를 읽는데, binlog_format=ROW인 서버라면 복제본이 있든 없든 이미 그것을 만들어 냅니다. gh-ost는 복제본에 대해 실행하고 소스에서 전환하는 것도 지원하며, 매우 크거나 매우 바쁜 테이블에는 그쪽이 더 안전한 패턴이지만, 그것은 배포 선택이지 동작시키기 위한 요구 사항은 아닙니다.

gh-ost가 끝난 뒤 옛 테이블은 어떻게 되나요?

기본적으로 gh-ost는 원래 테이블을 삭제하는 대신 접미사(위에서 기록한 실행에서는 _del)를 붙여 이름을 바꿉니다. 직접 삭제할 때까지 스키마에 사용되지 않은 채 남아 있습니다.

문제 해결

문제	해결 방법
`ERROR 1846: ALGORITHM=INPLACE is not supported`	작업에 전체 다시 쓰기가 필요함. 점검 시간에 `ALGORITHM=COPY`를 쓰거나, 라이브 테이블에는 `gh-ost`/`pt-online-schema-change`를 사용.
`ERROR 1846: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table...`	변경이 메타데이터만으로 끝나지 않음. 먼저 `ALGORITHM=INPLACE, LOCK=NONE`을 시도하고, 그것도 실패하면 `COPY`로 돌아가기.
`ERROR 1553: Cannot drop index 'PRIMARY': needed in a foreign key constraint`	참조하는 외래 키를 먼저 삭제하고, 인덱스를 변경한 다음, 외래 키를 다시 만들기.
`ERROR 3780: Referencing column ... and referenced column ... are incompatible`	외래 키의 자식 열과 부모 열은 유형이 호환되어야 함. 양쪽을 함께 바꾸거나, 마이그레이션 동안 외래 키를 삭제.
`COPY` 알고리즘 `ALTER`가 끝이 보이지 않게 쓰기 작업을 막고 있음	`SHOW PROCESSLIST`에서 상태를, `information_schema.INNODB_TRX`에서 얼마나 오래 실행되었는지 확인. 기다릴 수 없다면, 중단하고 `gh-ost`로 전환.
`gh-ost`가 시작을 거부	`binlog_format=ROW`인지, 대상 테이블에 기본 키나 고유 키가 있는지 확인. 둘 다 필수.

함께 보기

MySQL의 스키마 마이그레이션 — 이 가이드가 실행하는 ALTER TABLE 문을 만들어 내는 마이그레이션 파일을 구성하고 버전 관리하기
MySQL 인덱스의 동작 방식 — 온라인 ADD INDEX 중에 MySQL이 만드는 인덱스가 이후 쿼리 성능에 미치는 영향
MySQL의 EXPLAIN 읽기 — 마이그레이션이 끝난 뒤 새로 추가한 인덱스가 실제로 사용되는지 확인하기

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성