震惊 - Gbase 8s 的性能超出我的想象

Roger的数据库专栏 2023-12-04

596

南大通用也算是国内老四家国产数据库厂商了，其数据库产品为Gbase。最早主要是做MPP场景，后面也扩展到其他场景，最后对产品升级后统一命名Gbase，细分则包括：Gbase 8a、Gbase 8s、Gbase 8c等。

其中8a是MPP场景，8s是最早基于informix的版本，因此操作几乎与informix一致，8c是基于postgresql的版本。这里先简单测一下8s吧。

软件安装完毕后，先尝试创建一个database:

[gbasedbt@yashandb1 ~]$ dbaccess - -
Your evaluation license will expire on 2024-12-01 00:00:00
> 
> create database enmotech;


Database created.


> database enmotech;


Database closed.




Database selected.


>

数据库创建完后，开始创建一下测试表，仍然使用之前测试的脚本，确保结果集一致：

> create table test1(owner varchar2(20),object_type varchar2(10),object_id nummber);


 9628: Type (nummber) not found.
Error in line 1
Near character position 80
> create table test1(owner varchar2(20),object_type varchar2(10),object_id bigint);


Table created.


> create table test2(owner varchar2(20),object_type varchar2(10),object_id bigint);


Table created.


> 
>  create procedure test_gbase_hash()
> define i int; 
> let i = 1;
>  loop
>      if i > 1000000 then
          exit;
> >   else
>      insert into test1 values('ENMO','TABLE',i);
>   end if;
>   let i = i+1;
>   end loop;
>  end procedure;


Routine created.


> call test_gbase_hash();


Routine executed.


> select count(1) from test1;




         (count) 


         1000000


1 row(s) retrieved.


> 
> create procedure test_gbase_hash1()
> define i int; 
> let i = 1;
>  loop
>      if i > 500000 then
          exit;
> >   else
>      insert into test2 values('ENMO','TABLE',i);
>   end if;
>   let i = i+1;
>   end loop;
>  end procedure;


Routine created.


> call test_gbase_hash1();


  271: Could not insert new row into the table.


  131: ISAM error: no free disk space
Error in line 1
Near character position 1

可以看到，提示空间不足了，先进行一下扩容，通过dbaccess登陆进行扩展，发现报错：

> execute function task ('modify chunk extendable on',1);


  674: Routine (task) can not be resolved. 
Error in line 1
Near character position 22
>

看来还是要使用超级用户才行：



[gbasedbt@yashandb1 ~]$ dbaccess sysadmin -
Your evaluation license will expire on 2024-12-01 00:00:00


Database selected.


> execute function task ('modify chunk extendable on',1);






(expression)  Chunk 1 is now extendable. 


1 row(s) retrieved.


> ^C[gbasedbt@yashandb1 ~]$ 
[gbasedbt@yashandb1 ~]$ onstat -d
Your evaluation license will expire on 2024-12-01 00:00:00
On-Line -- Up 00:40:12 -- 175748 Kbytes


Dbspaces
address          number   flags      fchunk   nchunks  pgsize   flags    owner    name
4545d028         1        0x40001    1        1        2048     N  BA    gbasedbt rootdbs
 1 active, 2047 maximum


Chunks
address          chunk/dbs     offset     size       free       bpages     flags pathname
4545d258         1      1      0          78848      3                     PO-BE- /opt/GBASE/gbase/storage/rootdbs
 1 active, 32766 maximum


NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.




Expanded chunk capacity mode: always

从上面的输出来看，可以进行下一步测试工作了，Let's go on!

> truncate table test2;


Table truncated.


> call test_gbase_hash1();


Routine executed.


> select count(1) from test2;




         (count) 


          500000


1 row(s) retrieved.


>
> update test1 set owner='ENMOTECH' where OBJECT_ID > 10000 and OBJECT_ID < 50000;


39999 row(s) updated.


> update test1 set owner='yashan' where OBJECT_ID > 50000;


950000 row(s) updated.


> commit;


  256: Transaction not available.
Error in line 1
Near character position 5
> update test2 set owner='ENMOTECH' where OBJECT_ID > 10000 and OBJECT_ID < 50000;


39999 row(s) updated.


> update test2 set owner='yashan' where OBJECT_ID > 50000;


450000 row(s) updated.

这里可以看到dbaccess模式的操作，默认是自动提交的。到这里准备工作完毕，可以开始见证奇迹了！来看看Gbase 8s的hash join如何？

[gbasedbt@yashandb1 ~]$ export DBACCESS_SHOW_TIME=1
[gbasedbt@yashandb1 ~]$ dbaccess - -
Your evaluation license will expire on 2024-12-01 00:00:00
> database enmotech;


Database selected.


Elapsed time: 0.022 sec


> 


>  select b.owner, count(*)
>   from test1 a, test2 b
>  where a.object_id = b.object_id
 group by b.owner;> 






owner       yashan 
(count(*))  450000


owner       ENMOTECH 
(count(*))  39999


owner       ENMO 
(count(*))  10001


3 row(s) retrieved.


Elapsed time: 14.485 sec


> 
> SELECT b.owner,
>        sum(a.object_id)
FROM test1 a,
> >      test2 b
WHERE a.object_id = b.object_id
GROUP BY b.owner;> > 






owner  yashan 
(sum)  123750225000


owner  ENMOTECH 
(sum)  1199970000


owner  ENMO 
(sum)  50055000


3 row(s) retrieved.


Elapsed time: 9.347 sec


>

Oh my god！效果很惊人。。这是出奇的慢呀！我怀疑是不是我配置不够合理，翻了一下官方文档，参考做了一下优化调整，把bufferpool搞到10240m，然后再来看看效果：

[gbasedbt@yashandb1 ~]$ dbaccess - -
Your evaluation license will expire on 2024-12-01 00:00:00
> database enmotech;


Database selected.


Elapsed time: 0.024 sec


> SELECT b.owner,
>        count(*)
FROM test1 a,
     test2 b
> > > WHERE a.object_id = b.object_id
GROUP BY b.owner;> 






owner       yashan 
(count(*))  450000


owner       ENMOTECH 
(count(*))  39999


owner       ENMO 
(count(*))  10001


3 row(s) retrieved.


Elapsed time: 12.989 sec


> SELECT b.owner,
>        sum(a.object_id)
> FROM test1 a,
>      test2 b
> WHERE a.object_id = b.object_id
GROUP BY b.owner;
> 






owner  yashan 
(sum)  123750225000


owner  ENMOTECH 
(sum)  1199970000


owner  ENMO 
(sum)  50055000


3 row(s) retrieved.


Elapsed time: 8.624 sec

可以看到性能提升并不大，看起来还是比较慢的。这里进一步看看上述SQL的执行计划呢？

[gbasedbt@yashandb1 ~]$ cat sqexplain.out 


QUERY: (OPTIMIZATION TIMESTAMP: 12-02-2023 12:14:55)
------
SELECT b.owner,
       count(*)
FROM test1 a,
     test2 b
WHERE a.object_id = b.object_id
GROUP BY b.owner


Estimated Cost: 11
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By 


  1) gbasedbt.a: SEQUENTIAL SCAN


  2) gbasedbt.b: SEQUENTIAL SCAN




DYNAMIC HASH JOIN 
    Dynamic Hash Filters: gbasedbt.a.object_id = gbasedbt.b.object_id 


UDRs in query:
--------------
    UDR id  :   -24
    UDR name:   equal


Query statistics:
-----------------


  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                a
  t2                b


  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     1000000    1         1000000    00:00.99   4       


  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t2     500000     1         500000     00:00.94   4       


  type     rows_prod  est_rows  rows_bld  rows_prb  novrflo  time       est_cost
  ------------------------------------------------------------------------------
  hjoin    500000     1         500000    1000000   2        00:09.07   9       


  type     rows_prod  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  group    3          1         500000     00:10.84   3

说实话，上述的执行计划看起来有点费劲，只能从字面意思进行猜测。我们大致可以看出来在hjoin这一步耗时是最长的，高达9秒，另外group分组这里时间并不长。

之前我们测试Oracle 11g同样操作，时间大概是0.47s；openGauss(MogDB)大概是0.9s（启用向量化引擎是0.4s)。

这样简单估算一下，Gbase 8s hash join性能是openGauss的1/14 ?

提供数据库诊断、优化、架构设计、数据恢复等服务！加微信请扫码！

table gbase

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

震惊 - Gbase 8s 的性能超出我的想象

评论