星环 Transwarp Inceptor Spark 任务运行星环starloop评测

转载

AIGC创想家 2023-11-20 14:05:49

文章标签 数据库 ci sql 文章分类 Spark 大数据

一、测试目的

在本文中，七镜将基于 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：查询性能测试结果

星环 Transwarp Inceptor Spark 任务运行星环starloop评测_数据库

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

星环 Transwarp Inceptor Spark 任务运行星环starloop评测_ci_02

分析：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数据库官方号称全场景极速，也有不同场景的使用案例；

当然，七镜的测试并不完善，还有一些局限性和不足之处。例如：

七镜只使用了一种数据集和一种工具进行测试，可能不能覆盖所有可能的情况。
七镜只考虑了一些常见的性能指标，没有涉及到其他方面，如安全性、可用性、可扩展性等。
七镜没有考虑到不同数据库之间的兼容性和迁移成本等问题。

因此，在实际选择数据库时，还需要根据自己的具体需求和场景进行综合考虑和评估。

六、附加

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

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

分析脚本

#!/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

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数据集上的评测。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。