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

AnalyticDB for MySQL provides a result set cache to reduce unnecessary computations. This feature helps lower the load on your cluster and accelerate queries. This topic describes the limits and usage of the result set cache in AnalyticDB for MySQL.

Prerequisites

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

If the table engine is XUANWU, the kernel version of the cluster must be 3.2.1 or later.
If the table engine is XUANWU_V2, the kernel version of the cluster must be 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.

Feature description

The result set cache stores the results of a query in the local storage of the AnalyticDB for MySQL access layer. When the same query is submitted again, AnalyticDB for MySQL returns the cached results directly to the client if the data in the associated tables has not changed. This prevents the query from being recalculated.

Scenarios

Data is written at fixed intervals or during specific periods. For most of the time, the data remains static with no updates or writes.
Queries are repetitive. This means the client sends the same query multiple times within a short period.

Notes

Because of the limitations of the current multi-primary architecture, when data in the underlying tables is updated, the updates must be synchronized from the storage layer to the access layer. The updates are then synchronized between the inner nodes of the access layer. This synchronization process can cause a data update latency of up to 10 seconds. Therefore, the result set cache cannot guarantee strong consistency. However, it does guarantee eventual consistency. This means that after a data update, the related cache is always evicted within 10 seconds. The next time the query is run, the new result set is cached.

Limits

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

The syntax of the query statement is identical to that of a previously cached query statement.
The data in the associated tables has not been changed by additions, deletions, or updates.
The query does not use non-deterministic functions, such as NOW or CURRENT_TIMESTAMP.
The query does not use external data sources or internal system tables.
The tables used in the query comply with the corresponding access controls when blacklists and whitelists are enabled.
The size of the result set does not exceed the row count threshold. By default, a result set of up to 10,000 rows can be cached. If the result set exceeds this threshold, it is not cached.
The query execution time is longer than 1 s.
The query does not use a Common Table Expression (CTE) subquery.

Enable the result set cache

You can enable the result set cache in one of the following two 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 cache at the cluster level causes the system to attempt to cache the result set for every request. This can increase the system load, especially in high-concurrency scenarios. To reduce the additional cache overhead, configure blacklist and whitelist access controls after you enable the cache at the cluster level. For more information, see Configure blacklist and whitelist access controls.

Blacklist and whitelist access controls

Enabling the result set cache at the cluster level applies to all queries. This can add significant overhead. Conversely, enabling the cache for individual queries is too granular and requires extensive manual changes. Therefore, when you enable the result set cache at the cluster level, you can use blacklist and whitelist access controls to specify which tables can use the cache.

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

Parameters:

disable (default): Does not use blacklist and whitelist access controls.
whitelist: Uses whitelist access controls.
blacklist: Uses blacklist access controls.

When you enable blacklist and whitelist access controls, you can adjust the table-level cache property RESULT_CACHE to implement different access controls.

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

If blacklist or whitelist access controls are not enabled, the `RESULT_CACHE` table property has no effect. All queries that meet the limits can use the result set cache.
When blacklist access controls are enabled, if the `RESULT_CACHE` property of a table is set to `default` or `enable`, queries that involve this table are allowed to use the result set cache. If the value is `disable`, queries that involve this table are not allowed to use the result set cache.
When whitelist access controls are enabled, queries that involve a table are allowed to use the result set cache only if the `RESULT_CACHE` property of the table is set to `enable`. If the value is `default` or `disable`, queries that involve this table are not allowed to use the result set cache.

You can run the following SQL statement to view the tables that have blacklist and whitelist access controls configured.

SELECT * FROM INFORMATION_SCHEMA.RESULT_CACHE_LIST_CONSTRAINT;

Result set cache size limits

The data volume of a result set cache is limited by the number of rows specified by RESULT_CACHE_MAX_ROW_COUNT. If the number of rows exceeds this threshold, the result set is not cached.

You can configure the row count threshold for the result set cache. The default value is 10,000.

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 manually manage the result set cache. AnalyticDB for MySQL automatically evicts expired result set caches based on eviction policies. AnalyticDB for MySQL supports two eviction policies: timeout-based eviction and maximum cache count-based eviction. A cached result set is evicted if it meets the criteria of either policy.

The timeout-based eviction policy automatically deletes result set caches that have not been accessed within a specified timeout period. The timeout period is set by the RESULT_CACHE_EXPIRATION_TIME parameter, which has a default value of 86400 seconds (s).
You can configure the timeout-based eviction policy.
```
SET ADB_CONFIG RESULT_CACHE_EXPIRATION_TIME = 86400;
```
The maximum cache count-based eviction policy deletes the least recently used cache when the number of caches on an access node exceeds the limit set by RESULT_CACHE_MAX_CACHE_COUNT. The default value is 100.
You can configure the maximum cache count-based eviction policy.
```
SET ADB_CONFIG RESULT_CACHE_MAX_CACHE_COUNT = 100;
```