clickhouse时序数据应用

发表于 | 分类于

应用场景

使用场景

  • 绝大多数请求都是用于读访问的，数据一次写入，多次查询
  • 数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
  • 数据只是添加到数据库，没有必要修改
  • 读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
  • 表很“宽”，即表中包含大量的列
  • 查询频率相对较低（通常每台服务器每秒查询数百次或更少）
  • 对于简单查询，允许大约50毫秒的延迟
  • 列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
  • 在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
  • 不需要事务
  • 数据一致性要求较低

业务场景

流量分析、精准营销、广告实时竞价、BI 报表分析、用户行为分析、日志分析、实时大屏等。

  • 数据仓库：ClickHouse 可以快速地处理大量的数据，支持高并发查询和复杂的聚合分析，是数据仓库和数据分析的首选工具。
  • 实时数据分析：ClickHouse 支持实时数据分析和实时查询，可以快速地处理实时数据流，是实时数据分析和实时监控的首选工具。
  • 时序数据存储：ClickHouse 支持时序数据存储和时序数据分析，可以快速地处理时间序列数据，是时序数据存储和时序数据分析的首选工具。
  • 数据可视化：ClickHouse 支持数据可视化和报表生成，可以快速地生成各种类型的报表和图表，是数据可视化和报表生成的首选工具。

缺点

  • 没有完整的事务支持；
  • 不支持分词查询；
  • 缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据；
  • 不擅长 join 操作；
  • 不支持高并发，官方建议qps为100。原因： ClickHouse 将数据划分为多个 partition，每个 partition 再进一步划分为多个 index_granularity(索引粒度)，然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下， 单条 Query 就能利用整机所有 CPU。 极致的并行处理能力，极大的降低了查询延时。所以，ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务， ClickHouse 并不是强项。

优点

  • 查询快

    利用存储引擎的特殊设计充分减少磁盘I/O对查询速度的影响。从用户提交一条SQL语句进行查询到最终输出结果的过程中，大量的时间是消耗在了磁盘I/O上，在很多情况下，I/O所占用的时间可以达到整个时间的90%以上。对存储引擎磁盘I/O的优化可以获得非常大的收益。ClickHouse的存储引擎设计中大量优化的目的也是为了减少磁盘I/O。
    • 列存
    • 预排序：在实现范围查找时，可以将大量的随机读转换为顺序读，从而有效提高I/O效率，降低范围查询时的I/O时间；
    • 数据压缩：可以减少读取和写入的数据量，从而减少I/O时间。
    • 向量化引擎，尽可能多地使用内置函数
    • 尽可能避免Join操作，可以用Spark替代
    • ClickHouse 会在内存中进行 GROUP BY，并且使用 HashTable 装载数据。
    • 索引
    • 多线程和分布式

    • 算法：ClickHouse针对不同的应用场景，选择不同的算法：

      对于常量字符串查询，使用volnitsky算法
      对于非常量字符串，使用CPU的向量化执行SIMD，进行暴力优化
      对于字符串正则匹配，使用re2和hyperscan算法

  • 写入性能好

    • 列存
    • 数据压缩：能够有效地减少数据的存储空间，从而提高写入性能
    • 分布式架构
    • 多线程写入：ClickHouse采用多线程写入机制，能够同时处理多个写入请求，从而提高写入性能。

    • LSM-Tree存储结构。
      先明白一个测试数据：磁盘顺序读写和随机读写的性能差距大概是1千到5千倍之间
      连续 I/O 顺序读写，磁头几乎不用换道，或者换道的时间很短，性能很高，比如0.03 * 2000 MB /s
      随机 I/O 随机读写，会导致磁头不停地换道，造成效率的极大降低，0.03MB/s

      ClickHouse中的MergeTree也是类LSM树的思想，日志结构合并树，但不是树，而是利用磁盘顺序读写能力，实现一个多层读写的存储结构 是一种分层，有序，面向磁盘的数据结构，核心思想是利用了磁盘批量的顺序写要远比随机写性能高出很多 大大提升了数据的写入能力。
充分利用了磁盘顺序写的特性，实现高吞吐写能力，数据写入后定期在后台Compaction。在数据导入时全部是顺序append写，在后台合并时也是多个段merge sort后顺序写回磁盘。官方公开benchmark测试显示能够达到50MB-200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度。

存储引擎

MergeTree

MergeTree 表引擎主要用于海量数据分析，支持数据分区、主键索引、稀疏索引和数据 TTL 等。

  • 列式存储：只读取需要的列，节省 IO 和 CPU 资源。
  • 数据分区：按照日期或其他条件将数据分割成多个部分，方便管理和查询。
  • 稀疏主键索引：按照主键或排序键对数据进行排序和索引，加速范围查询。
  • 次级跳过索引：根据列的最小值和最大值等统计信息跳过不符合条件的数据，进一步提高查询效率。
  • 数据合并：后台定期将多个小的数据部分合并成一个大的数据部分，减少数据冗余和碎片

MergeTree 引擎还有一些变种，如 ReplicatedMergeTree、AggregatingMergeTree 和 SummingMergeTree 等，它们在基本的 MergeTree 功能上增加了数据复制、数据聚合、数据求和等特性。

索引

一级索引

一级索引是稀疏索引，意思就是说：每一段数据生成一条索引记录，而不是每一条数据都生成索引， 如果是每一条数据都生成索引，则是稠密索引。用一个形象的例子来说明：如果把MergeTree比作一本书，那么稀疏索引就好比是这本书的一级章节目录。一级章节目录不会具体对应到每个字的位置，只会记录每个章节的起始页码。

MergeTree 的主键使用 PRIMARY KEY 定义，待主键定义之后，MergeTree 会依据 index_granularity 间隔（默认 8192 行），为数据表生成一级索引并保存至 primary.idx 文件内。
稀疏索引的优势是显而易见的，它仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势越为明显。以默认的索引粒度（8192）为例，MergeTree只需要12208行索引标记就能为1亿行数据记录提供索引。由于稀疏索引占用空间小，所以primary.idx内的索引数据常驻内存，取用速度自然极快。

在 ClickHouse 中，一级索引常驻内存。总的来说：一级索引和标记文件一一对齐，两个 索引标记之间的数据，就是一个数据区间，在数据文件中，这个数据区间的所有数据，生成一个压缩数据块。每列压缩数据文件，存储每一列的数据，每一列字段都有独立的数据文件，每一列都有对应的标记文件，保存了列压缩文件中数据的偏移量信息，与稀疏索引对齐，又与压缩文件对应，建立了稀疏索引与数据文件的映射关系。不能常驻内存，使用LRU缓存策略加快其取用速度。

需要注意的是：ClickHouse 的主键索引与 MySQL 等数据库不同，它并不用于去重，即便 primary key 相同的行，也可以同时存在于数据库中。 要想实现去重效果，需要结合具体的表引擎 ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree 实现。

二级索引

二级索引：又称之为跳数索引。目的和一级索引一样，是为了减少待搜寻的数据的范围。

跳数索引的默认是关闭的，需要通过参数来开启，索引生成粒度由 granularity 控制，如果生成了二级索引，则会在分区目录下生成额外的：skp_idx_[Column].idx 与 skp_idx_[Column].mrk 文件。

跳数索引的生成规则：按照特定规则每隔 granularity 个 index_granularity 条数据，就会生成一条跳数索引。比如 minmax 跳数索引，生成的是：granularity 个 index_granularity 条数据内的最大值最小值生成一条索引，如果将来需要针对构建二级索引的这个字段求最大值最小值，则可以帮助提高效率。

跳数索引一共支持四种类型：minmax(最大最小)、set(去重集合)、 ngrambf_v1（ngram 分词布隆索引）和 tokenbf_v1（标点符号分词布隆索引），一张数据表支持同时声明多个跳数索引。

实际案例

笔者在一次业务开发中，使用了 clickhouse 存储设备的时序数据，下面通过实际案例测试了一下 clickhouse 在此业务场景下的性能、压缩率。

集群配置

集群高可用
内核版本：22.8.5.1
计算节点类型：标准型
计算节点规格：32核128GB
计算节点数量：2
计算节点存储：增强型SSD云硬盘500GB
ZK节点规格：4核16GB
ZK节点存储：增强型SSD云硬盘100GB
ZK节点数量：3

测试数据写入

笔者自己写了一个伪造数据的脚本，以 100 万台设备为维度，总共写入了 20 亿条数据，平均每条数据将近 1k。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE DATABASE IF NOT EXISTS event_message ON CLUSTER default_cluster;

CREATE TABLE event_message.event ON CLUSTER default_cluster (
    `created_at` DateTime DEFAULT now() COMMENT '创建时间' CODEC(Delta(1), ZSTD(1)),
    `ts` DateTime64(3, 'Asia/Shanghai') COMMENT '事件上报时间' CODEC(Delta(8), ZSTD(1)),
    `uuid` String,
    `event_id` String COMMENT '事件id' CODEC(ZSTD(1)),
    `app_id` UInt64,
    `user_id` UInt64,
    `type` UInt8 COMMENT '事件类型 1告警' CODEC(ZSTD(1)),
    `event_type` Int64 COMMENT '上报数据内容中的事件类型' CODEC(ZSTD(1)),
    `event_end` UInt64 CODEC(Delta(1), ZSTD(1)),
    `image_url` String CODEC(ZSTD(1)),
    `video_url` String CODEC(ZSTD(1)),
    `video_start` UInt64 CODEC(Delta(1), ZSTD(1)),
    `video_end` UInt64 CODEC(Delta(1), ZSTD(1)),
    `status` UInt8 COMMENT '状态 1未读 2已读 3删除' CODEC(ZSTD(1)),
    `channel` Nullable(Int64) COMMENT '通道号'
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/event', '{replica}')
PARTITION BY toYYYYMMDD(ts)
PRIMARY KEY (uuid, ts) ORDER BY (uuid, ts, event_type) TTL toDateTime(ts) + toIntervalMonth(3) SETTINGS index_granularity = 2048
  • 数据示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ed069b3366a6 :) select * from event_message.event limit 1\G

Row 1:
──────
created_at:  2024-01-11 17:43:39.689
ts:          2023-12-13 14:38:25.003
uuid:        3301000000189xxx
event_id:    1702449505
app_id:      9
user_id:     114928
type:        1
event_type:  3
event_end:   1702449804
image_url:   http://s-cn-example-xxx.com/xxx...
video_url:   http://s-cn-example-xxx.com/xxx...
video_start: 1702449501
video_end:   1702449505
status:      1
channel:     0
  • 批量异步写入
1
2
3
4
5
[ops@VM-0-45-tencentos event]$ ./ck_event_tool -addr "10.32.32.10:9000" -file ./d3301000000044702.csv -num 20000
table: event_idx_20, sql len: 17415225 byte, AsyncInsert took 116454 microseconds
table: event_idx_20, sql len: 17415162 byte, AsyncInsert took 51526 microseconds
table: event_idx_20, sql len: 17415125 byte, AsyncInsert took 46664 microseconds
table: event_idx_20, sql len: 17415135 byte, AsyncInsert took 52592 microseconds

写入速度能达到 200M/s，这点已经满足业务数据写入的需求了。

监控

基准测试

SQL 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT *
FROM
(
    SELECT
        ts,
        argMax(event_id, event_id) AS event_id,
        argMax(app_id, app_id) AS app_id,
        argMax(user_id, user_id) AS user_id,
        argMax(type, type) AS type,
        argMax(event_type, event_type) AS event_type,
        argMax(event_end, event_end) AS event_end,
        argMax(image_url, image_url) AS image_url,
        argMax(video_url, video_url) AS video_url,
        argMax(video_start, video_start) AS video_start,
        argMax(video_end, video_end) AS video_end,
        argMax(status, created_at) AS status,
        argMax(channel, channel) AS channel
    FROM event_message.event
    WHERE ((ts >= '2024-01-24 00:00:00') AND (ts <= '2024-01-24 23:59:59')) AND (uuid = '3301000000044703')
    GROUP BY
        ts,
        uuid
    HAVING (status != 3) AND (image_url != '')
)
ORDER BY ts DESC
LIMIT 10

10 rows in set. Elapsed: 0.070 sec. Processed 32.64 thousand rows, 22.19 MB (468.13 thousand rows/s., 318.19 MB/s.)

SQL 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT count(*)
FROM
(
    SELECT
        ts,
        argMax(event_type, event_type) AS event_type,
        argMax(image_url, image_url) AS image_url,
        argMax(channel, channel) AS channel,
        argMax(status, created_at) AS status
    FROM event_message.event
    WHERE ((ts >= '2024-01-24 00:00:00') AND (ts <= '2024-01-24 23:59:59')) AND (uuid = '3301000000044703')
    GROUP BY
        ts,
        uuid
    HAVING status IN (1,2) AND image_url != ''
)

Query id: 5726ca46-564f-4ee3-bc6c-306b97b02669

┌─count()─┐
│    5549 │
└─────────┘

1 rows in set. Elapsed: 0.036 sec. Processed 32.64 thousand rows, 10.47 MB (899.62 thousand rows/s., 288.52 MB/s.)

SQL 3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT *
FROM
(
    SELECT
        ts,
        argMax(event_type, event_type) AS event_type,
        argMax(image_url, image_url) AS image_url,
        argMax(status, created_at) AS status
    FROM event_message.event
    WHERE uuid = '3301000000044704'
    GROUP BY
        ts,
        uuid
    HAVING (status != 3) AND (image_url != '')
)
ORDER BY ts DESC
LIMIT 1

1 rows in set. Elapsed: 0.114 sec. Processed 350.34 thousand rows, 210.66 MB (3.08 million rows/s., 1.85 GB/s.)

SQL 4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT
    count(*) AS total,
    toDate(ts) AS d,
    status
FROM
(
    SELECT
        ts,
        argMax(event_type, event_type) AS event_type,
        argMax(image_url, image_url) AS image_url,
        argMax(status, created_at) AS status
    FROM event_message.event_idx_20
    WHERE ((ts >= '2024-01-24 00:00:00') AND (ts <= '2024-01-27 23:59:59')) AND (uuid = '3301000000044703')
    GROUP BY
        ts,
        uuid
    HAVING (status != 3) AND (image_url != '')
)
GROUP BY
    d,
    status

Query id: 28bdb597-9d81-4db6-8baa-cd5db4d11dea

┌─total─┬──────────d─┬─status─┐
│     1 │ 2024-01-27 │      2 │
│  5549 │ 2024-01-24 │      1 │
│    73 │ 2024-01-27 │      1 │
│    93 │ 2024-01-26 │      1 │
│   183 │ 2024-01-25 │      1 │
└───────┴────────────┴────────┘

5 rows in set. Elapsed: 0.043 sec. Processed 26.63 thousand rows, 6.19 MB (617.89 thousand rows/s., 143.74 MB/s.)

基准测试结果

1
docker exec -it ck-server ClickHouse-benchmark -h 10.32.32.10 --user "" --password "" -c 50 --query ""
1分片2节点、32核128GB、SSD云硬盘500GB

index_granularity = 2048

  • 150并发
SQL P50（秒） P70（秒） P80（秒） P90（秒） P95（秒） P99（秒） P99.9（秒）
SQL 1 0.146 0.164 0.176 0.192 0.207 0.238 0.280
SQL 2 0.079 0.092 0.100 0.113 0.124 0.147 0.177
SQL 3 0.209 0.303 0.357 0.421 0.496 0.661 0.700
SQL 4 0.135 0.155 0.168 0.186 0.204 0.241 0.280
  • 200并发
SQL P50（秒） P70（秒） P80（秒） P90（秒） P95（秒） P99（秒） P99.9（秒）
SQL 1 0.198 0.223 0.239 0.264 0.285 0.330 0.388
SQL 2 0.107 0.126 0.138 0.156 0.173 0.204 0.233
SQL 3 0.395 0.462 0.491 0.544 0.580 0.627 0.696
SQL 4 0.180 0.211 0.231 0.262 0.300 0.340 0.432
  • 250并发
SQL P50（秒） P70（秒） P80（秒） P90（秒） P95（秒） P99（秒） P99.9（秒）
SQL 1 0.249 0.281 0.302 0.334 0.361 0.416 0.479
SQL 2 0.136 0.161 0.179 0.203 0.224 0.271 0.330
SQL 3 0.543 0.669 0.727 0.822 0.855 0.909 1.080
SQL 4 0.238 0.259 0.295 0.353 0.370 0.444 0.572

附录

  • SQL 1
    10 rows in set. Elapsed: 0.044 sec. Processed 10.24 thousand rows, 7.28 MB (232.86 thousand rows/s., 165.60 MB/s.)
    • 150 并发
      10.32.32.10:9000, queries: 2893, QPS: 855.413, RPS: 8759429.869, MiB/s: 5940.758, result RPS: 8554.131, result MiB/s: 7.089.
  • SQL 2
    1 rows in set. Elapsed: 0.029 sec. Processed 10.24 thousand rows, 3.45 MB (357.13 thousand rows/s., 120.16 MB/s.)
    • 200 并发
      10.32.32.10:9000, queries: 7049, QPS: 1639.300, RPS: 16786436.834, MiB/s: 5386.267, result RPS: 1639.300, result MiB/s: 0.013.
  • SQL 4
    5 rows in set. Elapsed: 0.049 sec. Processed 26.63 thousand rows, 6.19 MB (538.26 thousand rows/s., 125.22 MB/s.)
    • 200 并发
      10.32.32.10:9000, queries: 5020, QPS: 946.233, RPS: 25196304.941, MiB/s: 5589.924, result RPS: 4731.167, result MiB/s: 0.050.

常见问题

  • 集群高可用副本同步，需要使用 ReplicatedMergeTree 引擎，否则不会同步消息
  • 并发查询限制，默认 100，并发越高查询效率越慢，相关参数：config.xml -> max_concurrent_queries
    • 目前调整至 300
  • 查询内存限制，DB::Exception: Memory limit 相关参数：users.xml -> max_memory_usage
    • 目前调整至 25G
  • 根据业务情况调整主键顺序以及主键索引颗粒度
  • 主键顺序按字段基数从小到大排序有利于压缩率
  • 主键索引顺序问题需要通过多主键索引进行调优，原理都是基于附加表进行数据查询