如何检测、清理Greenplum垃圾 - 阿里云HybridDB for PG最佳实践

postgresql , greenplum , hdb for pg

greenplum通过多版本支持数据的删除和更新的并发和回滚，在删除数据时（使用delete删除），对记录的头部xmax值进行标记。在删除记录时，对记录的头部进行标记，同时插入新的版本。

这一就会导致一个问题，如果用户经常删除和插入或更新数据，表和索引都会膨胀。

postgresql是通过hot技术以及autovacuum来避免或减少垃圾的。但是greenplum没有自动回收的worker进程，所以需要人为的触发。

1、首先要更新表的统计信息，因为接下来的两个视图是从统计信息来计算膨胀率的。

2、查询gp_toolkit.gp_bloat_diag，膨胀较厉害的表。

use the gp_toolkit administrative schema:

gp_toolkit.gp_bloat_diag - this view shows tables with moderate and significant amount of bloat

列:

例子:

in this example the table "t1" is severely bloated (the calculated data size for data currently in table is 1 page, but table consists of 97 pages).

3、查询gp_toolkit.gp_bloat_expected_pages，所有对象的膨胀明细。

gp_toolkit.gp_bloat_expected_pages - this view shows the page data (current pages/expected pages) for all tables in the database columns:

列：

例子：

in this example the tables shown all have calculated data size of 1 page and actual data file size 1 page. no bloat is detected.

4、gp的系统表也可能产生垃圾，例如频繁的使用临时表（临时表是会话级别的，所以每次使用都要创建。会在pg_class, pg_attribute等系统表产生写入和删除的动作。产生系统表垃圾）。

如果配置了autovacuum，postgresql会自动生成统计信息，不需要人为干预。

2、查看膨胀的表、索引

<a href="https://github.com/digoal/blog/blob/master/201306/20130628_01.md">《postgresql 如何精确计算表膨胀(fsm,数据块layout讲解) - postgresql table exactly bloat monitor use freespace map data》</a>

<a href="https://github.com/digoal/blog/blob/master/201504/20150429_02.md">《postgresql 垃圾回收原理以及如何预防膨胀 - how to prevent object bloat in postgresql》</a>

连接到对应的数据库查询。

1、vacuum full

注意，vacuum full不能回收索引的膨胀空间。vacuum full 加载的锁与ddl锁类似，是排它锁。建议在没有业务的时候执行，不要堵塞业务。

使用vacuum full回收垃圾的建议操作流程：

例子

回收垃圾的错误做法

回收垃圾的正确做法

2、alter table set distribute

set distribute可以回收索引的膨胀空间。set distribute 加载的锁与ddl锁类似，是排它锁。建议在没有业务的时候执行，不要堵塞业务。

同时set distribute只要分布条件不变，就是在节点内完成的，不会涉及数据的重分布。

建议的操作流程：

postgresql 通常用在oltp系统中，业务对数据库的可用性比olap系统要高很多，所以长时间持有排它锁的vacuum full要少用。

通常postgresql的autovacuum参数开启后，不会导致大量的膨胀，除非有长事务、或者人为的设定（例如防止备库query与vacuum冲突的设定）妨碍了垃圾回收。这些都有解决方法，如下：

<a href="https://github.com/digoal/blog/blob/master/201704/20170410_02.md">《为什么啤酒和纸尿裤最搭 - 用hybriddb/postgresql查询商品营销最佳组合》</a>

<a href="https://github.com/digoal/blog/blob/master/201511/20151109_01.md">《postgresql snapshot too old补丁, 防止数据库膨胀》</a>

<a href="https://github.com/digoal/blog/blob/master/201610/20161005_02.md">《postgresql 9.6 快照过旧 - 源码浅析》</a>

postgresql的垃圾回收方法举例：

1、首推reorg的方法，这种方法是新建一个对象，增量同步到新的对象，最后将新对象的datafile和老对象（膨胀对象）的datafile进行交换。

仅仅是交换文件时，需要一个排它锁，非常短暂。

参考

<a href="https://github.com/digoal/blog/blob/master/201610/20161030_02.md">《postgresql 收缩膨胀表或索引 - pg_squeeze or pg_repack》</a>

2、如果你没有按照pg_squeeze或pg_repack插件，那么在遇到膨胀后，可以通过vacuum full来回收，postgresql 9.0以后，vacuum full会回收索引的垃圾，比gp更高级一点。

建议的操作

1、在执行vacuum full或alter table回收垃圾时，务必注意这个是排它锁，请在维护窗口执行，或者至少应该加一个锁超时的设定在开始搞。

2、pg通常不会产生膨胀，除非配置或使用不规范。见文中详解。

3、pg的垃圾回收，建议使用reorg的方式，尽量避免使用vacuum full。

4、gp评估垃圾时，如果你发现没有垃圾，别高兴太早，有可能是统计信息没有收集。所以保持有节奏的analyze是好的习惯。

另外可以参考一下这个issue，不需要依赖analyze，通过采样的方法评估垃圾比例。

<a href="https://github.com/greenplum-db/gpdb/issues/706">https://github.com/greenplum-db/gpdb/issues/706</a>

1、alter table 语法

<a href="https://gpdb.docs.pivotal.io/4370/ref_guide/sql_commands/alter_table.html">https://gpdb.docs.pivotal.io/4370/ref_guide/sql_commands/alter_table.html</a>

2、性能诊断

<a href="https://gpdb.docs.pivotal.io/4330/admin_guide/perf_issues.html">https://gpdb.docs.pivotal.io/4330/admin_guide/perf_issues.html</a>

3、日常维护

<a href="https://gpdb.docs.pivotal.io/4330/admin_guide/managing/maintain.html">https://gpdb.docs.pivotal.io/4330/admin_guide/managing/maintain.html</a>

4、表膨胀

<a href="https://discuss.pivotal.io/hc/en-us/articles/202873573-faq-bloat-in-heap-tables">https://discuss.pivotal.io/hc/en-us/articles/202873573-faq-bloat-in-heap-tables</a>

5、消除表膨胀的方法

<a href="https://discuss.pivotal.io/hc/en-us/articles/206578327-what-are-the-different-option-to-remove-bloat-from-a-table-">https://discuss.pivotal.io/hc/en-us/articles/206578327-what-are-the-different-option-to-remove-bloat-from-a-table-</a>

6、如何通过重分布消除表膨胀

<a href="https://discuss.pivotal.io/hc/en-us/articles/203248413-tip-removing-fragmentaion-aka-bloat-on-a-relation-using-redistribute-">https://discuss.pivotal.io/hc/en-us/articles/203248413-tip-removing-fragmentaion-aka-bloat-on-a-relation-using-redistribute-</a>