前言
本文为您介绍如何使用 Star Schema 数据集对腾讯云数据仓库 TCHouse-C 进行性能测试,给出数据导入及性能测试的参考方案。
准备工作
购买实例
准备测试机器
准备能够访问腾讯云数据仓库 TCHouse-C 服务的 Linux 系统机器,并在该机器上安装 clickhouse-client 工具。测试机器需要能够访问腾讯云数据仓库 TCHouse-C 服务,至少需1.5TB存储空间。在测试机器上安装`clickhouse client`工具,请参见安装文档。
在购买实例后,请在控制台中调整参数如下:
参数名称 | 所载文件 | 作用 | 建议值 |
max_threads | users.xml | 单个查询允许使用的线程数 | CPU 核数 |
max_insert_threads | users.xml | 单次写入允许使用的线程数据 | CPU 核数 |
max_memory_usage | users.xml | 单次查询允许使用的内存最大值 | 总内存数 | 10GB |
background_pool_size | users.xml | MergeTree 引擎后台任务线程池大小 | CPU 核数*2 |
max_thread_pool_size | config.xml | 全局线程池最大分配线程数量 | 20000 |
max_open_files | config.xml | 允许进程打开的最大文件句柄上 | 1000000 |
mark_cache_size | config.xml | mark 文件缓存大小 | 10737418240 |
注意:调整完成后,请重启集群。
测试步骤
确认软件版本
使用
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亿行规模的数据。建议采用-s 1000
. $ ./dbgen -s 1000 -T c$ ./dbgen -s 1000 -T l$ ./dbgen -s 1000 -T p$ ./dbgen -s 1000 -T s
创建数据库表
在腾讯云数据仓库 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 SELECTl.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_flatADD PROJECTION p1(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE)GROUP BYyear,P_BRAND,P_CATEGORY,S_REGION);ALTER TABLE lineorder_flatADD PROJECTION p2(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE)GROUP BYyear,C_NATION,S_NATION,C_REGION,S_REGION);ALTER TABLE lineorder_flatADD PROJECTION p3(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE)GROUP BYyear,C_CITY,S_CITY);ALTER TABLE lineorder_flatADD PROJECTION p4(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE)GROUP BYyear,C_NATION,C_CITY,S_NATION,S_CITY);ALTER TABLE lineorder_flatADD PROJECTION p5(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE - LO_SUPPLYCOST) AS profitGROUP BYyear,C_NATION,C_REGION,S_REGION,P_MFGR,P_MFGR);ALTER TABLE lineorder_flatADD PROJECTION p6(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE - LO_SUPPLYCOST)GROUP BYyear,S_NATION,P_CATEGORY,C_REGION,S_REGION,P_MFGR);ALTER TABLE lineorder_flatADD PROJECTION p7(SELECTtoYear(LO_ORDERDATE) AS year,sum(LO_REVENUE - LO_SUPPLYCOST)GROUP BYyear,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) = 1994AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
Q2.1
SELECTsum(LO_REVENUE),toYear(LO_ORDERDATE) AS year,P_BRANDFROM lineorder_flatWHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'GROUP BYyear,P_BRANDORDER BYyear,P_BRAND;
Q2.2
SELECTsum(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 BYyear,P_BRANDORDER BYyear,P_BRAND;
Q2.3
SELECTsum(LO_REVENUE),toYear(LO_ORDERDATE) AS year,P_BRANDFROM lineorder_flatWHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'GROUP BYyear,P_BRANDORDER BYyear,P_BRAND;
Q3.1
SELECTC_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 BYC_NATION,S_NATION,yearORDER BYyear ASC,revenue DESC;
Q3.2
SELECTC_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 BYC_CITY,S_CITY,yearORDER BYyear ASC,revenue DESC;
Q3.3
SELECTC_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 BYC_CITY,S_CITY,yearORDER BYyear ASC,revenue DESC;
Q3.4
SELECTC_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 BYC_CITY,S_CITY,yearORDER BYyear ASC,revenue DESC;
Q4.1
SELECTtoYear(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 BYyear,C_NATIONORDER BYyear ASC,C_NATION ASC;
Q4.2
SELECTtoYear(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 BYyear,S_NATION,P_CATEGORYORDER BYyear ASC,S_NATION ASC,P_CATEGORY ASC;
Q4.3
SELECTtoYear(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 BYyear,S_CITY,P_BRANDORDER BYyear ASC,S_CITY ASC,P_BRAND ASC;
总结
性能测试作为腾讯云数据仓库 TCHouse-C 业务接入前的重要一环,是性能评估以及资源评估的重要依据。
通常业务面临多种系统选型时,也会进行性能对比测试。在对比测试过程中,需要注意以下几点:
腾讯云数据仓库 TCHouse-C 的一些关键参数会影响性能,务必调整合理,才能充分发挥其性能优势。
需要对齐资源。例如,一次性分布式查询,腾讯云数据仓库 TCHouse-C 只有1/2节点参与计算;而其他系统则是全部节点参与计算。在这种情况下,在等同数据规模下,腾讯云数据仓库 TCHouse-C 可能性能数据不占优。在这种情况下,可以调整集群备份策略,让所有节点参与计算。
腾讯云数据仓库 TCHouse-C 有很多特有的性能优化机制,开启这些机制,能够明显提升查询性能。