PostgreSql vacuum

持续创作,加速成长!这是我参与「掘金日新计划 · 6 月更文挑战」的第6天,点击查看活动详情

一、 概述

在 PostgreSQL 中,被删除或者被更新的元组并没有在物理上从它们的表中移除,它们将一直存在,直到一次 VACUUM 被执行。因此有必要周期性地做 VACUUM,特别是在频繁被更新的表上。

VACUUM 有如下几个作用:

  • 恢复或重用被已更新或已删除行所占用的磁盘空间。
  • 更新被PostgreSQL查询规划器使用的数据统计信息。
  • 更新可见性映射,它可以加速只用索引的扫描。
  • 保护老旧数据不会由于事务ID回卷或多事务ID回卷而丢失。

二、 语法

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

option 可以是下列之一:

FULL:“完全”清理,它可以收回更多空间,并且需要更长时间和表上的排他锁。这种方法需要额外的磁盘空间,因为它会创建该表的一个新拷贝,并且在操作完成之前都不会释放旧的拷贝。通常这种方法只用于需要从表中收回数量庞大的空间时。
FREEZE:选择激进的元组“冻结”。指定 FREEZE 等价于参数 vacuum_freeze_min_age 和 vacuum_freeze_table_age 设置为0的 VACUUM。当表被重写时总是会执行激进的冻结,因此指定 FULL 时这个选项是多余的。
ANALYZE:更新优化器用以决定最有效执行一个查询的方法的统计信息。
VERBOSE:为每个表打印一份详细的清理活动报告。
DISABLE_PAGE_SKIPPING:通常,VACUUM 将基于可见性映射跳过页面,被冻结的元组页面也总是会被跳过,这个选项禁用所有的跳过页面的行为,其意图是只在可见性映射内容被怀疑时使用,这种情况只有在硬件或者软件问题导致数据库损坏时才会发生。 SKIP_LOCKED:跳过锁等待。
INDEX_CLEANUP:默认 true,清理索引,如果需要尽快运行 VACUUM 操作的话,可将此选项设置为 false。此选项对于没有索引的表无效,如果使用 FULL 选项,则忽略此选项。
TRUNCATE:默认允许将清空页的磁盘空间返回到操作系统,将此选项设置为 false 可能有助于避免 ACCESS EXCLUSIVE 锁定需要清空的表,如果使用FULL选项,则忽略此选项。
PARALLEL:并行清理,该数量还受到 max_parallel_maintenance_workers 限制,当且仅当索引的大小大于 min_parallel_index_scan_size 时,索引才能参与并行清理,此选项不能与FULL选项一起使用。
table_name:要清理的表或物化视图的名称(可以有模式修饰)。如果指定的表示一个分区表,则它所有的叶子分区也会被清理。
column_name:要分析的指定列的名称。缺省是所有列。如果指定了一个列的列表,则ANALYZE也必须被指定。

三、 注意事项

  • VACUUM 不能在一个事务块内被执行。
  • 除非你希望在物理上收缩表以减少磁盘空间占用,否则不推荐日 FULL 选项。
  • PARALLEL 选项仅用于清理目的,如果此选项与 ANALYZE 选项一起指定,则不会影响ANALYZE。
  • VACUUM 会导致I/O流量的大幅度增加,这可能导致其他活动会话性能变差。因此,有时建议使用基于代价的清理延迟特性。
  • 日常运维推荐 autovacuum 工具,它可以自动进行例行的清理维护。

四、相关参数

vacuum_cleanup_index_scale_factor:触发 vacuum 操作清理索引的插入数据比例,默认 0.1,当新插入的数据行,超过上次收集统计信息总数据行的 10% 时,vacumm 操作会执行索引清理,否则跳过索引清理。(仅针对适用于 B 树索引),该值设置为 0 时,vacuum 操作不会跳过索引扫描清理。
vacuum_cost_delay:控制 vacuum 是否启用基于代价的清理延迟,默认值为 0,不启用,该值设置为正值时,启用。当代价达到 vacuum_cost_limit 参数所指定的值后,执行清理的 vacuum 进程会休眠 vacuum_cost_delay 参数所指定的时间。
vacuum_cost_limit:导致清理进程休眠的累计代价,默认值为200。
vacuum_cost_page_dirty:当清理修改一个之前干净的块时需要花费的估计代价。它表示再次把脏块刷出到磁盘所需要的额外I/O。默认值为20。
vacuum_cost_page_hit:清理一个在共享缓存中找到的缓冲区的估计代价。它表示锁住缓冲池、查找共享哈希表和扫描页内容的代价。默认值为1。
vacuum_cost_page_miss: 清理一个必须从磁盘上读取的缓冲区的代价。它表示锁住缓冲池、查找共享哈希表、从磁盘读取需要的块以及扫描其内容的代价。默认值为10。
vacuum_defer_cleanup_age:主从架构中,指定主库 vacuum 清除死亡行版本之前,应该推迟多久(以事务数量计),默认值 0,这允许备机上的查询,有更多时间来完成而不会由于先前的行清除产生冲突。比 old_snapshot_threshold 参数优先级低。
vacuum_freeze_min_age:指定 vacuum 操作的冻结年龄(当前事务id - 上一次冻结事务id)。默认值是 5 千万个事务。尽管用户可以将这个值设置为从 0 到 10 亿,VACUUM会悄悄地将有效值设置为 autovacuum_freeze_max_age 值的一半,这样在强制执行的自动清理之间不会有过短的时间间隔。
vacuum_freeze_table_age:当表的 pg_class.relfrozenxid 的年龄达到该设置指定的年龄时,vacuum 会执行一次激进的扫描。会访问每一个可能包含未冻结 XID 或者 MXID 的页面,而不只是那些可能包含死亡元组的页面。默认值是 1.5 亿个事务。尽管用户可以把这个值设置为从 0 到 20 亿,vacuum 会悄悄地将有效值设置为 autovacuum_freeze_max_age 值的95%,因此在表上启动一次反回卷自动清理之前有机会进行一次定期手动 vacuum。
vacuum_multixact_freeze_min_age:指定 vacuum 操作的冻结年龄(当前多事务id - 上一次冻结多事务id)。默认值是 5 千万个组合事务。尽管用户可以将这个值设置为从 0 到 10 亿,VACUUM会悄悄地将有效值设置为 autovacuum_multixact_freeze_max_age 值的一半,这样在强制执行的自动清理之间不会有过短的时间间隔。
vacuum_multixact_freeze_table_age:当表的 pg_class.relminmxid 的年龄超过了这个设置指定的年龄,vacuum 会执行一次激进的扫描。会访问每一个可能包含未冻结 XID 或者 MXID 的页面,而不是只扫描那些可能包含死亡元组的页面。默认值是 1.5 亿个组合事务。尽管用户可以把这个值设置为从 0 到 20 亿,vacuum 会悄悄地将有效值设置为 autovacuum_multixact_freeze_max_age 值的95%,因此在表上启动一次反回卷自动清理之前有机会进行一次定期手动 vacuum。

五、 示例

管理员用户连入数据库执行。
--清理数据库 db1,并打印详细过程。
\c - db1
vacuum (verbose);

--清理表 test,并收集其统计信息,打印详细过程。
vacuum (analyze,verbose) test;

--清理并统计表 test 中 id 字段。(必须带 analyze)
vacuum (analyze) test(id);

--查询 test 表上一次
select schemaname as "schema 名",relname as "表名",last_vacuum as "上次 vacuum 时间",last_analyze as "上次 analyze 时间" from pg_stat_all_tables where relname='test';

--查询 vacuum 进度
select * from pg_stat_progress_vacuum;