[MySQL - Online DDL] part 3 use. pt-online-change-schema
pt-online-change-schema 이란,
Mysql에서 몇억건 이상의 대량의 데이터를 갖고 있는 테이블을 수정 하는것은 어렵습니다. 단순히 alter table을 해버리면 4시간, 5시간 이상 수행되기 떄문에 이를 해결 하기 위해 create select(CTAS) 방법을 사용합니다.
MySQL은 특히나 온라인 스키마 변경이 취약합니다. 물론5.6부터는 online alter기능이 포함되어 있다고는 하나 100% 완벽하게 모든 상황을 온라인스럽게 제공해주지도 않고그렇다하더라도, 일정 트래픽 이상의 데이터 변경이 이루어지는 경우, 게다가 슬레이브 지연을 염두한다면 꺼려지는 상황이 있습니다. (마스터에서 온라인 스키마 변경이 이루어졌을지라도, 이 관련 alter구문이 슬레이브로 넘어갔을 때는, alter이후 데이터 변경을 수행해야 하므로, 그만큼 복제 지연이 발생합니다.)
사용 이유 : 대상 테이블의 쓰기 잠금이 발생하는 경우가 있어서 일반적으로 온라인 상태에서의 테이블 스키마의 변경은 운영 중 항상 부담이 되고 DDL이 실행 되는 동안 정상적인 서비스가 불가능한 상태가 될수도 있었습니다. 또한 DDL 타임이 길어짐에 따라 Replication 지연이 발생 될 수도 있습니다. pt-online-schema-change 는 이러한 MySQL 의 Alter(DDL작업) 수행을 자동화(스크립트)하고 온라인으로 처리 될 수있도록 도움을 주는 도구입니다.
pt-online-change-schema 동작 방식
session parameter 변경 ( innodb_lock_wait_timeout / lock_wait_timeout / sql_mode 등 )
외래키 확인
TO-BE TABLE 생성
TO-BE TABLE DDL 실행
TRIGGER 생성
insert into select 구문으로 data 이관
trigger를 이용하여 AS-IS에서 발생했던 dml TO-BE TABLE 적용(PT-OSC 의 수행 방식은 트리거를 활용하여 데이터를 복제하는 방식)
RENAME TABLE
■ Perl 패키지 설치
rpm으로 설치하면 간편하지만, 가끔 rpm파일이 설치가 안될때가 있어 tar.gz으로 설치 진행
[root@jh-mysql ~]# wget percona.com/get/percona-toolkit.tar.gz
[root@jh-mysql ~]# tar -xvf percona-toolkit.tar.gz
[root@jh-mysql ~]# cd percona-toolkit-3.3.1/
[root@jh-mysql percona-toolkit-3.3.1]# perl ./Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for percona-toolkit
[root@jh-mysql percona-toolkit-3.3.1]# make && make install
...(생략)
Installing /usr/local/bin/pt-slave-delay
Installing /usr/local/bin/pt-sift
Installing /usr/local/bin/pt-diskstats
Installing /usr/local/bin/pt-visual-explain
Installing /usr/local/bin/pt-pg-summary
Installing /usr/local/bin/pt-variable-advisor
Installing /usr/local/bin/pt-index-usage
Installing /usr/local/bin/pt-duplicate-key-checker
Installing /usr/local/bin/pt-config-diff
Installing /usr/local/bin/pt-stalk
Appending installation info to /usr/lib64/perl5/perllocal.pod
■ alias 설정
[root@jh-mysql ~]# vi ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/mysql/bin
export PATH
alias pt-online-schema-change="/root/percona-toolkit-3.3.1/bin/pt-online-schema-change"
■ test용 테이블 생성
mysql> create table test_tbl ( a int primary key auto_increment, b varchar(100), c varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_tbl select null, uuid(),uuid() from information_schema.columns as a, information_schema.columns as b limit 1000000;
Query OK, 1000000 rows affected (17.32 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
■ 작업전 확인 사항
용량 확인 필요
PT-OSC 는 복제 테이블을 생성하고 작업을 하기 때문에 작업 대상 테이블(인덱스 포함) 사이즈 만큼의 공간이 추가로 필요합니다. 또한 작업 중에 binary log 또한 더 많이 생성되게 됩니다.
mysql> select sum(data_length+index_length)/1024/1024 from information_schema.tables where TABLE_NAME='test_tbl';
+-----------------------------------------+
| sum(data_length+index_length)/1024/1024 |
+-----------------------------------------+
| 101.62500000 |
+-----------------------------------------+
1 row in set (0.00 sec)
## 데이터 파일 크기 확인
[root@jh-mysql kim]# du -sh /data/kim/test_tbl.ibd
113M /data/kim/test_tbl.ibd
## 데이터 스토리지 여유 공간 확인
[root@jh-mysql kim]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda3 48G 6.7G 42G 14% /
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.8G 0 1.8G 0% /dev/shm
tmpfs 1.8G 8.4M 1.8G 1% /run
tmpfs 1.8G 0 1.8G 0% /sys/fs/cgroup
tmpfs 354M 0 354M 0% /run/user/0
pt-online-schema-change --alter "변경할 Alter 정보" D=데이터베이스,t=테이블명 \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8 \
--alter-foreign-keys-method=auto \
--preserve-triggers
--execute
* --no-drop-old-table
* 마이그레이션 후, 기존 테이블을 삭제 하지 않겠다는 옵션 입니다.
* host, port, user
* 스크립트를 실행할 대상 DB 정보입니다.
* chunk-size
* 한번에 복사할 데이터양을 얘기합니다. 데이터를 복사하는 단위 인 chunk 사이즈를 줄여서 데이터를 조금씩 복사(데이터 건 수를 줄여서)하여 MySQL의 부하를 낮추거나 IOPS 가 높은 스토리지 시스템을 통해서 새벽 시간때 작업할 경우 빠른 작업 완료를 위해 chunk 사이즈를 증가 시켜서 사용할 수도 있는 옵션입니다. chunk-size-limit=1 : chunk-size 의 max 배수값, 기본값 4.0
* --charset=UTF8
* 필수로 넣으셔야 합니다.
* 안넣을 경우 한글명 데이터들이 이관하면서 깨지는 경우가 생깁니다.
* --alter-foreign-keys-method=auto
* FK도 복사할것인지 여부입니다.
* 옵션값이 auto일 경우 최적화된 방식으로 FK를 복사합니다.
■ OSC 이용 테이블 컬럼 추가
[root@jh-mysql ~]# pt-online-schema-change --execute \
--recursion-method=none \
--host=localhost \
--defaults-file=/etc/my.cnf \
--chunk-size=500 --chunk-size-limit=1 \
--chunk-index=PRIMARY \
--sleep=0.1 --progress=time,10 \
--port=3306 --charset=UTF8 \
--user=root --ask-pass \
--alter "add column newcolumn varchar(30)" \
D=kim,t=test_tbl
No slaves found. See --recursion-method if host jh-mysql has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 7119.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 7119.
# A software update is available:
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 `kim`.`test_tbl`...
Creating new table...
Created new table kim._test_tbl_new OK.
Altering new table...
Altered `kim`.`_test_tbl_new` OK.
2022-08-19T14:19:22 Creating triggers...
2022-08-19T14:19:22 Created triggers OK.
2022-08-19T14:19:22 Copying approximately 993705 rows...
Copying `kim`.`test_tbl`: 4% 03:49 remain
Copying `kim`.`test_tbl`: 8% 03:28 remain
Copying `kim`.`test_tbl`: 13% 03:16 remain
Copying `kim`.`test_tbl`: 17% 03:05 remain
Copying `kim`.`test_tbl`: 22% 02:54 remain
Copying `kim`.`test_tbl`: 26% 02:43 remain
Copying `kim`.`test_tbl`: 31% 02:33 remain
Copying `kim`.`test_tbl`: 35% 02:22 remain
Copying `kim`.`test_tbl`: 40% 02:12 remain
Copying `kim`.`test_tbl`: 44% 02:02 remain
Copying `kim`.`test_tbl`: 49% 01:52 remain
Copying `kim`.`test_tbl`: 53% 01:42 remain
Copying `kim`.`test_tbl`: 58% 01:32 remain
Copying `kim`.`test_tbl`: 62% 01:22 remain
Copying `kim`.`test_tbl`: 67% 01:12 remain
Copying `kim`.`test_tbl`: 72% 01:02 remain
Copying `kim`.`test_tbl`: 76% 00:52 remain
Copying `kim`.`test_tbl`: 81% 00:42 remain
Copying `kim`.`test_tbl`: 85% 00:32 remain
Copying `kim`.`test_tbl`: 89% 00:22 remain
Copying `kim`.`test_tbl`: 94% 00:12 remain
Copying `kim`.`test_tbl`: 98% 00:02 remain
2022-08-19T14:23:06 Copied rows OK.
2022-08-19T14:23:06 Analyzing new table...
2022-08-19T14:23:06 Swapping tables...
2022-08-19T14:23:06 Swapped original and new tables OK.
2022-08-19T14:23:06 Dropping old table...
2022-08-19T14:23:06 Dropped old table `kim`.`_test_tbl_old` OK.
2022-08-19T14:23:06 Dropping triggers...
2022-08-19T14:23:06 Dropped triggers OK.
Successfully altered `kim`.`test_tbl`.
## 생성 확인
mysql> desc kim.test_tbl;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| a | int | NO | PRI | NULL | auto_increment |
| b | varchar(100) | YES | | NULL | |
| c | varchar(100) | YES | | NULL | |
| newcolumn | varchar(30) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
※ 아래 에러 발생 시 1
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 6340.
BEGIN failed--compilation aborted at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 6340.
패키지 설치
[root@jh-mysql ~]# yum install perl-Digest-MD5
※ 아래 에러 발생 시 2
Cannot connect to MySQL: DBI connect('kim;mysql_read_default_file=/etc/my.cnf;host=localhost;charset=UTF8;mysql_read_default_group=client','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /root/percona-toolkit-3.3.1/bin/pt-online-schema-change line 2345.
mysql_native_password 옵션 사용
mysql> alter user root@localhost identified with mysql_native_password by 'root';
※ replication 구조일 시
복제 구성 환경에서는 실행하는 서버에서 -> Replica(slave) MySQL 로 접속이 가능해야 합니다.
slave에 접속하지 못하는 경우(예: CloudDB for MySQL) 에는 --recursion-method=none 옵션 사용
■ 임시 테이블 확인
OSC 작업진행되는 생성되는 임시테이블 확인
mysql> show tables;
+---------------+
| Tables_in_kim |
+---------------+
| _test_tbl_new |
| test_tbl |
+---------------+
2 rows in set (0.00 sec)
## 데이터파일 생성 확인
[root@jh-mysql ~]# du -sh /data/kim/*
113M /data/kim/test_tbl.ibd
112M /data/kim/_test_tbl_new.ibd
NCP의 CloudDB for MySQL이나 AWS RDS와 같은 클라우드 서비스형 DB에서는 SUPER권한이 제외되어 있습니다. 따라서 SUPER권한 관련 에러가 발생 할 수 있습니다.
db do failed: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)[for Statement "CREATE TRIGGER `pt_osc_...`]
해결 방법
아래 파라미터를 ON으로 설정, 동적 변수이기 때문에 재기동 없이 변경 가능.
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.00 sec)
