mysql慢查询分析工具比较与实战-IT技术资讯

Table of Contents

00 前言

在进行mysql性能优化的时候，第一个想到的便是查看慢sql。

但是对于慢sql有没有什么好的工具进行分析呢？

推荐两个工具mysqldumpslow及pt-query-digest。

mysqlslowdump较为简单，常用命令：

#得到返回记录最多的20个sql
mysqldumpslow -s r -t 20 slowSQl.log

# 得到平均访问次数最多的20条sql
mysqldumpslow -s ar -t 20 slowSQl.log

如果linux上没有安装mysqldumpslow，yum install安装下就行了。

本文主要说下pt-query-digest。

pt-query-digest可以非常清晰地将slowSQL分析出来，类似oracle的AWR报告。

    # Rank Query ID                        Response time   Calls R/Call V/M   
# ==== =============================== =============== ===== ====== ===== 
#    1 0xABD1DCCCCD5AA5128E10C27B34... 1246.6948 41.7%   283 4.4053  0.04 UPDATE ziweidashi_deviceinfo
#    2 0x6914B81AAD1785E50708ABD113...  877.6900 29.3%   339 2.5891  0.09 SELECT birthDay_notify
#    3 0x44D9474C6D5C58DD07B5FEEA0D...  299.4193 10.0%    71 4.2172  0.05 SELECT tmall_product_orders
#    4 0xA9BE84CBE3DAA9B1CDD9B5A9EC...  127.0137  4.2%    46 2.7612  0.04 SELECT daily_user_action_log
#    5 0xCF0E12117C971C3013142E3717...  118.3138  4.0%    49 2.4146  0.05 SELECT tmall_user_take_coupon_record
#    6 0x94263184D24186330B13193534...   97.0805  3.2%    35 2.7737  0.56 SELECT tgg_users
#    7 0xC51165F1287A2ECDA221AC1F54...   52.5870  1.8%    22 2.3903  0.04 SELECT util_user_task_log
#    8 0xB8004D6D8A7A7967E04CD81E26...   43.7895  1.5%    16 2.7368  0.08 SELECT daily_user_action_log
#    9 0x910E19224F33DAA6391927B8E8...   41.3720  1.4%    15 2.7581  1.17 SELECT qifugong_tianbi_record
# MISC 0xMISC                            86.7871  2.9%    30 2.8929   0.0 <12 ITEMS>

并且不只可以分析慢SQL日志，还可以分析binlog、general log。

此外，pt-query-digest是percona-toolkit工具包的其中一个工具。
这个工具包下还有很多实用的性能分析辅助工具。

01 安装排坑

1、下载

# 进入安装目录
cd /usr/local/src

# 下载percona-toolkit 工具包
wget percona.com/get/percona-toolkit.tar.gz

# 解压
tar zxf percona-toolkit.tar.gz

# 进入解压文件夹
cd /usr/local/src/percona-toolkit-3.1.0

# 安装perl模块，制定依赖路径
perl Makefile.PL PREFIX=/usr/local/percona-toolkit

2、报错 prerequisite DBD::mysql 3 not found

报错如下，找不到DBD包

[root@iZ2zebthf35ejlps5v87ksZ percona-toolkit-3.1.0]# perl Makefile.PL PREFIX=/usr/local/percona-toolkit
Checking if your kit is complete...
Looks good
Warning: prerequisite DBD::mysql 3 not found.
Warning: prerequisite DBI 1.46 not found.
Writing Makefile for percona-toolkit

百度问题，找到链接，https://blog.csdn.net/heizistudio/article/details/45724707?locationNum=8&fps=1

安装依赖包

yum install perl-DBD-MySQL

然后重新执行命令

[root@iZ2zebthf35ejlps5v87ksZ percona-toolkit-3.1.0]# perl Makefile.PL PREFIX=/usr/local/percona-toolkit
Writing Makefile for percona-toolkit

3、安装

make && make install

安装后内容如下

……
Installing /usr/local/percona-toolkit/bin/pt-summary
Installing /usr/local/percona-toolkit/bin/pt-table-sync
Appending installation info to /usr/local/percona-toolkit/lib64/perl5/perllocal.pod

4、使用

[root@iZ2zebthf35ejlps5v87ksZ bin]# pt-query-digest /usr/local/mysql/data/slow.log
-bash: pt-query-digest: command not found

发现没找到pt-query-digest命令，是因为bash命令默认是从/usr/bin下找的；

如果rpm安装，会默认添加到/usr/bin下；

而我们现在是编译二进制安装到，并且默认是装到了/usr/local/percona-toolkit下，发现本文件夹下有个bin目录，pt工具都在其下。

-rwxrwxr-x 1 hc hc   41747 Sep 16  2019 pt-align
-rwxrwxr-x 1 hc hc  270675 Sep 16  2019 pt-archiver
-rwxrwxr-x 1 hc hc  170783 Sep 16  2019 pt-config-diff
-rwxrwxr-x 1 hc hc  167978 Sep 16  2019 pt-deadlock-logger
-rwxrwxr-x 1 hc hc  166450 Sep 16  2019 pt-diskstats
-rwxrwxr-x 1 hc hc  171099 Sep 16  2019 pt-duplicate-key-checker
-rwxrwxr-x 1 hc hc   50157 Sep 16  2019 pt-fifo-split
-rwxrwxr-x 1 hc hc  151809 Sep 16  2019 pt-find
-rwxrwxr-x 1 hc hc   67304 Sep 16  2019 pt-fingerprint
-rwxrwxr-x 1 hc hc  134955 Sep 16  2019 pt-fk-error-logger
-rwxrwxr-x 1 hc hc  223887 Sep 16  2019 pt-heartbeat
-rwxrwxr-x 1 hc hc  228213 Sep 16  2019 pt-index-usage
-rwxrwxr-x 1 hc hc   32405 Sep 16  2019 pt-ioprofile
-rwxrwxr-x 1 hc hc  256092 Sep 16  2019 pt-kill
-rwxrwxr-x 1 hc hc   21913 Sep 16  2019 pt-mext
-rwxrwxr-x 1 hc hc 8196032 Sep 16  2019 pt-mongodb-query-digest
-rwxrwxr-x 1 hc hc 8522944 Sep 16  2019 pt-mongodb-summary
-rwxrwxr-x 1 hc hc  108113 Sep 16  2019 pt-mysql-summary
-rwxrwxr-x 1 hc hc  426996 Sep 16  2019 pt-online-schema-change
-rwxrwxr-x 1 hc hc 4794784 Sep 16  2019 pt-pg-summary
-rwxrwxr-x 1 hc hc   24598 Sep 16  2019 pt-pmp
-rwxrwxr-x 1 hc hc  527607 Sep 16  2019 pt-query-digest
-rwxrwxr-x 1 hc hc 3624992 Sep 16  2019 pt-secure-collect
-rwxrwxr-x 1 hc hc   78242 Sep 16  2019 pt-show-grants
-rwxrwxr-x 1 hc hc   37784 Sep 16  2019 pt-sift
-rwxrwxr-x 1 hc hc  146952 Sep 16  2019 pt-slave-delay
-rwxrwxr-x 1 hc hc  131404 Sep 16  2019 pt-slave-find
-rwxrwxr-x 1 hc hc  184944 Sep 16  2019 pt-slave-restart
-rwxrwxr-x 1 hc hc   76226 Sep 16  2019 pt-stalk
-rwxrwxr-x 1 hc hc   90816 Sep 16  2019 pt-summary
-rwxrwxr-x 1 hc hc  459729 Sep 16  2019 pt-table-checksum
-rwxrwxr-x 1 hc hc  405119 Sep 16  2019 pt-table-sync
-rwxrwxr-x 1 hc hc  247743 Sep 16  2019 pt-table-usage
-rwxrwxr-x 1 hc hc  333011 Sep 16  2019 pt-upgrade
-rwxrwxr-x 1 hc hc  178415 Sep 16  2019 pt-variable-advisor
-rwxrwxr-x 1 hc hc  102545 Sep 16  2019 pt-visual-explain

本次使用到主力工具，pt-query-digest,执行命令，进行慢日志分析

./pt-query-digest /usr/local/mysql/data/slow.log

5、又报错 Can’t locate Digest/MD5.pm in @INC

[root@iZ2zebthf35ejlps5v87ksZ bin]# ./pt-query-digest /usr/local/mysql/data/slow.log
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pt-query-digest line 2470.
BEGIN failed--compilation aborted at ./pt-query-digest line 2470.

安装perl-Digest-MD5工具

yum -y install perl-Digest-MD5

终于可以运行了

./pt-query-digest /usr/local/mysql/data/slow.log

6、无脑命令如下

yum -y install perl-DBD-MySQL
yum -y install perl-Digest-MD5

cd /usr/local/src
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd /usr/local/src/percona-toolkit-3.1.0
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install

二、分析实战

1、执行工具pt-query-digest

./pt-query-digest /usr/local/src/slowsqlExample/slow0312.log

2、结果分析

找了一个慢sql，分析结果如下

[root@iZ2zebthf35ejlps5v87ksZ bin]# ./pt-query-digest /usr/local/src/slowsqlExample/slow0312.log 

第一部分
该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 360ms user time, 10ms system time, 22.56M rss, 187.09M vsz
工具执行时间
# Current date: Fri Mar 20 22:54:14 2020
运行分析工具的主机名    
# Hostname: iZ2zebthf35ejlps5v87ksZ
被分析的文件名
# Files: /usr/local/src/slowsqlExample/slow0312.log
语句总数量，唯一的语句数量，QPS，并发数
# Overall: 906 total, 21 unique, 0.02 QPS, 0.07x concurrency _____________
日志记录的时间范围
# Time range: 2020-03-11 12:22:13 to 2020-03-12 00:16:57
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
语句执行时间
# Exec time          2991s      2s     10s      3s      5s      1s      3s
锁占用时间
# Lock time          552ms    24us   371ms   609us   103us    12ms    57us
发送到客户端的行数
# Rows sent        167.53k       0  17.99k  189.35  487.09   1.22k       0
select语句扫描行数
# Rows examine     980.73M     238   1.96M   1.08M   1.95M 757.80k 753.18k
查询的字符数
# Query size       258.71k      17   1.77k  292.41  463.90  202.02  329.68


第二部分
# Profile
Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象
# Rank Query ID                        Response time   Calls R/Call V/M   
# ==== =============================== =============== ===== ====== ===== 
#    1 0xABD1DCCCCD5AA5128E10C27B34... 1246.6948 41.7%   283 4.4053  0.04 UPDATE ziweidashi_deviceinfo
#    2 0x6914B81AAD1785E50708ABD113...  877.6900 29.3%   339 2.5891  0.09 SELECT birthDay_notify
#    3 0x44D9474C6D5C58DD07B5FEEA0D...  299.4193 10.0%    71 4.2172  0.05 SELECT tmall_product_orders
#    4 0xA9BE84CBE3DAA9B1CDD9B5A9EC...  127.0137  4.2%    46 2.7612  0.04 SELECT daily_user_action_log
#    5 0xCF0E12117C971C3013142E3717...  118.3138  4.0%    49 2.4146  0.05 SELECT tmall_user_take_coupon_record
#    6 0x94263184D24186330B13193534...   97.0805  3.2%    35 2.7737  0.56 SELECT tgg_users
#    7 0xC51165F1287A2ECDA221AC1F54...   52.5870  1.8%    22 2.3903  0.04 SELECT util_user_task_log
#    8 0xB8004D6D8A7A7967E04CD81E26...   43.7895  1.5%    16 2.7368  0.08 SELECT daily_user_action_log
#    9 0x910E19224F33DAA6391927B8E8...   41.3720  1.4%    15 2.7581  1.17 SELECT qifugong_tianbi_record
# MISC 0xMISC                            86.7871  2.9%    30 2.8929   0.0 <12 ITEMS>


第三及后续部分，第一条查询语句 query id：0xABD1DCCCCD5AA5128E10C27B34BC04E7
# Query 1: 0.01 QPS, 0.03x concurrency, ID 0xABD1DCCCCD5AA5128E10C27B34BC04E7 at byte 355748
# Scores: V/M = 0.04
# Time range: 2020-03-11 12:24:03 to 2020-03-12 00:16:13
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         31     283
# Exec time     41   1247s      4s      8s      4s      5s   437ms      4s
# Lock time     69   386ms    24us   371ms     1ms    93us    21ms    44us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  18 180.00M 651.14k 651.45k 651.29k 650.62k       0 650.62k
# Query size    10  27.64k     100     100     100     100       0     100
# String:
数据库名
# Databases    taxen_ziweidashi
执行主机
# Hosts        118.190.93.166
执行用户
# Users        devAccount
查询时间占比
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `taxen_ziweidashi` LIKE 'ziweidashi_deviceinfo'\G
#    SHOW CREATE TABLE `taxen_ziweidashi`.`ziweidashi_deviceinfo`\G
UPDATE ziweidashi_deviceinfo
         SET expired = 1 
        WHERE createTime   <=   1583942580685\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  expired = 1 from ziweidashi_deviceinfo where  createTime   <=   1583942580685\G

# Query 2: 0.03 QPS, 0.07x concurrency, ID 0x6914B81AAD1785E50708ABD11319E02E at byte 13829
# Scores: V/M = 0.09
# Time range: 2020-03-11 12:22:13 to 16:05:47
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         37     339
# Exec time     29    878s      2s      4s      3s      4s   472ms      2s
# Lock time      5    29ms    31us     4ms    86us    98us   229us    66us
# Rows sent      0      24       0       2    0.07       0    0.32       0
# Rows examine  67 665.20M   1.96M   1.96M   1.96M   1.96M       0   1.96M
# Query size    59 154.47k     462     467  466.60  463.90    2.07  463.90
# String:
# Hosts        10.66.186.115
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'birthDay_notify'\G
#    SHOW CREATE TABLE `birthDay_notify`\G
# EXPLAIN /*!50100 PARTITIONS*/
select birthdayno0_.id as id1_1_, birthdayno0_.index_card_show_date as index_ca2_1_, birthdayno0_.userId as userId3_1_, birthdayno0_.push_content as push_con4_1_, birthdayno0_.card_content as card_con5_1_, birthdayno0_.birthday_userId as birthday6_1_, birthdayno0_.birthday_contactId as birthday7_1_, birthdayno0_.need_push as need_pus8_1_ from birthDay_notify birthdayno0_ where birthdayno0_.userId=1304747 and birthdayno0_.index_card_show_date='2020-03-11 00:00:00'\G

……省略

3、实例优化

找出这几条语句，对症下药，进行写法的修改、索引的设计，基本可以解决慢SQL问题。

例如query1的语句

    UPDATE ziweidashi_deviceinfo
         SET expired = 1 
        WHERE createTime   <=   1583942580685

分析后发现，这张表大部分数据的expired字段都是1，每次update都相当于全表查询、锁定了一次。

从逻辑上分析，是expired不等于1才修改的。

可以修改为

UPDATE ziweidashi_deviceinfo
         SET expired = 1 
        WHERE createTime   <=   1583942580685
        and expired != 1

直接从平均的5秒执行时间降低到了0.04秒。

其他语句类似。

三、常用命令

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 '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log

4、通过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

5、分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest –type=binlog mysql-bin000093.sql > slow_report10.log

6、分析general log

pt-query-digest  --type=genlog  localhost.log > slow_report11.log

四、参考资料

1、高性能mysql（第三版）

2、MySQL慢查询分析工具pt-query-digest详解作者：枫叶工作室。

3、Warning: prerequisite DBD::mysql 3 not found 作者：ora600

4、使用lcov时遇到错误can’t locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 …的错误作者：迷茫的叶

5、percona官网

mysql慢查询分析工具比较与实战

00 前言

01 安装排坑

二、分析实战

三、常用命令

四、参考资料

相关推荐

评论抢沙发

评论前必须登录！

随机推荐

分类

热门标签

最新文章

最新评论

其他操作2

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

00 前言

01 安装排坑

二、分析实战

三、常用命令

四、参考资料

相关推荐

评论 抢沙发

评论前必须登录！

随机推荐

分类

热门标签

最新文章

最新评论

其他操作2

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

评论抢沙发