Geekly articles weekly

MVCC-6。 清洁用品

我们从与隔离相关的问题开始，对低级组织数据进行了讨论，然后详细讨论了行版本以及如何从版本中获取快照 。

上次我们谈论热更新和页内清洁，今天我们来看看著名的普通清洁真空吸尘器 。 是的，关于她的报道已经太多了，我不太可能再说什么了，但是照片的完整性需要牺牲。 要有耐心。

正常清洁（真空）


清洁是做什么的


页内清洁速度很快，但只释放了一小部分空间。 它在同一表格页面中工作，并且不影响索引。

主要的“常规”清洁是由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';

但是在再次更新该行之前，让另一个事务开始（但不结束）。 在我们的示例中，它将在“读取已提交”级别上工作，但是应该获得真实（非虚拟）交易编号。 例如，它可以更改甚至锁定任何表中的某些行，而不必锁定在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)

表中剩余的行有两个版本：清理确定尚不能删除版本（0.2）。 原因当然在于数据库事务范围，在我们的示例中，这是由不完整的事务确定的：

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

您可以要求清洁以谈论发生了什么：

 => 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个不可删除的行版本-在表格中找到2个无法删除的版本，
  • 目前尚无法删除1个死行版本-包括1个“死行”版本，
  • 最旧的xmin显示当前范围。

我们再次重复该结论：数据库中存在长寿命事务（未完成或运行时间很长）可能导致（膨胀）表的扩展，而不考虑执行清理的频率。 因此，在PostgreSQL中，OLTP和OLAP工作负载在一个数据库中的组合很差：运行数小时的报告将不允许及时清除频繁更新的表。 一种可能的解决方案是创建一个单独的“报告”副本。

在完成未结交易后，视界转移并且情况得到纠正：

 | => 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内存中，则将根据需要对所有索引进行完全扫描多次。

在大型桌子上，这可能会花费大量时间，并给系统造成很大的负担。 当然，请求不会被阻止，但是“额外”的I / O也令人不快。

为了加快该过程，有意义的是要么更频繁地进行清理（这样每次不会清除大量的行版本），要么分配更多的内存。

我在括号中指出，从11版开始，如果不是绝对必要，PostgreSQL 可以跳过索引扫描 。 对于仅添加（但不更改）行的大型表的所有者，这将使生活变得更轻松。

监控方式


如何理解清洁不能一pass而就？

我们已经看到了第一种方法：您可以使用VERBOSE调用VACUUM命令。 然后，有关工作阶段的信息将显示在控制台上。

其次，从9.6版开始，有一个视图pg_stat_progress_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();

我们开始清理，并且在工作的同时，将多次转到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）；
  • 索引的通过次数（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

在这里，您可以看到总共有3次遍历索引，每个遍历清除了174,480个指向无效版本字符串的指针。 这个数字从哪里来？ 一个链接（tid）占用6个字节，而我们在pg_stat_progress_vacuum.max_dead_tuples中看到的数字为1024 * 1024/6 = 174762。 实际上，它可以使用的更少一些：可以保证在阅读下一页时，所有指向“死”版本的指针都将完全适合内存。

分析方法


分析或换句话说，查询计划者的统计信息的收集与清理没有正式联系。 但是，我们不仅可以与ANALYZE团队一起执行分析，还可以将清洗与分析相结合：VACUUM ANALYZE。 在这种情况下，首先执行清洁，然后进行分析-不会产生任何节省。

但是，正如我们稍后将看到的，自动清洁和自动分析是在一个过程中执行的，并且以类似的方式进行管理。

全面清洁（真空已满）


如我们所见，常规清洁比页面内清洁释放更多的空间，但即使这样也不能始终完全解决问题。

如果由于某种原因，表或索引的大小显着增加，则定期清理将释放现有页面内的空间：它们将包含“孔”，然后将这些孔用于插入新版本的行。 但是页数不会改变，因此，从操作系统的角度来看，文件将占用与清理之前完全相同的空间。 这很不好，因为：

  • 完全扫描表（或索引）会减慢速度；
  • 可能需要更大的缓冲区高速缓存（因为存储了页面，并且有用信息的密度降低了）；
  • 索引树中可能会出现“额外”级别，这将减慢索引访问；
  • 文件占用额外的磁盘空间并进行备份。

（唯一的例外是文件末尾的完全清除的页面-这些页面“咬住”文件并返回到操作系统。）

如果文件中有用信息的份额低于合理限制，则管理员可以执行全表清理。 同时，该表及其所有索引都完全从头开始重建，并且数据尽可能紧凑地打包（当然要考虑到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)

我们看到大小没有改变：定期清理无法以任何方式减小文件的大小。 尽管信息密度明显降低了约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)

现在检查完全清除后会发生什么。 这是表和索引现在使用的文件：

 => 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，它会跳过可见性图中标记的页面并显示近似数字。

甚至更快但更不准确的方法是估计系统目录中数据量与文件大小的比率。 这些查询的选项可以在Wiki上找到。

完全清除不需要常规使用，因为它会在整个工作期间完全阻止表的所有工作（包括查询表）。 显然，在积极使用的系统上，这可能是不可接受的。 锁将被单独考虑，但是现在我们将仅提及pg_repack扩展，该扩展在工作结束时仅在短时间内锁定表。

类似团队


有几个命令也可以完全重建表和索引，这类似于完全清除。 它们全部完全阻塞了表的工作，它们都删除了旧数据文件并创建了新数据文件。

CLUSTER命令在所有方面都与VACUUM FULL相似，但是还根据可用索引之一在物理上安排了字符串的版本。 在某些情况下，这使调度程序能够更有效地使用索引访问。 但是，应该理解，不支持群集：对表进行后续更改时，将违反行版本的物理顺序。

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

TRUNCATE命令在逻辑上与DELETE相同-它删除所有表行。 但是，正如已经讨论过的，DELETE只将行的版本标记为已删除，这需要进一步清除。 TRUNCATE只会创建一个新的干净文件。 通常，这会更快地工作，但请记住，TRUNCATE将在整个过程中完全阻塞表的工作，直到事务结束。

待续 。

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

More articles:


All Articles