分析型数据库 PostgreSQL的堆表使用 PostgreSQL 的多版本并发控制(MVCC)存储实现。被删除或更新的行被从数据库逻辑删除,但是该行的一个不可见映像保留在表中。这些被删除的行(也被称为过期行)被存储在一个空闲空间映射文件中。运行 VACUUM 会把过期行标记为可以被后续插入重用的空闲空间。
如果空闲空间映射不足以容纳所有的过期行,VACUUM 命令就不能从导致空闲空间映射溢出的过期行回收空间。磁盘空间只能通过运行 VACUUM FULL 恢复,这个操作会锁住表,逐行拷贝到文件的开头,然后截断文件。这是一种昂贵的操作,对于大型的表,它可能需要超乎想象的时间来完成,应该只在较小的表上使用这种操作。如果使用者尝试杀死 VACUUM FULL 操作,系统可能会损坏。
注意在大量的的 UPDATE 以及 DELETE 操作之后非常有必要运行 VACUUM, 这样可以避免运行 VACUUM FULL。
如果空闲空间映射溢出并且需要恢复空间,推荐使用 CREATE TABLE...AS SELECT 命令把该表拷贝为 一个新表,这将会创建一个新的紧凑的表。然后删除原始表并且重命名拷贝的表为原始表名。
对于频繁更新的表来说,有少量或者中等数量的过期行以及空闲空间很正常,空闲空间将随着新数据的加入而被重用。 但是当表被允许增长得非常大以至于活动数据只占空间的一小部分时,该表就明显地“膨胀”了。膨胀的表要求更多磁盘存储以及可能拖慢查询执行的额外 I/O。
除了堆表以外,系统目录和索引也会膨胀。
在表上定期运行 VACUUM 语句可以防止它们长得过大。如果表确实出现了明显的膨胀,必须使用 VACUUM FULL 语句(或者可替代的过程)来紧缩文件。如果一个大型表变得明显膨胀,更好的方法是使用从数据库表移除膨胀中描述的方法之一来移除膨胀。
注意请谨慎运行 VACUUM FULL
,尤其在分析型数据库 PostgreSQL中的大型表上运行 VACUUM FULL。
检测膨胀
ANALYZE 语句所收集的统计信息可以被用来计算存储一个表所要求的磁盘页面的预计数量。页面的预计数量和实际数量之间的差别就是膨胀的度量。gp_toolkit 模式提供了一个 gp_bloat_diag 视图,它通过预计页数和实际页数的比率来确定表膨胀。要使用这个视图,确定为数据库中所有的表都收集了最新的统计信息。然后运行下面的 SQL:
gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_diag;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+------------+-------------+-------------+---------------------------------------
21488 | public | t1 | 97 | 1 | significant amount of bloat suspected
(1 row)
其结果只包括发生了中度或者明显膨胀的表。当实际页面数和预期页面的比率超过 4 但小于 10 时,就会报告为中度膨胀。 当该比率超过 10 时就会报告明显膨胀。
gp_toolkit.gp_bloat_expected_pages 视图会为每个数据库对象列出其已用页面的实际数量和预期数量。
gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_expected_pages LIMIT 5;
btdrelid | btdrelpages | btdexppages
----------+-------------+-------------
10789 | 1 | 1
10794 | 1 | 1
10799 | 1 | 1
5004 | 1 | 1
7175 | 1 | 1
(5 rows)
btdrelid 是该表的对象 ID。btdrelpages 列报告该表使用的页面数, btdexppages 列是预期的页面数。另外,报出的数字是基于表统计信息的,因此要确保在已经被更改的表上运行 ANALYZE。
从数据库表移除膨胀
VACUUM 命令会把过期行加入到共享的空闲空间映射中,这样这些空间能被重用。当在被频繁 更新的表上定期运行 VACUUM 时,过期行所占用的空间可以被迅速地重用,从而防止表文件长得 更大。在空闲空间映射被填满之前运行 VACUUM 也很重要。对于更新密集的表,用户可能需要每天运行 VACUUM 至少一次来防止表膨胀。
当表积累了显著的膨胀时,运行 VACUUM 命令并不能起到明显作用。对于小型表,运行 VACUUM FULL <table_name>能够回收导致空闲空间映射溢出的行所使用的空间并且减小表文件的尺寸。不过,VACUUM FULL 语句是一种昂贵的操作,它要求一个 ACCESS EXCLUSIVE 锁并且可能需要非常长的时间完成。比起在一个大型表上运行 VACUUM FULL,采用另一种方法从大型文件中移除膨胀会更好。注意每一种从大型表中移除膨胀的方法都是资源密集型的,并且只应该在极端情况下完成。
第一种从大型表中移除膨胀的方法是创建一个将过期行排除在外的表拷贝,删掉原始的表并且把这个拷贝重命名为原来的表名。这种方法使用 CREATE TABLE <table_name> AS SELECT 语句创建新表,例如:
gpadmin=# CREATE TABLE mytable_tmp AS SELECT * FROM mytable;
gpadmin=# DROP TABLE mytable;
gpadmin=# ALTER TABLE mytabe_tmp RENAME TO mytable;
第二种从表移除膨胀的方法是重新分布该表,这会把该表重建为不含过期行的表。参考步骤如下:
-
把表的分布列记下来。
-
把该表的分布策略改为随机分布:
ALTER TABLE mytable SET WITH (REORGANIZE=false) DISTRIBUTED randomly;
这会为该表更改分布策略,但不会移除任何数据。该命令应该会立即完成。
-
将分布策略改回其初始设置:
ALTER TABLE mytable SET WITH (REORGANIZE=true) DISTRIBUTED BY (<original distribution columns>);
这一步会重新分布数据。因为表之前是用同样的分布键分布的,表中的行只需要简单地在同一 Segment 上重写 即可,同时排除过期行。
从索引移除膨胀
VACUUM 命令只会从表中恢复空间。要从索引中恢复空间,需要使用 REINDEX 命令重建它们。
要在一个表上重建所有的索引,可运行 REINDEX _table_name_;
。要重建一个特定的索引, 可运行 REINDEX _index_name_;
。REINDEX 会将该索引相关 reltuples 和 relpages 的值设置为 0,如果要更新统计信息, 则有必要在重建索引后运行 ANALYZE 来更新它们。
从系统目录移除膨胀
分析型数据库 PostgreSQL系统目录也是堆表并且也可能随着时间推移变得膨胀。随着数据库对象被创建、修改或者删除, 过期行会留在系统目录中。使用 gpload 导入数据会加剧膨胀,因为 gpload 会创建并且删除外部表。(为了避免使用 gpload,推荐使用 gpfdist 导入数据。)
系统目录中的膨胀会导致扫描表所需的时间增加,例如在创建执行计划时需要扫描系统目录。系统目录会被频繁扫描, 那么如果它们变得膨胀,整体的系统性能都会退化。
推荐每晚在系统目录上运行 VACUUM,或者至少每周运行一次。同时,运行 REINDEX SYSTEM 从索引中移除膨胀。此外,还可以使用带-s(--system)选项的 reindexdb 工具对系统目录重建索引。在移除系统目录膨胀后,还有必要运行 ANALYZE 以更新系统目录表的统计信息。
以下是分析型数据库 PostgreSQL系统目录维护步骤:
- 在系统目录表上执行 REINDEX 操作用于重建系统目录索引。该操作可以移除索引膨胀并提高 VACUUM 性能。
注意
当在系统目录表上执行 REINDEX 操作时,会锁住相应的表,进而影响到当前正在执行的查询性能。用户可以在系统的非活动窗口时间来调用 REINDEX 命令重建索引,以避免打扰正常业务操作的进行。
- 在系统目录表上执行 VACUUM 命令。
- 在系统目录表上执行 ANALYZE 操作来更新表的统计信息。
如果在维护窗口期内,由于时间限制需要停止目前正在进行的系统目录维护,可以运行分析型数据库 PostgreSQL函数 pg_cancel_backend()来安全的停止该任务。
下面的脚本在系统目录上运行 REINDEX、VACUUM 和 ANALYZE:
#!/bin/bash
DBNAME="<database_name>"
SYSTABLES="' pg_catalog.' || relname || ';' from pg_class a, pg_namespace b \
where a.relnamespace=b.oid and b.nspname='pg_catalog' and a.relkind='r'"
reindexdb -s -d $DBNAME
psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME
analyzedb -s pg_catalog -d $DBNAME
如果系统目录膨胀得很厉害,使用者就必须执行一次大强度的系统目录维护过程。采用 CREATE TABLE AS SELECT 移除膨胀的方法以及重新分布数据的方法均不能被用于系统目录。使用者必须转而在计划的停机时段运行 VACUUM FULL。 在此期间,停止系统上所有的目录活动,VACUUM FULL 会对系统目录加排他锁。定期运行 VACUUM 能够预防最终不得不采用上面的高代价方法。
以下是较为彻底解决系统目录膨胀的步骤:
- 停止分析型数据库 PostgreSQL上所有系统目录操作。
- 在系统目录表上执行 REINDEX 操作来重建系统目录索引。该操作可以移除索引膨胀 并提高 VACUUM 性能。
- 在系统目录表上执行 VACUUM FULL 操作。注意关注下面提到的注意事项。
- 在系统目录表上执行 ANALYZE 操作来更新系统目录表的统计信息。
注意系统目录表 pg_attribute 通常是这里面最大的表。如果 pg_attribute 表明显膨胀,在该表上的 VACUUM FULL 操作会占用很长时间,此时可能需要将操作分解。以下两种情形表明 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 可以禁用对追加表的紧缩。