工具分享丨分析GreatSQL Binglog神器

GreatSQL發表於2024-03-25
SQL

file

在GreatSQL中，Binlog可以說是 GreatSQL 中比較重要的日誌了，在日常開發及運維過程中經常會遇到。Binlog即Binary Log，二進位制日誌檔案，也叫作變更日誌（Update Log）。

詳細Binglog日誌介紹

Binglog主要應用於資料恢復和資料複製，但是在Binlog中也含有非常多有價值的資訊，比如說：

  • 資料修改事件
  • 表結構修改事件
  • 狀態修改事件
  • 事務控制事件
  • 管理語句事件
  • ......

事務控制事件涵蓋了事務的起始時間、起始位置、結束時間和結束位置。透過這些詳細資訊，我們能夠計算事務的大小，進而評估其是否屬於大型事務，以及是否可能引起主從同步的延遲問題，及時發現大事務,可避免複製故障。

簡介

本文分享的神器的名字就叫做binlog_summary，出自陳臣老師的手筆，也是開源的Python指令碼檔案，開源地址

下載

執行此工具需要有Python環境，若沒有python環境請自行下載

下載binlog_summary.py指令碼，並授權

$ wget https://raw.githubusercontent.com/slowtech/dba-toolkit/master/mysql/binlog_summary.py
$ chmod 755 binlog_summary.py

先用./binlog_summary.py -h檢視下幫助

$ ./binlog_summary.py -h
usage: binlog_summary.py [-h] [-f BINLOG_TEXT_FILE] [--new] [-c {tps,opr,transaction}] [--start START_DATETIME] [--stop STOP_DATETIME] [--sort SORT_CONDITION] [-e]
                         [--limit LIMIT]

options:
  -h, --help            show this help message and exit
  -f BINLOG_TEXT_FILE, --file BINLOG_TEXT_FILE
                        Binlog text file, not the Raw binary file
  --new                 Make a fresh start
  -c {tps,opr,transaction}, --command {tps,opr,transaction}
                        Command type: [tps, opr, transaction],tps: transaction per second, opr: dml per table, transaction: show transaction info
  --start START_DATETIME
                        Start datetime, for example: 2004-12-25 11:25:56
  --stop STOP_DATETIME  Stop datetime, for example: 2004-12-25 11:25:56
  --sort SORT_CONDITION
                        Sort condition: time or size, you can use it when command type is transaction
  -e, --extend          Show transaction info in detail,you can use it when command type is transaction
  --limit LIMIT         Limit the number of rows to display

其中引數介紹:

  • -f：Binlog 透過 mysqlbinlog 解析後的文字檔案。注意，是文字檔案，不是Binlog原始檔案。

  • --new：工具輸出預設儲存在sqlite3資料庫中。使用--new可刪除舊資料庫。分析新binlog時需指定。

  • -c：指定命令的型別。支援的命令型別有：

    • tps：分析例項的TPS資訊
    • opr：分析表的操作情況
    • transaction：分析事務資訊

  • --start/--stop：指定時間範圍

  • --sort：事務排序方式，僅針對-c選擇為transaction模式

    • size，按事務大小排序
    • time，按事務的持續時間排序

  • -e：輸出事務詳細操作資訊，僅針對-c選擇為transaction模式

  • limit：限制輸出的行數。

最佳實踐

前置工作

由於工具只支援解析經mysqlbinlog處理後的文字檔案，首先需要進行解析轉換。

先從GreatSQL資料目錄中複製一份需要分析的binlog檔案。

$ cp /data/GreatSQL/binlog.000021 ./
$ du -h binlog.000021 
2.0G    binlog.000021

先使用 mysqlbinlog 解析 Binlog

  • 推薦使用引數-v（偽SQL）和--base64-output=decode-rows（不顯示Base64編碼結果），這樣生成的文字檔案最小，相應地，binlog_summary工具的解析速度也會更快。
$ mysqlbinlog --base64-output=decode-rows -v binlog.000021 > ./greatsql-bin.000001.txt

解析後的檔案大小大概在1.7G左右

$ du -h greatsql-bin.000001.txt            
1.7G    greatsql-bin.000001.txt

分析例項的TPS資訊

使用-f指定解析後的檔案，-c選擇分析TPS資訊，--limit選擇只顯示5行

$ ./binlog_summary.py -f ./greatsql-bin.000001.txt -c tps --limit 5
COMMIT_TIME        TPS                
2024-02-04 14:28:45 1                  
2024-02-04 14:28:56 1                  
2024-02-04 14:28:57 2                  
2024-02-04 14:28:58 1                  
2024-02-04 14:28:59 1

這裡TPS是根據事務的提交時間進行統計的。獲取如此精細TPS資訊通常需要透過Binlog來實現，一般的監控手段難以達到如此精細的水平

當然，也可以對TPS進行排序，只需要加上管道和sort。

  • k：對第三列排序
  • n：是按照數值（預設是字元）的大小進行排序
  • r：進行逆序排序
$ ./binlog_summary.py -f ./greatsql-bin.000001.txt -c tps --limit 5 | sort -k 3 -n 
COMMIT_TIME        TPS                
2024-02-04 14:28:45 1                  
2024-02-04 14:28:56 1                  
2024-02-04 14:28:58 1                  
2024-02-04 14:28:59 1                  
2024-02-04 14:28:57 2

分析表的操作情況

如果要分析表操作情況，需要-c選擇opr功能模式，NUMS是執行次數，DML_TYPE是執行SQL的型別

$ ./binlog_summary.py -f ./greatsql-bin.000001.txt -c opr --limit 5
TABLE_NAME         DML_TYPE           NUMS               
test_db.idx_test   INSERT             10000001           
aptest.sys_user    INSERT             1002000            
test_db.t1         INSERT             524288             
aptest.sys_dept    INSERT             101000             
aptest.sys_user    DELETE             1000

分析Binlog中的大事務

$ ./binlog_summary.py -f ./greatsql-bin.000001.txt -c transaction --sort size --limit 5
TRANS_NAME         BEGIN_TIME         COMMIT_TIME        BEGIN_LOG_POS      COMMIT_LOG_POS     DURATION_TIME      SIZE               
t21                2024-02-05 16:14:32 2024-02-05 16:23:53 14319911           869025248          561                854705337          
t33                2024-02-20 16:02:41 2024-02-20 16:08:21 913362031          1425529317         340                512167286          
t32                2024-02-20 16:01:37 2024-02-20 16:02:06 881773547          913361946          29                 31588399           
t31                2024-02-20 16:00:14 2024-02-20 16:00:15 871100835          881773462          1                  10672627           
t20                2024-02-04 14:29:43 2024-02-04 14:29:43 7163617            14319264           0                  7155647

其中，各個引數解析如下

  • TRANS_NAME：事務編號
  • BEGIN_TIME：事務開始時間
  • COMMIT_TIME：事務提交時間
  • BEGIN_LOG_POS：事務的開始位置點
  • COMMIT_LOG_POS：事務的結束位置點
  • DURATION_TIME：事務的持續時間，單位秒。其中，DURATION_TIME = COMMIT_TIME - BEGIN_TIME
  • SIZE：事務的大小，單位位元組，其中，SIZE = COMMIT_LOG_POS - BEGIN_LOG_POS

拿到事務的大小，可以粗略地判斷這個Binlog中是否存在大事務。如果要進一步分析事務中包含哪些操作，需加上–extend，如：

$ ./binlog_summary.py -f ./greatsql-bin.000001.txt -c transaction --sort size --extend --limit 5
TRANS_NAME      BEGIN_TIME           COMMIT_TIME          BEGIN_LOG_POS   COMMIT_LOG_POS  DURATION_TIME   SIZE
t21             2024-02-05 16:14:32  2024-02-05 16:23:53  14319911        869025248       561             854705337
├──             test_db.idx_test                          INSERT          10000000
t33             2024-02-20 16:02:41  2024-02-20 16:08:21  913362031       1425529317      340             512167286
├──             aptest.sys_user                           INSERT          1000000
t32             2024-02-20 16:01:37  2024-02-20 16:02:06  881773547       913361946       29              31588399
├──             aptest.sys_dept                           INSERT          100000
t31             2024-02-20 16:00:14  2024-02-20 16:00:15  871100835       881773462       1               10672627
├──             aptest.tap_dept_tax                       INSERT          1000
t20             2024-02-04 14:29:43  2024-02-04 14:29:43  7163617         14319264        0               7155647
├──             test_db.t1                                INSERT          262144

效能

實測分析一個2G的Binlog，大概分析時間是2分半，也不慢

$ time python binlog_summary.py -f ./greatsql-bin.000001.txt --new -c transaction --sort size --extend --limit 5
......結果不展示
154.86s user 2.26s system 99% cpu 2:37.47 total

參考閱讀

  • Binlog分析利器-binlog_summary.py
  • binlog_summary.py原始碼

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章