Clickhouse单机写入调优

语落心生
232 阅读11分钟

前言

小公司没什么钱买公有云，由于目前的xxx数据中心架构所导致，客户在部署单机Clickhouse的时候需要做资源控制。 需要进行资源调优

分别创建天级，小时级，分钟级的物化视图，对使用的内存资源进行控制, 分别测试iot_json表按小时分区和按照天级分区

用例sql

CREATE DATABASE if not exists test;

DROP table if exists test.iot_json ;

CREATE TABLE test.iot_json

(

`id` Int64 DEFAULT generateSnowflakeID(1),

`iot_json` String,

`create_time` DateTime DEFAULT now()

)

ENGINE = MergeTree

PARTITION BY toStartOfHour(create_time)

ORDER BY (id DESC ,create_time DESC)

TTL create_time + toIntervalHour(1)

SETTINGS

allow_experimental_reverse_key = 1;

DROP table if exists test.iot_json_buffer ;

CREATE TABLE test.iot_json_buffer AS test.iot_json ENGINE = Buffer(test, iot_json, 1, 5, 20, 2000, 10000, 1048576, 5242880,10);

DROP table if exists test.iot_main ;

CREATE TABLE test.iot_main

( `node_id` Int64,

`uuid` String,

`batch_id` String,

`device_time` DateTime,

`device_timestamp` Int64,

`key_id` String,

`str_v` String,

`is_delete` Int32 DEFAULT 0,

`create_time` DateTime64(3) DEFAULT now())

ENGINE = ReplacingMergeTree

PARTITION BY toStartOfHour(device_time)

ORDER BY (node_id,batch_id,device_time,key_id,intHash32(device_time),device_timestamp)

SAMPLE BY intHash32(device_time)

TTL device_time + toIntervalDay(3) TO DISK 'cold_disk'

SETTINGS storage_policy = 'hot_to_cold',index_granularity = 8192;

DROP table if exists test.iot_main_minute_agg ;

CREATE TABLE test.iot_main_minute_agg

( `node_id` UInt64 NOT null COMMENT '测点id',

`device_time` DateTime NOT null COMMENT '设备时间',

`key_id` String NOT null COMMENT '指标key',

`avg_v` AggregateFunction(avg,Float64) NOT null COMMENT '平均值',

`max_v` AggregateFunction(max,Float64) NOT null COMMENT '最大值',

`min_v` AggregateFunction(min,Float64) NOT null COMMENT '最小值',

`deleted` UInt8 DEFAULT 0 COMMENT '是否删除',

`create_time` DateTime64(3) DEFAULT now() COMMENT '创建时间')

ENGINE = AggregatingMergeTree

PARTITION BY toStartOfInterval(device_time, INTERVAL 4 HOUR)

ORDER BY (node_id, device_time,key_id,intHash32(device_time))

SAMPLE BY intHash32(device_time)

TTL device_time + toIntervalDay(7) TO DISK 'cold_disk'

SETTINGS storage_policy = 'hot_to_cold',index_granularity = 8192;

DROP table if exists test.iot_main_hour_agg ;

CREATE TABLE test.iot_main_hour_agg

( `node_id` UInt64 NOT null COMMENT '测点id',

`device_time` DateTime NOT null COMMENT '设备时间',

`key_id` String NOT null COMMENT '指标key',

`avg_v` AggregateFunction(avg,Float64) NOT null COMMENT '平均值',

`max_v` AggregateFunction(max,Float64) NOT null COMMENT '最大值',

`min_v` AggregateFunction(min,Float64) NOT null COMMENT '最小值',

`deleted` UInt8 DEFAULT 0 COMMENT '是否删除',

`create_time` DateTime64(3) DEFAULT now() COMMENT '创建时间')

ENGINE = AggregatingMergeTree

PARTITION BY toYYYYMMDD(device_time)

ORDER BY (node_id, device_time,key_id,intHash32(device_time))

SAMPLE BY intHash32(device_time)

TTL device_time + toIntervalDay(15) TO DISK 'cold_disk'

SETTINGS storage_policy = 'hot_to_cold',index_granularity = 8192;

DROP table if exists test.iot_main_day_agg ;

CREATE TABLE test.iot_main_day_agg

( `node_id` UInt64 NOT null COMMENT '测点id',

`device_time` DateTime NOT null COMMENT '设备时间',

`key_id` String NOT null COMMENT '指标key',

`avg_v` AggregateFunction(avg,Float64) NOT null COMMENT '平均值',

`max_v` AggregateFunction(max,Float64) NOT null COMMENT '最大值',

`min_v` AggregateFunction(min,Float64) NOT null COMMENT '最小值',

`deleted` UInt8 DEFAULT 0 COMMENT '是否删除',

`create_time` DateTime64(3) DEFAULT now() COMMENT '创建时间')

ENGINE = AggregatingMergeTree

PARTITION BY toYYYYMM(device_time)

ORDER BY (node_id, device_time,key_id,intHash32(device_time))

SAMPLE BY intHash32(device_time)

TTL device_time + toIntervalDay(90) TO DISK 'cold_disk'

SETTINGS storage_policy = 'hot_to_cold',index_granularity = 8192;

DROP VIEW if exists test.iot_main_view ;

CREATE MATERIALIZED VIEW test.iot_main_view TO test.iot_main

AS

SELECT node_id,uuid,batch_id,device_time,device_timestamp,key_id,str_v

FROM

(

SELECT

JSONExtractString(iot_json,'node_id') AS node_id,

JSONExtractString(iot_json,'uuid') AS uuid,

JSONExtractString(iot_json,'batch_id') AS batch_id,

JSONExtractString(iot_json,'timestamp') AS device_timestamp,

toDateTime64(toInt64(device_timestamp) / 1000,3) AS device_time,

arrayJoin(JSONExtractKeysAndValues(iot_json,'String')) AS kv,

kv.1 AS key_id,

kv.2 AS str_v

FROM test.iot_json WHERE empty(JSONExtractString(iot_json,'timestamp'))=0

);

DROP VIEW if exists test.iot_main_minute_agg_view ;

CREATE MATERIALIZED VIEW test.iot_main_minute_agg_view TO test.iot_main_minute_agg

AS SELECT node_id, key_id,device_time,avg_v,max_v,min_v

FROM(SELECT

node_id, key_id, toStartOfMinute(device_time) AS device_time,avgState(toFloat64(str_v)) AS avg_v,maxState(toFloat64(str_v)) AS max_v,minState(toFloat64(str_v)) AS min_v

FROM test.iot_main

WHERE isNotNull(toFloat64OrNull(str_v))

GROUP BY node_id,key_id, device_time);

DROP VIEW if exists test.iot_main_hour_agg_view ;

CREATE MATERIALIZED VIEW test.iot_main_hour_agg_view TO test.iot_main_hour_agg

AS SELECT node_id, key_id,device_time,avg_v,max_v,min_v

FROM(SELECT

node_id, key_id, toStartOfHour(device_time) AS device_time,avgState(toFloat64(str_v)) AS avg_v,maxState(toFloat64(str_v)) AS max_v,minState(toFloat64(str_v)) AS min_v

FROM test.iot_main

WHERE isNotNull(toFloat64OrNull(str_v))

GROUP BY node_id,key_id, device_time);

DROP VIEW if exists test.iot_main_day_agg_view ;

CREATE MATERIALIZED VIEW test.iot_main_day_agg_view TO test.iot_main_day_agg

AS SELECT node_id, key_id,device_time,avg_v,max_v,min_v

FROM(SELECT

node_id, key_id, toStartOfDay(device_time) AS device_time,avgState(toFloat64(str_v)) AS avg_v,maxState(toFloat64(str_v)) AS max_v,minState(toFloat64(str_v)) AS min_v

FROM test.iot_main

WHERE isNotNull(toFloat64OrNull(str_v))

GROUP BY node_id,key_id, device_time);

Clickhouse 内存消耗测试

部署环境

单机

● 磁盘: 200G

● 内存: 8G

● CPU类型: Intel(R) Xeon(R) Silver 4210 CPU @ 2.20GHz

● 线程数: 10核心20线程 (这是宿主机的配置，因为开的虚拟机用的宿主机的部分资源，真实只用了4核8线程)

前置知识

Part的基本概念

ClickHouse的MergeTree表由多个有序的数据部件(parts)组成。每个part包含一定范围内的数据，并按照主键排序。

当数据被插入到表中时，会创建新的part，每个part内部的数据按照主键进行字典序排序。例如，如果主键是(CounterID, Date)，那么part中的数据首先按CounterID排序，然后在每个CounterID内部按Date排序。

Part的结构和命名

每个part都有一个特定的命名格式，包含了很多关于数据的信息：

<partition_id>_<minimum_block_number>_<maximum_block_number>_<level>_<data_version>

其中：

● partition_id - 标识分区键

● minimum_block_number - 标识part中的最小块号

● maximum_block_number - 标识part中的最大块号

● level - 每次合并后增加1，级别0表示这是一个新的未合并的part

● data_version - 可选值，当part发生变异时递增

Part的生命周期

1.  创建：当执行INSERT操作时，会创建新的数据部件

2.  合并：后台进程会定期选择一些部件并将它们合并成一个更大的有序部件，这就是为什么它被称为MergeTree（合并树）

3.  激活/非激活：活跃的part用于表的查询，非活跃的part（如合并后的源part）会在一段时间后被删除

背景说明

数据分片可以以 Wide 或 Compact 格式存储。在 Wide 格式中，每一列存储在文件系统中的单独文件中，而在 Compact 格式中，所有列存储在一个文件中。使用 Compact 格式可以提高小量频繁插入的性能。

数据存储格式受表引擎的 min_bytes_for_wide_part 和 min_rows_for_wide_part 设置控制。如果数据分片的字节数或行数少于相应设置的值，则该分片以 Compact(JSONEachRow) 格式存储。否则，以 Wide 格式存储。如果未设置这些设置，则数据分片以 Wide 格式存储。

min_bytes_for_compact_part 和 min_rows_for_compact_part 
控制MergeTree表是否采用in-memory模式还是compact格式。
min_bytes_for_wide_part 和 min_rows_for_wide_part 
控制MergeTree表是否采用wide格式还是compact格式
 设置项的关系:
    0 <= min_bytes_for_compact_part <=  min_bytes_for_wide_part
    0 <= min_rows_for_compact_part<= min_rows_for_wide_part

Compact格式通常用于存储小型数据部分（最多10M），并且不支持非自适应粒度的表

在默认格式下以wide格式存储，并可以开启垂直合并提高效率

将表属性加入wide合并上限配置，开启wide存储格式需要达到的阈值

min_rows_for_wide_part = 1000

调整clickhouse的storge.xml配置文件

<merge_tree>

    <parts_to_throw_insert>1000</parts_to_throw_insert> //如果单个分区中的活跃数据部件数量超过此值，INSERT操作将被中断并抛出"Too many parts",防止表中part数量过多造成性能下降

    <parts_to_delay_insert>1000</parts_to_delay_insert> //定义了单个分区中活跃数据部分的阈值，超过这个阈值后，INSERT操作会被人为地延迟执行

    <max_delay_to_insert>3</max_delay_to_insert> //当一个分区中的活跃数据部分数量超过parts_to_delay_insert值时，系统会人为地延长INSERT操作的执行时间（通过添加"睡眠"时间），以便后台合并进程能够比新增数据部分更快地合并现有部分

    <max_suspicious_broken_parts>1000</max_suspicious_broken_parts> //用于控制在加载数据部分(part)时，系统可以容忍的损坏数据部分的最大数量。如果在启动时发现的损坏部分数量超过此设置值，ClickHouse将拒绝启动表并抛出异常

    <vertical_merge_algorithm_min_rows_to_activate>1000</vertical_merge_algorithm_min_rows_to_activate> //要激活垂直合并算法，合并的数据部分中的行数总和必须至少达到这个阈值

    <vertical_merge_algorithm_min_columns_to_activate>6</vertical_merge_algorithm_min_columns_to_activate> //激活垂直合并算法所需的最小列数

</merge_tree>

在user.xml调整聚合时内存

set max_bytes_before_external_group_by=
0.75M(数据大小)*1000(part数量)*1024*1024=786432000; #20G，默认值为0，表示在GROUP BY中启动将临时数据转存到磁盘上的内存阈值。
 
# 使用max_bytes_before_external_group_by时，
建议将max_memory_usage设置为它的两倍。这是因为一个聚合需要两个阶段来完成：
#（1）读取数据并形成中间数据 
#（2）合并中间数据。临时数据的转存只会发生在第一个阶段。
# 如果没有发生临时文件的转存，那么阶段二将最多消耗与1阶段相同的内存大小。
set max_memory_usage=1572864000; #为max_bytes_before_external_group_by的
两倍

想要详细了解clickhouse写入原理的朋友可以看我的这篇Clickhouse RepicatedReplacingMergeTree下推分析（二）

指标观测说明

Linux的ps -ef命令显示的进程内存与ClickHouse的MemoryTracking指标之间存在差距。这是一个常见的现象，有几个重要原因：

● 1. 测量方式不同

ps -ef命令（或更常用的ps aux）显示的是进程的RSS（Resident Set Size）或VSZ（Virtual Size），而ClickHouse的MemoryTracking指标跟踪的是通过ClickHouse内存分配器分配的内存。

在ClickHouse中，MemoryTracking是通过跟踪所有内存分配和释放操作来实现的：

每次内存分配时，ClickHouse都会调用Memory::trackMemory来更新内存跟踪器。

● 2. RSS与分配内存的区别

RSS（Resident Set Size）是进程实际使用的物理内存量，包括共享库、堆栈、页缓存等，而MemoryTracking只跟踪通过ClickHouse分配器分配的内存。

ClickHouse实际上会在AsynchronousMetrics中收集RSS信息：

并且在某些情况下会使用RSS来更新MemoryTracker

● 3. 内存校正机制

ClickHouse有一个内存校正机制，它会定期将MemoryTracking与实际的RSS进行比较，并在必要时进行调整。这是通过MemoryWorker后台线程实现的：

此外，ClickHouse还维护了一个MemoryTrackingUncorrected指标，它表示未经RSS校正的内存分配总量：

● 4. 内存分配器的影响

ClickHouse可以使用不同的内存分配器（如jemalloc），这些分配器可能有自己的内存管理策略，包括缓存和预分配，这会导致RSS和MemoryTracking之间的差异

● 机械盘 128G + 小时分区

500点位写入 1.43MB/s

image.png 转换为wide格式的记录合并阈值 250M-390M之间

image.png

插入记录随着写入程序上升

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.003.jpeg

内存逐渐下降至1.55G左右

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.004.jpeg

磁盘io 20io/s -40io/s

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.005.jpeg

● 机械盘 128G + 天级分区

写入磁盘 1.43M/s

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.006.jpeg

转为wide格式的记录合并阈值 在300-400M之间

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.007.jpeg

一天后的内存消耗出现明显下降 (下降是因为加入了group-by的内存控制参数刷写到磁盘)

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.008.jpeg

随着插入数据量上涨 2.3k行/s

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.009.jpeg

磁盘io表现在20io/s- 40io/s

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.010.jpeg

固态的硬盘上随着写入速度增加，内存中的part合并数量比较小的速度赶不上写入磁盘的速度。

所以可能会出现频繁的内存抖动

需要更改part小范围合并数量,更改表的设置项如下:

CREATE TABLE test.iot_json
(   
    `id` Int64 DEFAULT generateSnowflakeID(1),   -- 可以用于同步数据的时候偏移量
    `iot_json` String,
    `create_time` DateTime DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toStartOfDay(create_time)
ORDER BY (id DESC ,create_time DESC)
TTL create_time + toIntervalHour(3) 

SETTINGS   
    max_parts_to_merge_at_once = 50,  //控制每批合并的parts数量(最大值)
    min_parts_to_merge_at_once = 10,  //控制每批合并的parts数量(最小值)
    max_bytes_to_merge_at_max_space_in_pool = 5000(设置为垂直合并part数量的倍数
    ), //控制在资源充足的情况下，一次合并操作中所有源parts的最大总大小 
    merge_selector_base = 4.0;// 该值会影响写放大率, 
    较小的值会导致更频繁的合并，但每次合并的parts数量较少；
    较大的值会减少合并频率，但每次合并的parts数量较多

● 固态 128G + 天级分区

设置合并parts之后，wide格式的合并阈值在100-200M之间

Aspose.Words.b9719c12-ac6d-4d17-8bb5-572d3d12a544.011.jpeg

内存在100M-388M之间

image.png

插入条数: 500条/s-2000条/s

image.png

io写入速率: 5io/s - 15io/s

image.png

● 固态 128G + 小时级分区

wide合并part的内存阈值:

从11点开始从180M逐渐下降道126M左右

image.png

内存稳定在19M-66M

image.png 插入速率: 1.29k/s-2.33k/s

image.png

avatar
文章
阅读
粉丝