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