对于一个查询,不是每一个人都会得到相同的返回数据。除此之外,DELETE
和UPDATE
不能直接覆盖数据,因为那样ROLLBACK
将无法工作。如果用户恰好处于一次大型DELETE
操作的中间,他将不能确定是否能够COMMIT
。还有,当用户执行DELETE
时,涉及的数据仍然是可见的,甚至有时在修改完成很久以后数据都是可见的。
这意味着清理不得不异步地发生。这个问题的解决方案就是VACUUM:
postgres=# \h VACUUM
Command: VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE | DISABLE_PAGE_SKIPPING } [, ...] ) ] [ table_name [ (column_name [, ...] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, ...] ) ] ]
VACUUM将访问所有可能包含修改的页面并且找出所有的死亡空间。找到的空闲空间交由该关系的 空闲空间映射(FSM) 跟踪。
配置VACUUM和autovacuum
autovacuum会自动地处理清理并且在后台工作,它每一分钟(见postgresql.conf中的autovacuum_naptime = 1)醒来一次,并且检查是否有工作要做。如果有工作要做,将最多有3个(见postgresql.conf中的autovacuum_max_workers = 3)工作者进程。
autovacuum进程并不自己派生进程,实际上,它会告诉主进程来做这件事。这是为了防止在失败的情况下产生僵尸进程,并且提高鲁棒性。
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_scale_factor告诉PostgreSQL:如果一个表中20%的数据以及被改过,那么它就值得被清理。如果一个表只有一行,一次更改就已经是100%,派生出一个完整的进程来只清理一行是没有意义的。因此,autovacuum_vacuum_threshold会要求我们需要20%的修改并且那20%必须有至少50行。在进行优化器统计信息创建时也使用了同样的机制,至少需要10%的修改并且至少50行才能触发对优化器统计信息的更新。
在理想情况下,autovacuum会在一次普通的VACUUM中创建新的统计信息以避免对表不必要的访问。
观察工作中的VACUUM
在大部分情况下,VACUUM将不会收缩你的表,空间通常不会被交还给文件系统。
创建一个包含100000行的简单表,并检查尺寸:
CREATE TABLE t_test (
id int
)
WITH
(
autovacuum_enabled = off
);
INSERT INTO t_test
SELECT
*
FROM
generate_series(1, 100000);
SELECT
pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
3544 kB
(1 row)
更新表中所有行并再次检查:
UPDATE
t_test
SET
id = id + 1;
SELECT
pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
7080 kB
(1 row)
执行VACUUM后再次检查:
VACUUM t_test ;
SELECT
pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
7080 kB
(1 row)
可见,VACUUM并没有把空间还给文件系统。它允许空间被重用,因此,下一次UPDATE
不会让该表长大。只有再一次的UPDATE
会让该表再次长大。
UPDATE
t_test
SET
id = id + 1;
SELECT
pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
7080 kB
(1 row)
UPDATE
t_test
SET
id = id + 1;
SELECT
pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
10 MB
(1 row)
让我们运行更多的SQL:
VACUUM t_test;
UPDATE
t_test
SET
id = id + 1;
VACUUM t_test;
看看表里有什么:
SELECT
ctid,
*
FROM
t_test
ORDER BY ctid DESC;
ctid | id
-----------+--------
(1327,46) | 112
(1327,45) | 111
(1327,44) | 110
...
(884,20) | 99798
(884,19) | 99797
...
ctid是行在磁盘上的物理位置。通过使用ORDER BY ctid DESC
,用户将按照物理顺序从后向前读到该表。为什么要关心这一点?原因是在该表的末尾有一些非常小的值和非常大的值【注1】。如果它们被删除会发生什么?
DELETE FROM t_test
WHERE
id > 99000
OR id < 1000;
VACUUM t_test ;
SELECT
pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
3504 kB
(1 row)
尽管只有2%的数据被删除,该表的尺寸却下降了三分之二。原因在于,如果VACUUM只在该表中的特定位置之后寻找死亡行,它可以把空间还给文件系统。这是唯一一种可以看到表尺寸下降的情况。
【注1】:为什么有这么多小值和大值在该表的末尾?在该表最初被100000个行填充之后,最后一个块并没有被完全填满,因此第一次的UPDATE
将填满最后一块。这自然把该表的末尾搅乱了一点。
利用snapshot too old
VACUUM什么时候才能真正清理掉行并且把它们转变成空闲空间?规则是这样:如果一行再也不能被任何人看见,它就能被回收。这意味着最老的事务都看不见的部分就可以被认为是真正死亡了。
这还意味着长事务可以把清理推迟相当长的时间,由此带来的后果就是表膨胀。表将会超比例增长并且性能将会趋向于衰退。
为了限制快照的生存时间,可以使用postgresql.conf中的一个设置:
old_snapshot_threshold = -1
# 1min-60d; -1 disables; 0 is immediate
如果设置了这个变量,事务将在一定时间之后失败。注意这个设置是在实例级别,并且不能在会话中设置。