All posts
Handling Repeated Slow Queries in ClickHouse

Handling Repeated Slow Queries in ClickHouse

November 12, 20269 min readReshma M
Share:

1. The problem

Repeated queries can remain slow even when they access the same data. Disk reads also remain high, which can affect dashboard and application performance.

One possible cause is limited cache capacity. When frequently used marks and index entries do not stay in memory, ClickHouse must load them from storage again. ClickHouse provides several caches for metadata and data blocks. Each serves a different purpose, so the correct cache must be identified before changing its size.

This article covers how to inspect cache usage, identify capacity issues, and tune the relevant settings. It starts with the mark cache and primary index cache, which are the first areas to investigate for repeated metadata reads.

2. Why it happens

ClickHouse uses the MergeTree family of table engines to store data in parts. Each part contains data files, marks, and index information.

When a query reads a table, ClickHouse uses marks to locate the required data ranges. It also uses index information to identify which data parts and granules may contain matching rows.Reading this metadata from storage repeatedly can increase query latency. ClickHouse keeps frequently used metadata in memory to reduce these reads.

However, each cache has a configured size limit. When the cache reaches that limit, ClickHouse removes entries to make room for new ones. A later query may need an entry that has already been removed.

The following caches have different purposes:

  • Mark cache: Stores marks for MergeTree data parts. Marks help ClickHouse locate data ranges in column files.
  • Primary index cache: Stores primary index data loaded on demand when the relevant primary-key cache features are enabled.
  • Index mark cache: Stores marks for secondary data-skipping indexes.
  • Uncompressed cache: Stores decompressed data blocks. It can help when many short queries read the same data.
  • Query condition cache: Stores results of evaluating query conditions for eligible data granules. Repeated conditions may reuse these results.
  • Page cache: Stores data pages in a ClickHouse-managed userspace cache. It can reduce repeated reads from storage. A cache does not make every query faster. Its benefit depends on the workload, the data access pattern, and the amount of reusable data.

3. How to confirm

Start by checking the mark cache hit rate. ClickHouse records cache hits and misses in system.events.

A hit means ClickHouse found the required entry in the mark cache. A miss means ClickHouse had to load the mark information again.

Run this query:

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

The values are cumulative counters since the server started. Use them to calculate the mark cache hit rate.

SELECT
    sumIf(value, event = 'MarkCacheHits') AS hits,
    sumIf(value, event = 'MarkCacheMisses') AS misses,
    round(
        hits / nullIf(hits + misses, 0) * 100,
        2
    ) AS hit_rate_percent
FROM system.events
WHERE event IN ('MarkCacheHits', 'MarkCacheMisses');

A low hit rate can indicate that the mark cache is too small. However, it does not prove that cache size is the only problem. A workload that reads new data frequently can also produce many misses.

Compare the counters during a representative workload. Do not judge the cache from a single query or a short period.

Next, check the current cache usage in system.asynchronous_metrics.

SELECT
    metric,
    value
FROM system.asynchronous_metrics
WHERE metric IN
(
    'MarkCacheBytes',
    'PrimaryIndexCacheBytes',
    'IndexMarkCacheBytes',
    'UncompressedCacheBytes',
    'QueryConditionCacheBytes',
    'PageCacheBytes'
)
ORDER BY metric;

These metrics report the memory currently used by the corresponding caches. Some metrics may not be available in every release. Check the available metric names on your server before using them.

You can also inspect the configured server settings:

SELECT *
FROM system.server_settings
WHERE name IN
(
    'mark_cache_size',
    'primary_index_cache_size',
    'index_mark_cache_size',
    'uncompressed_cache_size',
    'query_condition_cache_size',
    'page_cache_max_size',
    'cache_size_to_ram_max_ratio'
);

Check the configured values and whether they differ from the defaults. The system.server_settings table reports server-level settings. Use system.settings for session-level settings.

4. The settings

These default settings may differ in ClickHouse 26.8 LTS. Verify them on your server before making changes.

SettingDefaultWhat it controlsRestart needed
mark_cache_size5 GiBMaximum size of the mark cacheNo
primary_index_cache_size5 GiBMaximum size of the primary index cacheNo
index_mark_cache_size5 GiBMaximum size of the secondary index mark cacheNo
uncompressed_cache_size0 (disabled)Maximum size of the uncompressed data cacheNo
query_condition_cache_size100 MiBMaximum size of the query condition cacheNo
page_cache_max_size0 (disabled)Maximum size of the userspace page cacheYes
cache_size_to_ram_max_ratio0.5Limits cache size based on available RAMYes

Check the defaults on your own server. Do not copy values from another ClickHouse release.

5. How to fix it

Increase cache sizes in a controlled way. Start with the mark cache. Then check the results before increasing the primary index cache.

Use the following configuration examples as starting points. Adjust the values to match your server memory and workload.

Step 1: Increase the mark cache

The mark cache stores marks for MergeTree data parts. A larger cache can reduce repeated reads of mark files.

Create a configuration file on the ClickHouse server:

/etc/clickhouse-server/config.d/cache-settings.xml

Add the following configuration:

<clickhouse>
    <mark_cache_size>10737418240</mark_cache_size>
</clickhouse>

This sets the mark cache to 10 GiB. The example doubles the 5 GiB master-branch default. Use this value only if your server has enough memory.

No Restart required, Reload the server configuration after the change: SYSTEM RELOAD CONFIG. Monitor the mark cache hit rate and memory usage after the reload.

Step 2: Increase the primary index cache

The primary index cache stores primary index data loaded on demand. Its use depends on the primary-key cache features enabled in your ClickHouse version and table configuration.

If the primary index cache is active and its usage indicates a capacity problem, increase its size.

Update the same configuration file:

<clickhouse>
    <primary_index_cache_size>10737418240</primary_index_cache_size>
</clickhouse>

This example sets the primary index cache to 10 GiB. It provides more room for frequently accessed primary index data.

No Restart required, Reload the server configuration: SYSTEM RELOAD CONFIG. Do not increase this setting without checking whether your workload uses the primary index cache. Confirm its actual usage through the available metrics.

Step 3: Increase the secondary index mark cache when needed

The secondary index mark cache stores marks for data-skipping indexes. Increase it only when queries use these indexes and monitoring shows that the cache needs more capacity.

For example, set it to 2 GiB:

<clickhouse>
   <index_mark_cache_size>2147483648</index_mark_cache_size>
</clickhouse>

This value is an example, not a universal recommendation.

No Restart required, Reload the server configuration : SYSTEM RELOAD CONFIG

Step 4: Enable the uncompressed cache for suitable workloads

The uncompressed cache stores decompressed data blocks from MergeTree tables. It can help when many short queries read the same data repeatedly.

It does not usually improve long-running analytical queries. Do not enable it only because disk reads are high.

For a workload with repeated short queries, test a 1 GiB cache:

<clickhouse>    
   <uncompressed_cache_size>1073741824</uncompressed_cache_size>
</clickhouse>

This sets the cache capacity to 1 GiB. It does not guarantee that queries will use the cache. Confirm the benefit with workload tests.

Restart required: No. Reload the server configuration: SYSTEM RELOAD CONFIG

Step 5: Review the other cache settings

The query condition cache can help repeated queries that evaluate the same conditions. Test its impact before increasing its size.

For example, set it to 200 MiB:

<clickhouse>
    <query_condition_cache_size>209715200</query_condition_cache_size>
</clickhouse>

The page cache is a separate userspace cache. Its maximum size is controlled by page_cache_max_size. The supplied default is zero, which disables it.

The cache_size_to_ram_max_ratio setting controls the cache size limit relative to available RAM. Its supplied default is 0.5.

These two settings require a server restart according to the target settings list. Do not change them as part of the first tuning step. Test them separately and review their memory impact.

6. How to verify the fix

After changing the cache settings, run the same queries under a similar workload. Compare the results with your baseline.

Check the mark cache hit rate again:

SELECT
 sumIf(value, event = 'MarkCacheHits') AS hits,
 sumIf(value, event = 'MarkCacheMisses') AS misses,
 round(
 hits / nullIf(hits + misses, 0) * 100,
 2
 ) AS hit_rate_percent
 FROM system.events
 WHERE event IN ('MarkCacheHits', 'MarkCacheMisses');

Check the current cache usage:

SELECT
    metric,
    formatReadableSize(value) AS cache_size
FROM system.asynchronous_metrics
WHERE metric IN
(   'MarkCacheBytes',
    'PrimaryIndexCacheBytes',
    'IndexMarkCacheBytes',
    'UncompressedCacheBytes',
    'QueryConditionCacheBytes'
)
ORDER BY metric;

Compare query duration and read volume before and after the change. Use the same query parameters and similar data volumes.

A successful change should reduce repeated metadata reads and improve query latency. The cache usage should also remain within your planned memory budget. Do not use a higher hit rate as the only success measure. Confirm that the queries are faster and that disk reads have decreased.

7. Risks and trade-offs

Larger caches use more memory. ClickHouse cache memory counts toward the server memory limit. This memory is not separate from the memory available to queries and other server operations.

If you allocate too much memory to caches, queries may have less memory available. The server may also reach its memory limit more often.

Review the server memory limit before increasing cache sizes. Check memory usage during peak workloads, not only when the server is idle.

Also consider these points:

  • A larger cache does not fix inefficient queries or poor table design.
  • A high mark cache hit rate does not guarantee low query latency.
  • The uncompressed cache can use memory without helping long analytical queries.
  • The page cache and query condition cache need workload-specific testing.
  • Cache sizes should match the working set, not the total size of all stored data. If memory pressure increases after a change, reduce the cache size and test again.

8. Summary

  • Repeated slow queries can indicate that frequently used metadata does not stay in memory.
  • Check MarkCacheHits and MarkCacheMisses to calculate the mark cache hit rate.
  • Increase mark_cache_size first, then review primary_index_cache_size.
  • Use the uncompressed cache only when repeated short queries benefit from it.
  • Monitor cache memory because it counts toward the server memory limit.

Further Readings

Share: