MySQL InnoDB Cluster如何定位或找出超過事務大小的SQL？

潇湘隐者發表於2024-06-04
MySql

在MySQL InnoDB Cluster中，有一個系統變數/引數group_replication_transaction_size_limit控制著事務的大小，如下所示

mysql> select @@global.group_replication_transaction_size_limit;
+---------------------------------------------------+
| @@global.group_replication_transaction_size_limit |
+---------------------------------------------------+
|                                         150000000 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql>

如果資料庫中的事務大小超過了系統變數group_replication_transaction_size_limit指定的閾值，事務就會回滾,MySQL的錯誤日誌中會出現類似下面這樣的報錯資訊

2024-05-21T05:20:07.438958+08:00 3726581 [ERROR] [MY-011608] [Repl] Plugin group_replication reported: 'Error on session 3726581. Transaction of size 588366005 exceeds specified limit 150000000. To increase the limit please adjust group_replication_transaction_size_limit option.'
2024-05-21T05:20:07.439097+08:00 3726581 [ERROR] [MY-010207] [Repl] Run function 'before_commit' in plugin 'group_replication' failed

關於系統變數group_replication_transaction_size_limit，官方文件的介紹如下：

Use the system variable group_replication_transaction_size_limit to specify a maximum transaction size that the group accepts. In MySQL 8.0, this system variable defaults to a maximum transaction size of 150000000 bytes (approximately 143 MB). Transactions above this size are rolled back and are not sent to Group Replication s Group Communication System (GCS) for distribution to the group. Adjust the value of this variable depending on the maximum message size that you need the group to tolerate, bearing in mind that the time taken to process a transaction is proportional to its size.

簡單翻譯如下所示：

使用系統變數 group_replication_transaction_size_limit 指定(MGR)組所能接受的最大事務大小。在 MySQL 8.0 中，此係統變數預設最大事務大小為 150000000 位元組（約 143 MB）。超過此大小的事務將會被回滾，並且不會透過組複製的組通訊系統 （GCS）分發到其他組成員。 根據您需要組容忍的最大訊息大小調整此變數的值，請記住，處理事務所需的時間與其大小成正比。

那麼MySQL InnoDB Cluster出現這個錯誤，怎麼定位是哪一個事務的SQL超過大小限制而回滾了呢？ 因為找到問題的根源才是解決問題的前提。下面總結一下如何定位超過事務大小的SQL語句。

在展開話題前，我們先來了解一下“事務大小（Transaction of size）”這個概念。一般而言，其他資料庫一般會有大事務或小事務，長事務與短事務的概念，其實它們都是透過事務執行了多長時間來衡量判斷一個事務的大小與長短，（長事務與短事務，大事務與小事務）其實只是不同的名詞而已，個人覺得它們本質上就是一回事。但是很少資料庫有“事務大小（Transaction of size）”這個概念，那麼MySQL中事務的大小到底是指啥呢？ 說白了，其實它指的是事務生成的binlog的大小。有興趣就參考一下Query and Transaction size in MySQL[1]這篇文章。

那麼有沒有一個系統表或檢視能夠找出事務的大小呢？很遺憾，至少到目前為止，還沒有哪一個系統表或檢視包含事務的大小資訊，我們計算事務大小的唯一方法就是透過binlog來分析。在MySQL 8.0.2之前，我們透過解析binlog獲取事務開始與結束時binlog的大小來計算。而從MySQL 8.0.2 起，GTID 事件帶有一個新欄位：transaction_length。其值表示以位元組為單位的完整事務大小，從 GTID 事件本身的開始到事務的最後一個事件的結束。

那麼在開始解析binlog前先回答一個問題： “回滾事務的相關資訊會記錄到binlog中嗎？”。答案是回滾事務的資訊不回記錄到binlog中。其實這裡要分兩種情況：

1:事務裡面都是事務表的話，那麼如果事務回滾了，它是不會寫到binlog中去。

2：事務裡面修改了非事務表(nontransactional tables)的話,即使事務回滾了，它依然會寫binlog。

這裡就不展開了，有興趣，自己構建一個簡單的實驗，然後解析一下binlog就能驗證一些你的看法。那接下來就麻煩了，因為回滾事務的相關資訊沒有記錄到binlog，那就沒法展開分析了。關於這個問題，基本上有下面幾種方案解決：

1： 臨時調整生產環境中系統變數group_replication_transaction_size_limit的大小，讓這些大事務寫入binlog中，那麼就可以展開後續分析工作了。完成採集後，將系統變數group_replication_transaction_size_limit調整回原來的值。

2：如果UAT環境也能重現這個問題，那麼在UAT環境調整系統變數。 這個方案比較穩妥可靠一些。

接下來，我們可以使用部落格技術分享 | 如何透過 binlog 定位大事務？[2]中的指令碼來找出事務大小，如下所示

mysqlbinlog /data/mysql/bin_logs/mysql_binlog.000102 | grep "GTID$(printf '\t')last_committed" -B 1 \
 | grep -E '^# at' | awk '{print $3}' \
 | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
 | sort -n -r | head -n 10

但是這個指令碼只獲取了事務的大小，沒有獲取事務的其他資訊，那麼我們要怎麼才能獲取更多一點的詳細資訊呢？我們改寫一下指令碼，

mysqlbinlog /data/mysql/bin_logs/mysql_binlog.000102 | grep "GTID$(printf '\t')last_committed" -B 1 \
       | grep -E '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp,tmp);tmp=$1}'  \
       | sort -n -r -k 1 | head -n 10

如下截圖所示，第二列值為binlog中的pos

如上所示， 我們獲取了事務開始時的位置資訊。然後我們就開始將binlog解析出來，然後在kerry_bigtrans.log中搜尋關鍵字“at 591932661”

$ mysqlbinlog --base64-output=DECODE-ROWS  -vv /data/mysql/bin_logs/mysql_binlog.000102 > kerry_bigtrans.log

如下所示，這個案例搜尋到下面內容

# at 591932661
#240527  5:00:06 server id 1  end_log_pos 591932746 CRC32 0x46f449e8    Anonymous_GTID  last_committed=96110    sequence_number=96111   rbr_only=yes    original_committed_timestamp=1716757206761059   immediate_commit_timestamp=1716757206761059     transaction_length=198288631

這裡面有幾個資訊，紅色標記部分，binlog中事務的開始位置591932661，last_committed=96110 transaction_length=198288631 ，那麼我們只需要從這裡開始分析，到last_committed=96111結束部分的內容即可。如下所示

........................................................
# at 790221261
#240527  5:00:06 server id 1  end_log_pos 790221292 CRC32 0x760d2231    Xid = 44754468
COMMIT/*!*/;
# at 790221292
#240527  5:00:09 server id 1  end_log_pos 790221371 CRC32 0xbdf9ddcf    Anonymous_GTID  last_committed=96111    sequence_number=96112   rbr_only=yes    original_committed_timestamp=1716757209269202   immediate_commit_timestamp=1716757209269202     transaction_length=429

由於解析出來的SQL不是原始的SQL語句，如果可以的話，可以臨時開啟MySQL通用查詢日誌，結合起來就容易定位到超過事務大小限制的原始SQL語句了。關於我這個案例中，其實就是一些簡單的UPDATE語句造成的。具體原因是因為這些表中儲存了圖片，從而導致事務大小變得很大，其實UPDATE語句中並不涉及圖片相關欄位，但是因為系統變數binlog_row_image值是預設值FULL，所以binlog會記錄列的所有修改，即使欄位沒有發生變更也會記錄。這樣，如果表中儲存了圖片，那麼事務大小就變得很大了。這個也是 反對將圖片儲存到資料庫中的緣由之一。至於為什麼這個資料庫會儲存圖片呢？ 這個系統是公司從供應商購買的一個系統，我們DBA的話語權也非常小。即使我們早期郵件指出過這個問題，給出過建議，也不了了之。總之是一個曲折的故事。有些話也不便挑明。

那麼這裡還有種解決方法是調整系統變數group_replication_transaction_size_limit的大小。當然可以調整其大小來解決這個問題，但是這個是有風險的。官方給出這個預設值也肯定是經過大量的測試驗證的。所以不建議透過調整系統變數group_replication_transaction_size_limit的大小。這裡不展開討論這個話題。其實最根本的原因還是在於這個問題的根源是設計不合理，為什麼不從根源上解決問題？而要去修改系統變數去彌補一個錯誤的設計問題呢？

SQL語句找到了，解決方案也有幾個：

  • 1：修改設計，將圖片儲存到檔案系統，不要儲存到資料庫。那麼自然而然事務的大小就變得很小了。
  • 2：修改業務邏輯，一次更新少量記錄，避免事務大小超過閾值。
  • 3：修改系統binlog_row_image為MINIMAL，讓binlog只記錄修改列的值。這樣減少binlog記錄資訊，減小事務大小。

其實修改系統變數binlog_row_image為MINIMAL，查了一下資料，似乎也沒有問題，但是也不敢保證就不會帶來什麼問題。就這幾種方案來說，最好的方案還是方案1.這個方案測試上上策。

參考資料

[1]

Query and Transaction size in MySQL: https://mp.weixin.qq.com/s?__biz=Mzg2OTAwMTE3NQ==&mid=2247488703&idx=1&sn=7faa397616c52b663517d73fa844a1e4&chksm=cea2e607f9d56f116abf2499f656ff13ed744e5afeda9c490364fbf6b7ad3ebdee1f33fdc924&token=211971234&lang=zh_CN#rd

 [2]

技術分享 | 如何透過 binlog 定位大事務？: https://mp.weixin.qq.com/s/lR3CZyM8_Mz0nGC53MxcKg

相關文章