Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use pt-query-digest. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Grammar 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 automatically created if there is no table.

-- create-history-table when you use the-- history parameter to output the analysis results to a table, it is automatically created if there is no table.

-- filter matches and filters the input slow query according to the specified string and then analyzes it

-- limit limits the percentage or number of output results. The default value is 20, that is, the output of the slowest 20 statements. If it is 50%, it is sorted by the proportion of the total response time from the largest to the lowest, and the total output reaches the 50% position.

-- host mysql server address

-- user mysql user name

-- password mysql user password

-- history saves the analysis results to a table, and the analysis results are more detailed. The next time you use-history, if the same statement exists and the time interval of the query is different from that in the history table, it will be recorded in the data table. You can compare the historical changes of a certain type of query by querying the same CHECKSUM.

-- review saves the analysis results to the table. This analysis only parameterizes the query conditions, and it is relatively simple to query one record for each type of query. The next time you use-- review, if the same statement parsing exists, it will not be recorded in the data table.

-- the output type of output analysis results. The values can be report (standard analysis report), slowlog (Mysql slowlog), json, json-anon. Generally, report is used for ease of reading.

-- when does since start to analyze. The value is a string. It can be a specified time point in a "yyyy-mm-dd [hh:mm:ss]" format, or a simple time value: s (seconds), h (hours), m (minutes), d (days). For example, 12 hours means that statistics started 12 hours ago.

-- until deadline. With-since, you can analyze slow queries over a period of time.

two。 Standard analysis report explanation

Part I: overall statistical results, as shown in the following figure

Overall: how many queries are there? the above example is a total of 266 queries.

Time range: the time range within which the query is executed.

Unique: the unique number of queries, that is, the total number of different queries after the query conditions have been parameterized, in this case 55.

Total: total min: minimum max: maximum avg: average

95%: the number that arranges all values from small to large, located at 95%, which is generally the most valuable for reference.

Median: median, arranging all values from small to large, in the middle of the number.

Part II: query grouping statistical results, as shown in the following figure

As can be seen from the above figure, this part parameterizes and groups the queries, and then analyzes the execution of all kinds of queries, and the results are sorted according to the total execution time, from large to small.

Response: total response time.

Time: the total time percentage of this query in this analysis.

Calls: the number of times executed, that is, the total number of query statements of this type in this analysis.

R/Call: average response time per execution.

Item: query object

Part III: detailed statistical results of each query, as shown in the following figure:

As can be seen from the above figure, the detailed statistical results of query No. 12, the top table lists the statistics of the number of times of execution, maximum, minimum, average, 95% and so on.

Databases: library name

Users: number of times performed by each user (percentage)

Query_time distribution: query time distribution, length reflects the interval proportion. In this case, the number of queries between 1s-10s is twice as large as that of more than 10 seconds.

Tables: the tables involved in the query

Explain: exampl

3. Usage example

(1) analyze slow query files directly:

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

(2) analyze the enquiries made in the last 12 hours:

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

(3) analyze the queries within the specified time range:

Pt-query-digest slow.log-- since '2014-04-17 09 until' 2014-04-17 10 until'> slow_report3.log

(4) Analysis refers to slow queries with select 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 full join slow queries

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_ table

Pt-query-digest-- user=root-password=abc123-- review hobbies localhost slow.log slow.log

(8) Save the query to the query_ query table

Pt-query-digest-- user=root-password=abc123-- review hobbies localhost _ history--create-review-table slow.log_20140401

Pt-query-digest-- user=root-password=abc123--review hobbies localhostDelitedpaper thumbquerylocalhostwafletable slow.log_20140402

(9) grab the tcp protocol data of mysql through tcpdump, and then analyze

Tcpdump-s 65535-x-nn-Q-tttt-I any-c 1000 port 3306 > mysql.tcp.txt

Pt-query-digest-- type tcpdump mysql.tcp.txt > slow_report9.log

(10) analyze binlog

Mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql

Pt-query-digest-- type=binlog mysql-bin000093.sql > slow_report10.log

(11) analyze general log

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

The above is the editor for you to share how to use pt-query-digest, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.