本文将为您介绍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;
- Q1.1
- DDL
测试结果
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 |