Using Disk Cache to Improve Query Performance

Scenarios

As cloud computing advances, storage and compute decoupling technologies have grown increasingly sophisticated. Many companies now keep vast amounts of user data in cloud storage. This increases frequent read-write demands on OBS, making data access speed a key performance issue. Enhancing cloud data processing efficiency remains a critical challenge.

DWS speeds up data access by using the local disk cache and manages cache space effectively with the least recently used (LRU) policy. DWS also provides cache prefetch for cross-VW (VW stands for virtual warehouse) scenarios. The function loads selected data, like tables or partitions, into the cache immediately after creating an elastic VW, boosting query speed.

This function is available only for clusters of 9.1.0.x or later. The time-to-live (TTL) queue is available only for clusters of 9.1.1.x or later.

Multi-Queue LRU

• LRU

  The LRU cache policy organizes cached data in a queue. Accessed or new data moves to the front of the queue to avoid early removal. The system removes the oldest data first when the buffer becomes full.

  DWS uses the LRU-2Q algorithm to prevent LRU cache pollution. It keeps frequently accessed core service data in the cache, even when processing large amounts of older information.

  LRU2Q uses three queues: A1in, A1out, and Am. On its first access, data goes into the A1in queue. If A1in becomes full (It is 25% of total queue size by default, but it can be changed as required), the evicted data is moved to the A1out queue. The data in A1out not cached but acts as extra storage for A1in. If data in A1out is accessed again, it is promoted to the Am queue. The Am queue stores the most frequently used data.

• TTL

  The TTL policy keeps newly imported data in the cache for a period of time without removal. During this period, all TTL data holds top priority equally. No data gets removed early just because it is nearing expiration. If the TTL buffer runs out of space, the system takes space from the LRU queue to allow writing TTL data. The GUC parameter disk_cache_ttl_max_ratio sets the maximum space for the TTL queue, with a default value of 0.5 (half the total buffer). Once the TTL queue hits its limit and new data arrives, the system removes the older data to its LRU space.

  Application scenario: TTL works for small-scale data tables that need to be persisted locally. You can set a longer TTL for permanent tables to keep their data safe, and adjust the TTL for temporary tables based on the active time of hot data.

• Multi-queue

  As data grows in massive big data scenarios, storage needs and resource usage rise quickly. To meet user demands for accessing data at various periods, systems manage information by separating it into hot (frequently used) and cold (rarely accessed) categories. This improves local cache efficiency.

  For example, in a network traffic analysis system, users often focus on recent security incidents or network activity within the past month but rarely review older data. Data is categorized as hot and cold based on time.

  Data is categorized as hot or cold based on how often it is accessed or updated.

  • Hot data is frequently used and modified, will likely be needed again soon, and requires fast access response.
  • Cold data is rarely changed or accessed and does not need quick responses.

  Hot data should be stored in the local cache whenever possible. Cold data should take advantage of spatial locality by caching accessed and nearby data to minimize future OBS reads and should also have higher chances of being removed compared to hot data. In DWS, cold data has a dedicated 1 GB disk space managed by the A0 queue, following the LRU policy

  DWS employs an LRU-based multi-queue system. It categorizes data into three types using the TTL and hot/cold attributes: hot data with a TTL goes into the TTL queue, hot data without a TTL enters the LRU-2Q queue, and cold data is assigned to the A0 queue.

  During data read and write, DWS selects the queue to be filled and read to maximize cache utilization.

  Table 1 Data read and write mechanism of DWS

  Operation	Queue Filled upon a Miss	Queue Filled upon Data Write
  Import (with cache dual-write enabled)	TTL / LRU - 2Q / A0	TTL / LRU - 2Q / A0
  Query	TTL / LRU - 2Q / A0	N/A
  Prefetch	N/A	TTL / LRU - 2Q / A0

  Take the query operation as an example. If the data is not hit during data reading, the system checks whether the data is cold data. Cold data goes into the A0 cache. For hot data, the system checks if the table has a TTL parameter. If the TTL is set and the data has not expired, it is moved to the TTL buffer, or it is moved to the LRU-2Q buffer.

• Eviction

  The A0 queue has a fixed buffer size of 1 GB. The TTL and LRU-2Q queues share the buffer space defined by the GUC parameter disk_cache_max_size. Initially, the LRU-2Q queue uses this space. When accessing TTL-attributed data or if much TTL data expires, the system adjusts the allocation between the TTL and LRU-2Q queues to optimize cache usage. The TTL queue's maximum share cannot exceed the limit set by disk_cache_ttl_max_ratio.

  • Eviction of the TTL queue

    New data entering the TTL queue pushes expired data into the LRU-2Q queue.

    A background thread regularly evicts expired data from the TTL and moves it to the LRU-2Q queue.

    If the TTL space hits its limit with no expired data, the LRU policy activates, sending evicted data to the LRU-2Q queue.

    When you delete table data for which expiration time is set, background threads periodically delete invalid data from the TTL.

  • LRU: Eviction of 2Q queues

    If new data is inserted and the LRU space hits the upper limit, the LRU policy activates, and the evicted data is cleared from the cache.

    When the TTL queue runs low on space but has not hit its limit, it takes space from the LRU-2Q queue. If the space of the LRU-2Q queue is full, LRU eviction starts.

    When you delete table data for which expiration time is set, background threads periodically delete invalid data from the LRU-2Q.

Multi-disk Caches

Cloud environments route traffic for accessing various cloud disks through separate network interface cards (NICs), allowing their bandwidths to combine. When using multiple EVS disks, DWS enables setting them as cache paths, distributing cache files across these disks to enhance access speed. By default, both primary and standby disks serve as cache media for the primary DN on the current node during normal cluster operation, optimizing performance and utilizing available disk space efficiently. You can query the following parameters to view related information:

disk_cache_base_paths: You can use it to view, add, or delete a cache path.You cannot change its value. To change it, contact technical support. For example, the instance name is h10dn1.

The default multi-disk cache path of the primary DN is as follows:

• Path 1: /DWS/data1/h10dn1/primary0/disk_cache
• Path 2: /DWS/data2/h9dn1/primary0_disk_cache

The default multi-disk cache path of the standby DN is as follows:

• Path: /DWS/data2/h9dn1/secondry/disk_cache

The standby DN's cache path shares the same disk as primary DN's path 2. If the standby DN becomes primary, the max disk cache space drops to 1 GB, significantly reducing performance. Restore the cluster to its balanced state promptly.

Cache prefetch

In the architecture with decoupled storage and compute, DWS supports cross-VW clusters. The VWs access shared data, but their caches remain separate. Newly created VWs start with empty caches, potentially impacting query speeds. To solve this, DWS offers cache prefetch that lets users transfer data from remote storage to local caches. Cache prefetch supports two modes.

• Table data prefetch: Pre-load the data in table A.

  1 2 3 4

  /*Pre-load data in table A to the A1in queue.*/ EXPLAIN warmup SELECT * FROM A; /*Pre-load the data in table A to the Am queue.*/ EXPLAIN warmup hot SELECT * FROM A;

• Partition data prefetch: Pre-load the data in partition p1 of table A.

  1 2 3 4

  /*Pre-load the data in partition p1 of table A to the A1in queue.*/ EXPLAIN warmup SELECT * FROM A partition(p1); /*Pre-load the data in partition p1 of table A to the Am queue.*/ EXPLAIN warmup hot SELECT * FROM A partition(p1);

Checking Cached Data

Checking the disk space and hit ratio

You can run pgxc_disk_cache_all_stats to view the current cache status of the disk cache. The command is as follows:

1

SELECT * FROM pgxc_disk_cache_all_stats;

Enabling Disk Cache

By default, disk cache is enabled for cloud clusters. That is, access to all v3 tables uses disk cache. To verify if disk cache is enabled on your cluster, follow these steps. The following information is for reference only. You do not need to set it separately. If you need to set it, contact technical support.

1. Run the following commands on the DN:

   SHOW enable_aio_scheduler; 
   SHOW obs_worker_pool_size;

   Ensure that enable_aio_scheduler=on and obs_worker_pool_size >=4.

2. Run the following command on the CN:

   1	SHOW enable_disk_cache

   Ensure that enable_disk_cache=on.

The preceding two conditions must be both met.

Specifying a Table-level Cache Policy

There are four cache policies supported by disk cache.

• HPN(HOT PARTITION NUMBER)

  Set the cache policy to HPN : N during CREATE TABLE or ALTER TABLE operations. Specify a value for N between -1600 and 1600. Data in the most recent N partitions will be hot cached, while older partitions will remain cold cached. If N is 0, all data in all partitions will be cold cached. HPN is valid only for range partition tables and list partition tables in V3 tables.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

  CREATE TABLE IF NOT EXISTS lineitem ( L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL , L_TAX DECIMAL(15,2) NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL , L_LINESTATUS CHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT CHAR(25) NOT NULL , L_SHIPMODE CHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL ) with ( orientation = column, colversion = 3.0, cache_policy = 'HPN : 3' ) TABLESPACE cu_obs_tbs DISTRIBUTE BY HASH(L_ORDERKEY) PARTITION BY RANGE(L_SHIPDATE) ( PARTITION L_SHIPDATE_4 VALUES LESS THAN ('1993-01-01 00:00:00'), -- Cold cache PARTITION L_SHIPDATE_5 VALUES LESS THAN ('1994-01-01 00:00:00'), -- Cold cache PARTITION L_SHIPDATE_3 VALUES LESS THAN ('1995-01-01 00:00:00'), -- Cold cache PARTITION L_SHIPDATE_1 VALUES LESS THAN ('1996-01-01 00:00:00'), -- Cold cache PARTITION L_SHIPDATE_2 VALUES LESS THAN ('1997-01-01 00:00:00'), -- Hot cache PARTITION L_SHIPDATE_7 VALUES LESS THAN ('1998-01-01 00:00:00'), -- Hot cache PARTITION L_SHIPDATE_6 VALUES LESS THAN ('1999-01-01 00:00:00') -- Hot cache );

• HPL(HOT PARTITION LIST)

  Set the cache policy to HPL : , , ... during the CREATE TABLE or ALTER TABLE operations. Specify the name of the hot partition. The system will store data from this partition in the hot cache, while placing data from other partitions into the cold cache. HPL is valid only for range partitioned tables and list partitioned tables in V3 tables.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  CREATE TABLE IF NOT EXISTS nation ( N_NATIONKEY INT NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY INT NOT NULL, N_COMMENT VARCHAR(152) ) WITH ( orientation = column, colversion = 3.0, cache_policy = 'HPL : ASIA, AMERICA, REST' ) TABLESPACE cu_obs_tbs DISTRIBUTE BY ROUNDROBIN PARTITION BY LIST(N_NAME) ( PARTITION ASIA VALUES ('INDIA', 'INDONESIA', 'JAPAN'), --Hot cache PARTITION EUROPE VALUES ('FRANCE', 'GERMANY', 'UNITED KINGDOM'), -- Cold cache PARTITION AFRICA VALUES ('ALGERIA', 'ETHIOPIA', 'KENYA'), -- Cold cache PARTITION AMERICA VALUES ('ARGENTINA', 'BRAZIL', 'CANADA'), --Hot cache PARTITION REST VALUES (DEFAULT) --Hot cache );

• NONE

  Set the cache policy to NONE during the CREATE TABLE or ALTER TABLE command. After that, all data in the table is cold cached.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  CREATE TABLE IF NOT EXISTS partsupp ( PS_PARTKEY BIGINT NOT NULL, PS_SUPPKEY BIGINT NOT NULL, PS_AVAILQTY BIGINT NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL ) WITH ( orientation = column, colversion = 3.0, cache_policy = 'NONE' ) TABLESPACE cu_obs_tbs DISTRIBUTE BY HASH (PS_PARTKEY); -- Cold cache

• ALL

  Set the cache policy to ALL during the CREATE TABLE or ALTER TABLE command. After that, all data in the table is hot cached.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  CREATE TABLE IF NOT EXISTS orders ( O_ORDERKEY BIGINT NOT NULL, O_CUSTKEY BIGINT NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, O_TOTALPRICE DECIMAL(15,2) NOT NULL, O_ORDERDATE DATE NOT NULL, O_ORDERPRIORITY CHAR(15) NOT NULL, O_CLERK CHAR(15) NOT NULL, O_SHIPPRIORITY BIGINT NOT NULL, O_COMMENT VARCHAR(79) NOT NULL ) WITH ( orientation = column, colversion = 3.0, cache_policy = 'ALL' ) DISTRIBUTE BY ROUNDROBIN; -- Hot cache

Setting the TTL Policy

When creating a table, set the table-level parameter disk_cache_ttl to cache data in the table using the TTL policy. disk_cache_ttl defines how long newly imported data stays in the cache. It uses an interval format, with values ranging from 1 second to 100 years.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE IF NOT EXISTS ProductDimension
(
    ProductID INT,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    Description TEXT
)
WITH (
 orientation = column,
 colversion = 3.0,
 disk_cache_ttl = '1 day'
)
DISTRIBUTE BY ROUNDROBIN; -- Hot cache

Typical TTL Scenarios

• Dimension tables have a small data size with minimal changes but high access frequency. You can set a longer TTL, such as one year, to keep the data in the cache and avoid replacement during queries on larger tables. This allows quick access to the data throughout the year.

  Set disk_cache_ttl to 1 year for a new dimension table.

  1	CREATE TABLE dimension_table(a int, b text) with (orientation = column, COLVERSION = 3.0, disk_cache_ttl = '1 year');

  Set disk_cache_ttl for an existing dimension table by running the ALTER command.

  If you are not sure about the time when the table data was imported, set disk_cache_ttl to 10 years.

  1	ALTER TABLE dimension_table SET (disk_cache_ttl = '10 years');

  You can also set it to one year and then perform VACUUM FULL on the dimension table to update the data import time.

  1 2	ALTER TABLE dimension_table set (disk_cache_ttl = '1 years'); VACUUM FULL dimension_table;

• Real-time tables that are not frequently updated are mainly used for data import and query. You can set a proper TTL for these tables as required. For example, if you frequently access data of the last half day, you can set the TTL to 0.5 day.

  1	ALTER TABLE dimension_table set (disk_cache_ttl = '0.5 day');

• Real-time tables are frequently updated and the UPSERT operations are performed frequently. You can add the TTL time to tables based on the UPSERT time range to improve the UPSERT operation performance. For example, if UPSERT is performed on the data imported in the last two days, you can set the TTL to two days.

  1	ALTER TABLE dimension_table set (disk_cache_ttl = '2 days');

FAQs

1. Why Does the du or ls Command Show That the Disk Cache Directory Takes up Far More Space Than the Actual Data Size? (Only technical support personnel can run the du and ls commands.)

   Disk cache usage indicates the highest amount of stored data ever recorded, not the current cached data size. For example, if you import 100 GB of data, perform operations like UPSERT to expand it to 200 GB, and then run VACUUM to reduce it back to 100 GB, the disk cache still reflects the peak of 200 GB despite holding only 100 GB of actual data. Invalid data gets removed only when the disk cache is used up and the LRU eviction policy is triggered.

2. Will the Disk Cache Start Evicting Data Automatically? Why Does the Disk Watermark Remain at Its Highest Level?

   Yes. Eviction begins when disk usage hits either the maximum limit (default: 50% of total disk space for active and standby disks) or reaches 80% usage. Eviction marks cache locations as free but does not delete data. New data replaces these marked data. This keeps disk usage the same, maintaining a high watermark.

3. Why Does Not the Disk Cache Data Decrease After Dropping a Table, Even Though OBS Shows the Table Was Deleted?

   Dropping a table does not remove its cached data from the disk cache. The internal LRU mechanism automatically clears cached data for non-existent tables. This process has no impact on functionality.

4. Why Does the Cache Usage of Each Node Differ Greatly?

   Cache usage differs across nodes due to several reasons. It naturally varies because of limitations in single-node caching. Query delays stay unchanged. Key influencing factors include:

   • Time when different instances are added to the cluster.
   • Number of tables in different VWs.
   • Data distribution of tables and the data accessed by applications. The data accessed by an application may be on one node.
   • The time difference of VACUUM FULL on different nodes. VACUUM FULL doubles the cache space used by tables. Over time, the internal LRU mechanism removes unnecessary cached data.