MySQL - pt_online_schema_change

https://docs.percona.com/percona-toolkit/pt-online-schema-change.html

Percona Toolkit Documentation

 

 

pt-online-schema-change는 읽기나 쓰기를 차단하지 않고 Alter tabel 시 테이블 구조를 Locking 없이 변경을 도와줍니다.

 

---

1. pt_online_schema_change 작동방식 및 설치

1-1. pt_online_schema_change 작동방식

• 새로 적용할 ALTER TABLE ~ ADD/DROP/RENAME ~ 이 적용된 임시 테이블 B 생성
• 트리거 생성 (trigger 형식으로 A to B로 옮기는 와중에 생성된 데이터도 이관 가능)
• 기존 테이블 A의 데이터 B로 이관 ( chunk size )
• 기존 테이블 A Rename/Drop → A` , 신규 테이블 B Rename → A
• 트리거 삭제

1-2. pt_online_schema_change 설치 가이드

• percona-toolkit 설치 시 의존성 패키지 파일 목록
  • libcbor, ca-certificates, openssl@3, libfido2, zlib, lz4, xz, zstd, mysql-client and perl-dbd-mysql

2. pt_online_schema_change TEST

2-1. 테이블 생성 및 데이터 삽입

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  email VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

## 약 1만건 INSERT

2-2. pt_online_schema_change 를 통한 컬럼 추가 ( 시뮬레이션 )

## 시뮬레이션 ##

pt-online-schema-change \\
--alter "ADD COLUMN phone VARCHAR(20) NULL" \\
--user=[DB User] \\
--password=[DB User Password] \\
--host=localhost \\
D=test01,t=users \\
--dry-run

 

• test01.users will not be altered : 시뮬레이션으로 인한 변경 없음.
• _users 로 임시테이블 생성
• dry run 으로 인한 trigger 미생성
• 기타 유용한 옵션

옵션 설명

--dry-run	실제 실행 없이 시뮬레이션
--nocheck-replication-filters	복제 필터 경고 무시
--max-load Threads_running=25	시스템 부하 기준으로 일시 정지
--chunk-size=1000	복사 시 한 번에 처리할 행 수
--alter-foreign-keys-method=auto	외래키 자동 처리 방식 지정 (rebuild, drop_swap 등)
--no-drop-old-table	테이블 swap 이후 변경 전 테이블 유지

2-3. pt_online_schema_change 를 통한 컬럼 추가

• 데이터 INSERT 도중 Online DDL

pt_online_schema_change - 컬럼 추가 작업 진행

pt-online-schema-change \\
  --alter "ADD COLUMN phone VARCHAR(20) NULL" \\
  --user=[DB User] \\
  --password=[DB User Password] \\
  --host=localhost \\
  D=test01,t=users \\
  --execute

 

 

• 임시 테이블 _users 생성
• 트리거 생성 후 옮기는 와중에 생성된 데이터도 이관
• 카피 완료
• Analyzing 테이블
• _users ↔ users 테이블 스왑
• 기존 테이블 삭제
• 생성한 트리거 삭제

 

Transaction 활성화 중 컬럼 추가 완료

 

 

만약 pt_online_schema_change 실패시 ❌

• 실패 시 트리거 삭제 따로 진행해야 합니다.
• 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 ‘[테이블 명]

 

3. 마무리

MySQL 에서 운영중인 데이터베이스에 크기가 몇십, 몇백 또는 몇억건의 테이블에 DDL 작업은 위험도가 큰 작업이기도 하고, 락이 잡히면 운영 서비스에 영향도가 생기기 때문에 작업이 쉽지가 않습니다.

pt_online_schema_change ( pt-osc) 는 percona 에서 제공하는 3rd-party tool 입니다. 이 써드파티 툴을 이용해 많은 기업들이 Online 중에 많이 DDL 작업들을 하곤 합니다.

그러나 Online 중 DDL 이 가능하다고 하더라도, 해당 데이터베이스 리소스에 맞게 커스터마이징하고, 트래픽이 몰리는 시간대는 피하는게 좋습니다.

ALTER TABLE [ADD/DROP/MODIFY/RENAME] [Column/Index] 등등.. 서비스별로 알맞게 사용하면 좋을 것 같습니다.