適用於 PostgreSQL 的 Azure 資料庫 中的自動數據清理調整 - 彈性伺服器
適用於：
適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器
本文提供 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器之自動數據清理功能的概觀，以及可用來監視資料庫膨脹、自動數據清理封鎖程式的功能疑難解答指南，以及資料庫與緊急或包裝情況之間的距離相關信息。
什麼是自動數據清理
PostgreSQL 中的內部數據一致性是以多重版本並行控制 （MVCC） 機制為基礎，可讓資料庫引擎維護數據列的多個版本，並在不同進程之間以最少的封鎖來提供更高的並行存取。
PostgreSQL 資料庫需要適當的維護。 例如，刪除數據列時，不會實際移除。 相反地，數據列會標示為「死」。 同樣地，對於更新，數據列會標示為「無效」，並插入新版的數據列。 這些作業會留下無效的記錄，稱為無效 Tuple，即使看到這些版本的所有交易都已完成。 除非清除，否則會保留無效的 Tuple、耗用磁碟空間和膨脹的數據表和索引，因而導致查詢效能變慢。
PostgreSQL 會使用稱為 autovacuum 的程式來自動清除無效的 Tuple。
自動數據清理內部
自動數據清理會讀取尋找無效 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 pages/
vacuum_cost_page_hit） * 每頁 50 * 8 KB] 如果共用緩衝區中找到所有具有無效 Tuple 的頁面。 - ~8 MB/秒 [ （200 pages/
vacuum_cost_page_miss） * 每頁 50 * 8 KB] 如果所有具有無效 Tuple 的頁面都是從磁碟讀取。 - ~4 MB/秒 [ （200 pages/
vacuum_cost_page_dirty） * 每頁 50 * 8 KB] autovacuum 最多可以寫入 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 時，死元組的百分比。
- last_autovacuum：上次自動清理數據表的日期。
- last_autoanalyze：上次自動分析數據表的日期。
PostgreSQL 觸發自動數據清理的時機
當死元組數目超過相依於兩個因素的特定數位時，自動數據清理動作（即 ANALYZE 或 VACUUM）就會觸發：數據表中的數據列總數加上固定臨界值。 根據預設，當 10% 的數據表加上 50 個數據列變更時，ANALYZE 會觸發，而 當 20% 的數據表加上 50 個數據列變更時，VACUUM 就會觸發。 由於 VACUUM 臨界值與 ANALYZE 臨界值高兩倍，因此 ANALYZE 會比 VACUUM 更早觸發。
每個動作的確切方程式如下：
- Autoanalyze = autovacuum_analyze_scale_factor * Tuple + autovacuum_analyze_threshold
- Autovacuum = autovacuum_vacuum_scale_factor * Tuple + autovacuum_vacuum_threshold
例如，使用下列方程式，在包含100個資料列的數據表上分析60個資料列變更之後的觸發程式，並在資料表上變更70個數據列時進行真空觸發程式：
Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70
使用下列查詢來列出資料庫中的數據表，並識別符合自動數據清理程序的數據表：
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 ，則 autovacuum 會使用 vacuum_cost_limit 參數，但如果 autovacuum_vacuum_cost_limit 本身設定為大於 -1 ， autovacuum_vacuum_cost_limit 則會考慮參數。
如果自動數據清理未跟上，可能會變更下列參數：
| 參數 | 描述 |
|---|---|
autovacuum_vacuum_scale_factor |
預設值： 0.2、範圍： 0.05 - 0.1。 縮放比例是工作負載特定的，應該根據數據表中的數據量來設定。 變更值之前，請先調查工作負載和個別數據表磁碟區。 |
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
自動清理持續執行
持續執行自動數據清理可能會影響伺服器上的 CPU 和 IO 使用率。 以下是可能的原因：
maintenance_work_mem
Autovacuum 精靈會使用 autovacuum_work_mem 預設設定為 -1 表示 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/DB 數目]。
如果叢集中有更多的資料庫，最好增加 autovacuum_naptime 。 同時，可以藉由增加和減少autovacuum_cost_delay參數，並將預設值從 3 增加到 autovacuum_cost_limitautovacuum_max_workers 4 或 5，讓自動數據清理程式更具侵略性。
記憶體不足錯誤
過度積極的 maintenance_work_mem 值可能會定期在系統中造成記憶體不足的錯誤。 請務必先了解伺服器上的可用 RAM，再對 參數進行任何變更 maintenance_work_mem 。
自動數據清理太干擾
如果自動數據清理耗用大量資源，可以完成下列作業：
自動數據清理參數
評估參數 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 以供刪除。
您可以使用下列查詢來偵測長時間執行的交易：
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 個數據列變更時，自動數據清理會在 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);
僅限插入的工作負載
在 13 之前的 PostgreSQL 版本中，自動數據清理不會在具有僅限插入工作負載的數據表上執行，因為如果沒有更新或刪除，就不會有無效的 Tuple，也不需要回收任何可用空間。 不過，Autoanalyze 會針對僅限插入的工作負載執行，因為有新的數據。 其缺點如下：
- 數據表的可見性對應不會更新，因此查詢效能，特別是在只有索引掃描的地方，會隨著時間開始受到影響。
- 資料庫可以執行交易標識碼包裝保護。
- 不會設定提示位。
方案
13 之前的 Postgres 版本
使用pg_cron擴充功能，可以設定cron作業來排程數據表上的定期真空分析。 cron 作業的頻率取決於工作負載。
如需使用 pg_cron 的逐步指引，請檢閱 延伸模組。
Postgres 13 和更新版本
自動數據清理會在具有僅限插入工作負載的數據表上執行。 在僅限插入的數據表上觸發自動數據清理時，有兩個新的伺服器參數 autovacuum_vacuum_insert_threshold 和 autovacuum_vacuum_insert_scale_factor 協助控制。
疑難排解指南
使用 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器入口網站上可用的功能疑難解答指南，即可監視資料庫或個別架構層級的膨脹，以及識別可能阻礙自動清理程式的程式。 第一個有兩個疑難解答指南是自動數據清理監視，可用來監視資料庫或個別架構層級的膨脹。 第二個疑難解答指南是自動數據清理封鎖器和包裝，有助於識別潛在的自動數據清理封鎖程式，以及伺服器資料庫從包裝或緊急情況到多遠的資訊。 疑難解答指南也會分享降低潛在問題的建議。 如何設定疑難解答指南以使用它們，請遵循 設定疑難解答指南。