先说结论:
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;