altinity/altinity-expert-clickhouse-memory

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 |