greenplum 数据库表膨胀优化（复制表、替换表）

新丨客

已于 2024-02-23 17:01:11 修改

阅读量888

点赞数 20

文章标签：数据库

于 2024-02-23 16:57:31 首次发布

本文链接：https://blog.csdn.net/amberwangfeng/article/details/136259531

版权

Greenplum 数据库的堆表使用PostgreSQL 的多版本并发控制（MVCC）的存储实现方式。删除和更新的行仅仅是逻辑删除，其实际数据仍然存储在表中，只是不可见。这些删除的行，也称为过期行，由空闲空间映射表（FSM, Free Space Map）记录。 VACUUM 标记这些过期的行为空闲空间，并可以被后续插入操作重用。

ACUUM
VACUUM命令可以与其他查询并行运行。它会标记之前被过期行所占用的空间为空闲可用，如果剩余的空闲空间数量可观，它会把该页面加到该表的空闲空间映射中。当Greenplum数据库之后需要空间分配给新行时，它首先会参考该表的空闲空间映射以寻找有可用空间的页面。如果没有找到这样的页面，它会为该文件追加新的页面。
VACUUM不会合并页面或者减小表在磁盘上的尺寸，它回收的空间只是放在空闲空间映射中表示可用。为了阻止磁盘文件大小增长，重要的是足够频繁地运行VACUUM。运行VACUUM的频率取决于表中更新和删除（插入只会增加新行）的频率。重度更新的表可能每天需要运行几次VACUUM来确保通过空闲空间映射能找到可用的空闲空间。在运行了一个更新或者删除大量行的事务之后运行VACUUM也非常重要。

VACUUM不会合并页面或者减小表在磁盘上的尺寸，它回收的空间只是放在空闲空间映射中表示可用。为了阻止磁盘文件大小增长，重要的是足够频繁地运行VACUUM。运行VACUUM的频率取决于表中更新和删除（插入只会增加新行）的频率。重度更新的表可能每天需要运行几次VACUUM来确保通过空闲空间映射能找到可用的空闲空间。在运行了一个更新或者删除大量行的事务之后运行VACUUM也非常重要。

VACUUM FULL VACUUM FULL命令会把表重写为没有过期行，并且将表减小到其最小尺寸。表中的每一页都会被检查，其中的可见行被移动到前面还没有完全填满的页面中，空页面会被丢弃。该表会被一直锁住直到VACUUM
FULL完成。相对于常规的VACUUM命令来说，它是一种非常昂贵的操作，可以用定期的清理来避免或者推迟这种操作。最好是在一个维护期来运行VACUUM
FULL。VACUUM FULL的一种替代方案是用一个CREATE TABLE AS语句重新创建该表并且删除掉旧表。
关于vaccum和vaccum
full的实际应用，可参考链接：http://blog.itpub.net/29989552/viewspace-2128914/

空闲空间映射 运行VACUUM 时，堆表的过期行被加入到共享的空闲空间映射表（FSM）中。 FSM必须足够容纳过期行。如果不够大，则溢出FSM的空间不能被 VACUUM 回收。必须使用VACUUM FULL
或者其他方法回收溢出空间。定期性运行VACUUM 可以避免FSM溢出。表越臃肿， FSM就需要记录越多的行。对于非常大的具有很多对象的数据库，需要增加FSM 以避免溢出。
空闲空间映射位于共享内存中，它跟踪着所有表和索引的空闲空间。每一个表或者索引使用大约60字节的内存，每一个具有空闲空间的页面会使用6个字节。配置空闲空间映射尺寸的两个系统配置参数是：

max_fsm_pages 设置可以加入到共享空闲空间映射的磁盘页最大数量。每一个页槽需要消耗共享内存的6个字节。默认值是200000。这个参数必须被设置为max_fsm_relations值的至少16倍。

max_fsm_relations 设置共享内存中空闲空间映射所跟踪的关系的最大数量。这个参数应该被设置为一个大于表数量 + 索引数量 + 系统表数量总和的值。默认值为1000。在每个实例上每一个关系会消耗大约60字节的内存。这个参数值宜高不宜低。

如果空闲空间映射容量不足，一些具有可用空间的磁盘页将无法被加到其中，那么这些空间将无法被重用直至下一次VACUUM命令运行。这将会导致文件尺寸增长。

执行下面SQL，查询表占用资源，发现和实际数据行数严重不符，但是占用2000MB以上，不敢轻易VACUUM，所以走复制表，再删除的方案。

SELECT pg_size_pretty(pg_total_relation_size('tbl_name')) As fulltblsize, pg_size_pretty(pg_relation_size('tbl_name')) As justthetblsize;

复制表（同时复制主键、索引、序列、注释等约束）

--会把全部DDL语句复制过来，但是只有结构没有数据
--{ INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS| ALL }
create table cs_company_tmp (like  cs_company including all);

--再执行一次复制数据
insert into cs_company_tmp select * from cs_company;

--把原表先改个名字，留几天再drop掉
ALTER TABLE cs_company RENAME TO cs_company_old;

--把复制后的新表修改为业务使用的名称，后续观察插入和查询（主键、索引）
ALTER TABLE cs_company_tmp RENAME TO cs_company;