Postgresql關於Vacuum的作用和操作方法，Vacuum full鎖表並生成新的relfilenode
官方文件
http://postgres.cn/docs/11/sql-vacuum.html
http://postgres.cn/docs/11/routine-vacuuming.html
個人實驗得出的一些總結
1、官方文件說vacuum full會釋放磁碟空間，標準VACUUM(即不帶FULL)不會，標準VACUUM也能把該表檔案空間交還給作業系統的情況：表尾部有空頁就能釋放這個空頁的空間，頁包含頁頭部和行資料，也就是說如果每行資料都是死元組，那麼整個這個頁就是空閒的，實驗場景，當我們insert一張新建的表或全是死元組的表時，insert的時候都是往尾部插入(類似oracle的insert /*+ append */往高水位線以上插入)，而一旦delete整表後，則整表的所有頁裡面都是死元組，直接VACUUM 不帶FULL也能把這個頁就給回收空間了，見本文的實驗“delete後，執行vacuum，表的大小從1GB變成了0KB”
2、vacuum full 表的情況下，select沒法查詢表，select會被vacuum full堵塞
3、vacuum full 表會釋放表檔案對應的磁碟空間，因為表對應的pg_class.relfilenode都變了
在PostgreSQL中，一次行的UPDATE或DELETE不會立即移除該行的舊版本。這種方法對於從多版本併發控制MVCC獲益是必需的：即當舊版本仍可能對其他事務可見時，它不能被刪除。但是最後，任何事務都不會再對一個過時的或者被刪除的行版本感興趣。它所佔用的空間必須被回收來用於新行，這樣可避免磁碟空間需求的無限制增長。這透過執行VACUUM完成。
VACUUM的標準形式移除表和索引中的死亡行版本並將該空間標記為可在未來重用。不過，它將不會把該空間交還給作業系統，除非在特殊的情況中表尾部的一個或多個頁面變成完全空閒並且能夠很容易地得到一個排他表鎖。相反，VACUUM FULL透過把死亡空間之外的內容寫成一個完整的新版本表檔案來主動緊縮表。這將最小化表的尺寸，但是要花較長的時間。它也需要額外的磁碟空間用於表的新副本，直到操作完成。
例行清理的一般目標是多做標準的VACUUM來避免需要VACUUM FULL。自動清理守護程式嘗試這樣工作，並且實際上永遠不會發出VACUUM FULL。在這種方法中，其思想不是讓表保持它們的最小尺寸，而是保持磁碟空間使用的穩定狀態：每個表佔用的空間等於其最小尺寸外加清理之間被用完的空間。儘管VACUUM FULL可被用來把一個表收縮回它的最小尺寸並將該磁碟空間交還給作業系統，但是如果該表將在未來再次增長這樣就沒什麼意義。因此，對於維護頻繁被更新的表，適度執行標準VACUUM執行比少量執行VACUUM FULL要更好。
PostgreSQL有一個可選的但是被高度推薦的特性autovacuum，它的目的是自動執行VACUUM和ANALYZE 命令。當它被啟用時，自動清理會檢查被大量插入、更新或刪除元組的表。這些檢查會利用統計資訊收集功能，因此除非track_counts被設定為true，自動清理不能被使用。在預設配置下，自動清理是被啟用的並且相關配置引數已被正確配置。
“自動清理後臺程式”實際上由多個程式組成。有一個稱為 自動清理啟動器的常駐後臺程式， 它負責為所有資料庫啟動自動清理工作者程式。 啟動器將把工作散佈在一段時間上，它每隔 autovacuum_naptime秒嘗試在每個資料庫中啟動一個工作者 （因此，如果安裝中有N個資料庫，則每 autovacuum_naptime/N秒將啟動一個新的工作者）。 在同一時間只允許最多autovacuum_max_workers 個工作者程式執行。如果有超過autovacuum_max_workers 個資料庫需要被處理，下一個資料庫將在第一個工作者結束後馬上被處理。 每一個工作者程式將檢查其資料庫中的每一個表並且在需要時執行 VACUUM和/或ANALYZE。 可以設定log_autovacuum_min_duration 來監控自動清理工作者的活動。
如果在一小段時間內多個大型表都變得可以被清理，所有的自動清理工作者可能都會被佔用來在一段長的時間內清理這些表。這將會造成其他的表和資料庫無法被清理，直到一個工作者變得可用。對於一個資料庫中的工作者數量並沒有限制，但是工作者確實會試圖避免重複已經被其他工作者完成的工作。注意執行著的工作者的數量不會被計入max_connections或superuser_reserved_connections限制。
VACUUM收回由死亡元組佔用的儲存空間,VACUUM不能在一個事務塊內被執行。
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
其中option可以是下列之一：
FULL
FREEZE
VERBOSE
ANALYZE
DISABLE_PAGE_SKIPPING
而table_and_columns是：
table_name [ ( column_name [, ...] ) ]
VACUUM的階段
1、初始化：VACUUM正在準備開始掃描堆。這個階段應該很簡短。
2、掃描堆：VACUUM正在掃描堆。如果需要，它將會對每個頁面進行修建以及碎片整理，並且可能會執行凍結動作。heap_blks_scanned列可以用來監控掃描的進度。
3、清理索引：VACUUM當前正在清理索引。如果一個表擁有索引，那麼每次清理時這個階段會在堆掃描完成後至少發生一次。如果maintenance_work_mem不足以存放找到的死亡元組，則每次清理時會多次清理索引。
4、清理堆：VACUUM當前正在清理堆。清理堆與掃描堆不是同一個概念，清理堆發生在每一次清理索引的例項之後。如果heap_blks_scanned小於heap_blks_total，系統將在這個階段完成之後回去掃描堆；否則，系統將在這個階段完成後開始清理索引。
5、清除索引：VACUUM當前正在清除索引。這個階段發生在堆被完全掃描並且對堆和索引的所有清理都已經完成以後。
6、截斷堆：VACUUM正在截斷堆，以便把關係尾部的空頁面返還給作業系統。這個階段發生在清除完索引之後。
7、執行最後的清除：VACUUM在執行最終的清除。在這個階段中，VACUUM將清理空閒空間對映、更新pg_class中的統計資訊並且將統計資訊報告給統計收集器。當這個階段完成時，VACUUM也就結束了。
VACUUM命令在沒有table_and_columns列表的情況下，VACUUM會處理當前使用者具有清理許可權的當前資料庫中的每一個表和物化檢視。如果給出一個列表，VACUUM可以只處理列表中的那些表。
簡單的 VACUUM（不帶FULL）簡單地收回空間並使其可以被重用。這種形式的命令可以和表的普通讀寫操作並行，因為它不會獲得一個排他鎖。但是，這種形式中額外的空間並沒有被還給作業系統（在大多數情況下），它僅僅被保留在同一個表中以備重用。VACUUM FULL將表的整個內容重寫到一個新的磁碟檔案中，並且不包含額外的空間，這使得沒有被使用的空間被還給作業系統。這種形式的命令更慢並且在其被處理時要求在每個表上保持一個排他鎖。
簡單的VACUUM可以和生產資料庫操作並行執行（SELECT、INSERT、UPDATE和DELETE等命令將繼續正常工作，但在清理期間你無法使用ALTER TABLE等命令來更新表的定義）。
VACUUM FULL要求在其工作的表上得到一個排他鎖，因此無法和對此表的其他使用並行。
VACUUM命令的引數解釋
FULL
選擇“完全”清理，它可以收回更多空間，並且需要更長時間和表上的排他鎖。這種方法還需要額外的磁碟空間，因為它會建立該表的一個新複製，並且在操作完成之前都不會釋放舊的複製。通常這種方法只用於需要從表中收回數量龐大的空間時。
FREEZE
選擇激進的元組“凍結”。指定FREEZE 等價於引數vacuum_freeze_min_age和 vacuum_freeze_table_age設定為0的 VACUUM。當表被重寫時總是會執行激進的凍結， 因此指定FULL時這個選項是多餘的。
VERBOSE
為每個表列印一份詳細的清理活動報告。
ANALYZE
更新最佳化器用以決定最有效執行一個查詢的方法的統計資訊。
DISABLE_PAGE_SKIPPING
通常，VACUUM將基於可見性對映跳過頁面。已知所有元組都被凍結的頁面總是會被跳過，而那些所有元組對所有事務都可見的頁面則可能會被跳過（除非執行的是激進的清理）。此外，除非在執行激進的清理時，一些頁面也可能會被跳過，這樣可避免等待其他頁面完成對其使用。這個選項禁用所有的跳過頁面的行為，其意圖是隻在可見性對映內容被懷疑時使用，這種情況只有在硬體或者軟體問題導致資料庫損壞時才會發生。
table_name
要清理的表或物化檢視的名稱（可以有模式修飾）。如果指定的表示一個分割槽表，則它所有的葉子分割槽也會被清理。
column_name
要分析的指定列的名稱。預設是所有列。如果指定了一個列的列表，則ANALYZE也必須被指定。
輸出
如果宣告瞭VERBOSE，VACUUM會發出進度訊息來表明當前正在處理哪個表。各種有關這些表的統計資訊也會列印出來。
lukes0818=# select count(*) from t1;
count
---------
1999999
(1 row)
lukes0818=# SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname='t1' ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
public | t1 | 2000012 | 0
(1 row)
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58425 | 142858
(1 row)
lukes0818=# delete from t1;
DELETE 1999999
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58425 | 142858
(1 row)
[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.0G /var/lib/pgsql/pg/PG_11_201809051/58424/58425
93M /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
304K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
40K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm
lukes0818=# SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname='t1' ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
public | t1 | 13 | 1999999
(1 row)
lukes0818=# VACUUM (VERBOSE, ANALYZE) t1;
INFO: vacuuming "public.t1"
INFO: "t1": removed 377861 row versions in 26991 pages
INFO: "t1": found 377861 removable, 0 nonremovable row versions in 26991 out of 142858 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1472
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 115867 frozen pages.
0 pages are entirely empty.
CPU: user: 0.23 s, system: 0.19 s, elapsed: 5.11 s.
INFO: "t1": truncated 142858 to 0 pages
DETAIL: CPU: user: 0.80 s, system: 2.99 s, elapsed: 310.87 s
INFO: vacuuming "pg_toast.pg_toast_58425"
INFO: index "pg_toast_58425_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
INFO: "pg_toast_58425": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1473
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
INFO: analyzing "public.t1"
INFO: "t1": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM
[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
16K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm
lukes0818=# SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname='t1' ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------+---------+------------+------------
public | t1 | 0 | 0
(1 row)
查詢Vacuum的進度
postgres=# select * from pg_stat_progress_vacuum;
-[ RECORD 1 ]------+----------------
pid | 14850
datid | 58424
datname | lukes0818
relid | 58425
phase | truncating heap
heap_blks_total | 142858
heap_blks_scanned | 142858
heap_blks_vacuumed | 142858
index_vacuum_count | 0
max_dead_tuples | 291
num_dead_tuples | 1
執行vacuum full的同時，無法執行select，select會被堵塞
會話1
lukes0818=# do $$
declare
v_idx integer := 1;
begin
while v_idx < 2000000 loop
v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
end loop;
end $$;
DO
lukes0818=# delete from t1;
DELETE 1999999
lukes0818=# vacuum full t1;
會話2
lukes0818=# select * from t1 limit 1;
會話3
lukes0818=# select a.locktype,b.datname,a.pid,a.mode,a.granted,regclass(a.relation),regclass(a.classid) from pg_locks a join pg_database b on a.database=b.oid and a.granted<>'t';
locktype | datname | pid | mode | granted | regclass | regclass
----------+-----------+-------+-----------------+---------+----------+----------
relation | lukes0818 | 26820 | AccessShareLock | f | t1 |
lukes0818=# select query from pg_stat_activity where pid=26820;
query
----------+
select * from t1 limit 1; | client backend
每次vacuum full都會重新生成relfilenode
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58464 | 142858
(1 row)
lukes0818=# vacuum full t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58470 | 142858
(1 row)
lukes0818=# truncate table t1;
TRUNCATE TABLE
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58476 | 0
(1 row)
lukes0818=# vacuum t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58476 | 0
(1 row)
lukes0818=# vacuum full t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
pg_relation_filepath | relpages
---------------------------------------------+----------
pg_tblspc/50003/PG_11_201809051/58424/58480 | 0
(1 row)
lukes0818=# select oid,relname,relfilenode from pg_class WHERE relname = 't1';
oid | relname | relfilenode
-------+---------+-------------
58425 | t1 | 58480
(1 row)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2793305/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL vacuum原理—vacuum揭秘SQL
- PostgreSQL 並行vacuum patch - 暨為什麼需要並行vacuum或分割槽表SQL並行
- 新特性：postgresql的vacuum漫談SQL
- PostgreSQL的vacuum流程SQL
- 【Postgresql】VACUUM 垃圾回收SQL
- PostgreSQL vacuum可見性SQL
- PostgreSQL Cost Based Vacuum探秘SQL
- PostgreSQL VACUUM 之深入淺出 (一)SQL
- PostgreSQL VACUUM 之深入淺出 (二)SQL
- PostgreSQL VACUUM 之深入淺出 (三)SQL
- PostgreSQL vacuum 核心原始碼機理SQL原始碼
- PostgreSQL vacuum原理—啟動機制SQL
- postgresql VACUUM 不會從表中刪除死行的三個原因SQL
- PostgreSQL DBA(92) - PG 12 Improving VACUUMSQL
- PostgreSQL vacuum原理一功能與引數SQL
- PostgreSQL DBA(142) - PG 12（Monitoring PostgreSQL VACUUM processes）SQL
- PostgreSQL 原始碼解讀（127）- MVCC#11（vacuum過程-vacuum_rel函式）SQL原始碼MVCC#函式
- postgreSQL 12-2 vacuum-主流程SQL
- PostgreSQL 原始碼解讀（131）- MVCC#15（vacuum過程-lazy_vacuum_heap函式）SQL原始碼MVCC#函式
- PostgreSQL 原始碼解讀（132）- MVCC#16（vacuum過程-lazy_vacuum_index函式#1）SQL原始碼MVCC#Index函式
- PostgreSQL 原始碼解讀（128）- MVCC#12（vacuum過程-heap_vacuum_rel函式）SQL原始碼MVCC#函式
- PostgreSQL 原始碼解讀（133）- MVCC#17（vacuum過程-lazy_vacuum_index函式#2）SQL原始碼MVCC#Index函式
- PostgreSQL DBA(143) - pgAdmin（Monitoring PostgreSQL VACUUM processes#2）SQL
- PostgreSQL 原始碼解讀（129）- MVCC#13（vacuum過程-vacuum_set_xid_limits函式）SQL原始碼MVCC#MIT函式
- 教你使用SQLite VacuumSQLite
- 技術應用丨DWS 空間釋放(vacuum full) 最佳實踐
- PostgreSQL DBA(159) - pgAdmin(Allow vacuum command to process indexes in paralleSQLIndex
- 第二週-20200306-PostgreSQL13並行vacuum索引SQL並行索引
- PostgreSQL 原始碼解讀（264）- PG 14（Speeding up recovery and VACUUM）SQL原始碼
- PostgreSQL 原始碼解讀（126）- MVCC#10（vacuum過程）SQL原始碼MVCC#
- PostgreSQL 原始碼解讀（134）- MVCC#18（vacuum過程-HeapTupleSatisfiesVacuum函式）SQL原始碼MVCC#APT函式
- PostgreSQL 原始碼解讀（125）- MVCC#9（vacuum-主流程）SQL原始碼MVCC#
- Postgresql驗證_update、delete產生死亡元組，標準vacuum釋放表檔案磁碟空間的場景SQLdelete
- Three Sets of Vacuum Degassers Awaiting ShipmentAI
- PostgreSQL14在做vacuum時候的邏輯判斷是否經過HEAPTUPLE_RECENTLY_DEADSQLAPT
- 深入淺出VACUUM核心原理(中): index by passIndex
- 關於ORACLE的鎖表與解鎖總結Oracle
- PG12中新增：VACUUM命令的SKIP_LOCKED選項