共用方式為

將資料大量上傳至適用於 PostgreSQL 的 Azure 資料庫的最佳做法

本文討論將資料大量載入至適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體的各種方法，以及空白資料庫中初始資料載入和累加資料載入的最佳做法。

載入方法

下列資料載入方法會依最耗時到最不耗時的順序排列：

  • 執行單一記錄 INSERT 命令。
  • 每個認可批次進行 100 到 1,000 個資料列。 您可以使用交易區塊來包裝每個認可的多個記錄。
  • 使用多個資料列值執行 INSERT。
  • 執行 COPY 命令。

將資料載入資料庫中的慣用方法是 COPY 命令。 如果無法使用 COPY 指令，則使用批次 INSERT 是次佳的方法。 使用 COPY 命令進行多執行緒作業是大量載入資料的最佳方式。

上傳大量資料的步驟

以下是將資料大量上傳至適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體的步驟。

步驟 1：準備您的資料

請確定您的資料是乾淨的且已對資料庫進行了適當的格式化。

步驟 2：選擇載入方法

根據資料的大小和複雜性選取適當的載入方法。

步驟 3：執行載入方法

執行所選擇的載入方法以將資料上傳至資料庫。

步驟 4：驗證資料

上傳後，請驗證資料是否已正確載入到資料庫中。

初始資料載入的最佳做法

以下是初始資料載入的最佳做法。

卸除索引

在進行初始資料載入之前，建議您先卸除資料表中的所有索引。 在資料載入後再建立索引，總是比較有效率。

卸除限制式

主要卸除限制式如下所述：

  • 唯一索引鍵限制式

若要達到強大的效能，建議您在初始資料載入之前先卸除唯一索引鍵限制式，並在資料載入完成後重新建立它們。 不過，卸除唯一索引鍵限制式會取消對重複資料的保護。

  • 外部索引鍵限制式

建議您在初始資料載入之前先卸除外部索引鍵限制式，並在資料載入完成後重新建立它們。

將 session_replication_role 參數變更為 replica 也會停用所有外部索引鍵檢查。 然而，如果變更未被正確使用，可能會導致資料不一致。

未記錄的資料表

在初始資料載入中使用未記錄的資料表之前，請先考慮其優缺點。

使用未記錄的資料表可以加快資料載入的速度。 寫入未記錄資料表的資料不會寫入預先寫入記錄。

使用未記錄資料表的缺點如下：

  • 它們並不具備損毀安全性。 在經歷當機或未正常關機後，未記錄的資料表將會自動截斷。
  • 未記錄資料表的資料無法複寫到待命伺服器。

若要建立未記錄的資料表，或將現有資料表變更為未記錄的資料表，請使用下列選項：

  • 使用下列語法建立新的未記錄資料表：

    CREATE UNLOGGED TABLE <tablename>;

  • 使用下列語法，將現有的已記錄資料表轉換為未記錄資料表：

    ALTER TABLE <tablename> SET UNLOGGED;

伺服器參數微調

  • auto vacuum': It's best to turn off auto vacuum' (在初始資料載入期間)。 在初始載入完成後，建議您在資料庫中的所有資料表上手動執行 VACUUM ANALYZE，然後開啟 auto vacuum。

附註

只有在記憶體和磁碟空間充足的情況，才遵循上述建議。

  • maintenance_work_mem：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，最多可以設定為 2 GB。 maintenance_work_mem 有助於加快自動清理、編製索引和外部索引鍵的建立。

  • checkpoint_timeout：在「適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器」執行個體上，checkpoint_timeout 值可以從預設的 5 分鐘增加到最多 24 小時。 建議您一開始在「適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器」執行個體上載入資料之前，先將值增加為 1 小時。

  • checkpoint_completion_target：建議值為 0.9。

  • max_wal_size：您可以在執行初始資料載入時，設定為「適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器」執行個體上的允許最大值，即 64 GB。

  • wal_compression：這可以開啟。 啟用此參數對於在預先寫入記錄 (WAL) 記錄期間進行壓縮以及在 WAL 重播期間進行解壓縮，可能會產生一些額外的 CPU 成本。

建議

在「適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器」執行個體上開始進行初始資料載入之前，建議您：

  • 停用伺服器上的高可用性。 您可以在主要伺服器上完成初始載入之後加以啟用。
  • 在初始資料載入完成之後建立讀取複本。
  • 在初始資料載入期間將記錄設為最小或將其完全停用 (例如，停用 pgaudit、pg_stat_statements、查詢存放區)。

重新建立索引並新增限制式

假設您在初始載入之前已卸除索引和限制式，建議您在 maintenance_work_mem 中使用高值 (如先前所述)，以建立索引和新增限制式。 此外，從 PostgreSQL 第 11 版開始，您可以修改下列參數，以在初始資料載入之後，更快速地建立平行索引：

  • max_parallel_workers：設定系統可支援平行查詢的背景工作角色數目上限。

  • max_parallel_maintenance_workers：控制可在 CREATE INDEX 中使用的背景工作處理序數目上限。

您也可以在工作階段層級藉由建立建議的設定，以建立索引。 以下是操作範例：

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

增量資料載入的最佳做法

增量資料載入的最佳做法如下所述：

分割區資料表

我們一律建議您分割大型資料表。 分割的一些優點，特別是在增量載入期間，包括：

  • 根據新的差異來建立新的分割區可以有效地將新資料新增至資料表中。
  • 維護資料表會變得更容易。 您可以在增量資料載入期間卸除分割區，以避免耗費時間來刪除大型資料表。
  • Autovacuum 只會在增量載入期間變更或新增的分割區上觸發，這可讓您更容易維護資料表上的統計資料。

維護最新的資料表統計資料

監視和維護資料表統計資料對於資料庫的查詢效能很重要。 這也包括您有增量載入的情況。 PostgreSQL 會使用 autovacuum 精靈程序來清除無效 Tuple，並分析資料表以保持統計資料更新。 如需詳細資訊，請參閱 Autovacuum 監視和微調。

建立外部索引鍵限制式的索引

在子資料工作表中建立外部索引鍵的索引，在下列情況中很有幫助：

  • 父資料表中的資料更新或刪除。 在父資料表中更新或刪除資料時，會在子資料工作表上執行查閱。 您可以在子資料表上為外部索引鍵編製索引，以加快查閱速度。
  • 查詢 (您可以在其中看到父資料表和子資料表在索引鍵資料行上的聯結)。

識別未使用的索引

識別資料庫中未使用的索引，並加以卸除。 索引是資料載入的額外負荷。 資料表上的索引越少，資料擷取期間的效能就越好。

您可以使用兩種方式來識別未使用的索引：透過查詢存放區和索引使用量查詢。

查詢存放區

查詢存放區功能可協助識別索引，這些索引可以根據資料庫的查詢使用模式來卸除。 如需逐步指導方針，請參閱查詢存放區。

啟用伺服器上的查詢存放區之後，您可以使用下列查詢來識別可藉由連線到 azure_sys 資料庫卸除的索引。

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

索引使用量

您也可以使用下列查詢來識別未使用的索引：

SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
 pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched
FROM
 pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

number_of_scans、tuples_read 和 tuples_fetched 資料行表示索引 usage.number_of_scans 資料行值為零點，做為未使用的索引。

伺服器參數微調

附註

只有在記憶體和磁碟空間足夠的情況下，才遵循下列參數中的建議。

  • maintenance_work_mem：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，此參數最多可以設定為 2 GB。 maintenance_work_mem 有助於加速索引建立和外部索引鍵新增。

  • checkpoint_timeout：在適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體上，checkpoint_timeout 值可以從預設的 5 分鐘增加到 10 或 15 分鐘。 將 checkpoint_timeout 增加到一個較大的值 (例如 15 分鐘) 可以減少 I/O 負載，但缺點是當機時需要較長的時間才能復原。 建議您在進行變更之前仔細考慮。

  • checkpoint_completion_target：建議值為 0.9。

  • max_wal_size：此值取決於 SKU、儲存體和工作負載。 以下範例顯示了獲得 max_wal_size 的正確值的一種方法。

在尖峰上班時間，執行下列動作以得出值：

一。 執行下列查詢，以取得目前的 WAL 記錄序號 (LSN)：

SELECT pg_current_wal_lsn ();

b。 等待 checkpoint_timeout 的秒數。 執行下列查詢以取得目前的 WAL LSN：

SELECT pg_current_wal_lsn ();

c. 使用兩個結果來檢查 GB 的差異：

SELECT round (pg_wal_lsn_diff('LSN value when running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression：這可以開啟。 啟用此參數對於在 WAL 記錄期間進行壓縮以及在 WAL 重播期間進行解壓縮，可能會產生額外的 CPU 成本。

相關內容

  • Last updated on