作者
digoal
日期
2018-06-20
标签
PostgreSQL , Greenplum , HybridDB for PG
背景
数据库空间不够用怎么办？
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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
