複製延遲時間

本頁說明如何排解及修正 Cloud SQL 唯讀副本的複製延遲問題。

總覽

複製延遲可能發生在下列情況：

• 主要執行個體無法快速將變更傳送至副本。
• 副本無法及時接收變更。
• 副本無法及時套用變更。

總延遲時間會以 replica_lag 指標表示。replica_lag 和 network_lag 之間的差異可能表示備用資源無法及時套用複製變更的第三個原因。下方的「監控複寫延遲」一節將說明這些指標。

加快備用資源設定速度

我們提供兩種方法，可讓 MySQL 副本更快套用變更。使用者可以透過下列選項設定副本：

• 平行複製
• 高效能排空

平行複製

平行複製功能可讓備用資源使用多個平行運作的執行緒，在備用資源上套用變更，有助於解決複製延遲問題。如要瞭解如何使用平行複製功能，請參閱「 設定平行複製功能」。

設定 replica_parallel_workers (或 slave_parallel_workers) 標記啟用並行複製時，請注意下列事項：

• 建議您將 replica_parallel_workers 旗標值設為與副本執行個體的 vCPU 數量相符的數字。如果將此值設得很高，可能會導致鎖定等待、鎖定等待逾時和死結。如果發現鎖定等待尖峰與複寫延遲時間一致，請考慮減少平行處理。
• 如果您的 MySQL 版本支援 binlog_transaction_dependency_tracking 旗標，請考慮將主要執行個體設為 WRITESET。這是 8.4 以上版本的預設行為。

高效能排空

根據預設，MySQL 適用的 Cloud SQL 會在每筆交易完成後，將重做記錄排清至磁碟，確保資料耐久性。高效能排清會將重做記錄檔排清至磁碟的頻率降至每秒一次。這樣做可以減少磁碟 I/O，進而提升備用資源的寫入效能。

將唯讀副本的 innodb_flush_log_at_trx_commit 旗標設為 2。如果已為副本啟用二進位記錄，建議您將 sync_binlog 標記設為較高的值 (例如 10,000)，以確保 innodb_flush_log_at_trx_commit 標記生效。

如要進一步瞭解此標記，請參閱「使用標記的訣竅」。

在唯讀備用資源上設定 innodb_flush_log_at_trx_commit 旗標後，如果 Cloud SQL 偵測到可能發生當機，就會自動重建備用資源。

確保備用資源已充分佈建

如果備用資源執行個體比主要執行個體小 (例如 vCPU 和記憶體較少)，可能會發生複製延遲。與較大的主要執行個體相比，較小的副本也可能具有不同的預設設定標記。建議副本執行個體至少要與主要執行個體一樣大，才能有足夠的資源處理複寫負載。

備用資源的 CPU 使用率偏高也可能導致複製延遲。如果副本的 CPU 使用率偏高 (例如超過 90%)，請考慮增加副本的 CPU 容量。

最佳化查詢和結構定義

本節提供一些常見的查詢和結構定義最佳化建議，有助於提升複寫效能。

唯讀副本中的查詢隔離等級

REPEATABLE READ和SERIALIZABLE交易隔離層級會取得可能封鎖複製變更的鎖定。建議您降低副本中查詢的隔離等級。READ COMMITTED交易隔離等級的成效可能較佳。

主要資料庫中長時間執行的交易

主要執行個體上長時間執行的交易可能會導致複製延遲。交易完成後，二進位記錄檔才會傳送至副本。

如果單一交易更新大量資料列，可能導致需要套用至主要執行個體，然後傳送至副本的變更數量突然暴增。這適用於一次影響多個資料列的單一陳述式更新或刪除作業。變更會在提交後傳送至副本。 如果備用資源的查詢負載也很高，在備用資源中突然套用大量變更可能會增加備用資源發生鎖定爭用的可能性，導致複寫延遲。

建議將大型交易拆分成多筆小型交易。您可以檢查主要節點上的 cloudsql.googleapis.com/database/mysql/innodb/active_trx_longest_time 指標，監控長時間執行的交易。

缺少主鍵

Cloud SQL 唯讀備用資源使用列式複製，如果複製的 MySQL 資料表沒有主索引鍵，效能就會不佳。建議所有複製的資料表都設有主鍵。

如果是 MySQL 8 以上版本，建議將 sql_require_primary_key 旗標設為 ON，要求資料庫中的資料表必須有主鍵。

唯讀副本中的長期交易

副本上長時間執行的交易 (例如 SELECT 陳述式) 可能會封鎖或減緩複製作業。資料表掃描是常見問題。調查任何長時間執行的查詢，並考慮進行最佳化。這些查詢可能會導致InnoDB記錄清單大小增加。

過長的InnoDB記錄長度

如果 InnoDB 記錄清單過大，可能會導致效能問題，並減緩複製速度。您可以使用 cloudsql.googleapis.com/database/mysql/innodb/history_list_length 指標監控記錄清單長度。主要廣告活動的這項指標也可能偏高，且可能已造成成效問題。如果副本在初始啟動後顯示複製延遲時間過長，可能就是這個原因。

造成記錄清單過長的原因可能如下：

• 長時間執行的交易。長時間執行或閒置的交易會阻礙系統清除舊的復原記錄項目。
• 磁碟效能緩慢。清除作業會大量使用 I/O。
• REPEATABLE READ隔離等級。這有助於增加歷史記錄清單。
• 清除設定不足。控制專用於清除作業的執行緒數量的 innodb_purge_threads 參數，可能設定得太低，無法滿足工作負載需求。

如要解決這個問題，請嘗試下列方法：

• 將大型交易拆成多筆小型交易。加快清除舊記錄的速度。
• 使用較大的執行個體。較大的執行個體有更多 CPU 和記憶體。
• 調整清除設定。調高 innodb_purge_threads、innodb_io_capacity 和 innodb_io_capacity_max。
• 使用 READ COMMITTED 隔離等級。
• 確認資料表有主鍵。如果資料表沒有主鍵，可能會導致資料表掃描，進而減緩複製作業，並導致記錄清單變長。

鎖定等待時間過長

備用資源上的鎖定等待次數過多可能會導致複製作業變慢，尤其是在啟用平行複製的情況下。您可以使用下列指標監控鎖定等待和死結：

• cloudsql.googleapis.com/database/mysql/innodb/row_lock_waits_count
• cloudsql.googleapis.com/database/mysql/innodb/row_lock_time
• cloudsql.googleapis.com/database/mysql/innodb/lock_timeout_count
• cloudsql.googleapis.com/database/mysql/innodb/deadlocks_count

如果這些鎖定指標過高，且似乎與複製延遲相關，請考慮調低 replica_parallel_workers 旗標的值。隔離等級也可能會影響鎖定。

DDL 導致的專屬鎖定

資料定義語言 (DDL) 指令 (例如 ALTER TABLE 和 CREATE INDEX) 可能會導致備用資源出現複製延遲，這是因為這些指令會造成獨占鎖定。為避免鎖定爭用，請考慮在副本的查詢負載較低時，排定 DDL 執行時間。

過載的副本

如果唯讀副本收到太多查詢，複製作業可能會遭到封鎖。請考慮將讀取作業分散到多個備用資源，以減輕每個資源的負擔。

為避免查詢量暴增，請考慮在應用程式邏輯或 Proxy 層 (如有使用) 中，調節副本讀取查詢。

如果主要執行個體上的活動量突然暴增，請考慮分散更新作業。

單體式主要資料庫

考慮垂直 (或水平) 分片主要資料庫，避免一或多個延遲的資料表拖累所有其他資料表。

監控複製延遲

您可以使用 replica_lag 和 network_lag 指標監控複製延遲，並判斷延遲原因是否在於主要資料庫、網路或副本。

確認複製設定

如要確認複製功能是否正常運作，請對副本執行下列陳述式：

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: xx.xxx.xxx.xxx
                  Master_User: cloudsqlreplica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.199927
          Read_Master_Log_Pos: 83711956
               Relay_Log_File: relay-log.000025
                Relay_Log_Pos: 24214376
        Relay_Master_Log_File: mysql-bin.199898
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 24214163
              Relay_Log_Space: 3128686571
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: master_server_ca.pem
           Master_SSL_CA_Path: /mysql/datadir
              Master_SSL_Cert: replica_cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: replica_pkey.pem
        Seconds_Behind_Master: 2627
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 321071839
                  Master_UUID: 437d04e9-8456-11e8-b13d-42010a80027b
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:52111095710-52120776390
            Executed_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:1-52113039508
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

如果正在進行複製作業，第一欄 Slave_IO_State 會顯示 Waiting for master to send event 或類似訊息。此外，Last_IO_Error 欄位為空白。

如果未進行複製作業，Slave_IO_State 欄會顯示狀態 Connecting to master，Last_IO_Error 欄則會顯示狀態 error connecting to master cloudsqlreplica@x.x.x.x:3306。

根據 MySQL 說明文件，與複製延遲相關的其他有趣欄位包括：

在先前的範例中，Relay_Master_Log_File 的值為 mysql-bin.199898。Master_Log_File 的值為 mysql-bin.199927。數字後置字元 199898 小於 199927。也就是說，即使副本已收到較新的 mysql-bin.199927 記錄檔，仍會套用較舊的 mysql-bin.199898。

在此情況下，SQL 執行緒在副本中會延遲。

您也可以連線至主要資料庫並執行下列指令：

  SHOW MASTER STATUS;

這個指令會顯示主要資料庫中正在寫入的 binlog 檔案。

如果主要資料庫的二進位記錄檔比副本中的 Master_Log_File 新，表示 I/O 執行緒延遲。副本仍在讀取主要資料庫中較舊的二進位記錄檔。

如果 I/O 執行緒延遲，network_lag 指標也會偏高。如果 SQL 執行緒延遲，但 I/O 執行緒沒有，則 network_lag 指標不會太高，但 replica_lag 指標會很高。

先前的指令可讓您在發生延遲時觀察延遲詳細資料，但 network_lag 和 replica_lag 指標可讓您查看過去發生的延遲。

重新建立落後的副本

如果複製作業延遲的時間超出可接受範圍，請重新建立延遲的副本。

使用 Cloud SQL 時，您可以設定唯讀備用資源，在複製作業延遲超過可接受的時間長度，且延遲時間持續至少五分鐘時，重新建立自身。

如果您將可接受的複寫延遲時間定義為少於 360 秒 (六分鐘)，且複寫延遲時間持續超過 361 秒超過五分鐘，則五分鐘後，主要執行個體會建立自身的全新快照，並使用這個快照重新建立唯讀副本。

重新建立延遲的唯讀副本有下列好處：

• 您可以控管複製延遲的可接受範圍。
• 您可以減少解決複製延遲問題的時間，節省數小時甚至數天的時間。

適用其他功能詳細資料：

• 支援下列版本：
  • MySQL 5.7
  • MySQL 8.0
  • MySQL 8.4
• 必須以秒為單位定義可接受的複製延遲或延遲範圍。
• 可接受的最低值為 300 秒或五分鐘。
• 可接受的最大值為 31,536,000 秒或一年。
  • 如果您為執行個體啟用「重新建立延遲的備用資源」，但未設定可接受的最大複製延遲時間，Cloud SQL 會使用預設值 (一年)。
• 支援的執行個體類型：
  • 唯讀備用資源
  • 跨區域唯讀副本
  • 層疊式副本
• 為 replicationLagMaxSeconds 欄位設定的值適用於每個副本執行個體。如果主要執行個體有多個副本執行個體，則可以為每個副本設定不同的值。
• 重建副本時，系統會完成下列作業，因此使用者可能會遇到停機時間：
  • 複製作業已停止。
  • 已刪除副本。
  • 建立主要執行個體的快照。
  • 系統會根據這個最新快照重新建立副本。新的副本會使用與先前副本相同的名稱和 IP 位址。因此 MySQL 必須停止並重新啟動。
  • 新的副本會開始複製資料。
• replicationLagMaxSeconds 是執行個體層級的欄位。每個執行個體都有自己的值。

• 如果同一個主要執行個體有多個唯讀備用資源，您可以為每個備用資源的 replicationLagMaxSeconds 欄位設定不重複的值。

  為不同副本定義不同的時間門檻，有助於避免所有副本同時停止運作。

啟用重新建立落後副本功能

重新建立落後副本功能預設為停用。如要在建立執行個體時啟用這項功能，請使用下列其中一種方法：

gcloud beta sql instances create REPLICA_INSTANCE_NAME \
  --master-instance-name=PRIMARY_INSTANCE_NAME \
  --database-version=DATABASE_VERSION \
  --tier=TIER \
  --edition=EDITION \
  --region=REGION \
  --root-password=PASSWORD \
  --replication-lag-max-seconds-for-recreate=REPLICATION_LAG_MAX_SECONDS

{
  "settings": {
  "replicationLagMaxSeconds" :REPLICATION_LAG_MAX_SECONDS,
  }
  ...
}

更新複製延遲的重新建立時間範圍

如要查看執行個體的設定，請使用「查看執行個體摘要資訊」一節所述的任何方法。

有了這項資訊，您就能選擇是否要更新複製延遲時間範圍，也就是在重新建立副本前可接受的時間範圍。

gcloud beta sql instances patch INSTANCE_NAME \
  --replication-lag-max-seconds-for-recreate=REPLICATION_LAG_MAX_SECONDS

限制

重新建立延遲副本時，請注意下列限制：

• replicationLagMaxSeconds 的值只能以秒為單位設定。
• 在重建作業之前，讀取副本上建立的索引不會保留。如果索引存在，請在重建副本後建立次要索引。
• 為避免唯讀副本頻繁停機，每個執行個體每天只能重新建立一次。
• 這項功能不支援外部伺服器的副本。
• 如果您在連鎖備用資源上啟用重新建立落後備用資源的功能，Cloud SQL 會先重新建立葉節點備用資源，以維持複製作業的一致性。
• 重新建立跨區域備用資源會產生額外費用。
• 您無法在 Cloud de Confiance 控制台中啟用重新建立落後備用資源。

後續步驟：

• 推送備用資源，以進行區域性遷移或災難復原