mysql慢日志分析pt-query-digest

目录

1.概要

2.部署

2.1 下载最新安装包

2.2 RPM安装

2.3 慢日志配置

3. 用法

3.1 常用选项说明

3.2 常见用法

3.3 分析示例及说明

4. 总结

---

1.概要

官网：pt-query-digest (percona.com)

pt-query-digest 可以分析slowlog日志、binlog、进程列表和从tcpdump抓取的MySQL数据包，分析结果包括对查询进行分组统计，统计出各查询的执行时间、次数、占比等，以此做为慢查询优化的方向。

2.部署

2.1 下载最新安装包

# 编译安装
wget percona.com/get/percona-toolkit.tar.gz
# RPM安装
wget percona.com/get/percona-toolkit.rpm
# DEB安装
wget percona.com/get/percona-toolkit.deb

2.2 RPM安装

# 下载
wget percona.com/get/percona-toolkit.rpm
# 安装依赖
yum install -y perl-CPAN perl-Time-HiRes perl-DBI.x86_64 perl-DBD-MySQL.x86_64 perl-Digest-MD5.x86_64 perl-TermReadKey.x86_64 
# 安装
yum install -y percona-toolkit.rpm

2.3 慢日志配置

# 查看慢日志是否开启
SHOW VARIABLES LIKE '%slow%';
Variable_name              Value           
-------------------------  ----------------
log_slow_admin_statements  ON             
log_slow_slave_statements  ON             
slow_launch_time           2               
slow_query_log             ON              
slow_query_log_file        mysql-slow.log  

# mysql-slow.log默认存放位置在mysql data目录，可在my.cnf配置指定目录存放

# 查看慢日志执行时间
SHOW VARIABLES LIKE '%long_query_time%';
Variable_name    Value     
---------------  ----------
long_query_time  0.500000  

# 开启慢日志命令
set global slow_query_log=on/off;
# 配置慢日志执行时间
set global long_query_time=0.5;

3. 用法

语法：pt-query-digest [OPTIONS] [FILES] [DSN]

3.1 常用选项说明

OPTIONS	作用
--host	mysql地址
--user	mysql用户名
--password	mysql密码
--since	从指定时间点开始分析，如 指定日期YYYY-MM-DD [HH:MM:SS]，1d/h/m等
--until	配合--since，指定时间段内的日志分析，从xx日期开始，xx日期截止
--type	指定类型，默认是slowlog，还可以指定binlog，tcpdump，genlog等类型

其它选项可以查阅官方文档pt-query-digest (percona.com)

3.2 常见用法

3.2.1 分析慢日志文件

# 直接分析慢日志文件，并将结果输出到txt文本
pt-query-digest  slow.log > slow_log.txt

3.2.2 分析最近xx小时内的慢日志

# 如最近1小时内的慢日志分析
pt-query-digest --since=1h slow.log > slow_log.txt

3.2.3 分析指定时间范围内的慢日志

pt-query-digest --since '2022-07-28 00:00:00' --until '2022-07-28 23:59:59' slow.log > slow_log.txt

3.2.4 分析binlog

# 导出binlog日志为sql
mysqlbinlog mysql-bin.000731 > mysql-bin000731.sql
pt-query-digest --type=binlog mysql-bin000731.sql > slow_log.txt

3.3 分析示例及说明

pt-query-digest  slow.log 

# 295.7s user time, 1.1s system time, 181.92M rss, 390.89M vsz
# pt-query-digest执行的时间（user time、system time），物理内存占用，虚拟内存占用
# Current date: Fri Jul 29 11:51:03 2022 #执行时间
# Hostname: gz-system
# Files: mysql-slow.log # 分析文件
# Overall: 1.08M total, 13 unique, 0.03 QPS, 0.14x concurrency ___________
# 分析语句数量，唯一语句，qps
# Time range: 2021-07-07 00:31:16 to 2022-07-28 15:00:03 #慢日志时间范围
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time(sql执行时间)        4596275s      2s    179s      4s      4s      2s      4s
# Lock time(锁占用时间)           92s       0   109ms    85us   113us   377us    73us
# Rows sent(发送行数）        446.83M       0 223.49k  434.30   1.33k  704.98  284.79
# Rows examine(select语句数)       2.94T       0   9.76M   2.86M   2.88M 213.18k   2.75M
# Query size(查询字符数)       140.69M     104     590  136.74  130.47   22.32  130.47

# Profile
# Rank：语句排名 
# Query ID:查询ID 
# Response :总响应时间 
# time: 执行时间占比 
# Calls：执行次数 
# R/Call：平均响应时间 
# Item：执行sql
# Rank Query ID           Response time      Calls   R/Call   V/M   Item
# ==== ================== ================== ======= ======== ===== ======
#    1 0xB9E336CCA326672F 4469195.7817 97.2% 1071392   4.1714  0.01 SELECT 
#    2 0x4C853557C93E7ABC   46636.0847  1.0%    2318  20.1191  0.28 SELECT stock_out

# Query 1: 0.03 QPS, 0.13x concurrency, ID 0xB9E336CCA326672F at byte 116891243
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2021-07-07 00:31:16 to 2022-07-28 15:00:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         99 1071392
# Exec time     97 4469196s      4s     11s      4s      4s   184ms      4s
# Lock time     86     79s       0    17ms    74us   108us    39us    69us
# Rows sent     99 443.33M       0  14.56k  433.89   1.33k  496.14  284.79
# Rows examine  99   2.93T   2.78M   3.00M   2.87M   2.88M  67.51k   2.75M
# Query size    98 137.94M     135     135     135     135       0     135
# String:
# Hosts        x.x.x.x
# Users        query_user
# Query_time distribution # 查询时间分布占比
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #
# Tables # 查询涉及的表
#    SHOW TABLE STATUS FROM `wms` LIKE 'sku_supplier'\G
#    SHOW CREATE TABLE `wms`.`sku_supplier`\G
# EXPLAIN /*!50100 PARTITIONS*/ # 执行的SQL语句
select SKUSupplier_ID as skuSupplierId from wms.sku_supplier where last_update >= date_sub('2021-10-26 07:03:33' , INTERVAL 20 MINUTE)\G

4. 总结

在实际生产中，比较喜欢使用直接分析慢日志按天进行分析，并输出到txt文本的方式，pt-query-digest只是一个分析统计的工具，可以根据输出的慢SQL语句进行合理的调整，如查询慢则增加索引，DML操作改成DDL以提高响应速度等，具体还需要多思考、实践。