@Rocky Am I correct to understand that to achieve the fastest query performance, the data & tables should ideally be loaded in the cache memory of BN or CN? My question is how is this handled? It is automatically by the system or administrator can perform certain instructions to tell StarRocks what data & tables will need the fastest query performance?

Yes, you are correct. Keeping data in the local cache of BE (Backend) or CN (Compute Node) is one of the most effective ways to achieve the fastest query performance, especially in data lake analytics or shared-data architectures. StarRocks handles this using a combination of automatic management and manual controls. 1. Automatic Handling (Default) By default, StarRocks manages caching automatically to ensure that "hot" data stays close to the CPU: * Page Cache (Memory): Automatically caches recently accessed data pages in memory. If a query requests data that was recently read, StarRocks pulls it directly from RAM. * Data Cache / Block Cache (Disk/Memory): In shared-data or data lake scenarios, StarRocks automatically caches data blocks from remote storage (like S3 or HDFS) onto the local NVMe/SSD disks of the BE/CN. * Eviction Policy: The system uses LRU (Least Recently Used) or SLRU (Segmented LRU) algorithms to automatically evict old data when the cache is full, making room for new data without administrator intervention. 2. Manual Controls & "Pinning" Data While the system is automatic, administrators have several ways to instruct StarRocks on which data should be prioritized: * Cache Warmup (

CACHE SELECT

): Introduced in v3.3, you can use the

CACHE SELECT

statement to proactively "warm up" specific tables or partitions. Instead of waiting for a user to run a slow first query, you can run this command (or schedule it as a background task) to pull data into the cache in advance.

sql
    -- Manually warm up a specific table
    CACHE SELECT * FROM hive_catalog.db.large_table WHERE dt = '2023-10-01';

* Table Properties (Shared-Data): When creating a table in a shared-data cluster, you can specify how long data should be considered "hot": * `"datacache.enable" = "true"`: Ensures the cache is used for this table. * `"datacache.partition_duration" = "1 MONTH"`: Instructs the system to prioritize keeping the last month of data in the cache. * Resource Allocation: Administrators can manually set the size of the cache using BE configuration parameters like

datacache_mem_size

(for memory) and

datacache_disk_size

(for disk). You can even update these dynamically:

sql
    UPDATE be_configs SET VALUE="20G" WHERE NAME="datacache_mem_size";

Summary * Standard Performance: Handled automatically via LRU/SLRU. * Fastest/Predictable Performance: Handled by administrators using

CACHE SELECT

or

partition_duration

to ensure specific hot data is pre-loaded and stays in the BE/CN nodes before queries arrive before users' nodes before the user even runs their query. References • data_source/block_cache_warmup.md • deployment/shared_data/feature-support-shared-data.md

@Rocky Are there any performance difference when loading data in memory or disk? How does the system handle data that are manually loaded if such data are not accessed frequently, or part of the automatic caching, the system experience insufficient storage from its memory & disk?

There are significant performance differences between memory and disk caching, and StarRocks uses a unified management strategy to handle both automatic and manually loaded data when resources are tight. 1. Performance Differences: Memory vs. Disk To achieve the "fastest" performance, memory is the superior medium, but disk caching is the practical solution for large-scale data. * Memory (Page Cache): This is the fastest. It stores data pages in an uncompressed format. Accessing data from Page Cache avoids both disk I/O and the CPU overhead of decompression. It is ideal for frequently accessed "hot" indexes and metadata. * Disk (Block Cache): While slower than RAM, this is still much faster than fetching data from remote storage (like S3 or HDFS). It stores data in compressed blocks on local NVMe/SSD. Its primary goal is to eliminate the high latency of remote network requests. * Performance Gap: In typical benchmarks (like SSB), queries hitting the local cache (Data Cache) can be 3x to 10x faster than queries fetching data from remote storage for the first time. 2. Handling Insufficient Storage & Manual Data StarRocks does not treat manually loaded data (via

CACHE SELECT

) as "permanent" or "pinned" in a way that ignores resource limits. Here is how the system handles it: * Unified Eviction Policy: Both manually warmed-up data and automatically cached data are managed by the same LRU (Least Recently Used) or SLRU (Segmented LRU) algorithms. * If you manually load a table today but never query it again, it becomes "cold" over time. * When the cache (disk or memory) reaches its capacity limit (typically 80-90% of the configured

datacache_disk_size

or

datacache_mem_size

), the system will automatically evict the least recently used data—even if that data was originally loaded manually. * "Overwritable" Space: StarRocks does not delete cache files immediately when space is needed. Instead, it marks old blocks as overwritable. This is why you might see disk usage stay at 90% even if the actual "active" data is less—it’s keeping the old data available just in case, but it will overwrite it instantly when new data arrives. * Automatic Scaling: If enabled (

datacache_auto_adjust_enable=true

), the system can dynamically expand or shrink the cache disk quota based on the actual free space of the physical disk, maintaining a "safe level" (default 80%) to prevent the node from crashing due to a full disk. Summary for Administrators If you want to ensure specific tables stay in the cache: 1. Monitor Usage: Use

TOTAL_CACHE_USAGE

from the

CACHE SELECT

output to see if your manual load fits within your current disk/memory limits. 2. Adjust Quotas: If important data is being evicted too quickly, increase

datacache_disk_size

or

datacache_mem_size

. 3. Scheduled Warmup: Because of the LRU policy, it is a best practice to schedule

CACHE SELECT

tasks (e.g., via a cron job or Airflow) shortly before your peak business hours to ensure the "hottest" data is refreshed in the cache. References • data_source/data_cache.md • using_starrocks/caching/block_cache.md • data_source/block_cache_warmup.md References • data_source/data_cache.md • using_starrocks/caching/block_cache.md • using_starrocks/caching/query_cache.md

@Rocky How does an administrator know what data or tables are currently in Memory or Disk cache? Show me some output examples if queries can be performed.

Identifying exactly which tables or partitions are currently residing in the cache is handled at different levels in StarRocks. Because the Data Cache (Block Cache) manages data as small, fixed-size blocks across multiple nodes, there isn't a single "list" of cached tables. Instead, administrators use node-level metrics, query profiles, and system views to monitor cache status. 1. Overall Cache Usage (Node-Level) To see how much total space is being used on each node (BE/CN) for disk and memory caching, use

SHOW BACKENDS

. Command:

sql
SHOW BACKENDS\G

Example Output Snippet:

text
*************************** 1. row ***************************
...
              Alive: true
   DataCacheMetrics: Status: Normal, DiskUsage: 450.20GB/1.00TB, MemUsage: 12.50GB/64.00GB
...

* DiskUsage: Shows how much of the local SSD/NVMe cache is occupied. * MemUsage: Shows how much of the memory-based Data Cache is occupied. Alternatively, you can query the system view for a tabular format:

sql
SELECT * FROM information_schema.be_datacache_metrics;

────────── 2. Validating if a Specific Table is Cached (Query-Level) The most accurate way to know if a table's data is currently in the cache is to check the Query Profile after running a query. How to check: 1. Run your query (e.g.,

SELECT * FROM my_table WHERE ...

). 2. Get the profile (using

get_query_profile(query_id)

or via the Manager UI). 3. Look for the following metrics: *

DataCacheReadBytes

: The amount of data read directly from the local cache (Memory/Disk). If this is high and

BytesRead

from remote storage is low, your data is cached. *

DataCacheWriteBytes

: If this is high, it means the data was not in the cache and is currently being written to the cache for the first time. ────────── 3. Monitoring Manual Warmup Results When you use the

CACHE SELECT

instruction to manually load data, the command itself returns a status report showing how much of that specific request was served by the cache vs. written to it. Example Command:

sql
CACHE SELECT * FROM hive_catalog.db.orders WHERE dt = '2024-01-01';

Example Output: READ_CACHE_SIZE | WRITE_CACHE_SIZE | AVG_WRITE_CACHE_TIME | TOTAL_CACHE_USAGE 10.5 MB | 2.1 GB | 45ms | 45.2% * READ_CACHE_SIZE: Data already in the cache. * WRITE_CACHE_SIZE: New data that was just loaded into the cache during this command. ────────── 4. Checking Cache Configuration per Partition You can check which partitions are eligible for caching (based on your

datacache.partition_duration

settings) by querying the

partitions_meta

table. Command:

sql
SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    ENABLE_DATACACHE, 
    DATA_SIZE 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_table_name';

* ENABLE_DATACACHE: Confirms if the system is allowed to cache this partition. 5. Real-Time Hit Rates (API) For a high-level view of how effective the cache is across the entire cluster, you can access the BE monitoring API directly:

bash
curl http://<BE_IP>:<BE_HTTP_PORT>/api/datacache/app_stat

Output Example:

json
{
    "block_cache_hit_rate": 0.95,
    "page_cache_hit_rate": 0.82
}

This tells you that 95% of requests to the disk-based cache are being served successfully without going to remote storage. References • using_starrocks/caching/block_cache.md • data_source/data_cache.md • data_source/block_cache_warmup.md • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky What other configurations or planning will enhance performance greatly?

Beyond caching, StarRocks provides several architectural and configuration-level "levers" that can significantly improve performance. These can be divided into Design-Time Planning and Runtime Configurations. 1. Design-Time Planning (Schema & Architecture) The most significant performance gains often come from how data is physically laid out on disk. * Colocate Join (Zero-Shuffle Joins): By default, joining two large tables requires "shuffling" data across the network. If you plan your tables to have the same distribution (bucketing) keys and put them in the same "Colocation Group," StarRocks will store related rows on the same physical node. * How: Use the

"colocate_with" = "group_name"

property during table creation. * Benefit: Eliminates network overhead for joins, making multi-table queries nearly as fast as single-table scans. * Sort Keys & Sparse Indexes (Zonemap): StarRocks automatically builds a Prefix Index on the columns you define in the

ORDER BY

clause (for Duplicate/Primary Key tables). * Planning Tip: Place columns used in

WHERE

filters and

GROUP BY

clauses at the beginning of your table definition. This allows the system to skip entire data blocks (Zonemap pruning). * Asynchronous Materialized Views (Pre-aggregation): If you have complex dashboard queries that join 10+ tables or perform heavy aggregations, create an Asynchronous Materialized View. * The "Magic": StarRocks features Transparent Query Rewrite. You don't need to change your SQL; if a query can be satisfied by a pre-computed Materialized View, the optimizer automatically routes the query there. * Bitmap & Bloom Filter Indexes: * Bitmap Index: Best for low-cardinality columns (e.g.,

gender

,

province

). * Bloom Filter Index: Best for high-cardinality columns (e.g.,

user_id

,

order_id

) used in equality filters (

=

). ────────── 2. Runtime Configuration (Query & System Level) You can tune these variables globally or per-session (

SET_VAR

) to optimize specific workloads. * Query Cache (Result Cache): While the Data Cache stores raw data blocks, the Query Cache stores the final results of sub-queries or fragments. * Configuration: Set

query_cache_type = 1

and ensure

query_cache_size

(default 512MB) is sufficient in

be.conf

. * Benefit: Subsequent identical queries return in milliseconds with zero CPU/IO cost. * Global Runtime Filters: When joining a large table with a small filtered table, StarRocks can generate a "filter" from the small table and push it down to the scan of the large table. * Configuration:

enable_global_runtime_filter = true

. * Benefit: Dramatically reduces the amount of data read from disk during joins. * Low-Cardinality String Optimization (Global Dictionary): StarRocks can automatically treat strings as integers (dictionary encoding) during processing. * Configuration:

cbo_enable_low_cardinality_optimize = true

. * Benefit: Can improve string processing performance by 3x or more by performing calculations on integers instead of raw text. * Pipeline Engine Parallelism: For massive queries, you can increase the degree of parallelism to use more CPU cores. * Variable:

pipeline_dop

(Degree of Parallelism). If set to

0

, StarRocks scales this automatically based on the machine's cores. Summary Checklist for Administrators Goal | Optimization Strategy Kill Shuffling | Use Colocate Join for related large tables. Kill Disk I/O | Use Sort Keys effectively and enable Data Cache. Kill CPU Work | Use Materialized Views for pre-aggregation. Kill Sub-second Latency | Enable Query Cache for repeatable dashboard queries. References