Doris、StarRocks 压测对比

先说结论：

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.1
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE LO_ORDERDATE >= 19930101 and LO_ORDERDATE <= 19931231 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;


--Q1.2
SELECT 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.3
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE 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.1
SELECT
    sum(LO_REVENUE),
    (LO_ORDERDATE DIV 10000) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;


--Q2.2
SELECT
    sum(LO_REVENUE),
    (LO_ORDERDATE DIV 10000) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;


--Q2.3
SELECT
    sum(LO_REVENUE),
    (LO_ORDERDATE DIV 10000) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;


--Q3.1
SELECT
    C_NATION,
    S_NATION,
    (LO_ORDERDATE DIV 10000) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE   <= 19971231
GROUP BY
    C_NATION,
    S_NATION,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q3.2
SELECT
    C_CITY,
    S_CITY,
    (LO_ORDERDATE DIV 10000) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE <= 19971231
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q3.3
SELECT
    C_CITY,
    S_CITY,
    (LO_ORDERDATE DIV 10000) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND S_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND LO_ORDERDATE  >= 19920101 AND LO_ORDERDATE <= 19971231
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q3.4
SELECT
    C_CITY,
    S_CITY,
    (LO_ORDERDATE DIV 10000) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_CITY in ('UNITED KI1', 'UNITED KI5') AND S_CITY in ( 'UNITED KI1',  'UNITED KI5') AND  LO_ORDERDATE  >= 19971201 AND LO_ORDERDATE <= 19971231
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;


--Q4.1
SELECT
   (LO_ORDERDATE DIV 10000) AS year,
    C_NATION,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR in ( 'MFGR#1' , 'MFGR#2')
GROUP BY
    year,
    C_NATION
ORDER BY
    year ASC,
    C_NATION ASC;


--Q4.2
SELECT
   (LO_ORDERDATE DIV 10000) AS year,
    S_NATION,
    P_CATEGORY,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE 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_CATEGORY
ORDER BY
    year ASC,
    S_NATION ASC,
    P_CATEGORY ASC;


--Q4.3
SELECT
    (LO_ORDERDATE DIV 10000) AS year,
    S_CITY,
    P_BRAND,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14'
GROUP BY
    year,
    S_CITY,
    P_BRAND
ORDER BY
    year ASC,
    S_CITY ASC,
    P_BRAND ASC;






多表查询

--Q1.1
select sum(lo_revenue) as revenue
from lineorder join dates on lo_orderdate = d_datekey
where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;


--Q1.2
select sum(lo_revenue) as revenue
from lineorder
join dates on lo_orderdate = d_datekey
where d_yearmonthnum = 199401
and lo_discount between 4 and 6
and lo_quantity between 26 and 35;


--Q1.3
select sum(lo_revenue) as revenue
from lineorder
join dates on lo_orderdate = d_datekey
where d_weeknuminyear = 6 and d_year = 1994
and lo_discount between 5 and 7
and lo_quantity between 26 and 35;


--Q2.1
select sum(lo_revenue) as lo_revenue, d_year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_category = 'MFGR#12' and s_region = 'AMERICA'
group by d_year, p_brand
order by d_year, p_brand;


--Q2.2
select sum(lo_revenue) as lo_revenue, d_year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'
group by d_year, p_brand
order by d_year, p_brand;


--Q2.3
select sum(lo_revenue) as lo_revenue, d_year, p_brand
from lineorder
join dates on lo_orderdate = d_datekey
join part on lo_partkey = p_partkey
join supplier on lo_suppkey = s_suppkey
where p_brand = 'MFGR#2239' and s_region = 'EUROPE'
group by d_year, p_brand
order by d_year, p_brand;


--Q3.1
select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where 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;


--Q3.2
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where 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;


--Q3.3
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where (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;


--Q3.4
select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
where (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;


--Q4.1
select d_year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where 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;


--Q4.2
select d_year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where 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;


--Q4.3
select d_year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit
from lineorder
join dates on lo_orderdate = d_datekey
join customer on lo_custkey = c_custkey
join supplier on lo_suppkey = s_suppkey
join part on lo_partkey = p_partkey
where 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;