The use of MySQL slow query analysis work pt-query-digest

First, simple installation

wget percona.com/get/pt-query-digestchmoe u+x pt-query-digest

Second, simple use

More

Reference article: Http://blog.itpub.net/29773961/viewspace-2024992/pt-query-digest is a set of tools to help DBAs manage MySQL Percona-toolkit , developed by Percona company. For log analysis, this is used to analyze slow log, in addition to the analysis of binary log, and general log.

First, installation

Go to the official website to select the corresponding version and platform:



Here I use the percona-toolkit-2.2.16.tar.gz, directly decompression use.

1. $ tar zxvf percona-toolkit-2.2.16.tar.gz
2. $ CD Percona-toolkit-2.2.16/bin
3. $./pt-query-digest--version

second, the basic use

Basic syntax:

PT-Query-[OPTION... [FILE]        

1. Complete analysis

1. $ pt-query-digest slow_log > Slow_report

2, analysis of the last 1 hours of log generated \ analysis from--since to--until generated log

1. $ pt-query-digest--since=1h slow_log > Slow_report2
3. $ pt-query-digest--since= ' 2016-01-01 00:00:00 '--until= ' 2016-02-01 ' Slow_log > Slow_report3

3, for a certain type of statement analysis, such as Select

1. $ pt-query-digest--filter ' $event->{fingerprint} =~ m/^select/i ' Slow.log > Slow_report4

4, for a user analysis, such as Dbback

1. $ pt-query-digest--filter ' ($event->{user} | | "") =~ m/^dbback/i ' slow.log> slow_report5

5. Output the analysis results to Mysql-server:

1. $ pt-query-digest--review H=localhost,d=test,t=review Slow.log

For more usage, refer to the Official Handbook


III. Analysis of output results

The first part:
Overall:
The number of queries that 126.72k is logged in, where 1k=10^3
140 the number of times to go back, that is, the total number of queries
Time range:
Extract the related statements from the log from 2016-02-01 00:02:12 to 2016-03-02 03:02:30
Next is the time and transport traffic statistics:
Total: Totals, min: Min, max: Max, avg: Average, StdDev: standard Variance, Median: median



Part II:
Rank: Rank, in the third part can be used to match specific statements
Query ID of the id:16 binary number, in the third part can be used to match the specific statement
Response: Total response time, which is the total execution time of this article
Calls: The total number of queries, that is, how many levels a piece is executed in total
R/calls: Average execution time for this article
v/m: Variance mean-value ratio
Item: Statement Overview



Part III:
Database: DB name
Host of Hosts:db
Users: DB user to execute the statement
Query_time Distribution: The number of times the execution time of this statement is distributed, # # #越多代表越多的执行时间在这个范围




Other:
If you want to analyze the results of visualization, you can combine anemometer and other tools to achieve.



Reference text Document :
Https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

The use of MySQL slow query analysis work pt-query-digest