MySQL複製延遲終結者：AliSQL 高效AI診斷和四大內核級優化

對於mysql數據庫來說，搭建主備架構增強可用性，搭建只讀實例增強擴展性，都是剛需中的剛需；這二者都依賴於 mysql 基於 binlog 的邏輯複製。

在mysql中，主庫會以事務維度，將被修改的所有數據記錄到 binlog 中，並傳輸到從庫；從庫收到 binlog 後，將修改應用到自己的表上。正常情況下，從主庫提交事務到從庫應用完成的時間差要很短，以保證從庫的數據的時效性；但是在一些業務場景中，這個時間差可能拉長到分鐘甚至小時級，嚴重影響從庫數據時效，這就是複製延遲問題。

mysql 複製延遲的成因很多，其中有些場景可以通過堆資源，改配置等方式扛過去；但有些“頑疾”場景，只能通過深度的內核優化來根除。在mysql中，最典型，線上最常見的“頑疾”場景有以下四類：

1. 大表 ddl

2. 大事務

3. 批量數據處理業務（例如低峰期進行的數據整理，導入，刪除等）

4. 小事務高並發業務（例如節假日或大促時的業務高峰期等）

我們在一個未開啟複製延遲優化的alisql實例上，驗證這四種業務場景下的複製延遲情況。

如上圖所示，依次執行一張大表的 optimize table；一個 500 萬行的 insert 大事務；一個批量數據清理任務（用4個並發，每個sql 刪除1萬行數據）；一個小事務高並發的業務場景（sysbench 的 write_only 腳本，512並發，9萬tps）。可以看到這四段測試都觸發了只讀實例的複製延遲。

用 rds ai 助手來診斷這四段複製延遲，如下圖所示。

ai 助手查看了監控，解析binlog並結合實例進行分析，最終診斷出了四段延遲的根本原因，並給出了解決延遲問題的優化建議。

我們在 alisql 實例上，按ai助手的建議打開alisql的四項複製延遲優化（ddl實時複製，大事務實時複製，中等事務並行複製優化，小事務打包優化）。

再次測試這四個業務場景，如上圖所示，優化開啟後複製延遲消失。對比優化前後的cpu利用率圖可以看到，大表ddl和大事務在從庫上提早開始執行，避免了複製延遲；批量數據處理和高並發業務場景的cpu利用率都有提升，這是因為它們的複製並發度提高了，從庫吞吐顯著提升，避免了複製延遲。

上章提到的四種複製延遲問題，rds ai 助手如何準確診斷出原因？alisql 內核又如何進行了優化？本章將說明四種延遲問題的成因，診斷和優化方法。

延遲原因

大事務和ddl是mysql中最典型的複製延遲問題，其原理如上圖所示。在mysql中，大事務和ddl都是在提交時寫binlog，隨後才會被傳輸到從庫並應用。因此，大事務和ddl在從庫執行多久，就會產生多久的複製延遲。

診斷方法

大事務和ddl在從庫執行期間，從庫的複製延遲計算方法為now - 主庫提交時間。大事務和ddl在主庫提交時間是固定的，因此其複製延遲每秒一定增加1，複製延遲曲線一定是一條斜率為1的直線。

當複製延遲曲線的斜率為1時，延遲的原因大概率是大事務或ddl；這時，只需要解析延遲開始的時刻主庫上的binlog，如果找到大事務或ddl，就能確定延遲的原因，找到導致延遲的具體業務。

alisql 實時複製優化

為了解決大事務和ddl導致的複製延遲，alisql 引入了實時複製優化。如上圖所示，將大事務和ddl的 binlog 內容在其執行期間就傳輸到從庫，並實時的在從庫執行，當大事務或ddl在主庫提交時，只需通知從庫一起提交，就可以做到零延遲；如果大事務或ddl在主庫回滾，只需通知從庫一起回滾，不影響主從複製。

通過這個優化，alisql徹底解決了大事務和ddl的複製延遲問題！

延遲原因

很多業務在高峰期會以較高的並發度更新數據庫，當主庫業務的並發度高於從庫應用的並發度時，就可能導致從庫的事務吞吐不如主庫，產生複製延遲。造成從庫並發度不夠的因素有哪些，我們按順序來講解。

1. worker 線程數

從庫的 worker 線程數量如果很少，並發度肯定上不去，這是最基礎的條件。worker線程的數量由 slave_parallel_workers 參數控制。

2. 事務可並發度

當worker線程充足時，兩個事務在從庫能否並發應用，取決於這兩個事務有沒有修改同一行數據。在mysql 中，可以選擇三種方法判斷兩個事務是否可以並發，分別是：是否修改同一個庫，是否在主庫同時提交，是否修改同一行（writeset）。後兩種方法僅在mysql 5.7及以上版本支持。

其中效果最好的方法是writeset，它引入了一個哈希表，來計算事務是否修改同一行數據。大部分日常業務場景中，事務間的數據衝突並不大，開啟writeset後可並發度都很高。writeset 在一些coner case（如無主鍵，外鍵表等）中也存在一定的局限性，本文不展開。

3. 複製鏈路上的性能瓶頸

當我們有了足夠多的worker線程，並且開啟writeset功能獲得了足夠的可並發度，我們就具備了提升從庫並發度的前提條件，但最後能否真的高並發度複製，還要看從庫io，sql和worker線程的吞吐。

熟悉mysql的朋友會知道，多線程複製中有io，sql和worker三類線程，io線程負責從主庫收取binlog event；sql線程負責將收到的binlog event分發給worker線程；worker線程有多個，負責並行應用事務。其中，io線程和sql線程，sql線程和worker線程之間都存在由鎖保護的等待和通知操作，高並發時，這些鎖很容易成為性能瓶頸。

在mysql的binlog中，一個dml事務會由多個event組成，包括gtid，query，table map，row和xid event。以 sysbench write_only 這個常用的測試腳本為例，該腳本一個事務中會更新2行，插入1行，刪除1行；體現在binlog中，一個事務就有11個event。開啟alisql的主庫性能優化後，write_only 腳本的峰值tps為8.8萬，即從庫每秒要應用97萬個binlog event，極端情況下每秒要加鎖300萬次。這樣頻率的加鎖，即便鎖內處理很快，也會帶來巨大性能瓶頸。

診斷方法

mysql 的 show slave status命令中的slave_sql_running_state列，會反饋出sql線程的狀態，是我們診斷這類問題的利器。

1. 當woker線程數不夠時，sql線程會經常處於等待空閑worker的狀態；此時執行show slave status命令，通常會看到：

slave_sql_running_state: waiting for replica workers to process their queues

2. 當事務可並發度不夠時，sql線程會經常處於等待事務依賴關係狀態，此時執行show slave status命令，通常會看到：

slave_sql_running_state: waiting for dependent transaction to commit

此時我們就要檢查主庫參數，開啟writeset；如果writeset已經開啟了，那說明主庫當前業務的可並發度確實很低，例如批量數據處理業務（下一節將詳細講解），外鍵表的業務或者熱點更新業務等。

3. 當worker線程和可並發度都充足時，io和sql線程的吞吐就成為複製的瓶頸。在延遲期間執行show slave status命令，如果是io線程吞吐瓶頸，通常會看到：

slave_sql_running_state: replica has read all relay log; waiting for more updates

很好理解，如果有延遲的情況下沒有新的relay log可讀，那一定是io線程慢了。如果是sql線程吞吐瓶頸，通常會看到：

slave_sql_running_state: reading event from the relay log

這個狀態包含了sql線程大部分工作內容，讀relay log和大部分鎖等待都包含在這個狀態中。

當看到上述兩個狀態時，複製的瓶頸很可能在io和sql線程的吞吐上，此時可以解析binlog，查看每秒鐘的事務數和binlog event數來輔助判斷，如果每秒有幾萬個事務，並且有幾十萬甚至上百萬的binlog event，就可以確定是小事務高並發導致的複製延遲問題。

alisql 高並發複製優化

針對小事務高並發的延遲問題，alisql從兩方面進行了優化：

1. alisql針對性的優化了複製多個線程之間的加鎖邏輯，比mysql原生複製邏輯減少了30%以上的加鎖次數。

2. alisql引入了小事務打包優化，能夠合併同一個事務內的多個event的加鎖動作，顯著減少加鎖次數。

通過這兩項優化，高並發場景下alisql從庫的吞吐可以高於主庫，小事務高並發場景的複製延遲問題徹底解決。

延遲原因

很多業務會在凌晨或其他低峰期進行數據刪除，整理或者導入等工作，每天都要處理數百萬甚至數千萬行數據。通常情況下，開發會用小批量多並發的方式進行處理，這樣處理速度快，並且可以靈活調整批量和並發的大小，控制對數據庫的影響。

然而，mysql的並行複製邏輯對這種業務非常不友好，執行此類定時任務時經常會出現複製延遲，嚴重的甚至到第二天定時任務開始前，第一天任務產生的延遲都還沒追齊。為什麼跑批這麼容易延遲呢？

在執行批量數據處理期間，實例上主要有兩種事務，一種是批量數據處理的中等大小事務，一般一個事務內處理幾千行數據；另一種是普通業務的小事務，大部分實例上，即便在低峰期還是會有一定量的普通業務，只是業務壓力較小。兩種事務會交叉存在於binlog文件中，當兩個中等事務中間夾着多個小事務，並且這些小事務修改了同行數據時，兩個中等事務就無法並發執行了。

如上圖例子中，trx2和trx5是批量數據處理的中等事務；trx3和trx4是普通業務的小事務，並且修改了同一行數據。sql線程在分發這些事務時，trx2 和 trx3都可以正常分發，trx4則需等待trx3及之前的所有事務都提交後，才能分發；這導致trx5的分發是到了trx2提交後才進行的，進而導致trx2和trx5無法並行應用。

因此，在批量數據處理期間，經常看到從庫上並發度非常低，甚至很多時刻只有一個worker在工作，幾乎退化成了單線程應用，導致複製延遲問題。

診斷方法

清楚原理後，診斷此問題就很容易了。

第一步，我們解析延遲期間的binlog，統計一個binlog內修改千行以上的中等事務和修改百行以內的小事務的數量；如果中等事務和小事務數量都不少，就要進一步判斷其並行度。

第二步：判斷binlog中每兩個相鄰的中等事務是否能夠並行執行（中等事務之間是否有相互依賴的小事務），如果有很多的相鄰的中等事務無法並行執行，複製就會退化為串行，進而導致複製延遲問題。

alisql 中等事務複製優化

為了優化這個場景，alisql 將原本在sql線程中的，阻塞後續事務分發的等待邏輯，放到了worker線程中。這樣一來，如果有少量修改同行的小事務分布在中等事務之間，這些小事務會在worker線程中等待依賴的事務提交，而不是在sql線程中等待，阻塞其他事務的分發。這樣一來，這些沒有衝突的中等事務也就能夠並發起來了。