針對 Aurora MySQL 的二進位日誌複寫延遲進行故障診斷

本節提供設定為二進位日誌複本之 Aurora MySQL 的二進位日誌複寫延遲指引。它涵蓋複寫架構、平行複寫、相依性追蹤、監控和組態最佳實務。

二進位日誌複寫會在 MySQL 相容資料庫之間複寫資料。本節涵蓋的二進位日誌複寫是非同步的：來源不會在遞交交易之前等待複本確認變更的套用。這不適用於半同步複寫。在半同步複寫中，來源會等待至少一個複本確認收到交易，然後再遞交。例如，適用於 Amazon RDS for MySQL 的多可用區域資料庫叢集使用半同步複寫，這超出本節的範圍。複本會透過 I/O 執行緒維持與來源的持續連線，以持續串流二進位日誌事件。本節適用於 Aurora MySQL 為複本的所有 binlog 複寫拓撲。來源可以是另一個 Aurora MySQL 叢集、Amazon RDS for MySQL、內部部署 MySQL 或 Amazon EC2 上的 MySQL。當 Aurora MySQL 是複寫至任何 MySQL 相容目標的來源時，本節也適用。

現在遇到延遲峰值？ 略過背景資料並以 開頭識別複寫延遲瓶頸，以判斷 I/O 執行緒或 SQL 執行緒是瓶頸，然後遵循該案例的連結： 對 I/O 執行緒延遲進行故障診斷或 針對 SQL 執行緒延遲進行故障診斷。

MySQL 複寫架構

MySQL 複寫是透過特殊的執行緒實作：

複寫程序的運作方式如下：

識別複寫延遲瓶頸

複寫延遲可能發生在兩個區域：I/O 執行緒或 SQL 執行緒。第一步是判斷哪個元件落後。

Source_Log_File:       mysql-bin.000045
Read_Source_Log_Pos:   524288000
Relay_Source_Log_File: mysql-bin.000045
Exec_Source_Log_Pos:   524200000

File:     mysql-bin.000045
Position: 1073741824

對 I/O 執行緒延遲進行故障診斷

I/O 執行緒負責從來源擷取二進位日誌事件，並將其寫入轉送日誌。常見原因和解決方案：

I/O 執行緒延遲的最佳化策略

針對 SQL 執行緒延遲進行故障診斷

SQL 執行緒是 I/O 執行緒趕上來源但Seconds_Behind_Source正在成長時的瓶頸。若要量化延遲，請監控 15 分鐘的時段。比較來源 （來自 Position 的差異SHOW MASTER STATUS) 的 binlog 產生率與複本 （差異） 上的 SQL Exec_Source_Log_Pos 執行緒套用率。

常見原因和解決方案：

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
  LEFT JOIN information_schema.table_constraints tc
    ON t.table_schema = tc.table_schema
    AND t.table_name = tc.table_name
    AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_type IS NULL
  AND t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY 1;

SELECT trx_id, trx_started, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE trx_rows_modified > 10000
ORDER BY trx_started;

SELECT database_name, table_name,
    MIN(last_update) AS oldest_stat_update,
    DATEDIFF(NOW(), MIN(last_update)) AS stats_age_in_days
FROM mysql.innodb_index_stats
WHERE database_name NOT IN ('mysql', 'sys')
GROUP BY database_name, table_name
HAVING stats_age_in_days > 7
ORDER BY stats_age_in_days DESC;

使用複本端複寫篩選條件減少套用工作負載

Aurora MySQL 3.01.0 版及更高版本支援複本端複寫篩選條件。使用這些項目在套用期間略過不相關的資料庫或資料表，以減少 SQL 執行緒工作負載。SQL 執行緒會套用複本端篩選條件 – I/O 執行緒仍會從來源擷取所有二進位日誌事件，因此這些篩選條件只會減少套用工作，而非網路傳輸。與來源端篩選條件 （僅在資料庫層級操作） 不同，複本端篩選條件支援使用 replicate-do-table和 的資料表層級精細程度replicate-ignore-table。如需詳細資訊，請參閱使用 Aurora MySQL 設定複寫篩選條件。

多執行緒複寫 (MTR)

使用 MTR 時，複本會平行套用獨立交易。它無法將單一大型交易分割為平行部分。平行處理的程度受限於來源寫入的相依性資訊。

啟用 MTR 時 (replica_parallel_workers >= 2)，SQL 附加器會分割為協調器執行緒和多個工作者執行緒。協調器會從轉送日誌讀取交易。它會檢查來源內嵌在每個交易中的邏輯時間戳記 (last_committed 和 sequence_number)。然後，它會將獨立交易指派給可用的工作者執行緒，以進行平行執行。如果兩個交易不共用資料相依性，則兩個交易是獨立的，也就是說，如果它們修改不同的資料列。來源會使用設定的相依性追蹤方法，在遞交時間決定這些相依性，並將其記錄在二進位日誌中。複本無法增加超過來源允許的平行處理。

MTR 是建議的預設值

我們建議在啟用 MTR 的情況下執行複本。MTR 預設會在 MySQL 8.0.27 及更高版本中啟用 (replica_parallel_workers=4)，包括基於這些版本的 Aurora MySQL 3 版本。在舊版 (Aurora MySQL 2.12.1 及更高版本，或 8.0.27 之前以 MySQL 版本為基礎的版本） 上，透過replica_parallel_workers將 設定為 2 或更高的值來明確啟用它。當平行處理無法使用時，保持啟用 MTR 的成本很少，並讓複本在工作負載允許時利用它。

在下列情況下，MTR 不會減少延遲：

來源上的相依性追蹤

來源決定哪些交易可以使用 binlog_transaction_dependency_tracking 參數平行套用。建議值：WRITESET。

平行類型 (replica_parallel_type)

replica_parallel_type 參數決定如何在工作者執行緒之間分配交易。您有兩個選項：

MTR 組態

變更平行工作者設定後，重新啟動複寫：

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Aurora 特定的複寫最佳化

Aurora MySQL 提供下列功能來改善二進位日誌複寫效能。下表摘要說明可用性、預設狀態，以及如何啟用每個功能。

監控平行複寫

使用下列方法來監控複寫效能，並平行識別瓶頸：

效能結構描述

用於監控 MTR 的關鍵資料表：

若要評估跨工作者執行緒平均工作的方式，請使用下列查詢：

SELECT
  a.channel_name,
  rw1.thread_id AS replica_worker_thread_id,
  ts1.count_star AS number_of_trx_executed,
  ROUND((ts1.count_star / sum_count_star) * 100, 2) AS percent_of_trx_executed_by_worker
FROM (
  SELECT rw.channel_name, SUM(ts.count_star) AS sum_count_star
  FROM performance_schema.events_transactions_summary_by_thread_by_event_name AS ts
    JOIN performance_schema.replication_applier_status_by_worker AS rw
      ON ts.thread_id = rw.thread_id
  GROUP BY rw.channel_name
) AS a
JOIN performance_schema.replication_applier_status_by_worker AS rw1
  ON a.channel_name = rw1.channel_name
JOIN performance_schema.events_transactions_summary_by_thread_by_event_name AS ts1
  ON rw1.thread_id = ts1.thread_id;

如果一或兩個工作者處理大多數交易，則表示高交易相依性限制平行處理。考慮切換到來源上的 WRITESET 相依性追蹤。

如果 I/O 執行緒和 SQL 執行緒位置彼此靠近，但仍Seconds_Behind_Source在成長中，協調器執行緒本身可能是瓶頸。檢查performance_schema.replication_applier_status_by_coordinator緩衝延遲。協調器瓶頸通常表示繁重的交易相依性或多載的單一協調器執行緒，無法以足夠快的速度分佈事件。

錯誤日誌多執行緒複本統計資料

當 log_error_verbosity=3（預設值） 時，Aurora MySQL 會定期將多執行緒複本統計資料寫入 MySQL 錯誤日誌。您可以透過下列方式檢視這些項目：

若要在錯誤日誌中找到多執行緒複本統計資料項目，請搜尋下列範例中顯示的字串。MySQL 錯誤日誌在此內部字串中使用舊版術語；本文件在整個字串中使用偏好的術語「複本」。

以下是錯誤日誌項目範例：

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 7215200; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1203644200 waited (count) when Workers occupied = 1022462 waited when Workers occupied = 62349203500

要分析的關鍵欄位：

工作範例：診斷平行套用瓶頸

下列範例示範如何使用上述監控來源來診斷常見的平行套用瓶頸。每個範例都從相同的徵狀開始 – Seconds_Behind_Source 正在穩定成長，而且您已經確認 SQL 執行緒是瓶頸 （請參閱識別複寫延遲瓶頸) – 並使用多執行緒複本統計資料和效能結構描述來判斷修正動作。

範例 1：工作者佈建不足

在 CloudWatch ReplicaLag 中，指標會在 30 分鐘內穩定地從接近零爬升到大約 400 秒。複本上的 CPU 使用率維持在 55% 左右，因此複本不受 CPU 限制。

若要確認延遲的位置，請執行SHOW REPLICA STATUS兩次，相隔 60 秒。 會Read_Source_Log_Pos前進大約 1.2 GB，而 只會Exec_Source_Log_Pos前進大約 300 MB。I/O 執行緒與來源保持同步，但 SQL 執行緒落後 – SQL 執行緒是瓶頸。

MTR 已啟用 replica_parallel_workers=4。從錯誤日誌擷取最新的多執行緒複本統計資料 （請參閱 錯誤日誌多執行緒複本統計資料)：

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 123; events assigned = 9876544; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 8455000 waited (count) when Workers occupied = 2456789 waited when Workers occupied = 110293847000

解譯關鍵欄位：

診斷：協調器持續準備套用比工作者執行更多的獨立交易。由於時鐘衝突等待可忽略，因此平行處理受限於工作者計數，而非依相依性而定。

動作：增加replica_parallel_workers複本的 vCPU 計數 （例如 上的 16db.r6g.4xlarge) 並重新啟動複寫：

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

驗證：稍後的統計資料行顯示等待幾乎消失，並在 5 秒內ReplicaLag返回 ：

2026-05-10 15:02:14.882201 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 31840552; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 9120000 waited (count) when Workers occupied = 41233 waited when Workers occupied = 5980411000

「工作者佔用時等待」從大約 110 秒下降到大約 6 秒，輸送量 （指派的事件） 超過三倍。當 CPU 使用率接近 80% 或等待停止減少時，停止增加工作者。

範例 2：COMMIT_ORDER 相依性追蹤序列化低並行工作負載

來源是執行 OLTP 工作負載並行遞交應用程式執行緒的 Amazon RDS for MySQL 執行個體。複本是db.r6g.4xlarge具有 replica_parallel_workers=16和 的 replica_parallel_type=LOGICAL_CLOCK。儘管有 16 名工作者， ReplicaLag仍維持穩定約 600 秒，而複本 CPU 使用率只有約 20% – 工作者看起來閒置。

首先，使用來自 的工作者分佈查詢，檢查交易在工作者之間分佈的均勻程度效能結構描述：

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                1903556 |                             96.41 |
|              |                       46 |                  23104 |                              1.17 |
|              |                       47 |                  18995 |                              0.96 |
|              |                       48 |                  16720 |                              0.85 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

上述輸出顯示 16 個工作者執行緒的前 4 個。一名工作者正在套用超過 96% 的交易，而其他 15 人幾乎閒置 （每個剩餘的工作者都執行不到 0.05%)。套用實際上是序列化的。

接著，使用錯誤日誌中的多執行緒複本統計資料進行確認：

2026-05-10 15:22:11.110432 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 2014500; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 98712340000 waited (count) when Workers occupied = 50122 waited when Workers occupied = 1209847000

這表示相依性問題，而不是容量問題。檢查來源上的相依性追蹤方法：

SELECT @@global.binlog_transaction_dependency_tracking;

+-------------------------------------------------+
| @@global.binlog_transaction_dependency_tracking |
+-------------------------------------------------+
| COMMIT_ORDER                                    |
+-------------------------------------------------+

根本原因：透過 COMMIT_ORDER，來源會根據同一二進位日誌群組遞交中一起遞交的交易，決定哪些交易可以平行執行。在此低並行工作負載中，一次只有少數執行緒遞交，因此群組遞交很小。因此，來源會以等於先前交易的 last_committed的值對幾乎每筆交易加上戳記sequence_number，並將它標記為與之前的交易相依。然後，複本必須逐一套用它們，即使它們修改了完全不相關的資料列，這就是 16 名工作者中有 15 名處於閒置狀態的原因。

動作：將來源切換到資料列層級相依性追蹤，這與遞交時間無關。以動態方式設定，並將其保留在叢集 （或資料庫） 參數群組中：

SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

WRITESET 會計算每個交易修改的資料列雜湊，因此接觸不同資料列的交易會收到獨立的時間戳記，無論交易何時遞交，都可以平行套用。確保所有資料表都有主索引鍵，因為 WRITESET 會為沒有它們的資料表產生空的寫入集 （請參閱 針對 SQL 執行緒延遲進行故障診斷)。在 MySQL 8.4 上，WRITESET 是預設值，此參數不再存在。

驗證：變更後，工作者分佈查詢會顯示平均分散到所有工作者的工作 （顯示前 4/16；每個工作者現在處理大約 6% 的交易）：

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                 132540 |                              6.30 |
|              |                       46 |                 125610 |                              5.97 |
|              |                       47 |                 129774 |                              6.17 |
|              |                       48 |                 122901 |                              5.84 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

和 錯誤日誌統計資料顯示當 ReplicaLag降至接近零時，時鐘衝突會摺疊：

2026-05-10 15:41:55.204713 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 19874100; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 412300000 waited (count) when Workers occupied = 88122 waited when Workers occupied = 7019847000

「等待時間衝突」從大約 99 秒下降到大約 0.4 秒，輸送量增加大約十倍，瓶頸從相依性轉移到工作者容量，此時您可以調整工作者計數，如範例 1 所示。

範例 3：缺少的主索引鍵強制在複本上掃描完整資料表

ReplicaLag 在執行大型 UPDATE和 DELETE陳述式的夜間批次任務期間尖峰，之後會復原。在尖峰期間，複本 CPU 是中等的，但有一個工作者似乎卡住。

在複本上，執行SHOW PROCESSLIST並查看複寫工作者執行緒。同一陳述式上的一個工作者一次會保持 Updating 狀態數秒：

+----+-------------+------+---------+----------+------+
| Id | User        | db   | Command | State    | Time |
+----+-------------+------+---------+----------+------+
| 12 | system user | app  | Connect | Updating |   38 |
+----+-------------+------+---------+----------+------+

使用來自 的偵測查詢，檢查哪些複寫資料表缺少主索引鍵針對 SQL 執行緒延遲進行故障診斷：

+--------------+----------------+
| table_schema | table_name     |
+--------------+----------------+
| app          | events_archive |
+--------------+----------------+

根本原因：使用以 ROW 為基礎的二進位記錄， UPDATE或 DELETE事件中的每一列都必須位於複本上，才能套用。如果資料表沒有主索引鍵或唯一的 NOT NULL 索引鍵，複本會為每個受影響的資料列執行完整資料表掃描。在multi-million-row的資料表上，批次操作會變成數百萬個完整掃描，而套用它的工作者會停滯 – 封鎖套用進度並增加延遲。

動作：將主索引鍵新增至資料表。如果您無法立即定義明確金鑰，請將 sql_generate_invisible_primary_key 參數設定為 （在基於 MySQL 8.0.30 和更新版本的 Aurora MySQL 3 版本中ON可用） 以啟用產生不可見的主金鑰 (GIPK)，讓新資料表接收自動主金鑰 （請參閱 針對 SQL 執行緒延遲進行故障診斷)。

驗證：新增主索引鍵後，工作者不再停留在 中Updating，SQL 執行緒會套用速率復原，並在下一個批次時段ReplicaLag返回基準。

範例 4：單一大型交易無法平行化

ReplicaLag 每天在可預測的時間急劇跳躍，保留幾分鐘，然後降回接近零。WRITESET 相依性追蹤已啟用， replica_parallel_workers=16和所有資料表都有主索引鍵，因此先前的範例不適用。

在尖峰期間，工作者分佈查詢會顯示一個工作者忙碌和閒置，但與範例 2 不同，錯誤日誌不會顯示高時鐘衝突或工作者佔用的等待：

2026-05-10 02:13:40.551922 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 95; events assigned = 4120000; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1530000 waited (count) when Workers occupied = 980 waited when Workers occupied = 210440000

相依性等待和工作者佔用的等待都很低，但只有一個工作者處於作用中狀態。這可排除相依性瓶頸 （範例 2) 和工作者容量瓶頸 （範例 1)。

檢查 中的忙碌工作者performance_schema.replication_applier_status_by_worker，或執行 SHOW PROCESSLIST。單一工作者在整個尖峰期間套用一筆交易。在來源上，應用程式任務會執行一個大型陳述式，例如DELETE FROM orders WHERE created_at < '2025-01-01'影響數百萬個資料列，做為單一交易。

根本原因：MTR 跨獨立交易平行化；無法跨工作者分割單一交易。一個大型交易只由一個工作者套用，因此新增工作者、切換到 WRITESET 或新增主索引鍵沒有幫助。如需詳細資訊，請參閱多執行緒複寫 (MTR)。

動作：將大型操作在來源上分成較小的批次 （例如，每個交易刪除幾千列的區塊，並在批次之間短暫暫停）。較小的交易會獨立遞交，因此複本可以將它們分散到工作者。盡可能在低流量時段期間排程大量任務。

驗證：在批次處理任務後，每日ReplicaLag尖峰會扁平化，而工作者分佈查詢會顯示批次分散到多個工作者，而不是一個。

監控最佳實務

將複寫延遲降至最低的最佳實務

下列建議摘要說明將複寫延遲降至最低的關鍵動作。如需每個主題的詳細指引，請遵循交叉參考連結。