Shulou(Shulou.com)06/01 Report--

Let's learn about the query log analysis tool pt-query-digest, I believe you will benefit a lot after reading, the text is not much in the essence, hope that the query log analysis tool pt-query-digest this short content is what you want.

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 CVM 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.

Part I: overall statistical results

Overall: how many queries are there altogether

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

Unique: unique number of queries, that is, how many different queries are there after the query conditions are parameterized

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

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

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

The tool performs log analysis user time, system time, physical memory footprint size, virtual memory footprint size 340ms user time, 140ms system time, 23.99m rss, 203.11m vsz tool execution time Current date: Fri Nov 25 02:37:18 2016 hostname Hostname: localhost.localdomain file name analyzed Files: total number of slow.log statements, unique statements, QPS Number of concurrency Overall: 2 total, 2 unique, 0.01 QPS 0.01x concurrency _ time range for logging Time range: 2016-11-22 06:06:18 to 06:11:40 attribute total minimum maximum average 95% standard medium Attribute total min max avg 95% stddev median= = statement execution time Exec time 3s 640ms 2s 1s 2s 999ms 1s lock occupancy time Lock time 1ms 0 1ms 723us 1ms 1ms 723us number of rows sent to the client Rows sent 5 1 4 2.50 4 2.12 2.50select statement scan rows Rows examine 186.17k 0 186.17k 93.09k 186.17k 131.64k 93.09k query number of characters Query size 45515440 227.50 440 300.52 227.50

Part II: query grouping statistical results

Rank: ranking of all statements, sorted by query time in descending order by default, specified by-- order-by

Query ID: the ID of the statement, (remove extra spaces and text characters, calculate the hash value)

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

VBG M: the ratio of response time Variance-to-mean

Item: query object

ProfileRank Query ID Response time Calls R/Call 0xF9A57DD5A41825CA M Item==== 1 0xF9A57DD5A41825CA 2.0529 76.2% 1 2.0529 0.00 SELECT2 0x4194D8F83F4F9365 0.6401 23.8% 1 0.6401 0.00 SELECT wx_member_base

Part III: detailed statistical results of each query

From the detailed statistical results of the query below, the top table lists the statistics of the number of times of execution, maximum, minimum, average, 95% and so on.

ID: the ID number of the query, which corresponds to the Query ID in the figure above

Databases: database 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:SQL statement

Usage example:

1. Direct analysis of slow query files:

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

two。 Analysis of queries in the last 12 hours:

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

3. Analyze queries within a specified time range:

Pt-query-digest slow.log-- since '2017-01-07 09 until'-- until '2017-01-07 10 until' > slow_report3.log

4. Parsing refers to slow queries with select statements

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

5. Slow query for a user

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

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")'/ data/mysql/localhost-slow.log

(7) Save the query to the query_ table

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

After reading this article of query log analysis tool pt-query-digest, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.