循序渐进丨openGauss / MogDB 列存表的delta表测试

MogDB 2024-10-18

349

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

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

一、参数配置

数据库里开启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

二、创建测试表

创建一张列存表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同名，列存表有几个列，除了原始表，就会多生成几个${oid}_c $的文件。


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

三、插入数据测试

3.1 插入单条数据

列存表

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


opengauss=# insert into test_cstore values(1,'test1');
INSERT 0 1
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_cudesc_25150;
 col_id | cu_id | min | max | row_count | cu_mode | size | cu_pointer | magic | extra
--------+-------+-----+-----+-----------+---------+------+------------+-------+-------
(0 rows)
 
opengauss=# select * from cstore.pg_delta_25150;
 id | name  |          insert_time
----+-------+-------------------------------
  1 | test1 | 2023-12-07 22:17:53.571677+08
(1 row)

从操作系统上查看，数据情况如下，数据仅写入了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

行存表

行存表直接按照上述列存表的数据进行一次测试：


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

测试结果

经过测试，列存表test_cstore的deltarow_threshold指定为1000，压缩级别为默认的low。后当插入单条数据，数据条数未达到deltarow_threshold指定值的情况下，系统上查看物理文件，可以看出数据仅存储到了pg_delta表中。
原始列存表中没有数据。数据库中读取的表的数据来自于pg_delta表。相当于在数据库层面做了一个映射。pg_delta表和原列存表的数据均取自pg_delta对应的物理文件。
产生的pg_delta表的数据存储方式是行存，它的结构以及物理文件存储形式和单纯以行存表保存的数据完全一致。同样数据的情况下，列存的pg_delta表和普通行存表数据文件对比如下，数据块的排布也是完全一致：

其他现象分析

测试过程，做过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)

3.2 继续插入999条数据

列存表

在上述的基础上插入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表里能查到条数。

而去系统上看物理文件，可以看到1000条数据依旧存在pg_delta表中。

行存表

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

测试结果

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

3.3 继续插入1条单条数据

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

比赛安排

列存表里再插入一条，满足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文件的大小

行存表

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

比赛安排

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

手动做vacuum deltamerge操作

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

这个时候查询：

相当于col_id的1、2、3分别对应三个列。三列分别在对原始列存表做了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文件的结构进行后续分析：

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

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

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

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

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

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

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

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

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

测试结果

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

关于作者

阎书利，云和恩墨PG技术顾问，PostgreSQL ACE，《快速掌握 PostgreSQL 版本新特性》一书副主编，中国PG分会认证讲师，PGfans 2021年度MVP，Gauss松鼠会2021年度优秀会员，拥有PGCM、OCP(MySQL)等十多项数据库认证，目前主要从事于 PostgreSQL、openGauss / MogDB 的运维工作。

END

MogDB 是云和恩墨基于 openGauss 开源内核进行增强提升，推出的一款安稳易用的企业级关系型数据库。其具备金融级高可用和全密态计算的极致安全、面向多核处理器的极致性能、AI自诊断调优的极致智能能力，能够满足从核心交易到复杂计算的企业级业务需求。

访问官网了解更多：www.mogdb.io

产品兼容适配申请：partner@enmotech.com

加微信进入交流群：Roger_database

文章转载自MogDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

循序渐进丨openGauss / MogDB 列存表的delta表测试

评论