altinity-expert-clickhouse-memory

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.
license	Apache-2.0

Memory Usage and OOM Diagnostics

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

---

Diagnostics

Run all queries from checks.sql in this skill's directory 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

-- 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

Finding	Load Module	Reason
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

---

Settings Reference

Setting	Scope	Notes
max_memory_usage	Query	Per-query limit
max_memory_usage_for_user	User	Per-user aggregate
max_server_memory_usage	Server	Global limit
max_server_memory_usage_to_ram_ratio	Server	Auto-limit as % of RAM
max_bytes_before_external_group_by	Query	Spill aggregation to disk
max_bytes_in_join	Query	Spill join to disk