All posts
Why Repeated Dashboard Queries Stay Slow in ClickHouse®

Why Repeated Dashboard Queries Stay Slow in ClickHouse®

November 18, 20268 min readReshma M
Share:

The problem

Dashboards often send the same SELECT queries at regular intervals. Each request can repeat the same scans, filters, joins, and aggregations.

This repeated work can increase CPU usage, disk reads, and query load. It can also increase response times when many users open the same dashboard.

The query result cache can help with this workload. It stores results from eligible queries and can return them again without running the full query.

This article explains how to inspect query cache activity and enable result caching for dashboard users. It also covers the freshness and memory limits that you must consider.

Why it happens

A dashboard may refresh every 30 seconds. If the query does not change, ClickHouse may perform the same work on every refresh.

For example, a dashboard may calculate total sales for a specific date range. It may scan the same table, apply the same filters, and calculate the same total many times.

ClickHouse has a query result cache for eligible SELECT queries. When the cache contains a matching result, ClickHouse can return that result instead of executing the query again.

The cache does not apply automatically to every query. You must enable it with theuse_query_cache setting. The query must also meet the cache's eligibility rules - for example, queries that use non-deterministic functions like now() or today() are not cached by default.

ClickHouse also provides two other caches:

  • Query condition cache: Stores condition results for eligible data granules.
  • Uncompressed cache: Stores decompressed data blocks.

These caches serve different purposes. For repeated dashboard queries, the query result cache is the main setting to consider.

One important risk is stale data. ClickHouse may return an older cached result until its TTL expires.

How to confirm

First, check whether the query result cache contains entries.

Run this query:

SELECT
    *
FROM system.query_cache;

This table shows the entries currently stored in the query cache. It can help you inspect cached queries, result sizes, expiration times, and stale entries.

The available columns can differ between ClickHouse versions. Check the table structure on your server before selecting individual columns.

Next, check the query cache hit and miss counters in system.events.

SELECT
    event,
    value
FROM system.events
WHERE event IN
(
    'QueryCacheHits',
    'QueryCacheMisses'
)
ORDER BY event;

A hit means ClickHouse used a cached query result. A miss means the query did not get its result from the query cache.

These counters are cumulative since the server started. Compare them over a representative dashboard workload. A high miss count alone does not prove that the cache is too small. The queries may not be eligible for caching, or users may not have enabled it.

You can also inspect the counters recorded for completed queries in system.query_log. The ProfileEvents column stores profile event values for each query.

SELECT
    event_time,
    query_duration_ms,
    ProfileEvents['QueryCacheHits'] AS cache_hits,
    ProfileEvents['QueryCacheMisses'] AS cache_misses,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
  AND query_kind = 'Select'
ORDER BY event_time DESC
LIMIT 20;

Check whether the query cache settings are enabled for the dashboard user.

SELECT
    name,
    value,
    changed
FROM system.settings
WHERE name IN
(
    'use_query_cache',
    'query_cache_ttl',
    'use_query_condition_cache',
    'use_uncompressed_cache'
);

This query shows the current session values. It does not show every user's settings or prove that a dashboard query used the cache.

The settings

The following defaults may differ according to your ClickHouse versions [26.8 LTS]. Verify them on your server.

SettingDefaultWhat it controlsRestart needed
use_query_cachefalse (0)Enables query result caching for eligible queriesNo
query_cache_ttl60 secsSets how long a cached query result remains validNo
use_query_condition_cachetrue (1)Enables the query condition cacheNo
use_uncompressed_cachefalse (0)Enables the uncompressed data cacheNo

These are session or query settings. You can apply them through a settings profile, a session using SET, or one query using SETTINGS.

Check the actual defaults and values on your server.

SELECT
    name,
    value,
    changed
FROM system.settings
WHERE name IN
(
    'use_query_cache',
    'query_cache_ttl',
    'use_query_condition_cache',
    'use_uncompressed_cache'
);

How to fix it

Enable the query result cache only for users who run dashboard queries. Do not enable it for every user without checking their workload.

Step 1: Choose a TTL that matches the dashboard refresh rate

First, check how often the dashboard refreshes.

For example, assume the dashboard refreshes every 30 seconds. Start with a TTL of 30 seconds. This allows ClickHouse to reuse a result during that refresh interval.

A shorter TTL gives fresher results but may reduce cache hits. A longer TTL can improve reuse but increases the time that users may see old results.

Step 2: Create a dashboard settings profile

Create a profile in a file such as:

/etc/clickhouse-server/users.d/dashboard-profile.xml

Add this profile:

<clickhouse>
    <profiles>
        <dashboard_profile>
            <use_query_cache>1</use_query_cache>
            <query_cache_ttl>30</query_cache_ttl>
        </dashboard_profile>
    </profiles>
</clickhouse>

This profile enables query caching and sets the TTL to 30 seconds.

Assign dashboard_profile to the dashboard user in the existing user configuration. Do not replace other user settings when you make this change.

For example, add the profile reference to the dashboard user's existing configuration:

<users>
    <dashboard_user>
        <profile>dashboard_profile</profile>
    </dashboard_user>
</users>

Use the actual dashboard username from your server. If the user already has a profile, update the existing configuration carefully.

These settings do not require a server restart. Reload the configuration after you save the file. Reconnect the dashboard user so new sessions receive the updated profile.

Step 3: Enable caching for one query when needed

You can also test the query cache on one dashboard query before applying a profile.

SELECT
    toDate(event_time) AS event_date,
    count() AS total_events
FROM events
GROUP BY event_date
ORDER BY event_date
SETTINGS
    use_query_cache = 1,
    query_cache_ttl = 30;

This example enables caching for the query and sets a 30-second TTL. Replace the table and columns with the actual dashboard query.

The first execution may populate the cache. A later eligible execution can reuse the cached result.

Step 4: Review the server-side cache size

The query result cache also has server-side capacity limits. Check settings such as query_cache_max_size_in_bytes and query_cache_max_entries on your ClickHouse version.

These settings control the memory available to cached results and the number of cache entries. A small limit can cause entries to leave the cache before their TTL expires.

Inspect the server settings:

SELECT
    name,
    value,
    changed
FROM system.server_settings
WHERE name LIKE 'query_cache.%';

Do not increase these limits without checking available server memory. The correct values depend on the number of dashboard queries and their result sizes.

How to verify the fix

Run the same dashboard query twice with the same user and query settings.

The first execution may miss the cache and store the result. The next execution should be able to use that cached result if the query is eligible and the entry has not expired.

Check the counters again:

SELECT
    event,
    value
FROM system.events
WHERE event IN
(
    'QueryCacheHits',
    'QueryCacheMisses'
)
ORDER BY event;

Compare the values before and after the repeated query. Look for an increase in QueryCacheHits.

You can also inspect the cache entries:

SELECT
    *
FROM system.query_cache;

Note: If you need to clear the cache manually during testing, run SYSTEM CLEAR QUERY CACHE;.

Finally, compare dashboard response times before and after the change. Use the same query parameters and similar data conditions.

A successful result should show cache hits and lower response times for repeated eligible queries. Confirm that the dashboard also displays data within the required freshness period.

Risks and trade-offs

The main risk is stale data. ClickHouse can return a cached result even after the source table changes. The result remains available until its TTL expires or the cache entry is removed.

Choose the TTL based on how fresh the dashboard data must be. Do not use a long TTL for dashboards that support operational decisions requiring current data.

Also consider these points:

  • Query eligibility : Queries using non-deterministic functions (such as now(), today(), or rand()) are skipped by default unless you adjust nondeterministic_function_handling.
  • Node-local storage : The query result cache is stored locally on each ClickHouse server. If your dashboard load-balances requests across multiple replicas, each node maintains its own cache independently.
  • Parameter sensitivity: Queries with different parameters, comments, or whitespace may create separate cache entries.
  • Memory limits: Large results can exceed the default per-entry size limit (1 MiB) or consume significant server memory if limits are raised.
  • TTL balance: A short TTL can reduce cache reuse, while a long TTL makes dashboard data less current.
  • User Isolation: Other users should not receive dashboard-specific cache settings unless their workload needs them.

Test the change with real dashboard queries. Confirm both response time and data freshness before applying it to more users.

Summary

  • Repeated dashboard queries can repeat the same scans and calculations.
  • Use use_query_cache to enable result caching for eligible queries.
  • Set query_cache_ttl to match the dashboard refresh rate.
  • Inspect system.query_cache and the QueryCacheHits and QueryCacheMisses events.
  • Monitor cache memory and data freshness after enabling the cache.
Share: