Clickhouse基准测试实践

技术分享 2年前 (2022-08-29) 0 999+

1.概述

本篇博客将对MySQL、InfluxDB、Clickhouse在写入时间、聚合查询时间、磁盘使用等方面的性能指标来进行比较。

2.内容

比较的数据集，是使用的Clickhouse官网提供的6600万的数据集来进行测试比较的，当MySQL、InfluxDB、Clickhouse也分配4CPU和16GB内存的资源时，Clickhouse完全是在导入速度、磁盘使用和查询性能等方面体现非常好的效果。结论如下所示：

	MySQL	InfluxDB	Clickhouse
导入时间	70分钟	35分钟	70秒
磁盘占用空间	12.35GB	5.9GB	2.66GB
全表count	24366ms	11674ms	100ms
全表max/min	27023ms	26829ms	186ms
全表求平均值	24841ms	12043ms	123ms
全表求方差	24600ms	OOM	113ms
复杂查询1	30260ms	OOM	385ms
复杂查询2	470ms	200ms	8ms

为了保证测试结果比较准确，上面的每条SQL至少执行10次，然后取中间值。其中InfluxDB的性能比预期差，甚至比MySQL差，这可能是由于数据样本和测试用例不合适InfluxDB场景造成的。

2.1 MySQL

MySQL是Oracle的产品，是目前最流行的关系型数据库管理系统之一。它使用的SQL语言是用于访问数据库的最常见的标准化语言。采用双授权策略，分为社区版和商业版。由于体积小、速度快、总体拥有成本低，尤其是其开源特性，一般选择MySQL作为中小型网站开发的网站数据库。

MySQL并不完美，但是它足够灵活，是架构中的最佳选择之一，并且在复杂的非单一项目中总能拥有一席之地。

2.2 InfluxDB

InfluxDB是InfluxData开发的开源时序数据库，专注于海量时序数据的高性能读、高性能写、高效存储和实时分析。在数据库引擎排名时序数据库中，它位居第一，广泛应用与开发运维监控、物联网监控、实时分析等场景。

传统数据库通常记录数据的当前值，而时序数据库记录所有历史数据。在处理当前时序数据时，必须不断接收新的时序数据。同时，时序数据的查询始终是基于时间的。它重点解决以下海量数据场景：

时序数据的写入：如何支持每条千万条数据的写入；
时序数据的读取：如何支持每条千万条数据的聚合查询；
成本问题：海量数据存储带来的成本问题，如何以更低的成本存储这些数据。

2.3 Clickhouse

Clickhouse是由Yandex开源的基于列存储的数据库，用于实时数据分析，其处理数据的速度比传统方法快100~1000倍。Clickhouse优于当前市场上类似的面向列的DBMS，每台服务器每秒处理数亿到超过10亿行和超过10GB的数据。

它是一个用于在线分析（OLAP）的列式数据库管理系统（DBMS），对OLTP和OLAP的做如下区别介绍：

OLTP：它是一个传统的关系型数据库，主要操作增删改查，强调交易一致性，比如银行系统、电子商务系统等；
OLAP：是一个仓库型的数据库，主要用于读取数据，做复杂的数据分析，专注于技术决策支持，提供直观简单的结果。

Clickhouse用于OLAP的适用场景如下：

读取多于写入；
一个大的宽表读取大量的行和很少的列，同时导入较小的结果集；
数据时分批写入的，数据不更新或者更新频率很低；
无需事务，数据一致性要求较低；
灵活多变，不适合模型预构建（类似Kylin的Cube）

3.测试数据准备

直接使用Clickhouse提供的测试数据地址：https://clickhouse.com/docs/en/getting-started/example-datasets/opensky/，这个数据集中的数据是从完整的OpenSky数据集中导出和清洗过的。

该数据集涵盖了自2019年1月1日以来，该网络2500多名成员看到的所有航班信息。

3.1 下载数据

执行如下命令：

wget -O- https://zenodo.org/record/5092942 | grep -oP 'https://zenodo.org/record/5092942/files/flightlist_d+_d+.csv.gz' | xargs wget

在互联网连接良好的情况下，下载大约需要 2 分钟。共有 30 个文件，总大小为 4.3 GB。

3.2 创建表Clickhouse

执行如下SQL命令：

CREATE TABLE opensky (     callsign String,     number String,     icao24 String,     registration String,     typecode String,     origin String,     destination String,     firstseen DateTime,     lastseen DateTime,     day DateTime,     latitude_1 Float64,     longitude_1 Float64,     altitude_1 Float64,     latitude_2 Float64,     longitude_2 Float64,     altitude_2 Float64 ) ENGINE = MergeTree ORDER BY (origin, destination, callsign);

3.2.1 导入数据

将数据导入到Clickhouse中，执行如下所示命令：

ls -1 flightlist_*.csv.gz | xargs -P100 -I{} bash -c 'gzip -c -d "{}" | clickhouse-client --date_time_input_format best_effort --query "INSERT INTO opensky FORMAT CSVWithNames"'

在这里，我们将文件列表 ( ls -1 flightlist_*.csv.gz)传递xargs给以进行并行处理。 xargs -P100指定最多使用 100 个并行工作器，但由于我们只有 30 个文件，工作器的数量将只有 30 个；
对于每个文件，xargs将运行一个带有bash -c. 该脚本以 of 的形式进行替换，{}并且该xargs命令将文件名替换为它（我们已经要求它xargs使用-I{}）；
该脚本会将文件 ( gzip -c -d "{}") 解压缩到标准输出 (-c参数) 并将输出重定向到clickhouse-client
我们还要求使用扩展解析器 ( --date_time_input_format best_effort ) 解析DateTime字段，以识别具有时区偏移的 ISO-8601 格式

最后，clickhouse-client会做插入。它将以CSVWithNames格式读取输入数据。并行上传需要 24 秒。如果不想使用并行上传，还可以使用顺序上传，可能需要的时间长一点，大概 75 秒，具体执行命令如下：

for file in flightlist_*.csv.gz; do gzip -c -d "$file" | clickhouse-client --date_time_input_format best_effort --query "INSERT INTO opensky FORMAT CSVWithNames"; done

完整代码如下：

$ clickhouse-client $ create database test; $ use test; $ CREATE TABLE opensky(callsign String,number String,icao24 String,registration String,typecode String,origin String,destination String,firstseen DateTime,lastseen DateTime,day DateTime,latitude_1 Float64,longitude_1 Float64,altitude_1 Float64,latitude_2 Float64,longitude_2 Float64,altitude_2 Float64) ENGINE = MergeTree ORDER BY (origin, destination, callsign); $ exit  # Import data (about 75 seconds) $ cd /tmp/flightlist $ for file in flightlist_*.csv; do cat "$file" | clickhouse-client --date_time_input_format best_effort --query "INSERT INTO test.opensky FORMAT CSVWithNames"; done  # Check if the data was imported successfully $ clickhouse-client $ SELECT count() FROM test.opensky;

3.3 创建MySQL

完整代码如下：

# Link MySQL to build database and table $ mysql -uroot -p123456 $ use test; $ CREATE TABLE `opensky` (`callsign` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`icao24` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`registration` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`typecode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`origin` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`destination` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`firstseen` datetime DEFAULT NULL,`lastseen` datetime DEFAULT NULL,`day` datetime DEFAULT NULL,`latitude_1` double DEFAULT NULL,`longitude_1` double DEFAULT NULL,`altitude_1` double DEFAULT NULL,`latitude_2` double DEFAULT NULL,`longitude_2` double DEFAULT NULL,`altitude_2` double DEFAULT NULL,KEY `idx_callsign` (`callsign`),KEY `idx_origin` (`origin`),KEY `idx_destination` (`destination`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;  # Import data (about 70 minutes) $ load data local infile 'flightlist_20190101_20190131.csv' into table opensky character set utf8mb4 fields terminated by ',' lines terminated by 'n' ignore 1 lines; # Omit the other 29 import commands：load data local infile 'flightlist_*_*.csv' into table opensky character set utf8mb4 fields terminated by ',' lines terminated by 'n' ignore 1 lines;  # Check if the data was imported successfully $ select count(*) from test.opensky;

3.4 创建InfluxDB

完整代码如下：

# Import data (about 30 minutes) $ influx -username 'admin' -password 'admin123456' -import -path=/tmp/flightlist/flightlist_20190101_20190131.txt -precision=ns; # Omit the other 29 import commands：influx -username 'admin' -password 'admin123456' -import -path=/tmp/flightlist/flightlist_*_*.txt -precision=ns;  # Check if the data was imported successfully $ influx -username 'admin' -password 'admin123456' $ select count(latitude_1) from test.autogen.opensky;

4.测试场景

4.1 MySQL测试维度

从不同的维度，对MySQL来进行测试，具体实现代码如下所示：

$ mysql -uroot -p123456 $ use test; -- Enable performance analysis set profiling = 1; -- query disk space select table_rows as `total_lines`, (data_length + index_length)/1024/1024/1024 as `disk_usage(G)` from information_schema.`TABLES` where table_name = 'opensky'; -- full table count select count(latitude_1) from opensky; -- full table max/min select max(longitude_1),min(altitude_1) from opensky; -- full table average select avg(latitude_2) from opensky; -- full table variance select var_pop(longitude_2) from opensky; -- Complex query 1: Aggregate query of multiple fields in the whole table select count(latitude_1),max(longitude_1),min(altitude_1),avg(latitude_2) from opensky; -- Complex query 2: Number of flights departing from the three main Moscow airports SELECT origin, count(1) AS c FROM opensky WHERE origin IN ('UUEE', 'UUDD', 'UUWW') GROUP BY origin; -- output analysis results show profiles;

4.2 InfluxDB测试维度

从不同的维度，对InfluxDB来进行测试，具体实现代码如下所示：

$ influx -username 'admin' -password 'admin123456' $ use test; -- Time-consuming statistics，queryReqDurationNs is the cumulative query time, and the subtraction of the time of the two tasks is the time-consuming select queryReq,queryReqDurationNs/1000/1000,queryRespBytes from _internal."monitor".httpd order by time desc limit 10; -- query disk space select sum(diskBytes) / 1024 / 1024 /1024 from _internal."monitor"."shard" where time > now() - 10s group by "database"; -- full table count select count(latitude_1) from opensky; -- full table max/min select max(longitude_1),min(altitude_1) from opensky; -- full table average select mean(latitude_2) from opensky; -- full table variance select stddev(longitude_2) from opensky; -- Complex query 1: Aggregate query of multiple fields in the whole table select count(latitude_1),max(longitude_1),min(altitude_1),mean(latitude_2) from opensky; -- Complex query 2: Number of flights departing from the three main Moscow airports SELECT count(latitude_1) AS c FROM opensky WHERE origin =~/^UUEE|UUDD|UUWW$/ GROUP BY origin;

4.3 Clickhouse测试维度

从不同的维度，对Clickhouse来进行测试，具体实现代码如下所示：

$ clickhouse-client $ use test; -- Time-consuming statistics select event_time_microseconds,query_duration_ms,read_rows,result_rows,memory_usage,query from system.query_log where query like '%opensky%' and query_duration_ms <> 0 and query not like '%event_time_microseconds%' order by event_time_microseconds desc limit 5; -- query disk space SELECT formatReadableSize(total_bytes) FROM system.tables WHERE name = 'opensky'; -- full table count select count(latitude_1) from opensky; -- full table max/min select max(longitude_1),min(altitude_1) from opensky; -- full table average select avg(latitude_2) from opensky; -- full table variance select var_pop(longitude_2) from opensky; -- Complex query 1: Aggregate query of multiple fields in the whole table select count(latitude_1),max(longitude_1),min(altitude_1),avg(latitude_2) from opensky; -- Complex query 2: Number of flights departing from the three main Moscow airports SELECT origin, count() AS c FROM opensky WHERE origin IN ('UUEE', 'UUDD', 'UUWW') GROUP BY origin;

5.为什么Clickhouse这么快

5.1 列式存储

数据存储在列中，数据就是索引；
查询只访问涉及的列，减少了系统 I/O；
每列由一个线程处理，有效利用 CPU 资源；
它还为矢量化执行奠定了基础。

5.2 数据压缩

数据压缩的本质是按照一定的步长对数据进行匹配扫描，发现重复数据时进行编码转换。

因为是列式存储，数据特性非常相似，所以数据中存在很多重复，压缩率越高，数据量越小，磁盘I/O压力越低，网络传输越快。

5.3 矢量化执行引擎

单指令多数据是指一条指令操作多条数据。是通过数据并行来提高性能的一种方式，可以简单理解为程序中数据在寄存器级别的并行处理。

Clickhouse 广泛使用 SIMD 来提高计算效率。通过使用SIMD，基本上可以带来数倍的性能提升。

5.4 多线程和分布式

分布式领域有个规律，计算移动比数据移动更划算，这就是它的核心。

数据的计算直接发送到数据所在的服务器，进行多机并行处理，然后将最终结果汇总在一起。

此外，ClickHouse 还通过线程级并行进一步提高效率，充分利用服务器资源。

5.5 各种表引擎

MergeTree 存储结构对写入的数据进行排序，然后有序存储。有序存储有两个主要优点：

对列存文件进行分块压缩时，排序键中的列值是连续的或重复的，这样列存分块中的数据才能得到最终的压缩比。
存储顺序本身可以加快查询的索引结构。根据排序键中列的等价条件或范围条件，我们可以快速找到目标的大致位置范围，而且这种索引结构不会产生额外的存储开销。

MergeTree 是 ClickHouse 表引擎中的核心引擎。其他引擎基于 MergeTree 引擎，在数据合并过程中实现不同的特性，从而形成 MergeTree 表引擎家族。

6.总结

Clickhouse的优缺点如下：

优势：极致的查询分析性能、低存储成本、高吞吐数据写入、多样化的表引擎、完备的DBMS功能。
缺点：不支持事务，不支持真正的删除/更新，分发能力弱；不支持高并发，官方推荐100 QPS。

对于非标准的SQL，join的实现比较特殊，性能不好；频繁的小批量数据操作会影响查询性能。目前还没有可以满足各种场景需求的OLAP引擎。本质原因是没有一个系统可以同时在查询效率、及时性和可维护性方面做到完美。只能说ClickHouse是为了极致的查询性能。做了一些取舍。ClickHouse 的优缺点是显而易见的。是否采用取决于与实际业务场景的契合度。适合你的架构是最好的架构。