原 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命令重建索引，以避免打扰 正常业务操作的进行。

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命令

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

概要

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。