[Mysql] pt-online-schema-change 로 서비스 중단 없이 데이터베이스 스키마 변경하기

개요

이 글에서는 운영 환경에서 스키마 변경이 필요할 경우, 어떻게 무중단 변경을 수행하는지 살펴보고. 관련해서 Mysql Online DDL, pt-online-schema-change 에 대해서 알아봅니다.

 

상황

회사에서 사용하는 프로그램에서 요청 결과를 log 형식으로 데이터베이스에 저장하고 있습니다.

 

Performance Insight

시간이 흘러 데이터가 많이 쌓였고, 자연스레 쿼리 성능이 떨어지기 시작했습니다. 확인해보니 34,977,366개의 데이터가 쌓여있었고 인덱스 개선을 통해 이를 개선해보기 결정했습니다.

 

Mysql 8.0 을 사용 중이기 때문에 Mysql Online DDL(https://mystudylab.tistory.com/207) 을 활용해도 되었지만, 테이블의 크기가 크고 DML 이 자주 일어나는 테이블에 적용하면 자칫 DB 성능 저하로 장애가 발생할 수 있습니다.

 

때문에 회사에서는  pt-online-schema-change 를 활영하여 무중단 스키마 변경을 수행하고 있습니다.

 

pt-online-schema-change?

데이터베이스를 운영하다 보면 테이블 구조를 변경해야 하는 상황이 자주 발생합니다. 특히 대용량 테이블에 인덱스를 추가하거나 컬럼을 변경해야 할 때, 기존의 ALTER TABLE 명령은 테이블 전체에 락을 걸어 서비스 중단으로 이어질 수 있습니다. 이런 문제를 해결하기 위한 도구가 바로 Percona Toolkit의 pt-online-schema-change입니다.

 

pt-online-schema-change는 MySQL이나 MariaDB에서 운영 중인 서비스를 중단하지 않고 테이블 스키마를 변경할 수 있게 해주는 도구입니다. Percona에서 개발한 이 도구는 대규모 프로덕션 환경에서도 안전하게 테이블 구조를 변경할 수 있게 해줍니다.

 

 

작동 원리

pt-online-schema-change의 작동 방식은 다음과 같은 단계로 이루어집니다:

1. 임시 테이블 생성: 원본 테이블과 동일한 구조로 빈 임시 테이블을 생성합니다.
2. 스키마 변경 적용: 이 임시 테이블에 원하는 스키마 변경(인덱스 추가, 컬럼 변경 등)을 적용합니다.
3. 트리거 설정: 원본 테이블에 INSERT, UPDATE, DELETE 트리거를 생성하여 모든 데이터 변경 사항이 임시 테이블에도 실시간으로 반영되도록 합니다.
4. 데이터 복사: 원본 테이블의 데이터를 작은 청크(chunk) 단위로 임시 테이블에 복사합니다. 각 청크 사이에 설정된 시간 동안 대기하여 서버 부하를 제어합니다.
5. 테이블 교체: 모든 데이터가 복사되면 트리거를 제거하고, 원본 테이블과 임시 테이블의 이름을 교체합니다(RENAME TABLE). 이 작업은 매우 짧은 시간(밀리초 단위)에 완료됩니다.
6. 정리: 선택한 옵션에 따라 이전 테이블을 보존하거나 삭제합니다.

pt-online-schema-change

 

MySQL Online DDL vs pt-online-schema-change

1. 테이블 변경 접근법

MySQL Online DDL:

• 내부 구현 방식: 데이터베이스 엔진 내부에서 최적화된 방식으로 직접 구현 -> chunk x
• INPLACE 알고리즘: 가능한 경우 물리적 데이터 복사 없이 테이블 구조 직접 수정
• 변경 로깅: 내부 로그 기반으로 진행 중인 DML 작업 처리
• 메모리 관리: InnoDB 버퍼 풀을 통한 효율적인 메모리 관리

pt-online-schema-change:

• 외부 도구 방식: MySQL 서버 외부에서 작동하는 독립 도구
• 트리거 기반: 변경 내용을 동기화하기 위해 트리거 메커니즘 사용
• 새 테이블 생성: 항상 새 테이블을 생성하고 데이터를 복사하는 방식
• 청크 단위 처리: 데이터를 작은 단위로 나누어 순차적으로 처리

2. 락(Lock) 처리 방식

MySQL Online DDL:

• 메타데이터 락: 작업 시작과 끝에 짧은 메타데이터 락만 사용
• 락 옵션: LOCK=NONE, LOCK=SHARED, LOCK=EXCLUSIVE 옵션 제공
• 테이블 접근: INPLACE + LOCK=NONE 조합으로 락 없이 작업 가능

pt-online-schema-change:

• 락 없는 복사: 작은 청크 단위로 복사하여 락 회피
• 테이블 스왑: RENAME TABLE 실행 시에만 매우 짧은 메타데이터 락 발생
• 사용자 제어: --chunk-time 등의 옵션으로 락 가능성 세밀하게 제어

3. 데이터 관리 비교

MySQL Online DDL:

• 공간 효율성: INPLACE 알고리즘은 추가 공간을 최소화
• 로그 기반: 변경 중 새로운 변경사항은 로그에 기록 후 반영
• 임시 공간: COPY 알고리즘에서는 임시 테이블스페이스 사용

pt-online-schema-change:

• 임시 테이블: 항상 원본 테이블과 크기가 동일한 임시 테이블 필요
• 트리거 오버헤드: 모든 DML 작업마다 트리거 실행으로 추가 부하
• 백업 유지: --no-drop-old-table 옵션으로 원본 보존 가능

4. 제약 및 호환성

MySQL Online DDL:

• 버전 의존성: MySQL 5.6 이상에서만 지원, 버전별 지원 범위 다름
• 작업 제한: 특정 스키마 변경(특히 데이터 타입 변경)은 지원하지 않음
• 자동 선택: 알고리즘과 락 옵션을 자동으로 최적화하여 선택 가능

pt-online-schema-change:

• 넓은 호환성: 거의 모든 MySQL 버전에서 작동
• 광범위한 지원: 대부분의 스키마 변경 작업 지원
• 트리거 제약: 기존 트리거가 있는 경우 충돌 가능성

5. 성능 및 리소스 영향

MySQL Online DDL:

• 최적화된 성능: 엔진 내부 구현으로 일반적으로 더 효율적
• 메모리 사용: InnoDB 버퍼 풀 활용으로 메모리 효율성 높음
• 백그라운드 스레드: 내부 최적화된 스레드로 처리

pt-online-schema-change:

• 서버 부하 제어: 청크 크기와 시간 간격으로 세밀한 부하 제어
• 리소스 사용: 두 배의 디스크 공간과 트리거 오버헤드 발생
• 커스텀 옵션: 다양한 옵션으로 상황에 맞게 최적화 가능

실제 사용 예시 비교

인덱스 추가 시나리오

MySQL Online DDL:

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

pt-online-schema-change:

pt-online-schema-change --alter "ADD INDEX idx_customer_id (customer_id)" \
  --chunk-time=1 --chunk-size=1000 --no-drop-old-table \
  D=database,t=orders,h=localhost,u=user

매우 큰 테이블에서 pt-online-schema-change의 장점

1. 세밀한 리소스 제어
   • --chunk-size와 --chunk-time 옵션을 통해 한 번에 처리할 데이터 양과 각 처리 간 대기 시간을 정밀하게 조절할 수 있습니다.
   • 수억 건 이상의 대용량 테이블에서는 서버 리소스 사용을 제어하는 것이 매우 중요한데, MySQL Online DDL은 이러한 세밀한 제어가 제한적입니다.
2. 중단 없는 복구 가능성
   • 대용량 테이블 작업 도중 문제가 발생하면 MySQL Online DDL은 전체 작업을 롤백해야 하며 이 과정이 매우 오래 걸릴 수 있습니다.
   • pt-online-schema-change는 --no-drop-old-table 옵션으로 원본 테이블을 보존하므로, 문제 발생 시 빠르게 원래 상태로 복원할 수 있습니다.
3. 타임아웃 문제 회피
   • 매우 큰 테이블에서 MySQL Online DDL은 내부 타임아웃이나 락 타임아웃에 도달할 가능성이 높습니다.
   • pt-online-schema-change는 외부 도구로 작동하므로 이러한 내부 타임아웃 제한에 덜 영향을 받습니다.
4. 서버 부하 분산
   • 대용량 테이블 작업 시 MySQL Online DDL은 서버 리소스를 많이 사용할 수 있고, 이로 인해 다른 쿼리 성능이 저하될 수 있습니다.
   • pt-online-schema-change는 청크 단위 처리와 시간 간격 지정으로 부하를 분산시켜 프로덕션 환경에 미치는 영향을 최소화할 수 있습니다.
5. 안정적인 복제 환경 유지
   • 대용량 테이블에서 MySQL Online DDL은 복제 지연을 크게 증가시킬 수 있습니다.
   • pt-online-schema-change는 복제 지연을 모니터링하고 필요시 작업 속도를 자동으로 조절하는 --max-lag 옵션을 제공합니다.
6. 메모리 사용량 관리
   • 매우 큰 테이블에서 MySQL Online DDL은 상당한 양의 버퍼 풀 메모리를 사용할 수 있습니다.
   • pt-online-schema-change는 작은 청크 단위로 작업하므로 메모리 사용량이 더 예측 가능하고 관리하기 쉽습니다.
7. 작업 진행 상황 가시성
   • 대용량 테이블 작업 시 MySQL Online DDL은 진행 상황을 자세히 보기 어렵습니다.
   • pt-online-schema-change는 각 청크 처리마다 상세한 진행 정보를 제공하므로 작업 모니터링과 예상 완료 시간 추정이 용이합니다.

실제 프로덕션 환경에서는 이러한 이유로 수억 건 이상의 대규모 테이블 스키마 변경 작업에 pt-online-schema-change를 선호하는 경우가 많습니다. 특히 서비스 중단 위험을 최소화하면서 리소스 사용을 세밀하게 제어해야 하는 상황에서 유용합니다.

결론: 어떤 상황에서 어떤 방식이 적합한가

1. MySQL Online DDL 선택이 좋은 경우:
   • MySQL 8.0 이상 최신 버전 사용 시
   • 단순한 인덱스 추가/삭제 작업
   • 추가 공간이 제한적인 환경
   • 트리거가 이미 있는 테이블
2. pt-online-schema-change 선택이 좋은 경우:
   • 구 버전 MySQL 사용 시
   • 매우 큰 테이블(수억 건 이상)
   • 세밀한 리소스 사용 제어가 필요한 경우
   • MySQL Online DDL이 지원하지 않는 변경 작업

두 도구 모두 각자의 장단점이 있으므로, 상황에 맞게 적절한 도구를 선택하는 것이 중요합니다.