Pt-query-digest Practice (RPM)

MySQL slowlog usage and introduction

• Slow_query_log =1-----Open
• Slow_query_log_file =/data/mysql_data/node-1/mysql-slow.log--Position
• Long_query_time=5-----Threshold Time
• Log_output = {File|table|none}

Analyze slow queries with pt-query-digest

• Use tcpdump to crawl the MySQL protocol package (server-side capture, the statement that accesses the server database in the network)
• • Tcpdump-s 65535-x-nn-q-tttt-i any-c-Port 3306 > Mysql.tcp.txt
• Using the Pt-query-digest analysis package (for the analysis of unfamiliar libraries, what happens now)
• • Pt-query-digest--type tcpdump--limit--watch-server 192.168.1.11:3306 mysql.tcp.txt > Mysql.slow.sql
• Analyze slow logs with pt-query-digest
• • Pt-query-digest--type slowlog--order-by rows_examined:sum/--order-by query_time:cnt/tmp/mysql-slow.log >/tmp/ Slow3306.sql

pt-query-digest Query Log Analysis tool (GO) 1. Introduction to Tools 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. Pt-query-digest is a Perl script that can only be downloaded and empowered to execute.

wget percona.com/get/pt-query-chmod u+x pt-query-diges

2. Syntax and important options

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table when you use the--review parameter to output the analysis results to a table, it is created automatically if no tables are available.
--create-history-table when you use the--history parameter to output the analysis results to a table, it is created automatically if no tables are available.
--filter the input slow query is matched and filtered by the specified string for analysis
--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, the output to the sum of 50% position cutoff.
--host MySQL server address
--user MySQL user name
--password MySQL user password
--history the analysis results to the table, the analysis results are detailed, the next time you use--history, if the same statement exists, and the query is in a different time period and history table, it will be recorded in the data table, You can compare the historical changes of a type of query by querying the same checksum.
--review Save the analysis results to a table, this analysis only to the query criteria parameterized, a type of query a record, relatively simple. The next time you use--review, if you have the same statement analysis, it will not be recorded in the datasheet.
--output analysis Results output types, values can be report (standard analysis Reports), Slowlog (Mysql slow log), JSON, Json-anon, general use report for readability.
--since from when to parse, the value is a string, can be a specified "Yyyy-mm-dd [hh:mm:ss]" format 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.

3. Interpretation of the standard analysis report

The first part of the overall statistical results , such as

Overall: How many queries are in total, the previous example being a total of 266 queries. Time range: The timeframe for query execution.
Unique: The number of unique queries, that is, after the query criteria are parameterized, a total of how many different queries, the example is 55.
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, arranges all values from small to large and positions in the middle number.Part II: Query the results of grouping statistics,As can be seen, this part of the query is parameterized and grouped, and then the execution of various types of query analysis, the results by the total execution time, from large to small sort. Response: Total response time.
Time: This query is accounted for in the overall period of the analysis.
Calls: number of executions, that is, the total number of this analysis of this type of query statements.
R/call: The average response time per execution.
Item: Query Object

Part III: Detailed statistical results for each querySuch as:

By visible, the detailed statistics of the 12th query, the top table lists the number of executions, the maximum, minimum, average, 95% and other statistical purposes. Databases: library Name users: number of executions per user (%) query_time Distribution: Query time distribution, length reflects the interval ratio, in this case 1s-10s between the number of queries is twice times more than 10s. Tables: Table Explain that is involved in the query: example

4. Usage examples

(1) Direct analysis of slow query files:pt-query-digest slow.log > Slow_report.log(2) Analysis of queries within the last 12 hours:

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

(3) analysis of queries within the specified time range:

Pt-query-digest slow.log--since ' 2014-04-17 09:30:00 '--until ' 2014-04-17 10:00:00 ' > > Slow_report3.log

(4) analysis refers to a slow query that contains a SELECT statement
pt-query-digest--filter ' $event->{fingerprint} =~ m/^select/i ' slow.log> slow_report4.log

Span style= "FONT-SIZE:14PX;" > (5) Slow query for a user
pt-query-digest--filter "($event->{user} | | "") =~ m/^root/i ' slow.log> slow_report5.log

(6) query all full-table scans or Slow query for join
pt-query-digest--filter "(($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_20140401
pt-query-digest--user=root–password=abc123--review h=localhost,d=test,t=query_history--create-review-table slow.log_20140402

(9) Crawl the MySQL TCP protocol data via tcpdump and then analyze
tcpdump-s 65535-x-nn-q-tttt-i any-c-Port 3306 > Mysql.tcp.txt
pt-query-digest--type tcpdump mysql.tcp.txt> slow_report9.log

(10) Analysis Binlog
mysqlbinlog mysql-bin.000093 > Mysql-bin000093.sql
pt-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

Official document: Http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

Pt-query-digest Practice (RPM)