一、測(cè)試目的

• 在本文中，七鏡將基于 Star Schema Benchmark 標(biāo)準(zhǔn)測(cè)試集對(duì)兩款MPP數(shù)據(jù)庫(kù)進(jìn)行性能比對(duì)測(cè)試，以評(píng)估它們?cè)诓煌瑘?chǎng)景下的表現(xiàn)。
• 七鏡將使用一些常見(jiàn)的性能指標(biāo)，如響應(yīng)時(shí)間、CPU最大占用率等，來(lái)對(duì)比不同數(shù)據(jù)庫(kù)的優(yōu)劣。
• 七鏡的測(cè)試目標(biāo)是為讀者提供一個(gè)參考，幫助讀者選擇適合自己需求的數(shù)據(jù)庫(kù)。

二、測(cè)試環(huán)境

• 在本文中，七鏡將使用以下幾種數(shù)據(jù)庫(kù)進(jìn)行測(cè)試：
  • Cloudwave 4.0：擁有頂級(jí)性能的國(guó)產(chǎn)云原生數(shù)據(jù)倉(cāng)庫(kù)，目前已知的國(guó)內(nèi)首款類snowflake的存算分離架構(gòu)數(shù)據(jù)庫(kù)（從10年積累的傳統(tǒng)分布式數(shù)據(jù)庫(kù)底層改造，支持徹底的存算分離，2021年9月份發(fā)版）。
  • Starrocks 3.0：新一代極速全場(chǎng)景 MPP (Massively Parallel Processing) 數(shù)據(jù)庫(kù)。
• 七鏡的測(cè)試環(huán)境如下：
  • 硬件配置：CPU 64核，內(nèi)存 256GB，1TB容量阿里云 ESSD pl1 云盤
  • 操作系統(tǒng)：Linux CentOS 7.6
  • 測(cè)試工具：測(cè)試腳本、分析腳本、sql腳本（見(jiàn)附加）
  • 測(cè)試數(shù)據(jù)：使用 SSB 基準(zhǔn)測(cè)試集生成的數(shù)據(jù)，總量約為 30GB、100GB

三、測(cè)試方案

• 七鏡將執(zhí)行 SSB 13條標(biāo)準(zhǔn)測(cè)試SQL，分別在不同數(shù)據(jù)庫(kù)上執(zhí)行，并比較它們的響應(yīng)時(shí)間和CPU資源消耗。
• 七鏡將按照以下步驟進(jìn)行測(cè)試：
  • 步驟一：準(zhǔn)備數(shù)據(jù)。七鏡將使用 SSB 工具生成數(shù)據(jù)，并導(dǎo)入到不同數(shù)據(jù)庫(kù)中。
  • 步驟二：設(shè)計(jì)測(cè)試腳本。七鏡將根據(jù)不同數(shù)據(jù)庫(kù)的特點(diǎn)，設(shè)計(jì)對(duì)應(yīng)測(cè)試腳本。
  • 步驟三：執(zhí)行測(cè)試。七鏡將執(zhí)行測(cè)試版腳本，并在不同數(shù)據(jù)庫(kù)上執(zhí)行測(cè)試。
  • 步驟四：分析結(jié)果。七鏡將收集和整理結(jié)果，并對(duì)比不同數(shù)據(jù)庫(kù)的性能指標(biāo)。

四、測(cè)試結(jié)果

• 在本節(jié)中，七鏡將展示和分析七鏡的測(cè)試結(jié)果。七鏡將使用表格和圖表的形式，來(lái)展示不同數(shù)據(jù)庫(kù)在不同場(chǎng)景下的性能指標(biāo)。
• 表1：查詢性能測(cè)試結(jié)果

• 圖1：查詢性能測(cè)試結(jié)果

• 分析：Cloudwave 4.0 單機(jī)版本在 30g的 Star Schema Benchmark 標(biāo)準(zhǔn)測(cè)試集下，整體性能優(yōu)于Starrocks 3.0 單機(jī)版本近0.5倍
• 圖2：查詢性能測(cè)試結(jié)果

• 分析：Cloudwave 4.0 單機(jī)版本在 100g的 Star Schema Benchmark 標(biāo)準(zhǔn)測(cè)試集下，整體性能優(yōu)于Starrocks 3.0 單機(jī)版本近0.7倍

五、總結(jié)與建議

• 在本文中，七鏡對(duì)Cloudwave 和 Starrocks數(shù)據(jù)庫(kù)進(jìn)行了性能比對(duì)測(cè)試，并得到了一些有價(jià)值的結(jié)論。
• 總體來(lái)說(shuō)，Cloudwave 在 SSB 標(biāo)準(zhǔn)測(cè)試集下，性能優(yōu)于 Starrocks
• 根據(jù)七鏡的測(cè)試結(jié)果，七鏡給出以下建議：
  • 對(duì)于追求極致性能的場(chǎng)景，尤其是olap多維分析場(chǎng)景（鑒于Star Schema Benchmark是學(xué)術(shù)界和工業(yè)界廣泛使用的一個(gè)星型模型測(cè)試集，通過(guò)這個(gè)測(cè)試集合可以方便的對(duì)比各種 OLAP 產(chǎn)品的基礎(chǔ)性能指標(biāo)），建議使用Cloudwave數(shù)據(jù)庫(kù)，因?yàn)镃loudwave數(shù)據(jù)庫(kù)在這塊的的性能真的優(yōu)秀；
  • 對(duì)于常規(guī)場(chǎng)景，建議使用Starrocks數(shù)據(jù)庫(kù)，因?yàn)镾tarrocks數(shù)據(jù)庫(kù)官方號(hào)稱全場(chǎng)景極速，也有不同場(chǎng)景的使用案例；
• 當(dāng)然，七鏡的測(cè)試并不完善，還有一些局限性和不足之處。例如：
  • 七鏡只使用了一種數(shù)據(jù)集和一種工具進(jìn)行測(cè)試，可能不能覆蓋所有可能的情況。
  • 七鏡只考慮了一些常見(jiàn)的性能指標(biāo)，沒(méi)有涉及到其他方面，如安全性、可用性、可擴(kuò)展性等。
  • 七鏡沒(méi)有考慮到不同數(shù)據(jù)庫(kù)之間的兼容性和遷移成本等問(wèn)題。
• 因此，在實(shí)際選擇數(shù)據(jù)庫(kù)時(shí)，還需要根據(jù)自己的具體需求和場(chǎng)景進(jìn)行綜合考慮和評(píng)估。

六、附加

1. Cloudwave 測(cè)試腳本

#!/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 測(cè)試腳本

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

七鏡還將帶來(lái)Cloudwave 4.0 集群版 VS Starrocks 3.0 集群版 在 1T SSB數(shù)據(jù)集上的評(píng)測(cè)。