Overview

In shared storage mode of OceanBase Database, full data is stored in low-cost object storage, while frequently accessed or actively used hot data is cached in local storage to accelerate business queries. Cold data that is unlikely to be accessed can be stored in object storage, and such data will not be cached in local storage to avoid occupying cache space. Caching hot data is an effective method to optimize storage costs and database performance. By default, the system dynamically caches hot data (data with high access frequency) and evicts cold data (data with low access frequency) based on data access frequency and generation time. However, for sensitive queries, the system's automatic identification of hot and cold data can be unpredictable, potentially causing RT jitter during queries.

For business scenarios with clear hot and cold data characteristics, such as time-series data, you can customize the hot and cold data strategy based on business features (such as time boundaries) to directly define the hot data range. This addresses the limitations of automatic hot and cold data identification and enhances performance predictability. By pre-caching hot data in local storage, you can avoid RT jitter during queries.

Scenarios

• Time-sensitive business: For example, businesses that only need to view recent data.
• Businesses with high RT stability requirements: For example, businesses that are sensitive to query latency for frequently accessed data.

Data caching mechanism

• Data attribute types:

  • HOT: Hot data, which is cached in local storage as much as possible. If the local storage capacity allows, all hot data is cached in local storage.

  • AUTO: The system automatically identifies hot and cold data based on access frequency and dynamically pre-fetches and caches data.

  • COLD: Cold data, which is not cached in local storage. For data that has been persisted to cache, the system actively triggers cache eviction. Subsequent accesses to table data can be stored in macroblock memory cache without being persisted to macroblock or microblock cache.

  • NONE: Inherit the hot and cold cache strategy from the parent entity (such as the main table). For example, if the cache strategy of a local index is NONE, it dynamically inherits the strategy of the main table.

    Note

    When the cold and hot storage strategy of data follows the value of STORAGE_CACHE_POLICY of the parent entity:

    • For subpartitions, the parent entity is the partition.
    • For partitions, the parent entity is the table.
    • For index tables, the parent entity is the main table.

• Priority mechanism:

  • User-defined hot data has higher priority than automatically identified hot data.

Support for hot and cold storage strategies

• In shared storage mode, OceanBase Database supports customizing hot and cold storage strategies using SQL statements:

  • Creating tables (user tables or index tables) with specified hot and cold storage strategies at the table or partition level (STORAGE_CACHE_POLICY).
  • Modifying the hot and cold storage strategies at the table or partition level (STORAGE_CACHE_POLICY) for tables (user tables or index tables).

• OceanBase Database provides configuration items to set global hot and cold storage strategies:

  • Configuring the default caching strategy default_storage_cache_policy.
  • Enabling user-defined hot and cold storage strategies enable_manual_storage_cache_policy.
  • Setting whether to suspend background caching tasks suspend_storage_cache_task.

• OceanBase Database provides views to view caching strategies:

  • V$OB_TABLET_LOCAL_CACHE displays the overall caching status of each tenant on the current OBServer node.
  • V$OB_STORAGE_CACHE_TASKS displays the caching task information of each tenant on the current OBServer node.

References

• Shared storage multi-level cache
• Create a table-level hot and cold storage strategy
• Create a partition-level hot and cold storage strategy
• Modify a table-level hot and cold storage strategy
• Modify a partition-level hot and cold storage strategy
• O&M guide for hot and cold storage strategies
• Create a macroblock-level hot and cold storage strategy