本文的翻译是专门为数据工程师课程的学生准备的。
ClickHouse是一个开源列数据库。 这是一个绝佳的环境，即使每天要引入数百亿个新条目，数百名分析人员也可以快速请求详细数据。 支持此类系统的基础架构成本每年可能高达10万美元，并且可能高达一半，具体取决于使用情况。 在某个时候，Yandex.Metrica ClickHouse安装包含10万亿个条目。 除Yandex之外，ClickHouse在Bloomberg和Cloudflare方面也获得了成功。
两年前，我使用一台机器对数据库进行了
比较分析 ，它成为
了我见过
的最快的免费数据库软件。 从那时起，开发人员就没有停止添加功能，包括对Kafka，HDFS和ZStandard压缩的支持。 去年，他们增加了对级联压缩方法的支持，从而实现了
delta-delta编码。 压缩时间序列数据时，可以使用增量编码很好地压缩量表值，但最好将增量-增量编码用于计数器。 良好的压缩已成为ClickHouse性能的关键。
除第三方库外，ClickHouse包含17万行C ++代码，并且是分布式数据库中最小的代码数据库之一。 相比之下，SQLite不支持分发，它包含23.5万行C语言代码，在撰写本文时，有207位工程师为ClickHouse做出了贡献，并且提交的强度最近有所提高。
2017年3月，ClickHouse启动了
更改日志，以作为跟踪开发的简便方法。 他们还将整体文档文件拆分为基于Markdown的文件层次结构。 通过GitHub跟踪问题和功能，并且在过去几年中，该软件的整体访问性得到了大大提高。
在本文中，我将介绍使用36核处理器和NVMe驱动器的AWS EC2在ClickHouse群集上的性能。
更新：在这篇文章的最初发布之后一周，我以改进的配置重新运行了该测试，并获得了更好的结果。 帖子已更新，以反映这些更改。
启动AWS EC2集群
我将在这篇文章中使用c5d.9xlarge EC2的三个实例。 它们每个都包含36个虚拟CPU，72 GB的RAM，900 GB的NVMe SSD，并支持10 Gb网络。 当按需推出时，它们在eu-west-1地区的价格为每小时1,962美元。 我将使用Ubuntu Server 16.04 LTS作为操作系统。
配置防火墙后，每台机器都可以相互通信而不受限制，并且SSH群集中仅将我的IPv4地址列入了白名单。
即用型NVMe
为了使ClickHouse正常工作，我将在NVMe驱动器中的每台服务器上创建一个EXT4文件系统。
$ sudo mkfs -t ext4 /dev/nvme1n1 $ sudo mkdir /ch $ sudo mount /dev/nvme1n1 /ch
配置完所有内容后，您可以看到每个系统上的安装点和783 GB的可用空间。
$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT loop0 7:0 0 87.9M 1 loop /snap/core/5742 loop1 7:1 0 16.5M 1 loop /snap/amazon-ssm-agent/784 nvme0n1 259:1 0 8G 0 disk └─nvme0n1p1 259:2 0 8G 0 part / nvme1n1 259:0 0 838.2G 0 disk /ch
$ df -h
Filesystem Size Used Avail Use% Mounted on udev 35G 0 35G 0% /dev tmpfs 6.9G 8.8M 6.9G 1% /run /dev/nvme0n1p1 7.7G 967M 6.8G 13% / tmpfs 35G 0 35G 0% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 35G 0 35G 0% /sys/fs/cgroup /dev/loop0 88M 88M 0 100% /snap/core/5742 /dev/loop1 17M 17M 0 100% /snap/amazon-ssm-agent/784 tmpfs 6.9G 0 6.9G 0% /run/user/1000 /dev/nvme1n1 825G 73M 783G 1% /ch
我将在此测试中使用的数据集是我六年来在纽约制造的11亿次出租车中生成的数据转储。
Redshift上的
Billion Taxi博客详细介绍了我如何收集此数据集。 它们存储在AWS S3中，因此我将使用访问权和私钥配置AWS命令行界面。
$ sudo apt update $ sudo apt install awscli $ aws configure
我将同时客户端请求的数量限制设置为100，以使文件加载速度比标准设置更快。
$ aws configure set \ default.s3.max_concurrent_requests \ 100
我将从AWS S3下载出租车行车数据集，并将其保存在第一台服务器上的NVMe驱动器上。 该数据集约为104 GB（采用GZIP压缩的CSV格式）。
$ sudo mkdir -p /ch/csv $ sudo chown -R ubuntu /ch/csv $ aws s3 sync s3://<bucket>/csv /ch/csv
安装ClickHouse
我将安装Java 8的OpenJDK发行版，因为这是运行Apache ZooKeeper所必需的，这对于在所有三台机器上分布式安装ClickHouse都是必需的。
$ sudo apt update $ sudo apt install \ openjdk-8-jre \ openjdk-8-jdk-headless
然后设置
JAVA_HOME环境变量。
$ sudo vi /etc/profile export JAVA_HOME=/usr $ source /etc/profile
然后，我将使用Ubuntu中的软件包管理系统在所有三台计算机上安装ClickHouse 18.16.1，glances和ZooKeeper。
$ sudo apt-key adv \
$ sudo apt install \ clickhouse-client \ clickhouse-server \ glances \ zookeeperd
我将为ClickHouse创建目录，并在所有三台服务器上进行一些配置覆盖。
$ sudo mkdir /ch/clickhouse $ sudo chown -R clickhouse /ch/clickhouse $ sudo mkdir -p /etc/clickhouse-server/conf.d $ sudo vi /etc/clickhouse-server/conf.d/taxis.conf
这些是我将使用的配置替代。
<?xml version="1.0"?> <yandex> <listen_host>0.0.0.0</listen_host> <path>/ch/clickhouse/</path>
<remote_servers> <perftest_3shards> <shard> <replica> <host>172.30.2.192</host> <port>9000</port> </replica> </shard> <shard> <replica> <host>172.30.2.162</host> <port>9000</port> </replica> </shard> <shard> <replica> <host>172.30.2.36</host> <port>9000</port> </replica> </shard> </perftest_3shards> </remote_servers>
<zookeeper-servers> <node> <host>172.30.2.192</host> <port>2181</port> </node> <node> <host>172.30.2.162</host> <port>2181</port> </node> <node> <host>172.30.2.36</host> <port>2181</port> </node> </zookeeper-servers>
<macros> <shard>03</shard> <replica>01</replica> </macros> </yandex>
然后，我将在所有三台计算机上运行ZooKeeper和ClickHouse服务器。
$ sudo /etc/init.d/zookeeper start $ sudo service clickhouse-server start
将数据加载到ClickHouse
在第一台服务器上，我将创建一个旅行表，该表将使用Log引擎存储出租车行驶数据集。
$ clickhouse-client
然后，我将每个CSV文件解压缩并加载到行程表中。 在55分钟10秒内完成以下操作。 执行此操作后，数据目录大小为134 GB。
$ time (for FILENAME in /ch/csv/trips_x*.csv.gz; do echo $FILENAME gunzip -c $FILENAME | \ clickhouse-client \
导入速度为每秒155 MB的未压缩CSV内容。 我怀疑这是由于GZIP减压的瓶颈所致。 使用xargs并行解压缩所有gzip文件，然后加载解压缩的数据可能更快。 以下是CSV导入过程中报告的内容的描述。
$ sudo glances
ip-172-30-2-200 (Ubuntu 16.04 64bit / Linux 4.4.0-1072-aws) Uptime: 0:11:42 CPU 8.2% nice: 0.0% LOAD 36-core MEM 9.8% active: 5.20G SWAP 0.0% user: 6.0% irq: 0.0% 1 min: 2.24 total: 68.7G inactive: 61.0G total: 0 system: 0.9% iowait: 1.3% 5 min: 1.83 used: 6.71G buffers: 66.4M used: 0 idle: 91.8% steal: 0.0% 15 min: 1.01 free: 62.0G cached: 61.6G free: 0 NETWORK Rx/s Tx/s TASKS 370 (507 thr), 2 run, 368 slp, 0 oth sorted automatically by cpu_percent, flat view ens5 136b 2Kb lo 343Mb 343Mb CPU% MEM% VIRT RES PID USER NI S TIME+ IOR/s IOW/s Command 100.4 1.5 1.65G 1.06G 9909 ubuntu 0 S 1:01.33 0 0 clickhouse-client
在继续之前，我将通过删除源CSV文件来释放NVMe驱动器上的空间。
$ sudo rm -fr /ch/csv
转换为列形式
Log ClickHouse引擎将以面向行的格式存储数据。 为了更快地请求数据，我使用MergeTree引擎将其转换为列格式。
$ clickhouse-client
在34分50秒内完成以下操作。 执行此操作后，数据目录大小为237 GB。
CREATE TABLE trips_mergetree ENGINE = MergeTree(pickup_date, pickup_datetime, 8192) AS SELECT trip_id, CAST(vendor_id AS Enum8('1' = 1, '2' = 2, 'CMT' = 3, 'VTS' = 4, 'DDS' = 5, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14)) AS vendor_id, toDate(pickup_datetime) AS pickup_date, ifNull(pickup_datetime, toDateTime(0)) AS pickup_datetime, toDate(dropoff_datetime) AS dropoff_date, ifNull(dropoff_datetime, toDateTime(0)) AS dropoff_datetime, assumeNotNull(store_and_fwd_flag) AS store_and_fwd_flag, assumeNotNull(rate_code_id) AS rate_code_id, assumeNotNull(pickup_longitude) AS pickup_longitude, assumeNotNull(pickup_latitude) AS pickup_latitude, assumeNotNull(dropoff_longitude) AS dropoff_longitude, assumeNotNull(dropoff_latitude) AS dropoff_latitude, assumeNotNull(passenger_count) AS passenger_count, assumeNotNull(trip_distance) AS trip_distance, assumeNotNull(fare_amount) AS fare_amount, assumeNotNull(extra) AS extra, assumeNotNull(mta_tax) AS mta_tax, assumeNotNull(tip_amount) AS tip_amount, assumeNotNull(tolls_amount) AS tolls_amount, assumeNotNull(ehail_fee) AS ehail_fee, assumeNotNull(improvement_surcharge) AS improvement_surcharge, assumeNotNull(total_amount) AS total_amount, assumeNotNull(payment_type) AS payment_type_, assumeNotNull(trip_type) AS trip_type, pickup AS pickup, pickup AS dropoff, CAST(assumeNotNull(cab_type) AS Enum8('yellow' = 1, 'green' = 2)) AS cab_type, precipitation AS precipitation, snow_depth AS snow_depth, snowfall AS snowfall, max_temperature AS max_temperature, min_temperature AS min_temperature, average_wind_speed AS average_wind_speed, pickup_nyct2010_gid AS pickup_nyct2010_gid, pickup_ctlabel AS pickup_ctlabel, pickup_borocode AS pickup_borocode, pickup_boroname AS pickup_boroname, pickup_ct2010 AS pickup_ct2010, pickup_boroct2010 AS pickup_boroct2010, pickup_cdeligibil AS pickup_cdeligibil, pickup_ntacode AS pickup_ntacode, pickup_ntaname AS pickup_ntaname, pickup_puma AS pickup_puma, dropoff_nyct2010_gid AS dropoff_nyct2010_gid, dropoff_ctlabel AS dropoff_ctlabel, dropoff_borocode AS dropoff_borocode, dropoff_boroname AS dropoff_boroname, dropoff_ct2010 AS dropoff_ct2010, dropoff_boroct2010 AS dropoff_boroct2010, dropoff_cdeligibil AS dropoff_cdeligibil, dropoff_ntacode AS dropoff_ntacode, dropoff_ntaname AS dropoff_ntaname, dropoff_puma AS dropoff_puma FROM trips;
这是操作过程中的扫视输出结果：
ip-172-30-2-200 (Ubuntu 16.04 64bit / Linux 4.4.0-1072-aws) Uptime: 1:06:09 CPU 10.3% nice: 0.0% LOAD 36-core MEM 16.1% active: 13.3G SWAP 0.0% user: 7.9% irq: 0.0% 1 min: 1.87 total: 68.7G inactive: 52.8G total: 0 system: 1.6% iowait: 0.8% 5 min: 1.76 used: 11.1G buffers: 71.8M used: 0 idle: 89.7% steal: 0.0% 15 min: 1.95 free: 57.6G cached: 57.2G free: 0 NETWORK Rx/s Tx/s TASKS 367 (523 thr), 1 run, 366 slp, 0 oth sorted automatically by cpu_percent, flat view ens5 1Kb 8Kb lo 2Kb 2Kb CPU% MEM% VIRT RES PID USER NI S TIME+ IOR/s IOW/s Command 241.9 12.8 20.7G 8.78G 8091 clickhous 0 S 30:36.73 34M 125M /usr/bin/clickhouse-server
在上一个测试中，转换并重新计算了几列。 我发现其中一些功能在此数据集中不再正常工作。 为了解决此问题，我删除了不合适的功能并下载了数据，而没有转换为更详细的类型。
集群数据分配
我将在群集的所有三个节点上分发数据。 首先，我将在所有三台计算机上创建一个表。
$ clickhouse-client
CREATE TABLE trips_mergetree_third ( trip_id UInt32, vendor_id String, pickup_date Date, pickup_datetime DateTime, dropoff_date Date, dropoff_datetime Nullable(DateTime), store_and_fwd_flag Nullable(FixedString(1)), rate_code_id Nullable(UInt8), pickup_longitude Nullable(Float64), pickup_latitude Nullable(Float64), dropoff_longitude Nullable(Float64), dropoff_latitude Nullable(Float64), passenger_count Nullable(UInt8), trip_distance Nullable(Float64), fare_amount Nullable(Float32), extra Nullable(Float32), mta_tax Nullable(Float32), tip_amount Nullable(Float32), tolls_amount Nullable(Float32), ehail_fee Nullable(Float32), improvement_surcharge Nullable(Float32), total_amount Nullable(Float32), payment_type Nullable(String), trip_type Nullable(UInt8), pickup Nullable(String), dropoff Nullable(String), cab_type Nullable(String), precipitation Nullable(Int8), snow_depth Nullable(Int8), snowfall Nullable(Int8), max_temperature Nullable(Int8), min_temperature Nullable(Int8), average_wind_speed Nullable(Int8), pickup_nyct2010_gid Nullable(Int8), pickup_ctlabel Nullable(String), pickup_borocode Nullable(Int8), pickup_boroname Nullable(String), pickup_ct2010 Nullable(String), pickup_boroct2010 Nullable(String), pickup_cdeligibil Nullable(FixedString(1)), pickup_ntacode Nullable(String), pickup_ntaname Nullable(String), pickup_puma Nullable(String), dropoff_nyct2010_gid Nullable(UInt8), dropoff_ctlabel Nullable(String), dropoff_borocode Nullable(UInt8), dropoff_boroname Nullable(String), dropoff_ct2010 Nullable(String), dropoff_boroct2010 Nullable(String), dropoff_cdeligibil Nullable(String), dropoff_ntacode Nullable(String), dropoff_ntaname Nullable(String), dropoff_puma Nullable(String) ) ENGINE = MergeTree(pickup_date, pickup_datetime, 8192);
然后，我将确保第一台服务器可以看到群集中的所有三个节点。
SELECT * FROM system.clusters WHERE cluster = 'perftest_3shards' FORMAT Vertical;
Row 1: ────── cluster: perftest_3shards shard_num: 1 shard_weight: 1 replica_num: 1 host_name: 172.30.2.192 host_address: 172.30.2.192 port: 9000 is_local: 1 user: default default_database:
Row 2: ────── cluster: perftest_3shards shard_num: 2 shard_weight: 1 replica_num: 1 host_name: 172.30.2.162 host_address: 172.30.2.162 port: 9000 is_local: 0 user: default default_database:
Row 3: ────── cluster: perftest_3shards shard_num: 3 shard_weight: 1 replica_num: 1 host_name: 172.30.2.36 host_address: 172.30.2.36 port: 9000 is_local: 0 user: default default_database:
然后，我将在第一个服务器上定义一个新表，该表基于
trips_mergetree_third并使用分布式引擎。
CREATE TABLE trips_mergetree_x3 AS trips_mergetree_third ENGINE = Distributed(perftest_3shards, default, trips_mergetree_third, rand());
然后，我将基于MergeTree的表中的数据复制到所有三台服务器。 在34分44秒内完成以下操作。
INSERT INTO trips_mergetree_x3 SELECT * FROM trips_mergetree;
完成上述操作后，我给了ClickHouse 15分钟的时间以使其脱离最大存储量标记。 三台服务器上的每台服务器的数据目录最终分别为264 GB，34 GB和33 GB。
ClickHouse群集效果评估
接下来看到的是在
trips_mergetree_x3表中多次执行每个查询时看到的最快时间。
$ clickhouse-client
以下完成了2.449秒。
SELECT cab_type, count(*) FROM trips_mergetree_x3 GROUP BY cab_type;
以下完成了0.691秒。
SELECT passenger_count, avg(total_amount) FROM trips_mergetree_x3 GROUP BY passenger_count;
以下以0. 582秒完成。
SELECT passenger_count, toYear(pickup_date) AS year, count(*) FROM trips_mergetree_x3 GROUP BY passenger_count, year;
在0.983秒内完成以下步骤。
SELECT passenger_count, toYear(pickup_date) AS year, round(trip_distance) AS distance, count(*) FROM trips_mergetree_x3 GROUP BY passenger_count, year, distance ORDER BY year, count(*) DESC;
为了进行比较，我在基于MergeTree的表中执行了相同的查询，该表专门位于第一台服务器上。
一个ClickHouse节点的性能评估
接下来看到的是在
trips_mergetree_x3表中多次执行每个查询时看到的最快时间。
以下步骤将在0.241秒内完成。
SELECT cab_type, count(*) FROM trips_mergetree GROUP BY cab_type;
以下步骤在0.826秒内完成。
SELECT passenger_count, avg(total_amount) FROM trips_mergetree GROUP BY passenger_count;
以下过程在1.209秒内完成。
SELECT passenger_count, toYear(pickup_date) AS year, count(*) FROM trips_mergetree GROUP BY passenger_count, year;
以下完成了1.781秒。
SELECT passenger_count, toYear(pickup_date) AS year, round(trip_distance) AS distance, count(*) FROM trips_mergetree GROUP BY passenger_count, year, distance ORDER BY year, count(*) DESC;
对结果的思考
在我的测试中，这是基于免费处理器的免费数据库首次能够胜过GPU数据库。 从那时起，基于GPU的数据库经历了两次修订，但是，尽管如此，ClickHouse在一个节点上显示的性能还是非常出色的。
同时，在分布式引擎上运行查询1时，开销要高一个数量级。 我希望我在这篇文章的研究中有所遗漏，因为当我向集群中添加更多节点时，查询时间如何减少将是一件很高兴的事情。 但是，值得注意的是，当执行其他查询时，生产率提高了大约2倍。
如果ClickHouse不断发展壮大，那么它将有可能将存储和计算分离开来，以便它们可以独立扩展。 去年增加了对HDFS的支持，这可能是朝此方向迈出的一步。 对于计算，如果可以通过向集群添加更多节点来加速单个请求，那么该软件的前景将非常光明。
感谢您抽出宝贵的时间阅读这篇文章。 我为北美和欧洲的客户提供咨询，架构和动手开发服务。 如果您想讨论我的建议如何帮助您的业务，请通过
LinkedIn与我联系。