mysql慢日志分析工具pt-query-digest_MySQL

1. 先安裝perl, 登錄 https://www.perl.org/get.html ,我選擇的是最新版。

解壓后來(lái)到perl的安裝目錄執(zhí)行

[root@VM-10 perl-5.22.0]# ./Configure -des -Dprefix=/usr/perl-5.22.0 -Dusethreads -Uversiononly

驗(yàn)證是否安裝成功

[root@VM-10 perl-5.22.0]# perl -version
This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi
Copyright 1987-2009, Larry Wall
Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl". If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

2.下載pt-query-digest，https://www.percona.com/downloads/percona-toolkit/，下載percona-toolkit_2.2.14.tar.gz 。

tar zxvf 解壓后，來(lái)到安裝目錄下/data/percona-toolkit-2.2.14執(zhí)行
[root@VM-10 perl-5.22.0]# perl Makefile.PL
[root@VM-10 perl-5.22.0]# make
[root@VM-10 perl-5.22.0]# make install

3.pt-query-digest /data/im-slow.log > /data/slow_report.log ，相比windows半天出不來(lái)，這個(gè)結(jié)果幾乎秒出。

Overall: 總共有多少條查詢(xún)，上例為總共266個(gè)查詢(xún)。
Time range: 查詢(xún)執(zhí)行的時(shí)間范圍。
unique: 唯一查詢(xún)數(shù)量，即對(duì)查詢(xún)條件進(jìn)行參數(shù)化以后，總共有多少個(gè)不同的查詢(xún)，該例為55。

# 34.3s user time, 310ms system time, 44.95M rss, 196.52M vsz
# Current date: Thu Aug 13 10:51:55 2015
# Hostname: VM-10.10.X.X
# Files: /data/im-slow.log
# Overall: 64.10k total, 179 unique, 0.00 QPS, 0.10x concurrency _________
# Time range: 2015-02-08 06:52:36 to 2015-07-29 09:02:02
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1405895s 5s 4899s 22s 32s 111s 10s
# Lock time 62995s 0 2476s 983ms 1ms 48s 144us
# Rows sent 5.69G 0 44.43M 93.04k 5.75 1.34M 0.99
# Rows examine 13.96G 0 693.12M 228.36k 44.45k 3.49M 0.99
# Query size 37.30M 6 335.57k 610.11 918.49 6.98k 537.02


# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================= ===== ========= ===== ========
# 1 0x0F2E6B958872280E 274452.3245 19.5% 26566 10.3310 2.39 SELECT gg_token gg_tenant
# 2 0x816F5D9DB1BD38C7 246396.5092 17.5% 15295 16.1096 31... INSERT gg_login_history
# 3 0xA3267D6CEFA1D5E3 203446.8729 14.5% 100 2034.4687 13... SELECT gg_token gg_login_history
# 4 0x67A347A2812914DF 170491.0463 12.1% 972 175.4023 44... SELECT app_chat_message
# 5 0x8BACA5842B7A7490 95999.5908 6.8% 5737 16.7334 37... INSERT app_offline_message
# 6 0x2B874BB5CB81DC1F 68234.7809 4.9% 105 649.8551 87.61 SELECT gg_token gg_login_history
# 7 0x848218FE4D9F7651 51218.6838 3.6% 210 243.8985 12.52 SELECT gg_token
# 8 0xBF9265005D4F8D80 40299.7591 2.9% 78 516.6636 28... SELECT gg_login_history gg_user gg_user_org gg_org
# 9 0xDCDBC481602798A4 39449.2606 2.8% 3193 12.3549 4.68 SELECT gg_token


Databases: 庫(kù)名
Users: 各個(gè)用戶(hù)執(zhí)行的次數(shù)（占比）
Query_time distribution : 查詢(xún)時(shí)間分布, 長(zhǎng)短體現(xiàn)區(qū)間占比
Tables: 查詢(xún)中涉及到的表
Explain: 示例

# Query 1: 0.03 QPS, 0.30x concurrency, ID 0x0F2E6B958872280E at byte 28156661
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.39
# Time range: 2015-06-16 00:05:14 to 2015-06-26 11:33:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 41 26566
# Exec time 19 274452s 5s 54s 10s 12s 5s 10s
# Lock time 0 24s 64us 40ms 885us 1ms 738us 839us
# Rows sent 0 80.00k 0 13 3.08 4.96 1.29 2.90
# Rows examine 8 1.15G 44.73k 45.59k 45.43k 44.45k 0 44.45k
# Query size 36 13.78M 542 544 543.73 537.02 0 537.02
# String:
# Databases im
# Hosts 10.100.252.38 (13301/50%)... 1 more
# Users im
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ########################################################
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `im` LIKE 'gg_token'\G
# SHOW CREATE TABLE `im`.`gg_token`\G
# SHOW TABLE STATUS FROM `im` LIKE 'gg_tenant'\G
# SHOW CREATE TABLE `im`.`gg_tenant`\G
# EXPLAIN /*!50100 PARTITIONS*/
select token0_.token_id as token_id1_119_, token0_.tenant_id as tenant_10_119_, token0_.account as account2_119_, token0_.client_id as client_i3_119_, token0_.create_date as create_d4_119_, token0_.host as host5_119_, token0_.last_auth_date as last_aut6_119_, token0_.session_id as session_7_119_, token0_.token as token8_119_, token0_.user_id as user_id9_119_ from gg_token token0_ left outer join gg_tenant tenant1_ on token0_.tenant_id=tenant1_.tenant_id where token0_.user_id='00014806a5a95f25ab' and tenant1_.tenant_id='0001456d92804b00'\G


# Query 2: 0.00 QPS, 0.02x concurrency, ID 0x816F5D9DB1BD38C7 at byte 17658778
# This item is included in the report because it matches --limit.
# Scores: V/M = 314.49
# Time range: 2015-03-21 13:31:25 to 2015-07-18 15:11:32
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 23 15295
# Exec time 17 246397s 5s 2498s 16s 32s 71s 11s
# Lock time 51 32142s 0 2476s 2s 125us 71s 89us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 9 3.55M 221 258 243.36 246.02 7.37 234.30
# String:
# Databases im
# Hosts 10.101.252.35 (7709/50%)... 3 more
# Users im
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s #############################################
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `im` LIKE 'gg_login_history'\G
# SHOW CREATE TABLE `im`.`gg_login_history`\G
INSERT INTO gg_login_history(login_history_id,username,resource,clientId,login_time,logout_time) VALUES('930e0b3e-d26b-4cc3-a02f-be0b1009744c','000148b62c6a6e290','Android','354273054207050','2015-04-13 23:04:42','2015-04-13 23:07:46')\G

最常用的使用方法：

(1)直接分析慢查詢(xún)文件:
pt-query-digest slow.log > slow_report.log
(2)分析最近1小時(shí)內(nèi)的查詢(xún)：
pt-query-digest --since=1h slow.log > slow_report.log
(3)分析指定時(shí)間范圍內(nèi)的查詢(xún)：
pt-query-digest slow.log --since '2015-04-17 10:30:00' --until '2015-04-17 18:00:00'>slow_report.log
(4)分析指含有select語(yǔ)句的慢查詢(xún)
pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report.log
(5) 查詢(xún)所有所有的全表掃描或full join的慢查詢(xún)
pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report.log

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com