当前位置:首页 » 《随便一记》 » 正文

Doris、StarRocks 压测对比

8 人参与  2024年05月15日 13:56  分类 : 《随便一记》  评论

点击全文阅读


先说结论:

0、本次测试,未调优二者的参数,开箱起服务,直接测试的,部署架构一致。

1、在单表查询下,StarRocks 在部分场景下优于Doris,但是二次查询,二者不分伯仲。

2、在多表查询下,仅在一个场景下Doris速度逊于StarRocks ,大部分场景是Doris优于StarRocks的。

3、在cpu和内存的使用上,doris会比starrocks多吃1.5倍的资源。

压测试验:

1、基础环境

组件

cpu

内存

Cpu架构

磁盘存储

Doris

8C

16G

x86_64

492G

8C

16G

x86_64

492G

8C

16G

x86_64

492G

8C

16G

x86_64

492G

8C

16G

x86_64

492G

StarRocks

8C

16G

x86_64

492G

8C

16G

x86_64

492G

8C

16G

x86_64

492G

8C

16G

x86_64

492G

8C

16G

x86_64

492G

2、版本

Doris-2.0.5 (2024 年 2 月 27 日 发 布)

StarRocks-3.2.3(2024 年 2 月 8 日 发 布)

Release 2.0.5 - Apache Doris

StarRocks version 3.2 | StarRocks

3、压测工具

(1)下载doris的ssb-tools

GitHub - apache/doris: Apache Doris is an easy-to-use, high performance and unified analytics database.

使用doris-master\tools\ssb-tools下的压测脚本

(2)编译脚本

./build-ssb-dbgen.sh

(3)生成测试数据

./gen-ssb-data.sh -s 40

(4)修改配置信息

./conf/doris-cluster.conf

(5)创建表并导入数据

创建表:./create-ssb-tables.sh

导入数据:./load-ssb-data.sh

表名

行数

解释

lineorder

2400万

SSB商品订单表

customer

120万

SSB客户表

part

120万

SSB 零部件表

supplier

8万

SSB 供应商表

dates

2556

日期表

lineorder_flat

2400万

SSB打平后的宽表

4、查询测试

通过单表、多表的sql查询

(1)doris-2.0.5

doris单表:

序号

首次

多次

Q1.1

0.59

0.03

Q1.2

0.19

0.02

Q1.3

0.08

0.03

Q2.1

8.15

0.08

Q2.2

0.08

0.08

Q2.3

0.06

0.06

Q3.1

2.45

0.14

Q3.2

2.55

0.08

Q3.3

0.09

0.06

Q3.4

0.02

0.02

Q4.1

0.89

0.13

Q4.2

0.08

0.05

Q4.3

0.05

0.04

doris多表

序号

首次

多次

Q1.1

0.96

0.04

Q1.2

0.85

0.03

Q1.3

0.03

0.02

Q2.1

3.8

0.16

Q2.2

0.14

0.13

Q2.3

0.13

0.12

Q3.1

0.64

0.34

Q3.2

0.14

0.13

Q3.3

0.14

0.14

Q3.4

0.05

0.05

Q4.1

0.43

0.33

Q4.2

0.16

0.15

Q4.3

0.14

0.12

(2)starrocks-3.2.3

starrocks单表

序号

首次

多次

Q1.1

0.13

0.03

Q1.2

0.05

0.02

Q1.3

0.06

0.02

Q2.1

1.2

0.13

Q2.2

0.11

0.11

Q2.3

0.06

0.06

Q3.1

0.53

0.13

Q3.2

0.32

0.09

Q3.3

0.1

0.09

Q3.4

0.03

0.02

Q4.1

0.4

0.15

Q4.2

0.1

0.06

Q4.3

0.07

0.04

starrocks多表

首次

多次

Q1.1

0.34

0.04

Q1.2

0.32

0.03

Q1.3

0.27

0.03

Q2.1

2.46

0.26

Q2.2

0.24

0.21

Q2.3

0.43

0.19

Q3.1

0.49

0.32

Q3.2

0.26

0.22

Q3.3

0.17

0.16

Q3.4

0.07

0.05

Q4.1

0.52

0.44

Q4.2

0.2

0.18

Q4.3

0.15

0.13

5、对比:

(1)单表对比

序号

doris首次

doris多次

star首次

star多次

Q1.1

0.59

0.03

0.1

0.04

Q1.2

0.19

0.02

0.04

0.04

Q1.3

0.08

0.03

0.08

0.03

Q2.1

8.15

0.08

0.42

0.12

Q2.2

0.08

0.08

0.13

0.12

Q2.3

0.06

0.06

0.07

0.06

Q3.1

2.45

0.14

0.31

0.16

Q3.2

2.55

0.08

0.18

0.09

Q3.3

0.09

0.06

0.11

0.09

Q3.4

0.02

0.02

0.03

0.03

Q4.1

0.89

0.13

0.33

0.2

Q4.2

0.08

0.05

0.1

0.08

Q4.3

0.05

0.04

0.07

0.04

(2)多表对比

序号

doris首次

doris多次

star首次

star多次

Q1.1

0.96

0.04

0.1

0.05

Q1.2

0.85

0.03

0.08

0.04

Q1.3

0.03

0.02

0.04

0.04

Q2.1

3.8

0.16

0.41

0.26

Q2.2

0.14

0.13

0.22

0.2

Q2.3

0.13

0.12

0.21

0.19

Q3.1

0.64

0.34

0.38

0.32

Q3.2

0.14

0.13

0.26

0.22

Q3.3

0.14

0.14

0.38

0.16

Q3.4

0.05

0.05

0.1

0.06

Q4.1

0.43

0.33

0.49

0.42

Q4.2

0.16

0.15

0.22

0.18

Q4.3

0.14

0.12

0.15

0.13

6、cpu、内存使用情况

(1)starrocks

(2)doris

7、测试使用到的sql

--Q1.1SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE LO_ORDERDATE >= 19930101 and LO_ORDERDATE <= 19931231 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;--Q1.2SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19940131 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;--Q1.3SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE weekofyear(LO_ORDERDATE) = 6 AND LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19941231  AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;--Q2.1SELECT    sum(LO_REVENUE),    (LO_ORDERDATE DIV 10000) AS year,    P_BRANDFROM lineorder_flatWHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'GROUP BY    year,    P_BRANDORDER BY    year,    P_BRAND;--Q2.2SELECT    sum(LO_REVENUE),    (LO_ORDERDATE DIV 10000) AS year,    P_BRANDFROM lineorder_flatWHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'GROUP BY    year,    P_BRANDORDER BY    year,    P_BRAND;--Q2.3SELECT    sum(LO_REVENUE),    (LO_ORDERDATE DIV 10000) AS year,    P_BRANDFROM lineorder_flatWHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'GROUP BY    year,    P_BRANDORDER BY    year,    P_BRAND;--Q3.1SELECT    C_NATION,    S_NATION,    (LO_ORDERDATE DIV 10000) AS year,    sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE   <= 19971231GROUP BY    C_NATION,    S_NATION,    yearORDER BY    year ASC,    revenue DESC;--Q3.2SELECT    C_CITY,    S_CITY,    (LO_ORDERDATE DIV 10000) AS year,    sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE <= 19971231GROUP BY    C_CITY,    S_CITY,    yearORDER BY    year ASC,    revenue DESC;--Q3.3SELECT    C_CITY,    S_CITY,    (LO_ORDERDATE DIV 10000) AS year,    sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE C_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND S_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE <= 19971231GROUP BY    C_CITY,    S_CITY,    yearORDER BY    year ASC,    revenue DESC;--Q3.4SELECT    C_CITY,    S_CITY,    (LO_ORDERDATE DIV 10000) AS year,    sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE C_CITY in ('UNITED KI1', 'UNITED KI5') AND S_CITY in ( 'UNITED KI1',  'UNITED KI5') AND  LO_ORDERDATE  >= 19971201 AND LO_ORDERDATE <= 19971231GROUP BY    C_CITY,    S_CITY,    yearORDER BY    year ASC,    revenue DESC;--Q4.1SELECT   (LO_ORDERDATE DIV 10000) AS year,    C_NATION,    sum(LO_REVENUE - LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR in ( 'MFGR#1' , 'MFGR#2')GROUP BY    year,    C_NATIONORDER BY    year ASC,    C_NATION ASC;--Q4.2SELECT   (LO_ORDERDATE DIV 10000) AS year,    S_NATION,    P_CATEGORY,    sum(LO_REVENUE - LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND  P_MFGR in ( 'MFGR#1' , 'MFGR#2')GROUP BY    year,    S_NATION,    P_CATEGORYORDER BY    year ASC,    S_NATION ASC,    P_CATEGORY ASC;--Q4.3SELECT    (LO_ORDERDATE DIV 10000) AS year,    S_CITY,    P_BRAND,    sum(LO_REVENUE - LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14'GROUP BY    year,    S_CITY,    P_BRANDORDER BY    year ASC,    S_CITY ASC,    P_BRAND ASC;多表查询--Q1.1select sum(lo_revenue) as revenuefrom lineorder join dates on lo_orderdate = d_datekeywhere d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;--Q1.2select sum(lo_revenue) as revenuefrom lineorderjoin dates on lo_orderdate = d_datekeywhere d_yearmonthnum = 199401and lo_discount between 4 and 6and lo_quantity between 26 and 35;--Q1.3select sum(lo_revenue) as revenuefrom lineorderjoin dates on lo_orderdate = d_datekeywhere d_weeknuminyear = 6 and d_year = 1994and lo_discount between 5 and 7and lo_quantity between 26 and 35;--Q2.1select sum(lo_revenue) as lo_revenue, d_year, p_brandfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin part on lo_partkey = p_partkeyjoin supplier on lo_suppkey = s_suppkeywhere p_category = 'MFGR#12' and s_region = 'AMERICA'group by d_year, p_brandorder by d_year, p_brand;--Q2.2select sum(lo_revenue) as lo_revenue, d_year, p_brandfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin part on lo_partkey = p_partkeyjoin supplier on lo_suppkey = s_suppkeywhere p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'group by d_year, p_brandorder by d_year, p_brand;--Q2.3select sum(lo_revenue) as lo_revenue, d_year, p_brandfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin part on lo_partkey = p_partkeyjoin supplier on lo_suppkey = s_suppkeywhere p_brand = 'MFGR#2239' and s_region = 'EUROPE'group by d_year, p_brandorder by d_year, p_brand;--Q3.1select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere c_region = 'ASIA' and s_region = 'ASIA'and d_year >= 1992 and d_year <= 1997group by c_nation, s_nation, d_yearorder by d_year asc, lo_revenue desc;--Q3.2select c_city, s_city, d_year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES'and d_year >= 1992 and d_year <= 1997group by c_city, s_city, d_yearorder by d_year asc, lo_revenue desc;--Q3.3select c_city, s_city, d_year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere (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 <= 1997group by c_city, s_city, d_yearorder by d_year asc, lo_revenue desc;--Q3.4select c_city, s_city, d_year, sum(lo_revenue) as lo_revenuefrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeywhere (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_yearorder by d_year asc, lo_revenue desc;--Q4.1select d_year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profitfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeyjoin part on lo_partkey = p_partkeywhere c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')group by d_year, c_nationorder by d_year, c_nation;--Q4.2select d_year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profitfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeyjoin part on lo_partkey = p_partkeywhere 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_categoryorder by d_year, s_nation, p_category;--Q4.3select d_year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profitfrom lineorderjoin dates on lo_orderdate = d_datekeyjoin customer on lo_custkey = c_custkeyjoin supplier on lo_suppkey = s_suppkeyjoin part on lo_partkey = p_partkeywhere 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_brandorder by d_year, s_city, p_brand;


点击全文阅读


本文链接:http://zhangshiyu.com/post/108965.html

<< 上一篇 下一篇 >>

  • 评论(0)
  • 赞助本站

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

关于我们 | 我要投稿 | 免责申明

Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1