自学内容网 自学内容网

greenplum 数据库表膨胀优化(复制表、替换表)

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;

原文地址:https://blog.csdn.net/amberwangfeng/article/details/136259531

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!