pt-online-schema-change란? (pt-osc)

MySQL을 사용하는 온라인 서비스의 경우 DDL에 algorithm=inplace, lock=none 옵션을 추가하여 테이블에 대한 Lock을 방지하고 서비스 중인 DML 트랜잭션의 영향을 최소화할 수 있는 기능이 있어 서비스 중에도 대부분 온라인 DDL이 가능합니다.

하지만, 해당 옵션이 지원되지 않는 작업이 있어 이런 작업에 대해 online ddl이 가능하도록 지원해주는 툴이 pt-online-schmea-change (=pt-osc)입니다.

  • 작동 원리
  1. 변경할 DDL이 적용된 스키마의 타겟 테이블 생성
  2. 원본 - 타겟테이블 간, 작업 중 실시간 데이터 동기화를 위한 트리거(Insert, Update, Delete) 생성
  3. 옵션으로 설정한 Chunk 단위 데이터 Copy
  4. Rename을 이용해 원본-타겟테이블 스위칭
  5. 원본 (구)테이블 삭제 (옵션을 통해 삭제하지 않고 유지 가능)

pt-osc 설치

  • yum install percona-toolkit 명령어 사용 시, 최신 버전 설치가 가능하지만, 특정 버전에 따라 실행오류가 발생하여 3.0.13버전으로 설치했습니다.
## 관련 패키지 설치
$ sudo yum install perl-DBI
$ sudo yum install perl-DBD-MySQL
$ sudo yum install perl-TermReadKey
$ sudo yum install perl perl-IO-Socket-SSL perl-Time-HiRes
$ sudo yum install perl-devel
 
## 특정 버전 percona-toolkit 설치파일 다운 (3.1 / 3.2버전 실행 실패로 3.0.13버전 실행)
$ wget https://downloads.percona.com/downloads/percona-toolkit/3.0.13/binary/tarball/percona-toolkit-3.0.13_x86_64.tar.gz
$ tar -zxvf percona-toolkit-3.0.13_x86_64.tar.gz
 
## percona-toolkit 설치
 
$ cd [path]/percona-toolkit-3.0.13
$ perl ./Makefile.PL
$ make
$ sudo make install
 
## 실행파일은 [path]/percona-toolkit-3.0.13/bin/pt-online-schema

pt-osc를 통한 online 컬럼 타입 변경

테스트 환경은 아래와 같습니다.

  • 인스턴스 타입: r5.xlarge
  • 버전: 5.7.mysql_aurora_2.09.2
  • 작업 대상: 약 6.3GB 사이즈의 테이블
  • 작업 내용: 컬럼 타입 변경 (char(120) -> varchar(120))
  1. 백그라운드로 pt-osc 스크립트 실행 
     $ nohup pt-online-schema-change --alter "CHANGE c c varchar(120) DEFAULT '' NOT NULL" D=sbtest_220822,t=sbtest1 \ 
     --no-drop-old-table --chunk-size=1000 --chunk-index=PRIMARY \          ## 작업 단위 (chunk) 설정, chunk 기준 인덱스 명시      
     --host=[호스트] --port=[포트] \                                           ## 접속 정보
     --user=[계정] --password=[패스워드] \                                     ## ID, Password
     --progress=time,30 \                                                   ## 로그 주기
     --charset=UTF8mb4 \                                                    ## 캐릭터셋 설정
     --max-load="Threads_running=50" \                                      ## 해당 Threads_running 임계치 기준 도달 시, 작업 일시 중지 (Default: 25)
     --critical-load="Threads_running=80" \                                 ## 해당 Threads_running 임계치 기준 도달 시, 작업 강제 종료 (Default: 50)
     --alter-foreign-keys-method=auto \                                     ## 외래키 복사 방식 설정
     --preserve-triggers \                                                  ## 기존 원본 테이블 트리거 -> 타겟테이블로 트리거 복사
     --sleep=0.01 / 0.05 / 생략 \                                            ## chunk 단위 작업마다 sleep 시간 설정 (CPU 부하 조절용)
     --execute > 220822_pt_log.txt 2>&1 &                                   ## 출력 로그파일 명시
    

     ## --set-vars 옵션 설정으로, pt-osc 세션 환경 변수 설정 가능 (ex) --set-vars="lock_wait_timeout=1"
  2. 정상 변경 확인 
         mysql> show create table sbtest1;
     CREATE TABLE `sbtest1` (
         `id` int(11) NOT NULL AUTO_INCREMENT,
         `k` int(11) NOT NULL DEFAULT '0',
         `c` varchar(120) NOT NULL DEFAULT '',
         `pad` char(60) NOT NULL DEFAULT '',
         PRIMARY KEY (`id`),
         KEY `k_1` (`k`)
         ) ENGINE=InnoDB AUTO_INCREMENT=30000001 DEFAULT CHARSET=utf8mb4

  3. 작업 시, CPU 부하 확인 sleep 옵션 유무에 따라, 작업에 걸리는 시간과, CPU 부하가 반비례하게 변하는걸 클라우드워치 메트릭을 통해 확인해봤습니다.

    sleep=0일 경우, CPU는 60%를 사용했지만 sleep=0.05 옵션값을 주었을때 CPU 45% 사용을 확인할 수 있었습니다. sleep 옵션값이 작아질수록 소요 시간은 짧아지고 CPU 부하는 커집니다. sleep 옵션값이 커질수록 소요 시간은 길어지고 CPU 부하는 낮아집니다.

작업 실패 시, 작업 순서

pt-osc 작업 중 예기치 못한 오류로 인해 실해하게 되면 위의 작동원리의 순서에 따라 생성된 오브젝트를 삭제해주어야 합니다. 먼저 작업 중 실시간 데이터 동기화를 위해 생성된 트리거 삭제를 진행해야 합니다.

현재 테스트에 사용된 3.0.13 버전 기준으로, 트리거는 pt_osc_[테이블명]_ins/upd/del 과 같은 네이밍으로 생성됩니다. 이를 찾아 제거하기 위해, 아래 구문을 사용하여 찾아낸 트리거를 제거합니다.

mysql> SHOW TRIGGERS LIKE '[테이블명]';
mysql> DROP TRIGGER 'pt_osc_[테이블명]_ins';
mysql> DROP TRIGGER 'pt_osc_[테이블명]_upd';
mysql> DROP TRIGGER 'pt_osc_[테이블명]_del';

이후, 새로운 스키마를 적용하기 위해 생성된 타겟테이블을 제거해주면 됩니다.

mysql> DROP TABLE [테이블명]_new;

위의 작업들을 진행하면 작업 도중 생긴 오브젝트를 모두 삭제해주고, 다시 작업을 진행할 때 중복 오류 발생을 방지할 수 있습니다.

결론

pt-osc를 이용하여 online DDL 작업을 진행하더라도, 작업 시간과 CPU 부하를 적절히 고려하여 트래픽이 낮은 시간대에 조심스럽게 진행이 되어야 할 거 같습니다. 해당 툴에서 사용하는 옵션들을 각 서비스 환경에 맞게 사용하면 유용하게 사용할 수 있는 좋은 3rd-party-tool이라고 생각합니다.