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

This article will explain in detail what is the use of the pt-query-digest command in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Pt-query-digest is a tool for analyzing mysql slow queries. It can analyze binlog, General log, slowlog, show processlist commands or tcpdump.

Grab the MySQL protocol data for analysis. You can output the analysis results to a file. The analysis process is to first parameterize the conditions of the query statement, and then group the parameterized queries.

Statistics, statistics of the execution time, times, proportion of each query, we can use the analysis results to find out the problems for optimization. By default, the tool analyzes slow log and reports which query is the slowest.

View help:

$pt-query-digest-help

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

-- host # specify MySQL address

-- port # specifies the MySQL port

-- socket # specifies the MySQL SOCK file

-- user # specify MySQL user

-- password # specify the MySQL password

-- type # specifies the type to be analyzed. The default is slowlog, as well as binlog,general log, etc.

-- charset # specify character set

-- 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, which 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.

-- 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. The next time you use-- review, if there is the same statement analysis, it will not be recorded in the data table.

-- history # saves the analysis results to the 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 with-- CHECKSUM.

-- since # when to start the analysis. 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, 12h means that statistics started 12 hours ago.

-- until # deadlines, with-- since can analyze slow queries over a period of time.

-- log # specifies the output log file

-- output # Analysis result output type, values can be report (standard analysis report), slowlog (Mysql slowlog), json, json-anon; generally use report, for ease of reading

-- create-review-table # when the analysis result is output to a table using the-- review parameter, it is automatically created if there is no table

-- create-history-table # when the analysis result is output to a table using the-- history parameter, it is automatically created if there is no table

Examples of use:

1) pt-query-digest analysis slow query log

$pt-query-digest-report slow.log

2) report slow queries for the last half hour

$pt-query-digest-- report-- since 1800s slow.log

3) report slow queries for a period of time

$pt-query-digest-- report-- since '2019-02-10 21 since' 48 since 59'--until '2019-02-16 02 33 slow.log

4) report slow queries with only select statements

$pt-query-digest-- filter'$event- > {fingerprint} = ~ m / ^ select / i'slow.log

5) report slow queries for a user

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

6) report 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

7) Save the query to the query_ table

$pt-query-digest-- user=root-password=123456-- review hobbies localhostDemptQuery review-- create-review-table slow.log

8) Save the query to the query_ query table

$pt-query-digest-- user=root-password=123456-- history hobbies localhost create-history-table slow.log

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_report.log

10) analyze binlog

$mysqlbinlog mysql-bin.000001 > mysql-bin000001.sql

$pt-query-digest-- type=binlog mysql-bin000001.sql > slow_report.log

11) analyze general log

$pt-query-digest-- type=genlog general.log > slow_report.log

This is the end of this article on "what is the use of pt-query-digest commands in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.