MySQL - Percona Toolkit - ONLINE-SCHEMA-CHANGE

Last Updated on 2월 15, 2021 by 태랑(정현호)



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








필요 패키지 및 Toolkit 설치

* 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 3.2.1







Percorna 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 repliacation 지연(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-onlie-schema-change

pt-onlie-schema-change 는 Percona Toolkit 구성 툴 중에서 가장 많이 언급되고 사용되는 툴중에 하나 입니다.

사실 포스팅에 주요한 이유도 pt-onlie-schema-change 를 확인 해보고자 하는게 가장 큰 이유 였습니다.


MySQL 은 5.6 이하 버전에서는 테이블의 스키마 변경하기 위하여 Alter 를 사용하였을 때 아래와 같이 동시성에서 제약이 생기면서 처리되는 방식으로 진행 되었습니다.


1. 대상 테이블을 Shared Lock 으로 잠급니다
대상 테이블의 읽기(Read)는 허용되지만 DML 등의 쓰기는 제한 됩니다.

2. 빈 임시 테이블(Temp)을 생성 하고 원본 테이블의 데이터를 임시 테이블로 복사 합니다.

3. 2번 작업이 완료 되면 원본 테이블을 삭제 하고 임시 복사 테이블을 원본 테이블 이름으로 rename 하게 됩니다.

4. 신규 테이블에 DML 이 가능하도록 Lock 을 해제 합니다.



[참고] 버전과 테이블 스키마 변경의 종류에 따라 Lock의 종류등은 상세하게 다르게 처리 됩니다.

관련 되어서는 아래 2개 포스팅을 참조하시면 더 많은 정보를 보실 수 있습니다.
 

 



이 처럼 대상 테이블의 쓰기 잠금이 발생하는 경우가 있어서 일반적으로 온라인 상태에서의 테이블 스키마의 변경은 운영중 항상 부담이 되고 DDL이 실행 되는 동안 정상적인 서비스가 불가능한 상태가 될수도 있었습니다.


또한 DDL 타임이 길어짐에 따라 Replication 지연이 발생 될 수도 있습니다.


pt-online-schema-change 는 이러한 MySQL 의 Alter 수행을 자동화(스크립트)하고 온라인으로 처리 될 수있도록 도움을 주는 도구 입니다.


MySQL 5.6 이상 Online DDL 이 도입된 시점에서도 자체적인 Online DDL(Native Online DDL) 대신에 pt-online-schema-change(이하 PT-OSC) 를 사용하는 경우가 많습니다.


여러 이유가 있겠으나 우선 상황에 따라 모든 Operation이 온라인으로 가능하지 않습니다.
또는 Alter 구문 내 다수의 처리를 필요한 경우가 있을수도 있습니다.


또한 대량의 트랜잭션의 발생시 부하에 대해서 더 잘 소화해 낼수 있는지에 대해서도 고려가 되기 때문 입니다.


그리고 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 구문을 적용 합니다.

임시 테이블에 데이터 변영을 위해서 원본 테이블에 트리거를 생성 합니다

원본 테이블의 데이터를 새로운 임시 테이블로 복사 합니다
  -> innore into, Chunk 단위로 복사를 진행하고 옵션(chunk-size) 을 설정하여 사용가능

원본 테이블 과 임시테이블의 이름을 변경 합니다.
  -> 원본테이블의 이름은 _old 로 변경하고 임시 테이블의 이름을 기존 원본테이블의 이름으로 변경

외래키(FK) 제약조건을 적용 합니다.
  -> 외래키 제약조건이 있는 테이블의 경우 외래키 제약조건을 적용

기존 원본 테이블의 삭제 합니다.
  ->  --no-drop-old-table 옵션을 사용하면 원본 테이블 삭제가 되지 않음


* 청크는 한번에 복사하는 레코드 묶음을 말하며 기본값은 1000 레코드 입니다.





pt-online-schema-change 명령어 실행의 예

사용법)

pt-online-schema-change --alter "<alter 명령어>" \
D=<데이터베이스>,t=<테이블> \
--no-drop-old-table \ : 원본테이블을 삭제 하지 않음
--chunk-size=500 \ : default: 1000, 청크단위,입력한 줄 수 만큼 복사됩니다
--chunk-size-limit=600 \ : 지정된 청크 더 크게 복사하지 않습니다.(chunk_size의 오차범위)
--host=DB 호스트 또는 IP \
--port=3306 \
--socket=/tmp/mysql.sock \
--user=계정명 \
--p=패스워드 \ or --ask-pass
--progress=time,30 \
--defaults-file=/etc/my.cnf \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8 \
--execute : 실제로 실행 여부, 지정하지 않으면 dry-run








사용 예시


1. 컬럼 추가

pt-online-schema-change --execute --host=localhost \

--defaults-file=/etc/my.cnf \
--charset=UTF8 --port=3306 \
--user=root --ask-pass \
--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"







테이블 변경 테스트

임시 테이블을 생성하여 트랜잭션이 발생되는 상황에서 테이블의 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 \
--progress=time,10 \
--port=3306 --charset=UTF8 \
--user=root --ask-pass \
--alter "add column col5 varchar(30)" \
D=mysqlslap,t=tb_test


[참고] replication 환경에서는 실행하는 서버에서 -> slave 로 MySQL 접속이 가능해야 합니다.

chunk-size: 데이터를 복사하는 단위 인 chunk 사이즈를 줄여서 데이터를 조금씩 복사(데이터 건 수를 줄여서)하여 MySQL의 부하를 낮추는 방향으로 사용

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







pt-query-digest

MySQL 기본적으로 제공되는 기능으로 슬로우 로그를 활성화 하여 특정 시간 이상 소요된 쿼리를 수집 할 수 있으며 제네럴 로그는 실행된 모든 쿼리 수집하는 기능을 MySQL 기본기능으로 제공 하고 있습니다.


MySQL 서버가 아주 많이 다수인 환경과 트랜잭션이 수많은 곳이라면 하루에 쌓이는 슬로우 로그와 제너럴 로그의 량은 상당히 많을 것 입니다.


이런 많은 로그를 분석 한다는 것은 쉽지 않은 일 입니다.


예를 들어 10GB 이상의 슬로우 로그 파일에서 실행 시간이 오래 걸린 상위 30개 쿼리의 실행 횟수, 실행 소요 시간등을 쿼리 별로 파악해야 할때 로그를 일일이 확인하여 사람이 눈으로 분석 하고 찾기란 쉽지 않은 일 입니다.


pt-query-digest 는 슬로우 로그 파일과 제네럴 로그 파일에서 쿼리의 실행 횟수 , 실행 소요 시간, 반환한 행의 수 등에 대한 통계분석 및 리포팅을 쉽게 해주는 도구 입니다.

또한 띄어쓰기가 다르거나 대소문자가 다른 SQL도 하나로 묶어서 리포트를 해줘서 분석에 유용하게 사용할 수 있습니다.




pt-query-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


MySQL(localhost) 에 접속하여 processlist 로 부터 가장 느린 쿼리를 리포팅 받습니다.


$ pt-query-digest --processlist h=localhost --user=root --ask-pass









pt-query-digest 제네럴 로그 분석

먼저 제네럴 로그를 모두 분석하기에는 용량과 쿼리가 많기 때문에 일정 용량이나 라인으로 파일을 분기한 후 분석을 하거나 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> C
REATE 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-explain

쿼리 실행계획 조회 명령어인 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,emp_no
| +- Index lookup
| key a->PRIMARY
| possible_keys PRIMARY,emp_no
| key_len 4
| ref employees.b.emp_no
| rows 1
+- Table scan
rows 299290
+- 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 운영 기술



연관된 다른 글

답글 남기기