글을 작성하게 된 계기

pt-online-schema-change 에 대해 알게 된 내용을 정리하기 위해 글을 작성하게 되었습니다.

1. 문제 상황

회사에서 결제 트래픽이 평소 몇 배 정도일 때, pt-online-schema-change 를 사용해 테이블을 변경하고 있었습니다. 이는 Percona Toolkit 에서 제공하는 온라인 스키마 변경 도구로, 테이블을 그대로 복사 한 후, 기존 테이블과 스위칭하는 방식입니다. MySQL 에서 테이블을 변경할 때, Lock 을 최소화하면서도 안전하게 변경할 수 있도록 도와주는 하나의 방법이죠.

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

이를 그림으로 보면 다음과 같습니다. 즉, 기존 테이블을 복사한 후, 데이터 동기화를 위해 트리거를 걸고, 데이터를 복사한 후, 동기화가 완료되면 테이블을 스왑하는 방식입니다. 그런데 작업 마무리 단계에서, 테이블을 스왑하던 중 결제 시스템이 마비 되는 장애가 발생했습니다. 왜 장애가 발생했을까요?

2. 무엇이 문제였을까?

문제 원인을 알기 위해서는 pt-online-schema-change 동작 과정, 로그 분석을 먼저 해보겠습니다.

pt-online-schema-change 동작 과정
로그 분석
원인 분석

2-1. pt-online-schema-change 동작 과정

pt-online-schema-change는 기존 테이블을 직접 수정하지 않고, 동일한 구조의 새 테이블을 생성 한 뒤 변경을 적용 합니다. 새 테이블이 준비되면, 데이터 동기화를 위해 원본 테이블에 트리거 를 생성하고, 데이터를 Chunk 단위로 복사 하면서 실시간 동기화를 유지 합니다. 이후 스왑 단계에서 원본 테이블과 새 테이블을 교체하고, 필요한 경우 외래키 재설정을 하며 작업을 마무리합니다.

pt-online-schema-change의 동작 과정을 정리하면 다음과 같습니다.

새 테이블 생성 (CREATE TABLE)
새 테이블에 변경 적용 (ALTER TABLE)
트리거 생성 (INSERT, UPDATE, DELETE 감지)
원본 테이블 → 새 테이블로 데이터 복사 (Chunk 단위로 처리)
데이터 동기화 유지 (트리거로 실시간 동기화)
스왑: 새 테이블과 원본 테이블 교체
트리거 제거, 외래키 갱신 등 마무리 작업

이때, 테이블을 교체하는 순간과 외래키를 다시 연결하는 과정은 민감한 구간으로, 짧은 시간 동안 메타데이터 락(Metadata Lock)이 발생할 수 있어 주의가 필요합니다.

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas, stored programs(procedures, functions, triggers, scheduled events), tablespaces, user locks acquired with the GET_LOCK() function, and locks acquired with the locking service ……

2-2. 로그 분석

이제 로그를 분석해 보겠습니다. 이는 크게 실행 재시도 설정 정보, 외래키 연결된 자식 테이블 정보, dry-run 실행 로그 로 나눌 수 있습니다. 아래는 장애를 가정한 로그 입니다.

  
# 1. 실행 재시도 설정 정보
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
  
# 2. 외래키 연결된 자식 테이블 정보
Child tables:
  `db`.`table_a` (approx. 270000 rows)
  `db`.`table_b` (approx. 22000 rows)
  `db`.`table_c` (approx. 9300 rows)
  `db`.`table_d` (approx. 1160000 rows)
Will automatically choose the method to update foreign keys.

# 3. dry-run 실행 로그
Starting a dry run.  `db`.`table` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table db.table_new OK.
Altering new table...
Altered `db`.`table_new` OK.
Altering new table...
Altered `db`.`table_new` OK.
2025-04-07T11:54:14 Dropping new table...
2025-04-07T11:54:14 Dropped new table OK.
Dry run complete.  `db`.`table` was not altered.

2-2-1. 실행 재시도 설정 정보

먼저 실행 재시도 설정 정보입니다. 이는 각 단계에서 실패 시 재시도할 횟수 와 대기 시간 을 뜻합니다. 이 중 위험한 구간은 swap_tables 과 update_foreign_keys 로, 메타데이터 락이 발생할 수 있고, 해당 테이블에 접근하려던 트랜잭션들이 대기 상태에 빠질 수 있기 때문입니다.

Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1

즉, 기존 테이블을 바라보고 있는 다른 테이블들이 새로운 테이블을 바라보게 만들 동안 다른 테이블에서 참조하지 못하도록 메타데이터 락을 잡고 있는 것이죠. 락을 잡고 있는 동안 연결된 외래키를 교체 하고, 테이블 이름을 변경 합니다. 락에는 타임아웃(Timeout) 이 존재하며, 일정 시간을 초과할 경우, 롤백(Rollback)이 발생합니다. 데이터가 많을 수록 롤백에 드는 시간도 비례해 증가하고요.

The timeout value applies separately for each metadata lock attempt. A given statement can require more than one lock, so it is possible for the statement to block for longer than the lock_wait_timeout value before reporting a timeout error.

2-2-2. 외래키 연결된 자식 테이블 정보

다음은 외래키에 연결된 자식 테이블 정보 입니다. 외래키로 연결된 자식 테이블이 총 3개이고, 이 중에는 row가 백만 건 이상인 테이블이 포함되어 있습니다. 외래키를 가진 자식 테이블과 꽤 많은 데이터를 포함한 테이블이 있다는 것은, 외래키를 다시 연결하는 작업이 느릴 수밖에 없고, 이 과정에서 데이터 무결성 검증을 위한 락이 발생했을 가능성이 있습니다.

  
Child tables:
  `db`.`table_a` (approx. 270000 rows)
  `db`.`table_b` (approx. 1 rows)
  `db`.`table_c` (approx. 1160000 rows)

외래키를 다시 연결하는 이유는 스왑 후에 원본 테이블과 새 테이블의 외래키를 다시 연결하기 위함입니다. 즉, 스왑 후에 원본 테이블을 삭제하고, 원본 테이블에 걸려있던 외래키를 새 테이블로 옮기는 작업이죠.

2-2-3. dry-run 실행 로그

마지막은 –dry-run 옵션으로 실행된 시뮬레이션 로그 입니다. 이는 실제 테이블에 영향을 주지 않고, 변경 작업이 정상적으로 수행될 수 있는지 미리 점검합니다. 아래 로그를 보면, 새 테이블 생성, 변경, 삭제까지 모든 과정이 문제없이 수행되었음을 확인할 수 있습니다. 실제 데이터 복사나 테이블 스왑이 발생하지 않기 때문에, 메타데이터 락이나 트랜잭션 충돌도 일어나지 않습니다. 즉, 운영 환경에 대한 안전성 검증을 위해 실행된 결과를 보는 절차입니다.

  
Starting a dry run.  `db`.`table` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table db.table_new OK.
Altering new table...
Altered `db`.`table_new` OK.
Altering new table...
Altered `db`.`table_new` OK.
2025-04-07T11:54:14 Dropping new table...
2025-04-07T11:54:14 Dropped new table OK.
Dry run complete.  `db`.`table` was not altered.

2-3. 원인 분석

여기까지 살펴봤으면 왜 장애가 발생했는지 알 수 있는데요, 장애는 결제 시스템의 트래픽이 평소보다 약 3배가량 증가한 시점 에 발생했습니다. 대상 테이블이 여러 개의 외래키를 가진 테이블들과 연결되어 있었고, 일부 자식 테이블은 수십만에서 백만 건 이상의 데이터를 포함 하고 있었습니다.

평소보다 3배 이상의 트래픽
외래키가 복잡하게 연결된 테이블

MySQL은 스왑 또는 외래키를 다시 연결하는 과정에서 데이터 무결성 을 보장하기 위해 짧은 시간 동안 메타데이터 락 획득을 시도합니다. 그런데 결제 시스템이 평소보다 3배 정도의 트래픽을 받는 상황에서, 스왑 및 외래키 갱신을 시도 하니 메타데이터 락을 획득하지 못하고 타임아웃이 발생해버렸죠.

스왑 시점에 메타데이터 락 발생
결제 트랜잭션으로 메타데이터 락 대기
타임아웃 발생
롤백 과정에서 수 백만 건의 데이터 롤백
그 동안 결제 시스템 마비

정리하면 스왑을 위해 메타데이터 락을 잡으려던 시도는 기존 결제 트랜잭션들과 충돌했고, 결국 타임아웃이 발생했으며, 그에 따른 대규모 롤백 과정에서 수백만 건의 데이터를 정리하느라 결제 시스템 전체가 마비된 것입니다.

롤백을 위한 데이터가 수 십 GB 였고, 롤백이 완료되기까지 약 수 십 분 정도의 시간이 소요되었습니다.

3. 다른 해결책은 없을까?

그렇다면 이런 불상사를 막기 위해서는 어떤 방법을 사용해야 할까요?

트래픽이 적은 시간대에 작업하기
pt-online-schema-change 위험 구간 이해하기

3-1. 트래픽이 적은 시간대에 작업하기

우선 가장 근본적인 해결책은, 중요한 실시간 서비스 테이블에 대해 스키마 변경을 할 때는 트래픽이 적은 시간대를 선택 해야 합니다. 장애는 트래픽이 평소보다 몇 배 많은 시점에 작업이 이루어졌기 때문에 발생한 것이지, pt-online-schema-change 도구 자체의 문제는 아니었습니다. 트래픽이 많은지 적은지를 정량적으로 판단하려면 아래 명령어를 사용할 수 있습니다.

  
-- 현재 연결된 클라이언트 수 확인
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 현재 락 대기 중인 세션 확인
SELECT * FROM performance_schema.data_locks;

-- 현재 실행 중인 쿼리 확인
SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep';

모니터링 툴을 도입할 수도 있는데요, 이는 일반적으로 수 초 간의 지연이 발생할 수 있습니다.

또는 SHOW ENGINE INNODB STATUS\G 명령어로 락 대기 현황, 트랜잭션 큐 상태, 잠금 경합 여부를 직접 확인할 수 있습니다. 즉, 이를 통해 정확한 수치를 확인한 후, 작업을 진행해야 합니다.

  
$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G"

이를 확인해야 할 값들은 다음과 같은데요, Threads_connected 는 현재 접속 중인 클라이언트 수를 나타냅니다. data_locks 은 특정 테이블에 락이 걸려 있거나 대기 중인 상태를 보여주며 락 충돌 가능성을 확인할 때 사용되며, processlist 는 실행 중인 쿼리 목록을 통해 장기 실행 쿼리나 대기 중인 트랜잭션 여부를 확인할 수 있습니다.

Threads_connected가 평소보다 2배 이상이면 작업 보류
data_locks에 현재 대상 테이블이 포함되어 있으면 작업 중단
processlist에 장기 실행 쿼리가 많으면 보류

3-2. pt-online-schema-change 위험 구간 이해하기

두 번째는, pt-online-schema-change의 위험 구간인 swap과 update_foreign_keys 작업을 명확히 이해하고, 이 시점에서 발생할 수 있는 충돌 가능성을 예측해야 한다는 것입니다. 특히 외래키가 얽혀있는 테이블의 경우, 스왑 이후 외래키 갱신 작업에서 자식 테이블을 건드리게 되고, 이때 수십만 건 이상의 자식 데이터가 있는 경우 짧은 락도 치명적이 될 수 있습니다. 그러므로 사전에 자식 테이블의 크기를 확인하고, 너무 큰 경우에는 스키마 변경 계획 자체를 재검토해야 합니다.

  
pt-online-schema-change \
  --alter "ADD COLUMN dummy INT" \
  --user=root --password=passwd \
  --host=localhost \
  --charset=utf8mb4 \
  --dry-run \
  D=dbname,t=target_table

dry-run으로, 정상적으로 실행되는 것을 확인하면, 실제 작업에서는 스왑 시점을 사람이 직접 제어하기 위해 --no-swap-tables 옵션을 사용할 수도 있습니다. 이는 실제 데이터를 복사하지만, 테이블 스왑은 하지 않도록 하는 옵션입니다.

  
# 실 작업, 하지만 테이블 스왑은 하지 않음
pt-online-schema-change \
  --alter "ADD COLUMN dummy INT" \
  --user=root --password=passwd \
  --host=localhost \
  --charset=utf8mb4 \
  --no-swap-tables --execute \
  D=dbname,t=target_table

그리고 트래픽이 가장 적은 새벽 시간에 수동으로 테이블을 스왑 합니다. 이 시점이 메타데이터 락이 걸리는 순간이므로, 반드시 트랜잭션이 거의 없는 상태에서 실행해야 합니다.

-- 스왑 직접 수행
RENAME TABLE target_table TO target_table_old, _target_table_new TO target_table;

운영 DB와 유사한 규모의 테스트 환경에서 –execute까지 실제로 돌려보는 과정이 필요합니다. pt-osc는 테이블 크기나 자식 테이블 수, 외래키 개수에 따라 실행 시간이 크게 달라집니다. 이때 –statistics, –progress time,30 옵션을 함께 붙이면 전체 작업 시간과 각 단계별 소요를 로그로 확인할 수 있어, 운영 시점 판단에 도움이 됩니다.

  
# 테스트 DB에서 실제 실행
pt-online-schema-change \
  --alter "ADD COLUMN dummy INT" \
  --user=root --password=passwd \
  --host=localhost \
  --charset=utf8mb4 \
  --execute \
  D=test_dbname,t=target_table

이 외에도 CDC(Change Data Capture), Online DDL, 복제본 교체 방식(Replica Promotion) 등 다양한 고급 기법들이 있는데요, 이는 별도의 포스팅으로 다루도록 하겠습니다.

CDC(Change Data Capture)
Online DDL
복제본 교체 방식(Replica Promotion)

4. 정리

pt-online-schema-change를 사용해 테이블을 변경하는 과정에서 발생한 장애의 원인과 해결책에 대해 살펴보았습니다. 옆 부서나 사람들의 장애를 볼 때마다 참 안타까운데요, 돈과 관련돼 있기 때문에 모두가 예민하고, 한 사람의 실수가 회사 전체에 큰 손실을 가져오기 때문입니다. 여튼, 저도 조심해야겠습니다. 이전에 1분간 결제 사고를 낸 적이 있거든요.

pt-online-schema-change를 사용하다 결제 시스템이 마비 됐다?