DB診斷日 | 99%的DBA都想深入了解的MySQL故障

• 2019 年 12 月 17 日
• 筆記

為更好的幫助DBA運維資料庫，騰訊雲將在每月12日開展DBbrain診斷日，騰訊雲高級產品經理迪B哥直播解析經典資料庫運維難題，結合騰訊雲資料庫智慧管家DBbrain的能力，為大家提供問題優化思路和方法，玩轉資料庫！

工作中遇到棘手故障不知道怎麼辦？歡迎投稿到診斷日，被選中的案例將由騰訊雲資深專家「會診」，並在DB診斷日在線分析教學，幫您提供解決方案。投稿即有機會獲得企鵝公仔，問題被選中即得騰訊雲資料庫千元代金券~投稿請關注「騰訊雲資料庫」官方微信後，回復「投稿」即可。

本期診斷日分享的案例是MySQL主從延時故障。

大家好，我是騰訊雲資料庫高級產品經理劉迪，網名迪B哥。我們都知道在資料庫運維過程中，很多問題都需要靠人力來及時發現和處理，我之前也是一名DBA，可以說我做DBA的那段時間基本沒有擁有過完整的屬於自己的休息時間，全天候Online。現在AI技術已經廣泛運用到了各個領域，資料庫運維其實也是同樣的，AI可以成為DBA的得力助手，有問題第一時間告警，甚至給出成熟的解決方案，DBA可以用更多的時間去完成高階的任務。我現在主要負責的產品是DBbrian，是騰訊雲推出的一款資料庫智慧運維工具。今天就以咱們MySQL運維過程中典型的主從延時故障來作為案例，告訴大家可以如何藉助智慧運維服務更好的發現和解決這類問題。

一、主從複製的模式和原理解讀

MySQL主從複製可以簡單解釋為數據可以從一個MySQL資料庫伺服器主節點複製到一個或多個從節點。MySQL 默認採用非同步複製方式，這樣從節點不用一直訪問主伺服器來更新自己的數據，數據的更新可以在遠程連接上進行，從節點可以複製主資料庫中的所有資料庫或者特定的資料庫或者特定的表。

MySQL 主從複製主要用途包括讀寫分離、 數據實時備份（當系統中某個節點發生故障時，可以方便的故障切換）、 架構擴展、高可以用HA等。

MySQL 主從複製的主要形式包括：一主多從、多主一從、雙主複製、級聯複製（部分slave的數據同步不連接master節點，而是連接slave節點。因為如果master節點有太多的從節點，就會損耗一部分性能用於replication，那麼可以讓一些slave節點連接主節點，其它從節點作為二級或者三級與slave節點連接）等。

MySQL主從複製涉及到三個執行緒，一個運行在master節點（log dump thread），其餘兩個(I/O thread, SQL thread)運行在slave節點，如下圖所示:

• master節點 binary log dump 執行緒

當salve節點連接master節點時，master節點會創建一個log dump 執行緒，用於發送binlog的內容。在讀取binlog中的操作時，此執行緒會對主節點上的binlog加鎖，當讀取完成，在發送給slave節點之前，鎖會被釋放。

• slave節點I/O執行緒

當slave節點上執行start slave命令之後，slave節點會創建一個I/O執行緒用來連接master節點，請求master節點中更新的binlog。I/O執行緒接收到master節點binlog dump 進程發來的更新之後，保存在本地relay-log中。

• slave節點SQL執行緒

SQL執行緒負責讀取relay log中的內容，解析成具體的操作並執行，最終保證主從數據的一致性。

要實現主從複製，必須打開Master 節點的binary log功能。因為整個複製過程實際上就是Slave 節點從Master 節點獲取該日誌然後再在自己身上完全順序的執行日誌中所記錄的各種操作。

• slave節點上的I/O 進程連接主節點，並請求從指定日誌文件的指定位置之後的日誌內容；
• master節點接收到slave節點的I/O請求後，通過複製的I/O進程根據請求資訊讀取指定日誌指定位置之後的日誌資訊，返回給slave節點。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊的binlog file 的以及binlog position。slave節點的I/O進程接收到內容後，將接收到的日誌內容更新到本機的relay log中，並將讀取到的binary log文件名和位置保存到master-info 文件中，在下一次讀取的時能告訴master節點需要從某個binlog的哪個位置開始往後的日誌；
• slave節點 的 SQL執行緒檢測到relay-log 中新增加了內容後，會將relay-log的內容解析成在master節點上實際執行過的操作，並在本資料庫中執行。

MySQL 主從複製模式分為非同步模式、半同步模式、全同步模式。

①非同步模式

master節點不會主動push binlog到slave節點，有可能導致fail over情況下，也許slave節點沒有即時地將最新的binlog同步到本地。

②半同步模式

半同步複製模式可以確保至少有一個slave節點（可配置）在接受完master節點發送的binlog日誌文件並寫入到中relay log後，返回給主節點一個ack訊號，告訴master節點已經接收完日誌，這時主節點執行緒才返回給當前session提交資訊。

③全同步模式

全同步模式是指slave節點接收到master節點發送的binlog日誌文件並寫入到中relay log，並且完成回放之後，返回給主節點一個ack訊號，master節點才會向客戶端返回成功。

二、DBbrian如何判斷主從延遲

從前面講到的的主從複製原理中不難發現，MySQL在使用「非同步」和「半同步」的複製模式下可能會出現主從延時。MySQL資料庫複製延遲會給業務帶來一系列嚴重問題：讀寫分離架構不利於高實時一致性業務；高可靠架構設計中也難以確定RTO/RPO指標。檢測，定位和解決MySQL主從複製延遲問題一直是DBA重點工作之一。

資料庫智慧管家DBbrain為雲上用戶提供了7*24小時資料庫智慧運維服務，對於「主從複製」延遲的故障，DBbrain又是怎麼診斷的呢？接下來就為大家一起揭秘這一問題。

那麼，首先簡要的介紹一下主從延遲（複製延遲）是如何發生的。

MySQL備庫複製會啟動兩類執行緒：IO執行緒負責連接主庫讀取binlog事件，然後將其寫入本地binlog文件；SQL執行緒則從複製得到的binlog文件中讀取事件apply到備庫。 可以通過 "show slave status" 查看備庫的複製狀態。其中 SecondsBehindMaster 值表示延遲時間，單位為秒。將主庫執行SQL語句時刻標記為T1，備庫執行SQL的時刻標記為T2，這兩個時刻之間的差值就是主備延遲時間。不過僅僅從 "show slave status" 結果看到的延遲時間可能」不準「。該值除精度問題外，還和主庫事務相關。如果在主庫開啟事務執行了IUD操作，但是commit有一分鐘滯後，那麼這個時間差也會在備庫複製延遲狀態中體現出來。我們通常看到備庫延遲性能曲線始終存在1，2秒的延遲波動，大概率是主庫事務導致的；若從事務提交的時間點算，大延遲並不存在；在主備切換時為了確保主備數據一致，需要確認主備binlog日誌文件和和位點一致後才能操作。

資料庫智慧管家DBbrain針對主從延遲（複製延遲）的異常場景採用的發現機制和方式主要可以分為以下三種：

①利用seconds_ behind_ master的值

在show slave status結果里的seconds_ behind_ master（In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.)的值可以用來衡量主備延遲時間的長短（單位是秒）。判斷seconds_ behind_ master 是否已經等於0，如果這個參數等於0，表示主從複製基本上無延遲。seconds_ behind__master是通過比較sql_thread執行的event的timestamp和io_thread複製好的event的timestamp進行比較，而得到的差值。

在某些場景中也會出現seconds_ behind_ master對複製延遲表徵不準確的情況，例如：

• 在網路環境特別差的情況下，I/O thread同步很慢，每次同步過來，SQL thread就能立即執行，這樣，在slave上查看到的seconds_ behind__master是0，而真正的，slave已經落後master有一定距離。
• 有一段時間沒有數據提交，slave I/O thread time和slave SQL thread time都保持在舊值，比如T(但事實上master上的時間已經到T+I了)，這個時候主庫出現提交，slave I/O開始去和master同步binlog，slave I/O thread time更新到T+I，但是slave SQL thread time保持在T值，這時的seconds_behind_master=I，但其實是否出現延遲是不確定的。

②通過對比位點

Master_Log_File和Read_Master_Log_Pos，表示的是讀到的主庫的最新位點。Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是備庫執行的最新位點。如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos這兩組值完全相同，就表示接收到的日誌已經同步完成。

③對比GTID集合

對於開啟GTID的資料庫實例，DBbrain會使用對比GTID集合的方式來檢測複製延遲是否存在。（Auto_Position=1，表示這對主備關係使用了GTID協議）

• Retrieved_Gtid_Set，是備庫收到的所有日誌的GTID集合；
• Executed_Gtid_Set, 是備庫所有已經執行完成的GTID集合。

如果這兩個集合相同，也表示備庫接收到的日誌都已經同步完成。比判斷seconds_ behind_ master 是否為0更準確。

三、DBbrian一鍵優化的案例

通常IO執行緒不會引起數據複製的較大延遲，除非網路問題導致連接斷開，又或者網路延遲以及頻寬存在瓶頸。自動化環境中主庫binlog被刪除或損壞也是導致IO執行緒斷開的一種原因。 在這裡主要對SQL執行緒應用event的延遲問題展開分析：

• 備庫/只讀實例資源不夠：備庫/只讀實例除了需要應用數據變更之外，在承擔查詢任務時，可能需要更多的資源。當發現備庫存在延遲後，需要首先確認備庫的資源使用情況。
• 主庫高並發數據更新：業務高峰期，主庫大量並發的插入、刪除和更新操作，QPS明顯增加，產生大量的binlog文件。這個時候備庫應用event的速度跟不，延遲產生。 備庫應用event的方式從最初的單執行緒演變和優化成當前的並行複製。其中並行複製的實現方式在不同版本以及廠商之間存在差異，比如基於Schema並行複製，基於表並行複製，基於commit-parent的並行複製，基於lock-interval的並行複製等。在備庫開啟組提交的並行複製，可以提高回放binlog性能減少延遲。
• 主庫單表大量更新：在row模式下，主庫一個sql語句的資料庫更改，會變成多個event複製到備庫。建議開發人員盡量分解大事務為小事務，並及時提交。另外也見過一個用戶在主庫循環更新單個表數10萬條數據的時間戳。這種場景備庫延遲會越來越大，備庫始終追不上主庫。最後只能建議用戶更改應用設計。
• 主庫DDL操作：大表DDL語句複製到備庫執行時，會導致並行複製失效，後續事件無法更新，從而延遲累積。這種場景會看到監控曲線成45度斜率增長。在這裡重點提一下，很多開發人員喜歡在主庫頻繁的使用optimize table操作，但是忽視了該語句容易導致備庫延遲的問題。由於DDL導致備庫延遲的問題比較容易定位：通過 "show processlist" 會話快照可以看到正在執行的DDL語句。針對大表DDL導致延遲問題，有經驗的DBA會選擇關閉SQLLOGBIN參數後，備庫手工執行DDL語句。
• 備庫執行SQL語句慢：在row模式下，缺少主鍵或適當的索引是導致SQL執行慢的主要原因。線上雲環境中，若用戶創建表時未指定主鍵，資料庫通常會自動引入隱式主鍵來避免該問題。
• 備庫事務阻塞複製：備庫/只讀實例長事務或未提交事務導致複製延遲或中止的情況容易被忽視。比如備庫開啟事務，執行查詢後並未提交；這個時候主庫過來的DDL語句會等待MDL鎖；而DDL語句會繼續阻塞後續過來的其它事件執行。

下面選取其中一類問題通過場景化的描述簡單的還原整個優化過程的邏輯。針對只讀實例開啟事務執行查詢後，不提交事務。（注意：開始事務只做查詢是常見的錯誤使用方式。這種操作不一定是開發人員顯示的寫在程式碼中，是所使用的框架導致的。）

此時我們可以從監控數據看到備庫延遲產生：

在只讀實例上，我們可以通過一系列命令查看到複製延遲的原因。 備庫複製狀態資訊中，可以看到當前SQL執行狀態為 "Waiting for table metadata lock"。

另外通過會話快照也可以直接看到當前被阻塞的DDL語句：

實例上查看長時間未提交的事務：

資料庫智慧管家DBbrain會主動發現原因，提交或kill會話後，延遲立即消失:

四、主從延遲的妙用

主從延遲（複製延遲）雖然出現在大多數場景中對業務都會帶來消極影響，但是在一些場景，人為手動設置「延遲」，能夠完美的解決一些特殊的業務需求。比如在將日誌及時複製到備庫，但有意的不立即應用的實現方式在容災系統中經常採用。容災切換概率很低，但是可以利用現有的資源及時「回滾」誤操作。複製延遲是非常有價值的「撤消」選項。例如，如果有人意外刪除了MySQL資料庫或表，則可以輕鬆地從延遲的MySQL從站恢復這些資料庫和表。MySQL已經支援 MASTER_DELAY 參數來實現類似功能。

五、騰訊雲MySQL基於主從複製的優化

MySQL在同步複製下耗時主要包含三個部分。第一個是SQL部分，第二是存儲引擎，第三部分是複製。和非同步相比，我們重點優化第三部分的延時。複製延時主要有兩部分：第一部分是binlog網路傳輸過去的耗時，第二部分是slave落地binlog的延時。binlog傳輸耗時取決於網路RTT值。我們的優化重要集中在slave落地binlog的延時上。

在這次優化過程中，做了一個測試進行定量分析。在全Cache下MySQL非同步的情況下，單事務耗時是3.37ms，也就是說它的SQL加引擎一共耗時3.37ms，但是我們發現在半同步的情況下延時就變成了8.33ms，發現RTT是2.6ms，那麼slave落地binlog就花費了1.9ms，那1.9ms是否合理呢?接著做了一個測試，模擬slave落地binlog的操作，發現只需要0.13ms，這裡面其實有接近1.8ms的優化空間。

第二個就是如何提升系統吞吐。當單個事務的延時降下來後，是不是就意味著整個系統的吞吐就上來了?這也未必，整個吞吐來說取決於兩個因素，一個是支援的並發數，另外一個就是單個事務的延時。假如有一些公共資源存在很大的競爭，那就可能存在並發數上不來了的問題，我們發現master的binlog發送/響應執行緒是有很大的優化空間的。所以我們就基於這兩個方面去做了系統吞吐的優化。

如何解決slave落地binlog的耗時呢?我們當時分析MySQL slave的IO執行緒接收binlog耗時的主要瓶頸有三個：第一個就是鎖衝突，IO/SQL執行緒間的鎖衝突，如元數據文件鎖;第二部分就是小IO消耗，IO執行緒離散小磁碟IO消耗過多的IOPS;第三個問題是串列化，IO執行緒接收和落盤操作串列。

迪B哥往期課程，請關注「騰訊雲資料庫」官方微信後，回復「迪B課堂」即可查看~