PostgreSQL的MVCC — 6. VACUUM

飞象数据 2022-07-15

838

我们从与隔离相关的问题开始，对底层数据结构展开了题外话，然后讨论了行版本并观察了如何从行版本中获取数据快照。

上次我们讨论了热点更新和页内vacuum，今天我们将继续讨论一个众所周知的vacuum常识。真的，已经写了太多关于它的内容，我几乎无法添加任何新内容，但是完美需要牺牲。所以保持耐心。

VACUUM

VACUUM有什么作用?

页内Vacuum运行速度很快，但仅释放部分空间。它在一个表页内工作，并且不涉及索引。

基本的“正常”Vacuum是使用 VACUUM 命令完成的，我们将其简称为“vacuum”（将“autovacuum”单独讨论）。

因此，vacuum处理整个表。它不仅会清除消亡元组，还会清除所有索引中对它们的引用。

vacuum与系统中的其他动作同时进行。表和索引可以以常规方式用于读取和更新（但是，并发执行诸如 CREATE INDEX、ALTER TABLE 和其他一些命令是不可能的）。

仅查看那些发生某些活动的表页。为了检测它们，使用了可见性映射（提醒您，该映射跟踪那些包含非常旧的元组的页面，这些元组肯定在所有数据快照中都可见）。只处理那些不被可见性映射跟踪的页面，并且映射本身会得到更新。

在这个过程中，空闲空间映射也会得到更新，以反映页面中额外的空闲空间。

像往常一样，让我们创建一个表：

=> CREATE TABLE vac(
  id serial,
  s char(100)
) WITH (autovacuum_enabled = off);
=> CREATE INDEX vac_s ON vac(s);
=> INSERT INTO vac(s) VALUES ('A');
=> UPDATE vac SET s = 'B';
=> UPDATE vac SET s = 'C';
复制

我们关闭autovacuum_enabled参数，此时autovacuum 进程关闭。我们将在下次讨论，现在手动控制vacuum对我们的实验至关重要。

该表现在有三个元组，每个元组都从索引中引用：

=> SELECT * FROM heap_page('vac',0);
 ctid  | state  |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+--------+----------+----------+-----+-----+--------
 (0,1) | normal | 4000 (c) | 4001 (c) |     |     | (0,2)
 (0,2) | normal | 4001 (c) | 4002     |     |     | (0,3)
 (0,3) | normal | 4002     | 0 (a)    |     |     | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
 itemoffset | ctid  
------------+-------
          1 | (0,1)
          2 | (0,2)
          3 | (0,3)
(3 rows)
复制

vacuum后，消亡元组被清空，只剩下一个活元组。索引中只剩下一个引用：

=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  |   xmin   | xmax  | hhu | hot | t_ctid 
-------+--------+----------+-------+-----+-----+--------
 (0,1) | unused |          |       |     |     | 
 (0,2) | unused |          |       |     |     | 
 (0,3) | normal | 4002 (c) | 0 (a) |     |     | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
 itemoffset | ctid  
------------+-------
          1 | (0,3)
(1 row)
复制

请注意，前两个指针获得了状态“未使用”而不是“消亡”，它们将通过页内vacuum获得。

再次关于处理范围

PostgreSQL 如何确定哪些元组可以被认为是消亡的？我们在讨论数据快照https://postgrespro.com/blog/pgsql/5967899时已经触及了事务范围的概念，但重申这样一个重要的事情也无妨。

让我们重新开始之前的实验。

=> TRUNCATE vac;
=> INSERT INTO vac(s) VALUES ('A');
=> UPDATE vac SET s = 'B';
复制

但是在再次更新该行之前，让另一个事务开始（但不结束）。在这个例子中，它将使用 Read Committed 级别，但它必须获得一个真实的（不是虚拟的）事务号。例如，事务可以更改甚至锁定任何表中的某些行，而不是强制性的vac
：

|  => BEGIN;
|  => SELECT s FROM t FOR UPDATE;
|    s  
|  -----
|   FOO
|   BAR
|  (2 rows)
=> UPDATE vac SET s = 'C';
复制

现在表中有三行，索引中有三个引用。vacuum后会发生什么？

=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+--------+----------+----------+-----+-----+--------
 (0,1) | unused |          |          |     |     | 
 (0,2) | normal | 4005 (c) | 4007 (c) |     |     | (0,3)
 (0,3) | normal | 4007 (c) | 0 (a)    |     |     | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
 itemoffset | ctid  
------------+-------
          1 | (0,2)
          2 | (0,3)
(2 rows)
复制

表中还剩下两个元组：VACUUM 决定 (0,2) 元组还不能被清空。原因当然是在数据库的事务范围内，在这个例子中是由未完成的事务决定的：

|  => SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();
|   backend_xmin 
|  --------------
|           4006
|  (1 row)
复制

我们可以要求 VACUUM 报告正在发生的事情：

=> VACUUM VERBOSE vac;
INFO:  vacuuming "public.vac"
INFO:  index "vac_s" now contains 2 row versions in 2 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.00 s.
INFO:  "vac": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL:  1 dead row versions cannot be removed yet, oldest xmin: 4006
There were 1 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.00 s.
VACUUM
复制

注意：

2 nonremovable row versions
- 表中找到两个无法删除的元组。
1 dead row versions cannot be removed yet
- 其中一个已经消亡。
oldest xmin
显示当前范围。

让我们重申一下结论：如果数据库有长期事务（未完成或执行时间很长），这可能会导致表膨胀，无论清空发生的频率如何。因此，OLTP 和 OLAP 类型的工作负载很难在一个 PostgreSQL 数据库中共存：运行数小时的报表不会让更新的表被适当地清理。为报表目的创建一个单独的副本可能是一个可能的解决方案。

完成一个开放事务后，范围移动，情况得到解决：

|  => COMMIT;
=> VACUUM VERBOSE vac;
INFO:  vacuuming "public.vac"
INFO:  scanned index "vac_s" to remove 1 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  "vac": removed 1 row versions in 1 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "vac_s" now contains 1 row versions in 2 pages
DETAIL:  1 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.00 s.
INFO:  "vac": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4008
There were 1 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.00 s.
VACUUM
复制

现在页面中只剩下该行的最新、实时版本：

=> SELECT * FROM heap_page('vac',0);
 ctid  | state  |   xmin   | xmax  | hhu | hot | t_ctid 
-------+--------+----------+-------+-----+-----+--------
 (0,1) | unused |          |       |     |     | 
 (0,2) | unused |          |       |     |     | 
 (0,3) | normal | 4007 (c) | 0 (a) |     |     | (0,3)
(3 rows)
复制

索引也只有一行：

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | ctid  
------------+-------
          1 | (0,3)
(1 row)
复制

里面会发生什么？

Vacuuming 必须同时处理表和索引，并且这样做以免锁定其他进程。怎么能这样呢？

一切都从扫描堆阶段开始（如前所述，考虑了可见性映射）。在读取的页面中，检测到消亡元组，并将它们的tid
s 写入专门的数组。该数组存储在vacuum 进程的本地内存中，其中为它分配了maintenance_work_mem字节的内存。此参数的默认值为 64 MB。请注意，全部内存量是一次性分配的，而不是在需要时分配。但是，如果表不大，则分配的内存量较小。

然后我们要么到达表的末尾，要么为数组分配的内存结束。在任何一种情况下，清空索引阶段都会开始。为此，在表上创建的每个索引都被完全扫描以搜索引用所记住的元组的行。找到的行从索引页中清除。

在这里，我们面临以下问题：索引还没有对消亡元组的引用，而表仍然有它们。这与没有相反：在执行查询时，我们要么不命中消亡元组（使用索引访问），要么在可见性检查时拒绝它们（扫描表时）。

之后，清空堆阶段开始。再次扫描该表以读取适当的页面，将它们从记住的元组中抽真空并释放指针。我们可以这样做，因为不再有来自索引的引用。

如果在第一个循环中没有完全读取该表，则该数组将被清除，并且从我们到达的位置开始重复所有内容。

总之：

该表总是被扫描两次。
如果vacuum删除了太多元组，以至于它们都无法放入大小为maintenance_work_mem 的内存中，则所有索引将根据需要进行多次扫描。

对于大型表，这可能需要大量时间并增加相当大的系统工作量。当然，查询不会被锁定，但额外的输入/输出肯定是不可取的。

为了加快这个过程，更频繁地调用 VACUUM （这样每次都不会有太多元组被清理掉）或分配更多内存是有意义的。

在括号中要注意的是，从版本 11 开始，PostgreSQL可以跳过索引扫描https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=857f9c36cda520030381bd8c2af20adf0ce0e1d4，除非出现令人信服的需求。对于只添加（但不更改）行的大表的所有者来说，这必须使他们的工作更容易。

监控

我们如何确定 VACUUM 不能在一个周期内完成它的工作？

我们已经看到了第一种方法：使用 VERBOSE 选项调用 VACUUM 命令。在这种情况下，有关进程阶段的信息将输出到控制台。

其次，从 9.6 版开始，该pg_stat_progress_vacuum
视图可用，它还提供了所有必要的信息。

（第三种方式也可以：将信息输出到消息日志中，但这仅适用于autovacuum，这将在下次讨论。）

让我们在表中插入相当多的行，以便vacuum过程持续很长时间，让我们更新所有行，以便 VACUUM 做一些事情。

=> TRUNCATE vac;
=> INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000);
=> UPDATE vac SET s  = 'B';
复制

让我们减少为标识符数组分配的内存大小：

=> ALTER SYSTEM SET maintenance_work_mem = '1MB';
=> SELECT pg_reload_conf();
复制

让我们启动 VACUUM，在它工作时，让我们pg_stat_progress_vacuum
多次访问视图：

=> VACUUM VERBOSE vac;
|  => SELECT * FROM pg_stat_progress_vacuum \gx
|  -[ RECORD 1 ]------+------------------
|  pid                | 6715
|  datid              | 41493
|  datname            | test
|  relid              | 57383
|  phase              | vacuuming indexes
|  heap_blks_total    | 16667
|  heap_blks_scanned  | 2908
|  heap_blks_vacuumed | 0
|  index_vacuum_count | 0
|  max_dead_tuples    | 174762
|  num_dead_tuples    | 174480
|  => SELECT * FROM pg_stat_progress_vacuum \gx
|  -[ RECORD 1 ]------+------------------
|  pid                | 6715
|  datid              | 41493
|  datname            | test
|  relid              | 57383
|  phase              | vacuuming indexes
|  heap_blks_total    | 16667
|  heap_blks_scanned  | 5816
|  heap_blks_vacuumed | 2907
|  index_vacuum_count | 1
|  max_dead_tuples    | 174762
|  num_dead_tuples    | 174480
复制

在这里我们可以看到，特别是：

当前阶段的名称 - 我们讨论了三个主要阶段，但总的来说还有更多https://postgrespro.com/docs/postgresql/11/progress-reporting#VACUUM-PHASES。
表页总数 ( heap_blks_total
)。
扫描的页数 ( heap_blks_scanned
)。
已清空的页面数 ( heap_blks_vacuumed
)。
索引vacuum循环次数 ( index_vacuum_count
)。

一般的进程是由比例确定heap_blks_vacuumed
到heap_blks_total
，但该值在大增量的变化，而不是平稳，因为扫描索引，我们应该考虑到。不过主要注意的是vacuum的循环次数：大于1表示分配的内存不足以在一个周期内完成vacuum。

到那时已经完成的 VACUUM VERBOSE 命令的输出将显示一般显示：

INFO:  vacuuming "public.vac"
INFO:  scanned index "vac_s" to remove 174480 row versions
DETAIL:  CPU: user: 0.50 s, system: 0.07 s, elapsed: 1.36 s
INFO:  "vac": removed 174480 row versions in 2908 pages
DETAIL:  CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.13 s
INFO:  scanned index "vac_s" to remove 174480 row versions
DETAIL:  CPU: user: 0.26 s, system: 0.07 s, elapsed: 0.81 s
INFO:  "vac": removed 174480 row versions in 2908 pages
DETAIL:  CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.10 s
INFO:  scanned index "vac_s" to remove 151040 row versions
DETAIL:  CPU: user: 0.13 s, system: 0.04 s, elapsed: 0.47 s
INFO:  "vac": removed 151040 row versions in 2518 pages
DETAIL:  CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.08 s
INFO:  index "vac_s" now contains 500000 row versions in 17821 pages
DETAIL:  500000 index row versions were removed.
8778 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "vac": found 500000 removable, 500000 nonremovable row versions in 16667 out of 16667 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4011
There were 0 unused item pointers.
0 pages are entirely empty.
CPU: user: 1.10 s, system: 0.37 s, elapsed: 3.71 s.
VACUUM
复制

我们可以在这里看到对索引进行了三个循环，并且在每个循环中，174480 个指向消亡元组的指针被清空了。为什么是这个数字？一个tid
占6个字节，1024*1024/6 = 174762，就是我们在pg_stat_progress_vacuum.max_dead_tuples
. 实际上，可能会使用更少：这确保在读取下一页时，所有指向消亡元组的指针肯定会适合内存。

分析

分析，或者换句话说，为查询规划器收集统计信息，在形式上与vacuum完全无关。尽管如此，我们不仅可以使用 ANALYZE 命令执行分析，还可以在 VACUUM ANALYZE 中将vacuum和analyze结合起来。这里先完成vacuum，然后是analyze，所以这没有任何好处。

但正如我们稍后将看到的，自动vacuum和自动analyze是在一个过程中完成的，并以类似的方式进行控制。

VACUUM FULL

如上所述，vacuum比页内vacuum释放更多空间，但仍不能完全解决问题。

如果由于某种原因表或索引的大小增加了很多，VACUUM 将释放现有页面内的空间：“洞”将出现在那里，然后将用于插入新元组。但是页数不会改变，因此，从操作系统的角度来看，文件将占用与vacuum之前完全相同的空间。这不好，因为：

表（或索引）的完整扫描会减慢速度。
可能需要更大的缓冲区缓存（因为页面存储在那里并且有用信息的密度降低）。
在索引树中可能会出现一个额外的级别，这会减慢索引访问速度。
这些文件在磁盘和备份副本中占用额外空间。

（唯一的例外是完全清理的页面，位于文件的末尾。这些页面从文件中删除并返回到操作系统。）

如果文件中有用信息的份额低于某个合理的限制，管理员可以对表执行 VACUUM FULL。在这种情况下，表及其所有索引从头开始重建，数据以最紧凑的方式打包（当然，fillfactor
参数考虑在内）。在重建过程中，PostgreSQL 首先重建表，然后一一重建它的每个索引。对于每个对象，都会创建新文件，并在重建结束时删除旧文件。我们应该考虑到在此过程中将需要额外的磁盘空间。

为了说明这一点，让我们再次向表中插入一定数量的行：

=> TRUNCATE vac;
=> INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000);
复制

我们如何估计信息密度？为此，使用专门的扩展很方便：

=> CREATE EXTENSION pgstattuple;
=> SELECT * FROM pgstattuple('vac') \gx
-[ RECORD 1 ]------+---------
table_len          | 68272128
tuple_count        | 500000
tuple_len          | 64500000
tuple_percent      | 94.47
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 38776
free_percent       | 0.06
复制

该函数读取整个表并显示统计信息：哪些数据在文件中占用了多少空间。我们现在感兴趣的主要信息是tuple_percent
字段：有用数据的百分比。由于页面内部不可避免的信息开销，它小于 100，但仍然相当高。

对于索引，输出的信息不同，但avg_leaf_density
字段含义相同：有用信息的百分比（以叶页为单位）。

=> SELECT * FROM pgstatindex('vac_s') \gx
-[ RECORD 1 ]------+---------
version            | 3
tree_level         | 3
index_size         | 72802304
root_block_no      | 2722
internal_pages     | 241
leaf_pages         | 8645
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 83.77
leaf_fragmentation | 64.25
复制

这些是表和索引的大小：

=> SELECT pg_size_pretty(pg_table_size('vac')) table_size,
  pg_size_pretty(pg_indexes_size('vac')) index_size;
 table_size | index_size 
------------+------------
 65 MB      | 69 MB
(1 row)
复制

现在让我们删除所有行的 90%。我们随机选择要删除的行，因此很可能在每一页中至少保留一行：

=> DELETE FROM vac WHERE random() < 0.9;
DELETE 450189
复制

VACUUM 后表的尺寸是多少？

=> VACUUM vac;
=> SELECT pg_size_pretty(pg_table_size('vac')) table_size,
  pg_size_pretty(pg_indexes_size('vac')) index_size;
 table_size | index_size 
------------+------------
 65 MB      | 69 MB
(1 row)
复制

我们可以看到大小没有变化：VACUUM 没办法减小文件的大小。这是虽然信息密度下降了大约 10 倍：

=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
 tuple_percent | avg_leaf_density 
---------------+------------------
          9.41 |             9.73
(1 row)
复制

现在让我们检查一下 VACUUM FULL 后我们得到了什么。现在表和索引使用以下文件：

=> SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s');
 pg_relation_filepath | pg_relation_filepath 
----------------------+----------------------
 base/41493/57392     | base/41493/57393
(1 row)
=> VACUUM FULL vac;
=> SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s');
 pg_relation_filepath | pg_relation_filepath 
----------------------+----------------------
 base/41493/57404     | base/41493/57407
(1 row)
复制

这些文件现在已替换为新文件。表和索引的大小大大减少，而信息密度相应增加：

=> SELECT pg_size_pretty(pg_table_size('vac')) table_size,
  pg_size_pretty(pg_indexes_size('vac')) index_size;
 table_size | index_size 
------------+------------
 6648 kB    | 6480 kB
(1 row)
=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
 tuple_percent | avg_leaf_density 
---------------+------------------
         94.39 |            91.08
(1 row)
复制

请注意，索引中的信息密度甚至比原来的还要大。从可用数据重建索引（B 树）比在现有索引中逐行插入数据更有利。

我们使用的pgstattuple https://postgrespro.com/docs/postgresql/11/pgstattuple 扩展函数读取整个表。但是如果表很大的话这很不方便，所以扩展有pgstattuple_approx
功能，跳过可见性映射中标记的页面并显示近似数字。

另一种但更不准确的方法是使用系统目录来粗略估计数据大小与文件大小的比率。您可以在 wiki 中https://wiki.postgresql.org/wiki/Show_database_bloat找到此类查询的示例。

VACUUM FULL 不适合常规使用，因为它会在整个过程期间阻止对表的任何工作（包括查询）。很明显，对于一个经常使用的系统，这似乎是不可接受的。锁将单独讨论，现在我们只提到pg_repack https://github.com/reorg/pg_repack 扩展，它在工作结束时只将表锁定一小段时间。

类似命令

有一些命令也完全重建表和索引，因此类似于 VACUUM FULL。所有这些都完全阻止了对该表的任何工作，它们都删除了旧数据文件并创建了新数据文件。

CLUSTER 命令与 VACUUM FULL 完全相似，但它也根据可用索引之一对元组进行物理排序。这使规划器在某些情况下可以更有效地使用索引访问。但是我们应该记住，集群是不维护的：元组的物理顺序会随着表的后续变化而被破坏。

REINDEX 命令在表上重建一个单独的索引。VACUUM FULL 和 CLUSTER 实际上使用这个命令来重建索引。

TRUNCATE 命令的逻辑类似于 DELETE - 它删除所有表行。但是，正如前面提到的，DELETE 只将元组标记为已删除，这需要进一步清理。而 TRUNCATE 只是创建一个新的、干净的文件。通常，这执行更快，但我们应该注意 TRUNCATE 将阻止对表的任何操作，直到事务结束。

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。