Detailed introduction to MySQL's pt-query-digest analysis of slow query logs-Mysql Tutorial-php.cn

Home

Database

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

Mar 20, 2017 pm 02:15 PM

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

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

Copy after login

3. Installation steps

Method 1: rpm installation

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

Copy after login

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 &amp;&amp; make install

Copy after login

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

4. Introduction to the usage of each tool

(1) Slow query log analysis statistics

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

Copy after login

(2) Server summary

1	`pt-summary`

Copy after login

(3) Server disk monitoring

1	`pt-diskstats`

Copy after login

(4)Mysql service status summary

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

Copy after login

3. pt-query-digest syntax and important options

string
and then analyze it
json
, json-anon, general Use report for easier reading.

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

Copy after login

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

Copy after login

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

由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、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

Copy after login

五、用法示例

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

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

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

Copy after login

7.把查询保存到query_review表

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

Copy after login

8.把查询保存到query_history表

1 2	`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`

Copy after login

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

1 2	`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`

Copy after login

10.分析binlog

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

Copy after login

11.分析general log

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

Copy after login

总结

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!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn