@Rocky How can I do cache warming with the

cache select

feature every time i change my CN Nodes or need to restart them? right now the volume of data for datacache configured partition duration (5 days) is too long, exceeding timeout. Right now my data volume is 1.460TB

The

CACHE SELECT

feature is essentially a specialized query that reads data from remote storage and writes it to the local Data Cache. Because it is implemented as a synchronous query (internally using

INSERT INTO BLACKHOLE

), it is subject to standard query timeouts and resource limits. To warm up 1.46 TB of data across CN nodes without hitting timeouts, you should use the following strategies: 1. Use

SUBMIT TASK

for Background Execution Instead of running

CACHE SELECT

directly in your SQL client (which can time out due to the client connection or the

query_timeout

variable), submit it as a background task. Tasks have a much higher default timeout (

task_runs_timeout_second

is 4 hours) and do not depend on an active client connection.

sql
SUBMIT TASK warmup_task
PROPERTIES (
    "session.query_timeout" = "28800", -- 8 hours
    "session.pipeline_dop" = "16"      -- Adjust based on CN core count
)
AS 
CACHE SELECT * FROM your_table 
WHERE date_col >= '2023-10-01';

2. Increase Query Timeouts If you prefer running it synchronously, you must increase the session timeout before executing the statement. For StarRocks v3.4+, use `insert_timeout`; for older versions, use

query_timeout

.

sql
-- For v3.4 and later
SET insert_timeout = 28800; 
-- For versions prior to v3.4
SET query_timeout = 28800; 

CACHE SELECT * FROM your_table WHERE ...;

3. Incremental Warmup (Partitioned Approach) Warming up 1.46 TB in a single statement is prone to failure and can saturate cluster resources. It is highly recommended to split the 5-day duration into smaller chunks (e.g., 6-hour or 12-hour intervals) using the

WHERE

clause. * Example Strategy: Submit multiple tasks, each covering a portion of the data.

sql
    -- Warm up Day 1
    SUBMIT TASK warmup_day1 AS CACHE SELECT * FROM table WHERE dt = '2023-10-01';
    -- Warm up Day 2
    SUBMIT TASK warmup_day2 AS CACHE SELECT * FROM table WHERE dt = '2023-10-02';

This reduces the blast radius of a single failure and allows you to monitor progress more effectively. 4. Optimize Parallelism You can speed up the warmup process by increasing the Degree of Parallelism (DOP). * Set

pipeline_dop

in your session or task properties to a higher value (e.g., equal to the number of CPU cores on your CN nodes) to maximize disk I/O and network throughput. 5. Managing CN Node Changes When you add or remove CN nodes, the consistent hashing for Data Cache shifts, causing some data to be reassigned to different nodes. * Best Practice: After a cluster scaling event or a full restart, prioritize warming up the most "hot" data (e.g., the last 24 hours) first using a high-priority task, then proceed with the rest of the 5-day window in chunks. Monitoring Progress You can monitor the status and performance of your warmup tasks using:

sql
-- Check task status
SELECT * FROM information_schema.task_runs WHERE task_name = 'warmup_task' ORDER BY create_time DESC;

-- Use verbose mode in manual queries to see per-node metrics
CACHE SELECT * FROM table PROPERTIES("verbose" = "true") WHERE ...;

Note: Ensure your

datacache.partition_duration

and the actual disk space allocated for cache (

starlet_star_cache_disk_size_percent

) are sufficient to hold 1.46 TB. If the cache disk is full, new data will evict the data you just warmed up based on the SLRU policy. References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/System_variable.md • administration/management/FE_configuration.md • administration/management/resource_management/query_queues.md

@Rocky +---------------+---------------------------------------------+------------------+-------------------+-------------------+---------------------+--------------+------------+-------------+------------+--------+--------------------+------------+-----------+-------------------+--------+ | TransactionId | Label | Coordinator | TransactionStatus | LoadJobSourceType | PrepareTime | PreparedTime | CommitTime | PublishTime | FinishTime | Reason | ErrorReplicasCount | ListenerId | TimeoutMs | PreparedTimeoutMs | ErrMsg | +---------------+---------------------------------------------+------------------+-------------------+-------------------+---------------------+--------------+------------+-------------+------------+--------+--------------------+------------+-----------+-------------------+--------+ | 292956 | insert_019f4338-a10b-7f8b-bcae-e8e6b6e48162 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 193332 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292946 | insert_019f4337-b6a7-7a2e-a6f1-538849c74aac | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 193232 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292933 | insert_019f4336-cc3d-7aa6-8d53-f5dfebb68aa1 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 193132 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292921 | insert_019f4335-e1d7-7656-acad-2469ec397bd1 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 193032 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292910 | insert_019f4334-f772-742f-bd01-dba2105528b3 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192932 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292899 | insert_019f4334-0d0e-7830-a032-23fc21f8fdda | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192832 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292887 | insert_019f4333-22a9-7968-b494-1d9a7c6fe621 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192732 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292876 | insert_019f4332-3844-70d8-854d-9f8cd519f269 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192632 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292861 | insert_019f4331-4dde-78d3-b9cb-1db98b7967ce | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192532 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292849 | insert_019f4330-6379-7b33-a68e-f0018a1e9ce2 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192432 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292836 | insert_019f432f-7915-73fb-b15f-60ff4e0d44e8 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192332 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292821 | insert_019f432e-8eb0-763e-ac62-ebc505ae061a | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192232 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292809 | insert_019f432d-a44c-7857-9d6b-e62427df6c0f | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192132 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292797 | insert_019f432c-b9e6-779e-a0ff-52f596731f6c | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 192032 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292780 | insert_019f432b-cf82-75f8-bdd0-41f824d86b24 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191932 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292769 | insert_019f432a-e51e-7a50-b43d-dc577109f843 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191832 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292757 | insert_019f4329-faba-74e4-adb5-6f7c595e248a | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191732 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292746 | insert_019f4329-1055-7d51-be01-afe7fed81d3d | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191632 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292735 | insert_019f4328-25eb-726c-9b49-a5b75164e4dd | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191532 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292726 | insert_019f4327-3b87-741b-84c5-6550cc0645c0 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191432 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292716 | insert_019f4326-5122-7963-96a9-853595add402 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191332 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292707 | insert_019f4325-66be-7206-bd12-f1d36d23d326 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191232 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292697 | insert_019f4324-7c5a-7163-b476-82ceebc635dc | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191132 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292685 | insert_019f4323-91f6-73e2-a7a5-f7ffe838d157 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 191032 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292675 | insert_019f4322-a792-703c-b5b5-5323d098f3c5 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190932 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292661 | insert_019f4321-bd2e-73cb-a6ff-84a08ccd7646 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190832 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292647 | insert_019f4320-d2c4-7b39-a76a-c9b634bf61d5 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190732 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292639 | insert_019f431f-e860-79f4-9c8a-1af5b33a540d | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190632 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292626 | insert_019f431e-fdfb-7872-ad7c-69796de24bbc | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190532 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292615 | insert_019f431e-1397-778d-b686-936bc372bb62 | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190432 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | | 292605 | insert_019f431d-2933-7258-84ab-914106939ede | FE: 172.30.89.99 | PREPARE | INSERT_STREAMING | 2026-07-08 190332 | NULL | NULL | NULL | NULL | | 0 | [-1] | 3600000 | 86400000 | | My transactions to statistics db is stuck. How do i resolve this. It is climbing non stop

The climbing number of transactions in the

_statistics_

database in a

PREPARE

state indicates that StarRocks' internal automatic statistics collection is hanging or running slower than the trigger interval (default 5 minutes). This is common when the cluster is under heavy resource pressure (like your 1.46TB cache warming) or if the internal

_statistics_

tables have too many versions or tablet issues. To resolve this and stop the transaction count from climbing, follow these steps: 1. Stop the "Bleeding" (Disable Auto-Collection) Disable the automatic statistics collection mechanism globally to prevent new transactions from being created.

sql
-- Disable automatic full statistics collection
SET GLOBAL enable_collect_full_statistic = false;

-- Disable general automatic statistics collection (v3.1+)
SET GLOBAL enable_auto_collect_statistics = false;

2. Identify and Kill Running Analyze Jobs Check which tables are currently being analyzed and kill the active tasks.

sql
-- View current and historical analyze jobs
SHOW ANALYZE STATUS;

-- If you see 'RUNNING' jobs, kill them using their ID from the output above
KILL ANALYZE <analyze_job_id>;

3. Clear Stuck Transactions The transactions you see in

PREPARE

are likely internal

INSERT

tasks. Since they have a

TimeoutMs

of 3,600,000 (1 hour), they will eventually abort on their own, but you can manually cancel them using their labels:

sql
-- Cancel specific stuck insert jobs
CANCEL LOAD FROM _statistics_ WHERE LABEL = "insert_019f4338-a10b-7f8b-bcae-e8e6b6e48162";

Note: If there are hundreds, it is usually better to wait for the 1-hour timeout after disabling the global variables in Step 1. 4. Investigate the

_statistics_

Database Health If these transactions frequently get stuck, the

_statistics_

tables themselves might be unhealthy (e.g., too many metadata versions). * Check tablet health: Run

SHOW TABLE STATUS FROM _statistics_;

and check for high

DataSize

or issues. * Check FE Logs: Search

fe.log

for keywords like

StatisticExecutor

or

transaction manager is full

to see why the

PREPARE

phase is failing to move to

COMMITTED

. Summary of Recommendations * During Cache Warming: Keep

enable_collect_full_statistic

set to

false

until your manual

CACHE SELECT

operations are complete. Heavy I/O from warming 1.46TB of data can cause the internal statistics DMLs to time out. * After Cluster Stability: Once the CN nodes are stable and data is cached, re-enable statistics collection if needed, but consider increasing the interval or using

SAMPLE

collection for large tables to reduce overhead. References • knowledge/trouble_shooting/fe_deadlock_problems.md

@Rocky MySQL [(none)]> SET GLOBAL enable_auto_collect_statistics = false; ERROR 1193 (HY000): Getting analyzing error. Detail message: Unknown system variable 'enable_auto_collect_statistics', the most similar variables are {'enable_delta_lake_column_statistics', 'enable_iceberg_column_statistics', 'enable_labeled_column_statistic_output'}. MySQL [(none)]> SET GLOBAL enable_collect_full_statistic = false; ERROR 1193 (HY000): Getting analyzing error. Detail message: Unknown system variable 'enable_collect_full_statistic', the most similar variables are {'enable_delta_lake_column_statistics', 'enable_collect_table_level_scan_stats', 'enable_connector_sink_spill'}.

The error you encountered is because

enable_statistic_collect

and

enable_collect_full_statistic

are FE Configuration Items, not system variables. They cannot be modified using

SET GLOBAL

. To stop the climbing transactions and disable automatic statistics collection, use the

ADMIN SET CONFIG

command: 1. Disable Statistics Collection (Correct Commands) Run these commands to turn off the background statistics tasks immediately:

sql
-- Disable the main statistics collection switch
ADMIN SET CONFIG ("enable_statistic_collect" = "false");

-- Disable full statistics collection
ADMIN SET CONFIG ("enable_collect_full_statistic" = "false");

-- (Optional) Disable collection triggered by new data loads (v3.1+)
ADMIN SET CONFIG ("enable_statistic_collect_on_first_load" = "false");

2. Kill Active Analyze Jobs Even after disabling the config, currently running jobs may keep transactions open. Check and kill them:

sql
-- View running jobs
SHOW ANALYZE STATUS;

-- Kill any job in 'RUNNING' state
KILL ANALYZE <job_id>;

3. Why are transactions "climbing"? The

INSERT_STREAMING

transactions you see in

_statistics_

are internal StarRocks tasks trying to write metadata to system tables. They are likely stuck in

PREPARE

because: * Resource Contention: Your 1.46TB cache warming is likely saturating the disk I/O or network, preventing the metadata transactions from committing. * Metadata Lock: The statistics update requires a lock on the

_statistics_

database/tables. If a previous job is hung or your cache warming query is holding a wide lock, new statistics jobs will queue up indefinitely. 4. How to clear the existing 292,000+ transactions Since these are internal transactions, they will stay in the

PREPARE

state until they hit their

TimeoutMs

(shown as 3,600,000ms or 1 hour in your table). * Immediate fix: Once you run the

ADMIN SET CONFIG

commands above, no new transactions will be created. * Cleanup: The existing ones will eventually move to

ABORTED

once they time out. You do not typically need to manually cancel 200k transactions one by one; the system will clear them automatically after the 1-hour window. Recommendation for your Cache Warming When performing a large-scale

CACHE SELECT

(1.4TB+): 1. Always disable stats collection first using the

ADMIN

commands above. 2. Break the warmup into smaller batches (e.g., partition by partition) to avoid long-running transactions that can interfere with FE metadata health. 3. Re-enable stats collection only after the warmup is finished and the cluster load has stabilized. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SET_CONFIG.md References • knowledge/trouble_shooting/fe_deadlock_problems.md • loading/loading_introduction/troubleshooting_loading.md

@Rocky The SQL blacklist for

select *

is blocking transactions

Root cause

  A SQL blacklist rule is silently breaking the internal statistics housekeeping loop.

  The offending rule (SHOW SQLBLACKLIST):
  Id: 0
  Forbidden SQL: (?i)select\s+\*\s+from\s+.+
  enable_sql_blacklist = true. This regex blocks every SELECT * FROM <anything> statement cluster-wide. It was added at ~18:44 today (first rejection: 18:44:32), which lines up exactly with when the stuck transactions began piling up (oldest running txn 18:47).

  The mechanism (from the FE journal, thread Load history syncer|61, once every 60s at :32):
  1. The syncer daemon fires INSERT INTO _statistics_.loads_history SELECT * FROM information_schema.loads WHERE ...
  2. beginTransaction runs first → txn enters PREPARE
  3. The blacklist check then rejects the statement: This sql is in blacklist (id: 0) — because the INSERT's body contains SELECT * FROM information_schema.loads
  4. The rejection path never aborts the already-begun transaction → it's orphaned in PREPARE (never reaches PREPARED/COMMITTED, hence all the NULL timestamps)
  5. Repeats every minute → 61 accumulated

How can i fix it? Make it particular to db? SO blacklist select * from requests and select * from requests.requests

To fix the transaction pile-up and refine your SQL blacklist, you should replace the global "catch-all" rule with a more specific one that targets your table while excluding system databases. 1. Stop the transaction pile-up The rule at Index 0 is currently blocking StarRocks' own internal housekeeping. You must remove it immediately to allow the system to recover.

sql
-- Remove the problematic global rule
DELETE SQLBLACKLIST 0;

Once the rule is deleted, the internal syncer daemon will stop creating orphaned transactions. The existing 292,000+ transactions will eventually time out (based on their

TimeoutMs

, usually 1 hour) and clear themselves. If the FE's memory or performance is severely impacted, a rolling restart of the FEs would clear the transaction manager immediately. 2. Refine the Blacklist for the

requests

table To block

SELECT _

only for your specific table and its fully qualified name, use a more restrictive regex. This prevents matching internal queries like

SELECT _ FROM information_schema.loads

. Run this command to target the

requests

table specifically:

sql
-- Targets 'requests' or 'requests.requests' specifically
ADD SQLBLACKLIST "(?i)select\\s+\\*\\s+from\\s+(requests|requests\\.requests)(\\s+|$|;)";

*3. (Alternative) Block

SELECT *

for everything EXCEPT system DBs* If you want to maintain a general ban on

SELECT *

for all user tables but allow StarRocks to perform its internal tasks, use a negative lookahead to ignore system databases (

_statistics_

,

information_schema

, etc.):

sql
-- Blocks SELECT * from any table UNLESS it starts with information_schema or _statistics_
ADD SQLBLACKLIST "(?i)select\\s+\\*\\s+from\\s+(?!(information_schema|_statistics_))\\S+";

Why this happened In StarRocks, when a SQL blacklist rule matches an internal query (like the stats syncer): 1. The transaction is started (State:

PREPARE

). 2. The blacklist check fails. 3. Because the rejection happens during the analysis phase of an internal background task, the transaction isn't always explicitly aborted by the daemon, leaving it "orphaned" until it hits the 1-hour

TimeoutMs

. Recommendation: Always use

SHOW SQLBLACKLIST

after adding a rule and test it with a manual query to ensure it doesn't accidentally catch system keywords. References • administration/management/resource_management/Blacklist.md • sql-reference/sql-statements/cluster-management/sql_blacklist/ADD_SQLBLACKLIST.md • sql-reference/sql-statements/cluster-management/sql_blacklist/DELETE_SQLBLACKLIST.md References • administration/management/resource_management/Blacklist.md • sql-reference/sql-statements/cluster-management/sql_blacklist/DELETE_SQLBLACKLIST.md • sql-reference/sql-statements/cluster-management/nodes_processes/DELETE_BACKEND_BLACKLIST.md • sql-reference/sql-statements/cluster-management/sql_blacklist/ADD_SQLBLACKLIST.md • administration/management/monitor_manage_big_queries.md

@Rocky In shared data architecture, does

show data from <table>

count s3 stored data or only cached data?

In a shared-data (Cloud Native) architecture, the command

SHOW DATA FROM <table_name>

counts the total persistent data stored in object storage (S3). It does not represent the amount of data currently residing in the local disk cache of your Compute Nodes (CN). Key Distinctions * Persistent Storage (S3): This is the source of truth for the table's size. When you run

SHOW DATA

or query

information_schema.partitions_meta

, the

DATA_SIZE

column reflects the size of the segment files stored on S3. * Local Cache: This is a transient copy of "hot" data stored on the CN nodes' local disks for acceleration. The cache size is limited by your CN configurations (e.g.,

starlet_star_cache_disk_size_percent

) and its contents change based on LRU eviction or TTL. How to verify You can see the relationship between the data size and the S3 path using the following command:

sql
SHOW PROC '/dbs/<db_id>/<table_id>/partitions';

In the output: * DataSize: Shows the size of the data in S3. * StoragePath: Displays the S3 bucket and prefix where this data is persisted. Monitoring Cache Standard SQL commands like

SHOW DATA

do not currently provide a table-by-table breakdown of how much data is "cached" vs "uncached." To monitor the health and performance of your cache, you should look at the CN metrics (typically via Prometheus/Grafana) for: * `starcache_disk_usage`: Total disk space used by the cache. * `starcache_read_bytes`: Total bytes read. * `starcache_skip_read_bytes`: Bytes read directly from S3 (cache misses). References • introduction/Architecture.md • deployment/shared_data/s3.md • knowledge/admin_and_operation/shared_data_compaction.md References • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky So if the total DataCacheMetrics (DiskUsage) matches my data size from show data, then I can say all data is 100% cached?

MySQL [(none)]> show compute nodes;
+---------------+--------------+---------------+--------+----------+----------+---------------------+---------------------+-------+----------------------+-----------------------+--------+---------------+----------+----------+-------------------+------------+------------+-----------------------------------------------------------------+----------------+------------+-------------+----------+-------------------+-----------+
| ComputeNodeId | IP           | HeartbeatPort | BePort | HttpPort | BrpcPort | LastStartTime       | LastHeartbeat       | Alive | SystemDecommissioned | ClusterDecommissioned | ErrMsg | Version       | CpuCores | MemLimit | NumRunningQueries | MemUsedPct | CpuUsedPct | DataCacheMetrics                                                | HasStoragePath | StatusCode | StarletPort | WorkerId | WarehouseName     | TabletNum |
+---------------+--------------+---------------+--------+----------+----------+---------------------+---------------------+-------+----------------------+-----------------------+--------+---------------+----------+----------+-------------------+------------+------------+-----------------------------------------------------------------+----------------+------------+-------------+----------+-------------------+-----------+
| 386018        | 172.30.72.74 | 9050          | 9060   | 8040     | 8060     | 2026-07-06 17:19:22 | 2026-07-08 20:53:18 | true  | false                | false                 |        | 4.0.7-b75f536 | 8        | 41.702GB | 0                 | 40.72 %    | 0.4 %      | Status: Normal, DiskUsage: 565.7GB/3.3TB, MemUsage: 8.3GB/8.3GB | true           | OK         | 9070        | 15394    | default_warehouse | 985       |
| 386020        | 172.30.76.86 | 9050          | 9060   | 8040     | 8060     | 2026-07-06 17:19:32 | 2026-07-08 20:53:18 | true  | false                | false                 |        | 4.0.7-b75f536 | 8        | 41.702GB | 0                 | 40.29 %    | 0.3 %      | Status: Normal, DiskUsage: 593.8GB/3.3TB, MemUsage: 8.3GB/8.3GB | true           | OK         | 9070        | 15395    | default_warehouse | 984       |
| 386019        | 172.30.88.64 | 9050          | 9060   | 8040     | 8060     | 2026-07-07 19:32:31 | 2026-07-08 20:53:18 | true  | false                | false                 |        | 4.0.7-b75f536 | 8        | 41.702GB | 0                 | 41.04 %    | 0.3 %      | Status: Normal, DiskUsage: 337.6GB/3.3TB, MemUsage: 8.3GB/8.3GB | true           | OK         | 9070        | 15393    | default_warehouse | 934       |
+---------------+--------------+---------------+--------+----------+----------+---------------------+---------------------+-------+----------------------+-----------------------+--------+---------------+----------+----------+-------------------+------------+------------+-----------------------------------------------------------------+----------------+------------+-------------+----------+-------------------+-----------+
3 rows in set (0.002 sec)

MySQL [(none)]> show data from events.events;
+-----------+-----------+----------+--------------+------------+
| TableName | IndexName | Size     | ReplicaCount | RowCount   |
+-----------+-----------+----------+--------------+------------+
| events    | events    | 1.462 TB | 2704         | 3787697409 |
|           | Total     | 1.462 TB | 2704         |            |
+-----------+-----------+----------+--------------+------------+
2 rows in set (0.001 sec)

Yes, based on the numbers you provided, it is highly likely that your table is 100% cached. Your math is almost a perfect match: * Total DiskUsage: $565.7\text{ GB} + 593.8\text{ GB} + 337.6\text{ GB} = \mathbf{1497.1\text{ GB}}$ * Table Size (Show Data): $1.462\text{ TB} \times 1024 = \mathbf{1497.088\text{ GB}}$ Since the total cached data across your Compute Nodes (CNs) matches the table size to the first decimal point, you have successfully warmed up the entire dataset into the local disk cache. Important Caveats While the matching numbers are a very strong indicator, keep the following in mind: 1. Global Metric:

DiskUsage

in

DataCacheMetrics

is a node-level metric. If you have any other tables in the cluster, their cached data is also included in that $1497.1\text{ GB}$ total. If this is your only table, the 100% conclusion is solid. 2. Metadata and Overhead: The cache storage includes a small amount of overhead for the cache index and metadata. However, at the TB scale, this is usually negligible. 3. ReplicaCount: In the shared-data architecture, the

ReplicaCount

(2704) in your

SHOW DATA

output refers to the total number of tablets (partitions $\times$ buckets). Unlike shared-nothing mode, there is only one physical copy of the data on S3. Each tablet is assigned to a specific CN for caching to avoid duplicate cache entries across nodes. How to verify with 100% certainty To confirm that a specific query is reading entirely from the cache without hitting S3, run a

SELECT

query and check the Query Profile: 1. Run your query:

SELECT count(*) FROM events.events;

2. Get the profile:

get_query_profile('<query_id>');

3. Look for the Scan Node section. You want to see: *

DataCacheHitBytes

should equal the total bytes read. *

RemoteReadBytes

(or similar S3 read metrics) should be 0. * Alternatively, monitor the

starcache_hit_ratio

metric in Grafana during the query; it should be

1.0

. Summary You can confidently say the data is cached. The fact that the $1.462\text{ TB}$ is distributed across your 3 nodes ($565\text{ GB}$, $593\text{ GB}$, $337\text{ GB}$) also shows that StarRocks has balanced the tablets across the warehouse for caching. References • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky MySQL [(none)]> explain analyze select min(request_time) from events.events; +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Explain String | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Summary | | QueryId: 019f4385-125f-79db-a1fc-91ffc8f4fae5 | | Version: 4.0.7-b75f536 | | State: Finished | | TotalTime: 6s176ms | | ExecutionTime: 6s157ms [Scan: 6s43ms (98.15%), Network: 1.140ms (0.02%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 6s155ms (99.96%)] | | CollectProfileTime: 2ms | | FrontendProfileMergeTime: 1.638ms | | QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 14.734 GB | | Top Most Time-consuming Nodes: | | 1. OLAP_SCAN (id=0) : 6s49ms (99.97%) | | 2. MERGE_EXCHANGE (id=2) [GATHER]: 1.333ms (0.02%) | | 3. TOP_N (id=1) [ROW_NUMBER, TOP-N]: 200.703us (0.00%) | | 4. AGGREGATION (id=3) [finalize, update]: 65.845us (0.00%) | | 5. RESULT_SINK: 47.322us (0.00%) | | Top Most Memory-consuming Nodes: | | NonDefaultVariables: | | enable_adaptive_sink_dop: false -> true | | enable_async_profile: true -> false | | enable_profile: false -> true | | Fragment 0 | | │ BackendNum: 1 | | │ InstancePeakMemoryUsage: 229.594 KB, InstanceAllocatedMemoryUsage: 244.125 KB | | │ PrepareTime: ? | | └──RESULT_SINK | | │ TotalTime: 47.322us (0.00%) [CPUTime: 47.322us] | | │ OutputRows: 1 | | │ SinkType: MYSQL_PROTOCAL | | └──AGGREGATION (id=3) [finalize, update] | | │ Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 7.5798916028E10] | | │ TotalTime: 65.845us (0.00%) [CPUTime: 65.845us] | | │ OutputRows: 1 | | │ PeakMemory: ?, AllocatedMemory: ? | | │ AggExprs: [min(1: request_time)] | | └──MERGE_EXCHANGE (id=2) [GATHER] | | Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 7.5798916008E10] | | TotalTime: 1.333ms (0.02%) [CPUTime: 193.121us, NetworkTime: 1.140ms] | | OutputRows: 1 | | PeakMemory: ?, AllocatedMemory: ? | | | | Fragment 1 | | │ BackendNum: 3 | | │ InstancePeakMemoryUsage: 37.081 MB, InstanceAllocatedMemoryUsage: 14.734 GB | | │ PrepareTime: ? | | └──DATA_STREAM_SINK (id=2) | | │ PartitionType: UNPARTITIONED | | └──TOP_N (id=1) [ROW_NUMBER, TOP-N] | | │ Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 7.5798915976E10] | | │ TotalTime: 200.703us (0.00%) [CPUTime: 200.703us] | | │ OutputRows: 3 | | │ PeakMemory: ?, AllocatedMemory: ? | | │ OrderByExprs: [<slot 1> 1: request_time] | | └──*OLAP_SCAN (id=0)* | | Estimates: [row: 3789945798, cpu: ?, memory: ?, network: ?, cost: 1.5159783192E10] | | TotalTime: 6s49ms (99.97%) [CPUTime: 6.422ms, ScanTime: 6s43ms] | | OutputRows: 98.304K (98304) | | RuntimeFilter: 98.304K (98304) -> 98.304K (98304) (0.00%) | | Table: : events | | Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime] | | IOTaskExecTime: 4s359ms [min=2s354ms, max=6s23ms] | | CreateSegmentIter: 4s122ms [min=2s280ms, max=5s463ms] | | IOTaskWaitTime: 8.737ms [min=1.183ms, max=88.788ms] | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------+In that case, why for this query profile for simple query, it had to fetch from S3?

The 6-second

ScanTime

in your profile does not mean the data was fetched from S3. In StarRocks,

ScanTime

and

IOTaskExecTime

represent the time spent by the I/O threads reading data, regardless of whether that data comes from the local disk cache or remote S3. Based on your profile, here is what is actually happening: 1. Verification of Cache Hits To confirm if it hit S3, you need to look further down in the

OLAP_SCAN

section of the profile (which was truncated in your message). Look for these specific counters: *

DataCacheReadBytes

: If this matches your total read size, it was 100% Cache. *

DataCacheWriteBytes

: If this is 0, then no data was fetched from S3 during this query. 2. Why did it take 6 seconds if it's cached? Even when data is 100% cached on local NVMe/SSD, a query can take several seconds for these reasons: *

CreateSegmentIter

(4.12s): This is the biggest bottleneck in your profile. It represents the time spent opening segment files and initializing the iterators. Because you have 2,704 tablets and likely many more segment files, the overhead of opening thousands of local files adds up. * Full Column Scan: Your query is

min(request_time)

. If

request_time

is not the first column in your

DUPLICATE KEY

(the Sort Key), StarRocks must scan the entire

request_time

column across all 3.7 billion rows to find the minimum value. * Aggregation Overhead: Although

min()

is simple, it still requires reading the data into memory, handling nulls, and performing the comparison across 98,304 internal chunks (as seen in your

OutputRows

from the scan node). 3. How to speed this up If you need this specific query to be faster: 1. Check the Sort Key: Ensure

request_time

is the first column in your

PROPERTIES("short_key" = "...")

or the first column in your table definition. If it is the primary sort key, StarRocks can find the

min

by just looking at the first block of the first segment, making it near-instant. 2. Tablet Count: 2,704 tablets for a 1.5TB table is reasonable, but if you have thousands of small files per tablet (check

show fragments from events

), consider triggering a compaction to reduce the

CreateSegmentIter

overhead. 3. Use a Materialized View: If you frequently query the min/max of this table, an asynchronous Materialized View can pre-aggregate this value. Summary: Your query is likely running entirely from the local cache. The 6-second latency is caused by the overhead of processing 3.7 billion rows across thousands of file segments, not S3 network latency. References • data_source/data_cache.md • best_practices/query_tuning/query_profile_operator_metrics.md References • data_source/data_cache.md • best_practices/query_tuning/query_profile_operator_metrics.md • administration/management/resource_management/Blacklist.md • sql-reference/sql-statements/cluster-management/sql_blacklist/ADD_SQLBLACKLIST.md • sql-reference/sql-statements/cluster-management/sql_blacklist/DELETE_SQLBLACKLIST.md • administration/management/monitor_manage_big_queries.md • introduction/Architecture.md • deployment/shared_data/s3.md

@Rocky PRIMARY KEY(

request_time

,

auction_id

,

imp_key

) COMMENT "OLAP" PARTITION BY date_trunc('hour', request_time) DISTRIBUTED BY HASH(

auction_id

) BUCKETS 16 ORDER BY(

request_time

,

auction_id

) PROPERTIES ( "compression" = "ZSTD", "datacache.enable" = "true", "datacache.partition_duration" = "5 days", "enable_async_write_back" = "false", "enable_persistent_index" = "true", "file_bundling" = "true", "partition_live_number" = "168", "persistent_index_type" = "CLOUD_NATIVE", "replication_num" = "1", "storage_volume" = "core_services_volume" ); This is my table property. Each partition is approximately 10 gigabytes with 16 buckets.

Based on your table properties and the query profile, here is the breakdown of why your query took 6 seconds and confirmation that it was almost certainly not fetching data from S3. 1. Did it fetch from S3? No. You can confirm this by looking at two specific counters in the full

OLAP_SCAN

section: * `DataCacheWriteBytes`: If this is 0, then no data was downloaded from S3 during the query. *

RemoteReadBytes

or `CompressedBytesReadRemote`: These should also be 0 if you are 100% cached. The 6-second "ScanTime" in the profile is a measurement of the I/O pipeline duration, which includes reading from your local NVMe/SSD cache. In StarRocks, "Scan" refers to the process of retrieving data from the storage layer (local or remote). 2. Why 6 seconds for a "simple" Min()? In a shared-data Primary Key table, a

min()

query is not a simple metadata lookup because of the Primary Key (Delete+Insert) Architecture. * Row Visibility (DelVector): Unlike a Duplicate Key table, a Primary Key table must check the

DelVector

(delete markers) for every row to ensure that the minimum value you find hasn't been deleted or updated by a newer version. This prevents a "short-circuit" optimization where StarRocks simply looks at the first block of the first segment. *

CreateSegmentIter

(4.12s): This is your main bottleneck. It is the time spent opening the thousands of segment files across your 2,704 tablets. * With 168 partitions and 16 buckets, you have ~2,700 tablets. * Each node is managing ~900 tablets. * Opening and initializing the iterators for 900 tablets (and their associated segments and delete vectors) takes several seconds, even if the data is local. 3. Analysis of your Table Design Your table design is quite good, but there are two factors contributing to the latency: * Tablet Granularity: You have ~2,700 tablets for 1.5TB, which is ~550MB per tablet. For massive scans, StarRocks generally performs better with larger tablets (1GB–10GB). Having so many "small" tablets increases the metadata overhead and the

CreateSegmentIter

time. * Persistent Index: You are using

CLOUD_NATIVE

persistent indexes. While these are cached locally, StarRocks still needs to verify the index state during the scan to handle the Primary Key logic. Recommendations for Optimization If you need this query to be sub-second: 1. Reduce Buckets: If you don't need 16 buckets for every hourly partition (e.g., if you don't query just one hour at a time), consider reducing

BUCKETS

to 4 or 8. This will reduce the total tablet count and the

CreateSegmentIter

overhead. 2. Trigger Compaction: High

CreateSegmentIter

time often points to many small "rowsets" (versions of data). Check the version count:

sql
    SHOW TABLET FROM events.events;

If

VersionCount

is high (>10-20), the system is doing a "merge-on-read" of many small files. Increasing compaction aggressiveness can help. 3. Use a Materialized View: Since you are using a Primary Key table, you can create a synchronous or asynchronous Materialized View to pre-calculate the

min(request_time)

if this is a high-frequency query. Summary: Your data is 100% cached. The 6 seconds is the "cost of doing business" for a Primary Key table ensuring data consistency across 3.7 billion rows and 2,700 tablets.