Clickhouse基准测试实践

2022/8/28 23:25:37

本文主要是介绍Clickhouse基准测试实践,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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 的优缺点是显而易见的。是否采用取决于与实际业务场景的契合度。适合你的架构是最好的架构。

7.结束语

这篇博客就和大家分享到这里,如果大家在研究学习的过程当中有什么问题,可以加群进行讨论或发送邮件给我,我会尽我所能为您解答,与君共勉!

另外,博主出书了《Kafka并不难学》和《Hadoop大数据挖掘从入门到进阶实战》,喜欢的朋友或同学, 可以在公告栏那里点击购买链接购买博主的书进行学习,在此感谢大家的支持。关注下面公众号,根据提示,可免费获取书籍的教学视频。



这篇关于Clickhouse基准测试实践的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程