DocsICP FilingConsole
Documentation
Home Page ApsaraDB for SelectDB User Guide Developer Guide Data table design Data cache

Data cache

Updated at:
Copy as MD
Product
My Favorites

Manage cached data and improve query performance inApsaraDB for SelectDB by configuring LRU and TTL policies and using cache prefetch to preload data for specific tables or partitions.

Background

ApsaraDB for SelectDB uses two types of cache: in-memory cache and data cache. They differ in their contents and management policies.

  • In-memory cache

    • Definition: The in-memory cache is a cache space allocated in the memory of aApsaraDB for SelectDB compute node. It temporarily stores hot data, such as frequently accessed data blocks or metadata.

    • Cached content:

      • Metadata: Table structures, partition information, statistics, and more.

      • Data blocks: Columnar storage blocks of hot data, such as Parquet or ORC file blocks.

    • Management policies:

      • Best-effort: When memory is limited, cold data is evicted first.

      • LRU (Least Recently Used): A common eviction algorithm that ensures frequently accessed data remains in memory.

  • Data cache (based on cloud disk)

    • Definition: The data cache is a persistent cache layer thatApsaraDB for SelectDB allocates on a cloud disk (such as an SSD). It serves as a secondary cache between memory and object storage (such as S3) and stores larger volumes of warm data.

    • Cached content:

      The data cache primarily stores columnar data blocks loaded from object storage, along with auxiliary structures like column indexes and dictionary encodings.

      Data is populated into the data cache through the following operations:

      • Data import: Newly imported data is asynchronously written to the cache to accelerate initial access.

      • Data query: If queried data is not in the cache, ApsaraDB for SelectDB reads it from remote storage into memory and then writes it to the local disk cache for future queries.

      • New cluster creation: Data in remote storage can be shared across multiple clusters, but the cache is not shared. When you create a new cluster, its cache is empty. You can use cache prefetch to proactively load required data from remote storage into the local cache. For an example, see Example: Cache prefetch.

    • Management policies:

      • Tiered storage: The system automatically migrates data based on access frequency. Hot data is stored in the cloud disk cache, while cold data remains in object storage.

      • Cache prefetch: You can manually or automatically pre-load data that is expected to be accessed.

This topic focuses on the cloud disk-based data cache.

Cache policies

TheApsaraDB for SelectDB data cache supports the LRU (Least Recently Used) policy and the TTL (Time To Live) policy. Control the cache policy for a table by adjusting its properties.

Cache policy

Use cases

Description

LRU policy

The LRU policy isApsaraDB for SelectDB.

This policy is suitable for most use cases.

Under this policy, data is maintained in a queue.

When a query hits a data block, the system moves that block to the front of the queue.

New data is also placed at the front to prevent premature eviction.

When the cache is full, the system evicts data from the end of the queue first.

TTL policy

Use this policy when the total data volume significantly exceeds the cache capacity and some data has a higher priority than other data.

For example, your database contains 10 tables with a total data size of 1 TB, but your cache capacity is only 200 GB, which is not enough to cache all the data. If one table is accessed far more frequently than the others, you can apply the TTL policy to that table.

The TTL policy prevents the eviction of high-priority data from the cache for a specific period after it is written. Its expiration time is calculated as follows:Expiration Time = Import Time + Timeout

Data with a TTL policy has the highest priority in cache management, and all such data shares the same priority level. "Highest" means that if the cache is full, the system evicts data from the LRU queue to make space for data with a TTL policy. "Equal" means that among all data with a TTL policy, the expiration time is not a factor for eviction.

If data with a TTL policy fills the cache, no new data is written to the cache, regardless of whether it is newly imported data (with or without a TTL) or cold data read from remote storage.

Important

This is an experimental feature and is not recommended for use in production environments.

Cache prefetch

AnApsaraDB for SelectDB instance consists of one or more clusters. These clusters share stored data but not cache data. A new cluster starts with an empty cache, which can slow initial queries. Use cache prefetch to proactively load data from remote storage into the local cache.

Three prefetch modes are supported:

  • Prefetch data for a target cluster based on the hot data of a source cluster:ApsaraDB for SelectDB periodically collects information about hot data in the tables or partitions of each cluster and stores this information in an internal table. During prefetch, the target cluster uses the hot data information from the source cluster to prefetch data for specific tables or partitions.

  • Prefetch data from a specific table to a cluster.

  • Prefetch data from a specific partition of a table to a cluster.

Cache space management

Set cache space size

You can specify the cache space size when you create anApsaraDB for SelectDB instance or cluster. For more information, see Create an instance.

View cache space size

Log on to the SelectDB console. On the Instances page, click the Instance ID of your target instance to go to the Instance Details page. Then, click Cluster Management to view the Cache Size.

Example: Cache prefetch

The following example prefetches data for cluster cluster_name1 based on hot data in cluster cluster_name0, demonstrating all three prefetch modes.

Step 1: Identify hot data

  1. List all clusters in the instance that have hot data information.

    SHOW CACHE HOTSPOT '/';

    The following result shows that cluster_name0 is a cluster with hot data information.

    SHOW CACHE HOTSPOT '/';
+------------------------+-----------------------+----------------------------------------+
| cluster_name           | total_file_cache_size | top_table_name                         |
+------------------------+-----------------------+----------------------------------------+
| cluster_name0          |          751620511367 | regression_test.selectdb_cache_hotspot |
+------------------------+-----------------------+----------------------------------------+

  2. View the hot data information for cluster_name0.

    SHOW CACHE HOTSPOT '/cluster_name0';

    The following result shows that p20230529 in the top_partition_name column is a partition with hot data. It is a partition of the customer table.

    +-----------------------------------------------------------+---------------------+--------------------+
| table_name                                                | last_access_time    | top_partition_name |
+-----------------------------------------------------------+---------------------+--------------------+
| regression_test.selectdb_cache_hotspot                    | 2023-05-29 12:38:02 | p20230529          |
| regression_test_cloud_load_copy_into_tpch_sf1_p1.customer | 2023-06-06 10:56:12 | p20230529          |
| regression_test_cloud_load_copy_into_tpch_sf1_p1.nation   | 2023-06-06 10:56:12 | nation             |
+-----------------------------------------------------------+---------------------+--------------------+

  3. View information about the hot partitions in the customer table.

    Note

    If a table has only one partition, the partition name is the same as the table name.

    SHOW CACHE HOTSPOT '/cluster_name0/regression_test_cloud_load_copy_into_tpch_sf1_p1.customer';

    The command returns the following result.

    +----------------+---------------------+
| partition_id   | partition_name      |
+----------------+---------------------+
| 422831494463   | p20230529           |
+----------------+---------------------+

Step 2: Create prefetch job

The following examples demonstrate the three cache prefetch modes.

Important

A cluster can run only one prefetch job at a time.

  • Prefetch data for cluster_name1 based on the hot data in cluster_name0.

    WARM UP CLUSTER cluster_name1 WITH CLUSTER cluster_name0

  • Prefetch data from the customer table into cluster_name1.

    WARM UP CLUSTER cluster_name1 WITH TABLE customer

  • Prefetch data from the p20230529 partition of the customer table into cluster_name1.

    WARM UP CLUSTER cluster_name1 WITH TABLE customer PARTITION p20230529

Step 3: Manage prefetch job

  • Get the job ID of the prefetch job.

    WARM UP CLUSTER cluster_name1 WITH TABLE customer;
+-------+
| JobId |
+-------+
| 13418 |
+-------+
1 row in set (0.01 sec)

  • Check the progress of the prefetch job.

    You can check the job progress based on the FinishBatch and AllBatch values. Each batch processes approximately 10 GB of data.

    SHOW WARM UP JOB WHERE ID = 13418;

    The following result is returned.

    +-------+-------------------+---------+-------+-------------------------+-------------+----------+------------+
| JobId | ClusterName       | Status  | Type  | CreateTime              | FinishBatch | AllBatch | FinishTime |
+-------+-------------------+---------+-------+-------------------------+-------------+----------+------------+
| 13418 | cluster_name1     | RUNNING | TABLE | 2023-05-30 20:19:34.059 | 0           | 1        | NULL       |
+-------+-------------------+---------+-------+-------------------------+-------------+----------+------------+
1 row in set (0.02 sec)

  • Cancel the prefetch job.

    You can cancel a prefetch job by using its job ID.

    1. Cancel the job.

      CANCEL WARM UP JOB where id = 13418;

      The following result is returned.

      Query OK, 0 rows affected (0.02 sec)

    2. Verify that the job was canceled.

      SHOW WARM UP JOB WHERE ID = 13418;

      The following result shows that the job was canceled successfully. The status is CANCELLED.

      +-------+-------------------+-----------+-------+-------------------------+-------------+----------+-------------------------+
| JobId | ClusterName       | Status    | Type  | CreateTime              | FinishBatch | AllBatch | FinishTime              |
+-------+-------------------+-----------+-------+-------------------------+-------------+----------+-------------------------+
| 13418 | cluster_name1     | CANCELLED | TABLE | 2023-05-30 20:19:34.059 | 0           | 1        | 2023-05-30 20:27:14.186 |
+-------+-------------------+-----------+-------+-------------------------+-------------+----------+-------------------------+
1 row in set (0.00 sec)

Example: TTL policy

Set TTL policy

When you create a table, set the file_cache_ttl_seconds parameter in PROPERTIES to apply the TTL policy to the table's data, as shown in the following example.

Note

The file_cache_ttl_seconds parameter specifies how long, in seconds, you want newly imported data to be retained in the cache.

In the following example, all new data imported into the customer table is retained in the cache for 300 seconds.

CREATE TABLE IF NOT EXISTS customer (
  C_CUSTKEY     INTEGER NOT NULL,
  C_NAME        VARCHAR(25) NOT NULL,
  C_ADDRESS     VARCHAR(40) NOT NULL,
  C_NATIONKEY   INTEGER NOT NULL,
  C_PHONE       CHAR(15) NOT NULL,
  C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
  C_MKTSEGMENT  CHAR(10) NOT NULL,
  C_COMMENT     VARCHAR(117) NOT NULL
)
DUPLICATE KEY(C_CUSTKEY, C_NAME)
DISTRIBUTED BY HASH(C_CUSTKEY) BUCKETS 32
PROPERTIES(
    "file_cache_ttl_seconds"="300" -- Set the TTL.
)

Modify TTL policy

To change the TTL or add one to an existing table in yourApsaraDB for SelectDB instance, run an ALTER TABLE statement to set the file_cache_ttl_seconds parameter.

Note

The change does not take effect immediately and may be delayed.

ALTER TABLE customer set ("file_cache_ttl_seconds"="3000");
Previous：Auto PartitioningNext：Hybrid row-column store