ClickHouse物化视图占用过高内存与CPU问题排查求助

我创建了如下ClickHouse表与物化视图：

临时表定义（所有数据插入至此） #

CREATE TABLE guardicore.mv_test_data_tmp
(
    `some_id` UUID,
    `count` Nullable(UInt32),
    `aggr_id` FixedString(64),
    `id` UUID,
    `start_time` DateTime64(6),
)
ENGINE = MergeTree
PARTITION BY toDate(start_time)
ORDER BY start_time
TTL toDateTime(start_time) + toIntervalHour(1)
SETTINGS allow_nullable_key = 1, index_granularity = 8192

最终聚合表定义 #

CREATE TABLE guardicore.mv_test_data
(
    `some_id` UUID,
    `count` Nullable(UInt32),
    `aggr_id` FixedString(64),
    `id` UUID,
    `start_time` DateTime64(6),
)
ENGINE = SummingMergeTree
PARTITION BY toDate(start_time)
ORDER BY (start_time, aggr_id)
SETTINGS allow_nullable_key = 1, index_granularity = 8192

物化视图定义 #

CREATE MATERIALIZED VIEW guardicore.mv_test_data_mv TO guardicore.mv_test_data
(
    `aggr_id` FixedString(64),
    `start_time` DateTime,
    `count` Nullable(UInt64),
    `some_id` UUID,
    `id` UUID,
) AS
SELECT
    aggr_id,
    toStartOfFifteenMinutes(start_time) AS start_time,
    sum(count) AS count,
    anyLast(some_id) AS some_id,
FROM guardicore.mv_test_data_tmp
GROUP BY
    aggr_id,
    start_time
ORDER BY
    aggr_id ASC,
    start_time ASC

启用该物化视图进行聚合时，ClickHouse的内存与CPU占用提升了2-3倍。当前使用ClickHouse默认配置，测试场景为4小时内向临时表插入3亿条数据。请问是否存在定义疏漏，或是需要检查哪些配置？

一、表与物化视图定义的疏漏点 #

• 字段不匹配引发额外开销
  物化视图的SELECT语句未包含目标表mv_test_data的id字段，插入时会触发默认值填充逻辑；同时物化视图的start_time类型为DateTime，目标表是DateTime64(6)，类型不匹配会引发隐式转换，增加CPU消耗。
  修复：补充anyLast(id) AS id到物化视图的SELECT语句，或删除目标表中无用的id字段；将物化视图的start_time类型改为DateTime64(6)，对齐目标表类型。

• GROUP BY与目标表排序键不一致
  目标表的ORDER BY为(start_time, aggr_id)，但物化视图的GROUP BY顺序是aggr_id, start_time，聚合结果写入目标表时需要重新排序，大幅消耗CPU和内存。
  修复：将物化视图的GROUP BY顺序改为start_time, aggr_id，与目标表排序键保持一致，避免写入时的排序操作。

• 临时表排序键无法优化聚合
  临时表mv_test_data_tmp的ORDER BY仅为start_time，而物化视图需要按aggr_id和start_time分组，临时表的排序键无法利用有序性优化聚合，导致全表扫描或随机读取开销增大。
  修复：将临时表的ORDER BY改为(aggr_id, start_time)，让物化视图聚合时可以借助有序性减少分组的内存开销。

• SummingMergeTree配置缺失
  目标表作为SummingMergeTree，未指定sum_columns参数，默认会对所有数值类型字段求和，增加Merge阶段的计算量；同时目标表count字段类型为Nullable(UInt32)，与物化视图输出的Nullable(UInt64)不匹配，引发隐式转换。
  修复：创建目标表时添加SETTINGS sum_columns = ['count']，并将count字段类型改为Nullable(UInt64)，对齐物化视图输出类型。

• 物化视图多余ORDER BY
  物化视图SELECT语句末尾的ORDER BY完全多余，目标表本身有排序键，该语句会触发聚合后额外排序，消耗CPU和内存。
  修复：直接删除物化视图中的ORDER BY子句。

二、需要调整的ClickHouse配置项 #

• 内存相关配置

  • max_memory_usage：默认值不足以支撑3亿条数据的聚合，建议调整为服务器内存的50%-70%（例如32G内存的服务器设为20000000000）。
  • max_bytes_before_external_group_by：默认阈值较小，建议调整为10000000000（10GB），当聚合数据量超过阈值时自动触发外部聚合，避免内存溢出并降低内存占用。
  • max_memory_usage_for_user：配合max_memory_usage设置单个用户的内存上限，防止资源耗尽。

• CPU与并发配置

  • max_threads：默认等于CPU核心数，可调整为核心数的1.5-2倍（不超过物理核心数的2倍），避免过多上下文切换。
  • num_pool_threads：设置为CPU核心数的2-4倍，优化并行处理能力。

• 插入与合并配置

  • min_insert_block_size_rows：从默认10000提高到100000，减少小数据块的合并次数，降低CPU消耗。
  • background_pool_size：从默认16调整为32，增加后台合并线程数，加快合并速度，避免任务堆积。
  • merge_tree_max_rows_to_merge_at_once：从默认1000000调整为5000000，减少合并次数。

• TTL清理配置

  • merge_tree_clear_old_partitions_interval_seconds：从默认600调整为300（5分钟），及时清理临时表的过期分区，释放磁盘和内存资源。

内容的提问来源于stack exchange，提问作者Ivan Sushkov