StarRocks3.3.1集群（存算一体）部署基准测试

文章目录

测试准备

部署规划

服务器FEBEMysqlClient
37yes
38yes
39yes
40yesyes

硬件环境

  • FE

    机器物理机
    CPUIntel® Xeon® CPU E5-2630 v4 @ 2.20GHz
    内存256G
    磁盘HDD
    网卡千兆网卡

  • BE

    机器物理机
    CPUIntel® Xeon® CPU E5-2630 v4 @ 2.20GHz
    内存256G
    磁盘HDD
    网卡千兆网卡

软件环境

  • 操作系统
    • FE（37）使用的是 CentOS release 6.5 (Final)
    • BE（38，39，40）使用CentOS Linux release 7.3.1611 (Core)
  • 内核版本
    • FE（37）使用的是Linux 2.6.32-504.23.4.el6.centos.plus.x86_64
    • BE（38，39，40） Linux 3.10.0-693.25.4.el7.x86_64
  • 软件版本：

测试数据与结果

测试数据

100G数据场景
表行数
call_center30
catalog_page20400
catalog_returns14404374
catalog_sales143997065
customer_address1000000
customer_demographics1920800
customer2000000
date_dim73049
household_demographics7200
income_band20
inventory399330000
item204000
promotion1000
reason55
ship_mode20
store402
store_returns28795080
store_sales287997024
time_dim86400
warehouse15
web_page2040
web_returns7197670
web_sales72001237
web_site24
1000G数据场景
表行数
call_center72
catalog_page50400
catalog_returns158401130
catalog_sales1583977481
customer_address7000000
customer_demographics3841600
customer14000000
date_dim146098
household_demographics14400
income_band40
inventory1182330000
item504000
promotion2500
reason120
ship_mode40
store1404
store_returns316794844
store_sales3167985023
time_dim172800
warehouse35
web_page5040
web_returns79195192
web_sales792001613
web_site78

测试结果

问题

一：Query58查询语句报错
with ss_items as
 (select i_item_id item_id
        ,sum(ss_ext_sales_price) ss_item_rev 
 from store_sales
     ,item
     ,date_dim
 where ss_item_sk = i_item_sk
   and d_date in (select d_date
                  from date_dim
                  where d_week_seq = (select d_week_seq 
                                      from date_dim
                                      where d_date = '2000-01-03'))
   and ss_sold_date_sk   = d_date_sk
 group by i_item_id),
 cs_items as
 (select i_item_id item_id
        ,sum(cs_ext_sales_price) cs_item_rev
  from catalog_sales
      ,item
      ,date_dim
 where cs_item_sk = i_item_sk
  and  d_date in (select d_date
                  from date_dim
                  where d_week_seq = (select d_week_seq 
                                      from date_dim
                                      where d_date = '2000-01-03'))
  and  cs_sold_date_sk = d_date_sk
 group by i_item_id),
 ws_items as
 (select i_item_id item_id
        ,sum(ws_ext_sales_price) ws_item_rev
  from web_sales
      ,item
      ,date_dim
 where ws_item_sk = i_item_sk
  and  d_date in (select d_date
                  from date_dim
                  where d_week_seq =(select d_week_seq 
                                     from date_dim
                                     where d_date = '2000-01-03'))
  and ws_sold_date_sk   = d_date_sk
 group by i_item_id)
  select  ss_items.item_id
       ,ss_item_rev
       ,ss_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ss_dev
       ,cs_item_rev
       ,cs_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 cs_dev
       ,ws_item_rev
       ,ws_item_rev/((ss_item_rev+cs_item_rev+ws_item_rev)/3) * 100 ws_dev
       ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average
 from ss_items,cs_items,ws_items
 where ss_items.item_id=cs_items.item_id
   and ss_items.item_id=ws_items.item_id 
   and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
   and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
   and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
   and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev
   and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev
   and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev
 order by item_id
         ,ss_item_rev
 limit 100;

错误信息：ERROR : Expected LE 1 to be returned by expression

原因构造表date_dim中有两条数据重复了。删除掉即可。

delete 
from
	(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY d_date_sk
	ORDER BY
		d_date_sk) AS row_num
	FROM
		date_dim
	where
		d_date = '2000-01-03' 
		) a
where
		row_num >1
二：Query64查询语句超时
with cs_ui as
 (select cs_item_sk
        ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
  from catalog_sales
      ,catalog_returns
  where cs_item_sk = cr_item_sk
    and cs_order_number = cr_order_number
  group by cs_item_sk
  having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
cross_sales as
 (select i_product_name product_name
     ,i_item_sk item_sk
     ,s_store_name store_name
     ,s_zip store_zip
     ,ad1.ca_street_number b_street_number
     ,ad1.ca_street_name b_street_name
     ,ad1.ca_city b_city
     ,ad1.ca_zip b_zip
     ,ad2.ca_street_number c_street_number
     ,ad2.ca_street_name c_street_name
     ,ad2.ca_city c_city
     ,ad2.ca_zip c_zip
     ,d1.d_year as syear
     ,d2.d_year as fsyear
     ,d3.d_year s2year
     ,count(*) cnt
     ,sum(ss_wholesale_cost) s1
     ,sum(ss_list_price) s2
     ,sum(ss_coupon_amt) s3
  FROM   store_sales
        ,store_returns
        ,cs_ui
        ,date_dim d1
        ,date_dim d2
        ,date_dim d3
        ,store
        ,customer
        ,customer_demographics cd1
        ,customer_demographics cd2
        ,promotion
        ,household_demographics hd1
        ,household_demographics hd2
        ,customer_address ad1
        ,customer_address ad2
        ,income_band ib1
        ,income_band ib2
        ,item
  WHERE  ss_store_sk = s_store_sk AND
         ss_sold_date_sk = d1.d_date_sk AND
         ss_customer_sk = c_customer_sk AND
         ss_cdemo_sk= cd1.cd_demo_sk AND
         ss_hdemo_sk = hd1.hd_demo_sk AND
         ss_addr_sk = ad1.ca_address_sk and
         ss_item_sk = i_item_sk and
         ss_item_sk = sr_item_sk and
         ss_ticket_number = sr_ticket_number and
         ss_item_sk = cs_ui.cs_item_sk and
         c_current_cdemo_sk = cd2.cd_demo_sk AND
         c_current_hdemo_sk = hd2.hd_demo_sk AND
         c_current_addr_sk = ad2.ca_address_sk and
         c_first_sales_date_sk = d2.d_date_sk and
         c_first_shipto_date_sk = d3.d_date_sk and
         ss_promo_sk = p_promo_sk and
         hd1.hd_income_band_sk = ib1.ib_income_band_sk and
         hd2.hd_income_band_sk = ib2.ib_income_band_sk and
         cd1.cd_marital_status <> cd2.cd_marital_status and
         i_color in ('purple','burlywood','indian','spring','floral','medium') and
         i_current_price between 64 and 64 + 10 and
         i_current_price between 64 + 1 and 64 + 15
group by i_product_name
       ,i_item_sk
       ,s_store_name
       ,s_zip
       ,ad1.ca_street_number
       ,ad1.ca_street_name
       ,ad1.ca_city
       ,ad1.ca_zip
       ,ad2.ca_street_number
       ,ad2.ca_street_name
       ,ad2.ca_city
       ,ad2.ca_zip
       ,d1.d_year
       ,d2.d_year
       ,d3.d_year
)
select cs1.product_name
     ,cs1.store_name
     ,cs1.store_zip
     ,cs1.b_street_number
     ,cs1.b_street_name
     ,cs1.b_city
     ,cs1.b_zip
     ,cs1.c_street_number
     ,cs1.c_street_name
     ,cs1.c_city
     ,cs1.c_zip
     ,cs1.syear
     ,cs1.cnt
     ,cs1.s1 as s11
     ,cs1.s2 as s21
     ,cs1.s3 as s31
     ,cs2.s1 as s12
     ,cs2.s2 as s22
     ,cs2.s3 as s32
     ,cs2.syear
     ,cs2.cnt
from cross_sales cs1,cross_sales cs2
where cs1.item_sk=cs2.item_sk and
     cs1.syear = 1999 and
     cs2.syear = 1999 + 1 and
     cs2.cnt <= cs1.cnt and
     cs1.store_name = cs2.store_name and
     cs1.store_zip = cs2.store_zip
order by cs1.product_name
       ,cs1.store_name
       ,cs2.cnt
       ,cs1.s1
       ,cs2.s1;

查询超时

默认超时时长300，5分钟。

TEST SQL

TPC-DS test SQL

tpcds-poc-1.0脚本操作记录

  • 脚本创建100G数据 耗时 3.5h左右
  • 脚本导入100G数据 耗时 40min左右
  • 脚本执行一次99个查询语句查询100G数据 耗时 30min左右
  • 脚本创建1000G数据 耗时 35h左右
  • 脚本导入1000G数据 耗时 6h左右
  • 脚本执行一次99个查询语句查询1000G数据 耗时 90min左右

与Doris测试对比

SQL100G100G100G1000G1000G1000G
-StarRocks-avg(ms)Doris-avg(ms)minStarRocks-avg(ms)Doris-avg(ms)min
Query01410612StarRocks32105424StarRocks
Query022404022StarRocks199735139StarRocks
Query03263235Doris31621397Doris
Query04188217504Doris23348270339Doris
Query055461328Doris101754138Doris
Query06302272Doris3041562Doris
Query07434564Doris116111496Doris
Query082075425Doris3183983Doris
Query098241983StarRocks1292412806Doris
Query10344432StarRocks50271578Doris
Query11101374893Doris13254046074Doris
Query12215195Doris1347244Doris
Query13391528Doris81741794Doris
Query1431278952StarRocks3534655529StarRocks
Query15291337StarRocks69731871Doris
Query1615911842Doris54922541Doris
Query17694786Doris149394500Doris
Query18462586Doris86842001Doris
Query19357308Doris5718779Doris
Query20201182Doris1237273Doris
Query21241211Doris643369Doris
Query2212866235StarRocks87196048Doris
Query232799420605Doris300932273127Doris
Query249176596StarRocks14837253072Doris
Query25651478Doris109432180Doris
Query26247353Doris5415725Doris
Query27359306Doris84251318Doris
Query287821587StarRocks1244812260Doris
Query296331347Doris118282910Doris
Query303091603Doris24171406Doris
Query3114111304Doris3246410024Doris
Query3212999Doris1132123Doris
Query33519495Doris84741885Doris
Query34323378Doris34852423Doris
Query358791108StarRocks176658160Doris
Query36308387StarRocks66461242Doris
Query37195160Doris814188Doris
Query3825395411StarRocks3911145072StarRocks
Query396331175StarRocks27991981Doris
Query40228662Doris2229826Doris
Query416881StarRocks7598StarRocks
Query42267297Doris3299261Doris
Query431323400Doris154302412Doris
Query44239813StarRocks35274656StarRocks
Query45328329Doris128431881Doris
Query46475731StarRocks85633365Doris
Query4710992917StarRocks216336427Doris
Query48344293Doris63651278Doris
Query49444455Doris84841331Doris
Query50443298Doris76581978Doris
Query5135386216StarRocks1954330309StarRocks
Query52265118Doris3491256Doris
Query53347247Doris60991006Doris
Query547893736Doris142913054Doris
Query55265116Doris3312247Doris
Query56509767Doris8437831Doris
Query5710681647StarRocks84913965Doris
Query581840407Doris-640Doris
Query5966572279Doris12488816880Doris
Query60553507Doris95631348Doris
Query61561196Doris76401105Doris
Query62241311StarRocks119571809Doris
Query63332229Doris6115843Doris
Query6419321782Doris-7341Doris
Query658023356StarRocks1347731614StarRocks
Query66399900Doris75771093Doris
Query67698115050StarRocks200395116469Doris
Query68393496StarRocks87776074Doris
Query69378491StarRocks45081978Doris
Query702645933Doris591926435Doris
Query71405486StarRocks67311695Doris
Query72139214127StarRocks8755436832Doris
Query73315281Doris36681861Doris
Query7455804600Doris10004242634Doris
Query7524211941Doris4700916450Doris
Query76262930StarRocks56314877Doris
Query77662355Doris10095727Doris
Query7852416332StarRocks7284562186Doris
Query7913531791StarRocks1741315120Doris
Query80628456Doris150222375Doris
Query81409510StarRocks44323359Doris
Query82229204Doris1314778Doris
Query83189277StarRocks18251491Doris
Query84167178Doris1523909Doris
Query85410517Doris20731844Doris
Query86230334StarRocks33612340Doris
Query8725335611StarRocks3857646143StarRocks
Query8811831620StarRocks1756412995Doris
Query89360279Doris64921264Doris
Query90130399StarRocks8721720Doris
Query91176206StarRocks452269Doris
Query9215076Doris768116Doris
Query9331642Doris72694128Doris
Query94911260Doris35741556Doris
Query952924356Doris61925221Doris
Query96204213StarRocks25821831Doris
Query979462071StarRocks1805116439Doris
Query98369445StarRocks25791084Doris
Query99384417StarRocks131403560Doris
数据量StarRocks快速个数Doris快速个数
100G4059
1000G990

彩蛋

由于执行的时间太长，需要以后台执行的方式来执行脚本

后台执行

nohup sh bin/benchmark.sh -p -d tpcds > test.log 2>&1 &

查询后台直接脚本

ps -ef | grep benchmark.sh
# 大数据
Logo
魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

小参数・大码力・易部署 | Qwen3.6-27B上线魔乐社区，基于昇腾的部署教程来了

继一周前模型开源发布后，千问再度开源Qwen3.6-27B —— 一个拥有270亿参数的稠密多模态模型，也是社区呼声最高的模型规格。Qwen3.6-27B 依然支持多模态思考与非思考模式，在智能体编程方面达到了旗舰级表现，全面超越前代开源旗舰 Qwen3.5-397B-A17B（总参数397B / 激活参数17B的MoE模型）。作为稠密架构，它无需MoE路由即可部署，是开发者在实用、可广泛部署规模

avatar 魔乐社区
cover

量化挑战赛冠军专访：4小时啃下W4A8量化，我靠的是这些经验

avatar 魔乐社区
cover

从0到1：魔乐社区贡献者丁一超的大模型量化实战指南

avatar 魔乐社区