MySQL Database optimization pt-query-digest use

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.

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-tableWhen 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 is20,is going to be the slowest20Statement 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
--historySave 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.
--reviewSave 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.
--outputAnalysis result output type, value can beReportStandard Analysis Report)、Slowlog (Mysql slow log)、Json、Json-anon, general useReportTo make it easier to read.
--sinceWhen 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:SSeconds) h ( hours ) m ( minutes ) d ( day ) 12h 12 start counting before hours.
--until   cutoff time, with -since

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

1==/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 total 1690 a query. time range:  The time range of query execution. unique:  The number of unique queries, that is, how many different queries are in total after the query condition is parameterized, the example is 28 total:  total    Min:   Max:  max   avg: average 95%:  arranges all values from small to large, in 95%  that number is generally the most valuable reference. median:  

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

 195% for the purpose of statistics. databases:  library name users: Span style= "color: #444444;" > number of executions per user (%) query_time distribution:  query time distribution ,  length reflects the interval ratio, in this case the query is concentrated in 10ms tables:  example           

(1) Direct parse Slow query file:

Pt-query-digest  mysql-slow.log

(2) 12 Query within the hour:

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 or The 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 to query_review
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)PassTcpdumpCrawlMysqlOfTcpprotocol 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
(10)Analysis Binlog
Mysqlbinlog mysql-bin.000093 > Mysql-bin000093.sql
Pt-query-digest--type=binlog Mysql-bin000093.sql >slow_report10.log
(11) Analysis Generallog
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