Geekly articles weekly

PostgreSQL-6中的MVCC。 真空度

我们从与隔离有关的问题开始，对低级数据结构进行了论述，然后讨论了行版本，并观察了如何从行版本中获取数据快照 。

上一次我们讨论HOT更新和页内吸尘，今天我们将继续进行众所周知的真空寻常 。 确实，关于它的文章已经写得太多了，我几乎无法添加任何新内容，但是要获得全貌的美丽，就需要付出牺牲。 因此，请保持耐心。

真空度


真空做什么？


页内真空工作速度很快，但仅释放了一部分空间。 它可以在一个表页面中工作，并且不涉及索引。

基本的“正常”真空是使用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参数关闭自动清理过程。 下次我们将讨论它，现在手动控制抽真空对于我们的实验至关重要。

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

 => 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 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)

请注意，前两个指针获得的状态为“未使用”，而不是“死”状态，它们将通过页内真空状态获得。

再次关于交易范围


PostgreSQL如何确定哪些元组可以视为已死？ 在讨论数据快照时 ，我们已经提到了事务视界的概念，但是重申这一重要问题并没有什么坏处。

让我们再次开始上一个实验。

 => 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 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删除的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)

内部会发生什么？


清理必须同时处理表和索引，并执行此操作，以免锁定其他进程。 怎么做呢？

所有步骤都从扫描堆阶段开始（已经提到了可见性图）。 在读取的页面中，检测到死元组，并将其tid记入专用数组。 数组存储在真空过程的本地内存中，在该内存中为其分配了maintenance_work_mem字节的内存。 此参数的默认值为64 MB。 请注意，将立即分配全部内存，而不是根据需要分配。 但是，如果表不大，则会分配较少的内存。

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

在这里，我们面临以下问题：索引还没有对死元组的引用，而表中仍然有它们。 这与什么都没有相反：执行查询时，我们要么不击中死的元组（具有索引访问权限），要么在可见性检查时不扫描它们（扫描表时）。

此后， 清理堆阶段开始。 再次扫描该表以读取适当的页面，将已记住的元组吸走它们并释放指针。 因为不再有索引的引用，所以我们可以这样做。

如果在第一个周期中未完全读取该表，则会清除该数组，并从到达的位置重复所有操作。

总结：

  • 该表始终被扫描两次。
  • 如果清理删除了太多的元组，以致它们都无法容纳在大小为maintenance_work_mem的内存中，则将根据需要对所有索引进行多次扫描。

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

为了加快处理速度，可以更频繁地调用VACUUM（这样就不会每次清理掉太多的元组），或者分配更多的内存。

在括号中要注意的是，从版本11开始，PostgreSQL 可以跳过索引扫描，除非迫切需要。 对于仅添加（但不更改）行的大型表的所有者来说，这必须使生活变得更轻松。

监控方式


我们如何确定VACUUM无法在一个周期内完成其工作？

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

其次，从9.6版开始，可以使用pg_stat_progress_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

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

  • 当前阶段的名称-我们讨论了三个主要阶段，但总体上有更多阶段。
  • 表页的总数（ heap_blks_total ）。
  • 扫描的页数（ heap_blks_scanned ）。
  • 已heap_blks_vacuumed页面数（ heap_blks_vacuumed ）。
  • 分度真空循环数（ index_vacuum_count ）。

总体进度由heap_blks_vacuumed与heap_blks_total的比率确定，但我们应考虑到此值由于扫描索引而以较大的增量而不是平滑地变化。 但是，应主要注意真空循环的次数：大于1的数字表示分配的内存不足以在一个循环中完成抽真空。

到那时已经完成的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看到的pg_stat_progress_vacuum.max_dead_tuples 。 实际上，可能会使用更少的代码：这确保了在读取下一页时，所有指向无效元组的指针肯定会容纳在内存中。

分析方法


分析，换句话说，为查询计划者收集统计信息，与形式上的清理完全无关。 但是，我们不仅可以使用ANALYZE命令执行分析，而且可以在VACUUM ANALYZE中结合抽真空和分析。 这里先进行真空处理，然后进行分析，因此不会产生任何收益。

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

真空已满


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

如果由于某种原因，表或索引的大小增加了很多，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 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扩展功能读取了整个表。 但是，如果表很大，这将pgstattuple_approx ，因此扩展名具有pgstattuple_approx函数，该函数将跳过可见性图中标记的页面并显示近似数字。

另一种方法，但准确性更低，是使用系统目录粗略估计数据大小与文件大小的比率。 您可以在Wiki中找到此类查询的示例。

VACUUM FULL不能用于常规用途，因为它在整个过程的整个过程中都会阻止该表的任何工作（包括查询）。 显然，对于使用率很高的系统，这似乎是不可接受的。 锁将单独讨论，现在我们仅提及pg_repack扩展，该扩展在工作结束时仅将表锁定一小段时间。

类似命令


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

CLUSTER命令与VACUUM FULL完全相似，但它实际上还会根据可用索引之一对元组进行排序。 这使计划人员在某些情况下可以更有效地使用索引访问。 但是我们应该记住，不能保持聚类：元组的物理顺序将随着表的后续更改而中断。

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

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

继续阅读 。

Source: https://habr.com/ru/post/zh-CN484106/

More articles:


All Articles