Skip to main content
    • Knowledge Base/
    Check Query Cache In Use

    How to Verify Query Cache Usage in ClickHouse

    Learn how to check if query cache is being utilized in ClickHouse using `clickhouse-client` trace logs or SQL commands.

    How can I check that query cache is being used in my query?​

    See this example using clickhouse client and ClickHouse Cloud service.

    create a query_cache_test table

    Using clickhouse client​

    fill the table with some data:

    enable trace logs:

    run a query asking to make use of query cache (appending SETTINGS use_query_cache=true to the query):

    run the same query again:

    Now observe the differences in the TRACE logs related to QueryCache between,

    1st execution:

    at 2nd execution:

    In the 1st execution, no entry was obviously found (No entry found for query SELECT...), so ClickHouse did store (Stored result of query SELECT...) the entry for us.

    In the 2nd execution, they query made use of they query cache as it found the entry already stored (Entry found for query SELECT...).

    Using just SQL​

    Just through issuing SQL commands without inspecting the clickhouse client trace logs,

    it is also possible to validate if query cache is being used by checking the relevant system tables:

    In the last results we see 1 QueryCacheMisses for the first time the query SELECT 1 SETTINGS use_query_cache=true; ran and a QueryCacheHits event related to the second execution of the query.

    Keep also in mind that the default maximum cache entry size is 1048576 bytes (= 1 MiB) and by default results are stored in cache for 60 seconds only (you can use query_cache_ttl=300 in SETTINGS for example to have a query cache result stored for 5 minutes instead).

    You can find more detailed info on ClickHouse Query Cache here

    · 7 min read