[pt-query-digest - Mysql slowquery 분석 ]

안녕하세요 이번글에서는 Mysql or Mariadb에서 쿼리 실행 시간이 설정된 시간 이상으로 걸릴 시 slow query log가 남게됩니다. 하지만 가시성 가독성이 떨어지기 때문에 Percona에서 만든 툴을 이용하여 분석하기 편하게 변환하도록 하겠습니다.

 

PT 쿼리 다이제스트는 MySQL 느린 쿼리를 분석하는 데 사용되는 도구입니다. show processlist 또는 tcpdump에서 캡처한 binlog, 일반 로그, slowlog 및 MySQL 프로토콜 데이터를 분석할 수 있습니다.

분석 결과를 파일로 출력할 수 있습니다. 분석 과정은 먼저 쿼리문의 조건을 매개변수화 한 다음 통계를 위해 매개변수화된 쿼리를 그룹화하는 것입니다. 

각 쿼리 의 실행 시간 , 횟수, 비율을 계산할 수 있습니다. 분석 결과의 도움으로 최적화를 위한 문제를 찾을 수 있습니다.

 

pt-query-digest

slow query 가 많을 경우, 쿼리를 parsing하고 결과를 summary 하여 보여줍니다.

쿼리의 유형, 빈도, 비중 등을 확인할 수 있습니다.

 

pt-summary

하드웨어 정보를 요약해서 보여줍니다. DB서버의 하드웨어 스펙를 뽑아서 저장할 때 좋습니다.

디스크, 메모리 

 

pt-online-schema-change

online alter 를 할 수 있게 해주는 툴입니다.

 

Percona Toolkit 다운로드 : https://www.percona.com/downloads/percona-toolkit/LATEST/

Download Percona Toolkit

현재 최신버전 다운로드 : https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/tarball/percona-toolkit-3.0.5_x86_64.tar.gz

 

## Perl 패키지 설치

[root@localhost]# yum install perl-DBI perl-DBD-MySQL perl-TermReadKey perl perl-IO-Socket-SSL perl-Time-HiRes perl-devel

...(생략)

Installed:
  perl-DBD-MySQL.x86_64 0:4.023-6.el7            perl-DBI.x86_64 0:1.627-4.el7            perl-IO-Socket-SSL.noarch 0:1.94-7.el7            perl-devel.x86_64 4:5.16.3-299.el7_9           

Dependency Installed:
  gdbm-devel.x86_64 0:1.10-8.el7                 glibc-devel.x86_64 0:2.17-325.el7_9           glibc-headers.x86_64 0:2.17-325.el7_9         kernel-headers.x86_64 0:3.10.0-1160.59.1.el7
  libdb-devel.x86_64 0:5.3.21-25.el7             perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7   perl-Data-Dumper.x86_64 0:2.145-3.el7        
  perl-ExtUtils-Install.noarch 0:1.58-299.el7_9  perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7   perl-ExtUtils-Manifest.noarch 0:1.61-244.el7  perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7    
  perl-IO-Compress.noarch 0:2.061-2.el7          perl-IO-Socket-IP.noarch 0:0.21-5.el7         perl-Mozilla-CA.noarch 0:20130114-5.el7       perl-Net-Daemon.noarch 0:0.48-5.el7          
  perl-Net-LibIDN.x86_64 0:0.12-15.el7           perl-Net-SSLeay.x86_64 0:1.55-6.el7           perl-PlRPC.noarch 0:0.2020-14.el7             perl-Test-Harness.noarch 0:3.28-3.el7        
  systemtap-sdt-devel.x86_64 0:4.0-13.el7       

Updated:
  perl.x86_64 4:5.16.3-299.el7_9                                                                                                                                                             

Dependency Updated:
  glibc.x86_64 0:2.17-325.el7_9     glibc-common.x86_64 0:2.17-325.el7_9     libdb.x86_64 0:5.3.21-25.el7     libdb-utils.x86_64 0:5.3.21-25.el7     perl-libs.x86_64 4:5.16.3-299.el7_9    

Complete!

 

 

## percona-toolkit 설치

[root@localhost ~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/tarball/percona-toolkit-3.0.5_x86_64.tar.gz
--2022-03-11 09:28:58--  https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/tarball/percona-toolkit-3.0.5_x86_64.tar.gz
Resolving www.percona.com (www.percona.com)... 172.67.8.157, 104.22.9.28, 104.22.8.28, ...
Connecting to www.percona.com (www.percona.com)|172.67.8.157|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://downloads.percona.com/downloads/percona-toolkit/3.0.5/binary/tarball/percona-toolkit-3.0.5_x86_64.tar.gz [following]
--2022-03-11 09:28:59--  https://downloads.percona.com/downloads/percona-toolkit/3.0.5/binary/tarball/percona-toolkit-3.0.5_x86_64.tar.gz
Resolving downloads.percona.com (downloads.percona.com)... 162.220.4.222, 162.220.4.221, 74.121.199.231
Connecting to downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 10232447 (9.8M) [application/x-gzip]
Saving to: ‘percona-toolkit-3.0.5_x86_64.tar.gz’

100%[===================================================================================================================================================>] 10,232,447  4.89MB/s   in 2.0s   

2022-03-11 09:29:02 (4.89 MB/s) - ‘percona-toolkit-3.0.5_x86_64.tar.gz’ saved [10232447/10232447]




[root@localhost ~]# tar -zxvf percona-toolkit-3.0.5_x86_64.tar.gz
percona-toolkit-3.0.5/
percona-toolkit-3.0.5/MANIFEST
percona-toolkit-3.0.5/COPYING
percona-toolkit-3.0.5/README.md
percona-toolkit-3.0.5/glide.lock
percona-toolkit-3.0.5/INSTALL
percona-toolkit-3.0.5/lib/
percona-toolkit-3.0.5/bin/
percona-toolkit-3.0.5/bin/pt-upgrade
percona-toolkit-3.0.5/bin/glide
percona-toolkit-3.0.5/bin/pt-fingerprint
percona-toolkit-3.0.5/bin/pt-heartbeat
percona-toolkit-3.0.5/bin/pt-mongodb-query-digest
percona-toolkit-3.0.5/bin/pt-pmp
percona-toolkit-3.0.5/bin/pt-fk-error-logger
percona-toolkit-3.0.5/bin/pt-mext
percona-toolkit-3.0.5/bin/pt-slave-find
percona-toolkit-3.0.5/bin/pt-summary
percona-toolkit-3.0.5/bin/pt-show-grants
percona-toolkit-3.0.5/bin/pt-variable-advisor
percona-toolkit-3.0.5/bin/pt-stalk
percona-toolkit-3.0.5/bin/pt-table-sync
percona-toolkit-3.0.5/bin/pt-slave-delay
percona-toolkit-3.0.5/bin/pt-sift
percona-toolkit-3.0.5/bin/pt-archiver
percona-toolkit-3.0.5/bin/pt-mysql-summary
percona-toolkit-3.0.5/bin/pt-table-checksum
percona-toolkit-3.0.5/bin/pt-kill
percona-toolkit-3.0.5/bin/pt-table-usage
percona-toolkit-3.0.5/bin/pt-config-diff
percona-toolkit-3.0.5/bin/pt-slave-restart
percona-toolkit-3.0.5/bin/pt-align
percona-toolkit-3.0.5/bin/pt-online-schema-change
percona-toolkit-3.0.5/bin/pt-fifo-split
percona-toolkit-3.0.5/bin/pt-index-usage
percona-toolkit-3.0.5/bin/pt-ioprofile
percona-toolkit-3.0.5/bin/pt-visual-explain
percona-toolkit-3.0.5/bin/pt-duplicate-key-checker
percona-toolkit-3.0.5/bin/pt-query-digest
percona-toolkit-3.0.5/bin/pt-mongodb-summary
percona-toolkit-3.0.5/bin/pt-find
percona-toolkit-3.0.5/bin/pt-deadlock-logger
percona-toolkit-3.0.5/bin/pt-diskstats
percona-toolkit-3.0.5/Changelog
percona-toolkit-3.0.5/CONTRIBUTE.md
percona-toolkit-3.0.5/Makefile.PL
percona-toolkit-3.0.5/glide.yaml
percona-toolkit-3.0.5/docs/
percona-toolkit-3.0.5/docs/percona-toolkit.pod
percona-toolkit-3.0.5/CONTRIBUTING.md
percona-toolkit-3.0.5/docker-compose.yml



[root@localhost ~]# cd percona-toolkit-3.0.5/


[root@localhost percona-toolkit-3.0.5]# perl ./Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for percona-toolkit


[root@localhost percona-toolkit-3.0.5]# make && make install
cp bin/pt-mysql-summary blib/script/pt-mysql-summary
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mysql-summary
cp bin/pt-mongodb-summary blib/script/pt-mongodb-summary
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mongodb-summary
cp bin/pt-kill blib/script/pt-kill
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-kill
cp bin/pt-online-schema-change blib/script/pt-online-schema-change
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-online-schema-change
cp bin/pt-table-sync blib/script/pt-table-sync
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-table-sync
cp bin/pt-upgrade blib/script/pt-upgrade
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-upgrade
cp bin/pt-table-usage blib/script/pt-table-usage
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-table-usage
cp bin/pt-fifo-split blib/script/pt-fifo-split
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-fifo-split
cp bin/pt-slave-find blib/script/pt-slave-find
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-slave-find
cp bin/pt-ioprofile blib/script/pt-ioprofile
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-ioprofile
cp bin/pt-find blib/script/pt-find
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-find
cp bin/pt-archiver blib/script/pt-archiver
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-archiver
cp bin/pt-deadlock-logger blib/script/pt-deadlock-logger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-deadlock-logger
cp bin/pt-fingerprint blib/script/pt-fingerprint
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-fingerprint
cp bin/pt-mext blib/script/pt-mext
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mext
cp bin/pt-slave-restart blib/script/pt-slave-restart
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-slave-restart
cp bin/pt-summary blib/script/pt-summary
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-summary
cp bin/pt-fk-error-logger blib/script/pt-fk-error-logger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-fk-error-logger
cp bin/pt-table-checksum blib/script/pt-table-checksum
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-table-checksum
cp bin/pt-query-digest blib/script/pt-query-digest
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-query-digest
cp bin/slow.log blib/script/slow.log
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/slow.log
cp bin/pt-show-grants blib/script/pt-show-grants
...(중략)
Installing /usr/local/bin/pt-sift
Installing /usr/local/bin/glide
Installing /usr/local/bin/pt-diskstats
Installing /usr/local/bin/pt-visual-explain
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

 

 

 

## slow query 분석 실행 -> 에러발생

(pt-query-digest {slow쿼리파일명} > {output 파일명})

[root@localhost bin]# ./pt-query-digest --type='slowlog' kim.slow.log > slow.log
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 ./pt-query-digest line 2470.
BEGIN failed--compilation aborted at ./pt-query-digest line 2470.

 

 

 

※ perl-Digest-MD5 패키지  가 없기 때문에 에러발생 -> 설치 진행

##  perl-Digest-MD5 설치

[root@localhost bin]# yum -y install perl-Digest-MD5
...
Total download size: 53 k
Installed size: 82 k
Downloading packages:
(1/2): perl-Digest-1.17-245.el7.noarch.rpm                            |  23 kB  00:00:00     
(2/2): perl-Digest-MD5-2.52-3.el7.x86_64.rpm                          |  30 kB  00:00:00     
---------------------------------------------------------------------------------------------
Total                                                        226 kB/s |  53 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Digest-1.17-245.el7.noarch                                           1/2
  Installing : perl-Digest-MD5-2.52-3.el7.x86_64                                         2/2
  Verifying  : perl-Digest-1.17-245.el7.noarch                                           1/2
  Verifying  : perl-Digest-MD5-2.52-3.el7.x86_64                                         2/2

Installed:
  perl-Digest-MD5.x86_64 0:2.52-3.el7                                                        

Dependency Installed:
  perl-Digest.noarch 0:1.17-245.el7                                                          

Complete!

 

 

## slow query 분석 재실행

## 분석할 slow query 를 해당 디렉토리로 옮깁니다
[root@localhost bin]# cp /root/kim.slow.log /root/percona-toolkit-3.0.5/bin/

[root@localhost bin]# ./pt-query-digest --type='slowlog' kim.slow.log > slow.log
kim.slow.log:   4% 10:04 remain
kim.slow.log:   9% 09:50 remain
kim.slow.log:  14% 09:01 remain
kim.slow.log:  19% 08:28 remain
kim.slow.log:  23% 07:56 remain
kim.slow.log:  28% 07:22 remain
kim.slow.log:  33% 06:48 remain
kim.slow.log:  38% 06:16 remain
kim.slow.log:  43% 05:44 remain
kim.slow.log:  48% 05:17 remain
kim.slow.log:  53% 04:46 remain
kim.slow.log:  58% 04:19 remain
kim.slow.log:  63% 03:46 remain
kim.slow.log:  67% 03:19 remain
kim.slow.log:  73% 02:46 remain
kim.slow.log:  78% 02:14 remain
kim.slow.log:  83% 01:43 remain
kim.slow.log:  87% 01:14 remain
kim.slow.log:  92% 00:44 remain
kim.slow.log:  97% 00:14 remain

 

 

 

## output으로 나온 파일을 local로 빼서 확인하면 아래와 같이 분석하기 편하게 Top slow query, 실행 시간 등 나옵니다.

아래 Top 순으로 쿼리 확인 가능합니다.