pg事务篇（四）—— vacuum官方文档

以下来自于pg 10官方文档24章，有删改，完整版参考：https://www.postgresql.org/docs/10/routine-vacuuming.html

官方文档对VACUUM 的定义是数据库垃圾回收及可选的数据库统计信息收集（VACUUM — garbage-collect and optionally analyze a database）

命令语法如下

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有两种：标准VACUUM和VACUUM FULL。

• 标准VACUUM基本可以online操作（DML运行正常，但不能执行ALTER TABLE），但能回收的磁盘空间很少。
• VACUUM FULL能回收更多的磁盘空间，但运行速度要慢得多；它需要对表加独占锁，因此不能与该表的其他操作并发进行；此外还需要额外空间存储表副本。

标准VACUUM

VACUUM FULL

因此，通常管理员应使用标准VACUUM，避免使用VACUUM FULL。VACUUM会产生大量IO，这可能会导致其他活动会话性能下降。有一些参数可以调整以减少vacuum对性能的影响，参考 https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

 

pg数据库需要定期维护，即所谓的清理。许多情况下，让autovacuum守护程序执行vacuum就足够了，一些DBA会希望用手动管理的VACUUM命令来补充或替换autovacuum（通常利用cron或Task Scheduler执行脚本）。为了正确设置手动管理的vacuum，必须理解接下来几个小节中讨论的问题。依靠自autovacuum的管理员也可阅读帮助理解和调整autovacuum。

 

VACUUM命令必须定期维护每个表，原因（vacuum的作用）如下：

• 释放或重用死元组占用的磁盘空间。
• 更新统计信息。
• 更新可见性映射，加快仅索引扫描。
• 防止事务ID或multixact ID回卷造成数据丢失。

每种作用都要求不同频率和范围的VACUUM操作，如以下小节所述。

 

① 释放磁盘空间

在pg中，由于MVCC需要，UPDATE或DELETE某行时不会立即删除该行的旧版本。但是当被删除的行版本已不再被任何事务需要时，必须回收它占用的空间以供新行重用，避免磁盘空间无限制地增长。

标准VACUUM可清理表和索引中的死元组，并将其标记为可重用空间。但是，它不会将空间返回给操作系统（除非表末尾的一个或多个页面变得完全空闲，并且可以轻松获得表独占锁）。相反，VACUUM FULL通过写全新版本的表文件主动压缩表，可以最大程度地减少表的大小，但是会花费很长时间。在操作完成之前，需要对表加独占锁，并且表的新副本还额外需要约等于表大小的磁盘空间。

通常选择经常做标准vacuum来避免需要做VACUUM FULL。autovacuum守护程序就尝试以这种方式工作，它永远不会发出VACUUM FULL。这种方法的思想不是将表保持在最小大小，而是保持磁盘空间的稳定使用——尽管VACUUM FULL可以用来将表缩到最小并将磁盘空间返回给操作系统，但是如果表将来会再次增长，则没有什么意义。因此，对于大量更新的表，中度频繁地运行标准VACUUM是比不频繁地运行VACUUM FULL更好的方法。

一些管理员更喜欢自己安排vacuum作业，例如在负载低的晚上进行所有工作。根据固定的时间进行清理的困难在于，如果表的更新意外大量增加，它可能会膨胀到真正有必要使用VACUUM FULL回收空间的程度。使用autovacuum可以缓解此问题，因为它会根据更新情况动态进行清理。除非您的工作负载非常可预测，否则完全禁用autovacuum是不明智的。一种折衷的办法是设置autovacuum的参数，使其仅对异常繁重的更新活动做出反应，避免事情失控，而在通常负载下，则使用计划的VACUUM完成大部分工作。

 

② 更新统计信息

pg执行计划生成器依赖于有关表内容的统计信息。统计信息由ANALYZE命令收集，该命令可以单独调用，也可以作为VACUUM中的可选步骤。拥有合理准确的统计信息很重要，执行计划选择不当可能降低数据库性能。

如果启用了autovacuum，当更改的表元组数达到阈值时，将自动发出ANALYZE命令。但是，管理员可能更喜欢依靠手动计划的ANALYZE操作，尤其是在已知表上的更新活动不会影响“ 感兴趣 ”列统计信息的情况下。autovacuum严格根据更改的元组数进行ANALYZE调度，而会不管统计信息的收集对业务是否有意义。

即使对于大量更新的表，如果数据的统计分布变化不大，也可能不需要更新统计信息。一个简单的经验法则是考虑表中列的最小值和最大值有多少变化。可以仅在特定表甚至仅在表的特定列上运行ANALYZE，因此，可以按需更频繁地进行更新某些统计信息。但是实际上通常最好只分析整个数据库，因为这是一个快速的操作。ANALYZE对表进行随机抽样，而不是读取每一行。

Tip

autovacuum不会对外部表发出ANALYZE命令，因为它无法确定可能有用的频率。如果您的查询需要有关外部表的统计信息，最好手动或定期运行ANALYZE命令。

 

③ 更新可见性映射

Vacuum为每个表维护一份可见性映射，以跟踪哪些页面仅包含对所有活动事务及未来事务可见的元组。

这有两个目的：

• vacuum可以在下一次运行时跳过此类页面，因为没有什么需要清理的。
• 允许pg仅使用索引进行查询，而无需引用基础表（不用回表）。

由于pg索引不包含元组可见性信息，普通索引扫描需要对每个匹配的元组回表检查它们对当前事务是否可见。而仅索引扫描会首先检查可视性映射，如果知道页面上的所有元组都是可见的，则可以跳过回表，减少大量磁盘访问。另外可见性映射远小于堆表，因此即使堆表很大，也可以轻松将其缓存。

 

④ 防止事务ID回卷失败

参考  https://blog.csdn.net/Hehuyi_In/article/details/102869893

 

⑤ autovacuum守护程序

pg中有一个可选的但强烈推荐的功能，称为autovacuum，即自动执行VACUUM and ANALYZE。启用后，自动清理将检查具有大量dml操作的表。这些检查使用统计信息收集工具，因此必须将track_counts设置为true，才能使用autovacuum。默认已启用autovacuum，并已正确设置了相关参数。

autovacuum守护程序实际上是多个进程——一个autovacuum launcher与多个autovacuum worker进程。

持久性守护进程autovacuum launcher（autovacuum启动器），负责启动所有数据库的autovacuum worker进程。

• 启动器进程定时工作，尝试在autovacuum_naptime秒内在每个数据库中都启动一个worker进程（如果有n个数据库，则每autovacuum_naptime/n秒会启动一个新的worker进程）。
• 最多autovacuum_max_workers个worker进程被允许在同一时间运行。如果db数超过autovacuum_max_workers，第一个worker进程完成工作后将立即处理下一个db。
• 每个worker进程将检查其数据库中的每个表，并根据需要执行VACUUM和/或ANALYZE操作。可以设置log_autovacuum_min_duration监视autovacuum worker的活动。如果几个大表在短时间内都符合vacuum的条件，可能所有的autovacuum worker都会被这些大表的vacuum操作长期占用，这将导致其他表和数据库在worker可用之前不会被清理。
• 单个数据库中可以有多少worker没有限制，但是worker会尝试避免重复其他worker已经完成的工作。
• 正在运行的worker数不计入max_connections或superuser_reserved_connections限制。
• relfrozenxid值超过autovacuum_freeze_max_age的表一定会被vacuum（也适用于已经通过存储参数修改最大冻结年龄的表，见下文）。
• 如果自上次VACUUM后过期的元组数量超过了vacuum阈值，表也会被vacuum。

 

vacuum阈值被定义为：

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

其中vacuum基本阈值为autovacuum_vacuum_threshold，vacuum比例因子为autovacuum_vacuum_scale_factor，元组数为pg_class.reltuples。过期的元组数从统计信息收集器中获得，它是在每次UPDATE和DELETE操作时会被更新的半精确计数（只是半精确，因为某些信息可能会在高负载下丢失）。如果表的relfrozenxid值大于vacuum_freeze_table_age，将执行vacuum操作以冻结旧元组并使relfrozenxid前进；否则，仅扫描自上次vacuum以来被修改过的页面。

 

分析阈值定义为：

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

它会与自上次ANALYZE之后的更改的总元组数进行比较。

默认阈值和比例因子取自postgresql.conf，也可以针对每个表设置，以覆盖全局参数。

当有多个worker在运行时，autovacuum成本延迟参数（请参见第19.4.4节）在所有正在运行的worker之间是平衡的 。因此，无论实际运行的worker数量如何，对系统的总IO影响都是相同的。但已覆盖全局参数设置了autovacuum_vacuum_cost_delay或autovacuum_vacuum_cost_limit参数的表不在平衡算法考虑之中。

另外注意临时表不能通过autovacuum访问，需要SQL执行适当的vacuum和analyze操作。

 

参考

https://www.postgresql.org/docs/10/routine-vacuuming.html

https://www.postgresql.org/docs/10/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

https://www.postgresql.org/docs/10/storage-vm.html

https://blog.csdn.net/Hehuyi_In/article/details/102925756

https://blog.csdn.net/Hehuyi_In/article/details/102926799

http://www.interdb.jp/pg/pgsql06.html