Apache Doris、Clickhouse查询性能测试

最新推荐文章于 2024-03-17 01:00:51 发布

甘末

最新推荐文章于 2024-03-17 01:00:51 发布

阅读量296

点赞数

文章标签： apache clickhouse

本文链接：https://blog.csdn.net/qq_36722011/article/details/132759980

版权

Apache Doris、Clickhouse查询性能测试

概述:

本测试参照Doris官方性能测试中的Star-Schema-Benchmark测试数据集
操作系统: CentOS Linux release 7.9.2009 (Core)
- MEM: 102G
- CPU: 48 core
Doris版本: Stable 2.0.1 (规划: 1FE 1BE)
Clickhouse版本: LTS 23.8.2.7 (单机)

表创建

doris表

Tables_in_ssb  |   count
---------------+
customer       | -- 3000000
dates          | -- 2556
lineorder2     | -- 10000000
lineorder_flat2| -- 10000000
part           | -- 1400000
supplier       | -- 200000

doris表DDL

CREATE TABLE `***` (
    ...
)
DUPLICATE KEY(`***`)
PARTITION BY RANGE(`***`)
(PARTITION p1 VALUES [("-2147483648"), ("19930101")),
PARTITION p2 VALUES [("19930101"), ("19940101")),
PARTITION p3 VALUES [("19940101"), ("19950101")),
PARTITION p4 VALUES [("19950101"), ("19960101")),
PARTITION p5 VALUES [("19960101"), ("19970101")),
PARTITION p6 VALUES [("19970101"), ("19980101")),
PARTITION p7 VALUES [("19980101"), ("19990101")))
DISTRIBUTED BY HASH(`***`) BUCKETS 12

建表语句详见脚本 doris_table.sql

clickhouse表

Tables_in_tutorial |count
---------------+
customer       | -- 3000000
dates          | -- 2556
lineorder2     | -- 10000000
lineorder_flat2| -- 10000000
part           | -- 1400000
supplier       | -- 200000

clickhouse表DDL

CREATE TABLE `***` (
    ...
)
ENGINE = MergeTree
PRIMARY KEY (LO_ORDERKEY,LO_ORDERDATE)
ORDER BY (LO_ORDERKEY,LO_ORDERDATE)
PARTITION BY
    CASE
        WHEN LO_ORDERDATE >= -2147483648 AND LO_ORDERDATE < 19930101 THEN 1
        WHEN LO_ORDERDATE >= 19930101 AND LO_ORDERDATE < 19940101 THEN 2
        WHEN LO_ORDERDATE >= 19940101 AND LO_ORDERDATE < 19950101 THEN 3
        WHEN LO_ORDERDATE >= 19950101 AND LO_ORDERDATE < 19960101 THEN 4
        WHEN LO_ORDERDATE >= 19960101 AND LO_ORDERDATE < 19970101 THEN 5
        WHEN LO_ORDERDATE >= 19970101 AND LO_ORDERDATE < 19980101 THEN 6
        WHEN LO_ORDERDATE >= 19980101 AND LO_ORDERDATE < 19990101 THEN 7
        ELSE 0 
    END
;

建表语句详见脚本 clickhouse_table.sql

简单查询

SELECT (LO_ORDERDATE DIV 10000) AS YEAR, S_CITY, P_BRAND, SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat2 WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 AND LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14' GROUP BY YEAR, S_CITY, P_BRAND ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;

数据库	执行时长(s)	内存消耗(GiB)	CPU消耗
Doris	0.10	0.12	0.12%
ClickHouse	0.22	0.01	0.23%

SELECT c_city, s_city, d_year, SUM(lo_revenue) AS REVENUE FROM customer, lineorder2, supplier, dates WHERE lo_custkey = c_custkey AND lo_suppkey = s_suppkey AND lo_orderdate = d_datekey AND ( c_city = 'UNITED KI1' OR c_city = 'UNITED KI5' ) AND ( s_city = 'UNITED KI1' OR s_city = 'UNITED KI5' ) AND d_year >= 1990 AND d_year <= 1999 GROUP BY c_city, s_city, d_year ORDER BY d_year ASC, REVENUE DESC;

数据库	执行时长(s)	内存消耗(GiB)	CPU消耗
Doris	0.3	0.42	0.28%
ClickHouse	0.81	0.23	0.28

并发查询

-- 100 并发执行
	SELECT (LO_ORDERDATE DIV 10000) AS YEAR, S_CITY, P_BRAND, SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat2 WHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 AND LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14' GROUP BY YEAR, S_CITY, P_BRAND ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;
	SELECT c_city, s_city, d_year, SUM(lo_revenue) AS REVENUE FROM customer, lineorder2, supplier, dates WHERE lo_custkey = c_custkey AND lo_suppkey = s_suppkey AND lo_orderdate = d_datekey AND ( c_city = 'UNITED KI1' OR c_city = 'UNITED KI5' ) AND ( s_city = 'UNITED KI1' OR s_city = 'UNITED KI5' ) AND d_year >= 1990 AND d_year <= 1999 GROUP BY c_city, s_city, d_year ORDER BY d_year ASC, REVENUE DESC;