如何對 Amazon RDS for MySQL 與 Aurora MySQL 的高 binlog 複寫延遲問題進行疑難排解？

簡短描述

Amazon RDS for MySQL 使用非同步複寫，因此複本有時會落後於主要資料庫執行個體。若要監控複寫延遲，使用具有二進位日誌檔案位置型複寫的 Amazon RDS for MySQL 讀取複本。

若要檢查 Amazon RDS 的 ReplicaLag 指標，請使用 Amazon CloudWatch。ReplicaLag 指標會報告 SHOW SLAVE STATUS 命令的 Seconds_Behind_Master 欄位值。

對於 Aurora，AuroraBinlogReplicaLag 指標會測量使用二進位日誌的 Aurora 資料庫叢集之間的複本延遲。

**注意：**對於 MySQL 8.0.22 及更新版本，SHOW REPLICA STATUS 命令已由 SHOW SLAVE STATUS 命令取代。如需詳細資訊，請參閱 MySQL 網站上的 SHOW SLAVE | REPLICA STATUS 陳述式。

Seconds_Behind_Master 欄位顯示複本資料庫執行個體目前落後於來源執行個體的秒數。它也顯示在複本資料庫執行個體上處理的事件的記錄在主要資料庫執行個體上的原始值。

MySQL 複寫使用二進位日誌傾印、複寫 I/O 接收器和複寫 SQL 套用程式執行緒。如需執行緒運作方式的詳細資訊，請參閱 MySQL 網站上的複寫執行緒。如果複寫出現延遲，請檢查 IO_THREAD 複本或 SQL_THREAD 複本是否導致了延遲。然後，您可以找出延遲的根本原因。

解決方法

確定延遲的複製執行緒

請完成下列步驟：

1. 若要確定來源或主要資料庫執行個體將二進位日誌寫入到何處，請在主要資料庫執行個體上執行 SHOW MASTER STATUS 命令：

   mysql> SHOW MASTER STATUS;

   輸出範例：

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   **注意：**在先前的範例輸出中，來源或主要資料庫執行個體會將二進位日誌寫入 mysql-bin.066552 檔案。
2. 若要尋找輸出狀態，請在複本資料庫執行個體上執行 SHOW SLAVE STATUS 或 SHOW REPLICA STATUS 命令：

   mysql> SHOW SLAVE STATUS\G;

輸出範例 1：

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在上述範例輸出中，Master_Log_File: mysql-bin.066548 顯示 IO_THREAD 複本從 mysql-bin.066548 二進位日誌檔案讀取資料。主要資料庫執行個體會將二進位日誌寫入 mysql-bin.066552 檔案。IO_THREAD 複本出現四個二進位日誌檔的延遲。但是，因為 Relay_Master_Log_File 是 mysql-bin.066548，所以 SQL_THREAD 複本會從與 IO_THREAD 相同的檔案讀取資料。SQL_THREAD 複本維持了速度，但複本 IO_THREAD 則會出現延遲。

範例輸出結果 2：

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

先前的範例輸出顯示主要執行個體的日誌檔案是 mysql-bin.066552。IO_THREAD 維持主要資料庫執行個體的速度。在複本輸出中，SQL 執行緒會執行 Relay_Master_Log_File: mysql-bin.066530。因此，SQL_THREAD 落後了 22 個二進位日誌。

由於 IO_THREAD 只會從主要或來源執行個體讀取二進位日誌，因此 IO_THREAD 通常不會造成大量複寫延遲。但是，網路連線和網路延遲可能會影響伺服器之間的讀取速度。較高的頻寬使用率可能會導致 IO_THREAD 複本的執行速度更為緩慢。

如果 SQL_THREAD 複本導致複寫延遲，請使用下列疑難排解步驟來解決問題。

主要執行個體上長期執行的寫入查詢

如果主要資料庫執行個體上長期執行的寫入查詢需要相同時間，才能在複本資料庫執行個體上執行，即會增加 seconds_behind_master。例如，如果對主要執行個體的變更需要 1 小時才能執行，則延遲時間為 1 小時。如果複本的變更也需要 1 小時才能完成，則總延遲時間為 2 小時。

若要將延遲降至最低，請監控主執行個體上的慢速查詢日誌，並找出需要最佳化的查詢。您也可以將長期運行的陳述式縮減為較小的陳述式或交易。

若要對與 binlog 相關的效能問題進行疑難排解，請分析 Performance Insights 中的等待事件以取得更多資訊。您也可以調整隔離層級。

資料庫執行個體類別大小或儲存空間不足

如果複本資料庫執行個體類別或儲存組態低於主要執行個體的類別或儲存組態，則複本可能因資源不足而遭到限流。複本無法應對主要執行個體上的變更次數。

若要解決此問題，請確定複本的資料庫執行個體類型等同或高於主要資料庫執行個體的類型。為了讓複寫作業有效執行，每個讀取複本都需要具有與來源資料庫執行個體相同數量的運算和儲存資源。

在主要資料庫執行個體上執行的平行查詢

預設情況下，MySQL 複寫是單執行緒作業。當您在主要執行個體上平行執行查詢時，系統會按照序列在複本上提交查詢。在平行執行來源執行個體的大量寫入作業時，即系統會使用單一 SQL_THREAD 來序列化對讀取複本的寫入作業。然後，來源資料庫執行個體和讀取複本之間可能會因此出現延遲。

對於 MySQL 版本 8.0.27 及更新版本，replica_parallel_workers 的預設值為 4。此值表示複本預設為多執行緒模式。同樣地，對於 Aurora MySQL 版本 3.04 及更新版本，複寫預設為多執行緒，replica_parallel_workers 的值設定為 4。您可以在自訂參數群組中修改此參數。

如需多執行緒複寫功能的詳細資訊，請參閱 MySQL 網站上的二進位日誌記錄選項和變數。

多執行緒複寫可能會造成複寫中出現差距。例如，很難識別您跳過的交易。當使用多執行緒複寫時，跳過複寫錯誤並非最佳實務。主要和複本資料庫執行個體之間的資料一致性可能會出現差距。

同步至複本資料庫執行個體上磁碟的二進位日誌

當您在複本上啟用自動備份功能時，將會產生將二進位日誌同步至複本上磁碟的開銷。sync_binlog 參數的預設值已設為 1。如果將該值變更為 0，則 MySQL 伺服器無法將二進位日誌同步到磁碟。作業系統 (OS) 會改為偶爾將二進位日誌重新整理到磁碟。

若要降低在每次提交時將二進位日誌同步至磁碟所需的效能開銷，請關閉二進位日誌同步功能。但是，如果出現電源故障或作業系統當機的情形，部分提交可能不會同步至二進位日誌。非同步功能可能會影響時間點復原 (PITR) 功能。如需詳細資訊，請參閱 MySQL 網站上的 sync_binlog。

Binlog_format 已設為 ROW

在下列情況下發生複寫時，SQL 執行緒會執行完整的資料表掃描：

• 主要資料庫執行個體上的 binlog_format 已設為 ROW。
• 來源資料表沒有主索引鍵。

發生此掃描是因為 slave_rows_search_algorithms 參數的預設值為 TABLE_SCAN,INDEX_SCAN。

若要暫時解決此問題，請將搜尋演算法變更為 INDEX_SCAN,HASH_SCAN，以便減少完整資料表掃描的開銷。如需更長久的解決方案，最佳做法是在每個資料表中新增明確的主索引鍵。

如需 slave-rows-search-algorithms 參數的詳細資訊，請參閱 MySQL 網站中的 slave_rows_search_algorithms。

複本建立延遲 (適用於 RDS MySQL)

為了建立 MySQL 主要執行個體的讀取複本，Amazon RDS 會擷取資料庫快照。然後，Amazon RDS 會還原快照以建立新的資料庫執行個體，並在兩者之間建立複本。

在 Amazon RDS 建立複寫後，當 Amazon RDS 建立主要資料庫執行個體的備份時，就會出現延遲。若要將此延遲時間降至最低，請在呼叫複本建立作業前建立手動備份。然後，資料庫快照將成為增量備份。

從快照還原讀取複本時，複本不會等到從來源傳輸所有資料的流程結束。複本資料庫執行個體可用於執行資料庫操作。現有 Amazon Elastic Block Store (Amazon EBS) 快照會在背景中建立新的磁碟區。

**注意：**對於 Amazon RDS for MySQL 複本 (由 Amazon EBS 支援的磁碟區) 而言，複本的延遲情況最初可能會加劇，這是因為延遲的載入會影響複寫效能。

若要減少延遲載入對全新讀取複本的資料表上造成的影響，請執行使用完整資料表掃描的作業。例如，在特定資料表或資料庫的讀取複本上執行 mysqldump，以優先處理來自 Amazon Simple Storage Service (Amazon S3) 的所有備份資料表資料。

您還可以對 RDS MySQL 使用隨需 InnoDB 快取暖機功能。InnoDB 快取暖機功能會將緩衝區集區狀態儲存在磁碟上 InnoDB 資料目錄中名為 ib_buffer_pool 的檔案中。由於 Amazon RDS 會在您建立讀取複本之前，傾印主要資料庫執行個體緩衝集區的目前狀態，因此在負載較低時效能會提升。然後，您可以在建立讀取複本之後重新載入緩衝區集區。

複本延遲的影響

複本執行個體或叢集的延遲過大，可能導致來源端二進位日誌累積。為避免潛在問題，請監控並管理 binlog 檔案大小及磁碟空間使用量。對於 RDS，您可以監控來源資料庫執行個體上的 BinLogDiskUsage 指標。

在 RDS MySQL 執行個體與 Aurora 上，設定最佳 binlog 保留期間，以平衡 時間點還原能力與儲存空間使用量。此外，若要利用 binlog 進行複寫與還原，請了解 binlog 檔案命名慣例及輪換行為。若要取得可用日誌清單，請執行 SHOW BINARY LOGS 指令。

相關資訊

在 Amazon RDS 中使用 MySQL 複寫

使用 MySQL 讀取複本

Aurora MySQL 二進位日誌複寫最佳化