Hologres和Clickhouse效能對比測試的結果 - 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位
硬體費用（不含公網頻寬）	1370.04 美元/月
公網IPv4頻寬	200 Mbps
網路費用	2323.85 美元/月
Clickhouse版本	21.8.3.44

Hologres測試環境
配置項
具體配置資訊
計算資源
CPU：64 Core，記憶體：256 GB
儲存資源
200 GB（邏輯儲存）
公網頻寬
大於5 Gbps
總費用
1653.73 美元/月
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
總費用	1653.73 美元/月
Hologres版本	0.10.33