腾讯云数据仓库 TCHouse-C 测试方案介绍

一、购买实例

参照 快速入门 购买腾讯云数据仓库 TCHouse-C 实例，可选择标准型、高性能型、大存储型，内核版本建议选择 22.8 及以后版本。

二、准备测试机器

准备能够访问腾讯云数据仓库 TCHouse-C 服务的 Linux 系统机器，并在该机器上安装 clickhouse client 工具。测试机器需要能够访问腾讯云数据仓库 TCHouse-C 服务，至少需 1.5TB 存储空间。在测试机器上安装 clickhouse client 工具，请参见 安装文档。

在购买实例后，请在控制台中调整参数如下：

具体参数调整，请参见 参数配置。

注意：调整完成后，请重启集群。

三、确认软件版本

使用clickhouse client访问腾讯云数据仓库 TCHouse-C 服务，查看软件版本。

clickhouse client –host $HOST –port $PORT -q "select version()"

注意：请确保软件版本大于22.8.*。

四、准备数据生成工具

$ git clone git@github.com:vadimtk/ssb-dbgen.git$ cd ssb-dbgen$ make

五、生成测试数据

ssb-dbgen 工具支持两种规模的数据，使用参数-s 100 可以生成约 6 亿行规模的数据，使用参数-s 1000 可以生成约 60 亿行规模的数据。建议采用如下方式：

# 生成约60亿行数据（-s 1000 表示缩放因子）./dbgen -s 1000 -T c # 生成 customer 表数据./dbgen -s 1000 -T l # 生成 lineorder 表数据./dbgen -s 1000 -T p # 生成 part 表数据./dbgen -s 1000 -T s # 生成 supplier 表数据

六、创建数据库表

在腾讯云数据仓库 TCHouse-C 控制台上，获取服务入口信息：访问IP地址和服务端口。分别记录为 HOST 和 PORT。使用clickhouse client工具链接腾讯云数据仓库 TCHouse-C 服务，执行如下 SQL：

CREATE TABLE customer( C_CUSTKEY UInt32, C_NAME String, C_ADDRESS String, C_CITY LowCardinality(String), C_NATION LowCardinality(String), C_REGION LowCardinality(String), C_PHONE String, C_MKTSEGMENT LowCardinality(String))ENGINE = MergeTree ORDER BY (C_CUSTKEY);CREATE TABLE lineorder( LO_ORDERKEY UInt32, LO_LINENUMBER UInt8, LO_CUSTKEY UInt32, LO_PARTKEY UInt32, LO_SUPPKEY UInt32, LO_ORDERDATE Date, LO_ORDERPRIORITY LowCardinality(String), LO_SHIPPRIORITY UInt8, LO_QUANTITY UInt8, LO_EXTENDEDPRICE UInt32, LO_ORDTOTALPRICE UInt32, LO_DISCOUNT UInt8, LO_REVENUE UInt32, LO_SUPPLYCOST UInt32, LO_TAX UInt8, LO_COMMITDATE Date, LO_SHIPMODE LowCardinality(String))ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);CREATE TABLE part( P_PARTKEY UInt32, P_NAME String, P_MFGR LowCardinality(String), P_CATEGORY LowCardinality(String), P_BRAND LowCardinality(String), P_COLOR LowCardinality(String), P_TYPE LowCardinality(String), P_SIZE UInt8, P_CONTAINER LowCardinality(String))ENGINE = MergeTree ORDER BY P_PARTKEY;CREATE TABLE supplier( S_SUPPKEY UInt32, S_NAME String, S_ADDRESS String, S_CITY LowCardinality(String), S_NATION LowCardinality(String), S_REGION LowCardinality(String), S_PHONE String)ENGINE = MergeTree ORDER BY S_SUPPKEY;

七、导入测试数据

首先，导入基础表数据：

$ clickhouse client –host $HOST –port $PORT –query "INSERT INTO customer FORMAT CSV" < customer.tbl$ clickhouse client –host $HOST –port $PORT –query "INSERT INTO part FORMAT CSV" < part.tbl$ clickhouse client –host $HOST –port $PORT –query "INSERT INTO supplier FORMAT CSV" < supplier.tbl$ clickhouse client –host $HOST –port $PORT –query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

其次，根据基础表数据生成宽表数据。

这里需要注意已经调整了max_memory_usage 和 max_insert_threads参数。

CREATE TABLE lineorder_flatENGINE = MergeTree ORDER BY (LO_ORDERDATE, LO_ORDERKEY)AS SELECT l.LO_ORDERKEY AS LO_ORDERKEY, l.LO_LINENUMBER AS LO_LINENUMBER, l.LO_CUSTKEY AS LO_CUSTKEY, l.LO_PARTKEY AS LO_PARTKEY, l.LO_SUPPKEY AS LO_SUPPKEY, l.LO_ORDERDATE AS LO_ORDERDATE, l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY, l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY, l.LO_QUANTITY AS LO_QUANTITY, l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE, l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE, l.LO_DISCOUNT AS LO_DISCOUNT, l.LO_REVENUE AS LO_REVENUE, l.LO_SUPPLYCOST AS LO_SUPPLYCOST, l.LO_TAX AS LO_TAX, l.LO_COMMITDATE AS LO_COMMITDATE, l.LO_SHIPMODE AS LO_SHIPMODE, c.C_NAME AS C_NAME, c.C_ADDRESS AS C_ADDRESS, c.C_CITY AS C_CITY, c.C_NATION AS C_NATION, c.C_REGION AS C_REGION, c.C_PHONE AS C_PHONE, c.C_MKTSEGMENT AS C_MKTSEGMENT, s.S_NAME AS S_NAME, s.S_ADDRESS AS S_ADDRESS, s.S_CITY AS S_CITY, s.S_NATION AS S_NATION, s.S_REGION AS S_REGION, s.S_PHONE AS S_PHONE, p.P_NAME AS P_NAME, p.P_MFGR AS P_MFGR, p.P_CATEGORY AS P_CATEGORY, p.P_BRAND AS P_BRAND, p.P_COLOR AS P_COLOR, p.P_TYPE AS P_TYPE, p.P_SIZE AS P_SIZE, p.P_CONTAINER AS P_CONTAINERFROM lineorder AS lINNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEYINNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEYINNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

八、优化查询（可选）

腾讯云数据仓库 TCHouse-C 提供了预计算能力，加速执行。这里使用 PROJECTION 来加速查询。执行如下 SQL：

ALTER TABLE lineorder_flat ADD PROJECTION p1 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) GROUP BY year, P_BRAND, P_CATEGORY, S_REGION ); ALTER TABLE lineorder_flat ADD PROJECTION p2 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) GROUP BY year, C_NATION, S_NATION, C_REGION, S_REGION ); ALTER TABLE lineorder_flat ADD PROJECTION p3 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) GROUP BY year, C_CITY, S_CITY ); ALTER TABLE lineorder_flat ADD PROJECTION p4 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) GROUP BY year, C_NATION, C_CITY, S_NATION, S_CITY ); ALTER TABLE lineorder_flat ADD PROJECTION p5 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE – LO_SUPPLYCOST) AS profit GROUP BY year, C_NATION, C_REGION, S_REGION, P_MFGR, P_MFGR ); ALTER TABLE lineorder_flat ADD PROJECTION p6 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE – LO_SUPPLYCOST) GROUP BY year, S_NATION, P_CATEGORY, C_REGION, S_REGION, P_MFGR ); ALTER TABLE lineorder_flat ADD PROJECTION p7 ( SELECT toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE – LO_SUPPLYCOST) GROUP BY year, S_CITY, P_BRAND, S_NATION, P_CATEGORY );

执行上述 SQL 后，需要对存量数据进行处理，使 PROJECTION 在存量数据上生效。执行如下 SQL：

ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p1;ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p2;ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p3;ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p4;ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p5;ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p6;ALTER TABLE lineorder_flat MATERIALIZE PROJECTION p7;

注意：该步骤是可选的，使用优化后，性能提升非常明显。

九、执行测试 SQL 并统计执行时间数据

Q1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;

Q1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE 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 revenueFROM lineorder_flatWHERE 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_BRANDFROM lineorder_flatWHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'GROUP BY year, P_BRANDORDER BY year, P_BRAND;

Q2.2

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRANDFROM lineorder_flatWHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'GROUP BY year, P_BRANDORDER BY year, P_BRAND;

Q2.3

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRANDFROM lineorder_flatWHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'GROUP BY year, P_BRANDORDER BY year, P_BRAND;

Q3.1

SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997GROUP BY C_NATION, S_NATION, yearORDER BY year ASC, revenue DESC;

Q3.2

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997GROUP BY C_CITY, S_CITY, yearORDER BY year ASC, revenue DESC;

Q3.3

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997GROUP BY C_CITY, S_CITY, yearORDER BY year ASC, revenue DESC;

Q3.4

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenueFROM lineorder_flatWHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712GROUP BY C_CITY, S_CITY, yearORDER BY year ASC, revenue DESC;

Q4.1

SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE – LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')GROUP BY year, C_NATIONORDER BY year ASC, C_NATION ASC;

Q4.2

SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE – LO_SUPPLYCOST) AS profitFROM lineorder_flatWHERE 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_CATEGORYORDER 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 profitFROM lineorder_flatWHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'GROUP BY year, S_CITY, P_BRANDORDER BY year ASC, S_CITY ASC, P_BRAND ASC;

十、总结

性能测试作为腾讯云数据仓库 TCHouse-C 业务接入前的重要一环，是性能评估以及资源评估的重要依据。

通常业务面临多种系统选型时，也会进行性能对比测试。在对比测试过程中，需要注意以下几点：

·腾讯云数据仓库 TCHouse-C 的一些关键参数会影响性能，务必调整合理，才能充分发挥其性能优势。

·需要对齐资源。例如，一次性分布式查询，腾讯云数据仓库 TCHouse-C 只有 1/2 节点参与计算；而其他系统则是全部节点参与计算。在这种情况下，在等同数据规模下，腾讯云数据仓库 TCHouse-C 可能性能数据不占优。在这种情况下，可以调整集群备份策略，让所有节点参与计算。

·腾讯云数据仓库 TCHouse-C 有很多特有的性能优化机制，开启这些机制，能够明显提升查询性能。