MySQL slow query (ii)-Pt-query-digest detailed slow query log

First, Introduction

Pt-query-digest is a tool for analyzing slow queries for MySQL, which analyzes Binlog, general log, Slowlog, It can also be analyzed using showprocesslist or MySQL protocol data crawled by tcpdump. The analysis results can be exported to a file, the analysis process is the parameters of the query statement, and then the parameterized query after the grouping statistics, statistics of the query execution time, frequency, percentage, etc., can be used to identify problems with the analysis of the optimization.

Second, installation Pt-query-digest

1. Download page:https://www.percona.com/doc/percona-toolkit/2.2/installation.html
Modules for 2.perl

Yum install-y perl-cpan perl-time-hires

3. Installation Steps
Method One: RPM installation

cd/usr/local/srcwget Percona. Com/get/percona-toolkit.  -y percona-toolkit.rpm

Tool installation directory in:/usr/bin

Method Two: Source code installation

cd/usr/local/srcwget percona.com/get/percona-toolkit.tar.gztar zxf Percona-  TOOLKIT.TAR.GZCD Percona-toolkit-2.2.  + perl makefile.pl PREFIX=/usr/local/percona-&& make install

Tool installation directory in:/usr/local/percona-toolkit/bin

4. Introduction to each tool usage (Details:https://www.percona.com/doc/percona-toolkit/2.2/index.html)
(1) Slow query log analysis statistics

Pt-query-digest/usr/local/mysql/data/slow.log

(2) Server Summary

(3) Server disk monitoring

(4) MySQL Service status summary

Iii. Pt-query-digest Grammar and important options

pt-query-Digest [OPTIONS] [FILES] [DSN]--create-review-table when using--when the review parameter outputs the analysis results to a table, it is automatically created without a table. --create-history-table when using--when the history parameter outputs the analysis results to a table, it is created automatically if no tables are found. --Filter the input slow query is matched and filtered by the specified string before parsing--limit limit the output percent or number, the default value is 20, the slowest 20 statement output, if 50% is the total response time from the largest to the small sort, output to the sum of 50%location Cutoff. --host MySQL server address--user MySQL username--password MySQL user password--history the analysis results to the table, the analysis results are more detailed, the next use--history, if the same statement exists, and the query is in a different time period and history table, it is recorded in the data table, you can query the same checksum to compare the historical changes of a type of query. --review Save the analysis results to a table, this analysis only to the query criteria parameterized, a type of query a record, relatively simple. When next use-Review, if there is the same statement analysis, it is not recorded in the data table. --output analysis result output type, values can be report (standard analysis Reports), Slowlog (Mysql slow log), JSON, json-Anon, the report is generally used for readability. --since from when to parse, the value is a string, can be a specified "yyyy-mm-DD [HH:MM:SS] "format a point in time, or it can be a simple time value: s (seconds), H (Hours), M (minutes), D (days), such as 12h means starting from 12 hours ago statistics. --until cut-off time, with-since can be analyzed for a period of time slow query.

Iv. analysis of pt-query-digest output results

Part I: Overall statistical results
Overall: How many queries are there in total?
Time range: The timeframe for query execution
Unique: The number of unique queries, that is, after the query criteria are parameterized, the total number of different queries
Total: min: min Max: Max avg: Average
95%: Put all the values from small to large, the position is located in the number 95%, this number is generally the most valuable reference
Median: Median, all values from small to large, position in the middle of the number

# The tool performs log analysis of user time, system time, physical memory footprint, virtual memory footprint # 340ms user times, 140ms system times,23.99MRss203.11Mvsz# Tool Execution Time # current Date:fri Nov -  Geneva:Panax Notoginseng: -  .# Run profiling tool hostname # hostname:localhost.localdomain# parsed file name # files:slow.log# Total number of statements, number of unique statements, QPS, concurrency # Overall:2Total2Unique0.01QPS,0. 01x concurrency ________________# The time range of the log record # times range: .- One- A  .: .: -To .: One: +# Attribute Total minimum maximum average the%Standard Medium # Attribute total min Max avg the%StdDev median#============     ======= ======= ======= ======= ======= ======= =======# statement Execution times # Exec time 3s 640ms 2s 1s 2s 999ms 1s# lock occupancy # lock Time 1ms 01ms 723us 1ms 1ms 723us# number of rows sent to the client # rows sent5       1       4    2.50       4    2.12    2.50# SELECT statement Scan lines # rows examine186.17k0 186.17k the.09k186.17k131.64k thethe number of characters queried by the. 09k# # query Size455       the      the  227.50      the  300.52  227.50

Part II: Query the results of grouping statistics
Rank: The ranking of all statements, by default in descending order by query time, specified by--order-by
Query ID: The ID of the statement (remove extra space and text characters, calculate hash value)
Response: Total response time
Time: The total percentage of the query in this analysis
Calls: number of executions, that is, the total number of this analysis of this type of query statements
R/call: Average response time per execution
V/m: Ratio of response time Variance-to-mean
Item: Query Object

 # profile# Rank Query ID Response time Calls R /call v/m item#  = = ================== ============= ===== ====== ===== ===============#  1  0xf9a57dd5a41825ca  Span style= "COLOR: #800080" >2.0529  76.2 % 1  2.0529  0.00   select#  2  0x4194d8f83f4f9365  0.6401  23.8 % 1  0.6401  0.00  Select Wx_member_base 

Part III: Detailed statistical results for each query
Detailed statistical results from the following query, the top table lists the number of executions, maximum, minimum, average, 95% and other purposes of statistics.
ID: The ID number of the query, and the reference ID corresponding to the
Databases: Database name
Users: Number of times each user executes (%)
Query_time Distribution: The query time distribution, the length reflects the interval ratio, in this case 1s-10s between the number of queries is more than 10s twice times.
Tables: Tables involved in the query
Explain:sql statements

# Query1:0QPS, 0x concurrency, ID0XF9A57DD5A41825CAAtbyte 802______# This item isIncludedinchThe report because it matches--limit.# scores:v/M =0.00# time Range:all events occurred at .- One- A  .: One: +# Attribute pct Total min max avg the%StdDev median#============ === ======= ======= ======= ======= ======= ======= =======# Count -       1# Exec time the2s 2s 2s 2s 2s02s# Lock time0       0       0       0       0       0       0       0# Rows sent -       1       1       1       1       1       0       1# Rows Examine0       0       0       0       0       0       0       0# Query Size3       the       the       the       the       the       0       the# string:# Databases test# Hosts192.168.8.1# Users mysql# query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s ###################### ########################################### 10s+# EXPLAIN/*!50100 Partitions*/SelectSleep2) \g

V. Examples of usage

1. Direct analysis of slow query files:

Pt-query-digest  slow.log > Slow_report.log

2. Analyze queries within the last 12 hours:

Pt-query-digest  --since=12h  slow.log > Slow_report2.log

3. Analyze queries within a specified time range:

' 2017-01-07 09:30:00 ' '  2017-01-07 10:00:00'> > Slow_report3.log

4. Parsing refers to slow queries with SELECT statements

' $event->{fingerprint} =~ m/^select/i ' slow.log> slow_report4.log

5. Slow query for a user

' ($event->{user} | | "") =~ m/^root/i' slow.log> slow_report5.log

6. Query all all full-table scans or slow queries

' (($event->{full_scan} | | "") eq "yes") | | (($event->{full_join} | | "") eq "yes")' slow.log> slow_report6.log

7. Save the query to the Query_review table

Pt-query-digest--user=root–password=abc123--review  h=localhost,d=test,t=query_review--create-review-table  Slow.log

8. Save the query to the Query_history table

Pt-query-digest  --user=root–password=abc123--review  h=localhost,d=test,t=query_history--create-review- table  slow.log_0001pt-query-digest  --user=root–password=abc123--review  h=localhost,d=test , T=query_history--create-review-table  slow.log_0002

9. Crawl the MySQL TCP protocol data via tcpdump, and then analyze

65535  + 3306 > mysql.tcp.txtpt-query-digest--type tcpdump mysql.tcp.txt> slow_report9.log

10. Analysis Binlog

Mysqlbinlog Mysql-bin. 000093 > mysql-bin000093.sqlpt-query-digest  --type=binlog  mysql-bin000093.sql > Slow_report10.log

11. Analysis of General Log

Pt-query-digest  --type=genlog  localhost.log > Slow_report11.log

Previous:mysql slow query (a)-turn on slow query

Resources:
Http://www.cnblogs.com/zhanjindong/p/3472804.html
http://blog.csdn.net/seteor/article/details/24017913

MySQL slow query (ii)-Pt-query-digest detailed slow query log