postgresql VACUUM 不會從表中刪除死行的三個原因

資料庫集中營發表於2023-02-28
SQL

一、為什麼是VACUUM？

每當更新或刪除PostgreSQL表中的行時，都會留下死元組。VACUUM擺脫了它們，以便空間可以重複使用。如果一個表沒有被清理，它就會變得臃腫，這會浪費磁碟空間並減慢表的順序掃描（以及索引掃描）。VACUUM還負責凍結錶行，以避免在事務ID計數器環繞時出現問題，但這是另一回事。通常，您不必處理所有這些，因為PostgreSQL中內建的autovacuum 守護程式會為您完成這些工作。

二、問題

如果你的表變得臃腫，首先你需要檢查的是 autovacuum 是否已處理它們：

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
    / (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 10;

如果你的臃腫的表沒有出現在這裡，n_dead_tup是0並且last_autovacuum是 NULL，你可能有統計資訊收集器的問題。

如果臃腫的表就在頂部，但last_autovacuum為NULL，則您可能需要將autovacuum 配置為更積極，以便完成對錶的處理。

但有時結果會如下所示：

schemaname |    relname   | n_live_tup | n_dead_tup |   last_autovacuum
------------+--------------+------------+------------+---------------------
 laurenz    | vacme        |      50000 |      50000 | 2018-02-22 13:20:16
 pg_catalog | pg_attribute |         42 |        165 |
 pg_catalog | pg_amop      |        871 |        162 |
 pg_catalog | pg_class     |          9 |         31 |
 pg_catalog | pg_type      |         17 |         27 |
 pg_catalog | pg_index     |          5 |         15 |
 pg_catalog | pg_depend    |       9162 |        471 |
 pg_catalog | pg_trigger   |          0 |         12 |
 pg_catalog | pg_proc      |        183 |         16 |
 pg_catalog | pg_shdepend  |          7 |          6 |
(10 rows)

autovacuum最近在這裡執行，但它沒有釋放死元組！

我們可以透過執行VACUUM (VERBOSE)來驗證問：

test=> VACUUM (VERBOSE) vacme;
INFO:  vacuuming "laurenz.vacme"
INFO:  "vacme": found 0 removable, 100000 nonremovable row versions in
       443 out of 443 pages
DETAIL:  50000 dead row versions cannot be removed yet,
         oldest xmin: 22300
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.

三、為什麼VACUUM不能刪除死元組？

VACUUM只能刪除不再需要的那些行版本（也稱為“元組”）。如果刪除事務的事務 ID（儲存在xmax中）早於 PostgreSQL 資料庫（或共享表的整個叢集）中仍處於活動狀態的最舊事務，則無法清除元組。

這個值（VACUUM上面輸出中的 22300）稱為“xmin 水平”。

在 PostgreSQL叢集中，有三件事可以阻止這個xmin範圍：

1、長時間執行的事務：

可以透過以下查詢找到長時間執行的事務及其xmin值：

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

可以使用該pg_terminate_backend()函式來終止阻止您的VACUUM.

2、廢棄的Replication Slot：

複製槽是一種資料結構，保持從主庫丟棄但仍需要由備用伺服器趕上主要資訊PostgreSQL伺服器的資料。

如果複製延遲或備用伺服器關閉，複製槽將阻

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

使用該pg_drop_replication_slot()函式刪除不再需要的複製槽。

注意：如果hot_standby_feedback = on. 對於邏輯複製存在類似的危險（無法回收元組），但只有系統目錄受到影響。catalog_xmin在這種情況下檢查列。

hot_standby_feedback引數之後備庫會定期向主庫通知最小活躍事務id（xmin）值，這樣使得主庫vacuum程式不會清理大於xmin值的事務。

3、孤立的準備執行的事務：

在兩階段提交期間，分散式事務首先用PREPARE語句準備，然後用COMMIT PREPARED語句提交。

一旦一個事務準備好，它就會一直“等待”直到它被提交或中止。它甚至必須在伺服器重啟後還需要保留下來！通常，事務不會長時間保持準備狀態，但有時會出錯，必須由管理員手動刪除準備好的事務。

可以xmin使用以下查詢找到所有準備好的交易及其價值：

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

使用ROLLBACK PREPAREDSQL 語句刪除準備好的事務。

 

 

相關文章