MySQL Database optimization pt-query-digest use

I. Introduction of PT-QUERY-DIGEST Tools

Pt-query-digest is a tool for analyzing MySQL slow query, which can analyze Binlog, general log, Slowlog, or through show Processlist or the MySQL protocol data captured by Tcpdump is analyzed. 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, the grammar of Pt-query-digest

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

--create-review-tablewhen using--reviewwhen the parameter outputs the analysis results to a table, it is created automatically without a table.
--create-history-table when using--historywhen the parameter outputs the analysis results to a table, it is created automatically without a table.
--filter A slow query of input is matched and filtered by a specified string before analysis
--limitlimit the percentage or number of output results, the default value is,is going to be the slowest -statement output, if it is50%The total response time ratio from the largest to the small sort, the output to the sum reached50%location Cutoff.
--host mysql server address
--user MySQLUser name
--password MySQLUser Password
--history Save the analysis results to a table, the analysis results are more detailed, the next time you use--history, if the same statement exists, and the query is in a different time period than the history table, it is recorded in the data table and can be queried by querying the sameCHECKSUMto compare the historical changes of a certain 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. The next time you use--review, if the same statement analysis exists, it is not recorded in the data table.
--output analysis result output type, value can beReport (Standard Analysis Report),slowlog (Mysql slow log),JSON,Json-anon, general use Reportto make it easier to read.
--since when to start parsing, the value is a string, which can be a specified"Yyyy-mm-dd [Hh:mm:ss]"The time point of the format, or it can be a simple time value:S (seconds),H (hours),m (minutes),D (days), such as12hit means that from Ahours ago to start counting.
--until closing time, with-sinceYou can analyze slow queries over a period of time.

Third, the installation process

http://www.percona.com/downloads/percona-toolkit/# tar zxvf percona-toolkit-2.2.4.tar.gz
# CD percona-toolkit-2.2.4
# perl makefile.pl
# make && make test && make install

Four, analysis slow query log

Since analysis slow log, of course need to turn on slow log query, and configure the database configuration, edit my.cnf

Under Mysqld, set the following

Long_query_time = 1
Slow_query_log = On
Slow_query_log_file =/usr/local/mysql/var/mysql-slow.log

Then restart the service to generate slow logs

The simplest way to use pt-query-digest pt-query-digest Mysql-slow.log

The overall statistical results are as follows

Overall:how many queries are in total, the above example being a total1690a query. Time Range:the time range for query execution. Unique:The number of unique queries, that is, after the query criteria are parameterized, the total number of different queries, the example is. Total:Totalmin:MinimumMax:Maximumavg:Average95%:Place all values from small to large, located in95%The number , which is generally the most valuable reference. Median:The median, which arranges all the values from small to large, is positioned in the middle of the number. 

Query Grouping Statistics results

By visible, 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. the total response time.   the total time of the query in this analysis is accounted for.   number of executions, that is, the total number of this analysis of this type of query statements.   The average response time for each execution.     querying objects

Detailed statistical results for each section

1   95%   databases:   library name  users:   query_time distribution:   query time distribution    10ms   tables:   tables that are involved in the query  explain:   example  

Usage examples

(1) Direct Parse Slow query file: pt-query-digest Mysql-slow.log

(2)Analyze Recent Ahours of query:

Pt-query-digest--since=12h slow.log > Slow_report2.log(3)analyze queries within a specified time range:Pt-query-digest slow.log--since ' 2014-10-29 09:30:00 '--until ' 2014-11-07 10:00:00 ' > >slow_report3.log(4)analysis contains onlySelectslow query for statements
Pt-query-digest--filter ' $event->{fingerprint} =~m/^select/i ' slow.log> slow_report4.log
(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 orFulljointhe slow query
Pt-query-digest--filter ' (($event->{full_scan} | | "" EQ "Yes" | | (($event->{full_join} | | "EQ" yes "' slow.log> Slow_report6.log
(7) Save the query toQuery_reviewTable
Pt-query-digest--user=root--password=xxxxxx--review h=127.0.0.1,d=test,t=query_review--create-review-table Slow.log(8)Save the query toquery_historyTable
Pt-query-digest--user=root--password=xxxxxx--review h=127.0.0.1,d=test,t=query_ history--create-review-table slow.log_20141107
(9)throughtcpdumpCrawlMySQLof theTCPprotocol data, 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
(Ten)AnalysisBinlog
Mysqlbinlog mysql-bin.000093 > Mysql-bin000093.sql
Pt-query-digest--type=binlog Mysql-bin000093.sql >slow_report10.log
(one)AnalysisGenerallog
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

MySQL Database optimization pt-query-digest use