Use query cache in Managed ClickHouse®
Managed ClickHouse® provides a query cache mechanism that helps improve query performance by caching query results.
How it works
When the ClickHouse query cache
is enabled, the result of a SELECT query is computed once and stored in the cache.
Subsequent executions of the same query are served directly from the cache.
Important
By default, the Managed ClickHouse query cache is disabled for all SELECT queries.
Why use it
Using query cache in your Managed ClickHouse services can help reduce latency and resource consumption.
Key use case for the Managed ClickHouse query cache are the following:
- Performance enhancement: Reducing latency and load for frequently executed queries
- Analytical workloads: Using complex repetitive queries that involve high data aggregation
Enable query cache
To enable the query cache for a query, set the use_query_cache setting for the query to 1.
You can achieve this by appending SETTINGS use_query_cache = 1 to the end of your query
using an SQL client (for example, the
ClickHouse client):
SELECT 1
SETTINGS use_query_cache = 1;Configure query cache
To configure the query cache settings, use an SQL client (for example, the ClickHouse client) and append a defined setting to your query, for example:
SELECT 1
SETTINGS use_query_cache = 1, query_cache_min_query_runs = 5000;You can configure the following query cache settings:
enable_writes_to_query_cacheenable_reads_from_query_cachequery_cache_max_entriesquery_cache_min_query_runsquery_cache_min_query_durationquery_cache_compress_entriesquery_cache_squash_partial_resultsquery_cache_ttlquery_cache_share_between_users
Limitation
- Cached results are not invalidated or discarded when the underlying data (the result
of a
SELECTquery) changes, which might cause returning stale results. - Maximum query cache size: 64 MiB for each GiB of RAM (for example, 256 MiB for a 4-GiB service or 1 GiB for a 16-GiB service)
- Maximum number of query cache entries: 1024