How to Control Cache Behavior in ClickHouse

How do I control ClickHouse cache behavior?

ClickHouse lets you tune, flush, and monitor its mark, uncompressed, and query caches to speed up repeat reads or free memory.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is cache behavior in ClickHouse?

Cache behavior covers how ClickHouse stores recently read data blocks (mark & uncompressed cache) and finished result sets (query cache) in RAM. Proper tuning avoids wasted memory and maximizes hit-rate for repetitive queries.

How do I enable or disable the query result cache?

Set use_query_cache per session or per query. A value of 1 stores results; 0 bypasses the cache.Combine with query_cache_min_query_duration_ms to avoid caching trivial queries.

How can I drop caches without restarting?

Use SYSTEM DROP [MARK|UNCOMPRESSED|QUERY] CACHE to free specific caches instantly. Handy after large batch loads or schema changes that make cached data obsolete.

When should I adjust mark and uncompressed cache sizes?

Increase sizes when dashboards reread the same columns frequently and the server still has free RAM.Decrease or disable when workloads are highly ad-hoc or RAM is scarce.

Practical example: speeding up repeat order reports

An ecommerce dashboard repeatedly totals Orders by day. Enabling the query cache and ensuring hot columns stay in the mark cache cuts latency from seconds to milliseconds.

Best practices for cache tuning

Start with defaults, monitor MarkCacheEntries and QueryCacheHits, then increment sizes gradually. Always drop caches after heavy INSERT batches to prevent stale data.

Common mistakes

See dedicated section below.

FAQs

Scroll down.

Why How to Control Cache Behavior in ClickHouse is important

How to Control Cache Behavior in ClickHouse Example Usage


-- Accelerate daily revenue report by caching results
SELECT order_date,
       sum(total_amount) AS revenue
FROM Orders
GROUP BY order_date
ORDER BY order_date
SETTINGS use_query_cache = 1, query_cache_min_query_duration_ms = 50;

How to Control Cache Behavior in ClickHouse Syntax


-- Enable query cache for a single statement
SELECT order_date, sum(total_amount) AS revenue
FROM Orders
GROUP BY order_date
SETTINGS use_query_cache = 1;

-- Disable uncompressed cache at session level
SET use_uncompressed_cache = 0;

-- Flush mark cache after large load
SYSTEM DROP MARK CACHE;

-- Increase mark cache via config file (server restart needed)
<mark_cache_size>5368709120</mark_cache_size>  <!-- 5 GB -->

Common Mistakes

Disabling caches globally because of one low-memory query. Fix: override caches at the query level with SETTINGS instead of changing server-wide settings.
Forgetting to drop caches after bulk INSERTs, causing stale results. Fix: run SYSTEM DROP MARK CACHE or SYSTEM DROP QUERY CACHE immediately after large data loads.