openGauss/MogDB列存表的delta表测试-每日运维

列存储格式是OLAP类数据库系统最常用的数据格式，适合复杂查询、范围统计类查询的在线分析型处理系统。cstore列存储的主体数据文件以CU为I/O单元，只支持追加写操作，因此cstore只有读共享缓冲区。CU间和CU内的可见性由对应的CUDESE表（astore表）决定，因此其可见性和并发控制原理与行存储astore基本相同。

对于cstore表的单条插入以及更新操作，提供与每个cstore表对应的delta表（astore行存储表），来接收单条插入或单条更新的元组，以降低CU文件的碎片化，如下针对列存表的delta表进行部分测试。

1.1参数配置

数据库里开启enable_delta_store参数。

`opengauss=# select * from pg_settings where name like '%delta%'; -[ RECORD 1 ]------------------------------- name | enable_delta_store setting | on unit | category | Query Tuning short_desc | Enable delta for column store. extra_desc | context | postmaster vartype | bool source | configuration file min_val | max_val | enumvals | boot_val | off reset_val | on sourcefile | /opt/mogdb/data/postgresql.conf sourceline | 803`

1.2 创建测试表

创建一张列存表test_cstore和一张行存表test_astore, 列存表test_cstore的deltarow_threshold指定为1000，压缩级别为默认的low。

`opengauss=# create table test_cstore(id int,name varchar(20),insert_time timestamptz not null default now()) with (orientation=column,deltarow_threshold=1000); CREATE TABLE opengauss=# create table test_astore(id int,name varchar(20),insert_time timestamptz not null default now()) with (orientation=row); CREATE TABLE`

根据系统表查询到两张表对应的oid如下,列存表在创建的时候，会对应在cstore这个schema下自动生成两张表，分别为pg_cudesc_{oid} 和pg_delta_{oid}两张表，${oid}对应列存表原始表的oid。

`opengauss=# select oid,relname from pg_class where relname like '%test_%'; oid | relname -------+------------- 25150 | test_cstore 25315 | test_astore (2 rows)`

根据函数可以查询到表在数据目录下的位置，对应的在系统上的表的物理文件的名字和表的oid同名，列存表有几个列，除了原始表，就会多生成几个oidc{oid}_coidc的文件。

`opengauss=#select pg_relation_filepath('test_cstore'); pg_relation_filepath ---------------------- base/25149/25150 (1 row)`
`opengauss=# select pg_relation_filepath('cstore.pg_cudesc_25150'); pg_relation_filepath ---------------------- base/25149/25157 (1 row) opengauss=# select pg_relation_filepath('cstore.pg_delta_25150'); pg_relation_filepath ---------------------- base/25149/25154 (1 row)`
omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ ll base/25149/25150* -rw------- 1 omm dbgrp 0 Dec 7 21:45 base/25149/25150 -rw------- 1 omm dbgrp 0 Dec 7 21:45 base/25149/25150_C1.0 -rw------- 1 omm dbgrp 0 Dec 7 21:45 base/25149/25150_C2.0 -rw------- 1 omm dbgrp 0 Dec 7 21:45 base/25149/25150_C3.0 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ ll base/25149/25157* -rw------- 1 omm dbgrp 0 Dec 7 21:45 base/25149/25157 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ ll base/25149/25154* -rw------- 1 omm dbgrp 0 Dec 7 21:45 base/25149/25154
如下是行存表的文件情况。

`pg_relation_filepath ---------------------- base/25149/25315 (1 row) opengauss=# q omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ ll base/25149/25178* -rw------- 1 omm dbgrp 0 Dec 7 21:46 base/25149/25315`

1.3 插入数据测试

1.3.1 插入单条数据

1.3.1.1 列存表

从数据库中看，在原始列存表test_cstore和pg_delta表里分别有一条记录。

从操作系统上查看，数据情况如下，数据仅写入了pg_delta表中。原始列存表中没有数据。数据库中读取的表的数据来自于pg_delta表。

#原始列存表 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ du -sh base/25149/25150* 0 base/25149/25150 0 base/25149/25150_C1.0 0 base/25149/25150_C2.0 0 base/25149/25150_C3.0 #pg_cudesc表 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ du -sh base/25149/25157* 0 base/25149/25157 #pg_delta表 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ du -sh base/25149/25154* 8.0K base/25149/25154

pg_delta表中的数据按行存方式存储。

opengauss=# select * from test_cstore; id | name | insert_time ----+-------+------------------------------- 1 | test1 | 2023-12-07 22:17:53.571677+08 (1 row) opengauss=# select * from cstore.pg_delta_25150; id | name | insert_time ----+-------+------------------------------- 1 | test1 | 2023-12-07 22:17:53.571677+08 (1 row) opengauss=# select pg_relation_filepath('cstore.pg_delta_25150'); pg_relation_filepath ---------------------- base/25149/25154 (1 row)

使用hexdump分析表数据文件中的内容。

omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ hexdump -C base/25149/25184 00000000 00 00 00 00 e0 4d 22 1e c6 ca 40 00 2c 00 d0 1f |.....M"...@.,...| 00000010 00 20 06 20 00 00 00 00 67 3a 00 00 00 00 00 00 |. . ....g:......| 00000020 00 00 00 00 00 00 00 00 d0 9f 60 00 00 00 00 00 |..........`.....| 00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00001fd0 03 00 00 00 00 00 00 00 0c 00 00 00 00 00 00 00 |................| 00001fe0 01 00 03 00 02 0b 18 00 01 00 00 00 0d 74 65 73 |.............tes| 00001ff0 74 31 00 00 00 00 00 00 5d 3f f1 e6 ea ae 02 00 |t1......]?......| 00002000

16进制的01对应的10进制的1

16进制的74对应的10进制的116,ascii 码对应为t
16进制的65对应的10进制的101,ascii 码对应为e
16进制的73对应的10进制的115,ascii 码对应为s
16进制的74对应的10进制的115,ascii 码对应为t
16进制的31对应的10进制的49,ascii 码对应为1

5d 3f f1 e6 ea ae 02 00部分应该存储的是表中的timestamp类型数据，具体转化方式未深究，待后续分析。

1.3.1.2 行存表

行存表直接按照上述列存表的数据进行一个

opengauss=# insert into test_astore select * from test_cstore ; INSERT 0 1 opengauss=# select * from test_astore; id | name | insert_time ----+-------+------------------------------- 1 | test1 | 2023-12-07 22:17:53.571677+08 (1 row) opengauss=# select pg_relation_filepath('test_astore'); pg_relation_filepath ---------------------- base/25149/25315 (1 row) opengauss=# q omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ du -sh base/25149/25315* 8.0K base/25149/25315 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ du -sh base/25149/25315* 8.0K base/25149/25315 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ hexdump -C base/25149/25315 00000000 00 00 00 00 28 ed 2e 1e 18 eb 40 00 2c 00 d0 1f |....(.....@.,...| 00000010 00 20 06 20 00 00 00 00 7d 3a 00 00 00 00 00 00 |. . ....}:......| 00000020 00 00 00 00 00 00 00 00 d0 9f 60 00 00 00 00 00 |..........`.....| 00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00001fd0 06 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00001fe0 01 00 03 00 02 09 18 00 01 00 00 00 0d 74 65 73 |.............tes| 00001ff0 74 31 00 00 00 00 00 00 5d 3f f1 e6 ea ae 02 00 |t1......]?......| 00002000

1.3.1.3测试结果

1.经过测试，列存表test_cstore的deltarow_threshold指定为1000，压缩级别为默认的low。后当插入单条数据，数据条数未达到deltarow_threshold指定值的情况下，系统上查看物理文件，可以看出数据仅存储到了pg_delta表中。

2．原始列存表中没有数据。数据库中读取的表的数据来自于pg_delta表。相当于在数据库层面做了一个映射。pg_delta表和原列存表的数据均取自pg_delta对应的物理文件。

3.产生的pg_delta表的数据存储方式是行存，它的结构以及物理文件存储形式和单纯以行存表保存的数据完全一致。同样数据的情况下，列存的pg_delta表和普通行存表数据文件对比如下,数据块的排布也是完全一致。

1.3.1.4 其他现象分析

测试过程，做过delete from并做了vacuum full的操作，列存表的原始表的物理文件位置发生了变化。底层在vacuum full的时候，创建了新的物理文件，整理完数据后，修改了表对应指向的物理文件映射。虽然依旧原始表对应的物理文件中没有数据，但是由于列存表原表的物理文件的名字和oid不一致，直接通过名字无法知道对应的cstore下的两张表是哪两张。

omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ ll base/25149/25181 -rw------- 1 omm dbgrp 0 Dec 8 00:21 base/25149/25181 omm@ubuntu-linux-22-04-desktop:/opt/mogdb/data$ du -sh base/25149/25181 0 base/25149/25181

但是可以通过pg_class里的oid和relfilenode关联。找到列存表原始表的新物理文件对应的oid以及其对应的两张cstore的相关的表。

opengauss=# select pg_relation_filepath('test_cstore'); pg_relation_filepath ---------------------- base/25149/25181 (1 row)

opengauss=# select oid,relfilenode from pg_class where relfilenode='25181'; oid | relfilenode -------+------------- 25150 | 25181 (1 row) opengauss=# dt cstore.*25150* List of relations Schema | Name | Type | Owner | Storage --------+-----------------+-------+-------+----------------------- cstore | pg_cudesc_25150 | table | omm | {internal_mask=33031} cstore | pg_delta_25150 | table | omm | {internal_mask=32768} (2 rows)

1.3.2 继续插入999条

1.3.2.1 列存表

在上述的基础上插入999条数据，凑够列存表test_cstore的deltarow_threshold的 1000阈值。

insert into test_cstore select generate_series(1,999),left(md5(random()::text),10);

可以发现1000条数据，达到了deltarow_threshold阈值的情况下，数据依然在数据库里原始表和pg_delta表里能查到条数。
而去系统上看物理文件，可以看到100条数据依旧存在pg_delta表中。

1.3.2.2 行存表

依旧和列存插入一样的数据。

1.3.2.3测试结果

列存表pg_delta里的数据在正好达到了deltarow_threshold阈值的时候，并没有任何反应，pg_delta里的数据并没有如预期的转换成列存形式存储在cu里。

1.3.3 继续插入1条单条数据

在原始列存表test_cstore和pg_delta表里分别再插入一条记录。

1.3.3.1 列存表

列存表里再插入一条，满足1001条，超过deltarow_threshold阈值，看是否有反应。发现也没有触发行转列。数据依旧存储在pg_delta表里。

insert into test_cstore values(1001,'test1001'); select count(*) from test_cstore; select count(*) from cstore.pg_cudesc_25150; select count(*) from cstore.pg_delta_25150;

如下是列存的三张表物理文件的大小。以及cu文件的大小

1.3.3.2 行存表

行存表预期正常，此处无需做对比。

1.3.3.3 测试结果

列存表里再插入一条，满足1001条，超过deltarow_threshold阈值，看是否有反应。发现也没有触发行转列。数据依旧存储在pg_delta表里。

1.3.3.4 手动做一下vacuum deltamerge操作

手动对原始列存表做一次vacuum merge操作之后。
发现pg_delta表里的数据已经清空了，而pg_cudesc表里有了四条记录，但是原始列存表的条数是不变的，此外原始列存表对应的物理文件也是自始至终不会存储数据的，数据只存在pg_detla表或者cu对应的列存表里。

这个时候查询

相当于col_id的1，2，3分别对应三个列。-10这个id对应的的暂时未知。三列分别在对原始列存表做了vacuum DELTAMERGE后，从pg_delta中把数据按列分别放到不同的cu文件里。此外，原始的pg_delta表会在做完vacuum DELTAMERGE后，抛弃原来的物理文件，直接使用新的物理文件，pg_delta表的relfilenode会发生变化。

更换relfilenode，使用新的物理文件的原因猜想可能是为了并发操作，尽可能减少行列转换过程，pg_delta表对更新或者插入的影响。可以一边用旧的文件进行行转列，一边用新的物理文件继续追加新的数据到pg_delta表里，vacuum DELTAMERGE过程申请AccessExclusiveLock。（待验证）

其中，生成的C2文件内容如下，转成规范十六进制+ASCII显示便于观察，这一个文件预期应该存储第二列name列的数据。

查找到之前插入的test1001。可以看到它相邻的都是这一列上对应的数据。列上的数据是堆积在一起的。

可参照cu文件的结构进行后续分析。

1.3.4 单点查询，数据分别在pg_delta和cu里区别

870f6110e6的数据在cu里，test1002的数据在pg_delta表里，可以看到，没有索引，仅做单点查询的情况下，无论数据是在cu表里还是在pg_delta里，执行计划是一样的，都显示cstore scan 访问原始列存表。

数据库重启不会影响数据在cu文件和pg_delta里的分布，也不会进行行列转换。

1.3.5 行转列的最大记录数限制测试（MAX_BATCHROW）

因为创建表的时候没有指定MAX_BATCHROW选项，默认为60000，所以在数据加载过程中一个存储单元可以容纳记录的最大数目是60000。

1.3.5.1 列存表一次性插入60000条数据

一次性插入60000条数据，发现会产生一个行数为60000的cu_id。

1.3.5.2 列存表一次性插入61000条数据

而插入61000，则会把61000分成两个cu_id。一个包含60000行，一个包含1000行。

1.3.5.3 测试结果

1.一次性插入数条数在进行行转列的时候，有最大值限制，60000是最大值，超出的会拆分成另一个cu_id，然后把记录写入到pg_cudesc里。
2.这次因为单次的插入值大于deltarow_threshold，直接触发了行转列，而不需要手动vacuum deltamerge操作。deltarow_threshol是指定列存表导入时小于多少行的数据进入delta表，而并不是一个delta中数据累积触发的，是单次插入的行数阈值。跟单次写入的行数有关。

openGauss/MogDB列存表的delta表测试

1.3 插入数据测试