一文搞懂 PostgreSQL 中 VACUUM 與 VACUUM FULL
- VACUUM 概述
在 PostgreSQL 8.0版本之前，必須手動執行 VACUUM 進程 (使用psql實用程序或libpq等客戶端程序中執行)，而在 2005 年xx版本中，VACUUM 已經通過守護進程 (autovacuum) 實現自動化。其好處是它定期運行ANALYZE進程來收集頻繁更新表的最新統計信息，這使查詢規劃器能夠優化它的計劃。
autovacuum守護進程會在每個autovacuum_naptime(默認1min) 時間間隔內啓動 PostgreSQL 的autovacuum工作進程 (worker process)，並且最多隻允許同時運行autovacuum_max_workers(默認是3) 個工作進程；然後每個工作進程將逐一檢查數據庫中的每個表，並在需要時，執行VACUUM或ANALYZE操作。如果需要處理的數據庫多於autovacuum_max_workers，則下一個數據庫將會在第一個worker結束後立即處理。
這幾個選項支持重新配置，在PGDATA路徑下的postgresql.conf文件中。如下：
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs每當postmaster服務啓動之後，系統就會fork()一個autovacuum守護進程， 該守護進程將會週期性地檢查指定表 (若未指定具體表，則檢查系統所有表) 中的各塊是否存在死元組等相關邏輯處理。和另外幾個輔助進程一樣，VACUUM 進程的運行狀態也會受到postmaster服務的監視。postmaster服務會定期去檢測 VACUUM 進程的運行情況，一旦發現該進程不存在，則會立刻重新fork()一個autovacuum輔助進程。ServerLoop()是整個postmaster服務的主要循環函數。
static int ServerLoop(void)
{
//省略若干代碼
. . . . .
for (;;)
{
//省略若干代碼
. . . . . .
if (!IsBinaryUpgrade && AutoVacPID == 0 &&
(AutoVacuumingActive() || start_autovac_launcher) &&
pmState == PM_RUN)
{
//若autovacuum進程退出, 則重新fork()一個autovacuum輔助進程
AutoVacPID = StartAutoVacLauncher();
if (AutoVacPID != 0)
start_autovac_launcher = false; /* signal processed */
}
//省略若干代碼
. . . . . .
}
}- VACUUM 分類
對於 VACUUM 清理死元組，它提供了兩種模式：VACUUM 和 VACUUL FULL。其中 VACUUM 也默認稱爲 “LAZY VACUUM”，或 “Concurrent VACUUM”。這兩種模式，都可以實現清理表中死元組的過程，但在底層實現即具體處理細節上有所不同，下面將分別介紹。
2.1 VACUUM
VACUUM 的主要工作是回收被標記爲 “dead” 元組佔用的存儲空間，不過它不會將回收的空間歸還給操作系統，而是在同一頁中進行碎片整理，因此它們只是可供將來在同一表中插入數據時重用。由於 VACUUM 操作特定表時，使用的是共享鎖，所以同時刻其他讀 (read)/ 寫 (write) 操作也可以同時在一個表上進行，而不會被阻塞。
VACUUM 刪除指定表中死元組的過程主要分爲以下3個步驟：
(1) 獲取死元組，並刪除死元組指向的索引元組
在該過程裏，VACUUM 會從指定的表中依次獲取每個表。在 乾貨 | PostgreSQL 數據表文件底層結構佈局分析 一文中描述過，當表文件大於1GB，會重新創建一個表文件，該表文件的命名方式是 “表OID + “.” + “從1開始遞增的序號”。所以這裏強調 “從指定表獲取每個表”。
之後爲表獲取ShareUpdateExclusiveLock鎖，該鎖是一個共享鎖，它允許從其它事務中讀取數據。對於 “VACUUM (non-FULL)、ANALYZE、CREATE INDEX CONCURRENTLY” 這幾個操作都將使用該鎖。鎖 (ShareUpdateExclusiveLock) 在src/include/storage/目錄下的lockdefs.h文件中定義。
#define NoLock 0
#define AccessShareLock 1 /* SELECT */
#define RowShareLock 2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock 3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4 /* VACUUM (non-FULL),ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6 /* like EXCLUSIVE MODE, but allows ROW SHARE */
#define ExclusiveLock 7 /* blocks ROW SHARE/SELECT...FOR UPDATE */
#define AccessExclusiveLock 8 /* ALTER TABLE, DROP TABLE, VACUUM FULL,
* and unqualified LOCK TABLE */
#define MaxLockMode 8然後將掃描所有頁 (page) 以獲取所有的死元組，並在必要的時候凍結舊元組。如果存在，則刪除指向各自死元組的索引元組。
當 PostgreSQL 在掃描目標表所有頁以獲取死元組的時候，它會將得到的所有死元組構建成一個列表。該列表存儲在maintenance_work_mem緩存中。該緩存默認大小是64MB，最小值不能低於1MB。該緩存大小支持動態修改，具體配置方式，有兩種，比如直接修改postgresql.conf配置文件中的該選項值，或是使用ALTER SYSTEM命令，對於該命令的詳細使用方式請閱讀 一文徹底搞清 postgresql.auto.conf 與 postgresql.conf 之間的差異 。
#maintenance_work_mem = 64MB # min 1MB之後，PostgreSQL 將通過引用該緩存 (maintenance_work_mem) 中的死元組列表來刪除索引元組數據。當緩存 (maintenance_work_mem) 已滿，且掃描未完成時，則 PostgreSQL 會進入第(2)個步驟，執行該步驟的操作；之後返回到步驟(1)中，繼續執行掃描剩餘頁，獲取死元組，並將死元組放入緩存 (maintenance_work_mem) 中。
(2) 刪除死元組，更新 FSM 和 VM
刪除緩存中的所有死元組，並重新分配 (排列) 該表頁中的剩餘活元組，以進行碎片整理。之後，更新目標表的“可見性映射(Visibility Map，VM)” 文件和 “可用空間映射 (Free Space Map，FSM)” 文件。
(3) 更新統計信息和系統目錄
當 VACUUM 處理完成後，需要更新目標表 (以適應最新的查詢優化) 以及與 VACUUM 處理相關的一些統計信息 (比如pg_class) 和系統目錄 (如：pg_stat)。
2.2 VACUUM FULL
VACUUM FULL 和 VACUUM 最大的區別在於，VACUUM FULL 物理刪除了死元組，並且將釋放的空間歸還給了操作系統。其操作過程也可以劃分爲3個執行步驟。
(1) 首先會爲操作的目標表創建一個互斥鎖 (AccessExclusiveLock)，以阻止外部對該表的任何讀 / 寫訪問；然後再創建一個與目標表結構完全相同的新表。
#define AccessExclusiveLock 8 /* ALTER TABLE, DROP TABLE, VACUUM FULL,
* and unqualified LOCK TABLE */(2) 掃描目標表，並將表中的所有活元組複製到新表中。
(3) 刪除目標表，並在新表上創建索引，並更新 VM、FSM 和統計信息，以及相關係統表、系統目錄等。然後釋放互斥鎖 (AccessExclusiveLock)。
爲了驗證 VACUUM FULL 會刪除舊錶，創建新表，並將舊錶中的所有活元組複製到新創建的表文件中，我們進行一個小測試。在該命令執行之前，目標表的OID是25402。
現在我們 psql 的終端中執行VACUUM FULL test; 然後再觀察磁盤上面的堆表文件情況。發現舊錶文件25402已被刪除，而多了一個新表文件33576。
- 監控 VACUUM 進程
我們知道，在 PostgreSQL 中的刪除元組、更新元組操作並不會立刻刪除磁盤上的對應元組數據，因爲這樣做的同時提供了理想的ACID語義 (當其他事務仍然可以看見更新或刪除的行時，會使系統變得更加複雜，並且可能會更慢)。
(1) 對於
DELETE，它將現有的對應元組標記爲死元組 (dead tuple)，而不是物理刪除這些元組。(2) 對於
UPDATE，它將現有的對應元組標記爲死元組 (dead tuple)，同時插入一個新的元組；即UPDATE=DELETE+INSERT INTO。
因此，對於表中的每個更新 (UPDATE)、刪除 (DELETE) 操作都會產生一個死元組，而這些死元組永遠不會被使用 (除非存在並行事務)。即使有效元組的數量相同或更少，這些死元組也會導致不必要的額外空間使用，這在 PostgreSQL 中也稱爲空間膨脹。因此，我們迫切需要 VACUUM 進程來清理這些死元組。
所以，監控 VACUUM(自動化守護進程autovacuum) 的運行狀態就顯得極其重要。爲了確保autovacuum在 PostgreSQL 中正常運行，我們通常需要監控幾個指標，它們分別是：死元組數量、表磁盤使用率、上一次autovacuum時間等。
3.1 死元組數量
對於那些頻繁更新、刪除的表，跟蹤其表中死元組的數量將有助於我們去確定、分析autovacuum守護進程是否能夠有效地定期清理死元組功能，以便於重用這些死元組所佔用的磁盤空間。
PostgreSQL 提供了pg_stat_user_tables視圖，該視圖提供了庫中所有表的詳細信息，比如刪除的元組數量、死元組數量、或元組數量、最後一次ANALYZE時間、索引掃描次數等等。
比如對於test表，當前總數是750W，並且其中這750W元組都是活元組。同時可看到，有125W的死元組 (是因爲我前面執行DELETE 150W元組操作)。
現在我們刪除test表中1666667條元組，再次查看該視圖，發現活元組、死元組數量都有所改變，並且活元組的數量值等同於上一次的750W - 本次刪除的1666667；而死元組數量等於之前的1250000 + 本次刪除的1666667。
3.2 上一次 VACUUM 運行時間
如3.1節所述，pg_stat_user_tables視圖提供了查詢上一次在每個表上成功運行VACUUM和autovacuum的時間信息。比如當前庫中共有兩個表，分別是test和l，當執行VACUUM(不指明具體表) 之後，這兩個表都會同時被VACUUM執行清理死元組的邏輯操作。
當然也可以在VACUUM中指明具體的表名，關於VACUUM的可選項參數列表請閱讀下面的第4章節。
細心的你一定發現了本文中第1章節裏面的這段話：
每個工作進程將逐一檢查數據庫中的每個表，並在需要時，執行
VACUUM或ANALYZE操作。
注意這裏特意強調了 “並在需要時”。autovacuum默認每隔1min時間會啓動autovacuum worker工作進程，按理說pg_stat_user_tables視圖中的last_autovacuum字段的值，每隔1min時間也會隨着更新。然而實際情況並非如此，因爲它還需要滿足其他條件，即只有當死元組的總數量超過一定閾值時，autovacuum纔會將VACUUM啓動運行。
以下公式用於決定是否需要執行VACUUM：
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
默認情況下， 基本閾值 (base threshold) 是50行，比例因子 (scale factor) 是0.2(即20%)。這意味着只要死元組超過表總元組數的20%時，就會立即執行VACUUM，但前提是至少有50個元組被標記爲 “死元組”。
3.2.1 示例演示
爲了驗證這個結論，我們將test表清空，然後重新導入2000條記錄。之後刪除500條數據 (保證死元組數量超過表的20%)，觀察pg_stat_user_tables視圖中字段last_autovacuum的值。
(1) 插入2000條記錄
test=# INSERT INTO test(id, name, age) VALUES (generate_series(1, 2000), 'lixiaogang5', 27);
INSERT 0 2000
Time: 18.751 ms
test=# SELECT COUNT(*) FROM test;
count
-------
2000
(1 row)
Time: 0.722ms(2) 刪除主鍵id小於等於500的記錄。
test=# DELETE FROM test WHERE id <= 500;
DELETE 500
Time: 0.793 ms
test=# SELECT COUNT(*) FROM test;
count
-------
1500
(1 row)
Time: 0.376 ms現在查看pg_stat_user_tables視圖中字段last_vacuum、last_autovacuum的值，發現last_autovacuum字段的值已更新。
注意，默認情況下，目標表中的總記錄總數不能低於autovacuum_vacuum_insert_threshold(默認1000) 條，不然，即使死元組超過20%也不會執行autovacuum。更多與 VACUUM 相關的配置參數如下：
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
// Enable autovacuum subprocess? 'on' requires track_counts to also be on.
#autovacuum = on
// -1 disables, 0 logs all actions and their durations, > 0 logs only actions running at least this number of milliseconds.
#log_autovacuum_min_duration = -1
// max number of autovacuum subprocesses(change requires restart)
#autovacuum_max_workers = 3
// time between autovacuum runs
#autovacuum_naptime = 1min
// min number of row updates before vacuum
#autovacuum_vacuum_threshold = 50
// min number of row inserts before vacuum; -1 disables insert vacuums
#autovacuum_vacuum_insert_threshold = 1000
// min number of row updates before analyze
#autovacuum_analyze_threshold = 50
// fraction of table size before vacuum
#autovacuum_vacuum_scale_factor = 0.2
// fraction of inserts over table size before insert vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2
// fraction of table size before analyze
#autovacuum_analyze_scale_factor = 0.1
// maximum XID age before forced vacuum (change requires restart)
#autovacuum_freeze_max_age = 200000000
// maximum multixact age before forced vacuum (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000
// default vacuum cost delay for autovacuum, in milliseconds; -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_delay = 2ms
// default vacuum cost limit for autovacuum, -1 means use vacuum_cost_limit
#autovacuum_vacuum_cost_limit = -13.3 目標表磁盤使用空間
跟蹤每個目標表磁盤空間的使用情況很重要，因爲它能幫助你衡量查詢性能隨時間的預期變化，以及檢測潛在的與VACUUM相關的問題。向大量執行更新、刪除操作的目標表中添加許多新的數據記錄，卻發現該目標表文件使用的磁盤空間額外快速增大，這或許間接地表明autovacuum進程沒有在正常運行。因爲正常情況下，autovacuum會將表文件塊中的死元組標記爲 “可重用”，因此，若autovacuum運行異常，則新入庫的數據會佔用新的磁盤空間，而不是重用死元組的磁盤空間。
SELECT
relname AS "table_name",
pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)通過上面的語句查詢出來的每個表大小和實際的表文件的磁盤空間佔用大小是能夠相對應的。
3.3.1 確定 autovacuum 是否啓用
我們可以通過pg_settings視圖來查看autovacuum進程是否啓用。
test=# SELECT name, setting FROM pg_settings WHERE name='autovacuum';
name | setting
------------+---------
autovacuum | on
(1 row)
test=#如果autovacuum進程已開啓，但是服務器上找不到該進程，則可能是 “統計收集器” 沒有打開。因爲autovacuum進程的處理邏輯中，需要去檢查目標表中死元組的數量，而這個死元組的數量統計來自於統計收集器。
同樣，我們可以通過pg_settings視圖來查看統計收集器是否開啓。
test=# SELECT name, setting FROM pg_settings WHERE name='track_counts';
name | setting
--------------+---------
track_counts | on
(1 row)當然，也有可能是autovacuum進程在表的某個點被禁止了，可以通過pg_class系統表來查看autovacuum進程的啓用情況。若參數autovacuum_enabled的值爲false，則表明禁用了autovacuum進程。
我們可以通過ALTER TABLE . . .語句來重新開啓autovacuum進程。
test=# ALTER TABLE test SET (autovacuum_enabled = true);
ALTER TABLE
test=#3.3.2 通過 pg_settings 視圖查詢 autovacuum 選項參數
通過pg_settings視圖，結合模糊查詢，可以獲取與autovacuum進程相關的選項參數列表。這些選項配置也可以在postgresql.conf配置文件中找到，通過查詢pg_settings視圖，能夠快速幫你確認選項的參數值是多少。
- VACUUM 選項參數
使用psql實用程序登錄postmaster服務後，可以使用\d VACUUM命令來獲取 VACUUM 的使用格式以及所有選項參數列表。
test=# \h VACUUM
Command: VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
where option can be one of:
FULL [ boolean ]
FREEZE [ boolean ]
VERBOSE [ boolean ]
ANALYZE [ boolean ]
DISABLE_PAGE_SKIPPING [ boolean ]
SKIP_LOCKED [ boolean ]
INDEX_CLEANUP [ boolean ]
TRUNCATE [ boolean ]
PARALLEL integer
and table_and_columns is:
table_name [ ( column_name [, ...] ) ]- VACUUM 和 VACUUM FULL 的區別
VACUUM 和 VACUUM FULL 兩者的主要區別有如下幾點：
(1) VACUUM 執行時，不會鎖住表；而 VACUUM FULL 執行期間則會鎖住目標表，其他事務無法進行讀 / 寫，不允許並行操作。
(2) VACUUM 只是將目標表中的死元組的空間轉換爲可使用狀態；而 VACUUM FULL 則會刪除目標表，並釋放死元組佔用的磁盤空間，將其歸還給操作系統。理想情況下，PostgreSQL 的應用程序設計方案避免使用 VACUUM FULL 方式。首先它使用互斥鎖，會阻止其他一切嘗試操作該目標表的讀寫行爲；其次，由於它本質是上創建一個新的 (堆) 表文件，然後將所有活元組(live tuples) 導入到新表文件中，所以其執行速度比較慢，效率低。當然，優點是執行 VACUUM FULL 後其表文件只存在活元組，由於沒有多餘的死元組，因此，將大大提高查詢執行的效率。總而言之，若可以，則儘量不去使用 VACUUM FULL，除非你十分清楚目標表中大部分的元組都是死元組。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/5oMx-tWEUAYMEoJOuXOTeA