GreenPlum管理数据库表和索引的膨胀及VACUUM命令

简介

Greenplum数据库的堆表使用PostgreSQL的多版本并发控制（MVCC）存储实现。被删除或更新的行被从数据库逻辑 删除，但是该行的一个不可见映像保留在表中。这些被删除的行（也被称为过期行）被存储在一个空闲空间映射文件中。 运行VACUUM会把过期行标记为可以被后续插入重用的空闲空间。

如果空闲空间映射不足以容纳所有的过期行，VACUUM命令就不能从导致空闲空间映射溢出的 过期行回收空间。磁盘空间只能通过运行VACUUM FULL恢复，这个操作会锁住表，逐行拷贝到 文件的开头，然后截断文件。这是一种昂贵的操作，对于大型的表，它可能需要超乎想象的时间来完成。应该只在较小 的表上使用这种操作。如果使用者尝试杀死VACUUM FULL操作，系统可能会损坏。

Important:

• 在大量的的UPDATE以及DELETE操作之后非常有必要运行VACUUM， 这样可以避免运行VACUUM FULL。

• 如果空闲空间映射溢出并且需要恢复空间，推荐使用CREATE TABLE…AS SELECT命令把该表拷贝为 一个新表，这将会创建一个新的紧凑的表。然后删除原始表并且重命名拷贝的表为原始表名。

• 对于频繁更新的表来说，有少量或者中等数量的过期行以及空闲空间很正常，空闲空间将随着新数据的加入而被重用。 但是当表被允许增长得非常大以至于活动数据只占空间的一小部分时，该表就明显地“膨胀”了。膨胀的表要求更多磁盘 存储以及可能拖慢查询执行的额外I/O。

• 膨胀影响堆表、系统目录和索引。

• 在表上定期运行VACUUM语句可以防止它们长得过大。如果表确实出现了明显的膨胀，必须使用 VACUUM FULL语句（或者可替代的过程）来紧缩文件。如果一个大型表变得明显膨胀，更好的方法 是使用从数据库表移除膨胀中描述的方法 之一来移除膨胀。

CAUTION:

不要在Greenplum 数据库中的大型表上运行VACUUM FULL。

检测膨胀（查询膨胀）

ANALYZE语句所收集的统计信息可以被用来计算存储一个表所要求的磁盘页面的预计数量。页面的 预计数量和实际数量之间的差别就是膨胀的度量。gp_toolkit模式提供了一个gp_bloat_diag 视图，它通过预计页数和实际页数的比率来确定表膨胀。要使用这个视图，确定为数据库中所有的表都收集了最新的统计 信息。

然后运行下面的SQL：

其结果只包括发生了中度或者明显膨胀的表。当实际页面数和预期页面的比率超过4但小于10时，就会报告为中度膨胀。 当该比率超过10时就会报告明显膨胀。

gp_toolkit.gp_bloat_expected_pages视图会为每个数据库对象列出其已用页面的实际数量 和预期数量。

btdrelid是该表的对象ID。btdrelpages列报告该表使用的页面数， btdexppages列是预期的页面数。另外，报出的数字是基于表统计信息的，因此要确保在已经 被更改的表上运行ANALYZE。

从数据库表移除膨胀

VACUUM命令会把过期行加入到共享的空闲空间映射中，这样这些空间能被重用。当在被频繁 更新的表上定期运行VACUUM时，过期行所占用的空间可以被迅速地重用，从而防止表文件长得 更大。在空闲空间映射被填满之前运行VACUUM也很重要。对于更新密集的表，用户可能需要每 天运行VACUUM至少一次来防止表膨胀。

Warning: 当表出现显著膨胀时，在运行ANALYZE之前先运行VACUUM 会更好。如果采样包含空的数据页，分析膨胀表会生成不合适的统计信息，所以在分析表之前先做VACUUM 是最好的选择。

当表积累了显著的膨胀时，运行VACUUM命令并不能起到明显作用。对于小型表，运行 VACUUM FULL 能够回收导致空闲空间映射溢出的行所使用的空间并且减小表 文件的尺寸。不过，VACUUM FULL语句是一种昂贵的操作，它要求一个ACCESS EXCLUSIVE 锁并且可能需要异常长的时间完成。比起在一个大型表上运行VACUUM FULL，采用另一种方法从 大型文件中移除膨胀会更好。注意每一种从大型表中移除膨胀的方法都是资源密集型的，并且只应该在极端情况下完成。

第一种从大型表中移除膨胀的方法是创建一个将过期行排除在外的表拷贝，删掉原始的表并且把这个拷贝重命名为原来 的表名。这种方法使用CREATE TABLE AS SELECT语句创建新表，例如：

第二种从表移除膨胀的方法是重新分布该表，这会把该表重建为不含过期行的表。参考步骤如下：

1. 把表的分布列记下来。

2. 把该表的分布策略改为随机分布：

   ALTER TABLE mytable SET WITH (REORGANIZE=false) DISTRIBUTED randomly;

   1 2	ALTER TABLE mytable SET WITH (REORGANIZE=false)                DISTRIBUTED randomly;

   这会为该表更改分布策略，但不会移除任何数据。该命令应该会立即完成。

3. 将分布策略改回其初始设置：

   ALTER TABLE mytable SET WITH (REORGANIZE=true) DISTRIBUTED BY (<original distribution columns>);

   1 2	ALTER TABLE mytable SET WITH (REORGANIZE=true) DISTRIBUTED BY (<original distribution columns>);

   这一步会重新分布数据。因为表之前是用同样的分布键分布的，表中的行只需要简单地在同一Segment上重写 即可，同时排除过期行。

从索引移除膨胀

VACUUM命令只会从表中恢复空间。要从索引中恢复空间，需要使用REINDEX命令重建它们。 The VACUUM command only recovers space from tables. To recover the space from indexes, recreate them using the REINDEX command.

要在一个表上重建所有的索引，可运行REINDEX table_name;。要重建一个特定的索引， 可运行REINDEX index_name;。REINDEX会将该索引相关 reltuples和relpages的值设置为0（零），如果要更新统计信息， 则有必要在重建索引后运行ANALYZE来更新它们。

从系统目录（元数据）移除膨胀

Greenplum数据库系统目录也是堆表并且也可能随着时间推移变得膨胀。随着数据库对象被创建、修改或者删除， 过期行会留在系统目录中。使用gpload装载数据会加剧膨胀，因为gpload 会创建并且删除外部表。（为了避免使用gpload，推荐使用gpfdist装载数据。）

系统目录中的膨胀会导致扫描表所需的时间增加，例如在创建执行计划时需要扫描系统目录。系统目录会被频繁扫描， 那么如果它们变得膨胀，整体的系统性能都会退化。

推荐每晚在系统目录上运行VACUUM，或者至少每周运行一次。同时，运行REINDEX SYSTEM 从索引中移除膨胀。此外，还可以使用带-s（--system）选项的 reindexdb工具对系统目录重建索引。在移除系统目录膨胀后，还有必要运行ANALYZE 以更新系统目录表的统计信息。

以下是Greenplum数据库系统目录维护步骤。

1. 在系统目录表上执行REINDEX操作用于重建系统目录索引。该操作可以移除索引膨胀并提高 VACUUM性能。

   Note: 当在系统目录表上执行REINDEX操作时，会锁住相应的表，进而影响到当前正在执行 的查询性能。用户可以在系统的非活动窗口时间来调用REINDEX命令重建索引，以避免打扰 正常业务操作的进行。

   本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

2. 在系统目录表上执行VACUUM命令。

3. 在系统目录表上执行ANALYZE操作来更新表的统计信息。

如果在维护窗口期内，由于时间限制需要停止目前正在进行的系统目录维护，可以运行Greenplum数据库函数 pg_cancel_backend()来安全的停止该任务。

下面的脚本在系统目录上运行REINDEX、VACUUM和 ANALYZE。

如果系统目录膨胀得很厉害，使用者就必须执行一次大强度的系统目录维护过程。采用CREATE TABLE AS SELECT 移除膨胀的方法以及重新分布数据的方法均不能被用于系统目录。使用者必须转而在计划的停机时段运行VACUUM FULL。 在此期间，停止系统上所有的目录活动，VACUUM FULL会对系统目录加排他锁。定期运行 VACUUM能够预防最终不得不采用上面的高代价方法。

以下是较为彻底解决系统目录膨胀的步骤。

1. 停止Greenplum数据库上所有系统目录操作。
2. 在系统目录表上执行REINDEX操作来重建系统目录索引。该操作可以移除索引膨胀 并提高VACUUM性能。
3. 在系统目录表上执行VACUUM FULL操作。注意关注下面提到的注意事项。
4. 在系统目录表上执行ANALYZE操作来更新系统目录表的统计信息。

Note: 系统目录表pg_attribute通常是这里面最大的表。如果pg_attribute 表明显膨胀，在该表上的VACUUM FULL操作会占用很长时间，此时可能需要将操作分解（先执行vacuum pg_attribute，再执行vacuum full pg_attribute）。

以下 两种情形表明pg_attribute表存在明显膨胀并可能需要运行长时间的VACUUM FULL 操作：

• pg_attribute表包含大量记录。
• gp_toolkit.gp_bloat_diag视图中有关pg_attribute表 的诊断信息上显示该表存在明显膨胀。

从追加优化表移除膨胀

对追加优化表的处理与堆表有很大不同。尽管追加优化表允许更新、插入和删除，但它们并非为这些操作 而优化，因此不推荐对追加优化表使用这些操作。如果使用者采纳这一建议并且为一次装载/多次读取负载使用追加优化，追加优化表上的VACUUM几乎会即刻运行。

如果使用者确实在追加优化表上运行了UPDATE或者DELETE 命令，过期行会由一个辅助位图而不是空闲空间映射来跟踪。VACUUM是唯一能恢复 空间的方式。在有过期行的追加优化表上运行VACUUM会通过把整个表重写成没有 过期行的表以紧缩该表。不过，如果表中过期行的百分数超过了gp_appendonly_compaction_threshold 配置参数的值，则不会执行任何操作，该参数的默认值是10（10%）。每个segment上都会检查该阈值， 因此VACUUM语句可能会在某些segment上对追加优化表进行紧缩而在其他segment 上不做紧缩。通过将gp_appendonly_compaction参数设置为no 可以禁用对追加表的紧缩。

VACUUM命令

垃圾收集并可选地分析数据库。

概要

描述

VACUUM回收已删除的元组占用的存储。 在正常的Greenplum数据库操作中，被更新删除或废弃的元组不会从表中物理删除； 它们将保留在磁盘上，直到完成VACUUM。 因此，有必要定期进行VACUUM，尤其是在频繁更新的表上。

如果没有参数，VACUUM将处理当前数据库中的每个表。 使用参数，VACUUM仅处理该表。

VACUUM ANALYZE对每个选定的表执行VACUUM，然后执行ANALYZE。 这是日常维护脚本的便捷组合形式。 有关处理的更多详细信息，请参见ANALYZE。

VACUUM（无FULL）在表和索引中标记已删除和过时的数据以供将来重用， 并且仅当该空间位于表的末尾并且可以轻松获得独占表锁时，才可以回收该空间以供重用。 表开始或中间的未使用空间保持不变。 对于堆表，这种形式的命令可以与表的正常读取和写入并行运行，因为不会获得排他锁。 但是，在大多数情况下，多余的空间不会返回给操作系统。 它只是可以在同一张表中重复使用。 VACUUM FULL将表的全部内容重写为一个没有额外空间的新磁盘文件，从而允许将未使用的空间返回给操作系统。 这种形式要慢得多，并且在处理过程中需要在每个表上获得排他锁。

使用追加优化表，VACUUM首先清理索引， 然后依次压缩每个段文件，最后清理辅助表并更新统计信息，从而压缩表。 在每个段上，将可见行从当前段文件复制到新的段文件，然后计划删除当前段文件，并使新的段文件可用。 追加优化表的普通VACUUM允许在压缩段文件的同时扫描，插入，删除和更新表。 但是，将使用Access Exclusive锁短暂删除当前段文件并激活新的段文件。

VACUUM FULL进行更广泛的处理，包括在块之间移动元组以尝试将表压缩到最小数量的磁盘块。 这种形式要慢得多，并且在处理每个表时都需要申请Access Exclusive锁。 Access Exclusive锁可确保所有者是以任何方式访问表的唯一事务。

当选项列表用括号括起来时，可以按任何顺序写入选项。 不带括号的选项必须严格按照上面显示的顺序指定。 括号中的语法是在Greenplum数据库6.0中添加的；不带括号的语法已弃用。

Important: 有关使用VACUUM，VACUUM FULL和VACUUM ANALYZE的信息， 请参阅注解。

输出

当指定了VERBOSE时，VACUUM发出进度消息以指示当前正在处理哪个表。 还将打印有关表格的各种统计信息。

参数

FULL

选择full vacuum，这可以回收更多空间，但是需要更长的时间排他锁定表。 此方法还需要额外的磁盘空间，因为它会写入表的新副本，并且在操作完成之前不会释放旧副本。 通常，仅在需要从表中回收大量空间时才应使用此选项。

FREEZE

指定FREEZE等效于将vacuum_freeze_min_age服务器配置参数设置为零来执行VACUUM。 请参阅服务器配置参数以获取有关vacuum_freeze_min_age的信息。

VERBOSE

为每个表打印详细的vacuum活动报告。

ANALYZE

更新优化器使用的统计信息，以确定执行查询的最快方法。

table

要vacuum的表的名称（可以用schema修饰）。 默认为当前数据库中的所有表。

column

要分析的特定列的名称。 默认为所有列。 如果指定了列列表，则意味着ANALYZE。

注解

VACUUM无法在事务块内执行。

频繁（至少每晚一次）vacuum活跃数据库，以便删除过期的行。 添加或删除大量行后，对受影响的表运行VACUUM ANALYZE命令可能会很有用。 这将使用所有最近更改的结果来更新系统catalog，并使Greenplum数据库查询优化器可以在计划查询中做出更好的选择。

Important: PostgreSQL有一个单独的可选服务器进程，称为autovacuum daemon，其目的是自动执行VACUUM和ANALYZE命令。 Greenplum数据库开启autovacuum守护程序仅在Greenplum数据库模板数据库template0上执行VACUUM操作。 为template0启用了autovacuum，因为不允许连接到template0。 autovacuum守护程序在template0上执行VACUUM操作以管理事务ID（XID），并帮助避免template0中的事务ID环绕问题。

必须在用户定义的数据库中执行手动VACUUM操作，以管理这些数据库中的事务ID（XID）。

VACUUM导致I/O流量大幅增加，这可能会导致其他活动会话的性能下降。 因此，建议在低使用率时vacuum数据库。

VACUUM命令跳过外部表。

VACUUM FULL回收所有过期的行空间， 但是它需要对每个正在处理的表进行独占锁定，这是一项非常昂贵的操作， 并且可能需要很长时间才能完成大型分布式Greenplum数据库表。 在数据库维护期间执行VACUUM FULL操作。

不建议例行使用FULL选项，但在特殊情况下可能有用。 例如，当您删除或更新了表中的大多数行，并希望该表在物理上缩小以占用更少的磁盘空间并允许更快的表扫描时。 VACUUM FULL通常比普通VACUUM将表缩小更多。

作为VACUUM FULL的替代方法， 您可以使用CREATE TABLE AS语句重新创建表并删除旧表。

对于附加优化表，VACUUM需要足够的可用磁盘空间以在VACUUM过程中容纳新的段文件。 如果段文件中隐藏行与总行的比率小于阈值（默认为10），则不会压缩段文件。 可以使用gp_appendonly_compaction_threshold服务器配置参数来配置阈值。 VACUUM FULL忽略阈值并重写段文件，而不考虑比率。 可以使用gp_appendonly_compaction服务器配置参数为附加优化表禁用VACUUM。 有关服务器配置参数的信息，请参阅服务器配置参数。

如果在清理附加优化表时检测到并发可序列化事务，则不会压缩当前和后续段文件。 如果已压缩段文件，但是在删除原始段文件的事务中检测到并发可序列化事务，则将忽略该删除。 清理完成后，这可能会使一个或两个段文件处于“等待删除”状态。

有关Greenplum数据库中并发控制的更多信息，请参阅Greenplum数据库管理员指南中的“例行系统维护任务”。

示例

要清理单个表onek，请对它进行优化分析并打印详细的vacuum活动报告：

清理当前数据库中的所有表：

仅清理特定表：

清理当前数据库中的所有表并收集查询优化器的统计信息：

VACUUM和VACUUM FULL的区别

VACUUM和VACUUM FULL都是用于PostgreSQL数据库中的VACUUM命令。它们的主要区别是：

1. VACUUM命令：

VACUUM命令用于回收未使用的空间并更新数据表的统计信息。它通过扫描表并释放已删除或过期的行所占用的空间来减少数据库文件的大小，并且还可以更新表的元数据，以便查询优化器可以做出更好的决策。

2. VACUUM FULL命令：

VACUUM FULL命令会在执行VACUUM命令的基础上进一步压缩表的大小。它通过重新组织表的物理存储来消除碎片和空间浪费，这样可以使表的大小更小，查询的性能更好。但是，它需要更长的时间来执行，而且在执行期间需要独占整个表，这意味着其他事务将无法访问该表。

因此，如果您只需要回收未使用的空间并更新统计信息，可以使用VACUUM命令。但是，如果您需要最大程度地压缩表的大小并愿意接受更长的执行时间和独占表的限制，则可以使用VACUUM FULL命令。

简单点，不带full不会回收空间，例如原表10g，0行，执行VACUUM后仍然是10g，需要执行VACUUM full才会变为0kb。但，VACUUM full会严重锁表，其它用户不能查询该表。

在执行 VACUUM FULL t1 命令期间，t1 表会被完全重建，也就是说，它会重新创建表结构并将数据移动到新表中。在此过程中，其他会话的查询可能会被阻塞，因为该命令需要对整个表进行写锁定。因此，在执行 VACUUM FULL t1 命令期间，t1 表是无法被查询的，直到该命令完成并释放了对表的写锁定。如果您需要对表进行清理和优化，但仍需要在此期间查询该表，您可以尝试使用其他的 VACUUM 命令，例如 VACUUM ANALYZE t1 命令，该命令可以在清理表的同时执行表的统计信息收集，并且可以并发地执行。

定时执行vacuum表的shell脚本

参考

https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/dd8b2565a2ab0c1b.md

https://www.cnblogs.com/easonbook/p/10945265.html