Clickhouse基准测试实践

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

比较的数据集，是使用的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 表引擎家族。

Clickhouse的优缺点如下：

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

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

7.结束语

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

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

Clickhouse基准测试实践 - 哥不是小萝莉

Clickhouse基准测试实践

2.1 MySQL

2.2 InfluxDB

2.3 Clickhouse

3.测试数据准备

3.1 下载数据

3.2 创建表Clickhouse

3.2.1 导入数据

3.3 创建MySQL

3.4 创建InfluxDB

4.测试场景

4.1 MySQL测试维度

4.2 InfluxDB测试维度

4.3 Clickhouse测试维度

5.为什么Clickhouse这么快

5.1 列式存储

5.2 数据压缩

5.3 矢量化执行引擎

5.4 多线程和分布式

5.5 各种表引擎

7.结束语

Recommend

扎克伯格公开暗示帮助掩盖拜登家族丑闻？各方紧急回应

RX 7000要复制锐龙成功之道 AMD今年能正面刚NVIDIA了

Android 自动取色并设置沉浸式状态栏 - Stars-one

Wii U正统在V社？玩家用Steam掌机玩3DS双屏游戏

Leverage IELTS - A comprehensive IELTS prep app | Product Hunt

MPI学习笔记（三）：矩阵相乘的分块并行(行列划分法) - 惊小呆520

现在北京四惠/四惠东地铁站自东向西早上 9.00-9.30 出发要排队吗？

从零开始搭建react基础开发环境（基于webpack5） - handsomezyw

GitHub - Marcisbee/messagepipe: Formats message strings with number, date, plura...

Building an Accurate Digital Clock with Multithreading in JavaScript!

About Joyk