對適用於 MySQL 的 Azure 資料庫 - 彈性伺服器中的複寫延遲進行疑難排解

發行項
12/18/2024

注意

本文包含「從屬」一詞的參考，Microsoft 已不再使用該字詞。從軟體中移除該字詞時，我們也會將其從本文中移除。

讀取複本功能可讓您將資料從適用於 MySQL 的 Azure 資料庫伺服器複寫到唯讀複寫伺服器。您可以將讀取和報告查詢從應用程式路由至複本伺服器，藉此擴增工作負載。此設定可降低來源伺服器的壓力，並隨著應用程式調整而改善應用程式的整體效能和延遲。

複本會使用 MySQL 引擎的原生二進位記錄 (binlog) 檔案位置型複寫技術來進行非同步更新。如需詳細資訊，請參閱 MySQL binlog 檔案位置型複寫設定概觀。

次要讀取複本上的複寫延遲取決於數個因素。這些要素包括但不限於：

網路延遲。
來源伺服器上的交易量。
來源伺服器和次要讀取複本伺服器的計算層。
在來源伺服器和次要伺服器上執行的查詢。

在本文中，您將了解如何針對適用於 MySQL 的 Azure 資料庫中的複寫延遲進行疑難排解。您也會更了解複本伺服器上增加的複寫延遲的一些常見原因。

複寫概念

啟用二進位記錄檔時，來源伺服器會將認可的交易寫入二進位記錄檔。二進位記錄檔會用於複寫。預設會針對支援最多 16 TB 儲存體的所有新佈建伺服器開啟。在複本伺服器上，每個複本伺服器上都會執行兩個執行緒。其中一個執行緒是 IO 執行緒，另一個是 SQL 執行緒：

IO 執行緒會連線到來源伺服器，並要求更新的二進位記錄檔。此執行緒會收到二進位記錄檔更新。這些更新會儲存在複本伺服器上，並儲存在稱為轉寄記錄檔的本機記錄檔中。
SQL 執行緒會讀取轉寄記錄檔，然後在複本伺服器上套用資料變更。

監視複寫延遲

適用於 MySQL 的 Azure 資料庫會在 Azure 監視器中提供複寫延遲 (秒) 的計量。此計量僅適用於讀取複本伺服器。這是透過 MySQL 中提供的 seconds_behind_master 計量來計算。

若要了解增加複寫延遲的原因，請使用 MySQL Workbench 或 Azure Cloud Shell 連線到複本伺服器。然後執行下列命令。

注意

在您的程式碼中，將範例值取代為您的複本伺服器名稱和管理使用者名稱。適用於 MySQL 的 Azure 資料庫管理員使用者名稱需要 @\<servername>。

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p

以下是 Cloud Shell 終端機中的體驗外觀：

Requesting a Cloud Shell.Succeeded.
Connecting terminal...

Welcome to Azure Cloud Shell

Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell

user@Azure:~$mysql -h myreplicademoserver.mysql.database.azure.com -u myadmin@mydemoserver -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64796
Server version: 5.6.42.0 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

在相同的 Cloud Shell 終端機中，執行下列命令：

mysql> SHOW SLAVE STATUS;

以下是典型的輸出：

輸出包含許多資訊。一般而言，您只需將焦點放在下表描述的資料列上。

計量	描述
Slave_IO_State	表示 IO 執行緒的目前狀態。一般而言，如果來源 (主要) 伺服器正在同步，狀態會是「等候 master 傳送事件」。「正在連線到 master」之類的狀態表示複本失去與來源伺服器的連線。請確定來源伺服器正在執行，或檢查防火牆是否封鎖連線。
Master_Log_File	表示來源伺服器正在寫入的二進位記錄檔。
Read_Master_Log_Pos	指出來源伺服器在二進位記錄檔中寫入的位置。
Relay_Master_Log_File	表示複本伺服器從來源伺服器讀取的二進位記錄檔。
Slave_IO_Running	指出 IO 執行緒是否執行中。值應為 `Yes`。如果值為 `NO`，則複寫可能會中斷。
Slave_SQL_Running	指出 SQL 執行緒是否執行中。值應為 `Yes`。如果值為 `NO`，則複寫可能會中斷。
Exec_Master_Log_Pos	指出要套用複本的 Relay_Master_Log_File 的位置。如果有延遲，則此位置順序應該小於 Read_Master_Log_Pos。
Relay_Log_Space	指出所有現有轉寄記錄檔的合併大小總計。您可以檢查上限大小，方法是查詢 `SHOW GLOBAL VARIABLES` 例如 `relay_log_space_limit`。
Seconds_Behind_Master	顯示覆寫延遲 (以秒為單位)。
Last_IO_Errno	顯示 IO 執行緒錯誤碼 (如果有的話)。如需這些錯誤碼的詳細資訊，請參閱 MySQL 伺服器錯誤訊息參考。
Last_IO_Error	顯示 IO 執行緒錯誤訊息 (如果有的話)。
Last_SQL_Errno	顯示 SQL 執行緒錯誤碼 (如果有的話)。如需這些錯誤碼的詳細資訊，請參閱 MySQL 伺服器錯誤訊息參考。
Last_SQL_Error	顯示 SQL 執行緒錯誤訊息 (如果有的話)。
Slave_SQL_Running_State	指出目前的 SQL 執行緒狀態。在此狀態中，`System lock` 是正常的。看到 `Waiting for dependent transaction to commit` 的狀態也是正常的。此狀態表示複本正在等候 SQL 背景工作執行緒更新認可的交易。

如果 Slave_IO_Running 為 Yes 且 Slave_SQL_Running 為 Yes，則複寫會正常執行。

接下來，檢查 Last_IO_Errno、Last_IO_Error、Last_SQL_Errno 和 Last_SQL_Error。這些欄位會顯示導致 SQL 執行緒停止的最新錯誤的錯誤編號和錯誤訊息。錯誤號碼為 0 及訊息空白表示沒有錯誤。藉由檢查 MySQL 伺服器錯誤訊息參考中的錯誤碼，調查任何非零的錯誤值。

高複寫延遲的常見案例

下列各節著重於常見的高複寫延遲案例。

來源伺服器上的網路延遲或高 CPU 耗用量

如果您看到下列值，則複寫延遲可能是因為來源伺服器上的高網路延遲或高 CPU 耗用量所導致。

Slave_IO_State: Waiting for master to send event
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller than Master_Log_File, e.g. mysql-bin.00010

在此情況下，IO 執行緒執行中，並且正在來源伺服器上等候。來源伺服器已寫入二進位記錄檔編號 20。複本只收到最多到檔案編號 10。在此案例中，高複寫延遲的主要因素是來源伺服器上的網路速度或高 CPU 使用率。

在 Azure 中，區域內的網路延遲通常可以以毫秒測量。跨區域，延遲範圍從毫秒到秒。

在大部分情況下，IO 執行緒與來源伺服器之間的連線延遲是由來源伺服器上的高 CPU 使用率所造成。系統會緩慢處理 IO 執行緒。您可以使用 Azure 監視器來檢查 CPU 使用率和來源伺服器上的並行連線數目，以偵測此問題。

如果您在來源伺服器上看不到高 CPU 使用率，問題可能是網路延遲。如果網路延遲突然異常地高，請檢查 Azure 狀態頁面是否有已知問題或中斷。

來源伺服器上的大量交易高載

如果您看到下列值，則來源伺服器上的大量交易高載可能造成複寫延遲。

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller then Master_Log_File, e.g. mysql-bin.00010

輸出顯示複本可以擷取來源伺服器背後的二進位記錄檔。但複本 IO 執行緒指出轉送記錄空間已滿。

網路速度未造成延遲。複本正嘗試趕上。但更新的二進位記錄檔大小超過轉送記錄空間的上限。

若要針對此問題進行疑難排解，請在來源伺服器上啟用慢速查詢記錄檔。使用慢速查詢記錄檔來識別來源伺服器上長時間執行的交易。然後微調該識別的查詢，以減少伺服器上的延遲。

此排序的複寫延遲通常是由來源伺服器上的資料載入所造成。當來源伺服器有每週或每月的資料載入時，複寫延遲是無法避免的。複本伺服器最終會在來源伺服器上的資料載入完成之後趕上。

複本伺服器上的緩慢

如果您觀察到下列值，則問題可能在複本伺服器上。

Slave_IO_State: Waiting for master to send event
Master_Log_File: The binary log file sequence equals to Relay_Master_Log_File, e.g. mysql-bin.000191
Read_Master_Log_Pos: The position of master server written to the above file is larger than Relay_Log_Pos, e.g. 103978138
Relay_Master_Log_File: mysql-bin.000191
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: The position of slave reads from master binary log file is smaller than Read_Master_Log_Pos, e.g. 13468882
Seconds_Behind_Master: There is latency and the value here is greater than 0

在此案例中，輸出會顯示 IO 執行緒和 SQL 執行緒都正常執行。複本會讀取來源伺服器寫入的相同二進位記錄檔。不過，複本伺服器上的某些延遲會反映來自來源伺服器的相同交易。

下列各節描述這種延遲的常見原因。

資料表上沒有主要索引鍵或唯一索引鍵

適用於 MySQL 的 Azure 資料庫使用以資料列為基礎的複寫。來源伺服器會將事件寫入二進位記錄檔，記錄個別資料表資料列中的變更。 SQL 執行緒接著會將這些變更複寫到複本伺服器上的對應資料表資料列。當資料表缺少主要索引鍵或唯一索引鍵時，SQL 執行緒會掃描目標資料表中的所有資料列以套用變更。這種掃描會導致複寫延遲。

在 MySQL 中，主要索引鍵是相關聯的索引，可確保快速查詢效能，因為它不能包含 NULL 值。如果您使用 InnoDB 儲存引擎，資料表資料實際上會經過組織，以根據主要索引鍵執行超快速查閱和排序。

建議您先在來源伺服器的資料表上新增主要索引鍵，再建立複本伺服器。在來源伺服器上新增主要索引鍵，然後重新建立讀取複本，以協助改善複寫延遲。

使用下列查詢來找出來源伺服器上遺漏主要索引鍵的資料表：

select tab.table_schema as database_name, tab.table_name 
from information_schema.tables tab left join 
information_schema.table_constraints tco 
on tab.table_schema = tco.table_schema 
and tab.table_name = tco.table_name 
and tco.constraint_type = 'PRIMARY KEY' 
where tco.constraint_type is null 
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys') 
and tab.table_type = 'BASE TABLE' 
order by tab.table_schema, tab.table_name;

複本伺服器上長時間執行的查詢

複本伺服器上的工作負載可能讓 SQL 執行緒延遲落後於 IO 執行緒。複本伺服器上長時間執行的查詢是高複寫延遲的常見原因之一。若要針對此問題疑難排解，請在複本伺服器上啟用慢速查詢記錄檔。

慢速查詢可能會增加資源耗用量或使伺服器變慢，使得複本無法跟上來源伺服器。在此案例中，微調緩慢的查詢。更快速的查詢可防止封鎖 SQL 執行緒，並大幅改善複寫延遲。

來源伺服器上的 DDL 查詢

在來源伺服器上，資料定義語言 (DDL) 命令 (如 ALTER TABLE) 可能需要很長的時間。當 DDL 命令正在執行時，其他數千個查詢可能會在來源伺服器上平行執行。

複寫 DDL 時，為確保資料庫一致性，MySQL 引擎會在單一複寫執行緒中執行 DDL。在此工作期間，所有其他複寫查詢都會遭到封鎖，而且必須等到 DDL 作業在複本伺服器上完成為止。即使是線上 DDL 作業也會造成此延遲。 DDL 作業會增加複寫延遲。

如果您已在來源伺服器上啟用慢速查詢記錄檔，您可以檢查在來源伺服器上執行的 DDL 命令來偵測此延遲問題。透過索引卸載、重新命名和建立，您可以使用 ALTER TABLE 的 INPLACE 演算法。您可能需要複製資料表資料並重建資料表。

一般而言，INPLACE 演算法支援並行 DML。但是，當您準備和執行作業時，您可以簡短地對資料表進行獨佔中繼資料鎖定。因此，針對 CREATE INDEX 陳述式，您可以使用子句 ALGORITHM 和 LOCK 來影響資料表複製的方法，以及讀取和寫入的並行層級。您仍然可以藉由新增 FULLTEXT 索引或 SPATIAL 索引來防止 DML 作業。

下列範例會使用 ALGORITHM 和 LOCK 子句來建立索引。

ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;

不幸的是，對於需要鎖定的 DDL 陳述式，您無法避免複寫延遲。若要降低潛在影響，請在離峰期間 (例如在夜間) 執行這些類型的 DDL 作業。

降級的複本伺服器

在適用於 MySQL 的 Azure 資料庫中，讀取複本會使用與來源伺服器相同的伺服器設定。建立複本伺服器設定之後，您可以加以變更。

如果複本伺服器已降級，工作負載可能會耗用更多資源，進而導致複寫延遲。若要偵測此問題，請使用 Azure 監視器來檢查複本伺服器的 CPU 和記憶體耗用量。

在此案例中，建議您將複本伺服器的設定保持在等於或大於來源伺服器的值。此設定可讓複本與來源伺服器保持同步。

藉由調整來源伺服器參數來改善複寫延遲

在適用於 MySQL 的 Azure 資料庫，依預設，複寫會最佳化為在複本上使用平行執行緒執行。當來源伺服器上的高並行工作負載造成複本伺服器落後時，您可以在來源伺服器上設定參數 binlog_group_commit_sync_delay 來改善複寫延遲。

binlog_group_commit_sync_delay 參數可控制二進位記錄認可要等待多少毫秒之後，才同步處理二進位記錄檔。此參數的優點是，來源伺服器不會立即套用每個認可的交易，而是大量傳送二進位記錄檔更新。此延遲可減少複本上的 IO，並協助改善效能。

將 binlog_group_commit_sync_delay 參數設定為 1000 之類可能有幫助。然後監視複寫延遲。謹慎設定此參數，並只用於高並行工作負載。

重要

在複本伺服器中，建議將 binlog_group_commit_sync_delay 參數設定為 0。建議這樣做是因為與來源伺服器不同，複本伺服器不會有高並行性，而且增加複本伺服器上 binlog_group_commit_sync_delay 的值可能會意外增加複寫延遲。

對於包含許多單一交易的低並行工作負載，binlog_group_commit_sync_delay 設定可能會增加延遲。延遲可能會增加，因為 IO 執行緒會等候大量二進位記錄檔更新，即使只認可了少數交易亦然。

進階疑難排解選項

如果使用 show slave status 命令無法提供足夠的資訊來針對複寫延遲進行疑難排解，請嘗試檢視這些其他選項，以了解哪些處理序為作用中或正在等候。

檢視執行緒資料表

performance_schema.threads 資料表會顯示處理序狀態。狀態為 Waiting for lock_type lock 的處理序表示其中一個資料表上有鎖定，導致複製執行緒無法更新該資料表。

SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';

如需詳細資訊，請參閱一般執行緒狀態 (英文)。

檢視 replication_connection_status 資料表

performance_schema.replication_connection_status 資料表會顯示處理複本與來源連線之複寫 I/O 執行緒的目前狀態，而且會更頻繁地變更。資料表包含的值會在連線期間不同。

SELECT * FROM performance_schema.replication_connection_status;

檢視 replication_applier_status_by_worker 資料表

performance_schema.replication_applier_status_by_worker 資料表會顯示背景工作執行緒的狀態、上次看到的交易以及最後一個錯誤號碼和訊息，這可協助您找出有問題的交易並找出根本原因。

您可以在 Data-in 複寫中執行下列命令，以略過錯誤或交易：

az_replication_skip_counter

或

az_replication_skip_gtid_transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker;

檢視 SHOW RELAYLOG EVENTS 陳述式

show relaylog events 陳述式會顯示複本轉送記錄中的事件。

· 針對 GITD 型復寫 (讀取複本)，陳述式會顯示 GTID 交易和 binlog 檔案及其位置，您可以使用 mysqlbinlog 來取得正在執行的內容和陳述式。 · 針對 MySQL binlog 位置複寫 (用於 Data-in 複寫)，它會顯示正在執行的陳述式，這有助於了解正在執行的資料表交易

檢查 InnoDB 標準監視器和鎖定監視器輸出

您也可以嘗試檢查 InnoDB 標準監視器和鎖定監視器輸出，以協助解決鎖定和死結，並將複寫延遲降至最低。鎖定監視器與標準監視器相同，不同之處在於它包含其他鎖定資訊。若要檢視這個額外的鎖定和死結資訊，請執行 show engine innodb status\G 命令。

下一步

請參閱 MySQL 二進位記錄檔複寫概觀。

共用方式為