如何對 Amazon RDS for MySQL 的大量複製延遲問題進行疑難排解?

2 分的閱讀內容
0

我想知道使用 Amazon Relational Database Service (Amazon RDS) for MySQL 時出現複製延遲的原因。

簡短說明

因為 Amazon RDS for MySQL 使用非同步複製功能,所以複本有時無法與主要資料庫執行個體進行同步處理,並導致複製延遲問題。

若要監控複製延遲情況,請使用具有二進位日誌檔案位置型複製的 RDS for MySQL 讀取複本。

在 Amazon CloudWatch 中,檢查 Amazon RDS 的 ReplicaLag 指標。ReplicaLag 指標會報告 SHOW SLAVE STATUS 命令的 Seconds_Behind_Master 欄位值。

Seconds_Behind_Master 欄位會顯示複本資料庫執行個體上的目前時間戳記。其也會顯示在複本資料庫執行個體上進行事件處理的主要資料庫執行個體上記錄的原始時間戳記。

MySQL 複製使用二進位日誌傾印、複製 I/O 接收器和複製 SQL 套用程式執行緒。如需執行緒如何運作的詳細資訊,請參閱 MySQL 網站上的複製執行緒。如果複製出現延遲,則確定是否由 IO_THREAD 複本或 SQL_THREAD 複本導致延遲。然後,您就可以識別延遲的根本原因。

解決方法

識別延遲的複製執行緒

在主要資料庫執行個體上執行 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 檔案。

在複本資料庫執行個體上執行 SHOW SLAVE 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_Filemysql-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.066552IO_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 小時。

若要將延遲時間降至最低,您可以監視主要執行個體上的慢速查詢日誌檔。您也可以將長期運行的陳述式縮減為較小的陳述式或交易。

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

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

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

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

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

多執行緒 (平行) 複製功能適用於 MySQL 5.6 及更新版本。如需多執行緒複製功能的詳細資訊,請參閱 MySQL 網站上的二進位日誌記錄選項和變數

多執行緒複製可能會造成複製中出現差距。例如,在略過複製錯誤時,因為較難識別自己略過的交易,所以最佳實務並非使用多執行緒複製功能。主要和複本資料庫執行個體之間的資料一致性可能會出現差距。

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

當您在複本上啟用自動備份功能時,將會產生將二進位日誌同步至複本上磁碟的開銷。sync_binlog 參數的預設值已設為 1。如果將值變更為 ** 0**,則您也會關閉 MySQL 伺服器將二進位日誌檔案同步至磁碟的功能。作業系統會改為偶爾會將二進制日誌排清到磁碟。

若要降低在每次提交時將二進位日誌同步至磁碟所需的效能開銷,請關閉二進位日誌同步功能。但是,如果出現電源故障或作業系統當機的情形,部分提交可能不會同步至二進位日誌。非同步功能可能會影響時間點復原 (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

複本建立延遲

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

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

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

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

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

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

相關資訊

在 Amazon RDS 中使用 MySQL 複寫

使用 MySQL 讀取複本