大桌子上的真空有時需要很長時間

在 PostgreSQL 11 數據庫伺服器中，我有一個大表（9.32 億行，2150GB，未分區），我每晚通過批處理作業向其中插入大約 2-3 百萬行。該表是用autovacuum_enabled=FALSE. 插入後，我VACUUM (ANALYZE)在桌子上執行 a，這通常需要大約 30-40 秒。然而，每隔幾週，這種真空就需要很長時間，比如今天需要 2:11 h:mm。

我現在正在考慮在我的夜間工作之前做一個真空吸塵器，以避免在工作執行期間這些長時間的執行時間。我查看了PostgreSQL 11 Vacuum 文件頁面中描述的選項。我知道 aVACUUM (FULL)在執行時非常昂貴，因此可能是不可能的。在我VACUUM (FREEZE)的情況下會有用嗎？另一種選擇VACUUM (DISABLE_PAGE_SKIPPING)似乎與我的需求無關。

---

**編輯（2021-09-27）：**回顧過去的 270 天（2021-01-01 到 2021-09-27），大多數日子都需要 16-60 秒才能完成抽真空。然而，在第 9 天，真空度超過了 1000 秒，其中 4 天甚至超過了 3500 秒（見下圖）。有趣的是這些異常值之間的時間差，介於 25 到 35 天之間。在這 25-35 天的時間裡，似乎有什麼東西在堆積，需要更長的真空期。這很可能是下面評論中提到的事務 ID 環繞。

---

**編輯 (2022-02-08)：**在 2021 年 9 月，我們通過更改以下內容修改了腳本：

VACUUM (ANALYZE)

到：

ANALYZE (VERBOSE)
VACUUM (FREEZE, VERBOSE)

不幸的是，這個工作步驟大約每月一次的長時間執行仍在繼續。然而，通過分成兩個獨立的分析和真空步驟，以及詳細的輸出，我們能夠更好地判斷發生了什麼。

在快速的一天，分析和真空步驟的輸出如下所示：

INFO:  analyzing "analytics.a_box_events"
INFO:  "a_box_events": scanned 30000 of 152881380 pages, containing 264294 live rows and 0 dead rows; 30000 rows in sample, 1346854382 estimated total rows
ANALYZE
INFO:  aggressively vacuuming "analytics.a_box_events"
INFO:  "a_box_events": found 0 removable, 3407161 nonremovable row versions in 406302 out of 152881380 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 108425108
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 152475078 frozen pages.
0 pages are entirely empty.
CPU: user: 4.53 s, system: 3.76 s, elapsed: 25.77 s.
INFO:  aggressively vacuuming "pg_toast.pg_toast_1361926"
INFO:  "pg_toast_1361926": found 0 removable, 3700 nonremovable row versions in 680 out of 465143 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 108425110
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 464463 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.05 s.
VACUUM

分析沒有定時輸出，主表上的真空大約需要20-40秒。分析和抽真空通常在一分鐘內完成。

每 35-45 天一次，清理需要更長的時間，因為需要花費幾分鍾清理表的 39 個索引中的每一個。這有兩種變體：

1. 沒有要刪除的索引行版本

範例輸出：

INFO:  index "idx_a_box_events_node_id" now contains 1347181817 row versions in 4038010 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 5.44 s, system: 13.45 s, elapsed: 230.59 s.

整個真空的最終輸出如下所示：

INFO:  "a_box_events": found 0 removable, 2837554 nonremovable row versions in 340887 out of 153111143 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 108429069
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 152770256 frozen pages.
0 pages are entirely empty.
CPU: user: 669.94 s, system: 870.59 s, elapsed: 10501.17 s.

2. 要刪除的索引行版本

範例輸出：

INFO:  scanned index "idx_a_box_events_node_id" to remove 2524 row versions
DETAIL:  CPU: user: 49.34 s, system: 11.42 s, elapsed: 198.63 s

和：

INFO:  index "idx_a_box_events_node_id" now contains 1228052362 row versions in 3478524 pages
DETAIL:  2524 index row versions were removed.
6 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

整個真空的最終輸出如下所示：

INFO:  "a_box_events": found 56 removable, 3851482 nonremovable row versions in 461834 out of 139126225 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 97583006
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 138664391 frozen pages.
0 pages are entirely empty.
CPU: user: 2367.93 s, system: 761.34 s, elapsed: 8138.76 s.

如此長的執行時間目前比顯示停止更令人討厭。計劃在 2023 年底升級到最新的 Postgres 伺服器版本。屆時我們將嘗試對 VACUUM 使用 INDEX_CLEANUP 選項，每週末執行一次，以避免在日常 VACUUM 中對索引進行代價高昂的清理。與此同時，我們顯然別無選擇，只能耐心等待。

這很可能是事務 ID 環繞，它必須掃描並凍結自上次環繞以來插入的行。

你應該改變你的夜間執行來VACUUM (FREEZE)分配負載，特別是如果它是一個只插入的表，主動凍結不會消耗不必要的資源。有關凍結元組的必要性的詳細討論，請參閱文件。

另一種方法是升級到 PostgreSQL v13，它根據INSERTs的數量添加了 autovacuum 。

我對這裡發生的事情的理論是，對於僅 INSERT 表，vacuum 只需要訪問被插入弄髒的表部分，並且可以完全跳過訪問索引。

但是，如果它甚至找到一個（在 v11 中）死元組，那麼它需要掃描所有索引的整體，這可能需要很長時間。我很驚訝它需要 2 多個小時，但那是一張非常大的桌子，所以索引也會非常大。成功的 INSERT 和 COPY 不會生成任何死元組。但即使是一個 UPDATE 或 DELETE 也會這樣做，任何回滾的 INSERT 或 COPY 也是如此。這個理論看起來與你所看到的相符嗎？您是否曾經對一個元組進行過任何刪除或更新，或者曾經有過失敗的 INSERT？

您可以在晚上 9 點執行 VACUUM，然後如果它確實必須掃描所有索引，那麼您在批量載入之後執行的那個就不必再做一次了——除非批量載入本身就是導致當機的東西元組。

但是，如果您只是在批量載入之後單獨並按順序執行 ANALYZE，然後 VACUUM，該怎麼辦？是長 VACUUM 在您開始工作時使用太多 IO 並減慢速度的問題，還是長 VACUUM 意味著 ANALYZE 尚未完成，因此您在工作日開始時計劃不好的問題？

另一個問題是，如果您不採取任何措施來凍結表格的某些部分，那麼一旦反環繞 VACUUM 啟動，您可能會陷入非常糟糕的境地。您執行的第一個 VACUUM FREEZE 將生成大量 IO 並花費很長時間，但之後它一次只需要凍結表的一小部分，因此應該是可管理的。與 VACUUM 的索引掃描部分不同，VACUUM FREEZE 的好處在於，如果它被中斷，它不會失去它迄今為止所做的所有工作，因為在可見性圖中記錄為凍結的頁面可以在下次跳過. 因此，您可以啟動 VACUUM FREEZE，然後在它似乎引起問題時取消，或者如果您離開您的辦公桌，這樣您將暫時無法監控它。那麼一旦你被追上，

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