HybridDB for PostgreSQL 列存表(AO表)的膨胀、垃圾检查与空间收缩

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , Greenplum , 垃圾检测 , 膨胀 , 列存表 , gp_appendonly_compaction_threshold


背景

Greenplum支持行存储(堆存储)与AO存储,堆存储的垃圾回收和膨胀检测方法请参考:

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

对于AO存储,虽然是appendonly,但实际上GP是支持DELETE和UPDATE的,被删除或更新的行,通过BITMAP来标记。

AO存储是块级组织,当一个块内的数据大部分都被删除或更新掉时,扫描它浪费的成本实际上是很高的。

如何检查AO表的膨胀,收缩AO表呢?

如何查看表的存储结构

pg_class.relstorage表示这个对象是什么存储:

postgres=# select distinct relstorage from pg_class ;  
 relstorage   
------------  
 a  -- 行存储AO表  
 h  -- heap堆表、索引  
 x  -- 外部表(external table)  
 v  -- 视图  
 c  -- 列存储AO表  
(5 rows)  

查询当前数据库有哪些AO表:

postgres=# select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('c', 'a');  
 nspname  |      relname        
----------+-------------------  
 postgres | tbl_tag  
 postgres | tbl_pos_1_prt_p1  
 postgres | tbl_pos_1_prt_p2  
 postgres | tbl_pos_1_prt_p3  
 postgres | tbl_pos_1_prt_p4  
 postgres | tbl_pos_1_prt_p5  
 postgres | tbl_pos_1_prt_p6  
 postgres | tbl_pos_1_prt_p7  
 postgres | tbl_pos_1_prt_p8  
 postgres | tbl_pos_1_prt_p9  
 postgres | tbl_pos_1_prt_p10  
 postgres | tbl_pos  
 postgres | xx_czrk_qm_col  
 postgres | ao1  
(14 rows)  

查询当前数据库有哪些堆表:

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('h') and relkind='r';  

查看AO表的膨胀率(有多少垃圾)

使用这个函数可以查看AO表的膨胀率

postgres=# \df+ gp_toolkit.__gp_aovisimap_compaction_info  
List of functions  
-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
Schema              | gp_toolkit  
Name                | __gp_aovisimap_compaction_info  
Result data type    | SETOF record  
Argument data types | ao_oid oid, OUT content integer, OUT datafile integer, OUT compaction_possible boolean, OUT hidden_tupcount bigint, OUT total_tupcount bigint, OUT percent_hidden numeric  
Type                | normal  
Data access         | no sql  
Volatility          | volatile  
Owner               | dege.zzz  
Language            | plpgsql  
Source code         |   
                    | DECLARE  
                    |     hinfo_row RECORD;  
                    |     threshold float;  
                    | BEGIN  
                    |     EXECUTE 'show gp_appendonly_compaction_threshold' INTO threshold;  
                    |     FOR hinfo_row IN SELECT gp_segment_id,  
                    |     gp_toolkit.__gp_aovisimap_hidden_typed(ao_oid)::gp_toolkit.__gp_aovisimap_hidden_t  
                    |     FROM gp_dist_random('gp_id') LOOP  
                    |         content := hinfo_row.gp_segment_id;  
                    |         datafile := (hinfo_row.__gp_aovisimap_hidden_typed).seg;  
                    |         hidden_tupcount := (hinfo_row.__gp_aovisimap_hidden_typed).hidden;  
                    |         total_tupcount := (hinfo_row.__gp_aovisimap_hidden_typed).total;  
                    |         compaction_possible := false;  
                    |         IF total_tupcount > 0 THEN  
                    |             percent_hidden := (100 * hidden_tupcount / total_tupcount::numeric)::numeric(5,2);  
                    |         ELSE  
                    |             percent_hidden := 0::numeric(5,2);  
                    |         END IF;  
                    |         IF percent_hidden > threshold THEN  
                    |             compaction_possible := true;  
                    |         END IF;  
                    |         RETURN NEXT;  
                    |     END LOOP;  
                    |     RAISE NOTICE 'gp_appendonly_compaction_threshold = %', threshold;  
                    |     RETURN;  
                    | END;  
                    |   
Description         |   

例子

postgres=# select * from gp_toolkit.__gp_aovisimap_compaction_info('postgres.ao1'::regclass);  
NOTICE:  gp_appendonly_compaction_threshold = 10  
  
  
 content | datafile | compaction_possible | hidden_tupcount | total_tupcount | percent_hidden   
---------+----------+---------------------+-----------------+----------------+----------------  
       2 |        1 | f                   |              21 |         208283 |           0.01  
       9 |        1 | f                   |              25 |         208303 |           0.01  
      16 |        1 | f                   |              16 |         208352 |           0.01  
       4 |        1 | f                   |              23 |         208356 |           0.01  
       6 |        1 | f                   |              23 |         208386 |           0.01  
       3 |        1 | f                   |              14 |         208333 |           0.01  
       1 |        1 | f                   |              14 |         208329 |           0.01  
      14 |        1 | f                   |              12 |         208350 |           0.01  
      15 |        1 | f                   |              24 |         208346 |           0.01  
       7 |        1 | f                   |              22 |         208329 |           0.01  
       8 |        1 | f                   |              18 |         208334 |           0.01  
       0 |        1 | f                   |              21 |         208314 |           0.01  
      18 |        1 | f                   |              16 |         208417 |           0.01  
      11 |        1 | f                   |              24 |         208337 |           0.01  
      17 |        1 | f                   |              31 |         208380 |           0.01  
      12 |        1 | f                   |              12 |         208367 |           0.01  
      13 |        1 | f                   |              22 |         208365 |           0.01  
       5 |        1 | f                   |              22 |         208367 |           0.01  
      10 |        1 | f                   |              18 |         208347 |           0.01  
      20 |        1 | f                   |              17 |         208384 |           0.01  
      27 |        1 | f                   |              22 |         208348 |           0.01  
      19 |        1 | f                   |              31 |         208425 |           0.01  
      23 |        1 | f                   |              28 |         208344 |           0.01  
      26 |        1 | f                   |              14 |         208339 |           0.01  
      25 |        1 | f                   |              21 |         208386 |           0.01  
      24 |        1 | f                   |              14 |         208332 |           0.01  
      21 |        1 | f                   |              28 |         208360 |           0.01  
      33 |        1 | f                   |              18 |         208354 |           0.01  
      31 |        1 | f                   |              23 |         208335 |           0.01  
      22 |        1 | f                   |              17 |         208309 |           0.01  
      28 |        1 | f                   |              21 |         208314 |           0.01  
      29 |        1 | f                   |              23 |         208329 |           0.01  
      30 |        1 | f                   |              22 |         208342 |           0.01  
      35 |        1 | f                   |              18 |         208305 |           0.01  
      34 |        1 | f                   |              26 |         208363 |           0.01  
      32 |        1 | f                   |              25 |         208396 |           0.01  
      36 |        1 | f                   |              23 |         208323 |           0.01  
      38 |        1 | f                   |              22 |         208367 |           0.01  
      37 |        1 | f                   |              12 |         208334 |           0.01  
      39 |        1 | f                   |              12 |         208389 |           0.01  
      41 |        1 | f                   |              16 |         208373 |           0.01  
      42 |        1 | f                   |              30 |         208376 |           0.01  
      40 |        1 | f                   |              31 |         208397 |           0.01  
      43 |        1 | f                   |              16 |         208378 |           0.01  
      46 |        1 | f                   |              29 |         208371 |           0.01  
      47 |        1 | f                   |              16 |         208382 |           0.01  
      45 |        1 | f                   |              17 |         208368 |           0.01  
      44 |        1 | f                   |              29 |         208381 |           0.01  
(48 rows)  

解读:

1、content:对应gp_configuration.content,表示greenplum每个节点的唯一编号。

2、datafile:这条记录对应的这个表的其中一个数据文件的编号,每个数据文件假设1GB。

3、compaction_possible:这个数据文件是否可以被收缩。(通过gp_appendonly_compaction_threshold参数和percent_hidden值判断)。

4、hidden_tupcount:有多少条记录已更新或删除(不可见)。

5、total_tupcount:总共有多少条记录(包括已更新或删除的记录)。

6、percent_hidden:不可见记录的占比。如果这个占比大于gp_appendonly_compaction_threshold参数,那么执行vacuum时,会收缩这个数据文件。

检查系统中膨胀率超过N的AO表

膨胀率超过千分之2的AO表:

select * from (  
  select t2.nspname, t1.relname, (gp_toolkit.__gp_aovisimap_compaction_info(t1.oid)).*   
  from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('c', 'a')   
) t   
where t.percent_hidden > 0.2;  

返回结果

 nspname  |      relname      | content | datafile | compaction_possible | hidden_tupcount | total_tupcount | percent_hidden   
----------+-------------------+---------+----------+---------------------+-----------------+----------------+----------------  
 postgres | tbl_pos_1_prt_p1  |       1 |        1 | t                   |        20833382 |       20833382 |         100.00  
 postgres | tbl_pos_1_prt_p1  |       7 |        1 | t                   |        20833495 |       20833628 |         100.00  
 postgres | tbl_pos_1_prt_p1  |       5 |        1 | t                   |        20833628 |       20833495 |         100.00  
 postgres | tbl_pos_1_prt_p1  |       3 |        1 | t                   |        20833469 |       20833469 |         100.00  
....  

显然膨胀了100%,验证如下(1条记录有效,占用了15GB空间):

postgres=# select pg_size_pretty(pg_relation_size('tbl_pos_1_prt_p1'));  
 pg_size_pretty   
----------------  
 15 GB  
(1 row)  
  
postgres=# select count(*) from tbl_pos_1_prt_p1;  
 count   
-------  
     1  
(1 row)  

vacuum可以直接收缩(因为膨胀率大于gp_appendonly_compaction_threshold参数):

postgres=# vacuum tbl_pos_1_prt_p1;  
VACUUM  
postgres=# select count(*) from tbl_pos_1_prt_p1;  
 count   
-------  
     1  
(1 row)  
  
postgres=# select pg_size_pretty(pg_relation_size('tbl_pos_1_prt_p1'));  
 pg_size_pretty   
----------------  
 40 bytes  
(1 row)  

VACUUM后,只占用40字节。

收缩已膨胀的AO表

方法有三:

1、执行VACUUM。(当膨胀率大于gp_appendonly_compaction_threshold参数时),为共享锁。

2、执行VACUUM FULL。(不管gp_appendonly_compaction_threshold参数的设置,都会回收垃圾空间。),为DDL锁。

3、执行重分布。(不管gp_appendonly_compaction_threshold参数,都会回收垃圾空间。),为DDL锁。

alter table <table_name> set with (reorganize=true) distributed randomly;    
    
或    
    
alter table <table_name> set with (reorganize=true) distributed by (<column_names1>,<column_names2>....)    

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

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

建议的操作流程:

1 记录下表的分布列    
    
2 执行set distribute (REORGANIZE=true)    
    
如果是随机分布,则设置为随机分布    

参考

http://greenplum.org/docs/510/admin_guide/managing/maintain.html

If the ratio of hidden rows to total rows in a segment file is less than a threshold value (10, by default),   
the segment file is not compacted.   
  
The threshold value can be configured with the gp_appendonly_compaction_threshold server configuration parameter.   
  
VACUUM FULL ignores the value of gp_appendonly_compaction_threshold and rewrites the segment file regardless of the ratio.  
  
You can use the __gp_aovisimap_compaction_info() function in the the gp_toolkit schema to investigate   
the effectiveness of a VACUUM operation on append-optimized tables.  

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
8月前
|
关系型数据库 分布式数据库 数据库
PolarDB对比X-Engine与InnoDB空间效率
本实验带您体验创建X-Engine和InnoDB两种不同的表存储引擎,通过Sysbench模拟数据注入的过程对比俩种表引擎的空间效率。
548 0
|
9天前
|
运维 Cloud Native 关系型数据库
云原生数据仓库产品使用合集之原生数据仓库AnalyticDB PostgreSQL版如果是列存表的话, adb支持通过根据某个字段做upsert吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
9天前
|
分布式计算 关系型数据库 大数据
MaxCompute产品使用合集之怎么才可以将 PostgreSQL 中的 geometry 空间类型字段同步到 MaxCompute 或另一个 PostgreSQL 数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
9月前
|
安全 关系型数据库 数据库
创建 PostgreSQL 表空间时没有指定空间的总大小
创建 PostgreSQL 表空间时没有指定空间的总大小
99 1
|
10月前
|
存储 缓存 关系型数据库
PostgreSQL 14新特性--减少索引膨胀
PostgreSQL 14新特性--减少索引膨胀
403 0
|
12月前
|
关系型数据库 定位技术 数据库
PostgreSQL技术大讲堂 - 第17讲:Vacuum空间管理工具
PostgreSQL从小白到专家,技术大讲堂 - 第17讲:Vacuum空间管理工具
152 0
|
关系型数据库 数据库 PostgreSQL
PG技术大讲堂 - 第14讲:PostgreSQL 检查点
PG技术大讲堂 - 第14讲:PostgreSQL 检查点
247 1
|
并行计算 算法 Cloud Native
PolarDB 开源版 使用PostGIS 数据寻龙点穴(空间聚集分析)- 大数据与GIS分析解决线下店铺选址问题
寻龙点穴是风水学术语。古人说:三年寻龙,十年点穴。意思就是说,学会寻龙脉要很长的时间,但要懂得点穴,并且点得准则难上加难,甚至须要用“十年”时间。 但是,若没正确方法,就是用百年时间,也不能够点中风水穴心聚气的真点,这样一来,寻龙的功夫也白费了。 准确地点正穴心,并不是一件容易的事,对初学者来说如此,就是久年经验老手,也常常点错点偏。 寻龙点穴旨在寻找龙气聚集之地,而现实中,我们也有类似需求,比如找的可能是人气聚集之地。 PolarDB 开源版 使用PostGIS 数据寻龙点穴(空间聚集分析)- 大数据与GIS分析解决线下店铺选址问题
487 0
PolarDB 开源版 使用PostGIS 数据寻龙点穴(空间聚集分析)- 大数据与GIS分析解决线下店铺选址问题
|
SQL 关系型数据库 数据库
解决 RDS SQL Server 日志空间增长问题
解决 RDS SQL Server 日志空间增长问题
|
存储 SQL Oracle
再谈PostgreSQL的膨胀和vacuum机制及最佳实践
作者介绍 朱贤文,成都文武信息技术有限公司创始人,PostgreSQL中国用户会核心组成员,熟悉数据库,存储和集群技术; 成都文武信息技术有限公司是PostgreSQL和GreenPlum数据库服务的专业厂商,主要产品是ECOX集群管理系统和Hunghu Cloud,专门运行数据库的私有云系统,带高端存储功能。公司总部位于天府软件园。公司网站:w3.ww-it.cn 写本文的原因 这两天有两篇专门介绍PostgreSQL的vacuum机制的技术文章,得到了比较热烈和正面的反馈,让用户可以比较清楚地理解和使用这个特性。 我个人觉得有点小遗憾:这两篇文章没有跳出技术的角度,分析为什么会有这
333 0

相关产品

  • 云原生数据库 PolarDB