pt-online-schema-change

Online DDL 작업을 할때 ALGORITHM=INPLACE 를 줘서 에러가 발생하지 않은 경우는 서비스중에 작업을 한다.

ALGORITHM=COPY 를 써야되면 점검걸고 하면되는데 점검을 걸지 못하는 경우라면?

 

방법이 있겠지. 찾아보자 구글검색! 구글신은 위대하니까.

 

pt-online-schema-change 라는게 있다. (gh-ost 라는것도 있는데 이건 다음에)

www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

 

어떻게 동작할까?  

- 변경할 스키마 구조의 임시 테이블 생성
- insert/update/delete 트리거를 생성하여 변경 데이터 동기화

- 지정한 청크 사이즈로 데이터를 끊어 데이터 전체를 임시 테이블로 복사

- 임시 테이블로 복사가 완료되면 테이블 이름을 변경하여 작업완료 

 

주의사항은?

- 적용 테이블에 PRIMARY KEY 또는 UNIQUE INDEX 가 있어야 한다.

- 서비스에 영향이 없도록 청크 사이즈를 잘 조절해서 작업 한다.

- 임시 테이블로 데이터를 복사하는 경우도 Binary Log에 기록된다. 

  Binary Log Format이 Row 일경우 로그 사이즈가 급격하게 늘어날 수 있다.

  작업시 Binary Log 사이즈에 대한 모니터링이 필요하다.

 

설치

- pt-online-schema-change 가 perl 기반이라 perl 관련 패키지 설치가 필요하다.

# perl 관련 패키지 설치 
sudo yum -y install perl-DBI perl-DBD-MySQL perl-TermReadKey
sudo yum -y install perl perl-IO-Socket-SSL perl-Time-HiRes
sudo yum -y install perl-Digest-MD5 perl-devel

# percona-toolkit 설치
wget percona.com/get/percona-toolkit.tar.gz
tar xzvf percona-toolkit.tar.gz
# percona-toolkit-3.3.0 디렉토리는 버전에 따라 다를 수 있다.
cd percona-toolkit-3.3.0/
perl ./Makefile.PL
make
sudo make install

percona-toolkit-3.3.0/bin 디렉토리의 pt-online-schema-change 을 사용하면 된다.

 

사용법

pt-online-schema-change --alter "변경할 Alter 정보" D=데이터베이스,t=테이블 \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--recursion-method=dsn=t=mysql.dsns \
--max-lag=5 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--password=password \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=200" \
--chunk-index=PRIMARY \
--charset=utf8 \
--set-vars="tx_isolation='repeatable-read',innodb_lock_wait_timeout=1,lock_wait_timeout=1" \
--no-check-alter \
--execute

- no-drop-old-table : 원본 테이블 rename 후 drop 안함
- no-drop-new-table : 작업 실패했을 때 생성한 new 테이블 drop 안함
- chunk-size : 복사할 데이터 크기

- recursion-method : slave 를 찾는 방법, DB Port가 기본 Port (3306)이 아닐 경우 Slave를 찾지 못한다. 
                            dsn 로 지정시 접속할 Slave 정보를 조회할 테이블을 지정한다. 

                            mysql.dsns 테이블을 지정했고 해당 테이블에 Slave 정보를 Insert 해야한다.
- max-lag : 지정값 이상의 복제지연(Slave에서)이 발생하면 작업을 중단하고 기다린다.
- progress : 각 줄을 복사하는 동안에 STDERR를 보고서에 작성해서 출력한다. 출력 간격 설정
- max-load : 작업 도중 서버 부하를 막기위해 지정한 한계점보다 부하가 크다면 작업을 일시 중지한다.
- critical-load : 지정한 한계점보다 부하가 크다면 작업을 중지한다.
- set-vars : 작업 시작시 설정할 session variables

- no-check-alter : --alter에서 명시된 구문을 분석하지 않는다. 

 

테스트

테스트를 위해 아래와 같이 복제를 구성한다.

- Master : 192.168.137.11 MySQL 5.7.34

- Slave : 192.168.137.12 MySQL 5.7.34

 

alter table alter table algorithm=inplace 컬럼사이즈 변경 에서 만든 zipcode 테이블로 테스트를 한다. 

 

alter table algorithm=inplace 컬럼사이즈 변경

# dsn 테이블에 slave 정보 입력
mysql> use mysql;
 
mysql> CREATE TABLE `dsn` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `parent_id` int(11) NOT NULL,
    ->   `dsn` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

# slave IP:port insert
mysql> INSERT INTO mysql.dsn(parent_id,dsn) VALUES(1,'h=192.168.137.12,P=3306'); 
Query OK, 1 row affected (0.01 sec)


# pt-online-schema-change 로 컬럼 사이즈 변경 실행 
[root@localhost percona-toolkit-3.3.0]# pt-online-schema-change --alter "change sigungu sigungu varchar(64)" D=zipcode,t=zipcode \
> --no-drop-old-table \
> --no-drop-new-table \
> --chunk-size=500 \
> --recursion-method=dsn=t=mysql.dsn \
> --max-lag=5 \
> --defaults-file=/etc/my.cnf \
> --host=127.0.0.1 \
> --port=3306 \
> --user=root \
> --password=패스워드 \
> --progress=time,30 \
> --max-load="Threads_running=100" \
> --critical-load="Threads_running=200" \
> --chunk-index=PRIMARY \
> --charset=utf8mb4 \
> --set-vars="tx_isolation='repeatable-read',innodb_lock_wait_timeout=1,lock_wait_timeout=1" \
> --no-check-alter \
> --execute
Found 1 slaves:
localhost.localdomain -> 192.168.137.12:3306
Will check slave lag on:
localhost.localdomain -> 192.168.137.12:3306
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
Altering `zipcode`.`zipcode`...
Creating new table...
Created new table zipcode._zipcode_new OK.
Altering new table...
Altered `zipcode`.`_zipcode_new` OK.
2021-05-02T13:35:35 Creating triggers...
2021-05-02T13:35:35 Created triggers OK.
2021-05-02T13:35:35 Copying approximately 6074412 rows...
Copying `zipcode`.`zipcode`:   5% 09:00 remain
Copying `zipcode`.`zipcode`:  10% 08:27 remain
Copying `zipcode`.`zipcode`:  15% 07:55 remain
Copying `zipcode`.`zipcode`:  21% 07:26 remain
Copying `zipcode`.`zipcode`:  25% 07:09 remain
Copying `zipcode`.`zipcode`:  31% 06:36 remain
Copying `zipcode`.`zipcode`:  36% 06:11 remain
Copying `zipcode`.`zipcode`:  40% 05:46 remain
Copying `zipcode`.`zipcode`:  46% 05:15 remain
Copying `zipcode`.`zipcode`:  50% 04:49 remain
Copying `zipcode`.`zipcode`:  56% 04:17 remain
Copying `zipcode`.`zipcode`:  61% 03:47 remain
Copying `zipcode`.`zipcode`:  66% 03:19 remain
Copying `zipcode`.`zipcode`:  71% 02:50 remain
Copying `zipcode`.`zipcode`:  76% 02:18 remain
Copying `zipcode`.`zipcode`:  81% 01:49 remain
Copying `zipcode`.`zipcode`:  86% 01:21 remain
Copying `zipcode`.`zipcode`:  91% 00:48 remain
Copying `zipcode`.`zipcode`:  96% 00:20 remain
2021-05-02T13:45:54 Copied rows OK.
2021-05-02T13:45:54 Analyzing new table...
2021-05-02T13:45:54 Swapping tables...
2021-05-02T13:45:55 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2021-05-02T13:45:55 Dropping triggers...
2021-05-02T13:45:55 Dropped triggers OK.
Successfully altered `zipcode`.`zipcode`.


# --chunk-size=500 만큼 끊어서 데이터를 복사하는것이 확인된다. 
# WHERE ((`seq` >= '771401')) AND ((`seq` <= '771900'))
mysql> select * from information_schema.processlist where user='root'\G;
     ID: 20
   USER: root
   HOST: localhost:45894
     DB: zipcode
COMMAND: Query
   TIME: 0
  STATE: query end
   INFO: INSERT LOW_PRIORITY IGNORE INTO `zipcode`.`_zipcode_new` (`zipcode`, `sido`, `sido_en`, `sigungu`, `sigungu_en`, `eupmyun`, `eupmyun_en`, `doro_code`, `doro`, `doro_en`, `under_yn`, `buildno1`, `buildno2`, `buildnum`, `multiple`, `buildname`, `dong_code`, `dong`, `ri`, `dong_hj`, `mount_yn`, `jibun1`, `eupmyundong_no`, `jibun2`, `zipcode_old`, `zipcode_seq`, `seq`) SELECT `zipcode`, `sido`, `sido_en`, `sigungu`, `sigungu_en`, `eupmyun`, `eupmyun_en`, `doro_code`, `doro`, `doro_en`, `under_yn`, `buildno1`, `buildno2`, `buildnum`, `multiple`, `buildname`, `dong_code`, `dong`, `ri`, `dong_hj`, `mount_yn`, `jibun1`, `eupmyundong_no`, `jibun2`, `zipcode_old`, `zipcode_seq`, `seq` FROM `zipcode`.`zipcode` FORCE INDEX(`PRIMARY`) WHERE ((`seq` >= '771401')) AND ((`seq` <= '771900')) LOCK IN SHARE MODE /*pt-online-schema-change 11751 copy nibble*/
3 rows in set (0.00 sec)