Limits and usage of the result set cache - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL provides a result set cache to avoid redundant computations and accelerate queries. This topic describes the limits and usage of the result set cache in AnalyticDB for MySQL.

Prerequisites

The kernel version of AnalyticDB for MySQL must meet the following requirements:

If the table engine is XUANWU, the cluster must run Milvus version 3.2.1 or later.
If the table engine is XUANWU_V2, the cluster must run Milvus version 3.2.3.11 or later.

Note

To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

Overview

The result set cache stores query results in the local storage of the access layer. When the same query is run again, AnalyticDB for MySQL can return the results directly from the cache if the underlying table data is unchanged. This avoids the need to recompute the query.

Scenarios

Data is written during specific periods and remains unchanged most of the time.
The same queries are run repeatedly within a short period.

Usage notes

The multi-master architecture has a limitation. When underlying table data is updated, the changes are synchronized from the storage layer to the access layer nodes. This synchronization can take up to 10 seconds. Therefore, the result set cache cannot guarantee strong consistency but does guarantee eventual consistency. This means that within 10 seconds of a data update, the relevant cache is invalidated. The next time the query runs, it caches the latest data.

Limits

A query can use the result set cache only if it meets all the following conditions:

The query statement is syntactically identical to a previously cached query.
No data has been added to, deleted from, or updated in the referenced tables.
The query does not use non-deterministic functions, such as NOW and CURRENT_TIMESTAMP.
The query does not use external data sources or internal system tables.
If blacklists and whitelists are enabled, the tables in the query meet the access rules.
The result set size does not exceed the row count threshold. The default threshold is 10,000 rows. If a result set exceeds the threshold, it is not cached.
The query's running time is longer than 1 second.

Enable the result set cache

You can enable the result set cache in one of the following ways:

Use a hint to enable the result set cache for a specific query.

/*+result_cache=true*/ SELECT * FROM tpcds.catalog_returns WHERE cr_item_sk < 100;

Modify a cluster-level configuration item to enable the result set cache for all queries.
```
SET ADB_CONFIG RESULT_CACHE_APPLY_ALL=true;
```
Warning
Enabling the result set cache at the cluster level causes AnalyticDB for MySQL to attempt to cache results for all requests. This increases the load in high-concurrency scenarios. To reduce this extra cache overhead, enable the cache at the cluster level and then configure blacklists and whitelists. For more information, see Configure blacklists and whitelists.

Blacklists and whitelists

Enabling the result set cache at the cluster level applies it to all queries, which can increase the load. Enabling it for individual queries is too granular and requires manual changes. A better approach is to enable the cache at the cluster level and then use blacklists and whitelists to define which tables can use the result set cache.

SET ADB_CONFIG RESULT_CACHE_LIST_CONSTRAINT_MODE=[whitelist | blacklist | disable];

Parameters:

disable (default): Disables blacklists and whitelists.
whitelist: Enables the whitelist.
blacklist: Enables the blacklist.

When you enable the blacklist or whitelist policy, you can use the RESULT_CACHE parameter to specify different constraints for different tables.

ALTER TABLE <table_name> RESULT_CACHE=[default | enable | disable];

If blacklists and whitelists are disabled, the RESULT_CACHE table property has no effect. All queries that meet the usage limits can use the result set cache.
When the blacklist is enabled, queries that reference a table can use the cache if the table's RESULT_CACHE property is set to default or enable. If the property is set to disable, queries that reference that table cannot use the cache.
When the whitelist is enabled, queries that reference a table can use the cache only if the table's RESULT_CACHE property is set to enable. If the property is default or disable, queries that reference that table cannot use the cache.

You can run the following SQL statement to view tables that have blacklist or whitelist rules configured.

SELECT * FROM INFORMATION_SCHEMA.RESULT_CACHE_LIST_CONSTRAINT;

Result set cache size limit

The size of the cached result set is limited by the row count threshold specified by the RESULT_CACHE_MAX_ROW_COUNT parameter. If a result set exceeds the threshold, it is not cached.

You can set the row count threshold for the result set cache. The default value is 10000.

SET ADB_CONFIG RESULT_CACHE_MAX_ROW_COUNT = 10000;

View all result set caches

You can run the following SQL statement to view all cached result sets.

SELECT * FROM INFORMATION_SCHEMA.RESULT_CACHE_STATUS;

Result set cache eviction policies

You do not need to manage the result set cache manually. AnalyticDB for MySQL automatically evicts expired cache entries based on eviction policies. AnalyticDB for MySQL supports two eviction policies: timeout and maximum cache count. A cache entry is evicted if it meets the condition of either policy.

Cache entries that are not accessed within the expiration period are automatically evicted. The expiration period is specified by the RESULT_CACHE_EXPIRATION_TIME parameter. Default value: 86400. Unit: seconds.
You can set the timeout eviction policy.
```
SET ADB_CONFIG RESULT_CACHE_EXPIRATION_TIME = 86400;
```
If the maximum number of cache entries for a single access node is reached, the least recently accessed cache entries are automatically evicted. The maximum number of cache entries for a single access node is specified by the RESULT_CACHE_MAX_CACHE_COUNT parameter. Default value: 100.
You can set the maximum cache count eviction policy.
```
SET ADB_CONFIG RESULT_CACHE_MAX_CACHE_COUNT = 100;
```