Hologres和Clickhouse性能对比测试的结果_实时数仓 Hologres(Hologres)-阿里云帮助中心

本文将为您介绍Hologres与Clickhouse在ClickBench和SSB单表数据集上进行的性能对比测试的结果。

ClickBench

ClickBench是ClickHouse的官方基准测试工具，通过模拟真实工作负载（如OLAP分析、大规模数据导入等），帮助用户验证数据库性能、比较不同配置或硬件环境的效率，并为开发者提供性能调优的参考依据。详情请参见ClickBench。

测试环境介绍

Hologres采用32 CU、64 CU、128 CU三种实例规格分别参与测试。测试过程请参见ClinkBench-Hologres。
开源ClickHouse、Doris、StarRocks采用的计算资源规格为c6a.metal：192 vCPU。详情请参见C6a实例规格。
ByteHouse采用的计算资源含如下规格：
- 8×L：32核。
- 4×M：16核。
- 2×S：8核。
- XS：4核。

测试结果对比

基于上述测试环境，通过ClickBench工具进行测试，对应测试结果表明，在相同计算资源的前提下，Hologres相较于开源产品ClickHouse、Doris、StarRocks及ByteHouse等，具有显著的性能优势。详细的对比结果如下：

Hologres对比ClickHouse

Hologres对比Doris

Hologres对比StarRocks

Hologres对比ByteHouse

SSB单表数据集

背景信息

Star schema benchmark（以下简称SSB）是学术界和工业界广泛使用的一个星型模型测试集，该测试集用于对比各种OLAP产品的基础性能指标。Clickhouse官方将SSB的星型模型打平转化成宽表，改造成了一个单表测试集，详情请参见Clickhouse官方链接。

本报告记录了Hologres和Clickhouse在SSB单表数据集上进行了性能对比测试的结果，测试结论如下。

测试结果

在单表测试的13个查询中，11个查询Hologres比Clickhouse更快。
在单表测试场景中，13个查询Clickhouse总耗时是Hologres的1.35倍。

测试环境介绍

为了消除网络带宽的影响，本测试使用同一台ECS向Hologres和Clickhouse发送查询请求且使用VPC连接，其中Hologres测试关闭了result cache。具体环境信息如下。

Clickhouse测试环境

配置项	详情配置信息
机器	1台阿里云ECS主机
CPU	Intel Xeon(Ice Lake) Platinum 8369B 64 vCore
内存	256 GiB
内网带宽	32 Gbps
磁盘	ESSD高效云盘200 GB PL1 单盘IOPS上限5万
操作系统	CentOS 8.4 64位
硬件费用（不含公网带宽）	9,032 元/月
公网IPv4带宽	200 Mbps
网络费用	15,725 元/月
Clickhouse版本	21.8.3.44

Hologres测试环境
配置项
具体配置信息
计算资源
CPU：64 Core，内存：256 GB
存储资源
200 GB（逻辑存储）
公网带宽
大于5 Gbps
总费用
11,080 元/月
Hologres版本
0.10.33

测试数据

表名	行数	解释
lineorder	6亿	SSB商品订单表
customer	300万	SSB客户表
part	140万	SSB 零部件表
supplier	20万	SSB 供应商表
dates	2556	日期表
lineorder_flat	6亿	SSB打平后的宽表

相关SQL命令

Clickhouse命令
所使用的DDL与查询SQL与Clickhouse官网测试方式一致，详情请参见Clickhouse官方链接。

Hologres命令

DDL

DROP TABLE IF EXISTS lineorder_flat;

BEGIN;
CREATE TABLE IF NOT EXISTS lineorder_flat (
  lo_orderdate     date NOT NULL ,
  lo_orderkey      int NOT NULL ,
  lo_linenumber    int NOT NULL ,
  lo_custkey       int NOT NULL ,
  lo_partkey       int NOT NULL ,
  lo_suppkey       int NOT NULL ,
  lo_orderpriority text NOT NULL ,
  lo_shippriority  int NOT NULL ,
  lo_quantity      int NOT NULL ,
  lo_extendedprice int NOT NULL ,
  lo_ordtotalprice int NOT NULL ,
  lo_discount      int NOT NULL ,
  lo_revenue       int NOT NULL ,
  lo_supplycost    int NOT NULL ,
  lo_tax           int NOT NULL ,
  lo_commitdate    date NOT NULL ,
  lo_shipmode      text NOT NULL ,
  c_name           text NOT NULL ,
  c_address text NOT NULL ,
  c_city text NOT NULL ,
  c_nation text NOT NULL ,
  c_region text NOT NULL ,
  c_phone text NOT NULL ,
  c_mktsegment text NOT NULL ,
  s_region text NOT NULL ,
  s_nation text NOT NULL ,
  s_city text NOT NULL ,
  s_name text NOT NULL ,
  s_address text NOT NULL ,
  s_phone text NOT NULL ,
  p_name text NOT NULL ,
  p_mfgr text NOT NULL ,
  p_category text NOT NULL ,
  p_brand text NOT NULL ,
  p_color text NOT NULL ,
  p_type text NOT NULL ,
  p_size int NOT NULL ,
  p_container text NOT NULL,
  PRIMARY KEY (lo_orderkey,lo_linenumber)
);
CALL set_table_property('lineorder_flat', 'distribution_key', 'lo_orderkey');
CALL set_table_property('lineorder_flat', 'segment_key', 'lo_orderdate');
CALL set_table_property('lineorder_flat', 'clustering_key', 'lo_orderdate');
CALL set_table_property('lineorder_flat', 'bitmap_columns', 'p_category,s_region,c_region,c_nation,s_nation,c_city,s_city,p_mfgr,p_brand');
CALL set_table_property('lineorder_flat', 'time_to_live_in_seconds', '31536000');
COMMIT;

查询SQL

Q1.1

SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
FROM LINEORDER_FLAT 
WHERE LO_ORDERDATE >= DATE '1993-01-01' 
AND LO_ORDERDATE <= DATE '1993-12-31' 
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 >= DATE '1994-01-01' 
AND LO_ORDERDATE <= DATE '1994-01-31' 
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 EXTRACT(WEEK FROM LO_ORDERDATE ) = 6 
AND LO_ORDERDATE >= DATE '1994-01-01' 
AND LO_ORDERDATE <= DATE '1994-12-31' 
AND LO_DISCOUNT BETWEEN 5 AND 7 
AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1

SELECT SUM(LO_REVENUE),
EXTRACT(YEAR FROM LO_ORDERDATE) 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),
EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
P_BRAND 
FROM LINEORDER_FLAT 
WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' 
AND S_REGION = 'ASIA' 
GROUP BY YEAR, P_BRAND 
ORDER BY YEAR, P_BRAND;

Q2.3

SELECT SUM(LO_REVENUE),
EXTRACT(YEAR FROM LO_ORDERDATE) 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, 
EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
SUM(LO_REVENUE) AS REVENUE 
FROM LINEORDER_FLAT 
WHERE C_REGION = 'ASIA' 
AND S_REGION = 'ASIA' 
AND LO_ORDERDATE  >= DATE '1992-01-01' 
AND LO_ORDERDATE  <= DATE '1997-12-31' 
GROUP BY C_NATION,S_NATION,YEAR 
ORDER BY  YEAR ASC,REVENUE DESC;

Q3.2

SELECT C_CITY, 
S_CITY, 
EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
SUM(LO_REVENUE) AS REVENUE
FROM LINEORDER_FLAT 
WHERE C_NATION = 'UNITED STATES' 
AND S_NATION = 'UNITED STATES' 
AND LO_ORDERDATE >= DATE '1992-01-01' 
AND LO_ORDERDATE <= DATE '1997-12-31' 
GROUP BY C_CITY, S_CITY, YEAR 
ORDER BY YEAR ASC, REVENUE DESC;

Q3.3

SELECT C_CITY,
S_CITY, 
EXTRACT(YEAR FROM LO_ORDERDATE) 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 >= DATE '1992-01-01' 
AND LO_ORDERDATE <= DATE '1997-12-31' 
GROUP BY C_CITY, S_CITY, YEAR 
ORDER BY YEAR ASC, REVENUE DESC;

Q3.4

SELECT C_CITY,
S_CITY, 
EXTRACT(YEAR FROM LO_ORDERDATE) 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 >= DATE '1997-12-01' 
AND LO_ORDERDATE <= DATE '1997-12-31' 
GROUP BY C_CITY, S_CITY, YEAR 
ORDER BY YEAR ASC, REVENUE DESC;

Q4.1

SELECT EXTRACT(YEAR FROM LO_ORDERDATE) 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 EXTRACT(YEAR FROM LO_ORDERDATE) 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 >= DATE '1997-01-01' 
AND LO_ORDERDATE <= DATE '1998-12-31' 
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 EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
S_CITY, 
P_BRAND, 
SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
FROM LINEORDER_FLAT 
WHERE S_NATION = 'UNITED STATES' 
AND LO_ORDERDATE >= DATE '1997-01-01' 
AND LO_ORDERDATE <= DATE '1998-12-31' 
AND P_CATEGORY = 'MFGR#14' 
GROUP BY  YEAR,  S_CITY, P_BRAND 
ORDER BY YEAR ASC,  S_CITY ASC,  P_BRAND ASC;

测试结果

SQL	Hologres用时（ms）	Clickhouse用时（ms）	Clickhouse/Hologres用时
Q1.1	43.66	59.00	1.35
Q1.2	20.68	21.00	1.02
Q1.3	57.98	22.00	0.38
Q2.1	247.63	254.00	1.03
Q2.2	251.90	281.00	1.12
Q2.3	165.73	214.00	1.29
Q3.1	332.84	434.00	1.30
Q3.2	247.79	348.00	1.40
Q3.3	117.46	299.00	2.55
Q3.4	30.05	25.00	0.83
Q4.1	298.48	456.00	1.53
Q4.2	116.47	171.00	1.47
Q4.3	97.68	146.00	1.49
合计	2,028.35	2,730.00	1.35

配置项	具体配置信息
计算资源	CPU：64 Core，内存：256 GB
存储资源	200 GB（逻辑存储）
公网带宽	大于5 Gbps
总费用	11,080 元/月
Hologres版本	0.10.33