PG中的vacuum和vacuumdb命令

3    3640    12

Tags:

👉 本文共约21073个字,系统预计阅读时间或需80分钟。

VACUUM简介

VACUUM — 垃圾收集并根据需要分析一个数据库。

PostgreSQL数据库管理工作中,定期vacuum是一个重要的工作。

vacuum的效果:

  1. 释放,再利用 更新/删除的行所占据的磁盘空间.
  2. 更新POSTGRESQL查询计划中使用的统计数据
  3. 防止因事务ID的重置而使非常老的数据丢失。

第一点的原因是PostgreSQL数据的更新和删除操作并不是真正放到数据库空间.如果不定期释放空间的话,由于数据太多,查询速度会巨降.
第二点的原因是PostgreSQL在做查询处理的时候,为了使查询速度提高,会根据统计数据来确定执行计划.如果不及时更新的话,查询的效果可能不如预期.
第三点的原因是PostgreSQL中每一个事务都会产生一个事务ID,但这个数字是有上限的. 当事务ID达到最大值后,会重新从最小值开始循环.这样如果不及时把以前的数据释放掉的话,原来的老数据会因为事务ID的丢失而丢失掉.

虽然在新版本的Postgresql中有自动的vacuum,但是如果是大批量的数据IO可能会导致自动执行很慢,需要配合手动执行以及自己的脚本来清理数据库。

注意:

  • vacuum可以与dml并行运行,但不能与ddl并行运行

  • vacuum full不能与dml、ddl并行运行

  • 允许VACUUM并行处理表的索引,方法是通过PARALLEL参数控制是否开启并行vacuum索引。

  • vacuumdb命令可以并行,指定--parallel选项。

  • reindexdb命令可以并行,指定--jobs选项。

VACUUM命令

描述

VACUUM收回由死亡元组占用的存储空间。在通常的PostgreSQL操作中,被删除或者被更新废弃的元组并没有在物理上从它们的表中移除,它们将一直存在直到一次VACUUM被执行。因此有必要周期性地做VACUUM,特别是在频繁被更新的表上。

在没有table_and_columns列表的情况下,VACUUM会处理当前用户具有清理权限的当前数据库中的每一个表和物化视图。如果给出一个列表,VACUUM可以只处理列表中的那些表。

VACUUM ANALYZE对每一个选定的表ANALYZE。这是两种命令的一种方便的组合形式,可以用于例行的维护脚本。其处理细节可参考ANALYZE

简单的 VACUUM(不带FULL)简单地收回空间并使其可以被重用。这种形式的命令可以和表的普通读写操作并行,因为它不会获得一个排他锁。但是,这种形式中额外的空间并没有被还给操作系统(在大多数情况下),它仅仅被保留在同一个表中以备重用。它还允许我们利用多个 CPU 来处理索引。 此功能称为parallel vacuum。要禁用此功能,可以使用PARALLEL选项并将并行工作程序指定为零。

VACUUM FULL将表的整个内容重写到一个新的磁盘文件中,并且不包含额外的空间,这使得没有被使用的空间被还给操作系统。这种形式的命令更慢并且在其被处理时要求在每个表上保持一个排他锁。

当选项列表被包围在圆括号中时,选项可以被写成任何顺序。如果没有圆括号,选项必须严格按照上面所展示的顺序指定。有圆括号的语法在PostgreSQL 9.0时被加入,无圆括号的语法则被废弃。

参数

  • FULL

    选择“完全”清理,它可以收回更多空间,并且需要更长时间和表上的排他锁。这种方法还需要额外的磁盘空间,因为它会创建该表的一个新拷贝,并且在操作完成之前都不会释放旧的拷贝。通常这种方法只用于需要从表中收回数量庞大的空间时。

  • FREEZE

    选择激进的元组“冻结”。指定FREEZE 等价于参数vacuum_freeze_min_agevacuum_freeze_table_age设置为0的 VACUUM。当表被重写时总是会执行激进的冻结, 因此指定FULL时这个选项是多余的。

  • VERBOSE

    为每个表打印一份详细的清理活动报告。

  • ANALYZE

    更新优化器用以决定最有效执行一个查询的方法的统计信息。

  • DISABLE_PAGE_SKIPPING

    通常,VACUUM将基于可见性映射跳过页面。已知所有元组都被冻结的页面总是会被跳过,而那些所有元组对所有事务都可见的页面则可能会被跳过(除非执行的是激进的清理)。此外,除非在执行激进的清理时,一些页面也可能会被跳过,这样可避免等待其他页面完成对其使用。这个选项禁用所有的跳过页面的行为,其意图是只在可见性映射内容被怀疑时使用,这种情况只有在硬件或者软件问题导致数据库损坏时才会发生。

  • SKIP_LOCKED

    规定VACUUM在开始处理关系时不等待任何冲突锁被释放:如果关系不能立即锁定而不等待,则跳过关系。 请注意即使采用此选项,VACUUM在打开关系的索引时仍可能阻塞。 此外,VACUUM ANALYZE在从分区、继承子表和某些类型的外表获取示例行时,仍然可能阻塞。 还有,虽然VACUUM通常处理指定分区表的所有分区,但如果分区表上的锁冲突, 此选项将导致VACUUM跳过所有分区。

    在pg12中,VACUUM命令新增了SKIP_LOCKED选项。SKIP_LOCKED选项的作用是,vacuum命令会跳过那些被lock住的table,并且此时vacuum命令会被看做是成功执行。在之前的版本中,vacuum命令遇到了lock住的table时,vacuum命令会一直在等待。

    在第一个session中执行lock table操作:

    然后在第二个session中进行vacuum测试:

  • INDEX_CLEANUP

    规定VACUUM尝试删除指向死元组的索引条目。 这通常是所需的行为,并且是默认行为,除非将vacuum_index_cleanup 选项设置为 false,对要被清空的表。 如果需要尽快运行清空操作的话,将此选项设置为 false 可能很有用,例如,为了避免即将发生的事务 ID 回绕[wraparound](请参阅 第 24.1.5 节)。 但是,如果不定期执行索引清理,性能可能会受到影响,因为随着表的修改,索引将累积死元组,并且表本身将累积死行指针,在索引清理完成之前都无法删除。 此选项对于没有索引的表无效,如果使用 FULL 选项,则忽略此选项。

  • TRUNCATE

    指定VACUUM尝试截断表末尾的任何空页,并允许将截断页的磁盘空间返回到操作系统。 这通常是所需的行为,并且是默认行为,除非将vacuum_truncate选项设置为 false,对要被清空的表。 将此选项设置为 false 可能有助于避免ACCESS EXCLUSIVE锁定需要截断的表。如果使用FULL选项,则忽略此选项。

    在pg12中,vacuum带有TRUNCATE选项。当vacuum(TRUNCATE off)时,vacuum不会对该table中尾部的free area区域进行回收,若是vacuum不带truncate选项,那么需要看该table的VACUUM_TRUNCATE属性设置。

  • PARALLEL

    使用integer后台处理器并行执行VACUUM 的索引真空和索引清理阶段(每个真空阶段的详细信息请参考表 27.25))。 用于执行操作的处理器数量等于关系上支持并行清理的索引数量,该数量受PARALLEL 选项指定的工人数量的限制,如果有的话,该数量还受到 max_parallel_maintenance_workers 限制。 当且仅当索引的大小大于min_parallel_index_scan_size时,索引才能参与并行清理。 请注意,不保证在执行期间会使用integer中指定的并行工作线程数。 清理运行时可能需要比指定的更少的处理器,甚至根本没有处理器。每个索引只能使用一名处理器。 所以只有当表中至少有2索引时才会启动并行工作程序。 在每个阶段开始之前启动清理工作进程,并在阶段结束时退出。这些行为可能会在未来的版本中发生变化。 此选项不能与FULL选项一起使用。

  • boolean

    指定打开还是关闭所选选项。你可以写入TRUEON1以启用该选项,以及FALSEOFF0来禁用它。 在TRUE被假定的情况下,boolean 值也可以被省略。

  • integer

    指定传递给所选选项的非负整数值。

  • table_name

    要清理的表或物化视图的名称(可以有模式修饰)。如果指定的表示一个分区表,则它所有的叶子分区也会被清理。

  • column_name

    要分析的指定列的名称。缺省是所有列。如果指定了一个列的列表,则ANALYZE也必须被指定。

输出

如果声明了VERBOSEVACUUM会发出进度消息来表明当前正在处理哪个表。各种有关这些表的统计信息也会打印出来。

注意

要清理一个表,操作者通常必须是表的拥有者或者超级用户。但是,数据库拥有者被允许清理他们的数据库中除了共享目录之外的所有表(对于共享目录的限制意味着一个真正的数据库范围的VACUUM只能被超级用户执行)。VACUUM将会跳过执行者不具备清理权限的表。

VACUUM不能在一个事务块内被执行。

对具有GIN索引的表,VACUUM(任何形式)也会通过将待处理索引项移动到主要GIN索引结构中的合适位置来完成任何待处理的索引插入。详见第 66.4.1 节

我们建议经常清理活动的生产数据库(至少每晚一次),以保证移除失效的行。在增加或删除了大量行之后, 对受影响的表执行VACUUM ANALYZE命令是一个很好的做法。这样做将把最近的更改更新到系统目录,并且允许 PostgreSQL查询规划器在规划用户查询时做出更好的选择。

日常使用时,不推荐FULL选项,但在特殊情况时它会有用。一个例子是当你删除或者更新了一个表中的绝大部分行时,如果你希望在物理上收缩表以减少磁盘空间占用并且允许更快的表扫描,则该选项是比较合适的。VACUUM FULL通常会比简单VACUUM更多地收缩表。

PARALLEL选项仅用于清理目的。如果此选项与ANALYZE选项一起指定,则不会影响ANALYZE

VACUUM会导致I/O流量的大幅度增加,这可能导致其他活动会话性能变差。因此,有时建议使用基于代价的清理延迟特性。 对于并行清理,每个处理器的睡眠与该处理器完成的工作成比例。详情请参阅第 19.4.4 节

PostgreSQL包括了一个“autovacuum”工具,它可以自动进行例行的清理维护。关于自动和手动清理的更多信息请见第 24.1 节

例子

清理单一表onek,为优化器分析它并且打印出详细的清理活动报告:

vacuumdb命令

vacuumdb — 对一个PostgreSQL数据库进行垃圾收集和分析。

  1. vacuumdb 是 SQL 命令 VACUUM的封装,所以用vacuumdb和vacuum来清理数据库都可以,效果是一样的。

  2. vacuumdb 中的几个重要参数:可以用vacuumdb --help查询。

  3. 切换到postgres用户下:

    来清理你的数据库。或者加到crontab中

    每天的一点一刻开始进行清理。

  4. 如何查询我的XID是否接近临界值的命令:

  5. 然而我们关心的是哪一个大的表组要真正的vacuum:

    这个命令是查询按照最老的XID排序,查看大于1G而且是排名前20的表。

大纲

vacuumdb [connection-option...] [option...] [ --table | -t table [( column [,...] )] ] ... [dbname]

描述

vacuumdb是用于清理一个PostgreSQL数据库的工具。vacuumdb也将产生由PostgreSQL查询优化器所使用的内部统计信息。

vacuumdb是 SQL 命令VACUUM的一个包装器。在通过这个工具和其他方法访问服务器来清理和分析数据库之间没有实质性的区别。

选项

vacuumdb接受下列命令行参数:

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

3 条回复

  1. Avatar photo 小麦苗说道:

  2. Avatar photo David.cui说道:

    是不是应该描述的时候顺便解释一下和maintenance_work_mem的关系

发表回复