工具分享丨分析GreatSQL Binglog神器

在GreatSQL中，Binlog可以說是 GreatSQL 中比較重要的日誌了，在日常開發及運維過程中經常會遇到。Binlog即Binary Log，二進位日誌文件，也叫作變更日誌（Update Log）。詳細Binglog日誌介紹 Binglog主要應用於數據恢復和數據複製，但是在Binlog ...

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

參考閱讀

Enjoy GreatSQL