適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器中的自動資料清理微調

發行項
07/17/2024

適用範圍：適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

本文提供適用於 PostgreSQL 的Azure 資料庫彈性伺服器的自動資料清理功能概觀，以及可用來監視資料庫膨脹、自動資料清理阻礙的功能疑難排解指南。同時提供資料庫距離發生緊急或環繞情況的相關資訊。

什麼是自動資料清理

自動資料清理是一種 PostgreSQL 背景處理程序，可自動清除無效 Tuple 和更新統計資料。此功能可自動執行兩項主要維護工作來協助維護資料庫效能：

VACUUM - 藉由移除無效 Tuple 來釋放磁碟空間。
ANALYZE - 收集統計資料，以協助 PostgreSQL 最佳化工具選擇查詢的最佳執行路徑。

為了確保自動資料清理正常運作，自動資料清理伺服器參數應該一律設定為 ON。啟用時，PostgreSQL 會自動決定何時在資料表上執行 VACUUM 或 ANALYZE，確保資料庫保持效率和最佳化。

自動資料清理內部

自動資料清理會讀取頁面來尋找無效 Tuple，如果找不到，自動資料清理則會捨棄該頁面。自動資料清理找到無效 Tuple 時，就會將它們移除。成本的依據如下：

參數	描述
`vacuum_cost_page_hit`	讀取已在共用緩衝區且不需要讀取磁碟的頁面之成本。預設值設為 1。
`vacuum_cost_page_miss`	擷取不在共用緩衝區內頁面的成本。預設值設為 10。
`vacuum_cost_page_dirty`	在找到無效 Tuple 的頁面中來進行寫入的成本。預設值設為 20。

執行的自動資料清理工作量取決於兩個參數：

參數	描述
`autovacuum_vacuum_cost_limit`	自動資料清理的工作量一次執行完成。
`autovacuum_vacuum_cost_delay`	當自動資料清理達到 `autovacuum_vacuum_cost_limit` 參數指定的成本限制後，自動資料清理進入睡眠狀態的毫秒數。

在所有目前支援的 Postgres 版本中，autovacuum_vacuum_cost_limit 的預設值為 200 (實際上，設定為 -1，使其等於一般 vacuum_cost_limit 的值，也就是預設為 200)。

至於 autovacuum_vacuum_cost_delay，在 Postgres 第 11 版中，預設為 20 毫秒，而在 Postgres 第 12 版和更新版本中，預設為 2 毫秒。

自動資料清理每秒喚醒 50 次 (50*20 毫秒=1000 毫秒)。每次喚醒時，自動資料清理都會讀取 200 頁。

這表示自動資料清理在一秒內可以執行下列動作：

~80 MB/秒 [ (200 頁/vacuum_cost_page_hit) * 50 * 每頁 8 KB]；如果在共用緩衝區中找到所有含無效 Tuple 的頁面。
~8 MB/秒 [ (200 頁/vacuum_cost_page_miss) * 50 * 每頁 8 KB]；如果從磁碟讀取到所有含無效 Tuple 的頁面。
~4 MB/秒 [ (200 頁/vacuum_cost_page_dirty) * 50 * 每頁 8 KB] 自動資料清理寫入速度最高可達 4 MB/秒。

監視自動資料清理

使用下列查詢來監視自動資料清理：

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

下列資料行有助於判斷自動資料清理是否跟得上資料表活動：

參數	描述
`dead_pct`	相較於即時 Tuple 的無效 Tuple 百分比。
`last_autovacuum`	上次自動清理資料表的日期。
`last_autoanalyze`	上次自動分析資料表的日期。

PostgreSQL 觸發自動資料清理的時機

當無效 Tuple 的數量超過相依於兩個因素的特定數量時，就會觸發自動資料清理動作 (ANALYZE 或 VACUUM)：資料表中的資料列總數，加上固定閾值。根據預設，當資料表有 10% 及 50 個資料列變更時，就會觸發 ANALYZE，而資料表有 20% 及 50 個資料列變更時，則會觸發 VACUUM。由於 VACUUM 閾值比 ANALYZE 閾值高出兩倍，因此 ANALYZE 會比 VACUUM 更早觸發。若 PG 版本 >=13，根據預設，ANALYZE 會在資料表有 20% 及 1000 個資料列插入時觸發。

每個動作的確切方程式如下：

Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold 或 autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (若 PG 版本 >= 13)
自動資料清理 = autovacuum_vacuum_scale_factor * Tuple + autovacuum_vacuum_threshold

例如，如果我們的資料表有 100 個資料列。下列方程式接著會提供 ANALYZE 和 VACUUM 觸發時機的相關資訊：

對於更新/刪除：Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

ANALYZE 會在資料表有 60 個資料列變更後觸發，而 VACUUM 會在資料表有 70 個資料列變更時觸發。

對於插入：Autoanalyze = 0.2 * 100 + 1000 = 1020

ANALYZE 會在資料表插入了 1,020 個資料列後觸發

以下是方程式使用的參數描述：

參數	描述
`autovacuum_analyze_scale_factor`	在資料表上觸發 ANALYZE 的插入/更新/刪除數百分比。
`autovacuum_analyze_threshold`	指定觸發 ANALYZE 資料表的 Tuple 插入/更新/刪除數下限。
`autovacuum_vacuum_insert_scale_factor`	在資料表上觸發 ANLYZE 的插入數百分比。
`autovacuum_vacuum_insert_threshold`	指定觸發 ANALYZE 資料表的 Tuple 插入數下限。
`autovacuum_vacuum_scale_factor`	在資料表上觸發 VACUUM 的更新/刪除數百分比。

使用下列查詢列出資料庫中的資料表，並找出符合自動資料清理流程的資料表：

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

注意

此查詢不會考慮使用 alter table 的 DDL 命令為每個資料表設定自動資料清理。

常見的自動資料清理問題

檢閱下表，了解自動資料清理流程可能遇到的常見問題。

跟不上忙碌的伺服器

自動資料清理流程會估計每個 I/O 作業的成本、累積其執行的每個作業總計，並在達到成本上限後暫停。 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit 是流程中使用的兩個伺服器參數。

根據預設，autovacuum_vacuum_cost_limit 會設定為 –1，這表示自動資料清理成本限制的值與參數 vacuum_cost_limit 相同，預設為 200。 vacuum_cost_limit 是手動資料清理的成本。

如果 autovacuum_vacuum_cost_limit 設定為 -1，則自動資料清理會使用 vacuum_cost_limit 參數，但如果 autovacuum_vacuum_cost_limit 本身設為大於 -1，則會考慮 autovacuum_vacuum_cost_limit 參數。

如果自動資料清理作業跟不上，可能會變更下列參數：

參數	描述
`autovacuum_vacuum_cost_limit`	預設值：`200`。成本限制可能會增加。在變更前和變更後，應該監視資料庫的 CPU 和 I/O 使用率。
`autovacuum_vacuum_cost_delay`	Postgres 第 11 版 - 預設：`20 ms`。參數可能會減少至 `2-10 ms`。 Postgres 第 12 版和更新版本 - 預設：`2 ms`。

注意

autovacuum_vacuum_cost_limit 值會依比例散發在執行中的自動資料清理背景工作角色之間，因此如果不只一個，則每個背景工作角色的限制總和不會超過 autovacuum_vacuum_cost_limit 參數的值。
autovacuum_vacuum_scale_factor 是另一個參數，可根據無效 Tuple 累積來觸發對資料表執行 VACUUM。預設：0.2、允許的範圍：0.05 - 0.1。比例因素是工作負載特有的，應該根據資料表中的資料量來設定。變更值之前，請先調查工作負載和個別資料表磁碟區。

自動資料清理作業持續執行

持續執行自動資料清理可能會影響伺服器上的 CPU 和 IO 使用率。以下是一些可能的原因：

`maintenance_work_mem`

自動資料清理精靈會使用預設設定為 -1 的 autovacuum_work_mem，表示 autovacuum_work_mem 的值會與參數 maintenance_work_mem 相同。此文件假設 autovacuum_work_mem 設定為 -1，而且自動資料清理精靈使用 maintenance_work_mem。

如果 maintenance_work_mem 很低，則適用於 PostgreSQL 的 Azure 資料庫彈性伺服器可能會增加到最多 2 GB。一般經驗法則是針對每 1 GB RAM 配置 50 MB 至 maintenance_work_mem。

大量資料庫

自動資料清理會每 autovacuum_naptime 秒嘗試在每個資料庫上啟動背景工作角色。

例如，如果伺服器有 60 個資料庫且 autovacuum_naptime 設定為 60 秒，則自動資料清理背景工作角色每秒會啟動 [autovacuum_naptime/資料庫數量]。

如果叢集中有更多資料庫，最好增加 autovacuum_naptime。同時，藉由增加 autovacuum_cost_limit 和減少 autovacuum_cost_delay 參數，並將 autovacuum_max_workers 從預設值 3 增加為 4 或 5，可以讓自動資料清理流程變得更主動。

記憶體不足錯誤

過度主動的 maintenance_work_mem 值可能會定期造成系統記憶體不足的錯誤。在變更 maintenance_work_mem 參數之前，請務必先了解伺服器上可用的 RAM。

自動資料清理造成太大干擾

如果自動資料清理會耗用更多資源，則可以執行下列動作：

自動資料清理參數

評估參數 autovacuum_vacuum_cost_delay、autovacuum_vacuum_cost_limit、autovacuum_max_workers。自動資料清理參數設定有誤可能會導致自動資料清理造成太大干擾的情況。

如果自動資料清理造成太大干擾，請考慮採取下列動作：

如果設定高於預設值 200，請增加 autovacuum_vacuum_cost_delay 並減少 autovacuum_vacuum_cost_limit。
如果設定高於預設值 3，請減少 autovacuum_max_workers 的數值。

自動資料清理背景工作角色過多

增加自動資料清理背景工作角色的數量並不會提升資料清理的速度。不建議使用大量的自動資料清理背景工作角色。

增加自動資料清理背景工作角色的數量會耗用更多的記憶體，而根據 maintenance_work_mem 的值而定，可能會導致效能降低。

每個自動資料清理背景工作處理序只會獲得總 autovacuum_cost_limit 的 (1/autovacuum_max_workers)，因此使用大量的背景工作角色會導致每個背景工作角色速度變慢。

如果增加背景工作角色的數量，也應該增加 autovacuum_vacuum_cost_limit 和/或應該減少 autovacuum_vacuum_cost_delay，以加快資料清理流程的速度。

不過，如果我們已設定資料表層級 autovacuum_vacuum_cost_delay 或 autovacuum_vacuum_cost_limit 參數，那麼在這些資料表上執行的背景工作角色就不會納入平衡演算法 [autovacuum_cost_limit/autovacuum_max_workers] 的考量之中。

自動資料清理交易識別碼 (TXID) 環繞保護

當資料庫遇到交易識別碼環繞保護時，可以觀察到類似下列的錯誤訊息：

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

注意

此錯誤訊息為長期監督。通常，您不需要切換到單一使用者模式。相反地，您可以執行必要的 VACUUM 命令，並針對 VACUUM 執行微調以加速執行。雖然您無法執行任何資料操作語言 (DML)，但仍可執行 VACUUM。

當資料庫未進行資料清理，或是有太多未透過自動資料清理移除的無效 Tuple 時，就會發生環繞問題。造成此問題的原因可能包括：

工作負載過重

工作負載可能會在短期內造成太多無效 Tuple，使得自動資料清理來不及將其移除。系統中的無效 Tuple 會隨著時間累積，導致查詢效能降低，並造成環繞情況。這種情況的其中一個原因可能是因為自動資料清理參數未能適當設定，而且跟不上忙碌的伺服器。

長時間執行的交易

系統中任何長時間執行的交易都不允許在執行自動資料清理時移除無效 Tuple。這對資料清理流程來說是種阻礙。移除長時間執行的交易會在執行自動資料清理時釋出無效 Tuple 以進行刪除。

您可以使用下列查詢來偵測長時間執行的交易：

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

備妥語句

如果有未認可的備妥語句，會防止移除無效 Tuple。
下列查詢有助於尋找未認可的備妥語句：

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

使用 COMMIT PREPARED 或 ROLLBACK PREPARED 來認可或復原這些語句。

未使用的複寫位置

未使用的複寫位置可防止自動資料清理宣告無效 Tuple。下列查詢有助於辨識未使用的複寫位置：

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

使用 pg_drop_replication_slot() 來刪除未使用的複寫位置。

當資料庫遇到交易識別碼環繞保護時，請檢查先前提及的任何阻礙，然後手動移除這些阻礙，讓自動資料清理作業能夠繼續並完成。您也可以將 autovacuum_cost_delay 設為 0，並將 autovacuum_cost_limit 增加為大於 200 的值，以提升自動資料清理的速度。不過，這些參數的變更不會套用至現有的自動資料清理背景工作角色。請將資料庫重新開機，或是手動終止現有的背景工作角色，以套用參數變更。

資料表特定需求

您可以為個別資料表設定自動資料清理參數。這對於小型和大型資料表特別重要。例如，對於只包含 100 個資料列的小型資料表來說，當 70 個資料列變更時，自動資料清理會觸發 VACUUM 作業 (如先前所計算)。如果經常更新此資料表，您可能會每天看到數百個自動資料清理作業，以防止自動資料清理維護其他變更百分比沒有那麼重要的資料表。或者，包含 10 億個資料列的資料表必須變更 2 億個資料列，以觸發自動資料清理作業。適當設定自動資料清理參數可防止這類情況發生。

若要為每個資料表進行自動資料清理設定，請變更伺服器參數，如下列範例所示：

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

僅限插入的工作負載

在 PostgreSQL <= 13 的版本中，自動資料清理不會在具有僅限插入工作負載的資料表上執行，因為沒有無效 Tuple 且沒有需要回收的可用空間。不過，因為有新的資料，所以會針對僅限插入的工作負載執行自動分析。這樣的缺點包括：

資料表的可見度對應不會更新，因此查詢效能會隨著時間開始受到影響，特別是在僅索引掃描的情況下。
資料庫可能會遇到交易識別碼環繞保護。
未設定提示位元。

方案

Postgres 版本 <= 13

使用 pg_cron 延伸模組時，可以設定 cron 作業為資料表上的定期資料清理分析進行排程。 cron 作業的頻率取決於工作負載。

如需使用 pg_cron 的逐步指引，請檢閱延伸模組。

Postgres 13 及更新版本

自動資料清理會在具有僅插入工作負載的資料表上執行。 兩個全新伺服器參數 autovacuum_vacuum_insert_threshold 和 autovacuum_vacuum_insert_scale_factor 可協助控制在僅限插入的資料表上觸發自動資料清理的時機。

疑難排解指南

使用適用於 PostgreSQL 的 Azure 資料庫彈性伺服器入口網站中可用的功能疑難排解指南，可以監視資料庫或個別結構描述層級的膨脹，以及識別自動資料清理處理的潛在阻礙。提供兩個疑難排解指南，第一個是自動資料清理監視，可用來監視資料庫或個別結構描述層級的膨脹。第二個疑難排解指南是自動資料清理阻礙和環繞，有助於找出潛在的自動資料清理阻礙。它也提供伺服器上的資料庫距離發生環繞或緊急情況的相關資訊。疑難排解指南也會分享減輕潛在問題的建議。如何設定疑難排解指南的操作問題，請遵循設定疑難排解指南。

Azure Advisor 建議

Azure Advisor 建議可以主動識別伺服器是否有高膨脹率，或伺服器是否即將面臨交易環繞情況。您也可以使用使用 Azure 入口網站建立新建議的 Azure Advisor 警示，設定建議的警示

建議如下：

高膨脹率：高膨脹率可能會以數種方式影響伺服器效能。其中一個重要問題是 PostgreSQL 引擎最佳化工具可能難以選取最佳執行計畫，導致查詢效能降低。因此，當伺服器上的膨脹百分比達到特定閾值時即會觸發建議，以避免發生這類效能問題。
交易環繞：此案例是伺服器可能會遇到的最嚴重問題之一。一旦伺服器處於此狀態，可能會停止接受任何其他交易，導致伺服器變成唯讀狀態。因此，當我們看到伺服器已超過 10 億筆交易閾值時，就會觸發建議。

共用方式為

適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器中的自動資料清理微調

什麼是自動資料清理

自動資料清理內部

監視自動資料清理

PostgreSQL 觸發自動資料清理的時機

常見的自動資料清理問題

跟不上忙碌的伺服器

自動資料清理作業持續執行

`maintenance_work_mem`

大量資料庫

記憶體不足錯誤

自動資料清理造成太大干擾

自動資料清理參數

自動資料清理背景工作角色過多

自動資料清理交易識別碼 (TXID) 環繞保護

工作負載過重

長時間執行的交易

備妥語句

未使用的複寫位置

資料表特定需求

僅限插入的工作負載

方案

Postgres 版本 <= 13

Postgres 13 及更新版本

疑難排解指南

Azure Advisor 建議

意見反應

意見反應

其他資源

共用方式為

適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器中的自動資料清理微調

什麼是自動資料清理

自動資料清理內部

監視自動資料清理

PostgreSQL 觸發自動資料清理的時機

常見的自動資料清理問題

跟不上忙碌的伺服器

自動資料清理作業持續執行

maintenance_work_mem

大量資料庫

記憶體不足錯誤

自動資料清理造成太大干擾

自動資料清理參數

自動資料清理背景工作角色過多

自動資料清理交易識別碼 (TXID) 環繞保護

工作負載過重

長時間執行的交易

備妥語句

未使用的複寫位置

資料表特定需求

僅限插入的工作負載

方案

Postgres 版本 <= 13

Postgres 13 及更新版本

疑難排解指南

Azure Advisor 建議

相關內容

意見反應

意見反應

其他資源

`maintenance_work_mem`