Mysql slow log analysis tool pt-query-digest_MySQL

In windows, mysql5620 used mysqldumpslowpl to analyze slow logs. Today, we see a tool pt-query-digest, which was originally intended to be installed and used on windows. The result is to run C on windows: before ProgramFilesMySQLMySQLServer56b, mysql5.6.20 in windows used mysqldumpslow. pl analyzes slow logs. Today, we see pt-query-digest, which was originally intended to be installed and used on windows. The result is that C is executed on windows: \ Program Files \ MySQL Server 5.6 \ bin> pt-query-digest.pl F: \ mysql \ im-slow.log> F: \ mysql \ slow_report.log, wait for half an hour to complete the results. Certainly, windows is not very supported, so you can use linux with one mind.

1. install perl first, log on to the https://www.perl.org/get.html, and I chose the latest version.

Decompress the package and run it in the installation directory of perl.

[Root @ VM-10 perl-5.22.0] #./Configure-des-Dprefix =/usr/perl-5.22.0-Dusethreads-Uversiononly

Verify whether the installation is successful

[Root @ VM-10] # perl-version
This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi
Copyright 1987-2009, Larry Wall
Perl may be copied only under the terms of either the Artistic License or
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, shocould be found on
This system using "man perl" or "perldoc perl". If you have access to
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

2. download pt-query-digest, https://www.percona.com/downloads/percona-toolkit/, and download percona-toolkit_2.2.14.tar.gz.

Tar zxvf unzipping, to the installation directory/data/percona-toolkit-2.2.14 to execute
[Root @ VM-10] # perl Makefile. PL
[Root @ VM-10] # make
[Root @ VM-10] # make install

3.pt-query-digest/data/im-slow.log>/data/slow_report.log, the result is almost second-out compared to windows half-day seek.

Overall: total number of queries. the preceding example shows a total of 266 queries. Time range: The Time range for query execution. Unique: The number of unique queries, that is, the total number of different queries after the query conditions are parameterized. This example is 55.

#34.3 s user time, 310 ms system time, 44.95 M rss, 196.52 M vsz
# Current date: Thu Aug 13 10:51:55 2015
# Hostname: VM-10.10.X.X
# Files:/data/im-slow.log
# Overall: 64.10 k total, 179 unique, 0.00 QPS, 0.10x concurrency _________
# Time range: 06:52:36 to 09:02:02
# Attribute total min max avg 95% stddev median
#===================================================== ======================================
# Exec time 1405895 s 5S 4899 s 22 s 32 s 111 s 10 s
# Lock time 62995 s 0 2476 s 983 ms 1 ms 48 s 144us
# Rows sent 5.69G 0 44.43 M 93.04 k 5.75 M 1.34
# Rows examine 13.96G 0 693.12 M 228.36 k 44.45 k 3.49 M 0.99
# Query size 37.30 M 6 335.57 k 610.11 918.49 6.98 k 537.02


# Profile
# Rank Query ID Response time callr/Call V/M Item
#===================================================== ======================================
#1 0x0F2E6B958872280E 274452.3245 19.5% 26566 10.3310 SELECT gg_token gg_tenant
#2 0x816F5D9DB1BD38C7 246396.5092 17.5% 15295 31... INSERT gg_login_history
#3 0xA3267D6CEFA1D5E3 203446.8729 14.5% 100 13... SELECT gg_token gg_login_history
#4 0x67a316a2812914df 170491.0463 12.1% 972 175.4023 44... SELECT app_chat_message
#5 0x8BACA5842B7A7490 95999.5908 6.8% 5737 16.7334 37... INSERT app_offline_message
#6 0x2B874BB5CB81DC1F 68234.7809 4.9% 105 649.8551 SELECT gg_token gg_login_history
#7 0x848218FE4D9F7651 51218.6838 3.6% 210 243.8985 SELECT gg_token
#8 0xBF9265005D4F8D80 40299.7591 2.9% 78 516.6636 28... SELECT gg_login_history gg_user gg_user_org gg_org
#9 0xDCDBC481602798A4 39449.2606 2.8% 3193 12.3549 SELECT gg_token


Databases: database name
Users: number of executions by each user (proportion)
Query_time distribution: query time distribution, which indicates the proportion of the interval.
Tables: Tables involved in the query
Explain: example

# Query 1: 0.03 QPS, 0.30x concurrency, ID 0x0F2E6B958872280E at byte 28156661
# This item is wrongly Ded in the report because it matches -- limit.
# Scores: V/M = 2.39
# Time range: 00:05:14 to 11:33:23
# Attribute pct total min max avg 95% stddev median
#===================================================== ======================================
# Count 41 26566
# Exec time 19 274452 s 5S 54 s 10 s 12 s 5S 10 s
# Lock time 0 24 s 64us 40 ms 885us 1 ms 738us 839us
# Rows sent 0 80.00 k 0 13 3.08 4.96 1.29
# Rows examine 8 1.15G 44.73 k 45.59 k 45.43 k 44.45 k 0 44.45 k
# Query size 36 13.78 M 542 544 543.73 537.02 0 537.02
# String:
# Databases im
# Hosts 10.100.252.38 (13301/50%)... 1 more
# Users im
# Query_time distribution
# 1us
# 10us
# 100us
#1 ms
#10 ms
#100 ms
#1 s ##################################### ###################
#10 s + #################################### ############################
# Tables
# Show table status from 'im 'LIKE 'GG _ token' \ G
# Show create table 'im '. 'GG _ token' \ G
# Show table status from 'im 'LIKE 'GG _ tenant' \ G
# Show create table 'im '. 'GG _ tenant' \ G
# EXPLAIN /*! 50100 PARTITIONS */
Select token0 _. token_id as token_id1_119 _, token0 _. tenant_id as tenant_10_119 _, token0 _. account as account2_119 _, token0 _. client_id as client_i3_119 _, token0 _. create_date as create_d4_119 _, token0 _. host as host5_119 _, token0 _. last_auth_date as last_aut6_119 _, token0 _. session_id as session_7_119 _, token0 _. token as token8_119 _, token0 _. user_id as user_id9_119 _ from gg_token token0 _ left outer join gg_tenant tenant1 _ on token0 _. tenant_id = tenant1 _. tenant_id where token0 _. user_id = '00014806a5a95f25ab' and tenant1 _. tenant_id = '0001456d92804b00 '\ G


# Query 2: 0.00 QPS, 0.02x concurrency, ID 0x816F5D9DB1BD38C7 at byte 17658778
# This item is wrongly Ded in the report because it matches -- limit.
# Scores: V/M = 314.49
# Time range: 13:31:25 to 15:11:32
# Attribute pct total min max avg 95% stddev median
#===================================================== ======================================
# Count 23 15295
# Exec time 17 246397 s 5S 2498 s 16 s 32 s 71 s 11 s
# Lock time 51 32142 s 0 2476 s 2 s 125us 71 s 89us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 9 3.55 M 221 258 243.36 246.02 7.37
# String:
# Databases im
# Hosts 10.101.252.35 (7709/50%)... 3 more
# Users im
# Query_time distribution
# 1us
# 10us
# 100us
#1 ms
#10 ms
#100 ms
#1 s ##################################### ########
#10 s + #################################### ############################
# Tables
# Show table status from 'im 'LIKE 'GG _ login_history' \ G
# Show create table 'im '. 'GG _ login_history' \ G
Insert into gg_login_history (login_history_id, username, resource, clientId, login_time, logout_time) VALUES ('930e0b3e-d26b-4cc3-a02f-be0b1009744c ', 'Android', '123456 ', '2017-04-13 23:04:42 ', '2017-04-13 23:07:46') \ G

The most common usage:

(1) directly analyze slow query files:
Pt-query-digest slow. log> slow_report.log
(2) analyze the query in the last hour:
Pt-query-digest -- since = 1 h slow. log> slow_report.log
(3) analyze queries within a specified time range:
Pt-query-digest slow. log -- since '2017-04-17 10:30:00 '-- until '2017-04-17 18:00:00'> slow_report.log
(4) analysis refers to slow queries containing select statements
Pt-query-digest -- filter' $ event-> {fingerprint} = ~ M/^ select/I 'slow. log> slow_report.log
(5) query all full table scans or full join slow queries
Pt-query-digest -- filter' ($ event-> {Full_scan} | "") eq "yes ") | ($ event-> {Full_join} | "") eq "yes") 'slow. log> slow_report.log