Last Updated on 1월 21, 2024 by Jade(정현호)
Percona Toolkit
Percona Toolkit 는 Percona 사에서 개발한 오픈소스 기반의 MySQL/MariaDB 의 유틸리티로 기술지원 시 사용하던 유용한 기능의 유틸리티 도구를 하나로 묶은 패키지입니다.
이러한 Toolkit은 일일이 수동으로 작업하기 다소 복잡하거나 어려운 작업들을 단순하고 수월하게 할 수 있게 도와주는 유용한 도구입니다.
Percona Toolkit 의 각 도구는 대부분 Perl 로 제작되어 있습니다.
그러므로 5.8 버전 이상의 Perl 이 필요하며 Shell은 bash 에서 정상 동작하게 되어 있어서 bash 쉘이 필요 합니다.
Toolkit 은 MySQL에 접속하여 수행하기 때문에 MySQL 클라이언트가 필요 합니다.
또한 Toolkit(pt-query-digest) 에 따라서 저장소(MySQL)가 필요할 수도 있습니다.
진행 환경
OS : CentOS 7.8 / Ubuntu 18.04
MySQL : 5.7.31
Percona Toolkit : 3.2.1-1
설치 방식 : Package
Percona Toolkit 설치
Percona provides packages for most popular 64-bit Linux distributions:
Debian 7 (“wheezy”)
Debian 8 (“jessie”)
Ubuntu 14.04 LTS (Trusty Tahr)
Ubuntu 16.04 LTS (Xenial Xerus)
Ubuntu 16.10 (Yakkety Yak)
Ubuntu 17.04 (Zesty Zapus)
Red Hat Enterprise Linux or CentOS 6 (Santiago)
Red Hat Enterprise Linux or CentOS 7 (Maipo)
Repository 구성
패키지 설치를 위해 먼저 Repository 를 설정합니다.
• Ubuntu
User$ sudo apt update
User$ sudo apt install -y wget gnupg2 lsb-release curl
User$ wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
User$ sudo dpkg -i percona-release_latest.generic_all.deb
• CentOS
User$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
패키지 설치
Replication 환경에서는 master/slave 모두 설치합니다.
• For Debian or Ubuntu:
User$ sudo apt-get update
User$ sudo apt install perl libdbi-perl \
libdbd-mysql-perl libtime-hires-perl \
libterm-readkey-perl
User$ sudo apt-get install percona-toolkit
• For RHEL or CentOS:
User$ sudo yum install perl perl-DBI \
perl-DBD-MySQL perl-Time-HiRes \
perl-TermReadKey
User$ sudo yum install percona-toolkit
version 체크
$ pt-query-digest --version
pt-query-digest 3.2.1
Percona Toolkit 사용 방법
Percona Toolkit 은 수십 개의 개별 도구로 구성되어 있으며, 각 도구마다 사용법과 활용 범위가 다양합니다.
다양한 Percona Toolkit 중에서 일반적으로 자주 사용하고 다양하게 활용 가능한 몇 가지 도구의 사용법을 알아보도록 하겠습니다.
pt-summary
pt-summary 는 리눅스 OS 의 전반적인 상태 값과 설정을 간편하게 요약해서 보여주는 도구이며, 요약된 리포팅이 주요 기능입니다.
$ pt-summary
# Percona Toolkit System Summary Report ##################
Date | 2020-12-19 17:05:10 UTC (local TZ: KST +0900)
Hostname | acs
Uptime | 20:27, 1 user, load average: 0.01, 0.00, 0.00
System | innotek GmbH; VirtualBox; v1.2 (Other)
Service Tag | 0
Platform | Linux
Release | Red Hat Enterprise Linux Server release 7.8 (Maipo)
Kernel | 4.14.35-2025.400.9.el7uek.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.17
Compiler | GNU CC version 4.8.5 20150623 (Red Hat 4.8.5-39.0.1).
SELinux | Enforcing
Virtualized | KVM
<중략...>
# Memory ##############################################
Total | 3.5G
Free | 1.5G
<중략...>
# Notable Processes #####################################
PID OOM COMMAND
1718 -17 sshd
# Memory mamagement ##################################
Transparent huge pages are enabled.
# The End ##############################################
Percona Toolkit System Summary Report
-> 조회 대상 리눅스 OS의 일반적인 버전 정보 등을 보여줍니다.
Processor
-> /proc/cpuinfo 로 확인되는 프로세스 정보를 보여줍니다.
Memory
-> /proc/meminfo , sysctl 로 부터 현재 사용 중인 메모리 사용량을 유형별로 보여 줍니다.
Mounted Filesystems
-> mount, df,fdisk ,/proc/sys/fs 로 부터 디스크 와 파일시스템 정보 등의 정보를 요약하여 보여줍니다.
Network Config 등
-> 사용중인 네트워크 인터페이스의 설정 값이나 사용 현황을 요약하여 보여줍니다.
pt-mysql-summary
pt-mysql-summary 는 MySQL 의 환경설정 값과 상태 값에 대해 요약된 결과를 간편하게 보여주는 기능입니다.
튜닝이나 진단의 결과가 아닌 요약된 정보 리포팅입니다.
실행 방법 요약
pt-mysql-summary [options] [--mysql options]
pt-mysql-summary --host=localhost --port=3306 --user root --password
[참고] --login-path 는 사용되지 않습니다.
pt-mysql-summary --host=localhost --port=3306 --user root --password root
pt-mysql-summary --host=localhost --port=3306 --user root --ask-pass
실행
user$ pt-mysql-summary --host=localhost --port=3306 \
--socket=/tmp/mysql.socket--user root \
--password root --socket
# Instances #####################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
/usr/local/mysql/data 0 0 /tmp/mysql.sock
# MySQL Executable #############################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Slave Hosts ##################################
No slaves found
# Report On Port 3306 ##########################
User | root@localhost
Time | 2020-12-20 04:33:29 (KST)
Hostname | acs
Version | 5.7.31-log MySQL Community Server (GPL)
Built On | el7 x86_64
Started | 2020-12-19 05:38 (up 0+22:55:28)
Databases | 6
Datadir | /usr/local/mysql/data/
Processes | 1 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /usr/local/mysql/tmp/mysqld.pid (exists)
<중략...>
# Status Counters (Wait 10 Seconds) #############
Variable Per day Per second 10 secs
Aborted_connects 3
Bytes_received 1500 500
Bytes_sent 30000 2500
Com_select 5 2
Com_show_status 2
Com_show_variables 1
Connections 9 1
Created_tmp_files 6
Created_tmp_tables 3 6
Flush_commands 1
Handler_commit 10
Handler_external_lock 250
Handler_read_first 15
Handler_read_key 10
Handler_read_next 2
Handler_read_rnd_next 2000 80
<중략...>
# Table cache ###############################
Size | 2000
Usage | 5%
# Key Percona Server features #################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
<중략...>
# Binary Logging ############################
Binlogs | 13
Zero-Sized | 0
Total Size | 2.7k
binlog_format | ROW
expire_logs_days | 7
sync_binlog | 1
server_id | 1
binlog_do_db |
binlog_ignore_db |
# Noteworthy Variables ######################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
<중략...>
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_general_ci
[mysqld_safe]
log_error = /usr/local/mysql/logs/mysqld.log
pid-file = /usr/local/mysql/tmp/mysqld.pid
debug = d,info,error,query:o,/usr/local/mysql/logs/mysqld.trace
stack-trace
core-file
core-file-size = unlimited
# Memory management library ###########################
jemalloc is not enabled in mysql config for process with id 2959
# The End ############################################
pt-heartbeat
pt-heartbeat 는 MySQL Replication 지연(lag)을 모니터링하기 위한 도구입니다.
Replication 관계에 있는 마스터 DB 와 슬레이브 DB의 Replication 지연을 실시간 측정합니다.
측정하기 위해 각 DB 마다 임의의 테이블을 생성하고 이 테이블에 양쪽 서버의 Binlog 와 릴레이 로그의 포지션 정보를 1초 마다 갱신하며 저장을 합니다.
모니터링 정보를 저장하기 위한 DB 와 테이블을 아래와 같이 생성합니다.
mysql> create database repmondb character set
utf8mb4 collate utf8mb4_general_ci;
mysql> use repmondb;
mysql> drop table `heartbeat`;
mysql> create table `heartbeat` (
`ts` varchar(26) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`file` varchar(255) default null,
`position` bigint(10) unsigned DEFAULT NULL,
`relay_master_log_file` varchar(255) DEFAULT NULL,
`exec_master_log_pos` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY(`server_id`));
마스터서버에서 백그라운드 모드로 pt-heartbeat 실행
# pt-heartbeat --daemonize --database='repmondb' \
--table='heartbeat' --user='root' --ask-pass \
--host='localhost' --port=3306 --replace --update
정상적으로 수행이 되었다면 1초 마다 replace into 문으로 마스터 서버의 정보를 갱신하게 됩니다.
슬레이브 서버에서 모니터링 수행
# pt-heartbeat -D repmondb --monitor \
--master-server-id 1 --user=root \
--ask-pass --print-master-server-id
0.01s [ 0.40s, 0.08s, 0.03s ]
0.00s [ 0.40s, 0.08s, 0.03s ]
0.00s [ 0.40s, 0.08s, 0.03s ]
0.00s [ 0.40s, 0.08s, 0.03s ]
1.00s [ 0.41s, 0.08s, 0.03s ]
2.00s [ 0.45s, 0.09s, 0.03s ]
3.00s [ 0.50s, 0.10s, 0.03s ]
4.00s [ 0.56s, 0.11s, 0.04s ]
5.00s [ 0.65s, 0.13s, 0.04s ]
6.00s [ 0.75s, 0.15s, 0.05s ]
7.00s [ 0.86s, 0.17s, 0.06s ]
8.00s [ 0.99s, 0.20s, 0.07s ]
9.00s [ 1.14s, 0.23s, 0.08s ]
10.00s [ 1.31s, 0.26s, 0.09s ]
11.00s [ 1.49s, 0.30s, 0.10s ]
12.00s [ 1.69s, 0.34s, 0.11s ]
13.00s [ 1.91s, 0.38s, 0.13s ]
0.00s [ 1.91s, 0.38s, 0.13s ]
0.00s [ 1.91s, 0.38s, 0.13s ]
0.00s [ 1.91s, 0.38s, 0.13s ]
pt-online-schema-change
pt-online-schema-change 는 Percona Toolkit 구성 툴 중에서 가장 많이 언급되고 사용되는 툴 중에 하나입니다.
사실 포스팅에 주요한 이유도 pt-online-schema-change 를 확인해보고자 하는 게 가장 큰 이유였습니다.
MySQL 5.6 이하 버전에서는 테이블의 스키마 변경하기 위하여 Alter 를 사용하였을 때 아래와 같이 동시성에서 제약이 생기면서 처리되는 방식으로 진행되었습니다.
1. 대상 테이블을 Shared Lock 으로 잠급니다
대상 테이블의 읽기(Read)는 허용되지만 DML 등의 쓰기는 제한됩니다.
2. 빈 임시 테이블(Temp)을 생성하고 원본 테이블의 데이터를 임시 테이블로 복사합니다.
3. 2번 작업이 완료되면 원본 테이블을 삭제하고 임시 복사 테이블을 원본 테이블 이름으로 rename 하게 됩니다.
4. 신규 테이블에 DML 이 가능하도록 Lock 을 해제합니다.
[참고] 버전과 테이블 스키마 변경의 종류에 따라 Lock의 종류 등은 상세하게 다르게 처리됩니다.
관련되어 아래 2개 포스팅을 참조하시면 더 많은 정보를 보실 수 있습니다.
PT-OSC 사용하는 여러 이유
위의 내용과 같이 대상 테이블의 쓰기 잠금이 발생하는 경우가 있어서 일반적으로 온라인 상태에서의 테이블 스키마의 변경은 운영 중 항상 부담이 되고 DDL이 실행되는 동안 정상적인 서비스가 불가능한 상태가 될 수도 있었습니다.
또한 DDL 타임이 길어 짐에 따라 Replication 지연이 발생될 수도 있습니다.
pt-online-schema-change 는 이러한 MySQL 의 Alter(DDL작업) 수행을 자동화(스크립트)하고 온라인으로 처리될 수 있도록 도움을 주는 도구입니다.
MySQL 5.6 이상 Online DDL 이 도입된 시점에서도 자체적인 Online DDL(Native Online DDL) 대신에 pt-online-schema-change(이하 PT-OSC) 를 사용하는 경우가 많습니다.
여러 이유가 있겠으나 우선 상황에 따라 모든 Operation 이 온라인으로 가능하지 않습니다. 또는 Alter 구문 내 다수의 처리를 필요한 경우가 있을 수도 있습니다.
무엇 보다 온라인 DDL(In-Place)의 제약사항에 따라서 PT-OSC 를 사용하게 되게 되며, 대표적으로 큰 용량의 테이블에 대해서 장시간 작업시 DDL 이 실패가 될 수 있기 때문입니다.
온라인 DDL(In-Place) 방식의 경우 innodb_online_alter_log_max_size 파라미터에 지정한 만큼 작업 중에 발생되는 Concurrent DML에 대한 임시 로그를 기록하게 되는데 파라미터에 지정된 용량 이상 되면 DDL 이 실패하게 됩니다.
(innodb_online_alter_log_max_size 기본 값은 128MB 입니다)
그래서 온라인 DDL(In-Place) 가 가능한 Operation Type 이라고 해도 테이블의 사이즈가 아주 크다면 innodb_online_alter_log_max_size 파라미터 제한에 따라서 DDL 이 실패할 수도 있습니다, 그렇다고 해당 파라미터를 무작정 크게 증가시키는 것도 어렵습니다.
또한 대량의 트랜잭션의 발생시 부하에 대해서 더 잘 소화해 낼 수 있는지에 대해서도 고려가 되기 때문입니다.
PT-OSC 는 Percona 에서 기술지원 등으로 필드나 운영 환경에서의 다수의 사용경험이 내재되어 있으며 릴리즈 된 지 오래되어 툴의 숙성도가 깊다고 할 수 있습니다.
이런 여러 이유 때문에 Native Online DDL이 미지원 되는 MySQL 버전에서도 또는 최근에 Online DDL이 지원되는 버전에서도 사용이 되고 있는 것으로 생각됩니다.
MySQL 의 Native Online DDL 도 버전이 올라가면서 미지원되었던 온라인 기능이 추가되거나 더 향상되어 계속 발전되고 있고 있습니다.
테이블의 크기와 테이블의 트랜잭션량 또는 busy 한지 그렇지 않은지 등을 고려하여 MySQL Native DDL을 통해서 작업할 수도 있습니다.
(편리함은 MySQL Native DDL이 더 편하다고 생각함)
그렇기 때문에 상황에 따라서 MySQL 의 Online DDL 을 사용하거나 PT-OSC 를 사용할지 등을 고려/선택하면 될 것 같습니다.
PT-OSC 의 수행 방식은 트리거를 활용하여 데이터를 복제하는 방식입니다.
수행 동작 순서
먼저 새로운 임시 테이블을(_new) 을 생성 후 새로운 임시 테이블에 ALTER 구문을 적용합니다.
임시 테이블에 변경되는 소스 데이터 계속 적용하기 위해서 원본 테이블에 트리거를 생성합니다.
원본 테이블의 데이터를 새로운 임시 테이블로 복사합니다.
-> ignore into, Chunk 단위로 복사를 진행하고 옵션(chunk-size) 을 설정하여 사용가능
원본 테이블 과 임시테이블의 이름을 변경합니다.
-> 원본테이블의 이름은 _old 로 변경하고 임시 테이블의 이름을 기존 원본테이블의 이름으로 변경
외래키(FK) 제약조건을 적용합니다.
-> 외래키 제약조건이 있는 테이블의 경우 외래키 제약조건을 적용
기존 원본 테이블의 삭제합니다.
-> --no-drop-old-table 옵션을 사용하면 원본 테이블 삭제가 되지 않음
* 청크는 한 번에 복사하는 레코드 묶음을 말하며 기본값은 1000 레코드 입니다.
pt-osc 명령어 실행 예시
사용법 예시)
pt-online-schema-change --alter "<alter 명령어>" \
D=<데이터베이스>,t=<테이블> \
--no-drop-old-table \ : 원본테이블을 삭제 하지 않음
--chunk-size=2000 \ : default: 1000, 청크단위, 입력한 줄 수 만큼 복사됩니다
--chunk-size-limit=4 \ : chunk-size 와 곱(x, *) 해서 계산 되는 수치
--host=DB 호스트 또는 IP --port=3306 \
--socket=/tmp/mysql.sock \
--user=계정명 \
--p=패스워드 \ or --ask-pass
--sleep=0.1 --progress=time,30 \
--defaults-file=/etc/my.cnf \
--max-load="Threads_running=50" \
--critical-load="Threads_running=100" \
--chunk-index=PRIMARY \
--charset=UTF8 \
--execute : 실제로 실행 여부, 지정하지 않으면 dry-run
추가 옵션 설명
--chunk-size-limit
chunk-size-limit 는 최소값 1 이고, 디폴트 4.0 입니다.
해당 옵션은 chunk_size 와 같이 사용되며 chunk-size x chunk-size-limit 2개의 옵션이 곱해져서 최종적으로 처리되는 청크의 단위가 결정되게 됩니다. 그리고 2개의 옵션 값의 결과 와 explain 을 하여 수행될 쿼리의 행의 수를 비교하여 사용됩니다.
지정한 청크 사이즈 보다 Explain 의 행의 추정치가 더 더 작다면 나눠서 처리하지 않고 한 번에 진행하게 되고 수행되는 SQL 에는 where 절이 제외되고 수행됩니다.
지정한 청크 사이즈보다 Explain 의 행의 추정치가 더 크다면 chunk-size x chunk-size_limit 값만큼 나눠서 수행되게 됩니다.
chunk-size-limit 의 파라미터 옵션의 이름과 chunk 처리단위의 max 값의 의미가 아닌 2개의 값을 곱하여 사용하는데 매뉴얼상에서도 "청크 크기에 제한을 곱한 값을" 이라는 표현되어 있습니다
또한 소스코드에서도 chunk-size 와 chunk-size-limit 을 곱하게 구현되어 있습니다.
my $chunk_size_limit = $o->get('chunk-size-limit') || 1;
my $one_nibble = !defined $args{one_nibble} || $args{one_nibble}
? $row_est <= $chunk_size * $chunk_size_limit
: 0;
--new-table-name
PT-OSC 가 수행될 때 생성되는 임시 테이블의 이름을 명시적으로 지정하는 옵션입니다.
기본 값으로 %T_new 으로 %T 는 테이블명으로 기본 설정으로 사용한다면 테이블명 뒤에 _new 가 붙게 됩니다
또한 고유한 테이블 이름을 찾기 위해 이름 앞에 최대 10개의 _(밑줄)을 붙이게 됩니다.
임시 테이블 이름의 형태 : _테이블명_new
그리고 임시테이블은 PT-OSC가 실행 시 존재하면 안 됩니다.
--progress
default: time,30
실행하는 과정의 출력에 관한 옵션으로 2개의 인자로 구분됩니다.
첫번째 인자에는 시간 ,비율(%), iterations(반복) 정할 수 있으며, 두번째 인자 값으로 비율, 시간(초), 반복의 수 를 명시하게 됩니다.
--recursion-method
default: processlist,hosts
Replication 인스턴스를 찾기 위해 recursion-method 를 선택합니다. 가능한 메소드는 아래와 같습니다.
METHOD USES
=========== ==================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves
SHOW SLAVE HOSTS 정보로는 Replication 에 대한 정보를 신뢰할 수 없기 때문에 processlist 를 사용하는 것이 기본 값입니다.
그러나 서버가 비표준 포트(3306 아님)를 사용하는 경우 hosts 메소드 방법이 더 잘 작동할 수 있습니다.
일반적으로 올바른 작업을 수행하고 모든 Replication을 찾지만 선호하는 방법을 지정하면 먼저 사용됩니다
그러나 선호하는 방법을 선택할 수 있으며 선택한 method를 먼저 사용하게 됩니다.
hosts method을 사용하려면 Replica 인스턴스에 report_host, report_port 시스템 변수가 구성해야 합니다.
dsn 메소드는 별도로 생성한 DNS 테이블을 읽어서 수행을 합니다.
CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
none 는 replica(slave) 인스턴스를 찾지 않는 것입니다.
AWS RDS 나 기타 서비스형 MySQL 에서 사용할 경우 아래에 사용시 특이사항 을 참조하시기 바랍니다.
--sleep=0.1
default: 0
각 청크를 복사한 후 얼마나 대기(sleep) 할지 를 정하는 옵션입니다.
이 옵션은 --max-lag 및 --max-load 에 의한 조절이 불가능할 때 유용합니다.
0.1과 같이 1초 미만의 작은 값을 사용해야 하며 그렇지 않으면 큰 테이블을 복사하는 데 매우 오랜 시간이 걸릴 수 있습니다.
default 가 0 이기 때문에 사용한 sleep 시간만큼 복제가 지연이 되게 됩니다.
다만 지연이 되는 만큼 높은 CPU 사용율이 높은 환경에서는 조금 더 도움이 될 수 도 있지만 보통 0.1 초 정도 부터 해보는 게 적당할 것 같습니다.
-- set-vars
set-vars 옵션을 통해서 여러 시스템 파라미터 중에서 세션레벨로 변경 가능한 파라미터를 적용해서 사용할 수 있으며 각 파라미터 간에는 콤마를 사용하여 구분하여 사용할 수 있습니다.
가령 transaction_isolation 파라미터를 변경하여 PT-OSC 수행 세션에서만 isolation 를 지정하여 수행할 수 있습니다.
또 다른 사용 예시로 sql_log_bin 시스템 변수의 경우 Replica 인스턴스에서 부터 테이블 변경을 진행한 상황(시나리오) 일 경우, Primary(Master) 인스턴스에서 PT-OSC 로 테이블을 변경할 경우 Replica 인스턴스로 테이블 변경 내역(DDL) 이 복제가 되지 않게 하기 위해서 set-vars 옵션에서 sql_log_bin 를 사용할 수 있습니다.
사용 예시)
--set-vars="sql_log_bin=off"
또는 --set-vars="sql_log_bin=off,transaction_isolation='READ-COMMITTED'"
--no-drop-old-table
테이블 변경 작업 및 신규 테이블과 기존 테이블의 Switch 하여 모든 작업이 완료된 이후, OLD테이블(기존 원본 테이블) 을 삭제하지 않겠다는 옵션입니다.
추가적인 사용 예시
1. 컬럼 추가
pt-online-schema-change --execute --host=localhost \
--defaults-file=/etc/my.cnf \
--charset=UTF8 --port=3306 \
--user=root --ask-pass \
--sleep=0.1 --progress=time,30 \
--alter "add column dept_name varchar(30)" \
D=데이터베이스명,t=테이블명
--ask-pass 실행 시 패스워드를 입력 받는 식이고 --password='패스워드' 를 대신 사용하여 명령어 라인에 직접 입력 할수도 있습니다.
2. 컬럼 수정
--alter "modify column dept_name varchar(30)"
* 구문 이외 나머지 명령어 라인은 그대로 사용
3. 컬럼 이름 수정
--alter "change column dept_name dept_name2 varchar(30)"
4. 컬럼 삭제
--alter "change column dept_name dept_name2 varchar(30)"
5. 인덱스 추가
--alter "add index idx_emp_1(dept_no,dept_name)"
6. 인덱스 삭제
--alter "drop index idx_emp_1"
7. 여러개 변경 작업
여러 Alter 작업을 동시에 진행하려고 할때는 콤마(,) 구문해서 진행을 합니다.
예시)
--alter "modify column col1 varchar(100) NULL DEFAULT NULL, drop column col2"
8. 테이블만 재생성 하려고 할 경우
optimize table 작업을 하려고 하는데 테이블 사이즈가 크거나 여러 다른 이유로 테이블 구조 변경 없이 재생성의 목적으로 사용하려고 할 경우에는 "--alter" 구문 옵션 절만 제외하고 명령어를 PT-OSC 명령어를 수행하면 구조 변경 없이 재생성 후 교체할 수 있습니다.
테이블 변경 테스트
테스트 용 임시 테이블을 생성하여 트랜잭션이 발생되는 상황에서 테이블의 Alter(변경을) PT-OSC 를 통해 진행하겠습니다.
[참고] 트랜잭션 발생 및 부하는 mysqlslap 를 이용하여 진행하도록 하겠습니다.
mysqlslap 정보는 아래 포스팅을 참조하시면 됩니다.
데이터 베이스 생성
mysql> drop database mysqlslap;
mysql> create database mysqlslap DEFAULT CHARACTER SET utf8mb4;
임시 테이블 생성
mysql> create table mysqlslap.tb_test
(col1 int not null auto_increment,
col2 int,
col3 varchar(100),
col4 varchar(100),
primary key(col1)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
mysql> insert into mysqlslap.tb_test (col2,col3,col4) values(1,'A','B');
mysql> commit;
mysqlslap 를 이용하여 데이터 입력 부하 생성
먼저 터미널 2개 중 1개에서 아래와 같이 mysqlslap 을 통해 데이터 생성 부하를 수행합니다.
$ mysqlslap --login-path=dba --delimiter=";" \
--query="Insert into mysqlslap.tb_test(col2,col3,col4) values(1,'AA','BB');" \
--concurrency=300 --iterations=300 --verbose
[참고] mysqlslap 이 일찍 종료(완료) 된다면 --concurrency=300 --iterations=300 2개 값을 더 늘리고 수행을 진행합니다.
--login-path 는 아래 포스팅을 참조하시면 되며 --login-path 외 아래와 같이 socket 이나 host를 지정하여 사용할 수 있습니다.
mysqlslap -u root -p --socket=/tmp/mysql.sock
또는
mysqlslap -u root -p --host=localhost
테이블 변경 수행(OSC)
위에서 데이터 적재가 수행이 되면 다른 터미널에서는 PT-OSC 수행으로 컬럼을 추가합니다.
$ pt-online-schema-change --execute \
--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 col5 varchar(30)" \
D=mysqlslap,t=tb_test
[참고] 복제 구성 환경에서는 실행하는 서버에서 -> Replica(slave) MySQL 로 접속이 가능해야 합니다.
그렇지 못한 환경일 경우(예: AWS RDS for MySQL) 에는 recursion-method=none 옵션 사용을 고려 해보시면 됩니다.
chunk-size: 데이터를 복사하는 단위 인 chunk 사이즈를 줄여서 데이터를 조금씩 복사(데이터 건 수를 줄여서)하여 MySQL의 부하를 낮추거나 IOPS 가 높은 스토리지 시스템을 통해서 새벽 시간때 작업할 경우 빠른 작업 완료를 위해 chunk 사이즈를 증가시켜서 사용할 수도 있는 옵션입니다.
chunk-size-limit=1 : chunk-size 의 max 배수값, 기본값 4.0
--dry-run --print
ALTER 구문이 실제 실행되는 않으며 실행될 구문의 출력과 시뮬레이션이 이루어집니다.
가급적 실제 실행 전 정상 수행여부를 사전에 확인해보는 것이 좋습니다
그리고 --print 와 같이 사용합니다.
컬럼 삭제(drop) 예시 , --dry-run --print
$ pt-online-schema-change --dry-run --print \
--host=localhost \
--defaults-file=/etc/my.cnf \
--chunk-size=500 --chunk-size-limit=1 \
--chunk-index=PRIMARY --progress=time,10 \
--port=3306 --charset=UTF8 \
--user=root --ask-pass \
--alter "drop column col5" \
D=mysqlslap,t=tb_test
사용시 특이사항
1. 용량 확인 필요
이 부분은 특이사항 보다는 PT-OSC 작업 전 꼭 확인을 해봐야 하는 사항으로 PT-OSC 는 복제 테이블을 생성하고 작업을 하기 때문에 작업 대상 테이블(인덱스 포함) 사이즈만큼의 공간이 추가로 필요 합니다. 또한 작업 중에 binary log 또한 더 많이 생성되게 됩니다.
그렇기 때문에 작업 대상 테이블 과 테이블의 인덱스의 사이즈를 먼저 확인이 필요하며, Master 와 Replica 인스턴스(읽기 복제 인스턴스)가 모두 MySQL 에서 사용하는 서버의 용량을 같이 확인이나 체크할 필요가 있습니다.
2. AWS RDS 에서 사용시
PT-OSC 를 사용할 때 SUPER 권한이 필요하지만 AWS RDS 나 기타 다른 서비스형 MySQL 를 이용 시 사용되는 관리자 계정에는 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_...`]
그래서 log_bin_trust_function_creators 시스템 변수를 1(ON) 으로 설정이 필요 합니다.
Dynamic 변수라서 Default Parameter Group 만 사용하고 있지 않다면 바로 적용할 수 있습니다.
AWS RDS 콘솔의 파라미터 그룹에서 log_bin_trust_function_creators 을 1 로 설정 합니다.
2. 3306이 아닌 다른 포트를 사용할 경우
PT-OSC에서는 --port 가 있으나 제대로 해당 옵션이 사용이 안되는 경우가 있는 것 같습니다.
실행 중인 리눅스 환경이 Client 용 시스템이라면 /etc/my.cnf 에 [client] 항목에서 port= 로 지정하는 방법을 사용하거나, 아래와 같이 데이터베이스 와 테이블 명 뒤에 ,P= 를 사용하는 방법이 있습니다.
예시 : D=tdb,t=tb_test,P=9900
3. AWS RDS 나 Aurora 와 같은 환경에서 진행 시 반응이 없거나 이후 실패하는 경우
진행이 중간에 멈춰 있거나 정상 동작하지 않는 경우는 여러 케이스가 있을 수 있습니다.
그 중에서 Replication Discovery 하는 과정에서 오래 지연이 생기는 경우도 있습니다. 기다리다가 실패가 되기도 합니다.
추가로 PT-OSC 사용시 표면적으로 발생되는 에러 없이 진행이 잘 안된다면 아래와 같이 PTDEBUG 를 활성화하고 실행해보면 문제 해결에 도움이 될수 있습니다.
$ PTDEBUG=1 pt-online-schema-change --execute \ ... ... ...
AWS RDS에서 Master/Replica 간에는 내부 IP 로 통신을 하게 되어서 recursion-method 옵션을 기본값으로 사용할 경우 Replica 인스턴스에 접속하지 못하여서 반응이 없다가 실패를 하게 됩니다.
show full processlist 를 통해서 확인되는 Replica 인스턴스의 IP 는 내부 IP 이기 때문에 Replica Instance Discovery 과정에서 지연과 실패가 될 수 있는 것입니다.
AWS RDS 를 포함한 다른 클라우드의 서비스 MySQL 에서 show full processlist 로 확인되는 Replica 의 IP 가 접속 불가능한 내부 IP 인 경우 아래 옵션을 통해서 Replica 인스턴스를 찾지 않게 하여 진행하시면 됩니다.
--recursion-method=none \
또는 --check-slave-lag 옵션을 통해서 명시적으로 --max-lag 값을 체크할 Replica 인스턴스 정보를 넣어서 실행할 수도 있습니다.
pt-online-schema-change 에서 원하는 단계에서 잠시 멈춤(pause) 하거나 진행 과정 중 잠시 멈추기를 원한다면 아래 포스팅을 참고하시면 됩니다.
pt-query-digest
MySQL 기본적으로 제공되는 기능으로 슬로우 로그를 활성화하여 특정 시간 이상 소요된 쿼리를 수집할 수 있으며 제네럴 로그는 실행된 모든 쿼리 수집하는 기능을 MySQL 기본기능으로 제공하고 있습니다.
MySQL 서버가 아주 많이 다수인 환경과 트랜잭션이 수많은 곳이라면 하루에 쌓이는 슬로우 로그와 제너럴 로그의 량은 상당히 많을 것입니다.
이런 많은 로그를 분석한다는 것은 쉽지 않은 일입니다.
예를 들어 10GB 이상의 슬로우 로그 파일에서 실행 시간이 오래 걸린 상위 30개 쿼리의 실행 횟수, 실행 소요 시간 등을 쿼리 별로 파악해야 할때 로그를 일일이 확인하여 사람이 눈으로 분석하고 찾기란 쉽지 않은 일입니다.
pt-query-digest 는 슬로우 로그 파일과 제네럴 로그 파일에서 쿼리의 실행 횟수, 실행 소요 시간, 반환한 행의 수 등에 대한 통계분석 및 리포팅을 쉽게 해주는 도구입니다.
또한 띄어쓰기가 다르거나 대소문자가 다른 SQL 도 하나로 묶어서 리포트를 해줘서 분석에 유용하게 사용할 수 있습니다.
digest - 슬로우 로그 분석
명령어
$ pt-query-digest slow.log
아래와 같은 내용으로 분석이 되게 됩니다.
# 100ms user time, 20ms system time, 29.63M rss, 221.80M vsz
# Current date: Mon Dec 21 17:32:35 2020
# Hostname: acs
# Files: slow.log
# Overall: 4 total, 3 unique, 0.22 QPS, 0.65x concurrency ________________
# Time range: 2020-12-21T13:01:38 to 2020-12-21T13:01:56
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= =======
# Exec time 12s 1s 4s 3s 4s 984ms 4s
# Lock time 199us 0 140us 49us 138us 56us 97us
# Rows sent 54 0 54 13.50 51.63 22.36 0
# Rows examine 54 0 54 13.50 51.63 22.36 0
# Query size 888 35 286 222 284.79 106.37 284.79
<중략...>
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== ================================== ============= =====
# 1 0x11A2BF30F67269B771327463069BED2E 4.4842 38.4% 2 2.2421 0.72 CREATE TABLE wp_wprss_logs `wp_wprss_logs`
# 2 0x19F4C67D62B30ED78AE5000CAF43D53F 4.2199 36.1% 1 4.2199 0.00 INSERT UPDATE wp_options
# 3 0xB082CC8A7989FA25604207749A18CD85 2.9874 25.6% 1 2.9874 0.00 SHOW COLUMNS
<중략...>
# Query 1: 0.25 QPS, 0.56x concurrency, ID 0x11A2BF30F67269B771327463069BED2E at byte 256
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.72
# Time range: 2020-12-21T13:01:48 to 2020-12-21T13:01:56
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= =======
# Count 50 2
# Exec time 38 4s 1s 3s 2s 3s 1s 2s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 64 572 286 286 286 286 0 286
<중략...>
위와 같이 slow 쿼리 로그 파일를 분석하여 report 를 해주게 됩니다.
fingerprint
쿼리 핑거프린트 는 쿼리 구문이 유사한 쿼리들을 그룹화해서 추상화한 쿼리를 의미합니다.
추상화는 쿼리의 숫자 값을 제거하거나 공백을 정규화하는 등의 처리를 의미합니다.
$ pt-query-digest slow.log --type slowlog \
--group-by fingerprint > localhost-slow.result
processlist
--processlist
MySQL(localhost) 에 접속하여 processlist 로 부터 가장 느린 쿼리를 리포팅 받습니다.
$ pt-query-digest --processlist h=localhost --user=root --ask-pass
제네럴 로그 분석
먼저 제네럴 로그를 모두 분석하기에는 용량과 쿼리가 많기 때문에 일정 용량이나 라인으로 파일을 분기한 후 분석을 하거나 1분간만 활성화 후에 수집 후 다시 비활성화 하는 형태로 특정 시간 대로의 파일로 분석이 필요 합니다
(전부는 너무 많습니다)
현재는 비활성화 상태 입니다.
mysql> show variables like '%general%';
+------------------+---------------------------------------------+
| Variable_name | Value |
+------------------+---------------------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/mysql/logs/general_query_all.log |
+------------------+---------------------------------------------+
활성화 후 1분간만 지속 후 다시 off 하도록 하겠습니다.
mysql> set global general_log=1;
<1분 후>
mysql> set global general_log=0;
pt-query-digest 실행
$ cd /usr/local/mysql/logs/
$ pt-query-digest general_query_all.log \
--type genlog --group-by fingerprint
# 220ms user time, 10ms system time, 30.74M rss, 222.96M vsz
# Current date: Mon Dec 21 21:56:07 2020
# Hostname: acs
# Files: general_query_all.log
# Overall: 1.64k total, 115 unique, 0 QPS, 0x concurrency ________________
# Time range: 2020-12-21T18:38:47.496332+09:00 to 2020-12-21T18:39:46.416014+09:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= =======
# Exec time 0 0 0 0 0 0 0
# Query size 217.93k 11 7.75k 136.49 363.48 393.96 88.31
# Profile
# Rank Query ID Response time Calls R/Call V/M It
# ==== =============================== ============= =====
# 1 0x9D781DC859347DF27AEDF19FDD... 0.0000 0.0% 659 0.0000 0.00 SELECT wp_options
# 2 0xDC54D7B9FBD3C2600C61670DB2... 0.0000 0.0% 208 0.0000 0.00 SELECT wp_moove_activity_log
# 3 0x5ADE57F6D81D9B27AD1969307A... 0.0000 0.0% 56 0.0000 0.00 SELECT wp_wfconfig
< 중략... >
# Query 1: 0 QPS, 0x concurrency, ID 0x9D781DC859347DF27AEDF19FDD3A33F1 at byte 298085
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-12-21T18:38:48.007567+09:00 to 2020-12-21T18:39:46.406683+09:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= =======
# Count 40 659
# Exec time 0 0 0 0 0 0 0 0
# Query size 28 61.29k 76 128 95.24 112.70 11.74 92.72
# String:
< 중략... >
# Query 2: 0 QPS, 0x concurrency, ID 0xDC54D7B9FBD3C2600C61670DB2999DFC at byte 216629
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-12-21T18:39:24.404850+09:00 to 2020-12-21T18:39:29.928834+09:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= =======
# Count 12 208
# Exec time 0 0 0 0 0 0 0 0
# Query size 5 12.04k 57 61 59.25 59.77 1.37 56.92
# String:
< 중략... >
--review
최근의 리뷰를 위해 쿼리 종류를 저장합니다. 이미 리뷰된 쿼리 종류는 리포트하지 않습니다.
기본테이블은 percona_schema.query_review 이고, Database D 와 table t DSN 옵션을 사용할 수 있습니다.
--group-by fingerprint 를 같이 사용 해야 합니다.
Review 에 사용될 테이블 생성
mysql> use percona_schema;
mysql> CREATE TABLE query_review (
checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
fingerprint TEXT NOT NULL,
sample TEXT NOT NULL,
first_seen DATETIME,
last_seen DATETIME,
reviewed_by VARCHAR(20),
reviewed_on DATETIME,
comments TEXT );
pt-query-digest review 실행
$ cd /usr/local/mysql/logs/
$ pt-query-digest general_query_all.log --type genlog \
--group-by fingerprint \
--user=root --ask-pass \
--no-create-review-table \
--review D=mysqlslap,t=query_review
확인
mysql> select *
from percona_schema.query_review\G
pt-fingerprint
이 도구는 SQL 문을 핑거프린트 형태로 변환해주는 도구입니다.
동적(Dynamic) 쿼리를 Static(정적) 쿼리로 패턴의 변환하는 도구입니다.
사용 예시)
$ pt-fingerprint --query "select a, b, c from user where id=500"
select a, b, c from user where id=?
$ pt-fingerprint --query "select a, b, c from user where id in (1,2,3)"
select a, b, c from user where id in(?+)
특정 옵션 없이 실행 했을 때 sql문 내의 특정 단어에 숫자가 포함돼 있다면 그 숫자까지 모두 치환합니다.
col1 => col?
$ pt-fingerprint --query "select col1, col2, col3 from user where id in (1,2,3)"
select col?, col?, col? from user where id in(?+)
--match-embedded-numbers 숫자로 이루어진 단어만 치환됩니다
$ pt-fingerprint --match-embedded-numbers --query "select col1, col2, col3 from user where id in (1,2,3)"
select col1, col2, col3 from user where id in(?+)
$ pt-fingerprint --match-embedded-numbers test.sql
select col1, col2, col3 from user where id in(?+)
select a, b, c from user where id=?
pt-visual-explan
쿼리 실행계획 조회 명령어인 explain 명령어의 실행결과를 트리형태로 보여주는 도구입니다.
사용 예시
$ pt-visual-explain <file_containing_explain_output>
$ pt-visual-explain -c <file_containing_query>
$ mysql -u root -p -e "explain select * from tab" | pt-visual-explain
mysql -e 를 사용하여 pt-visual-explain 를 이용
-- 실행 예시
$ mysql --login-path=dba employees -e \
"explain select *
from dept_emp a join employees b
on a.emp_no=b.emp_no;" | pt-visual-explain
-- 결과
JOIN
+- Bookmark lookup
| +- Table
| | table a
| | possible_keys PRIMARY
| +- Index lookup
| key a->PRIMARY
| possible_keys PRIMARY
| key_len 4
| ref employees.b.emp_no
| rows 1
+- Table scan
rows 299423
+- Table
table b
possible_keys PRIMARY
쿼리 구문이 저장된 파일을 통해 실행
$ echo "select * from dept_emp a join employees b on a.emp_no=b.emp_no" > emp.sql
$ pt-visual-explain --connect emp.sql \
--user=root --password=root \
--host=localhost \
--database=employees
MySQL explain 결과를 통해 실행
$ mysql --login-path=dba employees -e \
'explain select *
from dept_emp a join employees b
on a.emp_no=b.emp_no;' >> emp_exp.log
$ pt-visual-explain emp_exp.log
Conclusion
Percona Toolkit 은 포스팅 된 내용 외 더 많은 기능을 포함하고 있습니다.
Percona Toolkit 수년간 개발 발전된 도구로 실제 사용시 더 편하고 유용하게 사용될 수 있도록 개발과 개선되어 왔습니다.
매우 유용하고 DB 작업과 분석 등에 도움이 되는 유틸리티입니다.
상황에 따라서 유용하게 잘 사용한다면 DB 업무에 많은 도움이 되는 좋은 툴이라고 생각됩니다.
Ref link
Percona Toolkit Documentation
Ref Book
DBA를 위한 MySQL 운영 기술
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
