行存表压缩能力增强测试未达到预期

【请认真填下以下信息，否则可能由于无法定位，导致issue无法解决而被取消】

【标题描述】:【行存表压缩能力增强】指定压缩参数进行TPCC测试，与未压缩时相比，压缩率和性能劣化指标未达到预期
【测试类型：SQL功能/存储功能/接口功能/工具功能/性能/并发/压力长稳/故障注入/安全/资料/编码规范】【测试版本：2.0.0】问题描述

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):

【测试环境】(单机/1主x备x级联备):

【被测功能】:行存表压缩能力增强测试

【测试类型】:功能测试

【数据库版本】(查询命令: gaussdb -V):
3.1.0版本
【预置条件】:

【操作步骤】(请填写详细的操作步骤):
1、修改参数pca_shared_buffers='8GB'，重启数据库
2、创建两个数据库，一个用于未压缩测试，一个用于压缩测试
CREATE USER benchmarksql PASSWORD 'Test@test' SYSADMIN;
CREATE DATABASE benchmarksql_nocompress OWNER benchmarksql;
CREATE DATABASE benchmarksql_compress OWNER benchmarksql;
2、对用于未压缩的database导入100仓的数据

3、修改TPCC模型表sql及索引sql，指定压缩参数
fillfactor=100,compresstype=2, compress_chunk_size=1024,compress_level=0,prealloc_chunks=0,compress_byte_convert=0,compress_diff_convert=false
参考如下：
输入图片说明

4、数据导入成功后，对比表级别压缩率
\c benchmarksql_nocomress
\d+
\c benchmarksql_comress
\d+
5、对比库级别压缩率
\l+
6、对比查询效率

【预期输出】:
满足TPCC测试模型，压缩率满足2:1，性能劣化小于5%
【实际输出】:
表级别及库级别压缩率均未完全达到预期压缩率2：1，性能劣化未低于5%
表级别压缩率

执行效率
【原因分析】:

这个问题的根因
问题推断过程
还有哪些原因可能造成类似现象
该问题是否有临时规避措施
问题解决方案
预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

Hey @cathyli, Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss_bot.
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at Here to find the details.

Hi @cathyli, please use the command /sig xxx to add a SIG label to this issue.
For example: /sig sqlengine or /sig storageengine or /sig om or /sig ai and so on.
You can find more SIG labels from Here.
If you have no idea about that, please contact with @xiangxinyong , @zhangxubo .

您好，针对压缩率，我们这边统计为TPCC模型：

压缩率，统计方式为整体数据库压缩率，包含索引、数据等对象。在测试过程中，我们采用的压缩参数如下。这种测试方式，数据库初始化之后压缩率可以达到44.35%左右。且长期运行后，整体压缩率可以在30%左右。
- 索引

alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
  primary key (w_id);

alter table bmsql_district add constraint bmsql_district_pkey
  primary key (d_w_id, d_id);

alter table bmsql_customer add constraint bmsql_customer_pkey
  primary key (c_w_id, c_d_id, c_id) WITH (compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=1,compress_diff_convert=true,compress_byte_convert=true);

create index bmsql_customer_idx1
  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) WITH (compresstype=2,compress_chunk_size=1024,compress_level=1,compress_prealloc_chunks=4);

alter table bmsql_oorder add constraint bmsql_oorder_pkey
  primary key (o_w_id, o_d_id, o_id) WITH (compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=2,compress_diff_convert=true,compress_byte_convert=true);

create index bmsql_oorder_idx1
   on  bmsql_oorder (o_w_id, o_d_id, o_c_id) WITH (compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=3,compress_diff_convert=true,compress_byte_convert=true);

alter table bmsql_new_order add constraint bmsql_new_order_pkey
  primary key (no_w_id, no_d_id, no_o_id) WITH (compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=3,compress_diff_convert=true,compress_byte_convert=true) using index tablespace example2;

alter table bmsql_order_line add constraint bmsql_order_line_pkey
  primary key (ol_w_id, ol_d_id, ol_o_id, ol_number) WITH (compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=1,compress_diff_convert=true,compress_byte_convert=true);

alter table bmsql_stock add constraint bmsql_stock_pkey
  primary key (s_w_id, s_i_id) WITH (compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=1,compress_diff_convert=true,compress_byte_convert=true);

alter table bmsql_item add constraint bmsql_item_pkey
  primary key (i_id);

数据：

CREATE TABLESPACE example2 relative location 'tablespace2';
CREATE TABLESPACE example3 relative location 'tablespace3';
create table bmsql_config (
  cfg_name    varchar(30),
  cfg_value   varchar(50)
);-- DISTRIBUTE BY REPLICATION;

create table bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
)WITH (FILLFACTOR=80);-- DISTRIBUTE BY hash(w_id);

create table bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
 )WITH (FILLFACTOR=80);-- DISTRIBUTE BY hash(d_w_id);

create table bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500)
)WITH (FILLFACTOR=80,compresstype=2,compress_chunk_size=1024,compress_level=1,compress_prealloc_chunks=5) 
tablespace example2;
--DISTRIBUTE BY hash(c_w_id);

-- create sequence bmsql_hist_id_seq;

create table bmsql_history (
  hist_id  integer,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
)WITH (FILLFACTOR=80,compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=5,compress_diff_convert=true,compress_byte_convert=true);-- DISTRIBUTE BY hash(h_w_id);

create table bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
)WITH (FILLFACTOR=80,compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=5,compress_diff_convert=true,compress_byte_convert=true);-- DISTRIBUTE BY hash(no_w_id);

create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp
)WITH (FILLFACTOR=80,compresstype=2,compress_chunk_size=512,compress_level=1,compress_prealloc_chunks=3,compress_diff_convert=true,compress_byte_convert=true);-- DISTRIBUTE BY hash(o_w_id);

create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
)WITH (FILLFACTOR=80,compresstype=2,compress_chunk_size=1024,compress_level=1,compress_prealloc_chunks=3,compress_diff_convert=true,compress_byte_convert=true);-- DISTRIBUTE BY hash(ol_w_id);

create table bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer
);-- DISTRIBUTE BY REPLICATION;

create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24)
)WITH (FILLFACTOR=80,compresstype=2,compress_chunk_size=1024,compress_level=1,compress_prealloc_chunks=5) 
tablespace example3;
--DISTRIBUTE BY hash(s_w_id);

性能：我们统计方式为TPCC-整体性能。1小时TPCC值和非压缩性能差距在5%左右。

根据社区问题单处理流程，该问题没有更多的定位信息，已答复使用方法
问题取消，待再次出现问题后，再提issue

GVP openGauss / openGauss-server

内容风险标识

评论 (4)

GVPopenGauss / openGauss-server

内容风险标识