Aurora MySQL 中 MySQL 功能的建議 - Amazon Aurora
在 Aurora MySQL 中使用多執行緒複寫使用原生 MySQL 函數叫用 AWS Lambda 函數避免搭配 Amazon Aurora MySQL 使用 XA 交易DML 陳述式期間保持外部索引鍵的開啟狀態設定日誌緩衝區的排清頻率減少和疑難排解 Aurora MySQL 的死結情況

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

Aurora MySQL 中 MySQL 功能的建議

以下功能可在與 MySQL 相容的 Aurora MySQL 中使用。但是，這些功能在 Aurora 環境中存在效能、可擴展性、穩定性或相容性的問題。因此，建議您在使用這些功能時遵循某些指導方針。例如，建議您不要將特定功能用於生產 Aurora 部署。

在 Aurora MySQL 中使用多執行緒複寫

使用多執行緒二進位日誌複寫，SQL 執行緒會從轉送日誌讀取事件，並將它們排入佇列，以供 SQL 工作者執行緒套用。SQL 工作者執行緒是由協調器執行緒管理。可能的話，會平行套用二進位日誌事件。

Aurora MySQL 第 3 版和 Aurora MySQL 2.12.1 版及更新版本支援多執行緒複寫。

對於低於 3.04 版 Aurora MySQL，當您將 Aurora MySQL 資料庫叢集用來做為二進位日誌複寫的讀取複本時，Aurora 預設會使用單一執行緒複寫。

Aurora MySQL 第 2 版的早期版本已從 MySQL Community Edition 繼承數個有關多執行緒複寫的問題。對於這些版本，建議您不要在生產中使用多執行緒複寫。

如果您要使用多執行緒複寫，建議您徹底進行測試。

如需在 Amazon Aurora 中使用複寫的詳細資訊，請參閱以 Amazon Aurora 進行複寫。如需 Aurora MySQL 中多執行緒複寫的詳細資訊，請參閱 多執行緒二進位日誌複寫。

使用原生 MySQL 函數叫用 AWS Lambda 函數

建議您使用原生 MySQL 函數 lambda_sync 和 lambda_async，以叫用 Lambda 函數。

如果您使用已棄用的 mysql.lambda_async 程序，建議在預存程序中包裝對 mysql.lambda_async 程序的呼叫。您可以從不同的來源 (例如觸發程式或用戶端程式碼) 呼叫此預存程序。此方法有助於避免阻抗不符問題，並讓您的資料庫程式設計人員能夠輕鬆呼叫 Lambda 函數。

如需從 Amazon Aurora 叫用 Lambda 函數的詳細資訊，請參閱從 Amazon Aurora MySQL 資料庫叢集叫用 Lambda 函式。

避免搭配 Amazon Aurora MySQL 使用 XA 交易

建議您不要使用 eXtended Architecture (XA) 交易搭配 Aurora MySQL，因為如果 XA 處於 PREPARED 狀態，它們可能造成過長的復原時間。如果您必須使用 XA 交易搭配 Aurora MySQL，請遵循這些最佳實務：

  • 請勿讓 XA 交易保持開啟在 PREPARED 狀態。

  • 盡可能讓 XA 交易越小越好。

如需使用 XA 交易搭配 MySQL 的詳細資訊，請參閱 MySQL 文件中的 XA 交易。

DML 陳述式期間保持外部索引鍵的開啟狀態

在將 foreign_key_checks 變數設為 0 (關閉) 時，強烈建議您不要執行任何資料定義語言 (DDL) 陳述式。

如果您需要插入或更新需要暫時違反外部索引鍵的資料列，請遵循這些步驟：

  1. 將 foreign_key_checks 設定為 0。

  2. 進行您的資料處理語言 (DML) 變更。

  3. 確定您完成的變更未違反任何外部索引鍵限制。

  4. 將 foreign_key_checks 設為 1 (開啟)。

此外，遵循用於外部索引鍵限制的這些其他最佳實務：

  • 確定您的用戶端應用程式未隨著 foreign_key_checks 變數將 0 變數設為 init_connect。

  • 如果從邏輯備份 (例如 mysqldump) 的還原失敗或未完成，在相同工作階段中開始任何其他操作之前，請確定將 foreign_key_checks 設為 1。邏輯備份會在開始時將 foreign_key_checks 設為 0。

設定日誌緩衝區的排清頻率

在 MySQL Community Edition 中，若要使交易可以耐久，InnoDB 日誌緩衝區必須排清到耐久儲存。您可以使用 innodb_flush_log_at_trx_commit 參數來設定日誌緩衝區排清到磁碟的頻率。

當您將 innodb_flush_log_at_trx_commit 參數設為預設值 1 時，日誌緩衝區會在每次交易認可時進行排清。此設定有助於保持資料庫 ACID 合規。建議您保留預設設定 1。

將 innodb_flush_log_at_trx_commit 變更為非預設值可協助減少資料操作語言 (DML) 延遲，但會犧牲日誌記錄的耐久性。一旦缺乏耐久性，就會使資料庫 ACID 不合規。建議您的資料庫必須是 ACID 合規，以避免在伺服器重新啟動時發生資料遺失的風險。如需此參數的詳細資訊，請參閱 MySQL 文件中的 innodb_flush_log_at_trx_commit。

在 Aurora MySQL 中，重做日誌處理會卸載至儲存層，因此資料庫執行個體上不會排清至日誌檔。發出寫入時，重做日誌會從寫入器資料庫執行個體直接傳送至 Aurora 叢集磁碟區。唯一跨網路的寫入是重做日誌記錄。始終不會從資料庫層寫入任何頁面。

根據預設，每個認可交易的執行緒都會等待 Aurora 叢集磁碟區的確認。此確認指出已寫入此記錄和所有先前的重做日誌記錄，都已寫入並達到仲裁。不論是透過自動認可還是明確認可，持續保留日誌記錄並達到仲裁，都能使交易耐久。如需 Aurora 儲存架構的詳細資訊，請參閱 Amazon Aurora 儲存解密。

Aurora MySQL 不會如 MySQL Community Edition 一樣將日誌排清到資料檔案。不過，在將重做日誌記錄寫入至 Aurora 叢集磁碟區時，您可以使用 innodb_flush_log_at_trx_commit 參數來放鬆耐久性限制。

適用於 Aurora MySQL 第 2 版：

  • innodb_flush_log_at_trx_commit = 0 或 2 – 資料庫不會等待重做日誌記錄寫入 Aurora 叢集磁碟區的確認。

  • innodb_flush_log_at_trx_commit = 1 – 資料庫會等待確認重做日誌記錄寫入 Aurora 叢集磁碟區。

適用於 Aurora MySQL 第 3 版：

  • innodb_flush_log_at_trx_commit = 0 – 資料庫不會等待確認重做日誌記錄寫入 Aurora 叢集磁碟區。

  • innodb_flush_log_at_trx_commit = 1 或 2 – 資料庫會等待確認重做日誌記錄寫入 Aurora 叢集磁碟區。

因此，若要取得 Aurora MySQL 第 3 版中與 Aurora MySQL 第 2 版中值設定為 0 或 2 相同的非預設行為，請將參數設定為 0。

雖然這些設定可以降低用戶端的 DML 延遲，但在發生容錯移轉或重新啟動時，也可能導致資料遺失。因此，建議您將 innodb_flush_log_at_trx_commit 參數持續設定為預設值 1。

雖然 MySQL Community Edition 和 Aurora MySQL 都可能發生資料遺失，但由於其架構不同，每個資料庫的行為都會有所不同。這些架構差異可能會導致不同程度的資料遺失。若要確保您的資料庫是 ACID 合規，請將 innodb_flush_log_at_trx_commit 一律設定為 1。

注意

在 Aurora MySQL 第 3 版中，在您將 innodb_flush_log_at_trx_commit 變更為 1 以外的值之前，將您必須先將 innodb_trx_commit_allow_data_loss 的值變更為 1。如此一來，您就會確認資料遺失的風險。

減少和疑難排解 Aurora MySQL 的死結情況

在相同資料頁面上並行修改記錄時，執行在唯一次要索引或外部索引鍵上經常遇到限制條件違規之工作負載的使用者，可能會遇到更多的死結情況和鎖定等待逾時。這些死結情況和逾時皆源自 MySQL Community Edition 錯誤修正。

此修正包含在 MySQL Community Edition 5.7.26 及更新版本中，並已向後移植至 Aurora MySQL 2.10.3 及更新版本。強制序列化必須有此修正，方法是在 InnoDB 資料表的記錄變更上，針對這些資料處理語言 (DML) 類型執行額外鎖定。此問題是調查先前 MySQL Community Edition 錯誤修正導致的死結問題時發現的。

該項修正變更了 InnoDB 儲存引擎中，元組 (資料列) 更新的部分回復內部處理。在外部索引鍵或唯一次要索引產生限制條件違規的操作會導致部分回復。這包括但不限於並行 INSERT...ON DUPLICATE KEY UPDATE、REPLACE INTO, 和 INSERT IGNORE 陳述式 (upserts)。

在此情況下，部分回復並非應用程式層級交易的回復，而是發生限制條件違規時，對叢集索引的內部 InnoDB 回復變更。例如，在 upsert 操作期間找到重複索引鍵值。

在正常的插入操作中，InnoDB 會自動為每個索引建立叢集和次要索引項目。如果 InnoDB 在 upsert 操作期間偵測到唯一次要索引上的重複值，則叢集索引中插入的項目必須回復 (部分回復)，並將更新套用至現有的重複列。在此內部部分回復步驟中，InnoDB 必須在操作中鎖定每個記錄。此修正會在部分回復之後引入額外鎖定，以確保交易序列化。

減少 InnoDB 死結情況

您可以採取下列方法來減少資料庫執行個體的死結情況發生頻率。MySQL 文件中有更多範例。

  1. 為了減少死結情況發生，請在進行一系列相關變更後立即遞交交易。您可以將大型交易 (遞交之間的多列更新) 拆解為較小交易來執行此動作。若您要批次插入行，請盡量減少批次插入的大小，尤其是使用上述提及的 upsert 操作時。

    若要減少部分回復的次數，您可以嘗試下列方法：

    1. 一次插入一行，而非進行批次插入操作。如此便能減少可能發生衝突之交易的鎖定時間。

    2. 請不要使用 REPLACE INTO，而是將 SQL 陳述式以多陳述式交易重新寫入，如下所示：

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. 請不要使用 INSERT...ON DUPLICATE KEY UPDATE，而是將 SQL 陳述式以多陳述式交易重新寫入，如下所示：

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. 避免長時間執行且可能導致鎖定的作用中或閒置交易。其中包括互動式 MySQL 用戶端工作階段，這些工作階段可能會在未遞交的交易中長時間開啟。最佳化交易大小或批次大小時，影響會因並行、重複項目數量和資料表結構等因素而有所不同。任何變更都應根據您的工作負載來執行和測試。

  3. 在部分情況下，當兩筆交易嘗試以不同順序存取相同的資料集時，不論資料集是否位於同一個資料表，皆可能發生死結情況。為了防止這種情況，您可以修改交易以相同順序存取資料，進而序列化該存取。例如，建立待完成的交易佇列。當多個交易並行發生時，此方法便能協助避免死結情況。

  4. 將謹慎選擇的索引新增到資料表中，可改善選取性並減少存取資料列的需求，從而減少死結情況。

  5. 若發生間隙鎖定，您可以將工作階段或交易的交易隔離層級修改為 READ COMMITTED，以避免發生間隙鎖定。如需詳細了解 InnoDB 隔離層級和其行為，請參閱 MySQL 文件的交易隔離層級。

注意

雖然您可以採取預防措施來減少死結情況，但死結情況是可預期的資料庫行為，仍然可能發生。應用程式應具有必要的邏輯來處理死結情況。例如，在應用程式中執行重試和停止邏輯。最理想的是解決問題根本原因，但是若確實發生死結情況，應用程式仍可以選擇等待並重試。

監控 InnoDB 死結情況

應用程式交易嘗試以導致循環等待的方式取得資料表層級和資料列層級鎖定時，MySQL 中可能發生死結。偶爾的 InnoDB 死結情況不一定是大問題，因為 InnoDB 儲存引擎會立即偵測到狀況，並自動復原交易。若您經常遇到死結情況，建議您檢閱和修改應用程式，以減緩效能問題並避免死結發生。當死結偵測開啟 (預設值) 時，InnoDB 會自動偵測交易死結，並復原交易以解決死結情況。InnoDB 嘗試選擇要復原的小型交易，而交易的大小是由插入、更新或刪除的資料列數而定。

  • SHOW ENGINE 陳述式 – SHOW ENGINE INNODB STATUS \G 陳述式包含自上次重啟以來，資料庫最近遇到的死結詳細資訊。

  • MySQL 錯誤日誌 – 若您頻繁遇到死結，而 SHOW ENGINE 陳述式的輸出不敷使用，您可以開啟 innodb_print_all_deadlocks 資料庫叢集參數。

    開啟此參數時，InnoDB 使用者交易中所有死結的相關資訊皆會記錄在 Aurora MySQL 錯誤日誌。

  • Amazon CloudWatch 指標 – 我們也建議使用 CloudWatch 指標 Deadlocks 主動監控死結情況。如需更多詳細資訊，請參閱 Amazon Aurora 的執行個體層級指標。

  • Amazon CloudWatch Logs – 透過 CloudWatch Logs，您可以檢視指標、分析日誌資料，並建立即時警示。如需更多詳細資訊，請參閱使用 Amazon CloudWatch 監控 Amazon Aurora MySQL 和 Amazon RDS for MySQL 的錯誤，並透過 Amazon SNS 傳送通知。

    若在 innodb_print_all_deadlocks 開啟的情況下使用 CloudWatch Logs，您可以設定警示，以便在死結數量超過指定閾值時通知您。若要定義閾值，建議您觀察平時情況，根據您的正常工作負載來定義該值。

  • 績效詳情 — 當您使用績效詳情時，您可以監控 innodb_deadlocks 和 innodb_lock_wait_timeout 指標。如需這些指標的詳細資訊，請參閱 Aurora MySQL 的非原生計數器。