MogDB v5版本的hash join性能也很炸裂

Roger的数据库专栏 2023-11-22

262

前几天写了几篇关于崖山数据库的文章，其中关于崖山hash join的测试引起了不少用户的兴趣，于是乎非常关注我们MogDB的hash join性能究竟如何。为了确保测试的准确性，我这里仍然使用相同的脚本来创建测试数据。

##首先创建测算表并插入数据

[omm@mogdb1 ~]$ gsql -d enmotech -r
gsql ((MogDB 5.0.3 build 86d963ad) compiled at 2023-10-13 09:17:48 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


enmotech=# create table t_test1(owner varchar(64),OBJECT_TYPE varchar(18),object_id BIGINT) with (storage_type=astore);
CREATE TABLE
Time: 5.428 ms
enmotech=# create table t_test2(owner varchar(64),OBJECT_TYPE varchar(18),object_id BIGINT) with (storage_type=astore);
CREATE TABLE
Time: 5.242 ms
enmotech=# 
enmotech=# do $$
enmotech$# declare
enmotech$# v_id integer := 1;
enmotech$# begin
enmotech$#   while v_id < 1000001 loop
enmotech$#   v_id = v_id+1;
enmotech$# INSERT INTO t_test1(owner,object_type,object_id) VALUES ('enmo','TABLE',v_id);
enmotech$#   end loop;
enmotech$# end $$;
 ANONYMOUS BLOCK EXECUTE
Time: 89704.480 ms
enmotech=#  
enmotech=# 
enmotech=# do $$
enmotech$# declare
enmotech$# v_id integer := 1;
enmotech$# begin
enmotech$#   while v_id < 500000 loop
enmotech$#   v_id = v_id+1;
enmotech$# INSERT INTO t_test2(owner,object_type,object_id) VALUES ('enmo','TABLE',v_id);
enmotech$#   end loop;
enmotech$# end $$;
ANONYMOUS BLOCK EXECUTE
Time: 43097.586 ms
enmotech=#  update t_test1 set owner='ENMOTECH' where OBJECT_ID > 10000 and OBJECT_ID < 50000;
UPDATE 39999
Time: 533.817 ms
enmotech=# update t_test1 set owner='yashan' where OBJECT_ID > 50000;
UPDATE 950001
Time: 4887.159 ms
enmotech=# update t_test2 set owner='ENMOTECH' where OBJECT_ID > 10000 and OBJECT_ID < 50000;
UPDATE 39999
Time: 392.826 ms
enmotech=# update t_test2 set owner='yashan' where OBJECT_ID > 50000;
UPDATE 450000
Time: 2272.866 ms

上述SQL的相关结果集与之前的测试是完全一致的。首先我们更新一下上述2个table的统计信息。

enmotech=# analyze verbose t_test1;
INFO:  analyzing "public.t_test1"(dn_6001 pid=31688)
INFO:  ANALYZE INFO : "t_test1": scanned 12757 of 12757 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows(dn_6001 pid=31688)
ANALYZE
Time: 121.524 ms
enmotech=# analyze verbose t_test2;
INFO:  analyzing "public.t_test2"(dn_6001 pid=31688)
INFO:  ANALYZE INFO : "t_test2": scanned 6347 of 6347 pages, containing 499999 live rows and 0 dead rows; 30000 rows in sample, 499999 estimated total rows(dn_6001 pid=31688)
ANALYZE
Time: 82.203 ms

接下来就是见证奇迹的时刻！！！看看相同SQL在MogDB上的性能表现如何:

enmotech=# select b.owner, count(*)
enmotech-#   from t_test1 a, t_test2 b
enmotech-#  where a.object_id = b.object_id
enmotech-#  group by b.owner;
  owner   | count  
----------+--------
 yashan   | 450000
 ENMOTECH |  39999
 enmo     |  10000
(3 rows)


Time: 910.840 ms

我们可以看到时间大约是0.9s，看上去比Oracle慢了一半左右，如果是打开向量化执行引擎，那么性能可以提升2倍，如下：

enmotech=# set try_vector_engine_strategy=force;
SET
Time: 0.454 ms
enmotech=# select b.owner, count(*)
enmotech-#   from t_test1 a, t_test2 b
enmotech-#  where a.object_id = b.object_id
enmotech-#  group by b.owner;
  owner   | count  
----------+--------
 enmo     |  10000
 ENMOTECH |  39999
 yashan   | 450000
(3 rows)


Time: 394.585 ms
enmotech=#

我们可以看到，启用向量化执行引擎后，该统计SQL的执行时间大概是0.39s，比Oracle中的效率还要略高一点点。

同时当t_test1数据量分别为400w和1600w时的性能仍然是有所保证的：

+++t_test1 400w记录


enmotech=# select b.owner, count(*)
enmotech-#   from t_test1 a, t_test2 b
enmotech-#  where a.object_id = b.object_id
enmotech-#  group by b.owner;
  owner   |  count  
----------+---------
 enmo     |   40000
 ENMOTECH |  159996
 yashan   | 1800000
(3 rows)


Time: 1270.187 ms


+++ t_test1 1600w记录


enmotech=# select b.owner, count(*)
enmotech-#   from t_test1 a, t_test2 b
enmotech-#  where a.object_id = b.object_id
enmotech-#  group by b.owner;
  owner   |  count  
----------+---------
 enmo     |  160000
 ENMOTECH |  639984
 yashan   | 7200000
(3 rows)


Time: 4600.262 ms
enmotech=#

之前测试Oracle 11204的的性能在100w、400w、1600w的情况下分别是:0.47秒,1.29秒,4,3s。

MogDB v5对应的执行情况分别是：0.39秒,1.27秒,4.6秒。

同样测试hash+sum的情况下，效率也是非常的不错：

+++ t_test1 1600w记录


enmotech=# SELECT b.owner,
enmotech-#        sum(a.object_id)
enmotech-# FROM t_test1 a,
enmotech-#      t_test2 b
enmotech-# WHERE a.object_id = b.object_id
enmotech-# GROUP BY b.owner;
  owner   |      sum      
----------+---------------
 enmo     |     800879984
 ENMOTECH |   19199520000
 yashan   | 1980003600000
(3 rows)


Time: 4866.044 ms

因为不同平台的内存设置并非完全一样，有细微差异，因此可能导致测试数据并非100%准确。总的来讲，可以供大家参考一下吧。

mogdb

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

MogDB v5版本的hash join性能也很炸裂

评论