Postgresql关于Vacuum的作用和操作方法，Vacuum full锁表并生成新的relfilenode

zhousenshan

11157人浏览 · 2022-04-16 21:41:47

zhousenshan · 2022-04-16 21:41:47 发布

官方文档
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)