MySQL主從同步延遲調查

06-04

MySQL主從同步延遲調查

來自專欄升級之路

AWS RDS主從同步延遲調查

背景

我們的關係型資料庫用的是AWS的RDS，mysql主從延遲這個問題之前是知道的，簡單懷疑是AWS的主從同步機制問題但是一直沒有重視起來去研究一下問題的根源。終於，隱患暴露處來--- 一次alter table和主從延遲曾大發生的時間點重合，從而加劇主從延遲。造成從庫遠遠落後於主庫，而數據不一致。必須找到延遲過大的根本原因，解決這個問題。在分析問題之前我們先簡要介紹一下mysql主從同步機制

主從同步機制

mysql在主庫上記錄binlog。在準備提交事務完成數據更新前，主庫把數據更新的事件記錄到binlog中。 mysql通過上面說的binlog用3個線程來實現主從同步，master上的Binlog Dump Thread, slave上的Slave I/O Thread和Slave SQL Thread

Slave I/O Thread: I/O線程跟主庫建立一個普通的客戶端連接，並請求從指定日誌文件的指定位置（或者從最開始的日誌）之後的日誌內容, 存到relaylog中, 然後將讀取到的主庫binlog的文件名和位置記錄到master-info文件中
Binlog Dump Thread: 讀取主庫上的binlog中的事件，根據請求信息讀取制定日誌指定位置之後的日誌信息，返回給Slave I/O Thread。返回信息中除了日誌所包含的信息之外，還包括本次返回的信息已經到Master端的binlog文件的名稱以及binlog的位置。
Slave SQL Thread: 從relaylog中讀取事件並且在從庫執行，從而實現從庫的更新

通過觀察從庫的同步延遲曲線發現每天固定的三個時間點從庫都會報警主從延遲超過閾值。

首先從AWS官方文檔上尋找主從同步延遲的辦法

可通過多種方式來減少對源資料庫實例的更新與對只讀副本的後續更新之間的滯後，例如：將只讀副本的存儲大小和資料庫實例類調整到與源資料庫實例類似。確保源資料庫實例和只讀副本使用的資料庫參數組中的參數設置相兼容。有關更多信息和示例，請參閱本部分後面的有關 max_allowed_packet 參數的討論。 Amazon RDS 監控只讀副本的複製狀態，如果由於任何原因停止複製，則將只讀副本實例的 Replication State 欄位更新為 Error。可能會有這樣的例子，在您的只讀副本上運行的 DML 查詢與對源資料庫實例的更新衝突。您可通過查看 Replication Error 欄位，檢查 MySQL 或 MariaDB 引擎引發的關聯錯誤的詳細信息。還生成指示只讀副本狀態的事件，包括 RDS-EVENT-0045、RDS-EVENT-0046 和 RDS-EVENT-0047。有關這些事件和事件訂閱的詳細信息，請參閱使用 Amazon RDS 事件通知。如果返回 MySQL 錯誤消息，則檢查 MySQL 錯誤消息文檔中的錯誤編號。如果返回 MariaDB 錯誤消息，則檢查 MariaDB 錯誤消息文檔中的錯誤。一個可導致複製出錯的常見問題是只讀副本的 max_allowed_packet 參數的值小於源資料庫實例的 max_allowed_packet 參數的值。max_allowed_packet 參數是可在資料庫參數組中進行設置的自定義參數，用於指定可在資料庫上執行的最大 DML 代碼大小。有時候，與源資料庫實例關聯的資料庫參數組中的 max_allowed_packet 參數值，要小於與源的只讀副本關聯的資料庫參數組中的 max_allowed_packet 參數值。在這些情況下，複製過程可能會引發錯誤 (數據包大於 max_allowed_packet 位元組) 並停止複製。可通過讓源和只讀副本使用 max_allowed_packet 參數值相同的資料庫參數組來糾正該錯誤。

按照官網文檔的建議嘗試，發現並不是以上所說問題。必須深入分析，找到原因。

分析

是否在延遲報警的時間點有大量的寫操作通過代碼排查，在這些時間點並沒有類似的操作
在延遲發生的時間點觀察執行狀態

主庫show PROCESSLIST，並沒有很多的寫操作同時也印證了代碼的排查的結論
從庫show PROCESSLIST，看到十分重要的信息發生了鎖表並且有很多寫入在排隊

觀察innodb_trx和innodb_lock 查看是否有大的事務拿著鎖不放，結果是沒有通過以上信息，判讀不是我們自己的寫操作造成主從同步延遲
繼續尋找線索
查看發生延遲時間點的慢查詢日誌查看到這個時候有很多類似的查詢
SELECT /*!40001 SQL_NO_CACHE */ * FROM 先來看這個查詢的意義：

/*! */ 這是mysql里的語法，表示達到條件會執行相應的語句。
!後面是版本號，如果本資料庫等於或大於此版本號，那麼語句會執行。
那麼這句話的意思是如果版本號大於或等於4，會執行 sql_no_cache, 就是不用緩存數據。而並非說本次查詢不作為下次查詢的緩存。
在備份操作時Mysql 會自動調用此語法。該語句會查詢到表中所有數據，在備份文件中會生成相應的insert語句。

查看資料庫備份每隔8小時執行一次

mysqldump -u ${user} -p${password} -h ${host} ${name} > ${filename}

可以看到時間點與主從同步延遲的報警時間完全吻合

原因

通過一系列列大膽假設和小心求證,可以斷定是我們在執行自動備份的時候大量鎖表，造成很多同步的寫入操作被阻塞

mysqldumb 有個很關鍵的點是為了保證同步後數據的一致性，會採用 lock-tables, lock-all-tables, single-transaction不同的機制。
lock-tables: 默認會選擇這個機制，把所有需要dump的表都會加鎖，當然寫入表的操作就被阻塞了
lock-all-tables: dump期間鎖定所有資料庫中的所有表，以保證數據的一致性。這是一個全局鎖定，並且自動關閉–single-transaction 和–lock-tables 選項。這個參數副作用比較大，這是全庫鎖定，備份執行過程中，該庫無法進行讀寫操作

single-transaction: 這個機制把事務隔離級別設置為可重複讀，在dump之前提交一個START TRANSACTION語句，不會阻塞任何應用程序且能保證導出時資料庫的一致性狀態。它只適用於多版本存儲引擎(MVCC)，僅InnoDB。本選項和–lock-tables 選項是互斥的，因為LOCK TABLES 會使任何掛起的事務隱含提交，使用參數–single-transaction會自動關閉該選項。

解決方案

因為我們的db引擎是innodb, 最簡單的方式就是修改mysqldump保證數據一致性的機制，採用single-transaction
mysqldump -u ${user} -p${password} -h ${host} ${name} --single-transaction --quick > ${filename}
single-transaction只適用於innodb, 在innodb導出時會建立一致性快照，在保證導出數據的一致性前提下，又不會堵塞其他會話的讀寫操作，相比–lock-tables參數來說鎖定粒度要低，造成的影響也要小很多。指定這個參數後，其他連接不能執行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE這類語句，事務的隔離級別無法控制DDL(Data Definition Languages)語句。所以改表的時候，要注意查看是否有資料庫備份執行
再起一個從庫，單獨在此從庫上執行備份因為資料庫備份的時候佔用不少資源，而同時我們的從庫也有大量的讀的操作，會互相影響。
不用mysqldump選擇別的備份方案, 比如XtraBackup

下圖是執行解決方案後從庫延遲的曲線圖，可以看到之前定時出現的主從延遲(心頭大患)已經消失了(開心.jpg)

主要參考：https://dev.mysql.com/doc/refman/5.6/en/replication.html。

總結

多學習多思考，對資料庫的操作包括基本的增刪改查要有敬畏之心。

附加部分

資料庫查詢優化

資料庫主從同步延遲報警的時間點，還有主庫CPU和連接數報警，在一開始因為時間點基本一致，而認為兩者一定時相關的，誤導了我對根本原因的判讀。更重要的是針對這種報警必須優化。下面就是一個優化的例子。

1. 分析原因

根據資料庫報警信息 CPU和connections超過閾值報警，找到對應時間點的慢查詢

根據報警時間點，找到對應的定時任務
根據時間，查看慢查詢日誌 AWS RDS開啟slow_query_log，設置三個關鍵參數:

slow_query_log（1）,
long_query_time（比如10秒）,
log_output（file)

設置完成後可以用show variables like 參數名稱來查看是否設置成功

# Time: 180504 0:28:20# User@Host: xxx(脫敏) Id: 164478982# Query_time: 3.542192 Lock_time: 0.000056 Rows_sent: 1 Rows_examined: 76281SET timestamp=1525393700;SELECT COUNT(*) AS `__count` FROM `core_membership` WHERE (`core_membership`.`business_group_id` = 2908 AND `core_membership`.`last_visited_at` > 2018-05-04 23:59:59.999999 AND `core_membership`.`last_visited_at` <= 2018-02-03 23:59:59.999999);# 這個慢查詢必須優化# Time: 180504 0:27:31# User@Host: xxx(脫敏) Id: 164477983# Query_time: 45.261294 Lock_time: 0.000043 Rows_sent: 1 Rows_examined: 8583SET timestamp=1525393651;SELECT COUNT(*) AS `__count` FROM `core_membership` WHERE (`core_membership`.`business_group_id` = 1976 AND `core_membership`.`level` = 1);

慢查詢和應用中的定時任務要執行的查詢完全一致

2. 優化

分析慢查詢日誌確定拖垮db的根本原因是上面的第二條查詢通過explain分析上面的查詢語句，雖然用到了索引，但依然很慢

原因：level為1的會員佔了絕大多數，即使用了索引，這樣的查詢掃索引依然很多行，所以慢下面的執行結果非常明顯

sql> SELECT COUNT(*) AS `__count` FROM `core_membership` WHERE (`core_membership`.`business_group_id` = 2708 AND `core_membership`.`level` = 1)[2018-05-04 13:16:31] 1 row retrieved starting from 1 in 2s 987ms (execution: 2s 964ms, fetching: 23ms)sql> SELECT COUNT(*) AS `__count` FROM `core_membership` WHERE (`core_membership`.`business_group_id` = 2708 AND `core_membership`.`level` > 1)[2018-05-04 13:17:16] 1 row retrieved starting from 1 in 598ms (execution: 587ms, fetching: 11ms)

優化方案：level大於1的會員為少數，最簡單有效的方法就是只查詢高等級的會員，這樣查詢索引的時候自然就快。然後再用總數跟高等級的人數做減法，來避免level 1的查詢下圖是優化後的主庫CPU佔用曲線圖，可以看到優化後峰值明顯減少

圖上剩下的峰值就是下一步的優化對象。

@lxkaka
本文版權屬於再惠研發團隊，歡迎轉載，轉載請保留出處。