当前位置：首页 > news >正文

随笔之 ClickHouse 列式分析数据库安装注意事项及基准测试

news 2025/8/5 7:15:32

文章目录

一、基本概括
二、配置注意事项
- 2.1 依赖环境准备
- 2.2 启用web接口
- 2.3 修改数据库时区
- 2.4 端口与防火墙
- 2.5 资源分配
- 2.6 日志文件和数据文件位置修改
三、基准测试
- 3.1 测试数据集选择
- 3.2 下载数据
- 3.3 插入到ClickHouse中
- 3.4 检查数据行和大小
- 3.5 检索特定年份每个天气站的最高露点温度
- 3.6 以特定时间范围、字段和天气站raw数据获取
四、测试结论

一、基本概括

ClickHouse 版本：25.6.5.41（目前最新版本）
服务器配置：16核CPU，32GB内存，高IO 1000GB存储

二、配置注意事项

2.1 依赖环境准备

确保系统支持 SSE4.2指令集，否则可能影响性能.

$ grep -q sse4_2 /proc/cpuinfo && echo "SSE4.2 supported" || echo "SSE4.2 not supported"
SSE4.2 supported

安装部署可以参考一下文章：
How to Install ClickHouse on Ubuntu 22.04

2.2 启用web接口

修改配置文件：

sudo vim /etc/clickhouse-server/config.xml

修改以下配置项（（允许远程访问）或指定IP）：

    <!-- Same for hosts without support for IPv6: --><listen_host>0.0.0.0</listen_host><!-- Default root page on http[s] server. --><http_server_default_response><![CDATA[Greetings from ClickHouse!]]></http_server_default_response>

然后，重启服务。

sudo systemctl restart clickhouse-server

查看 8123 端口是否启用。

$ ss -antpl | grep 8123
LISTEN 0      4096         0.0.0.0:8123       0.0.0.0:*

后续就可以正常使用第三方工具访问数据库了。

2.3 修改数据库时区

数据库默认时区为UTC，根据需要修改时区。

修改配置文件：

sudo vim /etc/clickhouse-server/config.xml

修改以下配置项：

    <!-- Server time zone could be set here.Time zone is used when converting between String and DateTime types,when printing DateTime in text formats and parsing DateTime from text,it is used in date and time related functions, if specific time zone was not passed as an argument.Time zone is specified as identifier from IANA time zone database, like UTC or Africa/Abidjan.If not specified, system time zone at server startup is used.Please note, that server could display time zone alias instead of specified name.Example: Zulu is an alias for UTC.--><timezone>Asia/Shanghai</timezone>

如使用DBeaver发现连接ClickHouse时区不对，需要检查并修改DBeaver驱动属性（关键步骤）
右键点击ClickHouse连接 → 编辑连接 → 驱动属性 → 找到以下参数并修改：

use_server_time_zone：设为true（使用服务端时区）。
use_server_time_zone_for_dates：设为true（日期字段同步服务端时区）。
use_time_zone：设为目标时区（如Asia/Shanghai）。

保存设置，断开连接后重新连接。

操作后验证：执行SELECT NOW()或查询时间字段，确认显示时间与本地一致。

2.4 端口与防火墙

确保 HTTP端口（默认8123）和 TCP端口（默认9000）开放，可通过 ufw（Ubuntu）或 firewall-cmd（CentOS）配置.

Ubuntu/Debian：

sudo ufw allow 8123/tcp
sudo ufw allow 9000/tcp

2.5 资源分配

根据数据规模调整内存参数：max_server_memory_usage（默认无限制，可设置为服务器内存的70%~80%）。

sudo vim /etc/clickhouse-server/config.xml

    <!-- Maximum memory usage (resident set size) for server process.Zero value or unset means default. Default is "max_server_memory_usage_to_ram_ratio" of available physical RAM.If the value is larger than "max_server_memory_usage_to_ram_ratio" of available physical RAM, it will be cut down.The constraint is checked on query execution time.If a query tries to allocate memory and the current memory usage plus allocation is greaterthan specified threshold, exception will be thrown.It is not practical to set this constraint to small values like just a few gigabytes,because memory allocator will keep this amount of memory in caches and the server will deny service of queries.--><max_server_memory_usage>0</max_server_memory_usage>

大数据量场景需增加 max_threads（默认10000)

    <!-- Maximum number of threads in the Global thread pool.This will default to a maximum of 10000 threads if not specified.This setting will be useful in scenarios where there are a large numberof distributed queries that are running concurrently but are idling mostof the time, in which case a higher number of threads might be required.--><max_thread_pool_size>10000</max_thread_pool_size>

2.6 日志文件和数据文件位置修改

ClickHouse 默认的数据文件和日志文件安装位置如下：

数据文件目录：/var/lib/clickhouse/
日志文件目录：/var/log/clickhouse-server/（包含 clickhouse-server.log 和 clickhouse-server.err.log）

以上默认位置均为系统盘，很容易导致磁盘被打满，一般我们选择自定义路径到数据盘。

#批量替换存储路径
# 使用 sed 工具直接修改 ClickHouse 配置文件 /etc/clickhouse-server/config.xml
# 将默认数据目录路径从 /var/lib/clickhouse/ 替换为 /data/clickhouse/
# 将默认日志目录路径从 /var/log/clickhouse-server/ 替换为 /data/clickhouse/logs
sudo sed -i 's/var\/lib\/clickhouse/data\/clickhouse\/data/g' /etc/clickhouse-server/config.xml
sudo sed -i 's/var\/log\/clickhouse-server/data\/clickhouse\/logs/g' /etc/clickhouse-server/config.xml#创建数据盘存储目录
sudo mkdir -p /data/clickhouse/data /data/clickhouse/logs
sudo mkdir -p /data/clickhouse/data /data/clickhouse/data #设置所有权（确保 clickhouse 用户可访问）
sudo chown -R clickhouse:clickhouse /data/clickhouse/data /data/clickhouse/logs# 迁移数据目录（若有数据）
sudo rsync -avz /var/lib/clickhouse/ /data/clickhouse/data/# 迁移日志目录（若有日志）
sudo rsync -avz /var/log/clickhouse-server/ /data/clickhouse/logs/

修改完成重启服务验证。

#重启服务
sudo service clickhouse-server start  # 或 systemctl start clickhouse-server#检查服务状态
sudo service clickhouse-server status

三、基准测试

3.1 测试数据集选择

这里选择“台湾历史天气数据集”，这个数据集包含过去128年的历史气象观测测量记录。每一行都是某个时间点和天气站的测量值。

气象数据集的来源包括中央气象局建立的气象站（站代码以C0、C1和4开头）以及属于农业委员会的农业气象站（站代码不包括上述提到的情况）：
StationId
MeasuredDate，观测时间
StnPres，站点气压
SeaPres，海平面气压
Td，露点温度
RH，相对湿度
其他可用元素

3.2 下载数据

一份适用于ClickHouse的预处理版本，已清理、重组和增强。该数据集涵盖1896年至2023年。
下载原始原始数据并转换为ClickHouse要求的格式。希望添加自己列的用户可以探索或完成自己的方法。

预处理数据
该数据集已从每行一个测量值重组为每个天气站ID和测量日期一行，例如：

StationId,MeasuredDate,StnPres,Tx,RH,WS,WD,WSGust,WDGust,Precp,GloblRad,TxSoil0cm,TxSoil5cm,TxSoil20cm,TxSoil50cm,TxSoil100cm,SeaPres,Td,PrecpHour,SunShine,TxSoil10cm,EvapA,Visb,UVI,Cloud Amount,TxSoil30cm,TxSoil200cm,TxSoil300cm,TxSoil500cm,VaporPressure
C0X100,2016-01-01 01:00:00,1022.1,16.1,72,1.1,8.0,,,,,,,,,,,,,,,,,,,,,,,
C0X100,2016-01-01 02:00:00,1021.6,16.0,73,1.2,358.0,,,,,,,,,,,,,,,,,,,,,,,
C0X100,2016-01-01 03:00:00,1021.3,15.8,74,1.5,353.0,,,,,,,,,,,,,,,,,,,,,,,
C0X100,2016-01-01 04:00:00,1021.2,15.8,74,1.7,8.0,,,,,,,,,,,,,,,,,,,,,,,

查询变得简单，并且确保结果表更少稀疏，某些元素为null，因为它们在该气象站不可用。

该数据集可在以下Google Cloud Storage位置找到。用户可以将数据集下载到本地文件系统（并使用ClickHouse客户端插入）或直接插入ClickHouse中（

下载方式如下：

wget https://storage.googleapis.com/taiwan-weather-observaiton-datasets/preprocessed_weather_daily_1896_2023.tar.gz# Option: Validate the checksum
md5sum preprocessed_weather_daily_1896_2023.tar.gz# Checksum should be equal to: 11b484f5bd9ddafec5cfb131eb2dd008tar -xzvf preprocessed_weather_daily_1896_2023.tar.gz
daily_weather_preprocessed_1896_2023.csv# Option: Validate the checksum
md5sum daily_weather_preprocessed_1896_2023.csv# Checksum should be equal to: 1132248c78195c43d93f843753881754

3.3 插入到ClickHouse中

从本地文件插入

可以通过以下方式从本地文件插入数据（来自ClickHouse客户端）：

INSERT INTO tw_weather_data FROM INFILE '/path/to/daily_weather_preprocessed_1896_2023.csv'

其中/path/to表示本地文件在磁盘上的具体用户路径。

插入数据后，示例响应输出如下：

Query id: 92376efe-9b04-4cc1-91af-421243cf9050Ok.131985329 rows in set. Elapsed: 145.285 sec. Processed 131.99 million rows, 9.97 GB (908.45 thousand rows/s., 68.65 MB/s.)
Peak memory usage: 566.46 MiB.

结果解读：

单表数据库的写入性能： 1.32亿行数据，耗时（145秒），90.8万行/秒，68.65 MB/s，峰值内存使用约 566 MB。

3.4 检查数据行和大小

1. 让我们看看插入了多少行：

:) SELECT formatReadableQuantity(count())
FROM tw_weather_data;SELECT formatReadableQuantity(count())
FROM tw_weather_dataQuery id: 391c0b8f-bd91-4c54-b95c-2655861394f2┌─formatReadab⋯ty(count())─┐
1. │ 131.99 million           │└──────────────────────────┘1 row in set. Elapsed: 0.008 sec.

结果解读：

count()查询性能：1.32亿行数据，耗时：8毫秒

2. 让我们查看该表使用了多少磁盘空间：

:) SELECTformatReadableSize(sum(bytes)) AS disk_size,formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.parts
WHERE (`table` = 'tw_weather_data') AND activeSELECTformatReadableSize(sum(bytes)) AS disk_size,formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.parts
WHERE (`table` = 'tw_weather_data') AND activeQuery id: b425600e-8a09-4036-9e26-a18a51be015c┌─disk_size─┬─uncompressed_size─┐
1. │ 2.15 GiB  │ 32.94 GiB         │└───────────┴───────────────────┘1 row in set. Elapsed: 0.006 sec.

结果解读：

存储压缩性能：原始数据大小：32.94 GB，实际磁盘占用：2.15 GB，压缩比 ≈ 15:1

3.5 检索特定年份每个天气站的最高露点温度

 :) SELECTStationId,max(Td) AS max_td
FROM tw_weather_data
WHERE (year(MeasuredDate) = 2023) AND (Td IS NOT NULL)
GROUP BY StationIdSELECTStationId,max(Td) AS max_td
FROM tw_weather_data
WHERE (year(MeasuredDate) = 2023) AND (Td IS NOT NULL)
GROUP BY StationIdQuery id: d0055318-bbc3-4076-848f-f5f9cbfcdd56┌─StationId─┬─max_td─┐1. │ 466940    │      1 │2. │ 467300    │      1 │3. │ 467540    │      1 │4. │ 467490    │      1 │5. │ 467080    │      1 │6. │ 466910    │      1 │7. │ 467660    │      1 │8. │ 467270    │      1 │9. │ 467350    │      1 │
10. │ 467571    │      1 │
11. │ 466920    │      1 │
12. │ 467650    │      1 │
13. │ 467550    │      1 │
14. │ 467480    │      1 │
15. │ 467610    │      1 │
16. │ 467050    │      1 │
17. │ 467590    │      1 │
18. │ 466990    │      1 │
19. │ 467060    │      1 │
20. │ 466950    │      1 │
21. │ 467620    │      1 │
22. │ 467990    │      1 │
23. │ 466930    │      1 │
24. │ 467110    │      1 │
25. │ 466881    │      1 │
26. │ 467410    │      1 │
27. │ 467441    │      1 │
28. │ 467420    │      1 │
29. │ 467530    │      1 │
30. │ 466900    │      1 │└───────────┴────────┘30 rows in set. Elapsed: 0.051 sec. Processed 6.38 million rows, 203.30 MB (124.56 million rows/s., 3.97 GB/s.)
Peak memory usage: 3.86 MiB.

结果解读：

查询返回了30个气象站（StationId）在2023年的最大露点温度（max_td），耗时：0.051秒（极快），处理数据量：638万行，203.30 MB，吞吐量：1.24亿行/秒，3.97 GB/s，内存占用：仅3.86 MB

3.6 以特定时间范围、字段和天气站raw数据获取

:) SELECTStnPres,SeaPres,Tx,Td,RH,WS,WD,WSGust,WDGust,Precp,PrecpHour
FROM tw_weather_data
WHERE (StationId = 'C0UB10') AND (MeasuredDate >= '2023-12-23') AND (MeasuredDate < '2023-12-24')
ORDER BY MeasuredDate ASC
LIMIT 10SELECTStnPres,SeaPres,Tx,Td,RH,WS,WD,WSGust,WDGust,Precp,PrecpHour
FROM tw_weather_data
WHERE (StationId = 'C0UB10') AND (MeasuredDate >= '2023-12-23') AND (MeasuredDate < '2023-12-24')
ORDER BY MeasuredDate ASC
LIMIT 10Query id: 3a9297c1-ea76-4058-ada0-b2e9dda93ebd┌─StnPres─┬─SeaPres─┬───Tx─┬───Td─┬─RH─┬──WS─┬──WD─┬─WSGust─┬─WDGust─┬─Precp─┬─PrecpHour─┐1. │  1029.5 │    ᴺᵁᴸᴸ │ 11.8 │ ᴺᵁᴸᴸ │ 78 │ 2.7 │ 271 │    5.5 │    275 │ -99.8 │     -99.8 │2. │  1029.8 │    ᴺᵁᴸᴸ │ 12.3 │ ᴺᵁᴸᴸ │ 78 │ 2.7 │ 289 │    5.5 │    308 │ -99.8 │     -99.8 │3. │  1028.6 │    ᴺᵁᴸᴸ │ 12.3 │ ᴺᵁᴸᴸ │ 79 │ 2.3 │ 251 │    6.1 │    289 │ -99.8 │     -99.8 │4. │  1028.2 │    ᴺᵁᴸᴸ │   13 │ ᴺᵁᴸᴸ │ 75 │ 4.3 │ 312 │    7.5 │    316 │ -99.8 │     -99.8 │5. │  1027.8 │    ᴺᵁᴸᴸ │ 11.1 │ ᴺᵁᴸᴸ │ 89 │ 7.1 │ 310 │   11.6 │    322 │ -99.8 │     -99.8 │6. │  1027.8 │    ᴺᵁᴸᴸ │ 11.6 │ ᴺᵁᴸᴸ │ 90 │ 3.1 │ 269 │   10.7 │    295 │ -99.8 │     -99.8 │7. │  1027.9 │    ᴺᵁᴸᴸ │ 12.3 │ ᴺᵁᴸᴸ │ 89 │ 4.7 │ 296 │    8.1 │    310 │ -99.8 │     -99.8 │8. │  1028.2 │    ᴺᵁᴸᴸ │ 12.2 │ ᴺᵁᴸᴸ │ 94 │ 2.5 │ 246 │    7.1 │    283 │ -99.8 │     -99.8 │9. │  1028.4 │    ᴺᵁᴸᴸ │ 12.5 │ ᴺᵁᴸᴸ │ 94 │ 3.1 │ 265 │    4.8 │    297 │ -99.8 │     -99.8 │
10. │  1028.3 │    ᴺᵁᴸᴸ │ 13.6 │ ᴺᵁᴸᴸ │ 91 │ 1.2 │ 273 │    4.4 │    256 │ -99.8 │     -99.8 │└─────────┴─────────┴──────┴──────┴────┴─────┴─────┴────────┴────────┴───────┴───────────┘10 rows in set. Elapsed: 0.057 sec. Processed 68.00 thousand rows, 2.42 MB (1.20 million rows/s., 42.77 MB/s.)
Peak memory usage: 4.10 MiB.

结果解读：

台湾气象站 C0UB10 在 2023-12-23 全天的气象数据：返回 10行符合条件的数据（台湾气象站 C0UB10 2023-12-23的全天记录），耗时：0.057秒（极快），处理数据量：6.8万行，2.42 MB，吞吐量：120万行/秒，内存占用：仅 4.10 MB

四、测试结论

a.极致查询性能
- 简单聚合查询（如COUNT）耗时毫秒级（如COUNT 1.32亿行仅 8 毫秒）。
- 复杂条件查询（如多字段过滤+分组）耗时 5-10 毫秒，处理速度达百万至千万行/秒。
b.高效存储与压缩
- 原始数据压缩比约15:1（如32.94GB数据压缩后仅2.15GB），大幅降低存储成本。
- 列式存储+高效索引设计，支持快速检索和过滤。
c.高并发与稳定性
- 单机可支撑高吞吐查询（如 120万行/秒），且内存占用低（仅数MB)。
d.适用场景适配
- 特别适合时序数据（如气象、物联网）和大规模分析场景，满足实时性需求。