高手的存在,就是让服务10亿人的时候,你感觉只是为你一个人服务......

pt-query-digest

目录
  1. 1. 安装pt-query-digest
  2. 2. 开启慢查询
  3. 3. 使用pt-query-digest分析slowlog
  4. 4. 从tcpdump包中分析

慢查询日志(slow-log)可以帮助我们分析mysql的性能问题,pt-query-digest可以对慢查询日志进一步分析、汇总,整理出一份优秀的报表展示给我们。

不仅如此,pt-query-diges它还可以分析binlog、General log,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。


安装pt-query-digest

步骤一、下载pt-query-digest安装包

1
wget percona.com/get/percona-toolkit.tar.gz

步骤二、解压安装包

1
tar -xvf percona-toolkit.tar.gz

步骤三、编译安装pt-query-digest

1
2
3
perl Makefile.PL
make
make install

如果没有安装perl的执行以下命令:

1
2
3
4
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL


开启慢查询

步骤一、查看是否已经开启了慢查询日志

1
mysql> show variables like '%query%';

Alt text

步骤二、开启慢查询日志
如果未开启慢查询日志,可以通过以下两种方式开启:

方式一:全局变量设置
将 slow_query_log 全局变量设置为“ON”状态

1
mysql> set global slow_query_log='ON';

设置慢查询日志存放的位置

1
mysql> set global slow_query_log_file='/mysql/data/lcportalpstdb02-slow.log';

查询超过0.5秒就记录

1
mysql> set global long_query_time=0.5;

方式二:配置文件设置
修改配置文件my.cnf,在[mysqld]下的下方加入

1
2
3
4
[mysqld]
slow_query_log = ON
slow_query_log_file = /mysql/data/lcportalpstdb02-slow.log
long_query_time = 0.5

步骤三、重启MYSQL服务

1
service mysqld restart

步骤四、测试

执行一条慢查询SQL语句

1
mysql> select sleep(1);

查看是否生成慢查询日志 /mysql/data/lcportalpstdb02-slow.log


使用pt-query-digest分析slowlog

先做几条测试数据:

1
2
3
4
5
6
mysql> select sleep(1);
mysql> select sleep(1);
mysql> select sleep(1);
mysql> select sleep(2);
mysql> select sleep(3);
mysql> select sleep(3);

执行pt-query-digest命令:

1
pt-query-digest lcportalpstdb02-slow.log

结果(附上部分解释):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# 该工具执行日志分析的用户时间,系统时间,物理内存占用大小,虚拟内存占用大小
# 150ms user time, 10ms system time, 22.25M rss, 175.60M vsz
# 工具执行时间
# Current date: Wed Jan 9 17:16:05 2019
# 主机名
# Hostname: lcportalpstdb02
# 被分析的文件名
# Files: lcportalpstdb02-slow.log
# 语句总数量,唯一的语句数量,QPS,并发数
# Overall: 6 total, 1 unique, 0.40 QPS, 0.73x concurrency ________________
# 日志记录的时间范围
# Time range: 2019-01-09 15:52:04 to 15:52:19

# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time 11s 1s 3s 2s 3s 858ms 2s
# 锁占用时间
# Lock time 0 0 0 0 0 0 0
# 发送到客户端的行数
# Rows sent 6 1 1 1 1 0 1
# select语句扫描行数
# Rows examine 0 0 0 0 0 0 0
# Rows affecte 0 0 0 0 0 0 0
# Rows read 6 1 1 1 1 0 1
# Bytes sent 378 63 63 63 63 0 63
# Tmp tables 0 0 0 0 0 0 0
# Tmp disk tbl 0 0 0 0 0 0 0
# Tmp tbl size 0 0 0 0 0 0 0
# 查询的字符数
# Query size 90 15 15 15 15 0 15

# Profile
# Rank:整个分析中该“语句”的排名,一般也就是性能最慢的
# Query ID:语句的ID
# Response:总的响应时间
# time:该查询在本次分析中总的时间占比
# calls:执行次数,即本次分析总共有多少条这种类型的查询语句
# R/Call:平均每次执行的响应时间
# V/M:响应时间Variance-to-mean的比率
# Item:查询对象
# Rank Query ID Response time Calls R/Call V/M
# ==== ================================== ============== ===== ====== ====
# 1 0x59A74D08D407B5EDF9A57DD5A41825CA 11.0009 100.0% 6 1.8335 0.40 SELECT

# Query 1: 0.40 QPS, 0.73x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 1373
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.40
# Time range: 2019-01-09 15:52:04 to 15:52:19
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 6
# Exec time 100 11s 1s 3s 2s 3s 858ms 2s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 100 6 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Rows affecte 0 0 0 0 0 0 0 0
# Rows read 100 6 1 1 1 1 0 1
# Bytes sent 100 378 63 63 63 63 0 63
# Tmp tables 0 0 0 0 0 0 0 0
# Tmp disk tbl 0 0 0 0 0 0 0 0
# Tmp tbl size 0 0 0 0 0 0 0 0
# Query size 100 90 15 15 15 15 0 15
# String:
# Hosts localhost
# Last errno 0
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(3)\G

从tcpdump包中分析

tcpdump可以抓取所有的sql流量信息,进行性能分析。

步骤一、通过tcpdump命令抓取一定时间的网络数据包

1
2
3
4
5
6
7
8
tcpdump -i eth0 -s 65535 -x -nn -q -tttt port 3306 -c 2000 > sql.dmp &
-i #指定监听网络接口
-s #从每个组中读取在开始的snaplen个字节,而不是默认的68个字节
-x #把协议头和包内容原原本本的显示出来(tcpdump默认以16进制和ASCII的形式显示)。
-nn #不进行端口名称的转换
-q #快速输出,即只输出较少的协议信息
-tttt #在每一行中输出data处理的默认格式的时间戳
-c #指定收取数据包的次数,即在收到指定数量的数据包后退出tcpdump

步骤二、执行pt-query-digest命令进行分析

1
pt-query-digest --type tcpdump sql.dmp > query.log

更多pt-query-digest命令,参见官网https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html