AI 助理
备案控制台
文档
输入文档关键字查找
首页 实时数仓 Hologres 操作指南 性能测试 Hologres vs Clickhouse性能对比参考测试

Hologres vs Clickhouse性能对比参考测试

更新时间：
产品详情
我的收藏

本文将为您介绍Hologres和Clickhouse在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

上一篇：数据写入、更新、点查场景压测最佳实践下一篇：使用PTS测试Hologres性能实践
文档推荐