秒杀 SQL 的开源神器，测试报告来了

最新推荐文章于 2024-04-28 08:44:39 发布

逛逛GitHub

最新推荐文章于 2024-04-28 08:44:39 发布

阅读量4.5k

点赞数

文章标签： sql 数据库 oracle

原文链接：https://mp.weixin.qq.com/s?__biz=MzUxNjg4NDEzNA==&mid=2247515410&idx=1&sn=eeca3368077ef8694d1c7f1306ce10ad&chksm=f9a26edbced5e7cd1a91c07f86a71e8b3a89f65069344a39e790100503d0e0b1c8e4cc56a7f5&scene=126&sessionid=0

版权

一、测试任务

TPCH 100G。

TPCH是国际标准，具体内容不再过多解释。

需要说明的是，TPCH 虽然有 22 个题，但仍然不能全面反映出被测系统对实际业务的响应性能。主要原因如下两点：

1. TPCH中问题比较常规，没有涉及序运算，分步运算也较简单。而实际业务中有性能瓶颈的运算，其复杂度通常会远高于 TPCH，会大量涉及序运算和分步计算；

2. 测试问题已经被长期公开，有些数据库可能会专门做相应的优化；

当然，作为国际标准，也会有一定的参考价值。

二、对比技术

我们选择了下面几个产品用为对比技术

ClickHouse, 传说中世界上最快的OLAP数据库，测试版本 23
Starrocks, 宣称更快的OLAP数据库，测试版本 2.5.2
Oracle，使用量很大，常常作为数据库性能测试的对比标杆，测试版本 19

Oracle不是专业的OLAP数据库，性能指标会弱于其它产品，仅作为参考。

三、测试环境

单台物理服务器，配置：

2颗Intel3014，主频1.7G，共12核CPU

64G内存

SSD固态硬盘

TPCH 100G中最大表也就约70G，简单压缩后就很可能小于机器的物理内存。为了能测试出这些产品的外存计算能力以及对内存的敏感性，我们使用了虚拟机来限制CPU数量和内存，根据业界较常见的云虚拟机规格，我们设计了两套测试环境：

VM1：8CPU，32G内存

VM2：4CPU，16G内存

Starrocks至少要安装两个节点BE和FE，将承担计算任务的BE安装在虚拟机上，管理节点FE安装在物理机上，这样不会影响测试效果。

SPL、Clickhouse和Oracle都只要在虚拟机下安装就可以了。

SPL、Clickhouse、Starrocks在两套虚拟机上都测试了，Oracle因为仅作为参考，只在VM1测试了。

四、数据准备

将TPCH工具生成的文本数据做了如下转换：

1. 维表的单字段主键和事实表中的对应外键做序号化处理（TPCH的几个维表主键原本就是序号）

2. 所有数据表按主键排序

将改造过的数据导入到数据库中。再将：

3. 枚举字段串字段值转换成序号、日期型字段转换成整数

然后生成SPL的组表文件。

具体动作可参考这套学习资料：用 TPCH 练习性能优化

专业的OLAP数据库通常会根据元数据信息自动做上述的第3步以优化数据类型，而SPL没有元数据，缺乏自动优化能力，需要用代码事先处理。不过，即使加上这部分动作，大部分情况时，SPL代码也仍比SQL更短。

五、测试过程

1. SPL测试

用SPL社区版和企业版分别写了两套脚本，在VM1上用8线程并行，在VM2上用4线程并行。

SPL可以使用与SQL不同的算法，对于较复杂的查询，其代码更简单且计算复杂度更低，通常会有性能上的优势。

TPCH有22个题，依次罗列并讲解占篇幅太大，这里将SPL代码打包供下载阅读：

PL-TPCH.zip

右侧扫码下载查看 ⇨

脚本理解可以参考：用 TPCH 练习性能优化

需要指出的是，和上述学习资料中的优化代码不同，这次测试代码没有使用任何预加载动作，所有运算都是临时从文件中读取数据再计算。有元数据信息的专业数据库有时可以预加载一些小数据表，没有元数据的SPL也需要人工处理预加载（可参考上述学习资料），但考虑到小内存场景，这次测试没有使用这个技巧。

2. SQL测试

使用TPCH提供的SQL语句在Starrocks、Clickhouse和Oracle中运行。SQL语句本身不能描述低复杂的算法，只能依赖数据库的优化引擎。

用监控工具观察发现：Starrocks和Clickhouse在VM1上使用了全部8个CPU运行，在VM2上使用了全部4个CPU运行，并且使用率足够高，说明这款数据库都能充分利用CPU资源。而Oracle（仅在VM1）用8并行时，所有CPU的使用率都只在50%左右，用4个并行时，CPU使用率才能到达90%以上，说明Oracle还不能充分利用CPU资源。

测试过程中，如果某题运行10分钟后还未出结果，就终止运行，在测试结果中记录“>600”。

Clickhouse的SQL不支持子查询中引用主查询的字段，TPCH有几个标准SQL语句无法直接执行（报语法错误)，需要改写后才能运行。下面列出改写后的SQL语句：

Q2:

select  s_acctbal, s_name,  n_name,  p.p_partkey,  p_mfgr,  s_address,  s_phone,  s_comment
from  part p,  partsupp,  supplier,  nation,  region, (
      select  p_partkey, min(ps_supplycost) as supplycost
      from  partsupp,  part, supplier,  nation,  region
      where
        p_partkey = ps_partkey
        and s_suppkey = ps_suppkey
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'ASIA'
      group by p_partkey
  ) pps
where
  p.p_partkey = pps.p_partkey
  and ps_supplycost = pps.supplycost
  and p.p_partkey = ps_partkey
  and s_suppkey = ps_suppkey
  and p.p_size = 25
  and p.p_type like '%COPPER'
  and s_nationkey = n_nationkey
  and n_regionkey = r_regionkey
  and r_name = 'ASIA'
order by  s_acctbal desc, n_name,  s_name,  p.p_partkey
limit 100;

Q4:

select
    o_orderpriority,
    count(*) as order_count
from (
    select 
        distinct l_orderkey,o_orderpriority
    from orders
    join lineitem on 
        l_orderkey = o_orderkey 
    where
        o_orderdate >= date '1995-10-01'
        and o_orderdate < date '1995-10-01' + interval '3' month
        and l_commitdate < l_receiptdate
)
group by
    o_orderpriority
order by
    o_orderpriority;

Q20:

select s_name, s_address
from supplier, nation
where
  s_suppkey in (
    select ps_suppkey
    from partsupp, (
        select l_partkey lpk, l_suppkey lsk, 0.5 * sum(l_quantity) as lq
        from lineitem
        where
           l_shipdate >= date '1995-01-01'
           and l_shipdate < date '1995-01-01' + interval '1' year
       group by l_partkey, l_suppkey
    ) li
    where
      ps_partkey = li.lpk
      and ps_suppkey = li.lsk
      and ps_partkey in (
        select p_partkey from part
        where p_name like 'bisque%'
      )
      and ps_availqty > li.lq
  )
  and s_nationkey = n_nationkey
  and n_name = 'CHINA'
order by s_name
limit 100;

Q21:

改写困难，放弃执行。

Q22:

select
  cntrycode,
  count(*) as numcust,
  sum(c_acctbal) as totacctbal
from
  (
    select
      substr(c_phone, 1, 2) as cntrycode,
      c_acctbal
    from
      customer
    where
      substr(c_phone, 1, 2) in
        ('11', '14', '15', '19', '20', '21', '23')
      and c_acctbal > (
        select
          avg(c_acctbal)
        from
          customer
        where
          c_acctbal > 0.00
          and substr(c_phone, 1, 2) in
            ('11', '14', '15', '19', '20', '21', '23')
      )
      and c_custkey not in (
        select distinct o_custkey from orders
      )
  ) as custsale
group by
  cntrycode
order by
  cntrycode;

六、测试结果

VM1（单位：秒）

TPCH编号	SPL社区版	SPL企业版	Starrocks	Clickhouse	Oracle	Snowflake
1	29.9	9.7	14.0	15.4	114.3	7.3
2	2.7	1.3	0.6	17.3	1.9	3.0
3	17.8	8.8	9.8	内存溢出	165.8	3.3
4	7.4	4.9	5.7	内存溢出	158.4	3.0
5	35.3	8.9	13.1	内存溢出	174.5	4.0
6	8.6	4.5	3.9	4.8	126.7	0.5
7	24.1	10.5	12.4	内存溢出	181.5	3.8
8	45.3	6.9	8.3	内存溢出	209.7	4.2
9	66.3	16.8	21.3	内存溢出	256.0	14.7
10	16.4	8.3	11.1	58.3	195.6	8.2
11	3.3	0.9	1.3	6.7	8.7	1.7
12	10.3	4.9	4.8	10.7	186.0	1.7
13	53.9	12.1	21.3	134.1	33.3	7.7
14	12.8	3.3	4.6	10.2	170.0	1.3
15	14.3	4.7	7.1	11.2	161.8	0.8
16	10.2	2.7	2.9	4.0	10.8	2.6
17	24.7	5.3	4.2	44.6	156.5	2.8
18	18.6	6.4	20.8	内存溢出	416.8	12.0
19	10.0	5.8	6.0	>600	144.1	2.2
20	14.3	5.2	5.2	31.2	171.0	1.5
21	25.5	11.9	14.5	语法错误	360.7	9.0
22	11.0	2.5	1.9	8.4	37.7	1.7
合计	462.7	146.3	194.8	-	3441.8	96.5

这里我们附加了一列Snowflake自己做的测试（从其SIGMOD论文中的统计图扒出来，数值可能不精确）供参考，该项测试使用Snowflake Medium级集群，由4台8核16线程机器构成，CPU也更好，硬件资源相当于本次测试的4-8倍以上。

VM2（单位：秒）

TPCH编号	SPL社区版	SPL企业版	Starrocks	Clickhouse
1	61.8	21.5	26.0	29.1
2	3.7	2.0	0.9	31.1
3	38.5	20.8	17.5	内存溢出
4	14.3	12.1	10.8	内存溢出
5	72.1	21.3	25.3	内存溢出
6	19.8	7.5	7.9	8.9
7	51.4	24.1	22.5	内存溢出
8	77.8	18.5	14.3	内存溢出
9	100.8	39.0	43.0	内存溢出
10	33.5	22.4	20.7	内存溢出
11	4.9	1.2	1.9	12.0
12	21.3	8.8	12.0	19.3
13	77.0	22.8	44.5	内存溢出
14	28.3	5.4	9.2	16.6
15	25.7	10.3	13.4	20.0
16	13.6	4.4	5.8	7.0
17	42.2	14.0	8.0	68.6
18	32.5	14.8	内存溢出	内存溢出
19	24.2	14.2	11.8	>600
20	30.2	15.5	9.6	47.0
21	52.2	20.9	28.8	语法错误
22	21.8	3.5	3.5	11.7
合计	847.6	325.0	337.8	-

注：Starrocks有一题未跑出来，合计值不包括此题。

七、结果点评

总体来看，SPL 企业版的成绩最好，Starrocks 其次，SPL 社区版第三，Oracle 的表现和这些专业计算技术产品相差非常远。
Clickhouse表现很差，除了之前说过的某些 SQL 不支持外，还有许多题跑不出来，甚至有慢于 Oracle 的。即使能跑出来的题，也大都比 SPL 企业版和 Starrocks 更慢，减小内存后跑不出来就更多。传说中的世界上最快看来是严重名不符实的，应用场景就很少，能用的场景也不算快。
Starrocks在 SQL 数据库中表现优秀，它宣称采用了 CPU 的向量计算技术而拥有最快的 SQL 引擎（SPL 不是 SQL 引擎），至少从这个测试上看可以说是实至名归的。不过在小内存时也会发生内存溢出，说明其对资源要求较高。
SPL的社区版和企业版都能在小内存环境下跑出所有题，对资源要求较低。
SPL社区版会使用 Java 对象存储字段值，能获得灵活的泛型效果，但占用内存大且计算过程中判断很多。采用了低复杂度的算法后，性能远远超过 Oracle，但和 Starrocks 还存在差距。说明仅在工程上优化也会有相当大的潜力。
SPL企业版也实现了向量式计算（放弃泛型性），这方面和 Starrocks 接近，再在低复杂度算法的加持下，总体性能表现最为优异。但由于 Java 实现时还不能直接利用 CPU 特性，只是克服社区版中对象过大及判断过多的问题，所以不是每个题都能超越 Starrocks。
测试结论：

SPL和 Starrocks 都是优秀的计算引擎，对于 TPCH 这类不算复杂的任务，SPL 略占优势但并不太大。

Clickhouse计算能力很差，无法和 SPL 及 Starrocks 相提并论。

Oracle稳定，但性能和专业的 OLAP 计算引擎差距很大，不适合承担大计算量任务。

GitHub：https://github.com/SPLWare/esProc

重磅！开源SPL交流群成立了

简单好用的SPL开源啦！

为了给感兴趣的技术人员提供一个相互交流的平台，

特地开通了交流群（群完全免费，不广告不卖课）

需要进群的朋友，可长按扫描下方二维码

本文感兴趣的朋友，请到阅读原文去收藏 ^_^

逛逛GitHub

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
秒杀 SQL 的开源神器，测试报告来了

一、测试任务TPCH 100G。TPCH是国际标准，具体内容不再过多解释。需要说明的是，TPCH 虽然有 22 个题，但仍然不能全面反映出被测系统对实际业务的响应性能。主要原因如下两点：1. TPCH中问题比较常规，没有涉及序运算，分步运算也较简单。而实际业务中有性能瓶颈的运算，其复杂度通常会远高于 TPCH，会大量涉及序运算和分步计算；2. 测试问题已经被长期公开，有些数据库可能会专门做相应的...
复制链接

扫一扫