华为云云耀云服务器L实例评测|ClickHouse单机性能测试
云耀云服务器L实例性能介绍
CPU查看
查看CPU个数
cat /proc/cpuinfo | grep "physical id" | sort | uniq | wc -l
我这款云耀云服务器L实例的CPU个数是1
查看CPU核心数
cat /proc/cpuinfo | grep "cpu cores" | uniq
我这款云耀云服务器L实例的CPU核心数是1
查看线程总数
cat /proc/cpuinfo | grep "processor" | wc -l
CPU线程总数是2
存储查看
查看内存信息
free -h
可以看到目前可用内存是833MB
查看硬盘信息
df
可以看到目前的磁盘空间还剩余80%
lsblk -d -o name,rota
查看是否是固态硬盘
不是固态硬盘
Star Schema Benchmark性能测试
简介
Star Schema Benchmark是目前业界普遍采用的一个星型测试集,在此次ClickHouse的测试中我们将会用Star Schema Benchmark生成6000w行测试数据(大约6G)
测试流程
编译dbgen
$ git clone git@github.com:vadimtk/ssb-dbgen.git
$ cd ssb-dbgen
$ make
如果git命令使用不了,可以手动下载ssb-dbgen包上传至云耀云服务器L实例
编译完成图
生成测试数据
./dbgen -s 10 -T c
./dbgen -s 10 -T l
./dbgen -s 10 -T p
./dbgen -s 10 -T s
使用-s 10
dbgen将生成6000w行数据(大约7G)
生成数据的时间大约是3分钟,期间CPU使用率保持在50%
ls -l *tbl
查看生成的tbl文件
导入数据
在ClickHouse中创建数据表:
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;
复制数据文件到docker容器中
docker cp /root/ssbm/ssb-dbgen-master/part.tbl clickhouse-server:/etc/clickhouse-db
docker cp /root/ssbm/ssb-dbgen-master/lineorder.tbl clickhouse-server:/etc/clickhouse-db
docker cp /root/ssbm/ssb-dbgen-master/customer.tbl clickhouse-server:/etc/clickhouse-db
docker cp /root/ssb-dbgen-master/ssb-dbgen-master/supplier.tbl clickhouse-server:/etc/clickhouse-db
导入数据文件
clickhouse-client --password ClickHouse1314 --database testDB --query "INSERT INTO customer FORMAT CSV" < customer.tbl
clickhouse-client --password ClickHouse1314 --database testDB --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl
clickhouse-client --password ClickHouse1314 --database testDB --query "INSERT INTO part FORMAT CSV" < part.tbl
clickhouse-client --password ClickHouse1314 --database testDB --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
导入数据文件的时间大约在4分钟,期间CPU基本都是满占用
将star scheme
转换为flat scheme
SET max_memory_usage = 1200000000;
SET max_bytes_before_external_group_by = 20000000000;
CREATE TABLE lineorder_flat
ENGINE = 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_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;
由于云服务器的内存限制,我将SQL运行的最大内存限制在了1.2G
内存不足
非常的遗憾,运行到上一步转换的时候,无论我采取怎样的方法,始终无法完整的转换数据,因此我选择将数据的规模调整为600w行。
减少数据规模之后,表转换共进行了18s
运行查询
最后,用官方给出的sql检验数据结果,这里我选择了Q1与Q4,用来演示不同复杂度sql的执行时间
Q1.1
73ms
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
107ms
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
95ms
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;
Q4.1
336ms
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
112ms
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
98ms
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;
测试总结
这次的ClickHouse测试过程主要遇到的瓶颈是云耀云服务器L实例的内存不足,但是还是可以看到ClickHouse的强大性能,如果有充足的内存,ClickHouse作为列式存储库,能发挥出相当强大的作用。
同时,在测试过程中,除了内存不足的问题,很难想象在如此有限的硬件条件下,云耀云服务器L实例的使用过程能如此的流畅。