阿里云HybridDB for PG 空间紧张的解法 - 冷热分离、空间锁定、分区、压缩

2 minute read

背景

数据库空间不够用怎么办?

HDB PG是分布式数据库,空间不够用,扩容呗。但是用户如果不想扩容呢?还有哪些处理方法?

例子

1 查看当前已使用空间

查看数据库空间使用,表的空间使用,索引的空间使用等。

postgres=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc;
  datname  | pg_size_pretty
-----------+----------------
 postgres  | 32 MB
 template1 | 31 MB
 template0 | 31 MB
(3 rows)
postgres=# select relname,relkind,pg_size_pretty(pg_relation_size(oid)) from pg_class order by pg_relation_size(oid) desc limit 20;
             relname             | relkind | pg_size_pretty
---------------------------------+---------+----------------
 pg_proc                         | r       | 1920 kB
 pg_rewrite                      | r       | 1824 kB
 pg_depend                       | r       | 1344 kB
 pg_attribute                    | r       | 1248 kB
 pg_depend_reference_index       | i       | 1248 kB
 pg_depend_depender_index        | i       | 1248 kB
 pg_proc_proname_args_nsp_index  | i       | 864 kB
 pg_attribute_relid_attnam_index | i       | 576 kB
 pg_statistic                    | r       | 576 kB
 pg_description                  | r       | 576 kB
 pg_description_o_c_o_index      | i       | 480 kB
 pg_proc_oid_index               | i       | 480 kB
 pg_operator                     | r       | 384 kB
 pg_attribute_relid_attnum_index | i       | 384 kB
 pg_type                         | r       | 288 kB
 gp_persistent_relation_node     | r       | 288 kB
 pg_class                        | r       | 288 kB
 pg_authid_oid_index             | i       | 192 kB
 pg_authid_rolname_index         | i       | 192 kB
 pg_amproc_oid_index             | i       | 192 kB
(20 rows)

2 配置云监控

通过配置云监控,用户可以随时掌握数据库的已使用空间,剩余空间的情况。

3 空间不够用的策略

提供三种建议:

1、drop table, truncate table , 最简单直接

2、DELETE ,版本被保留。所以需要delete+vacuum 。

如果是列AO表,delete后 可以用VACUUM收缩。

如果是HEAP表,delete后 VACUUM无法收缩, 需要VACUUM FULL,但是VACUUM FULL需要双倍空间,并且会堵塞所有读写该表的操作,请慎用。

3、查看是不是有膨胀,可以清理垃圾减少膨胀。

《Greenplum 列存表(AO表)的膨胀、垃圾检查与空间收缩(含修改分布键)》

《如何检测、清理Greenplum膨胀、垃圾(含修改分布键) - 阿里云HybridDB for PG最佳实践》

4、如果表有PARTITION,可以TRUNCATE分区

5、创建OSS外部表,将不经常访问的数据表(或分区)写入OSS外部表。然后删除HDB PG里面对应的TABLE与PARTITION

详见:

https://help.aliyun.com/document_detail/35457.html

注意HDB PG沿用了GPDB的外部表框架,读写外部表操作是分开的。

导出需要创建可写外部表,然后将本地表的数据写出。

如果需要读取OSS中大数据,需要创建可读外部表。

6、使用压缩表(列存,大BLOCK压缩效果好,还可以使用聚集提高压缩比)。

Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name ( 
[ { column_name data_type [ DEFAULT default_expr ]     [column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]
] 
   | table_constraint
   | LIKE other_table [{INCLUDING | EXCLUDING} 
                      {DEFAULTS | CONSTRAINTS}] ...}
   [, ... ] ]
   [column_reference_storage_directive [, ... ]
   )
   [ INHERITS ( parent_table [, ... ] ) ]
   [ WITH ( storage_parameter=value [, ... ] )
   [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
   [ TABLESPACE tablespace ]
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
   [ PARTITION BY partition_type (column)
       [ SUBPARTITION BY partition_type (column) ] 
          [ SUBPARTITION TEMPLATE ( template_spec ) ]
       [...]
    ( partition_spec ) 
        | [ SUBPARTITION BY partition_type (column) ]
          [...]
    ( partition_spec 
      [ ( subpartition_spec 
           [(...)] 
         ) ] 
    )
where storage_parameter is:
   APPENDONLY={TRUE|FALSE}   // aO表,支持COLUMN存储
   BLOCKSIZE={8192-2097152}  // 块大小
   ORIENTATION={COLUMN|ROW}  // 列存压缩比高
   COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}
   COMPRESSLEVEL={0-9}      // 选择压缩比
   CHECKSUM={TRUE|FALSE}
   FILLFACTOR={10-100}
   OIDS[=TRUE|FALSE]

《一个简单算法可以帮助物联网,金融 用户 节约98%的数据存储成本 (PostgreSQL,Greenplum帮你做到)》

7、查看是否是数据倾斜造成的磁盘满。

《分布式DB(Greenplum)中数据倾斜的原因和解法 - 阿里云HybridDB for PostgreSQL最佳实践》

8、如果是系统表膨胀,需要vacuum系统表,特别是大量使用临时表可能导致pg_attribute膨胀。

建议后台调度,在空闲时间vacuum pg_attribute .

vacuum  pg_attribute ; 
vacuum  pg_attribute_encoding ; 
vacuum  gp_relation_node ; 
vacuum  pg_class ; 

如果发现元数据表以及膨胀得很厉害,需要VACUUM FULL清理,(找空闲时间,因为会堵塞所有操作)。

vacuum full pg_attribute;
reindex table pg_attribute;
vacuum full pg_attribute_encoding ;
reindex table pg_attribute_encoding;
vacuum full gp_relation_node ;
reindex table gp_relation_node;
vacuum full pg_class ;  
reindex table pg_class;

《大量使用临时表带来的系统表如pg_attribute膨胀问题,替代方案,以及如何擦屁股 - Greenplum, PostgreSQL最佳实践》

9、如果以上都做不了,建议升级实例

参考

https://help.aliyun.com/document_detail/35457.html

Flag Counter

digoal’s 大量PostgreSQL文章入口