Back to Details

altinity-expert-clickhouse-memory

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Memory Usage and OOM Diagnostics

内存使用与OOM诊断

Diagnose RAM usage, memory pressure, OOM risks, and memory allocation patterns.
诊断内存使用情况、内存压力、OOM风险以及内存分配模式。

Diagnostics

诊断步骤

Run all queries from the file checks.sql and analyze the results.
运行checks.sql文件中的所有查询并分析结果。

Problem Investigation

问题排查

High Memory from Aggregations

聚合操作导致内存占用过高

Solutions:
  • Add 
    max_bytes_before_external_group_by
  • Use 
    max_threads
    pragma to limit parallelism
  • Restructure query to reduce group by cardinality
解决方案：
  • 添加
    max_bytes_before_external_group_by
    参数
  • 使用
    max_threads
    编译指令限制并行度
  • 重构查询以降低分组的基数

High Memory from JOINs

JOIN操作导致内存占用过高

Solutions:
  • Use 
    max_bytes_in_join
  • Consider 
    join_algorithm = 'partial_merge'
    or 
    'auto'
  • Ensure smaller table on right side
解决方案：
  • 使用
    max_bytes_in_join
    参数
  • 考虑设置
    join_algorithm = 'partial_merge'
    或
    'auto'
  • 确保较小的表位于右侧

Ad-Hoc Query Guidelines

临时查询指南

Required Safeguards

必要的安全措施

sql
-- Always time-bound log queries
where event_date >= today() - 1

-- Limit results
limit 100
sql
-- 始终添加时间范围限制日志查询
where event_date >= today() - 1

-- 限制结果数量
limit 100

Memory-Related Metrics

内存相关指标

  • MemoryTracking
    - current tracked memory
  • MemoryResident
    - RSS
  • OSMemoryTotal
    , 
    OSMemoryFreeWithoutCached
    - system memory
  • MemoryTracking
    - 当前已追踪的内存
  • MemoryResident
    - 常驻内存集（RSS）
  • OSMemoryTotal
    , 
    OSMemoryFreeWithoutCached
    - 系统内存

Cross-Module Triggers

跨模块触发条件

FindingLoad ModuleReason
High merge memory
altinity-expert-clickhouse-merges
Analyze merge patterns
Large dictionaries
altinity-expert-clickhouse-dictionaries
Dictionary optimization
Cache too large
altinity-expert-clickhouse-caches
Cache sizing
PK memory high
altinity-expert-clickhouse-schema
ORDER BY optimization
Query OOMs
altinity-expert-clickhouse-reporting
Query optimization
发现的问题加载模块原因
合并操作内存占用过高
altinity-expert-clickhouse-merges
分析合并模式
字典体积过大
altinity-expert-clickhouse-dictionaries
字典优化
缓存体积过大
altinity-expert-clickhouse-caches
缓存大小调整
主键内存占用过高
altinity-expert-clickhouse-schema
ORDER BY优化
查询出现OOM
altinity-expert-clickhouse-reporting
查询优化

Settings Reference

配置参数参考

SettingScopeNotes
max_memory_usage
QueryPer-query limit
max_memory_usage_for_user
UserPer-user aggregate
max_server_memory_usage
ServerGlobal limit
max_server_memory_usage_to_ram_ratio
ServerAuto-limit as % of RAM
max_bytes_before_external_group_by
QuerySpill aggregation to disk
max_bytes_in_join
QuerySpill join to disk
参数作用范围说明
max_memory_usage
查询单查询内存限制
max_memory_usage_for_user
用户单个用户的总内存限制
max_server_memory_usage
服务器全局内存限制
max_server_memory_usage_to_ram_ratio
服务器按内存占比自动设置限制
max_bytes_before_external_group_by
查询将聚合操作溢出到磁盘
max_bytes_in_join
查询将JOIN操作溢出到磁盘