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

1.  
   --Q1.1
2.  
   SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
3.  
   FROM lineorder_flat
4.  
   WHERE LO_ORDERDATE >= 19930101 and LO_ORDERDATE <= 19931231 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
5.  
    
6.  
    
7.  
   --Q1.2
8.  
   SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat
9.  
   WHERE LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19940131 AND LO_DISCOUNT
10.  
    BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;
11.  
     
12.  
     
13.  
    --Q1.3
14.  
    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
15.  
    FROM lineorder_flat
16.  
    WHERE weekofyear(LO_ORDERDATE) = 6 AND LO_ORDERDATE >= 19940101 and LO_ORDERDATE <= 19941231
17.  
    AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
18.  
     
19.  
     
20.  
    --Q2.1
21.  
    SELECT
22.  
    sum(LO_REVENUE),
23.  
    (LO_ORDERDATE DIV 10000) AS year,
24.  
    P_BRAND
25.  
    FROM lineorder_flat
26.  
    WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
27.  
    GROUP BY
28.  
    year,
29.  
    P_BRAND
30.  
    ORDER BY
31.  
    year,
32.  
    P_BRAND;
33.  
     
34.  
     
35.  
    --Q2.2
36.  
    SELECT
37.  
    sum(LO_REVENUE),
38.  
    (LO_ORDERDATE DIV 10000) AS year,
39.  
    P_BRAND
40.  
    FROM lineorder_flat
41.  
    WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
42.  
    GROUP BY
43.  
    year,
44.  
    P_BRAND
45.  
    ORDER BY
46.  
    year,
47.  
    P_BRAND;
48.  
     
49.  
     
50.  
    --Q2.3
51.  
    SELECT
52.  
    sum(LO_REVENUE),
53.  
    (LO_ORDERDATE DIV 10000) AS year,
54.  
    P_BRAND
55.  
    FROM lineorder_flat
56.  
    WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
57.  
    GROUP BY
58.  
    year,
59.  
    P_BRAND
60.  
    ORDER BY
61.  
    year,
62.  
    P_BRAND;
63.  
     
64.  
     
65.  
    --Q3.1
66.  
    SELECT
67.  
    C_NATION,
68.  
    S_NATION,
69.  
    (LO_ORDERDATE DIV 10000) AS year,
70.  
    sum(LO_REVENUE) AS revenue
71.  
    FROM lineorder_flat
72.  
    WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
73.  
    GROUP BY
74.  
    C_NATION,
75.  
    S_NATION,
76.  
    year
77.  
    ORDER BY
78.  
    year ASC,
79.  
    revenue DESC;
80.  
     
81.  
     
82.  
    --Q3.2
83.  
    SELECT
84.  
    C_CITY,
85.  
    S_CITY,
86.  
    (LO_ORDERDATE DIV 10000) AS year,
87.  
    sum(LO_REVENUE) AS revenue
88.  
    FROM lineorder_flat
89.  
    WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
90.  
    GROUP BY
91.  
    C_CITY,
92.  
    S_CITY,
93.  
    year
94.  
    ORDER BY
95.  
    year ASC,
96.  
    revenue DESC;
97.  
     
98.  
     
99.  
    --Q3.3
100.  
     SELECT
101.  
     C_CITY,
102.  
     S_CITY,
103.  
     (LO_ORDERDATE DIV 10000) AS year,
104.  
     sum(LO_REVENUE) AS revenue
105.  
     FROM lineorder_flat
106.  
     WHERE C_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND S_CITY in ( 'UNITED KI1' ,'UNITED KI5') AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231
107.  
     GROUP BY
108.  
     C_CITY,
109.  
     S_CITY,
110.  
     year
111.  
     ORDER BY
112.  
     year ASC,
113.  
     revenue DESC;
114.  
      
115.  
      
116.  
     --Q3.4
117.  
     SELECT
118.  
     C_CITY,
119.  
     S_CITY,
120.  
     (LO_ORDERDATE DIV 10000) AS year,
121.  
     sum(LO_REVENUE) AS revenue
122.  
     FROM lineorder_flat
123.  
     WHERE C_CITY in ('UNITED KI1', 'UNITED KI5') AND S_CITY in ( 'UNITED KI1', 'UNITED KI5') AND LO_ORDERDATE >= 19971201 AND LO_ORDERDATE <= 19971231
124.  
     GROUP BY
125.  
     C_CITY,
126.  
     S_CITY,
127.  
     year
128.  
     ORDER BY
129.  
     year ASC,
130.  
     revenue DESC;
131.  
      
132.  
      
133.  
     --Q4.1
134.  
     SELECT
135.  
     (LO_ORDERDATE DIV 10000) AS year,
136.  
     C_NATION,
137.  
     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
138.  
     FROM lineorder_flat
139.  
     WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR in ( 'MFGR#1' , 'MFGR#2')
140.  
     GROUP BY
141.  
     year,
142.  
     C_NATION
143.  
     ORDER BY
144.  
     year ASC,
145.  
     C_NATION ASC;
146.  
      
147.  
      
148.  
     --Q4.2
149.  
     SELECT
150.  
     (LO_ORDERDATE DIV 10000) AS year,
151.  
     S_NATION,
152.  
     P_CATEGORY,
153.  
     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
154.  
     FROM lineorder_flat
155.  
     WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND P_MFGR in ( 'MFGR#1' , 'MFGR#2')
156.  
     GROUP BY
157.  
     year,
158.  
     S_NATION,
159.  
     P_CATEGORY
160.  
     ORDER BY
161.  
     year ASC,
162.  
     S_NATION ASC,
163.  
     P_CATEGORY ASC;
164.  
      
165.  
      
166.  
     --Q4.3
167.  
     SELECT
168.  
     (LO_ORDERDATE DIV 10000) AS year,
169.  
     S_CITY,
170.  
     P_BRAND,
171.  
     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
172.  
     FROM lineorder_flat
173.  
     WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 and LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14'
174.  
     GROUP BY
175.  
     year,
176.  
     S_CITY,
177.  
     P_BRAND
178.  
     ORDER BY
179.  
     year ASC,
180.  
     S_CITY ASC,
181.  
     P_BRAND ASC;
182.  
      
183.  
      
184.  
      
185.  
      
186.  
      
187.  
      
188.  
     多表查询
189.  
      
190.  
     --Q1.1
191.  
     select sum(lo_revenue) as revenue
192.  
     from lineorder join dates on lo_orderdate = d_datekey
193.  
     where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
194.  
      
195.  
      
196.  
     --Q1.2
197.  
     select sum(lo_revenue) as revenue
198.  
     from lineorder
199.  
     join dates on lo_orderdate = d_datekey
200.  
     where d_yearmonthnum = 199401
201.  
     and lo_discount between 4 and 6
202.  
     and lo_quantity between 26 and 35;
203.  
      
204.  
      
205.  
     --Q1.3
206.  
     select sum(lo_revenue) as revenue
207.  
     from lineorder
208.  
     join dates on lo_orderdate = d_datekey
209.  
     where d_weeknuminyear = 6 and d_year = 1994
210.  
     and lo_discount between 5 and 7
211.  
     and lo_quantity between 26 and 35;
212.  
      
213.  
      
214.  
     --Q2.1
215.  
     select sum(lo_revenue) as lo_revenue, d_year, p_brand
216.  
     from lineorder
217.  
     join dates on lo_orderdate = d_datekey
218.  
     join part on lo_partkey = p_partkey
219.  
     join supplier on lo_suppkey = s_suppkey
220.  
     where p_category = 'MFGR#12' and s_region = 'AMERICA'
221.  
     group by d_year, p_brand
222.  
     order by d_year, p_brand;
223.  
      
224.  
      
225.  
     --Q2.2
226.  
     select sum(lo_revenue) as lo_revenue, d_year, p_brand
227.  
     from lineorder
228.  
     join dates on lo_orderdate = d_datekey
229.  
     join part on lo_partkey = p_partkey
230.  
     join supplier on lo_suppkey = s_suppkey
231.  
     where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'
232.  
     group by d_year, p_brand
233.  
     order by d_year, p_brand;
234.  
      
235.  
      
236.  
     --Q2.3
237.  
     select sum(lo_revenue) as lo_revenue, d_year, p_brand
238.  
     from lineorder
239.  
     join dates on lo_orderdate = d_datekey
240.  
     join part on lo_partkey = p_partkey
241.  
     join supplier on lo_suppkey = s_suppkey
242.  
     where p_brand = 'MFGR#2239' and s_region = 'EUROPE'
243.  
     group by d_year, p_brand
244.  
     order by d_year, p_brand;
245.  
      
246.  
      
247.  
     --Q3.1
248.  
     select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue
249.  
     from lineorder
250.  
     join dates on lo_orderdate = d_datekey
251.  
     join customer on lo_custkey = c_custkey
252.  
     join supplier on lo_suppkey = s_suppkey
253.  
     where c_region = 'ASIA' and s_region = 'ASIA'and d_year >= 1992 and d_year <= 1997
254.  
     group by c_nation, s_nation, d_year
255.  
     order by d_year asc, lo_revenue desc;
256.  
      
257.  
      
258.  
     --Q3.2
259.  
     select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
260.  
     from lineorder
261.  
     join dates on lo_orderdate = d_datekey
262.  
     join customer on lo_custkey = c_custkey
263.  
     join supplier on lo_suppkey = s_suppkey
264.  
     where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES'
265.  
     and d_year >= 1992 and d_year <= 1997
266.  
     group by c_city, s_city, d_year
267.  
     order by d_year asc, lo_revenue desc;
268.  
      
269.  
      
270.  
     --Q3.3
271.  
     select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
272.  
     from lineorder
273.  
     join dates on lo_orderdate = d_datekey
274.  
     join customer on lo_custkey = c_custkey
275.  
     join supplier on lo_suppkey = s_suppkey
276.  
     where (c_city='UNITED KI1' or c_city='UNITED KI5')
277.  
     and (s_city='UNITED KI1' or s_city='UNITED KI5')
278.  
     and d_year >= 1992 and d_year <= 1997
279.  
     group by c_city, s_city, d_year
280.  
     order by d_year asc, lo_revenue desc;
281.  
      
282.  
      
283.  
     --Q3.4
284.  
     select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue
285.  
     from lineorder
286.  
     join dates on lo_orderdate = d_datekey
287.  
     join customer on lo_custkey = c_custkey
288.  
     join supplier on lo_suppkey = s_suppkey
289.  
     where (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997'
290.  
     group by c_city, s_city, d_year
291.  
     order by d_year asc, lo_revenue desc;
292.  
      
293.  
      
294.  
     --Q4.1
295.  
     select d_year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit
296.  
     from lineorder
297.  
     join dates on lo_orderdate = d_datekey
298.  
     join customer on lo_custkey = c_custkey
299.  
     join supplier on lo_suppkey = s_suppkey
300.  
     join part on lo_partkey = p_partkey
301.  
     where c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
302.  
     group by d_year, c_nation
303.  
     order by d_year, c_nation;
304.  
      
305.  
      
306.  
     --Q4.2
307.  
     select d_year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit
308.  
     from lineorder
309.  
     join dates on lo_orderdate = d_datekey
310.  
     join customer on lo_custkey = c_custkey
311.  
     join supplier on lo_suppkey = s_suppkey
312.  
     join part on lo_partkey = p_partkey
313.  
     where c_region = 'AMERICA'and s_region = 'AMERICA'
314.  
     and (d_year = 1997 or d_year = 1998)
315.  
     and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
316.  
     group by d_year, s_nation, p_category
317.  
     order by d_year, s_nation, p_category;
318.  
      
319.  
      
320.  
     --Q4.3
321.  
     select d_year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit
322.  
     from lineorder
323.  
     join dates on lo_orderdate = d_datekey
324.  
     join customer on lo_custkey = c_custkey
325.  
     join supplier on lo_suppkey = s_suppkey
326.  
     join part on lo_partkey = p_partkey
327.  
     where c_region = 'AMERICA'and s_nation = 'UNITED STATES'
328.  
     and (d_year = 1997 or d_year = 1998)
329.  
     and p_category = 'MFGR#14'
330.  
     group by d_year, s_city, p_brand
331.  
     order by d_year, s_city, p_brand;