Starting from V3.1, Hologres supports the query cache feature. This feature allows query results to be cached in the memory. When the system encounters identical or similar queries later, it can match aggregation results from the memory first, avoiding data reading and computation overhead, thereby improving query performance. Query cache is suitable for scenarios with high concurrency and repetitive queries (such as stress testing and BI reports) and can significantly optimize query efficiency and reduce resource waste.
Precautions
The query cache feature is only supported in Hologres V3.1 and later versions.
NoteIf your instance version is too early, upgrade the instance or join the Hologres DingTalk group to request an instance upgrade. For more information about how to join the online DingTalk group, see Obtain online support for Hologres.
Query cache is disabled by default. You need to enable it by executing the following statement before you can use it:
-- Enable the query cache feature. Only a Superuser can execute this statement. This feature takes effect for new connections after you enable it. ALTER DATABASE <database_name> SET hg_experimental_enable_query_cache=on;Query cache results have weak consistency. If data is written during the cache period, the correctness of results is not guaranteed.
Query results containing the RANDOM function will not be cached.
Use query cache
Memory in Hologres is roughly divided into three parts: query, cache, and metadata. The cache memory accounts for about 30% of the total instance memory. Taking the memory allocation of a single worker as an example, if the memory limit of a single worker is 64 GB, the cache memory limit is 30%, which is about 20 GB. The memory used by the query cache feature is part of the cache memory.
query_cache_percentage for allocating memory resources for query cache
When using the query cache feature, you must allocate corresponding memory resources for it. Otherwise, the query cache feature cannot be fully utilized.
Memory allocated for a single worker = 20 GB × query_cache_percentage × 0.01
Total allocated memory in the instance = Memory allocated for a single worker × Number of workers
-- Example: The allocated memory is 400 MB (formula: 20 GB × query_cache_percentage × 0.01).
SELECT hg_admin_command('set_global_flag', 'query_cache_percentage=2');Set the maximum memory limit for a single cache result
The maximum memory limit for a single cache result is 10 MB by default. You can modify it using the following statement. The upper limit is 20 GB.
-- Adjust the maximum memory limit for a single cache result (10 MB by default).
ALTER DATABASE <database_name> SET hg_experimental_query_cache_max_kb_per_item=10240;Adjust the expiration time for query cache
The cache in Hologres has an eviction policy, with algorithms including memory limits and a retention period. You can modify the expiration time using the following statement.
The default expiration time for the query cache feature is 60 seconds.
The unit is seconds.
-- Adjust the expiration time, in seconds. The default value is 60 seconds.
ALTER DATABASE <database_name> SET hg_experimental_query_cache_expire_time_sec=60;