mysql慢查询可视化分析

2023年 11月 27日 27.6k 0

背景

张总:小王,我们系统上线一段时间了,但是最近系统响应有点慢,同时系统系统访问量并不高,但mysql的cpu、内存资源一直在持续上涨,你去分析下慢sql,找出消耗资源的,进行优化,提升系统的性能

小王:好的,我去看下,有结果了,给你反馈

分析

找到mysql安排的目标机器,根据mysql的配置文件,就是my.conf文件找到慢sql记录的文件位置

打开慢sql日志文件,数据量实在是太大了,不好分析,有没有什么好的工具让我快速分析呢?

image.png

调研

经过夜以继日的查找,不负有心人,找到好的工具 percona-toolkit工具下的 pt-query-digest,其中就能对慢sql进行结构化的分析

pt-query-digest 正确打开方式

描述

pt-query-digest 可分析慢sql、常规和二进制日志文件中的 MySQL 查询。它还能分析来自 SHOW PROCESSLIST 的查询和来自 tcpdump 的 MySQL 协议数据。默认情况下,查询按指纹分组,并按查询时间降序报告(即最慢的查询先报告)。如果没有给出 FILES,工具将读取 STDIN。可选的 DSN 用于某些选项,如 --since 和 --until。

使用

查看使用方法

./pt-query-digest --help

部分截图,功能很多,可以看看文档

image.png

慢sql分析

那我们怎么使用这个工具分析的慢sql呢,看下面简单的例子感受下

-- 查询最近10分钟的慢sql,注意下type的值一定要是slowlog
./pt-query-digest /var/log/mysql4306-slow.log --type=slowlog --since='10m' >report.log

分析报告

第一部分:总体概况描述说明

-- 分析消耗的用户cpu时间,系统cpu的时间,物理内存占用大小,虚拟内存占用大小
# 26.6s user time, 2.3s system time, 132.00M rss, 211.10M vsz
-- 分析的当前日期
# Current date: Mon Nov 27 11:17:53 2023
-- 分析的主机名
# Hostname: hbdata2
-- 分析的慢sql日志文件所在的位置
# Files: /var/log/mysql4306-slow.log
-- 分析的整体情况:语句的数量,唯一语句数量,Qps和并发数
# Overall: 81 total, 23 unique, 0.02 QPS, 0.06x concurrency ______________
-- 分析的时间范围
# Time range: 2023-11-27T10:18:58 to 2023-11-27T11:16:39
--属性             总计      最小     最大     平均    95%   标准    中等
# 属性             total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
-- 执行时间
# Exec time           217s      2s     10s      3s      3s   938ms      2s
-- 锁的时间
# Lock time            15s     1us     10s   187ms    38us      1s     5us
-- 发送到客户端的行数
# Rows sent        844.68k       0 215.76k  10.43k  118.34  44.26k    9.83
-- 扫描的行数
# Rows examine      40.18M       0   2.68M 508.00k   2.62M 924.59k 106.99k
--查询的字节数
# Query size       374.06k     115  22.19k   4.62k  12.50k   4.88k   2.27k

-- 统计概况
# Profile
-- 排名,查询的指纹总响应时间百分比,执行的次数,执行的平均响应时间,响应时间Variance-to-mean的比率,查询的对象
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0xDF99061A6D1697D34D07EDA3BF0... 35.8138 16.5%    12 2.9845  0.00 SELECT t_adconfig_floor t_adconfig_ad_position t_adconfig_advertisement b
#    2 0x8A01A12F5084F1D5B374BD67A2A... 24.1755 11.2%    11 2.1978  0.01 SELECT t_product t_product_price t_r_product t_product t_r_product t_product t_product_image t_product t_product_index t_product_price
#    3 0xD7881C474749029922D65931F22... 22.3766 10.3%     7 3.1967  0.00 SELECT UNION t_order t_order_detail t_product_stock_detail t_return t_order t_return_detail t_product_stock_detail t_order_detail t_order t_order_detail t_recover_second_hand_good_goods t_recover_s
tock t_return t_order t_return_detail t_recover_second_hand_good_goods t_order_detail t_recover_stock t_write_off_product t_special_user t_order t_order_detail t_special_user t_write_off_product

# MISC 0xMISC                           10.4528  4.8%     5 2.0906   0.0 <5 ITEMS>

第二部分 查询sql概况说明

-- 查询的顺序号和第一部分的rank对应,qps,并发,查询的指纹(唯一号)
# Query 1: 0.67 QPS, 2.76x concurrency, ID 0xD9114A7EDA241D08F67779A0775D0EF1 at byte 4069289265
-- 响应比例
# Scores: V/M = 0.01
-- 分析查询的时间范围
# Time range: 2023-11-27T11:41:09 to 2023-11-27T11:41:27
--属性       百分比 总数      最小      最大    平均    95%时间分布 标准 中等
# 属性       pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
-- 次数
# Count         23      12
-- 执行时间
# Exec time     27     50s      4s      4s      4s      4s   237ms      4s
-- 锁的时间
# Lock time     40   847us    45us   132us    70us    84us    21us    66us
-- 发送到客户端的行数
# Rows sent      0      12       1       1       1       1       0       1
-- 扫描的行数
# Rows examine   1 130.31k   9.68k  11.57k  10.86k  11.34k  566.22  10.80k
-- 查询的字节数
# Query size    45   1.33M 112.59k 113.83k 113.21k 112.33k       0 112.33k
# String:
-- 数据库
# Databases    small_oa_169
-- 远程查询的主机
# Hosts        hbdata4 (5/41%), hbdata5 (3/25%)... 2 more
-- 查询用户
# Users        small_oa
-- 查询执行时间的分布直方图:1微妙、10微妙、100微妙、10毫秒、100毫秒、1秒、10秒以上查询分布的情况
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
-- 表信息
# Tables
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_product_stock_detail'G
#    SHOW CREATE TABLE `small_oa_169`.`t_product_stock_detail`G
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_order_detail'G
#    SHOW CREATE TABLE `small_oa_169`.`t_order_detail`G
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_order'G
#    SHOW CREATE TABLE `small_oa_169`.`t_order`G
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_product_info'G
#    SHOW CREATE TABLE `small_oa_169`.`t_product_info`G
-- 执行计划信息
# EXPLAIN /*!50100 PARTITIONS*/
-- 查询的sql
SELECT

            sum(t20.statistics_in_price) statistics_in_price,
            SUM(t20.in_price) in_price,
            SUM(t20.statistics_gross_profit) statistics_gross_profit,
            sum(t20.gross_profit) gross_profit,


            sum(t20.examine_price) examine_price,
            SUM(t20.examine_gross_profit) examine_gross_profit,

通过上面两部分,就能清晰、结构化的分析sql的执行请,进行有效的分析优化,提升系统性能,同时减少资源的占用

使用场景

  • 分析所有的慢查询,这种数据量非常大,看情况使用
  • pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog
    
  • 分析指定数据库的慢查询,数据库是指创建的逻辑库
  • pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '($event-&gt;{db} || "") =~ m/^dbsql/i'
    
  • 分析执行用户的慢查询
  • pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '($event-&gt;{user} || "") =~ m/^pmm/i'
    
  • 分析制定ip的慢查询
  • pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '($event-&gt;{host} || $event-&gt;{ip} || "") =~ m/^192.168.16.*/i'
    
  • 分析指定时间范围的慢查询,这个比较实用,我经常就用这个
  • pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --since='2023-03-26 00:00:00' --until='2023-03-27 23:59:59'
    
    ## 时间也可以用时间戳
    pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --since='1583048987' --until='1583049175'
    
  • 分析最近时间的慢查询,时间单位:h(小时)、m(分)、s(秒)
  • # 最近10小时慢查询,从执行命令的当前时间往前推10个小时
    pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --since='10h
    
  • 分析指定类型的慢查询,select、update等
  • pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '$event->{arg} =~ m/^update/i'
    
  • 分析慢查询,同时写入到表中
  • t-query-digest --user=root --password=root --port=3306 --review h=192.168.16.111,D=slow_query_log,t=global_query_review --history h=192.168.16.111,D=slow_query_log,t=global_query_review_history --limit=0% --filter='($event-&gt;{Bytes} = length($event-&gt;{arg}) and $event-&gt;{hostname}="dbsql") and ($event-&gt;{host} || $event-&gt;{ip}) !~ m/^localhost$|^192.168.16.1$/i and $event-&gt;{arg} =~ m/^select/i' --since='2023-03-23 12:00:00' --until='2023-03-30 13:00:00' /data/mysql/my3306/logs/slow.log --no-report
    

    这提供了一些使用的场景例子,可以参照文档,根据自身的实际情况使用,

    (自研)根据pt-query-digest结构化数据生成html

    指定输出的报告格式 --output json ,通过json的格式数据,二次开发生成html,方便查看,如有需要的可以私信,提供源代码

    pt-query-digest  --output json
    

    这样的界面给领导、同事查看,是不是很nice

    image.png

    写作不易,刚好你看到,刚好对你有帮助,麻烦点赞关注,谢谢。

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论