MySQL 学习 -- 第三天 -- MySQL查询分析器pt_query_digest的安装、使用

最新推荐文章于 2024-08-12 16:31:39 发布

小虾米vivian

最新推荐文章于 2024-08-12 16:31:39 发布

阅读量988

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/Penrosee/article/details/86520738

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、pt_query_digest 简介与安装

1.简介
pt-query-digest（官网地址https://www.percona.com/doc/percona-toolkit/3.0/index.html）是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOW PROCESSLIST 或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

2.安装
pt-query-digest是一个perl脚本，只需下载并赋权即可执行。

[root@localhost opt]#  wget percona.com/get/pt-query-digest

[root@localhost opt]#  chmod u+x pt-query-digest

注意：此处我将工具下载到/opt 目录下

二、pt-query-digest 语法及重要选项

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

三、用法示例

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

#分析slow.log日志，并将分析报告输入到slow_report.log中

[root@localhost opt]# ./pt-query-digest /data/mysql/mysql3306/data/slow.log > /log/slow_report.log

（示例中慢日志的路径及输出文件的路径根据位置替换即可）

以下输出信息为本机的分析情况，该服务器没有任何业务，只是开启了慢日志，执行了几条语句，简单模拟分析的过程。

[root@localhost opt]# ./pt-query-digest /data/mysql/mysql3306/data/slow.log  > /log/slow_report.log 
[root@localhost opt]# cat /log/slow_report.log 

# 180ms user time, 20ms system time, 25.69M rss, 218.07M vsz
# Current date: Thu Jan 17 11:38:33 2019
# Hostname: localhost.localdomain
# Files: /data/mysql/mysql3306/data/slow.log
# Overall: 2 total, 2 unique, 0.00 QPS, 0.00x concurrency ________________
# Time range: 2019-01-09T06:44:24 to 2019-01-17T02:49:55
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             4s      2s      2s      2s      2s   115ms      2s
# Lock time          290us       0   290us   145us   290us   205us   145us
# Rows sent        976.56k       1 976.56k 488.28k 976.56k 690.53k 488.28k
# Rows examine     976.56k       0 976.56k 488.28k 976.56k 690.53k 488.28k
# Query size            36      15      21      18      21    4.24      18

# Profile
# Rank Query ID                           Response time Calls R/Call V/M  
# ==== ================================== ============= ===== ====== =====
#    1 0x59A74D08D407B5EDF9A57DD5A41825CA  2.0011 52.1%     1 2.0011  0.00 SELECT
#    2 0x643732BA7790FD5CA537EDB14FD4BFAE  1.8392 47.9%     1 1.8392  0.00 SELECT sbtest

# Query 1: 0 QPS, 0x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 6378
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-01-17T02:49:55
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     52      2s      2s      2s      2s      2s       0      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    41      15      15      15      15      15       0      15
# String:
# Databases    test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

# Query 2: 0 QPS, 0x concurrency, ID 0x643732BA7790FD5CA537EDB14FD4BFAE at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2019-01-09T06:44:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     47      2s      2s      2s      2s      2s       0      2s
# Lock time    100   290us   290us   290us   290us   290us       0   290us
# Rows sent     99 976.56k 976.56k 976.56k 976.56k 976.56k       0 976.56k
# Rows examine 100 976.56k 976.56k 976.56k 976.56k 976.56k       0 976.56k
# Query size    58      21      21      21      21      21       0      21
# String:
# Databases    test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'sbtest'\G
#    SHOW CREATE TABLE `test`.`sbtest`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from  sbtest\G

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

[root@localhost opt]# ./pt-query-digest --since=12h /data/mysql/mysql3306/data/slow.log > /log/slow_report2.log

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

[root@localhost opt]# ./pt-query-digest /data/mysql/mysql3306/data/slow.log --since '2019-01-16 00:00:00' --until '2019-01-17 10:00:00' > /log/slow_report3.log

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

[root@localhost opt]# ./pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /data/mysql/mysql3306/data/slow.log > /log/slow_report4.log

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

[root@localhost opt]# ./pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /data/mysql/mysql3306/data/slow.log > /log/slow_report5.log

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

[root@localhost opt]# ./pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /data/mysql/mysql3306/data/slow.log > /log/slow_report6.log

7把查询保存到query_review表

[root@localhost opt]# ./pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_review--create-review-table /data/mysql/mysql3306/data/slow.log

8.把查询保存到query_history表

[root@localhost opt]# ./pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_hostory--create-review-table /data/mysql/mysql3306/data/slow.log

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

1）、安装tcpdump(yum 安装即可)

[root@localhost opt]# yum -y install tcpdump

[root@localhost opt]# yum -y install tcpdump
[root@localhost opt]# tcpdump -help
tcpdump version 4.9.2
libpcap version 1.5.3
OpenSSL 1.0.2k-fips  26 Jan 2017
Usage: tcpdump [-aAbdDefhHIJKlLnNOpqStuUvxX#] [ -B size ] [ -c count ]
		[ -C file_size ] [ -E algo:secret ] [ -F file ] [ -G seconds ]
		[ -i interface ] [ -j tstamptype ] [ -M secret ] [ --number ]
		[ -Q|-P in|out|inout ]
		[ -r file ] [ -s snaplen ] [ --time-stamp-precision precision ]
		[ --immediate-mode ] [ -T type ] [ --version ] [ -V file ]
		[ -w file ] [ -W filecount ] [ -y datalinktype ] [ -z postrotate-command ]
		[ -Z user ] [ expression ]

（2）、tcpdump抓取mysql的tcp协议数据

[root@localhost opt]#tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
[root@localhost opt]#./pt-query-digest --type tcpdump mysql.tcp.txt > /log/slow_report9.log

10.分析binlog

[root@localhost opt]# mysqlbinlog /data/mysql/mysql3306/logs/mysql-bin.000030 > /log/mysql-bin000093.sql
[root@localhost opt]# ./pt-query-digest --type=binlog /log/mysql-bin000093.sql > /log/mysql_report10.log

[root@localhost opt]# cat /log/mysql_report10.log 

# 130ms user time, 20ms system time, 25.64M rss, 217.96M vsz
# Current date: Thu Jan 17 14:23:33 2019
# Hostname: localhost.localdomain
# Files: /log/mysql-bin000093.sql
# Overall: 0 total, 9 unique, 0 QPS, 0x concurrency ______________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size         4.53k      11     554   92.70  136.99   83.15   92.72

# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

11.分析general log

（1）、登录mysql 数据库，查看general log位置

root@localhost [mysql]>show variables like 'gen%';

root@localhost [mysql]>show variables like 'gen%';
+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | OFF                                      |
| general_log_file | /data/mysql/mysql3306/data/localhost.log |
+------------------+------------------------------------------+

（2）、分析general log

[root@localhost opt]# ./pt-query-digest --typpe=genlog /data/mysql/mysql3306/data/localhost.log > /log/slow_report11.log

四.报告参数说明

参考https://www.imooc.com/article/20180

我们对以上红色框图标记的报表信息进行详细描述，事实上这也是我们需要掌握的重点：

0. pct :sql语句某执行属性占所有慢查询语句某执行属性的百分比
1. total：sql语句某执行属性的所有属性时间。
2. Count：sql语句执行的次数，对应的pct 表示此sql 语句执行次数占所有慢查询语句执行次数的％比。上图为50%，total:表示总共执行了1次。
3. Exec time:sql执行时间
4.Lock time:sql执行期间被锁定的时间
5.Rows sent:传输的有效数据，在select 查询语句中才有值
6.Rows examine:总共查询的数据，非目标数据。
7.Query_time distribution:查询时间分布
8.SQL 语句:上图中为 select sleep(2)\G

举例说明：假如某执行次数(count) 占比较高的sql语句，执行时间很长，Rows sent 数值很小，Rows examine 数值很大则表明(I/O较大)。那就表明有可能 sql 查询语句走了全表扫描，或者全索引扫描。那么就要建立合适索引或者优化sql语句了。

---------------------

参考：https://blog.csdn.net/qq_16399991/article/details/82383917

参考：https://www.imooc.com/article/20180