Detailed introduction to MySQL's pt-query-digest analysis of slow query logs

This article mainly introduces the relevant information about MySQL slow query pt-query-digest analysis of slow query logs. The introduction in the article is very detailed and has certain reference value for everyone. Friends who need it Let’s take a look together below.

1. Introduction

pt-query-digest is a tool used to analyze mysql slow queries. It Binlog, general log, slowlog can be analyzed, or the MySQL protocol data captured through SHOWPROCESSLIST or tcpdump can be analyzed. The analysis results can be output to a file. The analysis process is to first parameterize the conditions of the query statement, and then perform group statistics on the parameterized queries to calculate the execution time, number, proportion, etc. of each query. You can use the analysis As a result, problems are identified and optimized.

2. Installationpt-query-digest

##2.perl module

yum install -y perl-CPAN perl-Time-HiRes

3. Installation steps

Method 1: rpm installation

cd /usr/local/src
wget percona.com/get/percona-toolkit.rpm
yum install -y percona-toolkit.rpm

The tool installation directory is:/usr/bin

Method 2: Source code installation

cd /usr/local/src
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd percona-toolkit-2.2.19
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install

The tool installation directory is: /usr/local/percona-toolkit/bin

4. Introduction to the usage of each tool

(1) Slow query log analysis statistics

pt-query-digest /usr/local/mysql/data/slow.log

(2) Server summary

pt-summary

(3) Server disk monitoring

pt-diskstats

(4)Mysql service status summary

pt-mysql-summary -- --user=root --password=root

3. pt-query-digest syntax and important options

##pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table When using the --review parameter to output the analysis results to a table, it will be automatically created if there is no table.
--create-history-table When using the --history parameter to output the analysis results to a table, it will be automatically created if there is no table.
--filter Match and filter the input slow query according to the specified
4. string

   and then analyze it

--limit Limits the percentage or quantity of output results. The default value is 20, which means outputting the 20 slowest statements. If it is 50%, it will be sorted from large to small by the total response time, and the output will be cut off when the total reaches 50%.
--host mysql server address
--user mysql username
-- password mysql user password
--history Save the analysis results to the table. The analysis results are more detailed. The next time you use --history, if the same statement exists and the query is If the time interval 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 Save the analysis results to the table. This analysis only parameterizes the query conditions. One type of query is for one record, which is relatively simple. When --review is used next time, if the same statement analysis exists, it will not be recorded in the data table.
--output analysis result output type, the value can be report (standard analysis report), slowlog (Mysql slow log),
11. json

    , json-anon, general Use report for easier reading.

--since the time from which to start analysis, the value is a string, which can be a specified time point in the format of "yyyy-mm-dd [hh:mm:ss]" , or it can be a simple time value: s (seconds), h (hours), m (minutes), d (days). For example, 12h means that statistics started 12 hours ago.
--until deadline, combined with --since can analyze slow queries within a period of time.

13. 
    

4. Analysis of pt-query-digest output results

Part 1: Overall Statistical results

Overall: How many queries are there in total

Time range: The time range of query execution

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

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

95 %: Arrange all values ​​from small to large, the number located at the 95th percentile, this number generally has the most reference value

median: Median, arrange all values ​​from small to large, the position is at The number in the middle

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性    总计  最小 最大 平均 95% 标准 中等
# Attribute   total  min  max  avg  95% stddev median
# ============  ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time    3s 640ms  2s  1s  2s 999ms  1s
# 锁占用时间
# Lock time   1ms  0  1ms 723us  1ms  1ms 723us
# 发送到客户端的行数
# Rows sent    5  1  4 2.50  4 2.12 2.50
# select语句扫描行数
# Rows examine  186.17k  0 186.17k 93.09k 186.17k 131.64k 93.09k
# 查询的字符数
# Query size   455  15  440 227.50  440 300.52 227.50

Part 2: Query group statistical results

Rank: Ranking of all statements, by default arranged in descending order by query time, passed-- order-by specifies

Query ID: ID of the statement, (remove excess spaces and text characters, calculate

hash

value)
Response: Total Response time

time: The total time proportion of this query in this analysis

calls: Number of executions, that is, how many such queries are there in this analysis Type of query statement

R/Call: Average response time of each execution

V/M: Response time Variance-to-mean ratio

Item：查询对象

# Profile
# Rank Query ID   Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0xF9A57DD5A41825CA 2.0529 76.2%  1 2.0529 0.00 SELECT
# 2 0x4194D8F83F4F9365 0.6401 23.8%  1 0.6401 0.00 SELECT wx_member_base

第三部分：每一种查询的详细统计结果

由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。

ID：查询的ID号，和上图的Query ID对应

Databases：数据库名

Users：各个用户执行的次数（占比）

Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。

Tables：查询中涉及到的表

Explain：SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute pct total  min  max  avg  95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count   50  1
# Exec time  76  2s  2s  2s  2s  2s  0  2s
# Lock time  0  0  0  0  0  0  0  0
# Rows sent  20  1  1  1  1  1  0  1
# Rows examine 0  0  0  0  0  0  0  0
# Query size  3  15  15  15  15  15  0  15
# String:
# Databases test
# Hosts  192.168.8.1
# Users  mysql
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

五、用法示例

1.直接分析慢查询文件:

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

2.分析最近12小时内的查询：

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

3.分析指定时间范围内的查询：

pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log

4.分析指含有select语句的慢查询

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

5.针对某个用户的慢查询

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

6.查询所有所有的全表扫描或full join的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

7.把查询保存到query_review表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log

8.把查询保存到query_history表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0001
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0002

9.通过tcpdump抓取mysql的tcp协议数据，然后再分析

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.分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

11.分析general log

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

总结

The above is the detailed content of Detailed introduction to MySQL's pt-query-digest analysis of slow query logs. For more information, please follow other related articles on the PHP Chinese website!