如果您希望在不变更RDS PostgreSQL实例规格的前提下,减少存储空间占用,提高数据库读取性能,则可以参考本文,使用透明页压缩功能。该功能牺牲部分CPU性能,对Buffer Pool中的页数据进行实时压缩和解压缩,可以有效降低存储成本、提高I/O吞吐。
前提条件
RDS PostgreSQL实例版本需要满足以下条件:
实例大版本:14或以上版本。
内核小版本:大于等于20240530,如需升级内核小版本,请参见升级内核小版本。
什么是透明页压缩
透明页压缩 TPC(Transparent Page Compression)中,页压缩指对Buffer Pool中的页执行实时的I/O压缩和解压缩,透明是指使用过程中无感知,数据在写入磁盘时自动压缩,数据在从磁盘读取时自动解压缩。
透明页压缩的主要目标是减少存储空间的使用,并提高数据库的读性能。通过压缩数据,可以降低磁盘I/O,减少存储,提高缓存效率,并加快数据传输速度。
应用场景
CPU使用率在50%以下,IOPS或IO吞吐容易达到瓶颈的场景下,推荐使用透明页压缩功能。
透明页压缩的优势
存储成本平均可节约50%。
IO使用平均可节约50%。
TPS在读场景会有提高,部分IO打满的读场景最高可提升100%。
影响
该功能会使CPU使用量上涨,压缩的CPU上涨约260%,解压的CPU上涨约40%。
在写场景TPS会有下降。
对TOAST数据的压缩效果不佳。
操作步骤
透明页压缩功能依赖表空间,需要创建压缩表空间。
CREATE TABLESPACE rds_compress LOCATION '/data/postgresql/rds_compress' WITH(COMPRESSION='zstd');
重要本步骤创建的表空间名称、表空间路径以及压缩算法请勿修改。
创建压缩表或索引。
在创建或修改表、索引时指定压缩表空间,使用透明页压缩功能。
表压缩
-- 创建表时,指定压缩表空间,即可使用透明页压缩功能 CREATE TABLE <tablename> ... TABLESPACE rds_compress; -- 将现有表的表空间修改为压缩表空间 ALTER TABLE <tablename> SET TABLESPACE rds_compress;
索引压缩
-- 创建索引时,指定压缩表空间,即可使用透明页压缩功能 CREATE INDEX <indexname> ... TABLESPACE rds_compress; -- 将现有索引的表空间修改为压缩表空间 ALTER INDEX <indexname> SET TABLESPACE rds_compress;
修改默认表空间为压缩表空间,后续创建的表和索引,将默认使用透明页压缩功能。
-- 设置默认表空间为压缩表空间 SET default_tablespace TO 'rds_compress'; -- 创建表、索引时无需指定表空间,默认使用透明页压缩功能。 CREATE TABLE <tablename> ...; CREATE INDEX <indexname> ...;
相关查询
查询表、索引是否创建在压缩表空间下:
在psql命令行中输入
\d+ <表名>
查询指定表的详细信息。如果表和索引都创建在压缩表空间下,则表示可以使用透明页压缩功能。以下为sysbench表的结构查询结果。
Table "public.sbtest1" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+----------------+-----------+----------+-------------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('sbtest1_id_seq'::regclass) | plain | | | k | integer | | not null | 0 | plain | | | c | character(120) | | not null | ''::bpchar | extended | | | pad | character(60) | | not null | ''::bpchar | extended | | | Indexes: "sbtest1_pkey" PRIMARY KEY, btree (id), tablespace "rds_compress" "k_1" btree (k), tablespace "rds_compress" Tablespace: "rds_compress" Access method: heap
验证数据是否压缩:
您可以使用
pg_database_size
、pg_tablespace_size
、pg_relation_size
、pg_table_size
、pg_index_size
、pg_total_relation_size
等函数可以查看数据的实时大小。说明您可以利用sysbench测试工具分别向压缩表和非压缩表中插入相同数据量的测试数据,观察压缩表的磁盘占用情况,压缩表的大小约为非压缩表大小的50%。
计算指定表的压缩比:
SELECT pg_relation_size('<tablename>')::float / (relpages * 8192) from pg_class WHERE relname = '<tablename>';
说明上述语句中查询
pg_class
系统目录,使用pg_relation_size('<tablename>')
函数获取指定表的实际磁盘占用空间大小(以字节为单位)并转换为浮点数,通过除以(relpages * 8192)
计算得出每个页面的平均大小(以字节为单位)。其中relpages
是表的总页面数,8192
是PostgreSQL默认的页面大小(通常为8KB),最终,查询的结果表示指定表的平均压缩比率。值越小,表示压缩比越高。
将压缩的表、索引转为非压缩的表、索引
当您不再使用透明页压缩功能时,执行如下命令可以将压缩的表、索引转为非压缩的表、索引。
ALTER TABLE <tablename> SET TABLESPACE pg_default;
ALTER INDEX <indexname> SET TABLESPACE pg_default;
常见问题
Q:开启透明页压缩TPC后,数据库工具(pg_dump,pg_basebackup)还能正常使用吗?
A:pg_dump和pg_basebackup工具可以正常使用,其中如果要将pg_basebackup生成的备份恢复到另一个数据库时,目标数据库也需要开启透明页压缩。