ClickHouse 是一个用于联机分析( OLAP )的列式数据库管理系统( DBMS )。列式数据库更适合于 OLAP场景(对于大多数查询而言，处理速度至少提高了 100 倍)，下面通过图片更有利于直观理解：

图片来源：

https://ClickHouse.com/docs/assets/images/row-oriented-d515facb5bffb48cbd09dc7d064c8816.gif#

图片来源：

https://ClickHouse.com/docs/assets/images/column-oriented-b992c529fa4085b63b57452fbbeb27ba.gif#

同时 ClickHouse 基于列存，还提供了很高的压缩比。压缩算法 lz4 可以到 1:4 ，zstd 可以到 1:10 。本文主要是介绍我们在 ClickHouse 上面的实践，对于 ClickHouse 本身的特性及相关介绍就不在这里赘述了。

ClickHouse 创建表时是支持表级数据 TTL 策略的，TTL 策略可以支持数据过期自动合并(Compaction)后删除，当然也支持自动合并后移动到其他Disk或Volumn。日志平台的多级存储就是利用了存储策略，但由于踩了TTL的一个坑，我们最终放弃表级TTL设置，改成搬迁表part的任务调度实现TTL策略，后面会说到。

  <path>/data1/ClickHouse/data/</path>  --为了便于查找，我们建议在默认的存储路径下方添加存储策略    <storage_configuration>            <disks>                <hot>                    <path>/data1/ClickHouse/hot/</path>  --这里注意，使用存储策略后，建议任何数据路径之间不要有子集关系                </hot>                <cold>                    <path>/data2/ClickHouse/cold/</path>                </cold>            </disks>            <policies>                <ttl>                    <volumes>                        <hot>                           <disk>hot</disk>                        </hot>                        <cold>                           <disk>cold</disk>                        </cold>                    </volumes>                </ttl>            </policies>    </storage_configuration>

2）创建表

示例：

CREATE TABLE db_rdsauditlog_local ON CLUSTER auditlog(   `check_rows` Int64,   `client_ip` String,   `db` String,   `fail` Int64,   `instance_id` String,   `latency` Int64,   `origin_time` DateTime('Asia/Shanghai'),   `return_rows` Int64,   `sql` String,   `thread_id` Int64,   `update_rows` Int64,   `user` String,   `tables` Array(String),   `sqlhash` String,   `sqlfingerprint` String,   `sqltype` String,   INDEX instance_sqltype (instance_id, sqltype) TYPE set(100) GRANULARITY 5,   INDEX origintime_sqlhash (instance_id,sqlhash) TYPE set(100) GRANULARITY 5,   INDEX origintime_instance_clientip(instance_id, client_ip) TYPE set(100) GRANULARITY 5)ENGINE = MergeTreePARTITION BY toYYYYMMDD(origin_time)ORDER BY origin_timeTTL origin_time + INTERVAL 6 hour TO DISK 'cold'SETTINGS storage_policy = 'ttl',index_granularity = 8192;
Create table db_rdsauditlog on cluster auditlog as db_rdsauditlog_local ENGINE=Distributed(auditlog, default, db_rdsauditlog_local, rand());

创建表级 TTL ，要想使用分级冷热存储，必须要指定存储策略，才能使用到 Disk ，为什么要指定存储策略，我的理解是TTL可以指定到 Disk 和 Volumn 级，但是 Volumn 级别只能定义在存储策略里。

具体阅读：

https://ClickHouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl

PARTITION BY--分区键，表级可选的参数。在大多数情况下，其实不需要分区键，同时即便使用了分区键，不太建议使用比月更细粒度的分区键，分区不会加速查询（与 ORDER BY 表达式相反）。你永远不应该使用太细化的分区。不要按客户端标识符或名称对数据进行分区，而是将客户端标识符或名称作为 ORDER BY 表达式中的第一列(官方文档这句话还是要好好牢记，一般我们只建议时间字段做分区)。

以上是官方文档的原话，但是实际中我们需要根据具体情况是可以创建甚至到小时级别粒度的分区，还是那句话，看需求，用不好会有副作用，后面会提到。

业务背景中除了提到需要很高的写入能力，很高的压缩比，实际需求调研中，基础架构同学还提出各个业务域都有自己的日志时间查询范围需求(ES作为存储时，研发甚至需要能够提供天级的日志保留时间)，比如7天，30天，90天，甚至更长等不同范围，因为冷数据查询频次较低，可以使用更低廉的存储介质，另外有一些数据保留一段时间之后不会被查但需要满足合规要求，再长的日志就可以删除。基础架构希望DBA能够帮忙在ClickHouse的存储上对数据保留时间提供一些建议，尽最大可能降低存储成本。

总结一下就是如下需求：

根据这些需求，DBA对这些需求提供了如下方案：

下面一一讲解。

如业务需求，我们需要解决三个问题：

1）方案1：分区策略PARTITION BY (application,environment,toYYYYMMDD(log_time))

最初我们期望使用分区策略，用应用名称+环境+按天分区的组合分区策略来满足要求，即PARTITION BY (application,environment,toYYYYMMDD(log_time))。这样每个应用每个环境有独立的分区，业务研发还可以很灵活的随意修改天级的日志保留时间，这样每个分区可以根据保留策略独立的移动到不同的磁盘。理想很丰满，现实很骨感。虽然这个方案可以很好的满足需求，但是真正写入的时候，我们遇到了问题，由于线上应用在较多，且还在呈上升趋势，每个应用还可能有多个环境，即便每个应用每天只写入到一个天分区，实际测试过程中，我们发现写入性能严重不足，写入很慢，同时碰到多个参数值不够报错的问题。

意思是一次插入的数据块涉及到的分区数量。由于日志是消费的kafka一个某个topic，一个topic可能有几百个应用，每个应用还有多个环境，即便写入天级分区，分区是物理上隔离成不同的目录，一次写入也会被ClickHouse拆分成几千个不同partition中的part，一次insert涉及到的partition数爆炸，提示插入数据的part数量超过该参数值(too many partitions for single insert blocks)，远超参数设置，100根本不够，调整到1w还是报错。写入量不是很大的情况下，是可以适当调整该参数，但测试环境日志产生的实在太碎，上调该参数很快就有too many parts的报错，too many parts的出现就是ClickHouse合并跟不上写入，直接拒绝写入数据，这就是下面的参数max_parts_in_total值不够。

测试环境中由于有一些应用数据较少，导致攒批数据比较难，加上若代码在批次写入数据的地方处理不好，很容易出现频繁写入较少数据行的part，加上应用数，环境较多，导致写入的数据较碎，一个表内active的part数据非常容易超过10w(可以查看system.parts表中状态为active的数据个数)，多次报错too many parts in total的错误。这里可以适当提高background_pool_size的值提升合并速度，但是对于大量较碎的part也是杯水车薪，不能解决根本问题。最终我们放弃方案1，选择方案2。

2）方案2：分区策略PARTITION BY (toDate(log_time),log_save_time,oss_save_time)

方案1的问题最主要还是分区字段设置的问题，我们决定在满足写入能力的前提下提供一定能力的日志保留时间。最终将分区字段去掉应用名称application字段(为了保证查询速度，我们将application字段放入到order by的第一个字段。

由于三级保留策略，最开始想到用多个表即我们定义日志保留时间范围的固定选项(7d,30d,90d,180d)，日志保留时间需要研发做一些妥协，不能随意修改保留时间，根据这几个选项创建对应的表，这样能发挥写入的最佳性能。但是会有一个问题，就是当研发提工单修改应用的保留时间后，时间调整会导致日志落入不同的表，这样代码中查询语句不变的情况下会出现变更保留时间后查不到历史数据的问题，虽然可以代码做一定的路由规则，但很繁琐，不够友好，放弃该方案。

如何解决？经过我们讨论，我们想到一个方法，表中添加两个字段log_save_time，oss_save_time，这两个字段是int类型，将分区字段调整为PARTITION BY (toDate(log_time), log_save_time, oss_save_time)。log_save_time为每条日志在hot盘中保留的时间，超出会被任务移动到cold盘；oss_save_time为每条日志在cold盘中保留的时间，超出会被移动到arch盘(这里的移动任务会在下面介绍到)。任务每天会查询system.parts表，查看分区字段中三个字段对比，即toDate(log_time)和当前日期比较，差值大于分区中log_save_time的值，则移动该分区到cold盘，差值大于oss_save_time则移动到arch盘。这样如果应用日志保留策略的元数据信息修改，新产生的日志数据这两个字段也会写入新的保留策略值，新数据会落到不同的分区中。那么如何实现？进入问题2。

解决了第一个问题，那么日志怎么根据分区设定做过期移动？

1）方案1：使用表级TTL设置

最开始我们想到在表的创建语句中使用表级TTL设置：

PARTITION BY (toDate(log_time), log_save_time, oss_save_time)ORDER BY (application, environment, log_time, ip, file_offset)TTL origin_time + INTERVAL 24 hour TO DISK 'cold' --类似这样，SETTINGS allow_nullable_key = 1, storage_policy = 'ttl', index_granularity = 8192

优点是利用ClickHouse自身的能力做数据的搬迁，实现过期数据迁移到冷存储介质。

但会有一个问题，就是会导致前面提到的会根据多个可选时间范围创建多个对应的表。

另外还有一个坑就是，表级TTL一旦要修改TTL的保留时间，ClickHouse会reload表的所有part目录，导致IO Util打满，集群无法响应，这个很坑，目前还没有好的办法解决，所以放弃该方案。

2）方案2：开发调度任务，手动移动分区数据

结合问题1，最终我们选择这样创建表结构(只作为 Demo 参考，并非业务真实情况)，如下：

CREATE TABLE dw_log.tb_logs_local(    `application` String,    `environment` String,    `ip` String,    `filename` String,    `keys` Array(Nullable(String)),    `values_string` Array(Nullable(String)),    `values_number` Array(Nullable(Float64)),    `file_offset` Nullable(UInt32),    `message` String CODEC(ZSTD(1)),    `log_type` String,    `log_time` DateTime64(3),    `log_level` String,    `trace_id` String,    `pid` Int64,    `endpoint` String,    `log_save_time` Int32,    `oss_save_time` Int32,    `meta_size` Int64,    INDEX meta_size meta_size TYPE SET(100) GRANULARITY 2,    INDEX application application TYPE SET(100) GRANULARITY 2,    INDEX environment environment TYPE SET(100) GRANULARITY 2,    INDEX ip ip TYPE SET(100) GRANULARITY 2,    INDEX idx_message message TYPE tokenbf_v1(512, 2, 0) GRANULARITY 2,    INDEX trace_id trace_id TYPE SET(100) GRANULARITY 2,    INDEX log_level log_level TYPE SET(10) GRANULARITY 2,    INDEX pid pid TYPE SET(100) GRANULARITY 2,    INDEX idx_endpoint endpoint TYPE tokenbf_v1(512, 2, 0) GRANULARITY 2,    INDEX logtime log_time TYPE minmax GRANULARITY 2)ENGINE = MergeTreePARTITION BY (toDate(log_time), log_save_time, oss_save_time)ORDER BY (application, environment, log_time, ip, file_offset)SETTINGS allow_nullable_key = 1, storage_policy = 'ttl', index_granularity = 8192

日志平台会起一个调度任务，同时维护应用和(log_save_time,oss_save_time)的对应关系。每天根据该表的信息做应用对应日志分区的搬迁动作。

alter table dw_log.tb_logs_local on cluster default MOVE PARTITION XXX to disk 'cold'

但这里也有一个小问题就是当研发需要修改日志保留时间时，比如保留时间调大，则新的数据会落到新的分区里，这样之前的分区会因为匹配规则原因被提前删除，比如 7 天调整到 30 天，那么由于之前保留时间在分区中还是7这个值，到了第7天，之前的分区已经满足删除策略会被删除。虽然改成 30 天保留策略，依然会出现有7天日志查不到的情况，当然时间往后推移7天后还是能查到完整的 30 天日志。反之保留时间调小，比如从 30 调整到 7 ，会出现最长有 23 天的日志被持续保留，空间没有及时释放。时间推移 23 天后空间释放。这些问题不要紧，可以容忍，对比多个表的方案，该方案利大于弊，最终选择该方案。

解决了过期策略，表结构的设计后，前面提到的arch磁盘来存储基本不查的数据，使用低存储成本介质来降低成本，我们首先想到的就是能不能使用OSS？答案是可以，同时我们查看过费用，同等容量的 OSS 成本仅是 ESSD PL0 的三分之一，这无疑可以大幅降低存储费用。但怎么用最优，需要调研+测试。

1）方案1：ClickHouse +  JuiceFS + OSS

JuiceFS主要功能就是将 S3 转成文件系统挂载使用，且在多家知名互联网公司都有上线案例( 某海外电商平台的公开技术分享文章就提到了基于 JuiceFS 实现的 ClickHouse 冷热分离存储)，因此我们基于这些信息开始调研这个方案的可行性。

JuiceFS 介绍和架构图如下：

JuiceFS 是一款面向云原生设计的高性能共享文件系统，在 Apache 2.0 开源协议下发布。提供完备的 POSIX 兼容性，可将几乎所有对象存储接入本地作为海量本地磁盘使用，亦可同时在跨平台、跨地区的不同主机上挂载读写。

JuiceFS 采用「数据」与「元数据」分离存储的架构，从而实现文件系统的分布式设计。使用 JuiceFS 存储数据，数据本身会被持久化在对象存储（例如，Amazon S3），相对应的元数据可以按需持久化在 Redis、MySQL、TiKV、SQLite 等多种数据库中。

JuiceFS 提供了丰富的 API，适用于各种形式数据的管理、分析、归档、备份，可以在不修改代码的前提下无缝对接大数据、机器学习、人工智能等应用平台，为其提供海量、弹性、低价的高性能存储。

图片来源：

https://www.juicefs.com/docs/zh/assets/images/juicefs-arch-new-a58938733d246f30089d5302dd268c29.png

当时考虑 JuiceFS 的另外一个原因是它的读缓存机制，当访问 JuiceFS 中的文件时，会有多级缓存给经常访问的数据提供更好的性能，读请求会依次尝试内核分页缓存、JuiceFS 进程的预读缓冲区、本地磁盘缓存，当缓存中没找到对应数据时才会从对象存储读取，并且会异步写入各级缓存保证下一次访问的性能。

图片来源：

https://www.juicefs.com/docs/zh/assets/images/juicefs-cache-511bb8c80eafd64cf06c74ce70b4ccf5.png

可见 JuiceFS 支持将对象存储挂载到 ECS 上，通过文件系统做本地盘访问，同时支持读缓存来加速文件读取，我们也做了测试，确实读写性能还不错。

本地读写测试：

将 JuiceFS 的文件系统挂载到 ClickHouse 存储策略下，进行 SSBM 测试结果如下：

从 ClickHouse 角度看，查询性能和 PL0 相当，甚至略好一些。( PL0 相比 OSS 成本要高不少)

但是，最终我们没有使用 JuiceFS 原因如下：

基于以上问题，我们为了避免引入其他技术栈带来额外的运维成本，当然这里并不是说 JuiceFS 有什么弊端，而是在我们这个场景下需要的是尽可能少引入其他组件。恰好我们有现成的对象存储服务。于是继续测试了原生挂载 OSS 的方案(也就是下面要讲到的方案)。这次调研也让我们深入了解了JuiceFS的架构和优势，为后续在其他场景的应用奠定了基础。

2）方案2：ClickHouse + OSS(我们最终选择的方案)

ClickHouse 原生的 MergeTree 本身就支持直接挂载 S3 作为数据盘使用，我们基于这个特性做了测试，结果符合预期。虽然官方的 demo 是 S3，因为 OSS 也是支持 S3 协议，所以也是同样可以使用的。需要注意的是 endpoint 这里必须 http 开头，否则无法正常挂载。

存储策略配置如下：

<storage_configuration>            <disks>                <hot>                    <path>/data1/ClickHouse/hot/data/</path>                    <move_factor>0.1</move_factor>                </hot>                <cold>                    <path>/data2/ClickHouse/cold/data/</path>                </cold>                <arch>                   <type>s3</type>                   <endpoint>http://log-sh.oss-cn-xx-internal.xxx.com/xxxxxxxxxx/</endpoint>                   <access_key_id>xxxxxxxx</access_key_id>                   <secret_access_key>xxxxxx</secret_access_key>                   <metadata_path>/data1/ClickHouse/disks/s3/</metadata_path>                   <cache_enabled>true</cache_enabled>                   <data_cache_enabled>true</data_cache_enabled>                   <cache_path>/data1/ClickHouse/disks/s3/cache/</cache_path>                </arch>            </disks>            <policies>                <ttl>                    <volumes>                        <hot>                           <disk>hot</disk>                        </hot>                        <cold>                           <disk>cold</disk>                        </cold>                        <arch>                           <disk>arch</disk>                        </arch>                    </volumes>                </ttl>            </policies>    </storage_configuration>

测试情况：

OSS 写入可以到7Gb/s。

该方案架构简单可靠，便于运维，最终我们选择了直接挂载 OSS 。选择 OSS 后，对于归档的数据量，费用可以比 ESSD PL0 还能节省 66%。而且我们给每个集群单独申请一个 OSS Bucket，降低成本的同时还能满足足够的写性能。日志平台目前已经陆续有日志满足归档策略开始迁移，相信会节省很大的一笔费用。

基于上面讲的方案，我们的日志平台存储架构如下：

该架构充分利用了云上的基础设施带来的优势，比如：

这篇文章主要讲解了前一段时间 DBA 团队在日志平台的业务改造中参与的一部分事项，如表字段索引设计建议、过期策略的方案制定、SQL 编写和优化建议、提供成本降低方案等输出。最终通过与日志平台研发同学的努力，我们将日志平台存储由 ES 迁移到了ClickHouse，不但获得了高性能的写入能力，同时也额外节约存储成本 50% 以上。