技術文章 » 範例分析MySQL中pt-query-digest工具的使用記錄

範例分析MySQL中pt-query-digest工具的使用記錄

2022-11-30 18:01:39
本篇文章給大家帶來了關於mysql的相關知識，其中主要介紹了一個用於分析mysql慢查詢紀錄檔的工具pt-query-digest，下面一起來看一下，希望對大家有幫助。

一、簡介

pt-query-digest是用於分析mysql慢查詢紀錄檔的工具，它還可以分析來自「SHOW PROCESSLIST」和MySQL的查詢 tcpdump中的協定資料。我們可以把分析紀錄檔輸出到指定的檔案中，通過分析紀錄檔檔案做對應的優化等操作。

二、下載並且安裝

  • 根據不同的系統安裝，我的系統是centos，所以直接選擇centos安裝，
  • 直接使用yum安裝
> wget https://downloads.percona.com/downloads/percona-toolkit/3.4.0/binary/redhat/7/x86_64/percona-toolkit-3.4.0-3.el7.x86_64.rpm
> yum install percona-toolkit-3.4.0-3.el7.x86_64.rpm
登入後複製
  • 或者使用原始碼包編譯安裝
> wget https://downloads.percona.com/downloads/percona-toolkit/3.4.0/source/debian/percona-toolkit-3.4.0.tar.gz
> tar -zxvf percona-toolkit-3.4.0.tar.gz
> cd percona-toolkit-3.4.0
> perl Makefile.PL PREFIX=/usr/local/percona-toolkit
> make && make install
登入後複製

三、安裝完成

  • mysql慢查詢日誌pt-query-digest的使用記錄

    程式設計師必備介面測試偵錯工具：

四、分析msql的慢查詢紀錄檔

  • pt-query-digest引數可以使用perldoc命令檢視
    perldoc /usr/bin/pt-query-digest
    登入後複製
  • 分析整個慢紀錄檔檔案
    pt-query-digest mysql-slow.log > slow_report.log
    登入後複製
  • 分析指定時間的紀錄檔since~until
    pt-query-digest mysql-slow.log --since '2022-10-01 00:00:00' --until '2022-11-05 00:00:00'  > slow_report_date_20221021-202221105.log
    登入後複製
    • –since: 表示開始時間
    • –until: 表示結束時間
  • 分析慢紀錄檔，並且儲存分析結果到mysql資料表
    pt-query-digest --user=root --password=123456 --history 
h=192.168.33.10,D=local_test_db,t=query_review --create-history-table  mysql-slow.log  --since 
'2022-11-01 00:00:00' --until '2022-11-05 00:00:00'
    登入後複製
    • –user: 資料庫使用者名稱
    • –password: 資料庫密碼
    • h: 資料庫 host
    • D: 資料庫名
    • t: 生成的表名

五、結果分析

第一部分: 總的一個分析概況

  • Overall：總共有多少條查詢

  • Time range：查詢執行的時間範圍

  • unique：唯一查詢數量，即對查詢條件進行引數化以後，總共有多少個不同的查詢

  • total：所有查詢總計時長

  • min：所有查詢最小時長

  • max：所有查詢最大時長

  • avg：所有查詢平均時長

  • 95%：把所有時長值從小到大排列，位置位於 95% 的那個時長數，這個數一般最具有參考價值

  • median：中位數，把所有時長值從小到大排列，位置位於中間那個時長數

  • # A software update is available:

# 23.7s user time, 15.8s system time, 35.67M rss, 249.01M vsz
說明：
執行過程中，在使用者中所花費的所有時間
執行過程中，在核心空間中所花費的所有時間
pt-query-digest 程序所分配的記憶體大小
pt-query-digest 程序所分配的虛擬記憶體大小

# Current date: Mon Nov  7 09:01:23 2022
說明：當前時間
# Hostname: localhost.localdomain
說明：執行pt-query-digest的主機名
# Files: mysql-slow.log
說明：被分析的檔名稱
# Overall: 44.78k total, 54 unique, 0.01 QPS, 0.07x concurrency __________
說明：
total: 語句總數量
unique: 唯一語句數量
QPS: 每秒查詢量
concurrency: 查詢的並行

# Time range: 2022-10-01 00:00:03 to 2022-11-04 16:05:24
說明：執行過程中紀錄檔記錄的時間範圍
# Attribute          total     min     max     avg     95%  stddev  median
說明：屬性            總計      最小值   最大值  平均值   95%  標準差   中位數
95%： 把所有時長值從小到大排列，位置位於 95% 的那個時長數，這個數一般最具有參考價值
median： 中位數，把所有時長值從小到大排列，位置位於中間那個時長數

# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        204553s      3s   1540s      5s     10s      8s      3s
說明：執行時間
# Lock time             8s       0   107ms   186us    80us     2ms    36us
說明：鎖佔用時間
# Rows sent        238.87M       0   2.88M   5.46k   11.95  68.22k    0.99
說明：傳送到使用者端的行數
# Rows examine      73.56G       0   5.01M   1.68M   3.86M 724.49k   1.32M
說明：掃描的語句行數
# Query size         8.18M      30   4.36k  191.46  511.45  224.63   72.65
說明：查詢的字元數
    登入後複製

第二部分：分析

  • Rank：所有語句的排名，預設按查詢時間降序排列，通過 –order-by 指定

    • –order-by Query_time:sum ：按總的查詢時間倒序
      排序引數介紹：
      sum Sum/total attribute value(預設值)
      min Minimum attribute value（最小值）
      max Maximum attribute value(最小值)
      cnt Frequency/count of the query(按出現sql次數)

  • Query ID：語句的 ID（去掉多餘空格和文字字元，計算 hash 值）

  • Response：總的響應時間

  • time：該查詢在本次分析中總的時間佔比

  • Calls：執行次數，即本次分析總共有多少條這種型別的查詢語句

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

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

  • Item：查詢物件

# Profile
說明：分析
# Rank Query ID                            Response time    Calls R/Call  
# ==== =================================== ================ ===== ======= 
#    1 0xC000AA97F210B2AEAE4933AF9B00296A  104236.2061 5... 30988  3.3638  0.03 SELECT xxx
#    2 0x974C6E6D54DB8B0DF505CA7BDC508686  32167.9607 15.7%  3418  9.4113  1.34 SELECT xxx 
#    3 0x6BE180C5804B585F25BB16550447DC6C  18453.0185  9.0%  2499  7.3842  0.92 SELECT xxx
#    4 0xADF16E3E9EB5D6B08245E39FF1428C9F  17873.4338  8.7%  3114  5.7397  0.84 SELECT xxx 
#    5 0x2964CD629A24595719659BDAEBCF0E6F  10648.5404  5.2%  1437  7.4103  0.93 SELECT xxx
#    6 0x50566E6DCF8FA562B88AE41AB1E32DC6   7424.3855  3.6%   303 24.5029 15.41 SELECT xxx
#    7 0xDB0A3D60F85C2212C476B144E1678AB8   5327.8370  2.6%  1627  3.2746  0.05 SELECT xxx
#    8 0x04BB0B332CEED517298AB06DE2A30AD6   3190.6822  1.6%   657  4.8564  1.36 SELECT xxx    
#   10 0xDAB0AF524151C621DC0E9B92AC002C38    526.6288  0.3%   140  3.7616  0.01 SELECT xxx 
# MISC 0xMISC                               1807.1067  0.9%    57 31.7036   0.0 <27 ITEMS>
登入後複製

第三部分：具體的sql統計和分析

  • pct：該 sql 語句某執行屬性佔所有慢查詢語句某執行屬性的百分比

  • total：該 sql 語句某執行屬性的所有屬性時間。

  • Count：sql 語句執行的次數。對應的 pct 表示此 sql 語句執行次數佔所有慢查詢語句執行次數的 % 比（下圖為 69%），對應的 total 表示總共執行了 30988 次。

  • Exec time：sql 執行時間

  • Lock time：sql 執行期間被鎖定的時間

  • Rows sent：傳輸的有效資料，在 select 查詢語句中才有值

  • Rows examine：總共查詢的資料，非目標資料。

  • Query_time distribution：查詢時間分佈

  • SQL 語句：下圖中為 select sleep(7)\G

# Query 1: 0.01 QPS, 0.03x concurrency, ID 0xC000AA97F210B2AEAE4933AF9B00296A at byte 221452362
說明：查詢佇列1：每秒查詢量，查詢的並行，佇列1的ID值，對應第二部分的Query ID, 221452362表示偏移量(檢視方法看下面的「檢視偏移」)
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.03
# Time range: 2022-10-01 00:00:05 to 2022-11-04 16:05:24
說明：sql語句在慢紀錄檔檔案mysql_slow.log出現的時間範圍
# Attribute    pct   total     min     max     avg     95%  stddev  median
說明：屬性      佔整個 總數      最小值  最大值   平均值  95%   標準差  中間值
           分析中
           的百分
           比                
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         69   30988
說明：執行語句總數量
# Exec time     50 104236s      3s      7s      3s      4s   303ms      3s
說明：執行時間
# Lock time     24      2s    22us    93ms    65us    66us   775us    38us
說明：鎖佔用時間
# Rows sent      0  70.53k       0     799    2.33    3.89   16.60    0.99
說明：傳送到使用者端的行數
# Rows examine  54  40.28G   1.32M   1.35M   1.33M   1.32M  15.65k   1.32M
說明：掃描語句的行數
# Query size    26   2.16M      73      73      73      73       0      73
說明：查詢的字元數
# String:
# Hosts        localhost
說明：使用的資料主機IP
# Users        xxx
說明：使用的使用者
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
說明：查詢時間分佈
# Tables
#    SHOW TABLE STATUS LIKE 'xxx'\G
#    SHOW CREATE TABLE `xxx`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from `table_name` where `updated_at` >= '2022-10-15 00:00:40'\G
說明：查詢的mysql語句
第三部分是每一種查詢比較慢的 sql 的詳細統計結果
pct：該 sql 語句某執行屬性佔所有慢查詢語句某執行屬性的百分比
total：該 sql 語句某執行屬性的所有屬性時間。
Count：sql 語句執行的次數。
Exec time：sql 執行時間
Lock time：sql 執行期間被
登入後複製

六 檢視偏移

  • 可以利用偏移量在慢查詢紀錄檔檔案中到查詢到具體的 SQL 語句，查詢方法如下：
[localhost]# tail -c +221452362 ./mysql-slow.log | head
t: root[root] @ localhost []  Id: 13704150
# Query_time: 7.058835  Lock_time: 0.000040 Rows_sent: 2  Rows_examined: 1392521
SET timestamp=1665763267;
select * from `xxxxxx` where `updated_at` >= '2022-10-15 00:00:40';
# User@Host: root[localhost] @ localhost []  Id: 13704174
# Query_time: 7.445741  Lock_time: 0.000015 Rows_sent: 3  Rows_examined: 2214002
SET timestamp=1665763267;
select xxx from table where xxx
# Time: 221015 008
# User@Host: root[localhost] @ localhost []  Id: 13704414
登入後複製

以上就是範例分析MySQL中pt-query-digest工具的使用記錄的詳細內容，更多請關注TW511.COM其它相關文章！