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

来源:https://www.cnblogs.com/kerrycode/p/18231785
-Advertisement-
Play Games

在MySQL InnoDB Cluster中,有一個系統變數/參數group_replication_transaction_size_limit控制著事務的大小,如下所示 mysql> select @@global.group_replication_transaction_size_limit ...


在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

掃描上面二維碼關註我 如果你真心覺得文章寫得不錯,而且對你有所幫助,那就不妨幫忙“推薦"一下,您的“推薦”和”打賞“將是我最大的寫作動力! 本文版權歸作者所有,歡迎轉載,但未經作者同意必須保留此段聲明,且在文章頁面明顯位置給出原文連接.
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 問題描述 使用vmware新建伺服器,明明給系統盤分配了一個200GB的磁碟空間,伺服器創建成功後,但是伺服器中啟動一個大小約為11GB的docker服務卻提示 磁碟空間不足 no space left on device。 解決過程 檢查磁碟掛載詳情 `[root@SkyEye ~]# lsblk ...
  • 介紹 Apache DolphinScheduler是一個分散式易擴展的開源分散式調度系統,支持海量數據處理,具有任務流程調度、任務流程編排、任務監控告警、工作流引擎等功能。 本文將介紹如何將Apache DolphinScheduler集成到 Java Springboot 項目中,以實現更靈活和 ...
  • 近日,中國電信天翼雲的自研產品HBlock憑藉“存儲資源盤活技術”成功入選《國家工業和信息化領域節能降碳技術裝備推薦目錄(2024年版)》(以下簡稱《目錄》),這一成績是對中國電信天翼雲以科技創新賦能千行百業綠色低碳發展的認可。存儲資源盤活技術作為該目錄唯一的存儲軟體技術,能夠快速整合現網資源,大幅... ...
  • 一般資料庫中可能存在長連接或短連接會話。長連接是相對於通常的短連接而說的,也就是長時間保持客戶端與服務端的連接狀態。如果不從應用程式入手,如何判斷SQL Server資料庫中哪些會話是長連接呢? 在SQL Server中有連接與會話的概念,一般而言,連接是物理概念,而會話則是邏輯上的概念。兩者是對同 ...
  • 作為阿裡早期的開源產品,DataX是一款非常優秀的數據集成工具,普遍被用於多個數據源之間的批量同步,包括類似Apache DolphinScheduler的Task類型也對DataX進行了適配和增強,可以直接在DolphinScheduler裡面利用通用的數據源調用DataX進行數據批量同步。 作為 ...
  • 一、背景概述 在將資料庫從MySQL 5.7遷移到GreatSQL8.0.32時,由於數據量較小且關註安全性,決定使用mysqldump執行邏輯備份,並將數據導入GreatSQL。但在備份時採用了備份全庫(--all-databases)的方式,在導入GreatSQL後,修改用戶密碼時出現錯誤。這是 ...
  • 軟體版本 Redis 7.2.5 Docker 26.1.3 準備工作 由於docker直接拉取運行了,所以需要提前準備配置文件 Index of /releases/ (redis.io) 下載後,把redis-7.2.5.tar.gz\redis-7.2.5.tar\redis-7.2.5\里的 ...
  • 這裡介紹如何修改Windows平臺監聽的服務名稱,至於需求背景介紹,這裡就不做介紹了。存在既是合理。總有一些場景有這樣的需求。 那麼如何修改Windows的監聽名呢?修改過程中有什麼影響呢? 影響介紹: 修改監聽的服務名稱,需要停止Oracle的監聽服務,可能對業務有短暫的影響。這段時間新的會話連接 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...