透明页压缩TPC

如果您希望在不变更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数据的压缩效果不佳。

操作步骤

  1. 透明页压缩功能依赖表空间,需要创建压缩表空间。

    CREATE TABLESPACE rds_compress LOCATION '/data/postgresql/rds_compress' WITH(COMPRESSION='zstd');
    重要

    本步骤创建的表空间名称、表空间路径以及压缩算法请勿修改。

  2. 创建压缩表或索引。

    • 在创建或修改表、索引时指定压缩表空间,使用透明页压缩功能。

      • 表压缩

        -- 创建表时,指定压缩表空间,即可使用透明页压缩功能
        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> ...;

相关查询

  • 查询表、索引是否创建在压缩表空间下:

    1. 在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_sizepg_tablespace_sizepg_relation_sizepg_table_sizepg_index_sizepg_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生成的备份恢复到另一个数据库时,目标数据库也需要开启透明页压缩。