MariaDB 的匯入資料考量事項 - Amazon Relational Database Service
二進位記錄交易大小InnoDB資料匯入格式將 Amazon RDS 資料庫快照用於資料庫檢查點縮短資料庫載入時間

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

MariaDB 的匯入資料考量事項

下列內容包含有關將資料載入 MariaDB 的技術資訊。此內容的目標是熟悉 MariaDB 伺服器架構的使用者。

二進位記錄

啟用二進位記錄可能會降低資料載入效能，且相較於停用記錄，最多需要四倍額外的磁碟空間。用來載入資料的交易大小會直接影響到系統效能和磁碟空間需求 - 交易越大，需要的資源越多。

交易大小

交易大小會影響 MariaDB 資料載入的下列層面：

  • 資源耗用

  • 磁碟空間使用率

  • 繼續程序

  • 復原的時間

  • 輸入格式 (一般檔案或 SQL)

本節會說明交易大小如何影響二進位日誌，並解釋為何要在大型資料載入期間停用此功能。您可以設定 Amazon RDS 自動備份保留期間，藉此啟用及停用二進位記錄。非零的數值會啟用二進位日誌，而零則會停用此功能。如需詳細資訊，請參閱Backup retention period (備份保留期間)。

本節也說明大型交易對 InnoDB 的影響，以及需要盡量縮減交易大小的原因。

小型交易

若為小型交易，二進位日誌會將載入資料所需的磁碟寫入次數加倍。此效果會嚴重降低其他資料庫作業階段的效能，並增加載入資料所需的時間。經歷的降級部分取決於下列因素：

  • 上傳速率

  • 載入期間發生的其他資料庫活動

  • Amazon RDS 資料庫執行個體的容量

二進位日誌在完成備份並移除前，也會佔用與載入的資料量約莫相等的磁碟空間。Amazon RDS 會頻繁備份並移除二進位日誌，盡可能避免這種情況。

大型交易

對於大型交易，基於下列原因，二進位記錄會使 IOPS 和磁碟用量變成三倍：

  • 二進位日誌快取會將交易資料暫時存放在磁碟上。

  • 此快取會隨著交易大小而增加，這會耗用磁碟空間。

  • 當交易 (遞交或復原) 完成時，系統會將快取複製到二進位日誌。

此程序會建立三個資料複本：

  • 原始資料

  • 磁碟上的快取

  • 最終二進位日誌項目

每個寫入操作都會產生額外的 IO，進一步影響效能。

因此，相較於停用記錄，二進位記錄需要三倍的磁碟空間。例如，將 10 GiB 的資料載入為單一交易，會建立三個複本：

  • 10 GiB 用於資料表資料

  • 10 GiB 用於二進位日誌快取

  • 10 GiB 用於二進位日誌檔案

所需的暫存磁碟空間總計為 30 GiB。

重要磁碟空間考量：

  • 快取檔案會持續存在，直到工作階段結束或新的交易建立另一個快取為止。

  • 二進位日誌會保留到備份為止，可能會長時間保留 20 GiB (快取和日誌)。

如果您使用 LOAD DATA LOCAL INFILE 載入資料，則資料復原會建立第四個複本，以防必須從載入之前進行的備份中復原資料庫。復原期間，MariaDB 會擷取二進位日誌的資料，放入一般檔案中。接著，MariaDB 會執行 LOAD DATA LOCAL INFILE。根據上述範例，此復原需要 40 GiB 的總暫存磁碟空間，或是資料表、快取、日誌和本機檔案各 10 GiB。如果沒有至少 40 GiB 的可用磁碟空間，則復原會失敗。

最佳化大型資料負載

對於大型資料載入，請停用二進位記錄，以降低額外負荷和磁碟空間需求。您可以將備份保留期間設定為 0，以停用二進位記錄。載入完成後，請將備份保留期間還原至適當的非零值。如需詳細資訊，請參閱 修改 Amazon RDS 資料庫執行個體 和設定資料表中的備份保留期間。

注意

如果資料庫執行個體是僅供讀取複本的來源資料庫執行個體，則不可將備份保留期間設為 0。

載入資料之前，建議您先建立資料庫快照。如需詳細資訊，請參閱管理手動備份。

InnoDB

下列復原記錄和復原選項的相關資訊支援將 InnoDB 交易保持為小型，以最佳化資料庫效能。

了解 InnoDB 復原記錄

復原是一種記錄機制，可啟用交易轉返，並支援多版本並行控制 (MVCC)。

對於 MariaDB 10.11 和更低版本，復原日誌存放在 InnoDB 系統資料表空間 (通常是 ibdata1) 中並予以保留，直到清除執行緒將其移除為止。因此，大型資料載入交易可能會造成系統資料工作表變得無比龐大並佔用磁碟空間，且若未重新重建資料庫，將無法回收這些空間。

對於所有 MariaDB 版本，清除執行緒必須等待移除任何復原日誌，直到最舊的作用中交易遞交或復原為止。如果資料庫在載入期間同時處理其他交易，則即使這些交易皆已遞交，且沒有其他交易需要因為 MVCC 而復原，這些復原也會累積在系統資料表空間中，並且無法移除。在這種情況下，所有交易 (包括唯讀交易) 都會變慢。此速度變慢是因為所有交易都會存取任何交易 (而不只是載入交易) 變更的所有資料列。實際上，交易必須掃描因長時間執行的載入交易而無法在復原日誌清除期間遭到清除的復原日誌。這會影響到任何存取已修改資料列的操作效能。

InnoDB 交易復原選項

雖然 InnoDB 會最佳化提交操作，但大型交易復原速度緩慢。為了加快復原速度，請執行時間點復原或還原資料庫快照。如需詳細資訊，請參閱時間點復原及還原至資料庫執行個體。

資料匯入格式

MariaDB 支援兩種資料匯入格式：一般檔案和 SQL。檢閱每個格式的相關資訊，以判斷符合您需求的最佳選項。

一般檔案

對於小型交易，請使用 LOAD DATA LOCAL INFILE 載入一般檔案。相較於使用 SQL，此資料匯入格式可提供下列優點：

  • 較少網路流量

  • 降低資料傳輸成本

  • 減少資料庫處理開銷

  • 更快的處理速度

LOAD DATA LOCAL INFILE 會將整個一般檔案視為單一交易來載入。將個別檔案的大小保持為小型，以獲得下列優點：

  • 繼續執行功能 – 輕鬆追蹤哪些檔案已載入完成。如果載入期間發生問題，您可以從中止的地方繼續工作。有些資料可能需要重新傳輸到 Amazon RDS，但若使用小型檔案，要重新傳輸的內容就可以減至最少。

  • 平行資料載入 – 如果您有足夠的 IOPS 和網路頻寬進行單一檔案載入，則平行載入可以節省時間。

  • 負載率控制 – 如果您的資料負載對其他程序造成負面影響，您可以透過增加檔案之間的間隔來控制負載率。

大型交易會減少使用 LOAD DATA LOCAL INFILE 匯入資料的優點。當您無法將大量資料分解為較小型的檔案時，請考慮使用 SQL。

SQL

SQL 具備一項一般檔案所缺乏的主要優點，亦即您可輕鬆將交易大小維持在較小的狀態。不過，SQL 載入的時間可能會較一般檔案長得多。此外，在失敗之後，很難判斷要繼續的位置，您無法重新啟動 mariadb-dump 檔案。如果在載入 mariadb-dump 檔案時發生錯誤，您必須修改或替換檔案，才能繼續載入檔案。或者，替代方案是在修正失敗原因後，還原至載入前的時間點，然後重新傳送檔案。如需詳細資訊，請參閱時間點復原。

將 Amazon RDS 資料庫快照用於資料庫檢查點

如果您載入資料的時間很長 (例如數小時或數天) 而沒有二進位記錄，請使用資料庫快照來提供資料安全的定期檢查點。每個資料庫快照都會建立資料庫執行個體的一致複本，以做為系統故障或資料損毀事件期間的復原點。由於資料庫快照速度很快，經常使用檢查點對負載效能的影響極小。您可以刪除先前的資料庫快照，而不會影響資料庫耐久性或復原功能。如需資料庫快照的詳細資訊，請參閱 管理手動備份。

縮短資料庫載入時間

以下是縮短載入時間的其他秘訣：

  • 在將資料載入 MariaDB 資料庫之前，先建立所有次要索引。與其他資料庫系統不同，MariaDB 會在新增或修改次要索引時，重建整個資料表。此程序會建立新的資料表，其中包含索引變更、複製所有資料，以及捨棄原始資料表。

  • 依主索引鍵順序載入資料。對於 InnoDB 資料表，這可以縮短載入時間達 75%–80%，並將資料檔案大小減少 50%。

  • 將 foreign_key_checks 設定為 0 來停用外部索引鍵限制條件。對於使用 LOAD DATA LOCAL INFILE 載入的一般檔案，這通常是必要作業。對於任何載入，停用外部索引鍵檢查可加速資料載入。載入完成後，將 foreign_key_checks 設定為 1 並驗證資料，以重新啟用限制條件。

  • 除非接近資源限制，否則請平行載入資料。若要啟用跨多個資料表區段的並行載入，請適時使用分割的資料表。

  • 若要降低 SQL 執行負荷，請將多個 INSERT 陳述式合併為單一多值 INSERT 作業。mariadb-dump 會自動實作此最佳化。

  • 將 innodb_flush_log_at_trx_commit 設定為 0，以減少 InnoDB 日誌 IO 操作。載入完成後，將 innodb_flush_log_at_trx_commit 還原至 1。

    警告

    將 innodb_flush_log_at_trx_commit 設定為 0 會導致 InnoDB 每秒排清其日誌，而不是在每次提交時排清日誌。此設定可提高效能，但可能會在系統故障期間造成交易遺失的風險。

  • 如果您要將資料載入至沒有僅供讀取複本的資料庫執行個體，請將 sync_binlog 設定為 0。載入完成後，將 sync_binlog parameter 還原至 1。

  • 在資料庫執行個體轉換為多可用區域部署前，將資料載入至單一可用區域資料庫執行個體。如果資料庫執行個體已使用多可用區域部署，則不建議切換至單一可用區域部署來進行資料載入。這麼做只會提供邊際改善