ApsaraDB for SelectDB:Data cache

How data enters the cache

Data reaches the data cache through three paths:

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

• Query: If requested data is not in the cache, the system reads it from remote storage into memory and writes it to the local disk cache for subsequent queries.

• New cluster: A newly created cluster starts with an empty cache. Use cache prefetch to proactively load data from remote storage before queries run.

Choose a cache policy

Example: A database has 10 tables totaling 1 TB, but cache capacity is 200 GB. One table is accessed far more frequently than the others. Set TTL on that table to prevent it from being evicted.

TTL edge case: If the cache fills entirely with TTL data, no new data is written to the cache — including newly imported data with a TTL policy and cold data read from remote storage.

Cache policy parameters

Set the cache size

Specify the cache size when creating an instance and cluster. See Create an instance.

View the cache size

Log in to the SelectDB console. On the Instance List page, click the target instance ID to go to the Instance Details page, then click Cluster Management to view the Cache Size.

Step 1: Identify hot data

1. Query which clusters have cached hotspot information.

   SHOW CACHE HOTSPOT '/';

   The result shows that cluster_name0 has cached hotspot data.

   +------------------------+-----------------------+----------------------------------------+
   | cluster_name           | total_file_cache_size | top_table_name                         |
   +------------------------+-----------------------+----------------------------------------+
   | cluster_name0          |          751620511367 | regression_test.selectdb_cache_hotspot |
   +------------------------+-----------------------+----------------------------------------+

2. View the hotspot information for cluster_name0.

   SHOW CACHE HOTSPOT '/cluster_name0';

   The top_partition_name column shows that p20230529 is a hot partition in 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 the hot partitions for 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';

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

Step 2: Create a cache prefetch job

The following examples show all three prefetch modes.

• Cross-cluster prefetch — prefetch cluster_name0's cache into cluster_name1:

  WARM UP CLUSTER cluster_name1 WITH CLUSTER cluster_name0

• Table prefetch — prefetch the customer table into cluster_name1:

  WARM UP CLUSTER cluster_name1 WITH TABLE customer

• Partition prefetch — prefetch the p20230529 partition of the customer table into cluster_name1:

  WARM UP CLUSTER cluster_name1 WITH TABLE customer PARTITION p20230529

Step 3: Monitor and manage the prefetch job

1. Get the job ID by running the prefetch command. The output includes a JobId.

   WARM UP CLUSTER cluster_name1 WITH TABLE customer;

   +-------+
   | JobId |
   +-------+
   | 13418 |
   +-------+
   1 row in set (0.01 sec)

2. Check the job progress using the JobId. Track FinishBatch against AllBatch — each batch is approximately 10 GB.

   SHOW WARM UP JOB WHERE ID = 13418;

   +-------+-------------------+---------+-------+-------------------------+-------------+----------+------------+
   | 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)

3. (Optional) Cancel the job by its JobId.

   CANCEL WARM UP JOB where id = 13418;

   Verify the cancellation — the status should be CANCELLED.

   SHOW WARM UP JOB WHERE ID = 13418;

   +-------+-------------------+-----------+-------+-------------------------+-------------+----------+-------------------------+
   | 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)

Set a TTL policy

Set file_cache_ttl_seconds in the PROPERTIES clause when creating a table. The value specifies how long, in seconds, newly imported data remains in the cache.

The following example retains all newly imported data in the customer table 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"
);

Modify a TTL policy

Use ALTER TABLE to update the TTL for an existing table.

ALTER TABLE customer SET ("file_cache_ttl_seconds"="3000");