@Rocky in shared_data mode, what should be the disk cache size in comparison to the data we have in S3? 10% ? 20% of the data?

In StarRocks shared-data mode, there is no official "fixed percentage" (like 10% or 20%) recommended relative to your total S3 data. Instead, the disk cache should be sized based on your *working set*—the subset of data that is frequently or recently queried. Here are the key considerations and best practices for sizing the disk cache: 1. Default Configuration By default, StarRocks allocates 80% of the local disk capacity on each Compute Node (CN) or Backend (BE) for the Data Cache. This is controlled by the configuration: * `starlet_star_cache_disk_size_percent`: Default is

80

. * Alternatively, you can set a fixed size via

datacache_disk_size

. The system will take the larger value of these two settings. 2. Sizing Based on "Working Set" If your total data in S3 is 100 TB but you only frequently query the last 7 days of data (e.g., 5 TB), you only need enough local disk space to cover that 5 TB across your nodes. * Rule of Thumb: Estimate the size of the data you access in a typical day/week. Ensure the aggregate local disk space across all nodes (at 80% utilization) can accommodate this volume to avoid frequent cache evictions. * LRU Eviction: If the cache becomes full, StarRocks uses a Least Recently Used (LRU) policy to evict old data blocks to make room for new ones. 3. Managing "Hot Data" with TTL You can control which data is eligible for caching using the table-level property

datacache.partition_duration

. * Example:

PROPERTIES ("datacache.partition_duration" = "1 MONTH")

* This ensures that only data within the specified duration is eligible for caching. If you have years of historical data in S3 but only care about performance for the current month, this significantly reduces the amount of local disk you need to provision. 4. Hardware Recommendations * Disk Type: It is strongly recommended to use SSD or NVMe for the local cache. Using HDD for the cache significantly reduces the performance benefits of shared-data mode. * Scale-Out: Because CN nodes are stateless, if you find your cache hit ratio is low and query performance is suffering, you can add more CN nodes with local SSDs. StarRocks will automatically distribute the cache across the new nodes. Summary Instead of a fixed percentage of total S3 data, size your local disks to fit your active working set. If you are unsure of your working set size, starting with 10–20% of your total data is a common industry starting point, but you should monitor your Cache Hit Ratio (viewable via

SHOW BACKENDS

) and adjust disk capacity or node count accordingly. References • deployment/shared_data/s3.md • deployment/shared_data/feature-support-shared-data.md

@Rocky is there any settings to provision the last month of data to cache?

Yes, there are two complementary ways to handle this in StarRocks: one that manages which data stays in the cache (the policy) and one that actively loads it (the action). 1. The Policy:

datacache.partition_duration

You can set a table property to ensure that only the last month of data is considered "hot" and eligible for caching. If data older than this duration is evicted, StarRocks will not reload it into the cache even if it is queried again. Example:

sql
ALTER TABLE your_table SET ("datacache.partition_duration" = "1 MONTH");

* What it does: It tells StarRocks that only data within the last month (relative to the current time) should be kept in the disk cache. * Why use it: This prevents a large "cold" historical query from flushing your "hot" recent data out of the cache. 2. The Action: Data Cache Warmup (

CACHE SELECT

) If you want to proactively provision (pre-load) that month of data into the cache without waiting for users to run queries, you can use the

CACHE SELECT

command (available in StarRocks v3.3.0 and later). Example:

sql
CACHE SELECT * 
FROM your_table 
WHERE event_date >= date_sub(now(), interval 1 month);

* What it does: This command explicitly reads the data from S3 and writes it into the local disk cache of the Compute Nodes. * Recommendation: You can schedule this as a routine task (e.g., after your daily ETL) to ensure the cache is always "warm" for your users. Summary of Best Practices * To limit cache usage: Use

"datacache.partition_duration" = "1 MONTH"

. * To pre-fill the cache: Use

CACHE SELECT

with a

WHERE

clause for the last month. * Combined: Using both ensures your cache is only used for the data you care about and that the data is ready before the first user query arrives. References • data_source/block_cache_warmup.md • deployment/shared_data/s3.md • deployment/shared_data/feature-support-shared-data.md References • best_practices/partitioning.md • table_design/data_distribution/expression_partitioning.md • table_design/data_distribution/dynamic_partitioning.md • table_design/data_distribution/Data_distribution.md

@Rocky we backfilled ~105M rows into an unpartitioned PK table (

ORDER BY (last_activity, session_pk)

). UI-shaped query:

SELECT session_id, last_activity, spans_count
FROM sessions_list_poc
WHERE last_activity >= date_trunc('day', now() - INTERVAL 30 DAY)
ORDER BY last_activity DESC, session_pk DESC
LIMIT 50;

EXPLAIN ANALYZE

(4.1.6 shared-data): • TotalTime ~332ms • OLAP_SCAN OutputRows ~20.3M (full 30d filter set) • TOP_N then → 50 • QueryAllocatedMemoryUsage ~9.5 GB • TopN runtime filter: 0% pruned So it feels fast, but it still scans ~20M rows and allocates ~9.5GB for a LIMIT 50. Questions: 1. Is ~300ms on 20M sort-key rows expected (S3 + cache), i.e. “fast because of zonemap/parallel scan,” not because Top-N short-circuits? 2. Is ~9.5GB allocated for that pattern normal / acceptable for multi-user UI, or a red flag? 3. Any way to get first-page DESC Top-N without materializing the whole time window (or bound memory), short of day partitions?

Regarding your observations on the Primary Key (PK) table query performance (assuming version 3.1.6 based on the 4.1.6 mention), here is the breakdown of why you are seeing high memory usage despite the fast response times. 1. Why 300ms? (Parallel Scan vs. Short-Circuit) The ~300ms response time is "fast" primarily due to Prefix Indexing and Zonemaps, not an early-exit short-circuit. * The Sort Key: Since

last_activity

is the leading column in your

ORDER BY

and matches your

WHERE

filter, StarRocks uses its Short Key Index (a sparse index of the first 36 bytes of the sort key) to jump directly to the relevant data blocks in S3/Cache. * Vectorized Scan: It scans the 20.3M rows very quickly because it reads contiguous blocks of data and processes them using SIMD instructions. * No Global Short-Circuit: In StarRocks, a

LIMIT

doesn't usually allow the storage engine to stop reading after 50 rows if the data is distributed across multiple tablets/nodes. The engine must scan all rows matching the

WHERE

clause across all tablets to guarantee that the global top 50 rows are found. 2. Is 9.5GB Memory a Red Flag? Yes, for a UI-driven query with

LIMIT 50

, 9.5GB is high. This indicates Early Materialization. * The Cause: By default, StarRocks often materializes (reads into memory) all columns in your

SELECT

list (

session_id

,

spans_count

, etc.) for every row that passes the

WHERE

filter. * The Math: 20.3M rows × (session_id string size + other columns) + internal overhead easily reaches ~9.5GB. If

session_id

is a long string, the memory grows linearly with the number of rows passing the filter, regardless of the

LIMIT

. * Multi-user risk: If 10 users run this UI query simultaneously, you will consume ~95GB of RAM, which can lead to BE OOM (Out of Memory) or performance degradation for other tasks. 3. How to optimize (Late Materialization) To bound the memory usage, you need to ensure StarRocks only materializes the full row for the final 50 results. Solution A: Force Late Materialization Late materialization tells the engine to only read the columns needed for filtering and sorting (

last_activity

,

session_pk

) first. Only after identifying the top 50 rows does it "go back" and fetch the

session_id

and

spans_count

for those 50 specific rows. Try setting this in your session before running the query:

sql
-- Force late materialization for all reads
SET late_materialization_ratio = 1000;

Wait for version check: This parameter was introduced/refined in v3.2. If you are on an older version (like 3.1.x), it may not be available or may behave differently. Solution B: Primary Key Index Persistence Ensure your table is using a Persistent Index if you have many rows. This keeps the primary key metadata on disk (SSD) rather than RAM.

sql
ALTER TABLE sessions_list_poc SET ("enable_persistent_index" = "true");

Solution C: Check the "TopN" Filter In your

EXPLAIN ANALYZE

, look for the

TopNFilter

. If it shows

0% pruned

, it means the dynamic filter generated by the Top-N operator isn't helping skip data. In your case, because you are sorting

DESC

on a time-based sort key, the "best" rows are at the end of the range, forcing a scan of the window. Summary Recommendation: The memory usage is the main concern. Verify if your version supports

late_materialization_ratio

. If it does, forcing it should drop your memory usage from GBs to MBs, as the engine will only keep 50 "full" rows in memory while the 20M rows being scanned will only involve the

last_activity

column. References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md

@Rocky follow-up with numbers from our POC (StarRocks 4.1.6 shared-data). Table: unpartitioned PK,

ORDER BY (last_activity, session_pk)

, ~105M rows Query:

SELECT session_id, last_activity, spans_count
FROM sessions_list_poc
WHERE last_activity >= date_trunc('day', now() - INTERVAL 30 DAY)
ORDER BY last_activity DESC, session_pk DESC
LIMIT 50;

EXPLAIN ANALYZE: • ~165–332ms •

OLAP_SCAN OutputRows

≈ 20–23M •

QueryAllocatedMemoryUsage

≈ 5.7–9.5 GB • TopN RF: 0% pruned Cluster BE/CN config today:

late_materialization_ratio = 10          -- default
metric_late_materialization_ratio = 1000

Session

enable_global_late_materialization=true

only moved mem 9.5 → 5.7 GB — still multi-GB. Questions: 1. Will setting CN

late_materialization_ratio=1000

(restart) realistically drop this toward MBs, or will Top-N still hold ~20M sort keys / row handles in memory so we stay in GBs? 2. Is late materialization even the right lever for DESC Top-N over a large time window, or should we look at something else (partitioning, different first-page pattern, etc.)? 3. If you still recommend

late_materialization_ratio=1000

for a POC, we’ll ask platform — just want to know if it’s worth a CN restart.

To answer your follow-up with the data from your StarRocks POC (assuming version 3.x given the 4.1.6 typo): 1. Will

late_materialization_ratio=1000

drop memory toward MBs? Mostly, yes. It is worth the restart for a POC. The drop from 9.5 GB to 5.7 GB you saw with the session variable indicates the feature is active, but the BE-level threshold (

late_materialization_ratio = 10

) is likely still triggering early materialization for many segments because the filter "selectivity" isn't deemed high enough by the internal heuristic. * How it helps: By forcing this to

1000

, the

OLAP_SCAN

will only load the

last_activity

and

session_pk

columns into memory for the 20M rows. The "fat" columns (

session_id

and

spans_count

) will only be fetched from disk for the final 50 rows. * The residual memory: You likely won't hit "low MBs" because you are still processing 20M rows. 20 million rows of a sort key (e.g., 8-byte timestamp + 32-byte session string) plus StarRocks' internal

Chunk

overhead and pointers will still take ~1–2 GB of RAM in the

TOPN

node. However, it should be significantly lower than 5.7 GB. 2. Is Late Materialization the right lever, or should you partition? Late materialization is a "fix" for the symptom (high memory usage), but partitioning is the "cure" for the cause (scanning 20M rows). * The Problem: Without partitioning, StarRocks must scan all 20M rows across all tablets to find the global "Top 50" because the latest data could be scattered across any bucket. * The Partitioning Advantage: If you partition by

last_activity

(e.g., daily), StarRocks can perform Partition Pruning and Short-Circuiting. * The query specifies

ORDER BY last_activity DESC

. * The engine will look at the most recent partition (Today) first. * If Today has >50 rows, the engine can potentially stop scanning entirely and never even look at the other 29 days. This would drop your

OLAP_SCAN OutputRows

from 20M to exactly 50, and your memory usage to effectively zero. 3. Is the restart worth it? Yes, but only to confirm the memory behavior. Setting

late_materialization_ratio = 1000

is a standard "power user" move in StarRocks when dealing with wide tables and

LIMIT

queries. It will stabilize your cluster's memory under multi-user load. However, for a production UI: If you want sub-100ms performance and negligible memory usage, you should re-create the table with daily partitions. In shared-data mode, having many partitions is fine, and it is the only way to allow the engine to "short-circuit" the scan for a

DESC + LIMIT

query. Summary Recommendation 1. Short term: Set

late_materialization_ratio = 1000

to prevent OOMs during the POC. 2. Long term: Add

PARTITION BY last_activity

(daily). This will transform your query from a "fast scan of 20M rows" into a "point lookup of 50 rows." References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • using_starrocks/sorted_aggregate.md • knowledge/usage/best-practice.md References • administration/management/BE_configuration.md • sql-reference/System_variable.md • best_practices/query_tuning/query_profile_operator_metrics.md