677.【数据库评测】Cloudwave 4.0 单机版 VS Starrocks 3.0 单机版

一、测试目的

• 在本文中，七镜将基于 Star Schema Benchmark 标准测试集对两款MPP数据库进行性能比对测试，以评估它们在不同场景下的表现。
• 七镜将使用一些常见的性能指标，如响应时间、CPU最大占用率等，来对比不同数据库的优劣。
• 七镜的测试目标是为读者提供一个参考，帮助读者选择适合自己需求的数据库。

二、测试环境

• 在本文中，七镜将使用以下几种数据库进行测试：
  • Cloudwave 4.0：拥有顶级性能的国产云原生数据仓库，目前已知的国内首款类snowflake的存算分离架构数据库（从10年积累的传统分布式数据库底层改造，支持彻底的存算分离，2021年9月份发版）。
  • Starrocks 3.0：新一代极速全场景 MPP (Massively Parallel Processing) 数据库。
• 七镜的测试环境如下：
  • 硬件配置：CPU 64核，内存 256GB，1TB容量阿里云 ESSD pl1 云盘
  • 操作系统：Linux CentOS 7.6
  • 测试工具：测试脚本、分析脚本、sql脚本（见附加）
  • 测试数据：使用 SSB 基准测试集生成的数据，总量约为 30GB、100GB

三、测试方案

• 七镜将执行 SSB 13条标准测试SQL，分别在不同数据库上执行，并比较它们的响应时间和CPU资源消耗。
• 七镜将按照以下步骤进行测试：
  • 步骤一：准备数据。七镜将使用 SSB 工具生成数据，并导入到不同数据库中。
  • 步骤二：设计测试脚本。七镜将根据不同数据库的特点，设计对应测试脚本。
  • 步骤三：执行测试。七镜将执行测试版脚本，并在不同数据库上执行测试。
  • 步骤四：分析结果。七镜将收集和整理结果，并对比不同数据库的性能指标。

四、测试结果

• 在本节中，七镜将展示和分析七镜的测试结果。七镜将使用表格和图表的形式，来展示不同数据库在不同场景下的性能指标。
• 表1：查询性能测试结果

数据库	数据集	响应时间（ms）	CPU 最大占用率
Cloudwave 4.0	ssb30	779	1453%/6400%
Starrocks 3.0	ssb30	1182	3291%/6400%
Cloudwave 4.0	ssb100	1767	4143%/6400%
Starrocks 3.0	ssb100	3054	4462%/6400%

• 图1：查询性能测试结果

• 分析：Cloudwave 4.0 单机版本在 30g的 Star Schema Benchmark 标准测试集下，整体性能优于Starrocks 3.0 单机版本近0.5倍
• 图2：查询性能测试结果

• 分析：Cloudwave 4.0 单机版本在 100g的 Star Schema Benchmark 标准测试集下，整体性能优于Starrocks 3.0 单机版本近0.7倍

五、总结与建议

• 在本文中，七镜对Cloudwave 和 Starrocks数据库进行了性能比对测试，并得到了一些有价值的结论。
• 总体来说，Cloudwave 在 SSB 标准测试集下，性能优于 Starrocks
• 根据七镜的测试结果，七镜给出以下建议：
  • 对于追求极致性能的场景，尤其是olap多维分析场景（鉴于Star Schema Benchmark是学术界和工业界广泛使用的一个星型模型测试集，通过这个测试集合可以方便的对比各种 OLAP 产品的基础性能指标），建议使用Cloudwave数据库，因为Cloudwave数据库在这块的的性能真的优秀；
  • 对于常规场景，建议使用Starrocks数据库，因为Starrocks数据库官方号称全场景极速，也有不同场景的使用案例；
• 当然，七镜的测试并不完善，还有一些局限性和不足之处。例如：
  • 七镜只使用了一种数据集和一种工具进行测试，可能不能覆盖所有可能的情况。
  • 七镜只考虑了一些常见的性能指标，没有涉及到其他方面，如安全性、可用性、可扩展性等。
  • 七镜没有考虑到不同数据库之间的兼容性和迁移成本等问题。
• 因此，在实际选择数据库时，还需要根据自己的具体需求和场景进行综合考虑和评估。

六、附加

1. Cloudwave 测试脚本

#!/bin/bash
# Program:
#       test ssb
# History:
# 2023/03/17    junfenghe.cloud@qq.com  version:0.0.1

rm -rf ./n*txt
for ((i=1; i<20; i++))
do

    cat sql_ssb.sql |./cplus.sh > n${i}.txt

done

2. Starrocks 测试脚本

#!/bin/bash
# Program:
#       test ssb
# History:
# 2023/03/17    junfenghe.cloud@qq.com  version:0.0.1

rm -rf ./n*txt
for ((i=1; i<20; i++))
do

    cat sql_ssb.sql | mysql -uroot -P 9030 -h 127.0.0.1 -v -vv -vvv >n${i}.txt

done

3. 分析脚本

#!/bin/bash
#Program:
#       analysis cloudwave/starrocks logs of base compute
#History:
#2023/02/20     junfenghe.cloud@qq.com  version:0.0.1

path=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/sbin:/usr/local/bin:~/bin
export path



suff="(s)#####"

if [ -z "${1}" ]
then

        echo "Please input database'name"
        exit -1

fi

if [ -z "$2" ]
then

        echo "Please input times of scanner"
        exit -f
fi

if [ -n "${3}" ]
then
        suff=${3}
fi

for current in ${2}
do
        result_time=""

        if [ "${1}" == "starrocks" ]
        then
            for time in $( cat ${current} | grep sec  | awk -F '('  '{print $2}' | awk -F ' ' '{print $1}' )
            do
                result_time="${result_time}${time}${suff}"
            done
        elif [ "${1}" == "cloudwave" ]
        then
            for time in $( cat ${current} | grep Elapsed | awk '{print $2}'| sed 's/:/*60+/g'| sed 's/+00\*60//g ; s/+0\*60//g ; s/^0\*60+//g' )
            do
                result_time="${result_time}${time}${suff}"
            done
        fi

        echo ${result_time%${suff}*}

done


exit 0

4. sql_ssb.sql

use ssb100;
select sum(lo_revenue) as revenue from lineorder,dates where lo_orderdate = d_datekey and d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
select sum(lo_revenue) as revenue from lineorder,dates where lo_orderdate = d_datekey and d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
select sum(lo_revenue) as revenue from lineorder,dates where lo_orderdate = d_datekey and d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
select sum(lo_revenue) as lo_revenue, d_year, p_brand from lineorder ,dates,part,supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand order by d_year, p_brand;
select sum(lo_revenue) as lo_revenue, d_year, p_brand from lineorder,dates,part,supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' group by d_year, p_brand order by d_year, p_brand;
select sum(lo_revenue) as lo_revenue, d_year, p_brand from lineorder,dates,part,supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey and p_brand = 'MFGR#2239' and s_region = 'EUROPE' group by d_year, p_brand order by d_year, p_brand;
select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue from lineorder,dates,customer,supplier where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and c_region = 'ASIA' and s_region = 'ASIA'and d_year >= 1992 and d_year <= 1997 group by c_nation, s_nation, d_year order by d_year asc, lo_revenue desc;
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from lineorder,dates,customer,supplier where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and  c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES' and d_year >= 1992 and d_year <= 1997 group by c_city, s_city, d_year order by d_year asc, lo_revenue desc;
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from lineorder,dates,customer,supplier where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_year >= 1992 and d_year <= 1997 group by c_city, s_city, d_year order by d_year asc, lo_revenue desc;
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from lineorder,dates,customer,supplier where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth  = 'Dec1997' group by c_city, s_city, d_year order by d_year asc, lo_revenue desc;
select d_year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit from lineorder,dates,customer,supplier,part where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_partkey = p_partkey and c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') group by d_year, c_nation order by d_year, c_nation;
select d_year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit from lineorder,dates,customer,supplier,part where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_partkey = p_partkey and c_region = 'AMERICA'and s_region = 'AMERICA' and (d_year = 1997 or d_year = 1998) and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') group by d_year, s_nation, p_category order by d_year, s_nation, p_category;
select d_year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit from lineorder,dates,customer,supplier,part where lo_orderdate = d_datekey and lo_custkey = c_custkey and lo_suppkey = s_suppkey and lo_partkey = p_partkey and c_region = 'AMERICA'and s_nation = 'UNITED STATES' and (d_year = 1997 or d_year = 1998) and p_category = 'MFGR#14' group by d_year, s_city, p_brand order by d_year, s_city, p_brand;

---

七镜还将带来Cloudwave 4.0 集群版 VS Starrocks 3.0 集群版 在 1T SSB数据集上的评测。

---

附加：想要体验 Cloudwave 4.0 的话（或者遇到安装使用问题），可以在公众号【七镜全栈成长】回复：【翰云数据库，4.0】或者【cloudwave4.0】;

想要体验Starrocks3.0的话（或者遇到安装使用问题），可以在公众号回复：【starrocks3.0】