Doris、StarRocks 压测对比
xiaokebiubiubiu 2024-06-20 09:05:02 阅读 55
先说结论：
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;
声明
本文内容仅代表作者观点，或转载于其他网站，本站不以此文作为商业用途
如有涉及侵权，请联系本站进行删除
转载本站原创文章，请注明来源及作者。