在 Compute Engine 上設定 MySQL

Compute Engine 提供多種執行個體類型和儲存空間選項，可供您從 MySQL 資料庫讀取及寫入資料。為確保資料庫工作負載能發揮最佳效能並降低成本，建議您使用新一代基礎架構即服務 (IaaS) 產品。

下列設定建議考量到 MySQL 工作負載通常用於讀取量大的系統，例如線上交易處理 (OLTP) 或支援一般網頁應用程式的資料庫。這些最佳做法也考量了常見的設定選項，例如使用 MySQL 8.0 以上版本，以及使用 InnoDB 儲存引擎。對於講求效能的工作負載，您可能需要調整設定以符合需求。建議您先參考本指南做為部署的起點，然後使用實際工作負載進行測試，確認設定符合需求。

選擇虛擬機器 (VM)

對於 MySQL 工作負載，我們建議使用最新一代的 C 和 N 系列機器，因為這些系列包含的形狀適用於大多數實用的 MySQL 設定。如要瞭解這些機器系列，請參閱這篇Google Cloud 網誌文章。這些機器系列使用 Titanium，並以最新一代的 Intel、AMD 和 Axion 處理器為基礎。

著重效能

對於效能敏感型工作負載 (例如關鍵業務 MySQL 資料庫)，如果所在區域提供最新版 C4 和 C4A 執行個體，建議使用這些執行個體。如果無法存取，C3 和 C3D 執行個體也同樣著重效能。

這些執行個體可為運算量大的作業提供最低且最穩定的延遲時間，並包含下列實用功能，適合注重效能的工作負載：

  • 透過預先通知控管主機維護事件
  • 控制單核心渦輪加速，提升效能一致性
  • Tier_1 網路 提供更高網路頻寬

如果您使用 C4A、C3 或 C3D 執行個體，也可以使用本機固態硬碟 (本機 SSD) 滿足特定效能需求。

盡可能提高成本效益

如果工作負載的主要目標是盡量降低成本，例如流量偏低或中等的 MySQL 資料庫，或是用於測試或開發環境的資料庫，建議使用最新的 N4 執行個體。這些執行個體使用 Compute Engine 的新一代動態資源管理功能，可在維持穩定效能的同時，盡量降低總成本，但不會提供 C4、C4A、C3 和 C3D 提供的強效保證。詳情請參閱「新一代動態資源管理」。

設定 VM 大小

無論使用哪種 VM，請務必根據 MySQL 效能目標選擇適當的 VM 大小。

如果目標是達到高寫入交易數/秒 (TPS) 效能，主要考量因素是區塊儲存空間。詳情請參閱本頁下方的「設定區塊儲存空間」。

如果目標是達到高讀取查詢次數 (QPS) 效能，我們強烈建議您使用 MySQL 的 RAM 型緩衝區集區，快取熱門資料並減少磁碟存取次數。如要盡量發揮這些優勢，請按照下列步驟操作：

  • 選擇 VM 大小時，請確保工作集 (也就是資料庫一次處理的資料總量) 適合緩衝區集區。
  • 將緩衝區集區大小設為使用 VM 上的大部分 RAM。

為盡量減少這類 VM 大小調整的費用，建議使用 RAM 與虛擬 CPU (vCPU) 比例較高的 VM，以免支付您未使用的 vCPU 費用。

如要為大多數 MySQL 工作負載取得理想的平衡，請先判斷工作負載的工作集，然後選擇適合該工作集的最小 highmem 執行個體規格 (以 RAM 為單位)。highmem 執行個體規格的每個 vCPU 約有 8 GB 的 RAM。這樣一來，您就有足夠的記憶體來快取大型工作集，同時保留足夠的 CPU 來處理高查詢負載。

如果工作負載的工作集較大，但查詢率較低，使用 N4 執行個體時，您可以搭配自訂機器類型和擴充記憶體，進一步提高 RAM/vCPU 比率，藉此最佳化總費用。

設定 VM 的網路頻寬

對於大多數 MySQL 用途，您可以沿用執行個體的預設網路頻寬限制。如果符合需求，就不需要升級至 Tier_1 網路。

設定區塊儲存空間

Google Cloud Hyperdisk 是最新一代的耐用區塊儲存空間，適用於近期的 Compute Engine VM 系列。我們認為 Hyperdisk Balanced 最適合絕大多數的 MySQL 工作負載。如要進一步瞭解 Hyperdisk，請參閱 Hyperdisk 說明文件。

Google Cloud Hyperdisk

Hyperdisk Balanced 提供下列功能：

  • 低成本的固態硬碟 (SSD) 延遲
  • 為需要高效能的應用程式設定高效能
  • 高於 99.999% 的耐用性，可防範硬體故障和無聲資料損毀等全產業風險
  • 使用 Google 代管或客戶自行管理的加密金鑰，加密所有 Hyperdisk 靜態資料

選取成效等級

使用 Hyperdisk Balanced 時，您可以獨立選取磁碟的效能等級和儲存空間大小，因此能盡量提升資料庫效能，同時只須支付工作負載所需的輸入/輸出 (I/O) 資源費用。如果 MySQL 資料庫的緩衝區集區大於工作集，在穩定狀態作業期間，緩衝區集區幾乎可以處理所有讀取查詢，而無需存取磁碟。

如要為 Hyperdisk 磁碟區選取效能等級，請考量 MySQL 寫入工作負載，並特別注意下列事項：

  • 存取InnoDB重做記錄
  • 後續更新 InnoDB 資料檔案和索引

除了穩定狀態作業外，資料庫維護事件也可能導致磁碟效能大幅波動。這類事件的發生頻率通常會隨著資料庫的寫入工作負載而變化，因此在下列情況下較有可能發生：使用重做記錄或備份系統 (會讀取上次備份後的所有資料庫變更內容，藉此複製自身) 進行當機後復原。

調整磁碟大小

以下是三種常見的磁碟效能上限大小調整策略：

  1. 使用預設設定。每個磁碟至少都有每秒 3,000 次的輸入/輸出 (IOPS)，以及 140 MiBps 的處理量。這足以應付基本的 MySQL 工作負載和作業系統 (OS) 開機磁碟區。如果您的用途超出此範圍，可以視需要修改佈建的 I/O 效能，不必停止工作負載。
  2. 評估現有用量。如果資料庫已在其他環境中執行，請以一分鐘或更短的細微程度記錄其磁碟 IOPS 和總處理量。取得一到兩週的資料後 (樣本集應包含負載波動和正常維護事件)，請從該資料集中選取高百分位數值，並加入少量緩衝區，以因應自然成長或非預期用量。
  3. 先預估需求，之後再進行修改。如果沒有現有資料來源，您可能需要先預估效能需求，然後在部署後進一步調整。建議您先預先佈建高於初始需求的價值，以免工作負載遇到效能瓶頸，然後再視工作負載調整預先佈建的效能。

提升磁碟效能

每個 Hyperdisk Balanced 磁碟的效能最多可提高至 160,000 IOPS，總處理量則可提高至 2,400 MBps。VM 大小有助於判斷 Hyperdisk 的效能上限，因此如要獲得極高的 Hyperdisk 效能，可能需要增加 VM 的核心數量。如果需求最嚴苛的工作負載需要比單一 Hyperdisk Balanced 磁碟更高的磁碟效能，您可以使用下列其中一種方法，將多個 Hyperdisk Balanced 磁碟條紋化：

  • 升級至 Hyperdisk Extreme
  • 使用其他軟體容錯式磁碟陣列 (RAID) 機制，例如 mdadm

擴充 MySQL 資料庫時，您可以動態增加磁碟容量和效能，不必停機。這有助於提升線上分析處理 (OLAP) 樣式工作負載的效能，這些工作負載會執行大型複雜的聯結，無法放入 RAM 並溢出至磁碟。在極少數情況下，需要極低儲存空間延遲且可容許資料遺失的 MySQL 工作負載，可將完整資料集儲存在本機 SSD。您也可以使用下列混合式解決方案，縮短讀取延遲時間並限制耐久性降低：

  • 在 Hyperdisk 和本機 SSD 之間鏡像處理資料集。
  • 使用磁碟區管理工具，將本機 SSD 設定為儲存在基礎 Hyperdisk 的資料快取。

善用其他 Hyperdisk 功能

Hyperdisk 也提供下列功能，可擴增或簡化地端部署的高可用性和災難復原工作流程：

如要進一步瞭解如何使用 Compute Engine 專用的 MySQL 設定這些功能，請參閱本頁面下方的高可用性一節。

本機 SSD

部分 Compute Engine 機型系列可讓您使用本機 SSD，而非 Hyperdisk。這類儲存空間不具備耐久性，但 MySQL 工作負載通常會用來儲存暫時性表空間。

如要瞭解如何使用本機 SSD 擴充 MySQL 資料庫，請參閱本頁的「動態調整磁碟大小」一節。

其他 Compute Engine 功能

您可以運用下列 Compute Engine 功能，協助最佳化 MySQL 部署作業。

Cloud Monitoring

如要監控 VM 的效能和基礎架構服務用量，請使用Google Cloud 控制台。在「VM Instances」(VM 執行個體) 頁面的「Observability」(可觀測性) 分頁中，您可以監控 CPU 和記憶體使用率、網路頻寬，以及執行個體佈建效能等效能相關指標。同樣地，在「Disks」(磁碟) 頁面的「Observability」(可觀測性) 分頁中，您可以監控磁碟區的總處理量和 IOPS。

如要自訂顯示的效能指標，請使用 Cloud Monitoring 建構查詢。您可以選取要查看基礎架構服務的特定效能指標。如需 MySQL 特定指標，Compute Engine 提供 MySQL 工作負載外掛程式。

設定作業系統的最佳做法

  • 使用適當的檔案系統。Google 著重於為 Linux 的 ext4 和 XFS 檔案系統進行最佳化，但大多數檔案系統都適合搭配 MySQL 使用。
  • 在基礎作業系統設定中關閉透明巨頁 (THP)。如要瞭解如何關閉 THP，請參閱 THP 說明文件。
  • 如果您使用 Linux，請使用 relatime 和 lazytime 標記，設定檔案系統掛接。這樣做可減少更新檔案的 atime、mtime 和 ctime 值時，因讀取、修改檔案或變更檔案中繼資料而造成的效能負擔。

設定 MySQL 的最佳做法

建議您使用下列 MySQL 設定。

  • 使用最新版本的 MySQL。Google 致力於為 MySQL 8.0 以上版本提供最佳化服務。

  • 增加緩衝區集區的大小。MySQL 會使用緩衝區集區，在 RAM 中快取資料，減少磁碟存取次數，進而提升讀取效能。根據預設，MySQL 的緩衝區集區大小為 128 MiB，對大多數實際用途而言太小。建議您增加 innodb_buffer_pool_size 的大小，使其大於應用程式在資料庫中存取的工作集大小。這通常包含下列步驟：

    1. 在 MySQL 執行個體的執行副本上，測量或估算工作集的規模。
    2. 選擇虛擬機器 (VM) 大小和形狀，並確保有足夠的 RAM 來容納該工作集。
    3. 在 VM 上設定緩衝區集區大小，以佔用大部分可用 RAM。

  • 開啟雙重寫入緩衝區。MySQL 具有雙重寫入緩衝區，可防範寫入中斷。如果寫入作業涵蓋磁碟上的多個區塊，但寫入期間發生硬體或電源故障，可能只會部分提交。如要享有這項保護措施，請開啟 innodb_doublewrite。

  • 將 innodb_flush_log_at_trx_commit 的值設為 1。這樣可確保寫入交易在提交時，會持久儲存在磁碟上。

  • 為減少效能負擔，請為 innodb_flush_method 指定值。 如果是 MySQL 8.0.14 以上版本，請將 innodb_flush_method 的值設為 O_DIRECT_NO_FSYNC，這是最佳做法，但只有這些版本提供此值。如果是 MySQL 8.0.14 之前的版本，請將 innodb_flush_method 的值設為 O_DIRECT。

  • 在高可用性複製情境中，請將主要資料庫執行個體的 sync_binlog 值設為 1。MySQL 會使用二進位記錄檔，將主要資料庫的變更傳達至次要資料庫，因此這項設定可確保二進位記錄檔會在交易提交時一併提交，盡可能縮短資料庫之間的複製延遲時間和復原點目標 (RPO)。

  • 在 C 系列機器系列上使用 MySQL 時，請開啟 innodb_numa_interleave。 這可確保 MySQL 的緩衝區集區能善用非一致性記憶體存取 (NUMA) 政策。

何時關閉雙重寫入緩衝區

MySQL 的雙重寫入緩衝區可防止寫入作業中斷，但 MySQL 寫入交易的效能負擔最多會增加 25%，交易延遲時間也可能變長。Google Cloud Hyperdisk 提供內建的寫入中斷保護機制。如果您使用 MySQL 直接寫入在 Hyperdisk 上執行的 ext4 檔案系統，只要正確設定檔案系統和中介軟體層，即可關閉雙重寫入緩衝區。

如要有效保護 Hyperdisk 免於寫入中斷，您必須設定檔案系統，以及資料庫和磁碟之間的其他中介軟體層，避免在磁碟層上方發生寫入中斷。以下列舉可能在 Hyperdisk 層上方發生寫入中斷的設定：

  • 在容器中執行 MySQL 執行個體，包括 Google Kubernetes Engine 或自行代管的 Kubernetes。
  • 將 MySQL 檔案儲存在 XFS 檔案系統中，但大多數 Linux 核心設定都不支援足夠大的區塊大小。
  • 將 MySQL 檔案儲存在獨立磁碟冗餘陣列 (RAID) 設定中，導致磁碟條帶化，包括 Linux 的 mdadm，以及 Windows 的「儲存空間」和「儲存空間直接存取」。
  • 在磁碟區管理工具 (包括 Linux 的邏輯磁碟區管理工具 (LVM)，或 Windows 的儲存空間和儲存空間直接存取) 上儲存 MySQL 檔案。

  • 在 Hyperdisk 上儲存 MySQL 檔案，並將本機固態硬碟 (SSD) 設定為快取，使用 Linux 的 lvmcache、dm-cache 或 bcache，或是 Windows 的儲存空間。

  • 使用巢狀虛擬化功能，在 VM 中執行 MySQL 執行個體。

雖然您可以設定上述設定，避免出現寫入撕裂問題，但我們不建議在使用這些設定時關閉雙重寫入緩衝區，因為驗證特定設定是否安全並不容易。

(選用) 關閉雙重寫入緩衝區

如要關閉雙重寫入緩衝區，請完成下列步驟：

  1. 在 ext4 檔案系統中，您必須啟用 bigalloc 功能，並將檔案系統的叢集大小設為 16KiB，或大於 16KiB 的 2 的倍數。這樣可確保 MySQL 的寫入作業不會在發送至 Hyperdisk 前，被檔案系統分割成個別的 IO。如果未提高限制，或使用小於 16KiB 的值，就無法防範寫入作業中斷。以叢集大小為 16KiB 為例，這是在建立檔案系統時設定：

    mkfs.ext4 -O bigalloc -C 16384 /dev/<device-name>

  2. 停用 innodb_doublewrite，並將 innodb_flush_method 設為 O_DIRECT 或 O_DIRECT_NO_FSYNC (視您的 MySQL 版本而定，如上所述)。

設定高可用性 (HA) 和備份解決方案

強烈建議您為所有重要的 MySQL 工作負載設定高可用性 (HA) 和備份解決方案，藉此建立防護機制。無論是高可用性還是備份，最重要的因素如下：

高可用性解決方案通常以接近零的復原時間目標和復原點目標為目標，但僅防範基礎架構故障。備份解決方案的復原時間目標和復原點目標時間範圍較長，但涵蓋範圍較廣，可因應更多故障情況，例如：

  • 意外刪除資料
  • 勒索軟體攻擊
  • 天災

設定高可用性 (HA)

高可用性功能會使用儲存空間和運算資源備援，確保 MySQL 資料庫在主機故障或服務中斷時，能縮短停機時間，讓用戶端應用程式即使在執行個體或可用區無法使用時，也能存取資料。

MySQL 允許下列模式的複製作業：

  • 非同步模式：在非同步模式下，主要節點會在寫入交易提交至本機後立即確認，因此如果主要節點發生服務中斷，容錯移轉期間可能會遺失少量最近寫入的資料，因為 RPO 接近零，但並非實際為零。
  • 半同步模式。在半同步模式下，主要伺服器會等待可設定數量的副本確認收到交易後，才會確認交易。這能大幅降低非預期容錯移轉期間發生資料遺失的機率，因為 RPO 實際上為零。

無論是哪種模式，RTO 都是根據健康狀態檢查執行下列動作的速度而定：

  1. 找出失敗的執行個體。
  2. 觸發容錯移轉。
  3. 使用網域名稱系統 (DNS) 或其他識別資料庫伺服器的方式，通知用戶端容錯移轉執行個體現在是主要執行個體。

無論使用哪種複寫模式，都必須有容錯移轉執行個體，才能進行複寫。您可以在下列任一位置找到該執行個體：

  • 與主要執行個體位於相同區域
  • 與主要節點位於同一區域的不同可用區
  • 與主要區域不同的區域

為確保即使在可用區中斷期間仍能維持高可用性，建議採用下列設定：

  • 在不同區域中找出主要和容錯移轉執行個體，無論這些執行個體是否位於同一地區。
  • 使用非同步複製功能。這是因為如果您使用半同步複製，將主要和容錯移轉執行個體放在不同區域，可能會導致寫入交易提交作業的延遲時間過長。
  • 如要實現零 RPO，請使用 Hyperdisk Balanced High Availability，這項功能可讓您在相同區域的兩個可用區中同步複製磁碟資料。詳情請參閱 Google 的指南，瞭解如何使用 Hyperdisk 高可用性提供高可用性服務。設定 Hyperdisk Balanced 高可用性時，建議您整合具備狀態的代管執行個體群組，診斷執行個體健康狀態問題並自動執行復原動作。

設定備份和資料復原計畫

備份和資料復原計畫有助於防範資料遺失，例如意外刪除資料、遭到勒索軟體攻擊和發生天災時。您也可以將這些資料當做冷儲存空間，以符合法規和稽核要求。MySQL 有許多備份方法可供選擇，有些方法適用於資料庫層級，有些則適用於儲存空間磁碟區層級。選擇方法時，主要考量因素是 RTO 和 RPO 需求。

在資料庫層級備份

如要進行資料庫層級的備份，請考慮使用 MySQL 提供的下列選項：

如要進一步瞭解 MySQL 的資料庫層級備份選項，請參閱 MySQL 說明文件中的「備份與復原」。

無論選擇哪種方式，都必須有次要儲存系統，才能將備份資料複製到該系統。我們建議使用下列工具：

使用 Hyperdisk 在儲存空間層級建立快照和複製

如要進行儲存空間層級的備份，建議使用 Hyperdisk 產品建立快照、複製及擷取 MySQL 資料庫的時間點檢視畫面。這個方法的 RPO 取決於資料庫快照的建立頻率，而 RTO 則取決於您使用的具體解決方案。

如果您重視快速復原，且只需要可用區內的備份涵蓋範圍，建議使用 Hyperdisk 的即時快照。即時快照會以遞增方式擷取特定時間點的資料，並透過磁碟複製將資料快速還原至新的 Hyperdisk 磁碟區，提供幾分鐘的復原時間目標 (RTO)。這類快照可讓您在磁碟內容遭到覆寫、刪除或損毀時復原資料，且可從來源磁碟所在的相同可用區或區域存取。詳情請參閱「關於即時快照」一文。

在災難復原情境中，資料必須以高於原始磁碟的備援程度儲存，且必須儲存在不同位置，確保單一災難不會影響所有資料副本，因此建議使用 Hyperdisk 的封存和標準磁碟快照。封存和標準磁碟快照會建立磁碟在特定時間點的資料副本，並以不可變更的格式儲存，確保資料高度備援。如果您建立磁碟的多個快照 (例如使用快照排程)，Hyperdisk 只會儲存增量變更。如果您可以容許較高的 RTO，封存和標準磁碟快照就很適合，因為從快照儲存空間將資料複製回 VM 儲存空間可能需要較長的時間，因此還原時間也會較長。詳情請參閱「建立封存和標準磁碟快照」一文。

Hyperdisk 的即時快照，以及封存和標準快照，在單一磁碟中都具有當機一致性。這表示從快照還原時，MySQL 資料庫必須執行正常的 InnoDB 復原步驟，才能將記錄檔和資料檔案還原至一致狀態。視 InnoDB 重做記錄的設定而定，這可能會延長 RTO。下列模式可能會進一步增加建立一致資料庫快照的難度：

  • MySQL 資料庫檔案分散在多個磁碟區。
  • 您使用的是 Linux 軟體 RAID 公用程式，例如 mdadm。
  • 您已將 MySQL 設定的儲存空間位置，分散到不同磁碟上的檔案系統。

如要建立不需要在還原快照後復原的快照，請完成下列步驟：

  1. 暫時鎖定 MySQL 資料庫的寫入權限。
  2. 使用 LOCK INSTANCE FOR BACKUP 和 FLUSH TABLES WITH READ LOCK 指令，將所有進行中的緩衝區排清至磁碟。
  3. 啟動快照作業。

  4. 在多重磁碟情境中，於 MySQL 層級排清後，請在伺服器上執行 sync 和 fsfreeze 指令，將所有進行中的寫入作業排清至磁碟，並在檔案系統層級暫停新的寫入作業。

擷取資料庫的初始快照後，您不需要繼續鎖定磁碟，因為 Hyperdisk 會快速擷取時間點檢視畫面，然後非同步處理後續的任何儲存空間複製步驟。如需這些步驟來確保快照一致性，且想移除對主要資料庫的寫入影響，您也可以對資料庫副本執行備份，而非主要資料庫。

後續步驟