【测试】OpenLookeng-Clickhouse连接器TPC性能测试

• 测试背景
• 测试目的
• 测试环境
  • 系统信息
  • 硬件信息
  • 部署情况
• 数据集
  • 规模
  • 测试语句
• 测试方法
• 测试结果
  • ssb-1000
• 结果分析
  • ssb-1000
• 执行情况分析
• 结论

原始版本发布在InfoQ上: OpenLookeng 连接器 -Clickhouse connector 性能测试报告

测试背景

OpenLooKeng是一款开源的高性能数据虚拟化引擎，提供统一SQL接口，可以实现对多个数据库的跨源异构和跨域跨DC查询。

ClickHouse是Yandex开源的一个用于实时数据分析的基于列存储的数据库，其工作速度比传统方法快100-1000倍，性能超过了目前市场上的列式存储数据库¹。

ClickHouse connector²是为OpenLookeng开发的用于访问Clickhouse数据源的连接器，截止测试时最新版本更新至2020-12-24日的b2162c5，已支持对常见数据类型和函数的映射，详细支持列表可见https://gitee.com/heatao/hetu-core/blob/48650aa794c90a871df15661bf77f25225fd09c5/hetu-docs/zh/connector/clickhouse.md

测试目的

使用SSB测试基准提供的数据和SQL查询语句，对OpenLookeng的ClickHouse connector的性能进行评估。

测试环境

系统信息

硬件信息

部署情况

数据集

规模

按照Clickhouse官方文档中提到的Star Schema测试集生成数据³。

通过ssb-dbgen工具设置参数10和1000分别生成了两种规模的测试集，其规模如下，分别称为ssb-10和ssb-1000

ssb-10的数据占用空间为5.18G，包含59986052条数据

ssb-1000占用的空间为395.76G，数据量为4398761522

测试语句

SSB测试语句总共包含13条测试语句，如下

Q1.1: SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYear(LO_ORDERDATE) = 1993 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 toYYYYMM(LO_ORDERDATE) = 199401 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 toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994 AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1: SELECT sum(LO_REVENUE) toYear(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) toYear(LO_ORDERDATE) AS year P_BRAND FROM lineorder_flat WHERE P_BRAND >= ‘MFGR#2221’ AND P_BRAND <= ‘MFGR#2228’ AND S_REGION = ‘ASIA’ GROUP BY year P_BRAND ORDER BY year P_BRAND;

Q2.3: SELECT sum(LO_REVENUE) toYear(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 toYear(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_REGION = ‘ASIA’ AND S_REGION = ‘ASIA’ AND year >= 1992 AND year <= 1997 GROUP BY C_NATION S_NATION year ORDER BY year ASC revenue DESC;

Q3.2: SELECT C_CITY S_CITY toYear(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_NATION = ‘UNITED STATES’ AND S_NATION = ‘UNITED STATES’ AND year >= 1992 AND year <= 1997 GROUP BY C_CITY S_CITY year ORDER BY year ASC revenue DESC;

Q3.3: SELECT C_CITY S_CITY toYear(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = ‘UNITED KI1’ OR C_CITY = ‘UNITED KI5’) AND (S_CITY = ‘UNITED KI1’ OR S_CITY = ‘UNITED KI5’) AND year >= 1992 AND year <= 1997 GROUP BY C_CITY S_CITY year ORDER BY year ASC revenue DESC;

Q3.4: SELECT C_CITY S_CITY toYear(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = ‘UNITED KI1’ OR C_CITY = ‘UNITED KI5’) AND (S_CITY = ‘UNITED KI1’ OR S_CITY = ‘UNITED KI5’) AND toYYYYMM(LO_ORDERDATE) = 199712 GROUP BY C_CITY S_CITY year ORDER BY year ASC revenue DESC;

Q4.1: SELECT toYear(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 = ‘MFGR#1’ OR P_MFGR = ‘MFGR#2’) GROUP BY year C_NATION ORDER BY year ASC C_NATION ASC;

Q4.2: SELECT toYear(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 (year = 1997 OR year = 1998) AND (P_MFGR = ‘MFGR#1’ OR P_MFGR = ‘MFGR#2’) GROUP BY year S_NATION P_CATEGORY ORDER BY year ASC S_NATION ASC P_CATEGORY ASC;

Q4.3: SELECT toYear(LO_ORDERDATE) AS year S_CITY P_BRAND sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE S_NATION = ‘UNITED STATES’ AND (year = 1997 OR year = 1998) AND P_CATEGORY = ‘MFGR#14’ GROUP BY year S_CITY P_BRAND ORDER BY year ASC S_CITY ASC P_BRAND ASC;

根据OpenLookeng的语法，在功能不变的情况下对Clickhouse语法的sql语句进行一定的修改，主要为一下几点

• 将toYear()函数统一修改为year()
• 将toISOWeek()函数修改为week()
• 将toYYYYMM(A)替换为 year(A)*100+month(A)
• 将语法为select expr(A) as B group by B 等修改为select expr(A) as B group by expr(A)，即去掉对AS的使用

修改后的语法为

Q1.1 : SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE year(LO_ORDERDATE) = 1993 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 year(LO_ORDERDATE)*100+month(LO_ORDERDATE) = 199401 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 week(LO_ORDERDATE) = 6 AND year(LO_ORDERDATE) = 1994 AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1 : SELECT sum(LO_REVENUE) year(LO_ORDERDATE) AS year P_BRAND FROM lineorder_flat WHERE P_CATEGORY = ‘MFGR#12’ AND S_REGION = ‘AMERICA’ GROUP BY year(LO_ORDERDATE) P_BRAND ORDER BY year(LO_ORDERDATE) P_BRAND;

Q2.2 : SELECT sum(LO_REVENUE) year(LO_ORDERDATE) AS year P_BRAND FROM lineorder_flat WHERE P_BRAND >= ‘MFGR#2221’ AND P_BRAND <= ‘MFGR#2228’ AND S_REGION = ‘ASIA’ GROUP BY year(LO_ORDERDATE) P_BRAND ORDER BY year(LO_ORDERDATE) P_BRAND;

Q2.3 : SELECT sum(LO_REVENUE) year(LO_ORDERDATE) AS year P_BRAND FROM lineorder_flat WHERE P_BRAND = ‘MFGR#2239’ AND S_REGION = ‘EUROPE’ GROUP BY year(LO_ORDERDATE) P_BRAND ORDER BY year(LO_ORDERDATE) P_BRAND;

Q3.1 : SELECT C_NATION S_NATION year(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_REGION = ‘ASIA’ AND S_REGION = ‘ASIA’ AND year(LO_ORDERDATE) >= 1992 AND year(LO_ORDERDATE) <= 1997 GROUP BY C_NATION S_NATION year(LO_ORDERDATE) ORDER BY year(LO_ORDERDATE) ASC revenue DESC;

Q3.2 : SELECT C_CITY S_CITY year(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_NATION = ‘UNITED STATES’ AND S_NATION = ‘UNITED STATES’ AND year(LO_ORDERDATE) >= 1992 AND year(LO_ORDERDATE) <= 1997 GROUP BY C_CITY S_CITY year(LO_ORDERDATE) ORDER BY year(LO_ORDERDATE) ASC revenue DESC;

Q3.3 : SELECT C_CITY S_CITY year(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = ‘UNITED KI1’ OR C_CITY = ‘UNITED KI5’) AND (S_CITY = ‘UNITED KI1’ OR S_CITY = ‘UNITED KI5’) AND year(LO_ORDERDATE) >= 1992 AND year(LO_ORDERDATE) <= 1997 GROUP BY C_CITY S_CITY year(LO_ORDERDATE) ORDER BY year(LO_ORDERDATE) ASC revenue DESC;

Q3.4 : SELECT C_CITY S_CITY year(LO_ORDERDATE) AS year sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = ‘UNITED KI1’ OR C_CITY = ‘UNITED KI5’) AND (S_CITY = ‘UNITED KI1’ OR S_CITY = ‘UNITED KI5’) AND year(LO_ORDERDATE)*100+month(LO_ORDERDATE) = 199712 GROUP BY C_CITY S_CITY year(LO_ORDERDATE) ORDER BY year(LO_ORDERDATE) ASC revenue DESC;

Q4.1 : SELECT year(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 = ‘MFGR#1’ OR P_MFGR = ‘MFGR#2’) GROUP BY year(LO_ORDERDATE) C_NATION ORDER BY year(LO_ORDERDATE) ASC C_NATION ASC;

Q4.2 : SELECT year(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 (year(LO_ORDERDATE) = 1997 OR year(LO_ORDERDATE) = 1998) AND (P_MFGR = ‘MFGR#1’ OR P_MFGR = ‘MFGR#2’) GROUP BY year(LO_ORDERDATE) S_NATION P_CATEGORY ORDER BY year(LO_ORDERDATE) ASC S_NATION ASC P_CATEGORY ASC;

Q4.3 : SELECT year(LO_ORDERDATE) AS year S_CITY P_BRAND sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE S_NATION = ‘UNITED STATES’ AND (year(LO_ORDERDATE) = 1997 OR year(LO_ORDERDATE) = 1998) AND P_CATEGORY = ‘MFGR#14’ GROUP BY year(LO_ORDERDATE) S_CITY P_BRAND ORDER BY year(LO_ORDERDATE) ASC S_CITY ASC P_BRAND ASC;

测试方法

将ssb-10数据到192.168.40.152节点上，将ssb-1000数据导入到192.168.40.223节点上

使用python脚本分别在Clickhouse和OpenLookeng上执行脚本，每条SQL语句执行间隔为10秒,多次执行取平均值

其中Clickhouse的驱动使用的是开源库clickhouse-driver⁴,OpenLookeng的驱动来源于对web端API的调用，代码已发布于github⁵

测试结果

ssb-1000

在OpenLookeng上的执行结果

在Clickhouse上的执行结果

结果分析

ssb-1000

执行情况分析

根据查询clickhouse中system.query_log表，对后台实际执行的语句进行了分析，发现每在OpenLookeng执行一条sql语句，会在clickhouse中产生2-3条相关查询（忽略获取表时区的查询）

例如Q3.3会对应三次查询，第一次和第三次功能几乎一致，分别如下

--- 第一次
(SELECT *
FROM (
	SELECT LO_ORDERDATE AS lo_orderdate, LO_REVENUE AS lo_revenue, C_CITY AS c_city, S_CITY AS s_city
	FROM ssb.lineorder_flat
)
WHERE (c_city = CAST('UNITED KI1' AS varchar)
		OR c_city = CAST('UNITED KI5' AS varchar))
	AND (s_city = CAST('UNITED KI1' AS varchar)
		OR s_city = CAST('UNITED KI5' AS varchar))
	AND (toYear(lo_orderdate) >= 1992
		AND toYear(lo_orderdate) <= 1997))
FORMAT TabSeparatedWithNamesAndTypes;


--- 第二次
(SELECT year, c_city, s_city, CAST(sum(lo_revenue) AS bigint) AS sum
FROM (
	SELECT c_city, s_city, toYear(lo_orderdate) AS year, lo_revenue
	FROM (
		SELECT *
		FROM (
			SELECT LO_ORDERDATE AS lo_orderdate, LO_REVENUE AS lo_revenue, C_CITY AS c_city, S_CITY AS s_city
			FROM ssb.lineorder_flat
		)
		WHERE (c_city = CAST('UNITED KI1' AS varchar)
				OR c_city = CAST('UNITED KI5' AS varchar))
			AND (s_city = CAST('UNITED KI1' AS varchar)
				OR s_city = CAST('UNITED KI5' AS varchar))
			AND (toYear(lo_orderdate) >= 1992
				AND toYear(lo_orderdate) <= 1997)
	)
)
GROUP BY year, c_city, s_city)
FORMAT TabSeparatedWithNamesAndTypes;

--- 第三次
SELECT lo_orderdate, lo_revenue, c_city, s_city
FROM (
	SELECT *
	FROM (
		SELECT LO_ORDERDATE AS lo_orderdate, LO_REVENUE AS lo_revenue, C_CITY AS c_city, S_CITY AS s_city
		FROM ssb.lineorder_flat
	)
	WHERE (c_city = CAST('UNITED KI1' AS varchar)
			OR c_city = CAST('UNITED KI5' AS varchar))
		AND (s_city = CAST('UNITED KI1' AS varchar)
			OR s_city = CAST('UNITED KI5' AS varchar))
		AND (toYear(lo_orderdate) >= 1992
			AND toYear(lo_orderdate) <= 1997)
)
FORMAT TabSeparatedWithNamesAndTypes;

具体详情见OpenLookeng-clickhouse connector测试

结论

在千万量级的数据下执行sql查询，OpenLookeng相对Clickhouse的查询时间平均慢193.07%，最好情况下慢85%，最差情况下慢778%。

在十亿量级下执行sql查询，平均慢134.08%，最好的情况优于clickhouse 40%，最差的情况比clickhouse慢381.59%