古人云：工欲善其事，必先利其器。作為一名優秀的mysql dba也需要有掌握幾個好用的mysql管理工具，所以我也一直在整理和查詢一些能夠便於管理mysql的利器。以後的一段時間內，將會花一大部分的精力去搜尋這些工具。

效能的管理一直都是擺在第一位的，dba的很多工作管理層都看不到也沒有辦法衡量價值，但是如果一個系統慢的跟蝸牛一樣，dba透過監控調優把系統從崩潰邊緣重新拉回到高鐵時代。這種價值和觸動應該是巨大的。（很多企業的領導認為系統跑不動了就需要換更快的CPU、更大的記憶體、更快的儲存，而且這還不是少數，所以DBA的價值也一直體現不出來，薪水自然也就不會很高）

mysql的日誌是跟蹤mysql效能瓶頸的最快和最直接的方式了，系統效能出現瓶頸的時候，首先要開啟慢查詢日誌，進行跟蹤；這段時間關於慢查詢日誌的管理和檢視已經整理過兩篇文章了，不經意間又發現了一個檢視慢查詢日誌的工具：mk-query-digest，這個工具網上號稱mysql dba必須掌握的十大工具之首。

以下是軟體的介紹：

1、軟體的安裝,下載地址：

2、連結：密碼：ybtm

2、軟體的使用，pt-query-digest --help

Usage: pt-query-digest [OPTIONS] [FILES] [DSN]

常用的查詢語句：

pt-query-digest JOEONE-IPOSDS-SLOW.LOG> JOEONE-IPOSDS-SLOW1.log 把當前的結果輸出到檔案slow_report.log
pt-query-digest --since=24h JOEONE-IPOSDS-SLOW.LOG > slow_report2.log 分析最近24小時的結果
pt-query-digest --since '2015-01-09 09:30:00' --until '2015-02-01 09:30:00' JOEONE-IPOSDS-SLOW.LOG> slow_report3.log 分析規定時間段的結果
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' JOEONE-IPOSDS-SLOW.LOG> slow_report6.log 所有的全表掃描或full join的慢查詢

加入引數create-review-table，可以把結果寫到mysql的表中

pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_table --create-review-table JOEONE-IPOSDS-SLOW.LOG

3、分析結果的檢視

慢查詢日誌的結果可以分為三個部分：整體的概要、總體的sql語句、每條sql語句的詳細情況

3.1 整體的概要

# 21.9s user time, 80ms system time, 95.84M rss, 270.52M vsz
# Current date: Tue Feb 10 00:47:57 2015
# Hostname: DBR-T01     主機名稱
# Files: /data/IPOSDS-SLOW.LOG 慢日誌檔案
# Overall: 27.94k total, 181 unique, 0.01 QPS, 0.19x concurrency
# Time range: 2015-01-07 05:31:12 to 2015-02-09 13:02:08         結果的時間範圍
# Attribute          total     min     max     avg     95% stddev median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        553798s     10s   1829s     20s     33s     23s     16s
# Lock time           469s       0     34s    17ms   332us   474ms   144us
# Rows sent          5.92G       0 19.10M 222.29k   2.16k   1.34M       0
# Rows examine     282.33G       0 618.66M 10.35M 11.87M   9.62M 11.87M
# Query size        55.39M      17 781.03k   2.03k 487.09 33.70k 363.48

上面摘要顯示/data/IPOSDS-SLOW.LOG中共有27940次SQL語句（27.94k），其中181條跳語句是不重複的；

Exec time總時間表示這些SQL的總體執行時間，這部分時間參考Time range，可以大概知道系統的壓力情況；

Lock time 顯示鎖的時間，太長的鎖等待，說明程式設計有問題；

Rows sent 傳送客戶端的行數

Rows examine 表示掃描的行數，Rows examine和Rows sent的比值，可以初步判斷索引的選擇性問題；

Query size：查詢語句的字元數

以上的那些數值主要參考95%的資料平均結果；

3.2 語句整體概況

# Profile
# Rank   Query ID                 Response time Calls R/Call V/M   Item
# ==== ================== ================= ===== ======= ===== ==========
#    1 0xB6CE88A271A1C8C5 303925.9823 54.9% 17430 17.4369 1.36 UPDATE SELECT spkcb com_base_guige? com_base_shangpin com_base_kehu
#    2 0x67A347A2812914DF 69120.9420 12.5% 1306 52.9257 88.15 SELECT pdrwdmx
#    3 0xABB9B3395E22262F 20962.6758 3.8% 1180 17.7650 1.63 UPDATE qtlsdtj
#    4 0x6CB2B38E9A6BA060 20712.8072 3.7% 1438 14.4039 7.20 SHOW TABLE STATUS
#    5 0xE07B65EBB8252150 19329.7730 3.5%   896 21.5734 6.36 SELECT qtlsd
#    6 0xB95D3B1113D55B10 16386.1202 3.0% 1178 13.9101 7.74 SELECT qtlsdmx
#    7 0x2D709387799372B6 13340.1712 2.4%   229 58.2540 35... UPDATE zdjhd zdjhdtj
#    8 0x5F21295B4F65ADA4 12898.3620 2.3%   906 14.2366 1.03 INSERT UPDATE spkcb
#    9 0x35B910EAB55A5C9C   8953.4060 1.6%   243 36.8453 9.91 SELECT UNION pdzb_?_?_? zdpdd zdpddmx
#   10 0xEAD308EC0677AB59   7974.1363 1.4%   385 20.7120 4.76 INSERT SELECT UNION SELECT UNION SELECT UNION
#   11 0x54AA6A3907264713   7388.4440 1.3%   619 11.9361 0.32 SELECT qtlsd
#   12 0x223C39F84004B5A3   6455.4302 1.2%   266 24.2685 47.24 SELECT qtlsd
#   13 0x2F7AD31185241551   6034.4285 1.1%   142 42.4960 3.74 UPDATE ckkw cangku
#   14 0xD657E241B398C447   5546.4095 1.0%   145 38.2511 12.98 SELECT UNION pdzb_?_?_? zdpdd zdpddmx
#   15 0xC4BF8AE027FAD479   3976.7995 0.7%    72 55.2333 6.44 DELETE SELECT qdtjdmx qdtjd
#   16 0xA5288845FB6A8036   3817.4887 0.7%   274 13.9324 0.83 INSERT SELECT UPDATE spkcb zdjhdmx zdjhd

--Rank:分析的所有查詢語句的排名，預設按查詢時間降序排序，可以透過--order-by指定排序方式

--Query ID：查詢語句的指紋，去掉了多餘空格、和文字字元

--Response time：響應時間，佔所有響應時間的百分比（UPDATE SELECT spkcb com_base_guige? com_base_shangpin com_base_kehu這條語句佔用了54.9%的時間，首要調優物件）

--Calls：查詢執行的次數

--R/Call：每次執行的平均響應時間

--V/M：響應時間Variance-to-mean的比率

--Item：查詢語句

--最後一行沒有包括在報告中的查詢合計統計資訊，如使用了選項--limit和—outliers

第三部分，詳細的執行語句

# Query 1: 0.01 QPS, 0.19x concurrency, ID 0xB6CE88A271A1C8C5 at byte 37907885   （Query 1按照第二部分的順序進行排序）
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.36
# Time range: 2015-01-08 00:02:19 to 2015-01-26 08:38:49
# Attribute    pct   total     min     max     avg     95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         62   17430 （pct 佔總數的比例，total執行的總次數，17430/27940=62%）
# Exec time     54 303926s     10s    175s     17s     24s      5s     16s    （執行時間的分佈）
# Lock time      2     10s    74us   896ms   551us   247us    12ms   159us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine 72 204.54G   4.57M 12.15M 12.02M 11.87M 144.66k 11.87M         （從這邊可以看出，掃描的行數很多）
# Query size    10   6.05M     361     365 364.09 363.48    0.67 363.48
# String:
# Databases    ipos    （資料庫名稱）
# Hosts        192.168.0.10 （伺服器主機名）
# Users        root
# Query_time distribution
#   1us
# 10us
# 100us
#   1ms
# 10ms
# 100ms
#    1s
# 10s+ #######################這個語句涉及到的表名稱###############################
# Tables
#    SHOW TABLE STATUS FROM `ipos` LIKE 'ipos_spkcb'\G
#    SHOW CREATE TABLE `ipos`.`ipos_spkcb`\G
#    SHOW TABLE STATUS FROM `ipos` LIKE 'com_base_guige1'\G
#    SHOW CREATE TABLE `ipos`.`com_base_guige1`\G
#    SHOW TABLE STATUS FROM `ipos` LIKE 'com_base_guige2'\G
#    SHOW CREATE TABLE `ipos`.`com_base_guige2`\G
#    SHOW TABLE STATUS FROM `ipos` LIKE 'com_base_shangpin'\G
#    SHOW CREATE TABLE `ipos`.`com_base_shangpin`\G
#    SHOW TABLE STATUS FROM `ipos` LIKE 'com_base_kehu'\G
#    SHOW CREATE TABLE `ipos`.`com_base_kehu`\G

###############################完整的SQL語句#############################################
Update ipos_spkcb a
Set
gg1_id=(Select id From com_base_guige1 gg1 Where gg1.ggdm = a.gg1dm limit 1 ),
gg2_id=(Select id From com_base_guige2 gg2 Where gg2.ggdm = a.gg2dm limit 1 ),
sp_id = (Select id From com_base_shangpin sp Where sp.spdm = a.spdm),
zd_id = (Select id From com_base_kehu kh Where kh.khdm = a.drp_ckdm)
Where drp_mid= 'CDA07AJB1441321015A'\G

透過以上分析，再執行explain可以檢視這個語句的執行計劃；

總結：pt-query-digest語句給出的報告，相對於前面的2種方式，已經有了明顯的改進，最主要體現在可以從宏觀去看每條語句對系統的影響情況；

相信根據步驟分析了整個慢日誌檔案後，應該可以知道，哪種型別的語句、那種幾條語句消耗了效能，這些語句是突發的還是平時執行都是這種情況的。

透過檢視這些表的結構和語句的執行計劃後，可以進行相應的調優；

........................................................................................................................................................................

本文作者：JOHN，某上市公司DBA，業餘時間專注於資料庫的技術管理，從管理的角度去運用技術。

ORACLE技術部落格：ORACLE 獵人筆記資料庫技術群：367875324 （請備註ORACLE管理）

........................................................................................................................................................................

mysql 必須掌握的工具pt-query-digest

相關文章