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

Editor to share with you how to use MySQL slow log analysis tool pt-query-digest, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

I. installation

Go to the official website to select the corresponding version and platform:

Here I use percona-toolkit-2.2.16.tar.gz, which is directly decompressed and used.

Pt-query-digest [OPTION...] [FILE]

1. Complete analysis

$pt-query-digest slow_log > slow_report

2. Analyze the logs generated in the last hour\ analyze the logs generated from-- since to-- until

$pt-query-digest-- since=1h slow_log > slow_report2

$pt-query-digest-- since='2016-01-01 0000 until='2016-02-01' slow_log > slow_report3

3. Analyze certain kinds of sentences, such as select

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

4. Analyze a certain user, such as dbback

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

5. Output the analysis results to mysql-server:

$pt-query-digest-- review hobby localhost slow.log slow.log

For more usage, please refer to the official manual.

III. Analysis of output results

Part one:

Overall:

126.72k the number of queries recorded, where 1k = 10 ^ 3

140 times after deduplication, that is, the total number of queries

Time range:

Extract the relevant statements from 00:02:12 on 2016-02-01 to 03:02:30 on 2016-03-02 from the log.

Here are the time and traffic statistics:

Total: total, min: minimum, max: maximum, avg: average, stddev: standard variance, median: median

The second part:

Rank: sort level, which can be used to match specific statements in the third part

The query ID of Query ID:16 binary number can be used to match specific statements in the third part.

Response time: the total response time, that is, the total execution time of this item

Calls: the total number of queries, that is, the total number of levels at which an entry has been executed

R/Calls: the average execution time of this article

VBG M: variance-to-mean ratio

Item: statement overview

The third part:

Database: database name

Host of Hosts:DB

Users: the DB user who executed the statement

Query_time distribution: the number of times the statement executes. The more it takes, the more time it takes to execute.

Other:

If you want to visualize the analysis results, you can combine tools such as Anemometer to achieve it.

The above is all the contents of this article entitled "how to use pt-query-digest, a MySQL slow log analysis tool". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.