如何對 Amazon RDS for MySQL 的大量複本延遲進行疑難排解？

2 分的閱讀內容

我想找到使用 Amazon Relational Database Service (Amazon RDS) for MySQL 時出現複本延遲的原因。

簡短描述

Amazon RDS for MySQL 使用非同步複寫。這表示複本有時候無法與主要資料庫執行個體保持同步。因此，可能會發生複寫延遲。

若要監控複寫延遲，使用具有二進位日誌檔案位置型複寫的 Amazon RDS for MySQL 讀取複本。

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

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

MySQL 複寫適用於三個執行緒：Binlog Dump 執行緒、IO_THREAD 及 SQL_THREAD。如需這些執行緒如何運作的詳細資訊，請參閱 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-changelog.066552|      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

**注意：**在此範例輸出中，來源或主要資料庫執行個體會將二進位日誌寫入檔案 mysql-bin.066552。

2. 在複本資料庫執行個體上執行 SHOW SLAVE STATUS 命令，然後檢閱輸出。輸出看起來類似於下列範例：

範例 1：

mysql> SHOW SLAVE STATUS\G;
*************************** 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

在範例 1 中，Master_Log_File: mysql-bin.066548 表示複本 IO_THREAD 從二進位日誌檔案 mysql-bin.066548 中讀取資料。主要資料庫執行個體將二進位日誌寫入 mysql-bin.066552 檔案。此輸出顯示複本 IO_THREAD 落後四個 Binlog。但是，Relay_Master_Log_File 是 mysql-bin.066548，表示複本 SQL_THREAD 從與 IO_THREAD 相同的檔案中讀取資料。這表示複本 SQL_THREAD 保持同步，但複本 IO_THREAD 出現延遲。

範例 2：

mysql> SHOW SLAVE STATUS\G
*************************** 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

範例 2 顯示主要執行個體的日誌檔為 mysql-bin-changelog.066552。輸出顯示 IO_THREAD 與主要資料庫執行個體保持同步。在複本輸出中，SQL 執行緒執行 Relay_Master_Log_File：mysql-bin-changelog.066530。因此，**SQL\ _THREAD ** 延遲 22 個二進位日誌。

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

如果複本 SQL\ _THREAD 是複寫延遲的來源，則下列情況可能會造成延遲：

主要資料庫執行個體上長時間執行的查詢
資料庫執行個體類別大小或儲存空間不足
在主要資料庫執行個體上執行的並行查詢
同步至複本資料庫執行個體上磁碟的二進位日誌
複本上的 Binlog_format 已設定為 ROW
複本建立延遲

主要執行個體上長時間執行的查詢

如果主要資料庫執行個體上長時間執行的查詢需要相同時間，才能在複本資料庫執行個體上執行，即會增加 seconds_behind_master。例如，如果您在主要執行個體上啟動變更，並且需要一個小時的執行時間，即會延遲一個小時。如果在複本上完成變更也需要一個小時，則完成時的總延遲約為兩小時。此延遲在預料之中，但您可以監控主要執行個體上的慢速查詢日誌，以將此延遲降至最低。您也可以識別長時間執行的陳述式來減少延遲。然後，將長時間執行的陳述式分解成較小的陳述式或交易。

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

如果複本資料庫執行個體類別或儲存配置低於主要執行個體，則複本可能因資源不足而限流。這是因為複本無法跟上對主執行個體上的變更。確定複本的資料庫執行個體類型等於或高於主要資料庫執行個體。為了讓複寫有效運作，每個讀取複本都需要與來源資料庫執行個體相同數量的運算和儲存資源。如需詳細資訊，請參閱資料庫執行個體類別。

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

如果您在主要資料庫執行個體上並行執行查詢，則它們會以序列順序在複本上提交。這是因為 MySQL 複寫預設為單一執行緒 (SQL_THREAD)。如果並行執行來源資料庫執行個體的大量寫入作業，則會以序列化方式寫入讀取複本。對讀取複本的寫入會使用單一 SQL\ _THREAD 進行序列化。這可能會造成來源資料庫執行個體和讀取複本之間出現延遲。

多執行緒 (並行) 複寫適用於 MySQL 5.6、MySQL 5.7 及更高版本。如需多執行緒複寫的詳細資訊，請參閱 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。

複本建立延遲

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

Amazon RDS 需要時間建立新的複本。建立複寫之後，建立主要資料庫執行個體備份所需的時間會有延遲。若要將此延遲降至最低，請在調用複本建立前建立手動備份。然後，資料庫快照是增量備份。

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

**注意：**對於 Amazon RDS for MySQL 複本 (EBS 型磁碟區)，一開始可能會增加複寫延遲。這是因為延遲載入效果可能會影響複寫效能。

為協助減輕新建立的讀取複本之資料表延遲載入的影響，您可以執行涉及全資料表掃描的操作。例如，在特定資料表或資料庫的讀取複本上執行 mysqldump 操作。這可讓 Amazon RDS 排定優先順序並從 Amazon Simple Storage Service (Amazon S3) 下載所有備份的表格資料。

另請考慮使用「按需」InnoDB 快取暖機功能。InnoDB 快取暖機功能將緩衝區集區狀態儲存在磁碟上 InnoDB 資料目錄中名為 ib_buffer_pool 的檔案中。此功能會在建立讀取複本之前透過傾印主要資料庫執行個體緩衝集區的目前狀態來提升效能。然後，在建立讀取複本之後重新載入緩衝區集區。