ClickHouse-内存-使用率优化完整指南

📋 版本适用性声明

本指南旨在提供一个跨版本通用的ClickHouse内存优化方法论。核心优化原则（如减少主键数量、控制写入频次、限制查询内存）适用于所有主流版本。然而，具体配置参数、系统表字段和某些高级功能的可用性会因版本而异。

主要版本功能支持概览

 
功能模块20.x - 22.x LTS23.x (含您的23.8)24.x 及以上核心建议
主键缓存 (primary_key_cache) 部分支持，行为有差异 完整支持，需显式开启 完整支持，且持续优化 v23.8适用。这是您版本的关键优化点。
系统监控表字段 (如system.mutations) 字段较少 字段不完整，如缺duration_ms 字段最全，监控能力最强 v23.8需使用替代字段，下文会提供专属查询。
system.caches 表 不存在 不存在 存在 v23.8需通过异步指标间接观察缓存。
资源组(Resource Groups) 基础支持 功能增强 功能完善 v23.8可用，用于实现资源隔离。
通配符与函数 基础支持 支持更完善的通配符和聚合函数 完全支持 v23.8的查询语法基本现代化。

结论：您使用的 v23.8 是一个承上启下的版本，核心优化功能均已具备，但部分监控便捷性不如24.x及以上版本。本指南将特别标注适用于您版本的方案。

🔍 第一阶段：评估与监控（v23.8 特别适配）

在优化前，必须先定位内存消耗点。以下查询已针对 v23.8 调整。

1. 全局内存概况

sql
-- 查看ClickHouse进程整体内存 (v23.8 适用)
SELECT * FROM system.asynchronous_metrics 
WHERE metric LIKE '%Memory%' OR metric LIKE '%Cache%';

-- 查看当前内存分配器状态 (关键！)
SELECT * FROM system.metrics 
WHERE metric IN ('MemoryTracking', 'MarkCacheBytes', 'UncompressedCacheBytes');

2. 定位内存消耗最大的表（核心）

sql
-- 查看各表主键索引内存、parts数量 (v23.8 完全支持)
SELECT 
    database,
    table,
    formatReadableSize(sum(primary_key_bytes_in_memory)) AS pk_memory,
    count() AS parts_count,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.parts 
WHERE active = 1
GROUP BY database, table
ORDER BY pk_memory DESC
LIMIT 20;

3. 监控正在进行的危险操作

sql
-- 查看正在进行的Merge操作及内存使用 (v23.8 适用)
SELECT 
    database, 
    table, 
    elapsed, 
    formatReadableSize(memory_usage) AS memory_used,
    progress
FROM system.merges 
WHERE progress < 1
ORDER BY memory_usage DESC;

-- 查看近期的突变（Mutation，如TTL、DELETE）(v23.8 适用，使用现有字段)
SELECT 
    database, 
    table, 
    mutation_id, 
    command, 
    create_time, 
    parts_to_do,
    is_done
FROM system.mutations 
WHERE is_done = 0
ORDER BY create_time DESC;

4. 识别高内存查询

sql
-- 从查询日志中找出高内存消耗的查询 (v23.8 适用，注意无`elapsed`字段)
SELECT 
    query_id, 
    user, 
    substring(query, 1, 150) as short_query,
    formatReadableSize(memory_usage) AS memory_used,
    read_rows,
    written_rows,
    query_start_time
FROM system.query_log 
WHERE event_date = today() 
    AND memory_usage > 1_000_000_000  -- 查找使用超过1GB内存的查询
    AND type = 'QueryFinish' 
ORDER BY memory_usage DESC 
LIMIT 10;

⚙️ 第二阶段：配置与参数优化（版本分级配置）

1. 内存限制配置（所有版本核心）

在 users.xml 或 profiles 中为不同用户或查询设置硬限制。

xml
<!-- 示例：为 default 用户设置严格限制 (v20.x, 23.x, 24.x 均支持) -->
<profiles>
    <default>
        <!-- 单个查询内存限制，建议设置为总内存的 10%-20% -->
        <max_memory_usage>10000000000</max_memory_usage> <!-- 10GB -->
        <!-- 所有查询总内存限制，防止并发拖垮系统 -->
        <max_memory_usage_for_user>50000000000</max_memory_usage_for_user> <!-- 50GB -->
        <!-- 启用当内存超限时，将GROUP BY溢出到磁盘 -->
        <max_bytes_before_external_group_by>2000000000</max_bytes_before_external_group_by>
        <!-- 聚合时优化内存使用 -->
        <group_by_two_level_threshold>100000</group_by_two_level_threshold>
    </default>
</profiles>

2. 主键索引优化（v23.8+ 重点）

这是降低常驻内存最有效的手段。

sql
-- 为指定大表启用主键缓存 (v23.8 必需手动开启)
ALTER TABLE your_large_table 
    MODIFY SETTING use_primary_key_cache = 1,
    primary_index_cache_size = 1073741824; -- 为此表分配 1GB 缓存空间

-- 设置全局主键缓存总大小 (在 config.xml 中)
<primary_index_cache_size>10737418240</primary_index_cache_size> <!-- 10GB -->

-- 修改索引粒度以减少索引条目数 (所有版本支持)
ALTER TABLE your_table MODIFY SETTING index_granularity = 16384; -- 从默认8192调大

3. 合并（Merge）策略优化

控制后台Merge操作的内存和频率，避免集中爆发。

xml
<!-- 在 config.xml 的 merge_tree 部分调整 (v20.x+ 支持) -->
<merge_tree>
    <!-- 最大合并线程数，根据CPU核心数调整 -->
    <max_merge_threads>8</max_merge_threads>
    <!-- 控制待合并数据的总大小，避免超大合并 -->
    <max_bytes_to_merge_at_max_space_in_pool>107374182400</max_bytes_to_merge_at_max_space_in_pool>
    <!-- 限制同时进行的TTL Merge数量 -->
    <max_number_of_merges_with_ttl_in_pool>1</max_number_of_merges_with_ttl_in_pool>
</merge_tree>

🛠️ 第三阶段：表设计与写入优化（版本通用最佳实践）

1. 优化的表结构设计

sql
CREATE TABLE optimized_table (
    -- 使用低基数、短类型字段作为主键前缀
    event_date Date,
    user_id UInt32,
    -- 高基数字段放在主键后面或使用索引
    event_id UUID,
    -- 避免在ORDER BY中使用长字符串
    ...
) ENGINE = MergeTree()
-- 分区键选择：时间范围，避免产生过多分区
PARTITION BY toYYYYMM(event_date)
-- 主键设计：选择查询最常用的过滤条件，列数不宜过多（2-4列为佳）
ORDER BY (event_date, user_id)
-- 关键设置：
SETTINGS 
    index_granularity = 16384,                    -- 增大颗粒度
    min_bytes_for_wide_part = 104857600,          -- 100MB以上使用宽存储
    ttl_only_drop_parts = 1;                      -- TTL时整块删除parts，减少负载

2. 智能数据生命周期管理（TTL）

sql
-- 自动清理旧数据，减少数据量是根本
ALTER TABLE your_table 
    MODIFY TTL 
    event_time + INTERVAL 90 DAY TO VOLUME 'cold',
    event_time + INTERVAL 180 DAY DELETE;

-- 定期手动合并，优化存储
OPTIMIZE TABLE your_table FINAL;

3. 写入模式优化

  • 批量写入：单次插入至少 10万行 或 64MB 数据。

  • 控制频率：避免高频（如每秒）的小批量写入，建议间隔 1分钟以上。

  • 异步写入：使用 Buffer 表或外部队列（如Kafka）进行缓冲。

sql
-- 使用Buffer表平滑写入峰值 (所有版本支持)
CREATE TABLE your_table_buffer AS your_table 
ENGINE = Buffer('default', 'your_table', 16, 10, 30, 10000, 1000000, 10000000, 100000000);

🔎 第四阶段：查询优化（避免“读”引发内存爆炸）

1. 为高内存查询添加防护

sql
-- 在查询前设置会话级限制 (v23.8 适用)
SET max_memory_usage = 5000000000;      -- 本次查询最多用5GB
SET max_bytes_before_external_sort = 2000000000; -- 排序溢出到磁盘的阈值

-- 示例：一个可能消耗大量内存的GROUP BY查询
SELECT 
    user_id, 
    count()
FROM large_table
GROUP BY user_id
SETTINGS 
    optimize_aggregation_in_order = 1,  -- 按主键顺序优化聚合
    max_bytes_before_external_group_by = 2000000000;

2. 避免常见内存陷阱

  • 禁用 DISTINCT：在大数据集上使用 DISTINCT 极易OOM，改用近似函数 uniqCombined。

  • 慎用 JOIN：特别是将大表放在JOIN的右侧，应将小表放右侧。使用 GLOBAL IN 代替分布式JOIN。

  • 限制结果集：总是使用 LIMIT，尤其是交互式查询。

🚨 第五阶段：紧急处理与预防（v23.8 操作清单）

当内存使用率突然飙升，即将触发OOM时，按顺序执行：

  1. 立即终止危险查询：

    sql
    -- 查找正在运行的高内存查询
SELECT query_id, user, query, memory_usage FROM system.processes 
WHERE memory_usage > 5000000000 ORDER BY memory_usage DESC;

-- 终止指定查询
KILL QUERY WHERE query_id = 'query_id_here';

  2. 释放缓存（治标不治本）：

    sql
    SYSTEM DROP MARK CACHE;
SYSTEM DROP UNCOMPRESSED CACHE;

  3. 暂停后台Merge（极端情况）：

    sql
    -- 通过修改表设置临时降低Merge积极性
ALTER TABLE problem_table MODIFY SETTING merge_with_ttl_timeout = 3600; -- 延长TTL合并间隔

📈 第六阶段：长期监控与告警

为以下指标建立仪表盘和告警（以Prometheus为例）：

 
监控指标告警阈值（建议）说明
ClickHouse进程内存使用 > 总内存的 85% 最核心的告警。
单个查询内存使用 > 5GB 找出需要优化的查询。
Parts数量 单表 > 1000 Parts过多影响Merge和查询性能。
Merge操作内存 > 10GB 可能正在进行超大合并。

💎 总结与版本升级建议

对于 v23.8 用户，您可以立即实施本指南中 90% 的优化措施。您与最新版本的主要差距在于监控的便捷性和部分自动化管理功能。

长期建议：规划升级到 24.x 以上的LTS版本。新版本在内存管理上更为精细和智能，例如：

  • 更完善的 system.caches 和 system.metric_log。

  • 查询队列和资源组功能更成熟。

  • 后台任务自动调优能力更强。

通过 “配置优化打基础 + 监控分析找重点 + 查询与写入规范避风险” 的组合拳，您完全可以驯服ClickHouse的内存使用，使其在高效运行的同时保持稳定。

 posted on 2025-12-20 16:46  xibuhaohao  阅读(87)  评论(0)    收藏  举报