題圖?Photo by?Charles Deluvio?on?Unsplash

00 前言

在進(jìn)行mysql性能優(yōu)化的時(shí)候，第一個(gè)想到的便是查看慢sql。

但是對(duì)于慢sql有沒(méi)有什么好的工具進(jìn)行分析呢？

推薦兩個(gè)工具：mysqldumpslow及pt-query-digest。

mysqlslowdump較為簡(jiǎn)單，常用命令：

#得到返回記錄最多的20個(gè)sqlmysqldumpslow -s r -t 20 slowSQl.log# 得到平均訪(fǎng)問(wèn)次數(shù)最多的20條sqlmysqldumpslow -s ar -t 20 slowSQl.log

如果linux上沒(méi)有安裝mysqlslowdump，yum install安裝下就行了。

本文主要說(shuō)下pt-query-digest。

pt-query-digest可以非常清晰地將slowSQL分析出來(lái)，類(lèi)似oracle的AWR報(bào)告。

# 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工具包的其中一個(gè)工具。
這個(gè)工具包下還有很多實(shí)用的性能分析輔助工具。

01 安裝排坑

1、下載

# 進(jìn)入安裝目錄cd /usr/local/src# 下載percona-toolkit 工具包wget percona.com/get/percona-toolkit.tar.gz# 解壓tar zxf percona-toolkit.tar.gz# 進(jìn)入解壓文件夾cd /usr/local/src/percona-toolkit-3.1.0# 安裝perl模塊，制定依賴(lài)路徑perl Makefile.PL PREFIX=/usr/local/percona-toolkit

2、報(bào)錯(cuò) prerequisite DBD::mysql 3 not found

報(bào)錯(cuò)如下，找不到DBD包

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

百度問(wèn)題，找到鏈接，https://blog.csdn.net/heizistudio/article/details/45724707?locationNum=8&fps=1

安裝依賴(lài)包

yum install perl-DBD-MySQL

然后重新執(zhí)行命令

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

3、安裝

make && make install

安裝后內(nèi)容如下

……Installing /usr/local/percona-toolkit/bin/pt-summaryInstalling /usr/local/percona-toolkit/bin/pt-table-syncAppending 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

發(fā)現(xiàn)沒(méi)找到pt-query-digest命令，是因?yàn)閎ash命令默認(rèn)是從/usr/bin下找的；

如果rpm安裝，會(huì)默認(rèn)添加到/usr/bin下；

而我們現(xiàn)在是編譯二進(jìn)制安裝到，并且默認(rèn)是裝到了/usr/local/percona-toolkit下，發(fā)現(xiàn)本文件夾下有個(gè)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,執(zhí)行命令，進(jìn)行慢日志分析

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

5、又報(bào)錯(cuò) Can't locate Digest/MD5.pm in @INC

[root@iZ2zebthf35ejlps5v87ksZ bin]# ./pt-query-digest /usr/local/mysql/data/slow.logCan'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

終于可以運(yùn)行了

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

6、無(wú)腦命令如下

yum -y install perl-DBD-MySQLyum -y install perl-Digest-MD5cd /usr/local/srcwget percona.com/get/percona-toolkit.tar.gztar zxf percona-toolkit.tar.gzcd /usr/local/src/percona-toolkit-3.1.0perl Makefile.PL PREFIX=/usr/local/percona-toolkitmake && make install

二、分析實(shí)戰(zhàn)

1、執(zhí)行工具pt-query-digest

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

2、結(jié)果分析

找了一個(gè)慢sql，分析結(jié)果如下

[root@iZ2zebthf35ejlps5v87ksZ bin]# ./pt-query-digest /usr/local/src/slowsqlExample/slow0312.log 第一部分該工具執(zhí)行日志分析的用戶(hù)時(shí)間，系統(tǒng)時(shí)間，物理內(nèi)存占用大小，虛擬內(nèi)存占用大小# 360ms user time, 10ms system time, 22.56M rss, 187.09M vsz工具執(zhí)行時(shí)間# Current date: Fri Mar 20 22:54:14 2020運(yùn)行分析工具的主機(jī)名 # Hostname: iZ2zebthf35ejlps5v87ksZ被分析的文件名# Files: /usr/local/src/slowsqlExample/slow0312.log語(yǔ)句總數(shù)量，唯一的語(yǔ)句數(shù)量，QPS，并發(fā)數(shù)# Overall: 906 total, 21 unique, 0.02 QPS, 0.07x concurrency _____________日志記錄的時(shí)間范圍# Time range: 2020-03-11 12:22:13 to 2020-03-12 00:16:57# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======語(yǔ)句執(zhí)行時(shí)間# Exec time 2991s 2s 10s 3s 5s 1s 3s鎖占用時(shí)間# Lock time 552ms 24us 371ms 609us 103us 12ms 57us發(fā)送到客戶(hù)端的行數(shù)# Rows sent 167.53k 0 17.99k 189.35 487.09 1.22k 0select語(yǔ)句掃描行數(shù)# Rows examine 980.73M 238 1.96M 1.08M 1.95M 757.80k 753.18k查詢(xún)的字符數(shù)# Query size 258.71k 17 1.77k 292.41 463.90 202.02 329.68第二部分# ProfileRank：所有語(yǔ)句的排名，默認(rèn)按查詢(xún)時(shí)間降序排列，通過(guò)--order-by指定Query ID：語(yǔ)句的ID，(去掉多余空格和文本字符，計(jì)算hash值)Response：總的響應(yīng)時(shí)間time：該查詢(xún)?cè)诒敬畏治鲋锌偟臅r(shí)間占比calls：執(zhí)行次數(shù)，即本次分析總共有多少條這種類(lèi)型的查詢(xún)語(yǔ)句R/Call：平均每次執(zhí)行的響應(yīng)時(shí)間V/M：響應(yīng)時(shí)間Variance-to-mean的比率Item：查詢(xún)對(duì)象# 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>第三及后續(xù)部分，第一條查詢(xún)語(yǔ)句 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:數(shù)據(jù)庫(kù)名# Databases taxen_ziweidashi執(zhí)行主機(jī)# Hosts 118.190.93.166執(zhí)行用戶(hù)# Users devAccount查詢(xún)時(shí)間占比# 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`\GUPDATE 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、實(shí)例優(yōu)化

找出這幾條語(yǔ)句，對(duì)癥下藥，進(jìn)行寫(xiě)法的修改、索引的設(shè)計(jì)，基本可以解決慢SQL問(wèn)題。

例如query1的語(yǔ)句

UPDATE ziweidashi_deviceinfo SET expired = 1 WHERE createTime <= 1583942580685

分析后發(fā)現(xiàn)，這張表大部分?jǐn)?shù)據(jù)的expired字段都是1，每次update都相當(dāng)于全表查詢(xún)、鎖定了一次。

從邏輯上分析，是expired不等于1才修改的。

可以修改為

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

直接從平均的5秒執(zhí)行時(shí)間降低到了0.4秒。?

其他語(yǔ)句類(lèi)似。

三、常用命令

1.分析慢查詢(xún)文件

pt-query-digest slow.log > slow_report.log

2.分析最近12小時(shí)內(nèi)的查詢(xún)

pt-query-digest --since=12h slow.log > slow_report2.log

3.分析指定時(shí)間范圍內(nèi)的查詢(xún)

pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log

4、通過(guò)tcpdump抓取mysql的tcp協(xié)議數(shù)據(jù)，然后再分析

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txtpt-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慢查詢(xún)分析工具pt-query-digest詳解^[1]?作者：楓葉工作室。

3、Warning: prerequisite DBD::mysql 3 not found^[2]?作者：ora600

4、使用lcov時(shí)遇到錯(cuò)誤can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 ...的錯(cuò)誤^[3]?作者：迷茫的葉

5、percona官網(wǎng)^[4]

References

[1]?MySQL慢查詢(xún)分析工具pt-query-digest詳解:?https://www.cnblogs.com/phpfeng/p/9328705.html[2]?Warning: prerequisite DBD::mysql 3 not found:?https://blog.csdn.net/heizistudio/article/details/45724707?locationNum=8&fps=1[3]?使用lcov時(shí)遇到錯(cuò)誤can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 ...的錯(cuò)誤:?https://blog.csdn.net/ytx2014214081/article/details/79595519[4]?percona官網(wǎng):?https://www.percona.com/doc/percona-toolkit/2.2/index.html

總結(jié)

以上是生活随笔為你收集整理的truncate table 非常慢_你真的知道怎么分析mysql的慢sql吗？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。