PostgreSQL 9.6.12 autovacuum 不斷在系統表上執行

我來自 SQL Server、Oracle、Sybase DBA 背景，但我現在正在研究一個執行 PostgreSQL 9.6.12 的 AWS Aurora 集群，並註意到一些我認為很奇怪的東西，但也許不是，這就是我在這裡問的原因這個問題。我到處尋找，但找不到答案。仍設置預設的 autovacuum 和 autoanalyze 值。最終， Autovacuum似乎確實可以在應用程序表上做它需要做的事情，但我注意到它似乎大部分時間都在頻繁地清理和分析一小組系統表。他們是：

1. pg_type
2. pg_shdepend
3. pg_attribute
4. pg_class
5. pg_depend

我通過 AWS Performance Insights 數據以及使用以下程式碼直接查詢數據庫實例都看到了這一點：

   WITH rel_set AS
(
   SELECT
       oid,
       CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_threshold=', 2), ',', 1)
           WHEN '' THEN NULL
       ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_threshold=', 2), ',', 1)::BIGINT
       END AS rel_av_anal_threshold,        
       CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
           WHEN '' THEN NULL
       ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
       END AS rel_av_vac_threshold,
       CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_scale_factor=', 2), ',', 1)
           WHEN '' THEN NULL
       ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_scale_factor=', 2), ',', 1)::NUMERIC
       END AS rel_av_anal_scale_factor,        
       CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
           WHEN '' THEN NULL
       ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
       END AS rel_av_vac_scale_factor
   FROM pg_class
) 
SELECT
   PSUT.relname,
--    to_char(PSUT.last_analyze, 'YYYY-MM-DD HH24:MI')     AS last_analyze,
   to_char(PSUT.last_autoanalyze, 'YYYY-MM-DD HH24:MI') AS last_autoanalyze,    
--    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
   to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
   to_char(C.reltuples, '9G999G999G999')               AS n_tup,
   to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
   to_char(coalesce(RS.rel_av_anal_threshold, current_setting('autovacuum_analyze_threshold')::BIGINT) + coalesce(RS.rel_av_anal_scale_factor, current_setting('autovacuum_analyze_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_analyze_threshold,
   to_char(coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_vacuum_threshold,
   CASE
       WHEN (coalesce(RS.rel_av_anal_threshold, current_setting('autovacuum_analyze_threshold')::BIGINT) + coalesce(RS.rel_av_anal_scale_factor, current_setting('autovacuum_analyze_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
       THEN '*'
   ELSE ''
   end
   AS expect_av_analyze,    
   CASE
       WHEN (coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
       THEN '*'
   ELSE ''
   end
   AS expect_av_vacuum,
   PSUT.autoanalyze_count,
   PSUT.autovacuum_count
FROM
   pg_stat_all_tables PSUT
   JOIN pg_class C
       ON PSUT.relid = C.oid
   JOIN rel_set RS
       ON PSUT.relid = RS.oid
ORDER BY PSUT.autoanalyze_count DESC; --C.reltuples

AWS RDS Performance Insights 圖表顯示一半的 CPU 活動被 autovacuum 程序消耗：

起初我認為這可能是由於創建了許多臨時表然後銷毀或類似的原因，因為我會定期看到元組的數量從大約 8,000 到 8,000,000 然後又在幾個前面提到的表格。但是我還沒有找到任何臨時表創建的證據，而且離岸開發人員說他們不使用它們。

這種行為在普通 PostgreSQL 或 Aurora (PostgreSQL) 中是否正常？如果這不正常，是否有任何人可以建議查看以確定這裡可能發生的情況？在具有 122GB RAM（75% 分配給 shared_buffers - Aurora 的預設值）的實例上，該數據庫的大小約為 TB。

我希望從預設值更改自動清理設置以處理這個數據庫更大的表，但只是想確保如果有問題的表只是壟斷自動清理/自動分析的時間，那不會浪費時間。

目前設置（來自 pg_settings）：

autovacuum  on
autovacuum_analyze_scale_factor 0.05
autovacuum_analyze_threshold    50
autovacuum_freeze_max_age   200000000
autovacuum_max_workers  3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime  5
autovacuum_vacuum_cost_delay    5
autovacuum_vacuum_cost_limit    -1
autovacuum_vacuum_scale_factor  0.1
autovacuum_vacuum_threshold 50

這是查詢的相關輸出。注意 autoanalyze_count 和 autovacuum_count 的列。這個實例只執行了 6 天，所以這些數字看起來非常高。所有其他表格僅顯示這些列的 0-10（為了提高效率，我沒有將其餘表格放入其中）。

relname                                             |last_autoanalyze|last_autovacuum |n_tup         |dead_tup      |av_analyze_threshold|av_vacuum_threshold|expect_av_analyze|expect_av_vacuum|autoanalyze_count|autovacuum_count|
----------------------------------------------------|----------------|----------------|--------------|--------------|--------------------|-------------------|-----------------|----------------|-----------------|----------------|
pg_type                                             |2020-03-06 18:20|2020-03-06 18:20|         1,352|           192|           118      |           185     |*                |*               |            22781|           34428|
pg_shdepend                                         |2020-03-06 18:20|2020-03-06 18:20|       694,312|           164|        34,766      |        69,481     |                 |                |            20945|           73784|
pg_class                                            |2020-03-06 18:20|2020-03-06 18:20|         1,172|           264|           109      |           167     |*                |*               |            13758|           21198|
pg_attribute                                        |2020-03-06 18:20|2020-03-06 18:20|         9,205|         1,976|           510      |           970     |*                |*               |            12692|           17710|
pg_depend                                           |2020-03-06 18:20|2020-03-06 18:20|        10,981|         1,143|           599      |         1,148     |*                |                |            11255|           16883|

總而言之，我要問的是：一小組系統表不斷地自動清理是否正常？任何見解將不勝感激。

您提到的目錄表一直被清理表明確實表（或不太可能的複合數據類型）一直在創建和銷毀：

• pg_class是表（和其他關係）
• pg_attributes是列表
• pg_type包含類型，並且為每個表創建一個具有相同名稱的複合類型
• pg_depend包含表和（例如）關聯類型之間的依賴關係。
• pg_shdepend包含表和擁有角色之間的依賴關係。

最容易膨脹的表是pg_attribute.

由於您使用的是託管數據庫，因此您可能既沒有超級使用者訪問權限，也不能使用pgstattuple允許您準確確定表的膨脹程度的擴展。

但是你可以找出它的大小：

SELECT pg_total_relation_size('pg_attribute');

此外，您可以找出有多少死（刪除）元組：

SELECT n_live_tup, n_dead_tup
FROM pg_stat_sys_tables
WHERE relname = 'pg_attribute';

您看到的任何死元組都有證據表明列已被刪除。因此，如果這個數字不斷增加，您就有證據證明您的開發人員弄錯了（可能不是臨時表，而是正常表）。

為了避免問題，請盡可能使 autovacuum 更具侵略性：

autovacuum_vacuum_cost_delay = 0

你能發布你的 SQL 查詢的輸出並精確的 autovacuum 的頻率嗎？這 5 個系統目錄表的行數是多少？您是否經常執行大量 DDL 語句？

如果自上次 VACUUM 以來廢棄的元組數超過定義為的真空門檻值，則 Autovacuum 執行：

vacuum threshold =  autovacuum_vacuum_threshold +  autovacuum_vacuum_scale_factor *pg_class.reltuples;

在你的情況下：50 + 0.1 * pg_class.reltuples

引用自：https://dba.stackexchange.com/questions/261158