對適用於 MySQL 的 Azure 資料庫 - 彈性伺服器中的複寫延遲進行疑難排解
適用於：
適用於 MySQL 的 Azure 資料庫 - 單一伺服器適用於 MySQL 的 Azure 資料庫 - 彈性伺服器
重要
適用於 MySQL 的 Azure 資料庫單一伺服器位於淘汰路徑上。 強烈建議您升級至適用於 MySQL 的 Azure 資料庫彈性伺服器。 如需移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器的詳細資訊，請參閱適用於 MySQL 的 Azure 資料庫單一伺服器會發生什麼事？ (部分機器翻譯)
注意
本文會參照 Microsoft 已不再使用的詞彙。 從軟體中移除該字詞時，我們也會將其從本文中移除。
讀取複本功能可讓您將資料從適用於 MySQL 的 Azure 資料庫伺服器複寫到唯讀複寫伺服器。 您可以將讀取和報告查詢從應用程式路由至複本伺服器，藉此擴增工作負載。 此設定可降低來源伺服器的壓力，並隨著應用程式調整而改善應用程式的整體效能和延遲。
複本會使用 MySQL 引擎的原生二進位記錄 (binlog) 檔案位置型複寫技術來進行非同步更新。 如需詳細資訊，請參閱 MySQL binlog 檔案位置型複寫設定概觀。
次要讀取複本上的複寫延遲取決於數個因素。 這些要素包括但不限於：
- 網路延遲。
- 來源伺服器上的交易量。
- 來源伺服器和次要讀取複本伺服器的計算層。
- 在來源伺服器和次要伺服器上執行的查詢。
在本文中，您將了解如何針對適用於 MySQL 的 Azure 資料庫中的複寫延遲進行疑難排解。 您也會更了解複本伺服器上增加的複寫延遲的一些常見原因。
注意
本文包含「從屬」一詞的參考，Microsoft 已不再使用該字詞。 從軟體中移除該字詞時，我們也會將其從本文中移除。
複寫概念
啟用二進位記錄檔時，來源伺服器會將認可的交易寫入二進位記錄檔。 二進位記錄檔會用於複寫。 預設會針對支援最多 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 二進位記錄檔複寫概觀。