altinity-expert-clickhouse-caches

name: altinity-expert-clickhouse-caches description: Analyze ClickHouse cache systems including mark cache, uncompressed cache, and query cache. Use for cache hit ratio issues and cache tuning.

Cache Analysis and Tuning

Analyze ClickHouse cache systems: mark cache, uncompressed cache, query cache, and compiled expression cache.

Quick Audits

1. Mark Cache Health

with
    (select value from system.events where event = 'MarkCacheHits') as hits,
    (select value from system.events where event = 'MarkCacheMisses') as misses,
    hits / nullIf(hits + misses, 0) as hit_ratio,
    (select value from system.asynchronous_metrics where metric = 'MarkCacheBytes') as cache_bytes,
    (select sum(marks_bytes) from system.parts where active) as total_marks_bytes,
    (select value from system.asynchronous_metrics where metric = 'OSMemoryTotal') as total_ram
select
    'Mark Cache' as cache,
    formatReadableSize(cache_bytes) as size,
    hits,
    misses,
    round(hit_ratio, 3) as hit_ratio,
    multiIf(hit_ratio < 0.3, 'Critical', hit_ratio < 0.5, 'Major', hit_ratio < 0.7, 'Moderate', 'OK') as hit_severity,
    round(100.0 * cache_bytes / total_ram, 2) as pct_of_ram,
    multiIf(pct_of_ram > 25, 'Critical', pct_of_ram > 20, 'Major', pct_of_ram > 15, 'Moderate', 'OK') as size_severity,
    round(100.0 * cache_bytes / nullIf(total_marks_bytes, 0), 2) as pct_marks_cached
settings system_events_show_zero_values = 1

Interpretation:

• hit_ratio < 0.7 → Cache too small or access pattern not cache-friendly
• pct_of_ram > 15% → Cache consuming too much RAM
• pct_marks_cached < 1% → Very few marks in cache (might be OK for large datasets)

2. Uncompressed Cache Health

with
    assumeNotNull((select value from system.events where event = 'UncompressedCacheHits')) as hits,
    assumeNotNull((select value from system.events where event = 'UncompressedCacheMisses')) as misses,
    hits / nullIf(hits + misses, 0) as hit_ratio,
    (select value from system.asynchronous_metrics where metric = 'UncompressedCacheBytes') as cache_bytes,
    (select value from system.asynchronous_metrics where metric = 'OSMemoryTotal') as total_ram
select
    'Uncompressed Cache' as cache,
    formatReadableSize(cache_bytes) as size,
    hits,
    misses,
    round(hit_ratio, 3) as hit_ratio,
    multiIf(hit_ratio < 0.01 and misses > 1000, 'Moderate', 'OK') as hit_severity,
    round(100.0 * cache_bytes / total_ram, 2) as pct_of_ram,
    multiIf(pct_of_ram > 25, 'Critical', pct_of_ram > 20, 'Major', pct_of_ram > 15, 'Moderate', 'OK') as size_severity
settings system_events_show_zero_values = 1

Note: Uncompressed cache is disabled by default. Low hit ratio is normal if not explicitly configured.

3. Query Cache Health (v23.1+)

select
    'Query Cache' as cache,
    formatReadableSize(sum(result_size)) as cached_data,
    count() as entries,
    sum(times_used) as total_hits,
    round(avg(times_used), 2) as avg_hits_per_entry,
    min(query_start_time) as oldest_entry,
    max(query_start_time) as newest_entry
from system.query_cache

4. Compiled Expression Cache

with
    (select value from system.events where event = 'CompiledExpressionCacheHits') as hits,
    (select value from system.events where event = 'CompiledExpressionCacheMisses') as misses
select
    'Compiled Expression Cache' as cache,
    hits,
    misses,
    round(hits / nullIf(hits + misses, 0), 3) as hit_ratio
settings system_events_show_zero_values = 1

Detailed Diagnostics

Mark Cache by Table

select
    database,
    table,
    formatReadableSize(sum(marks_bytes)) as marks_size,
    sum(marks) as marks_count,
    count() as parts
from system.parts
where active
group by database, table
order by sum(marks_bytes) desc
limit 20

Primary Key Memory by Table

select
    database,
    table,
    formatReadableSize(sum(primary_key_bytes_in_memory)) as pk_in_memory,
    formatReadableSize(sum(primary_key_bytes_in_memory_allocated)) as pk_allocated,
    sum(marks) as marks
from system.parts
where active
group by database, table
order by sum(primary_key_bytes_in_memory) desc
limit 20

Cache Events Over Time

select
    toStartOfFiveMinutes(event_time) as ts,
    sumIf(value, metric = 'MarkCacheBytes') as mark_cache_bytes,
    sumIf(value, metric = 'UncompressedCacheBytes') as uncompressed_cache_bytes,
    sumIf(value, metric = 'QueryCacheBytes') as query_cache_bytes
from system.asynchronous_metric_log
where event_time > now() - interval 1 hour
  and metric in ('MarkCacheBytes', 'UncompressedCacheBytes', 'QueryCacheBytes')
group by ts
order by ts

Cache Sizing Recommendations

Current Cache Settings

select name, value, description
from system.server_settings
where name in (
    'mark_cache_size',
    'uncompressed_cache_size',
    'query_cache_max_size',
    'compiled_expression_cache_size'
)

Recommended Sizing

Sizing Analysis

with
    (select value from system.asynchronous_metrics where metric = 'OSMemoryTotal') as total_ram,
    (select sum(marks_bytes) from system.parts where active) as total_marks
select
    formatReadableSize(total_marks) as total_marks_size,
    formatReadableSize(total_ram * 0.05) as recommended_mark_cache_5pct,
    formatReadableSize(total_ram * 0.10) as recommended_mark_cache_10pct,
    formatReadableSize(least(total_marks, total_ram * 0.15)) as ideal_mark_cache,
    'Ideal = min(all_marks, 15% RAM)' as formula

Problem Investigation

Poor Mark Cache Hit Ratio

Possible causes:

1. Cache too small for working set
2. Queries scan many different tables
3. Many small queries to cold data

Diagnostic:

-- Check which tables are being queried
select
    arrayStringConcat(tables, ', ') as tables,
    count() as query_count,
    round(avg(read_rows)) as avg_rows_read
from system.query_log
where type = 'QueryFinish'
  and event_date = today()
  and length(tables) > 0
group by tables
order by query_count desc
limit 20

Cache Too Large

If mark cache > 15% RAM:

-- Check for tables with excessive marks
select
    database,
    table,
    formatReadableSize(sum(marks_bytes)) as marks_size,
    sum(marks) as marks_count,
    round(sum(marks_bytes) / (select value from system.asynchronous_metrics where metric = 'MarkCacheBytes') * 100, 2) as pct_of_cache
from system.parts
where active
group by database, table
having sum(marks_bytes) > 100000000
order by sum(marks_bytes) desc
limit 20

Solutions:

• Reduce index_granularity for tables with excessive marks
• Drop unused tables
• Reduce mark_cache_size setting

Ad-Hoc Query Guidelines

Required Safeguards

• Cache metrics are cumulative since restart - check uptime for context
• Compare hit ratios over time, not just current values

Useful Views

-- Cache hit ratio over time (last hour)
select
    toStartOfMinute(event_time) as ts,
    sum(ProfileEvent_MarkCacheHits) as hits,
    sum(ProfileEvent_MarkCacheMisses) as misses,
    round(hits / nullIf(hits + misses, 0), 3) as hit_ratio
from system.metric_log
where event_time > now() - interval 1 hour
group by ts
order by ts

Cross-Module Triggers

Settings Reference