altinity Cache Analysis and Tuning
Analyze ClickHouse cache systems: mark cache, uncompressed cache, query cache, and compiled expression cache.
Diagnostics
Run all queries from checks.sql in this skill's directory and analyze the results.
Cache Sizing Recommendations
| Cache | Typical Size | Notes |
|---|---|---|
| Mark Cache | 5-10% of RAM | Higher if random access patterns |
| Uncompressed | 0 (disabled) or 5-10% | Enable only for specific workloads |
| Query Cache | 1-5GB | For repeated identical queries |
| Compiled Expression | 128MB-1GB | Higher for complex expressions |
Problem Investigation
Poor Mark Cache Hit Ratio
Possible causes:
- Cache too small for working set
- Queries scan many different tables
- Many small queries to cold data
Cache Too Large
If mark cache > 15% RAM:
Solutions:
- Reduce
index_granularityfor tables with excessive marks - Drop unused tables
- Reduce
mark_cache_sizesetting
Cross-Module Triggers
| Finding | Load Module | Reason |
|---|---|---|
| Cache using too much RAM | altinity-expert-clickhouse-memory |
Overall memory analysis |
| Poor hit ratio + high disk IO | altinity-expert-clickhouse-storage |
Disk bottleneck |
| Many marks per table | altinity-expert-clickhouse-schema |
Consider index_granularity tuning |
| Query cache misses | altinity-expert-clickhouse-reporting |
Query pattern analysis |
Settings Reference
| Setting | Scope | Notes |
|---|---|---|
mark_cache_size |
Server | Global mark cache limit |
uncompressed_cache_size |
Server | Set to 0 to disable |
use_uncompressed_cache |
Query | Enable per-query |
query_cache_max_size |
Server | Query result cache |
use_query_cache |
Query | Enable per-query |