Pt-Query-Digest安装
pt-query-digest is a sophisticated but easy to use tool for analyzing MySQL queries. It can analyze queries from MySQL slow, general, and binary logs.
一、下载
https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/tarball/percona-toolkit-3.3.1_x86_64.tar.gz
https://www.percona.com/software/database-tools/percona-toolkit
https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html
二、安装依赖包
yum -y install perl-TermReadKey perl-Time-HiRes perl-IO-Socket-SSL.noarch perl-Digest-MD5 perl-DBD-MySQL三、安装Percona-toolkit
[root@db01 soft]# tar xvf percona-toolkit-3.3.1_x86_64.tar.gz -C /usr/local/
[root@db01 soft]# cd /usr/local/
[root@db01 local]# ln -s percona-toolkit-3.3.1 toolkit
[root@db01 bin]# vi /etc/profile
export PATH=/application/mysql/bin:/usr/local/toolkit/bin:$PATH
[root@db01 bin]# source /etc/profile
四、查看慢查询日志参数
[root@db01 ~]# more /etc/my.cnf
#慢查询日志
slow_query_log=1
slow_query_log_file=/data/mysql/data/slow.log
long_query_time=10
log_queries_not_using_indexes=1
五、使用方法
pt-query-digest [OPTIONS] [FILES] [DSN]
(1) 慢查询日志分析统计
pt-query-digest /usr/local/mysql/data/slow.log
(2) 服务器摘要
pt-summary
(3) 服务器磁盘监测
pt-diskstats
(4)mysql 服务状态摘要
pt-mysql-summary --user=root --password=123456
六、查看慢查询日志
mysql> select sleep(7);
+----------+
| sleep(7) |
+----------+
| 0 |
+----------+
1 row in set (7.00 sec)
mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)
mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
| 0 |
+-----------+
1 row in set (11.00 sec)
[root@db01 ~]# tail -f /data/mysql/data/slow.log
/application/mysql/bin/mysqld, Version: 5.7.26-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 2021-07-11T10:14:55.134944Z
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 10.000212 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1625998495;
select sleep(10);
# Time: 2021-07-11T10:15:16.267301Z
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 11.000393 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1625998516;
select sleep(11);
[root@db01 ~]# pt-query-digest /data/mysql/data/slow.log
# 80ms user time, 20ms system time, 22.11M rss, 184.77M vsz
# Current date: Sun Jul 11 18:17:49 2021
# Hostname: db01
# Files: /data/mysql/data/slow.log
# Overall: 2 total, 1 unique, 0.10 QPS, 1.00x concurrency ________________
# Time range: 2021-07-11T10:14:55 to 2021-07-11T10:15:16
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 21s 10s 11s 11s 11s 707ms 11s
# Lock time 0 0 0 0 0 0 0
# Rows sent 2 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0
# Query size 32 16 16 16 16 0 16
# Profile
# Rank Query ID Response time Calls R/Call V/
# ==== =================================== ============== ===== ======= ==
# 1 0x59A74D08D407B5EDF9A57DD5A41825CA 21.0006 100.0% 2 10.5003 0.05 SELECT
# Query 1: 0.10 QPS, 1.00x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 393
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2021-07-11T10:14:55 to 2021-07-11T10:15:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 2
# Exec time 100 21s 10s 11s 11s 11s 707ms 11s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 100 2 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query size 100 32 16 16 16 16 0 16
# String:
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(11)\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 '2021-01-07 09:30:00' --until '2021-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
12.从主机host1的进程中分析慢查询
pt-query-digest --processlist h=host1