AgentSkillsCN

altinity-expert-clickhouse-memory

诊断 ClickHouse 的内存使用情况、OOM 错误、内存压力及分配模式。适用于排查与内存相关的各类问题，以及应对内存不足错误。

SKILL.md
--- frontmatter
name: altinity-expert-clickhouse-memory
description: Diagnose ClickHouse RAM usage, OOM errors, memory pressure, and allocation patterns. Use for memory-related issues and out-of-memory errors.

Memory Usage and OOM Diagnostics

Diagnose RAM usage, memory pressure, OOM risks, and memory allocation patterns.

Diagnostics

Run all queries from the file checks.sql and analyze the results.

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

High Memory from JOINs

Solutions:

  • •Use max_bytes_in_join
  • •Consider join_algorithm = 'partial_merge' or 'auto'
  • •Ensure smaller table on right side

Ad-Hoc Query Guidelines

Required Safeguards

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

-- Limit results
limit 100

Memory-Related Metrics

  • •MemoryTracking - current tracked memory
  • •MemoryResident - RSS
  • •OSMemoryTotal, OSMemoryFreeWithoutCached - system memory

Cross-Module Triggers

FindingLoad ModuleReason
High merge memoryaltinity-expert-clickhouse-mergesAnalyze merge patterns
Large dictionariesaltinity-expert-clickhouse-dictionariesDictionary optimization
Cache too largealtinity-expert-clickhouse-cachesCache sizing
PK memory highaltinity-expert-clickhouse-schemaORDER BY optimization
Query OOMsaltinity-expert-clickhouse-reportingQuery optimization

Settings Reference

SettingScopeNotes
max_memory_usageQueryPer-query limit
max_memory_usage_for_userUserPer-user aggregate
max_server_memory_usageServerGlobal limit
max_server_memory_usage_to_ram_ratioServerAuto-limit as % of RAM
max_bytes_before_external_group_byQuerySpill aggregation to disk
max_bytes_in_joinQuerySpill join to disk