< Rocky> can statistics and force streaming reduce memory co StarRocks #questions-and-troubleshooting

Join Slack

<@U0A71G31CDV> can statistics and force_streaming ...

# questions-and-troubleshooting

ansh.gupta2

09/02/2026, 6:13 PM

@Rocky can statistics and force_streaming reduce memory consumption and boost query performace?

Rocky

09/02/2026, 6:13 PM

Yes, both statistics and force_streaming
(specifically the

streaming_preaggregation_mode

setting) are effective tools for reducing memory consumption and improving query performance in StarRocks, particularly for aggregation-heavy workloads. 1. Statistics Statistics are the foundation of the Cost-Based Optimizer (CBO). Accurate statistics help the system: * Reduce Memory: By providing cardinality and data distribution info, the CBO avoids "bad plans" such as broadcasting a large table (which can lead to OOM) or choosing the wrong join order. * Boost Performance: It allows the optimizer to choose the most efficient execution strategy (e.g., Shuffle Join vs. Broadcast Join) and decide whether to use pre-aggregation. * Recommendation: Ensure you have up-to-date statistics by running

ANALYZE TABLE <table_name>

. StarRocks v2.4+ performs automatic collection by default. 2.
force_streaming
(streaming_preaggregation_mode) This refers to the session variable

streaming_preaggregation_mode

. In StarRocks, many aggregations happen in two phases: local pre-aggregation and global aggregation. * How it reduces memory: By default (

auto

), StarRocks tries to pre-aggregate data in a local hash table. If the data has high cardinality (few duplicate keys), this hash table can grow very large and consume significant memory without actually reducing the data size. Setting this to

force_streaming

skips the local hash table creation and streams data directly to the next phase. * How it boosts performance: For high-cardinality data, the CPU cycles spent trying to pre-aggregate are wasted.

force_streaming

eliminates this overhead, speeding up the first phase of the query. * Usage:

Copy code

sql
    SET streaming_preaggregation_mode = 'force_streaming';

GROUP BY

on high-cardinality columns (e.g., IDs, timestamps). Related Tip:
enable_sort_aggregate
If your data is already sorted by the

GROUP BY

keys, you can also use:

Copy code

sql
SET enable_sort_aggregate = true;

This enables Sorted Streaming Aggregate, which further reduces memory by processing data in sequence without any hash tables. References • sql-reference/System_variable.md • using_starrocks/sorted_aggregate.md

Rocky

09/02/2026, 6:32 PM

Tuning a StarRocks cluster for both memory efficiency and query speed requires a multi-layered approach, ranging from system-level configurations to schema design. 1. Memory Management Tuning Memory is primarily consumed by queries, data loading, and metadata/cache. * Adjust BE Process Limits: *

mem_limit

(BE config): By default, it is 90% of the machine's RAM. If you share the machine with other services, lower this (e.g.,

80%

). *

query_max_memory_limit_percent

(v3.1+): Defines how much of the process memory is allocated to the Query Pool (default 90%). * `load_process_max_memory_limit_percent`: Limits memory for data ingestion (default 30%). If your cluster is query-heavy but has low-frequency loads, you can lower this to free up more RAM for queries. * Per-Query Control: * query_mem_limit
: Set this session variable to cap the memory a single query can consume on each BE. This prevents a "rogue" query from crashing nodes. * Intermediate Spilling: If queries frequently hit memory limits, enable spilling to disk:

Copy code

sql
        SET spill_mode = 'auto';

This trades some performance for stability, preventing OOM (Out Of Memory) errors by writing temporary data to disk. * Resource Groups: Use Resource Groups to isolate workloads. You can create a "Big Query" group with strict memory limits and a lower

spill_mem_limit_threshold

to ensure analytical tasks don't starve small, high-concurrency dashboard queries. 2. Query Performance Optimization Faster queries often result in lower memory consumption because resources are held for a shorter duration. * Optimize Data Distribution: * Colocate Join: If you frequently join two large tables, use the same

DISTRIBUTED BY

keys and assign them to the same

colocate_with

group. This allows "Local Joins," eliminating network shuffle and drastically reducing memory/CPU usage. * Proper Bucketing: Ensure your bucket count is reasonable (aim for 1–5 GB per tablet). Too many tablets increase metadata overhead; too few limit parallelism. * Schema Enhancements: * Sort Keys (Clustering): Choose sort keys based on your

WHERE

clause filters. This enables "Storage Index" filtering (min/max), which skips reading irrelevant data blocks entirely. * Global Dictionary: For high-cardinality strings used in

COUNT(DISTINCT)

GROUP BY

, use the Global Dictionary (often with AUTO_INCREMENT) to convert strings to integers. Processing integers is much faster and uses less memory than strings. * CBO & Statistics: * Always ensure statistics are up to date (

ANALYZE TABLE

). The Cost-Based Optimizer (CBO) uses this to choose the best join order (e.g., putting the smallest table on the "right" for a Broadcast Join). 3. Monitoring for Bottlenecks * Audit Log Analysis: Query the

starrocks_audit_db__

to find queries with the highest

memCostBytes

. * Compaction Score: Check

show proc '/compactions'

. If the score is high (e.g., >100), query performance will degrade due to too many small files. Increase

compact_threads

in the BE config to accelerate merging. * Query Profile: Use

EXPLAIN ANALYZE <SQL>

to see exactly which operator (Join, Aggregation, or Scan) is consuming the most memory. References • administration/management/BE_configuration.md • administration/management/resource_management/Memory_management.md • using_starrocks/Colocate_join.md • best_practices/query_tuning/schema_tuning.md References • best_practices/query_tuning/query_hint.md

Rocky

09/02/2026, 6:32 PM

• using_starrocks/Colocate_join.md • using_starrocks/query_acceleration_with_auto_increment.md • best_practices/query_tuning/schema_tuning.md • best_practices/query_tuning/query_planning.md

ansh.gupta2

09/02/2026, 6:33 PM

@Rocky why got this error and how to avoid it 1788231357159,116,JDBC Request - q0,42000 1064,"java.sql.SQLSyntaxErrorException: only found column statistics: {2: id, 4: order_release_id_fk, 37: packed_on, 1085: cast, 14: courier_code, 1086: cast, 63: order_created_on, 1088: cast}, but missing statistic of col: 73: part_created_on.",Thread Group 1-6,text,false,,204,0,6,6,null,0,0,1

Rocky

09/02/2026, 6:34 PM

The error

SQLSyntaxErrorException: only found column statistics: {...}, but missing statistic of col: ...

is a known issue in the StarRocks Cost-Based Optimizer (CBO). It occurs when the query planner expects statistical data for a specific column (in your case,

part_created_on

) to calculate the optimal execution plan, but finds the statistics metadata is missing, corrupted, or mismatched. Why this happens 1. Schema Changes: This is the most common cause. If you recently performed an

ALTER TABLE

(e.g., added or renamed a column), the internal column IDs used by the optimizer may have become inconsistent with the existing statistics. 2. Planner Bugs: Certain StarRocks versions (specifically in the 3.1.x through 3.4.x series) have known bugs where specific SQL patterns (like

DISTINCT

with

ORDER BY

, window functions, or complex joins) fail to correctly map column statistics. 3. External Catalogs: This error frequently appears when querying external tables (e.g., via Hive, Paimon, or JDBC catalogs) if the metadata synchronization is incomplete. How to avoid and fix it 1. Manually Re-collect Statistics Force the system to refresh the statistics for the specific table. This often re-aligns the internal column IDs.

Copy code

sql
-- Replace <table_name> with your actual table
ANALYZE TABLE <table_name> WITH SYNC;

Note: Using
WITH SYNC
ensures the collection completes before the next query runs. 2. Temporary Workaround (Session Variable) If re-collecting stats doesn't work, you can bypass the optimizer's requirement for these statistics. This will allow the query to run, though it might use a slightly less efficient plan:

Copy code

sql
-- Disable the use of statistics for the current session
SET optimizer_use_statistics = false;

-- OR disable the new planner's specific statistics requirement (if applicable)
SET enable_stats = false;

3. Clear Stale Statistics If the metadata is corrupted, you can try dropping the existing statistics and letting the automatic collection rebuild them:

Copy code

sql
DROP STATS <table_name>;

4. Check for Version Fixes This is a recognized bug in several versions. If you are on an older sub-version of 3.1, 3.2, or 3.3, upgrading to the latest patch release (e.g., v3.3.10+ or v3.4.2+) often resolves the underlying planner issue. Recommendation Start by running

ANALYZE TABLE ... WITH SYNC

. If the error persists, check if the query involves a

DISTINCT

and an

ORDER BY

on different columns, as this is a known trigger that has specific fixes in newer StarRocks releases. References • sql-reference/information_schema/statistics.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_META.md

ansh.gupta2

09/02/2026, 6:38 PM

@Rocky I am testing in CN for external catalog , if stats are incomplete for table, is it possible we can bypass this run query normally without throwing error, I mean if stats available fine then use them, if looks incomplete or fishy run normally, ideally this should be the behaviour?

Rocky

09/02/2026, 6:38 PM

Ideally, StarRocks should fall back to a heuristic-based plan (using default cost estimates) if statistics are missing. However, the error you are seeing is a recognized issue where the Cost-Based Optimizer (CBO) enforces a strict check on column statistics but fails to find them, leading to a query crash rather than a graceful fallback. This is particularly common in External Catalogs combined with Compute Nodes (CN) because statistics for external tables are managed in a separate metadata cache that can occasionally become inconsistent or stale. How to Bypass and Run the Query Normally If you want the query to run regardless of the "incomplete or fishy" statistics, use the following methods: 1. The Global "Bypass" (Recommended for testing) To stop the optimizer from failing on missing statistics, you can disable the use of statistics for the current session. The optimizer will still use the CBO framework but will rely on default values instead of the missing metadata.

Copy code

sql
SET optimizer_use_statistics = false;
-- OR
SET enable_stats = false;

Note: This will prevent the "Missing statistic" error, but the query plan might not be as efficient as one with full statistics. 2. Automatic Healing (v3.4.0 and later) If you are on version 3.4 or higher, you can enable Query-Triggered Analyze. This feature detects when a query needs statistics and automatically triggers a background collection task.

Copy code

sql
SET enable_query_trigger_analyze = true;

3. Force Refresh for External Tables For external catalogs (Hive, Iceberg, Hudi, etc.), statistics are stored in the

default_catalog._statistics_.external_column_statistics

table. If the optimizer is "stuck," manually re-syncing the stats for that specific column usually fixes the inconsistency:

Copy code

sql
-- Manually collect the missing column to "fill the gap"
ANALYZE TABLE <catalog_name>.<db_name>.<table_name>(part_created_on) WITH SYNC;

Why does this happen? * Schema Drift: If the external table's schema changed in the source system (e.g., Hive), the column IDs in StarRocks' statistics cache may no longer match the query plan. * External Metadata Latency: On CN-based clusters, there can be a slight lag in replaying the statistics metadata journals to all FE nodes. * Optimizer Bug: In some 3.x versions, specific SQL patterns (like window functions or

DISTINCT

ORDER BY

) have a bug where they require statistics that aren't actually needed, causing this specific exception. Summary Recommendation The ideal behavior you described (use if available, ignore if not) is currently implemented by setting

optimizer_use_statistics = false

. This allows you to bypass the error and continue your CN testing without statistics-related interruptions. References • using_starrocks/Cost_based_optimizer.md • sql-reference/System_variable.md • data_source/Catalog.md • administration/management/resource_management/Compute_node.md References • using_starrocks/Cost_based_optimizer.md • data_source/catalog/maxcompute_catalog.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md

ansh.gupta2

09/03/2026, 6:05 AM

@Rocky didnt find any property with name optimizer_use_statistics

Rocky

09/03/2026, 6:06 AM

The variable you are looking for is likely enable_stats
. The error

only found column statistics: ... but missing statistic of col: ...

occurs because the Cost-Based Optimizer (CBO) has a strict requirement for metadata to calculate the "cost" of a query. If even one column in the query lacks statistics (due to schema changes, external catalog sync issues, or a planner bug), the CBO crashes with this error instead of falling back to a default plan. How to Bypass and "Run Normally" If you want StarRocks to ignore the missing/incomplete statistics and proceed with a default execution plan (heuristics), use:

Copy code

sql
-- Disable the use of statistics for the current session
SET enable_stats = false;

If that doesn't work (depending on your specific StarRocks version), you can temporarily disable the CBO entirely to force the query to run:

Copy code

sql
SET enable_cbo = false;

Note: Disabling CBO should be a last resort as it may significantly slow down complex queries. Why "optimizer_use_statistics" didn't work The property

optimizer_use_statistics

is used in some other SQL engines but is not a standard session variable in StarRocks. StarRocks uses

enable_stats

to control whether the optimizer looks at the statistics table. Recommended Fix for CN & External Catalogs Since you are testing an External Catalog with Compute Nodes (CN), the statistics for external tables are often cached or sampled. If you want the query to benefit from statistics without crashing, you should manually trigger a synchronous collection for the missing column:

Copy code

sql
-- Replace with your actual catalog, db, and table name
-- This forces a sync of the metadata for the specific column
ANALYZE TABLE <catalog>.<db>.<table>(part_created_on) WITH SYNC;

Summary of Behavior * Ideal behavior (Fallback): In a perfectly stable version, the optimizer should fall back to default values. However, the error you see is a known exception in the 3.x and 4.x series where the planner's validation stage is too strict. * Best Workaround: Use

SET enable_stats = false;

for your testing session to bypass the "fishy" statistics and ensure your query always runs. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/System_variable.md • faq/Sql_faq.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/03/2026, 6:07 AM

@Rocky can FULL stats give high performace boost to query

Rocky

09/03/2026, 6:08 AM

Yes, FULL statistics can provide a significant performance boost, particularly for complex queries. While sampled statistics are faster to collect, they can lead the optimizer to make poor decisions if the data is skewed or the sample isn't representative. Here is how FULL statistics improve performance: 1. Accurate Join Reordering (The Biggest Impact) The optimizer uses statistics to decide which table should be the "build" side (left) and which should be the "probe" side (right) in a join. * Sampled Stats: Might underestimate the number of distinct values (NDV) or row counts, leading the optimizer to pick a massive table as the build side, potentially causing Out of Memory (OOM) errors or extreme slowness. * Full Stats: Provide the exact row counts and NDV, ensuring the smaller data set is used to build the hash table, which is much faster. 2. Better Filter Selectivity If you have a

WHERE

clause (e.g.,

WHERE status = 'active'

), FULL statistics provide an accurate count of how many rows match that filter. * With accurate stats, the optimizer can decide whether to use an index, a bucket shuffle join, or a broadcast join. * If the optimizer thinks a filter is very restrictive (but it isn't), it might choose a Broadcast Join that overwhelms the network. 3. Handling Data Skew Sampled statistics often miss "long tail" data or skewed distributions. If 90% of your data belongs to one ID, a sample might only see a few instances of it. FULL statistics capture the entire distribution, allowing the optimizer to choose execution strategies (like skew-join optimizations) that prevent single nodes from becoming bottlenecks. 4. Better Aggregation Plans For

GROUP BY

queries, knowing the exact number of groups helps the engine pre-allocate memory and decide between one-phase or two-phase aggregation. ────────── When should you use FULL vs. SAMPLE? Feature | FULL Statistics | SAMPLE Statistics Accuracy | 100% accurate | Estimated (approximate) Collection Cost | High (scans whole table) | Low (fast, low CPU/IO) Recommended for | Small/Medium tables, Predicate Columns, or tables with high skew. | Large tables (TB+), tables with uniform data distribution. Query Speed | Fastest plans | Good plans (usually), but risky for complex joins. Recommendation for your CN/External Catalog Test: Since you are hitting "missing statistics" errors on an external catalog, you should manually run a FULL collection on the specific columns used in your filters and joins:

Copy code

sql
-- Collect full stats only for the columns that matter (Predicate Columns)
-- This gives you the performance boost of FULL stats without the cost of scanning every column.
ANALYZE TABLE <catalog>.<db>.<table> (id, part_created_on, order_id) WITH SYNC;

Summary: If your query is slow or picking "bad" plans, switching from Sampled to Full statistics is one of the most effective ways to fix it. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • administration/Query_planning.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md

ansh.gupta2

09/03/2026, 6:11 AM

@Rocky does starrocks automatically sync stats, what is the frequency of it?

Rocky

09/03/2026, 6:11 AM

Yes, StarRocks automatically synchronizes statistics for both internal and external tables by default. The frequency depends on the size of the table and whether the collection is triggered by a periodic background task or by a query. 1. Background Automatic Collection (Default) StarRocks has a background thread that periodically checks for data updates and triggers collection based on these default intervals: Feature | Default Setting / Frequency Check Interval | Every 10 minutes (

statistic_collect_interval_sec

= 600). Small Tables (< 10M rows) | Immediately or very frequently (

statistic_auto_collect_small_table_interval

= 0). Large Tables (> 10M rows) | Every 12 hours (

statistic_auto_collect_large_table_interval

= 43200). Small Table Threshold | 10,000,000 rows (

statistic_auto_collect_small_table_rows

). For External Catalogs (Hive, Iceberg), StarRocks specifically checks if the underlying data has changed before triggering these tasks. 2. Query-Triggered Collection (v3.4+) If you are on version 3.4.0 or later, StarRocks uses a more proactive approach for external tables. When you run a query, the system checks if statistics are missing or stale for the columns in that query and automatically queues a background ANALYZE task. * Small Tables: Collected every 2 hours. * Large Tables: Collected every 12 hours. * Schedule Check: The queue is processed every 30 seconds. 3. How to check or change the frequency You can view the current background tasks or the status of statistics collection using these commands:

Copy code

sql
-- View recent collection tasks
SHOW ANALYZE JOB;

-- Check configuration for intervals (FE configuration)
ADMIN SHOW CONFIG LIKE '%statistic%';

Why you might still see "Missing Stats" Even though it is automatic, you might hit the error you mentioned because: 1. The 10-minute check interval hasn't passed yet since data was first loaded. 2. Large tables only refresh every 12 hours, so recent changes might not be reflected. 3. Hudi tables cannot have their data changes detected automatically, so they rely strictly on the 12-hour timer. Recommendation: If you are in a testing phase with frequently changing data, you can lower the interval (e.g.,

ADMIN SET CONFIG (statistic_collect_interval_sec = 60)

) or manually run

ANALYZE TABLE <tbl>

once to "prime" the optimizer. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • faq/Sql_faq.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/resource_management/Compute_node.md -node) • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • data_source/catalog/maxcompute_catalog.md • faq/Sql_faq.md • sql-reference/System_variable.md • data_source/Catalog.md

Rocky

09/03/2026, 6:11 AM

• using_starrocks/query_acceleration/Alter_table_sort_key.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/03/2026, 6:14 AM

@Rocky where does stats store and for big table first time stats take will not time next time it will be incremental or again complete scan?

Rocky

09/03/2026, 6:14 AM

Statistics in StarRocks are stored in several internal tables within the _statistics_
database. The behavior for large tables is optimized to be partition-aware, which avoids a complete table scan if only a portion of the data has changed. 1. Where are stats stored? Depending on the type of table (Internal vs. External) and the type of statistics (Basic vs. Histogram), they are stored in different system tables: Statistic Type | Storage Table (Internal Catalog) Internal Table Stats |

_statistics_.column_statistics

External Table Stats |

_statistics_.external_column_statistics

Histograms |

_statistics_.histogram_statistics

Multi-column Stats |

_statistics_.multi_column_statistics

Sampled Stats (old) |

_statistics_.table_statistic_v1

(Merged into

column_statistics

since v3.5) You can query these tables directly to see metadata like row counts, NDV (Number of Distinct Values), and update times:

Copy code

sql
SELECT * FROM _statistics_.column_statistics WHERE table_name = 'your_table_name' \G

────────── 2. Is it a "Complete Scan" every time? No. StarRocks uses a partition-level incremental update mechanism for full statistics. * Partition Awareness: For tables with partitions, StarRocks tracks which partitions have been modified. When an automatic

ANALYZE

job runs, it only scans the modified partitions. If a partition has no data changes, StarRocks reuses the existing statistics for that partition, significantly reducing I/O and CPU usage. * Large Table Safety: For very large tables (by default >100 GB, controlled by

statistic_max_full_collect_data_size

), StarRocks may automatically switch from a Full Scan to a Sampled Scan to prevent the collection process from impacting system performance. * Automatic Triggering: For "Big Tables" (>10M rows), the background check for changes happens every 12 hours by default. It only triggers a new collection if the "Health" of the statistics (ratio of modified rows) falls below a threshold (default 0.8). 3. Key Configurations for Big Tables If you find that statistics collection is taking too long or not happening fast enough, you can tune these FE configurations: * statistic_max_full_collect_data_size
: Default is 100 GB. If a table exceeds this, StarRocks will switch to sampled collection instead of scanning every row. * statistic_auto_collect_large_table_interval
: Default is 43200 seconds (12 hours). This is how often StarRocks checks for updates on large tables. * statistic_auto_collect_ratio
: Default is 0.8. Collection is triggered only if the percentage of "healthy" (unchanged) rows falls below 80%. Summary For a big table, the first time will be a full scan of all data. Subsequent updates will only scan the specific partitions that have changed, making it much more efficient than a full re-scan. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • faq/Sql_faq.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • faq/Sql_faq.md • data_source/catalog/catalog_overview.md

Rocky

09/03/2026, 6:14 AM

• knowledge/admin_and_operation/shared_data_compaction.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/03/2026, 6:16 AM

@Rocky where does it store in CN mode disk of FE or cloud storage provided ?

Rocky

09/03/2026, 6:16 AM

In Shared-data mode (Cloud-native architecture), statistics are stored in the Cloud Storage (S3, GCS, Azure Blob, or HDFS) provided to the cluster. Here is the breakdown of where everything goes: 1. Persistent Storage: Cloud Storage The actual data for statistics (the rows you see when you query the

_statistics_

database) is stored in your remote object storage. * In Shared-data mode, the

_statistics_

database consists of Cloud-native tables. * Just like your business data, these internal tables are persisted to the cloud bucket to ensure they are durable and accessible by any CN node. 2. Processing & Caching: CN Nodes The CN (Compute Nodes) are responsible for "doing the work." * Collection: When you run

ANALYZE

, the CN nodes scan the data (either from the cloud or from their local cache) and compute the statistics. * Caching: Once computed, the statistics are written to the cloud. However, the CN nodes also cache this data on their local disks to speed up future queries. If a CN node is restarted or replaced, it simply re-fetches the stats from the cloud storage. 3. Metadata: FE Nodes The FE (Front End) does not store the actual statistics data on its disk. * The FE only stores the metadata (the "map" of where the statistics tables are located in cloud storage) in its internal image and edit logs (BDBJE). * The FE uses the statistics during query planning, but it retrieves the required stats from the internal tables managed by the CNs. Summary Table Component | Role in Statistics | Storage Location Cloud Storage | Permanent Home | Remote Bucket (S3/GCS/etc.) CN Disk | Hot Cache | Local SSD/HDD (Ephemeral) FE Disk | Metadata/Catalog | FE

meta

directory (Small) Why this matters: Because stats are in cloud storage, you can scale your CN nodes up or down (even to zero and back) without losing your statistics. New CN nodes will automatically see the statistics stored in the cloud. References • introduction/Architecture.md • faq/shared_data_faq.md • using_starrocks/Cost_based_optimizer.md • deployment/shared_data/s3.md References • introduction/Architecture.md • faq/shared_data_faq.md • using_starrocks/Cost_based_optimizer.md • deployment/shared_data/s3.md • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/Query_planning.md References • deployment/shared_data/s3.md • faq/shared_data_faq.md

ansh.gupta2

09/03/2026, 6:27 AM

@Rocky so on the basis of table size it pick FULL or sample stats creation automatically?

Rocky

09/03/2026, 6:27 AM

Yes, StarRocks automatically chooses between Full and Sampled collection based on the data size of the partitions that need to be analyzed. The decision logic is primarily controlled by the following thresholds in the FE (Front End) configuration: 1. The 100 GB "Full-to-Sample" Switch By default, StarRocks prefers Full statistics because they are more accurate. However, to prevent a huge performance impact on very large datasets, it will automatically switch to Sampled collection if the data size is too large. * Threshold:

statistic_max_full_collect_data_size

(Default: 100 GB). * Logic: When an automatic collection task is triggered, StarRocks calculates the total size of the partitions that have changed. If that size exceeds 100 GB, it discards the "Full" collection plan and performs a Sampled collection instead. 2. The 5 GB "Small vs. Large" Frequency Threshold StarRocks also uses size to determine how often it checks for updates. * Small Table (≤ 5 GB): Defined by

statistic_auto_collect_small_table_size

. These are checked frequently (determined by

statistic_auto_collect_small_table_interval

, default 0, meaning almost immediately after the 10-minute check cycle). * Large Table (> 5 GB): Checked every 12 hours by default (

statistic_auto_collect_large_table_interval

). 3. Summary of Automatic Selection Table/Partition Size | Collection Method | Frequency of Check < 5 GB | Full | Every 10 mins (near real-time) 5 GB — 100 GB | Full | Every 12 hours 100 GB
| Sampled (Automatically downgraded) | Every 12 hours How to change these behaviors If you have high-performance disks and want Full stats even for larger tables, or if you want to force Sampled stats sooner to save resources, you can adjust these settings:

Copy code

sql
-- Increase the threshold for Full stats to 500GB
ADMIN SET CONFIG (statistic_max_full_collect_data_size = "536870912000");

-- Change the definition of a "Small Table" to 10GB
ADMIN SET CONFIG (statistic_auto_collect_small_table_size = "10737418240");

Note on "Incremental" behavior: Even when it picks Full collection, it is still "incremental" in terms of effort—StarRocks only scans the partitions that have actually changed since the last update. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/03/2026, 6:54 AM

@Rocky mysql> SHOW ANALYZE JOB; +-------+-----------------+----------+-------+---------+------+----------+-------------------------+---------+--------------+--------+ | Id | Catalog | Database | Table | Columns | Type | Schedule | Properties | Status | LastWorkTime | Reason | +-------+-----------------+----------+-------+---------+------+----------+-------------------------+---------+--------------+--------+ | 12223 | default_catalog | ALL | ALL | ALL | FULL | SCHEDULE | {default_job_flag=true} | PENDING | None | | +-------+-----------------+----------+-------+---------+------+----------+-------------------------+---------+--------------+--------+ 1 row in set (0.00 sec) mysql> ADMIN SHOW CONFIG LIKE '%statistic%'; ERROR 1064 (HY000): Trino parser parse sql error: [io.trino.sql.parser.ParsingException: line 11 mismatched input 'ADMIN'. Expecting: 'ALTER', 'ANALYZE', 'CALL', 'COMMENT', 'COMMIT', 'CREATE', 'DEALLOCATE', 'DELETE', 'DENY', 'DESC', 'DESCRIBE', 'DROP', 'EXECUTE', 'EXPLAIN', 'GRANT', 'INSERT', 'MERGE', 'PREPARE', 'REFRESH', 'RESET', 'REVOKE', 'ROLLBACK', 'SET', 'SHOW', 'START', 'TRUNCATE', 'UPDATE', 'USE', <query>], and StarRocks parser also can not parse: [com.starrocks.sql.parser.ParsingException: Getting syntax error at l mysql>

Rocky

09/03/2026, 6:54 AM

The error you're seeing is due to two things: a slight syntax requirement and potentially the way your SQL client is parsing the command. 1. Correct Syntax for FE Config In StarRocks, the command to view internal configurations requires the keyword FRONTEND
. The correct syntax is:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';

* Why the Trino error? Some environments or specific StarRocks versions (especially when using certain integrations or multi-engine features) may attempt to pass the query through a secondary parser if the primary one fails. Since

ADMIN

is a StarRocks-specific command, the parsers didn't recognize your abbreviated version. 2. Understanding your
SHOW ANALYZE JOB
output The job you see in your output is the default system-wide analyzer: * Status `PENDING`: This is normal. This is not a "stuck" job; it is a persistent background job with

default_job_flag=true

. It stays in a

PENDING

state while it waits for the next scheduled interval (every 5 minutes) to check if any tables need their statistics updated based on data changes. * Table/Database `ALL`: This indicates this is the global task responsible for all tables in the

default_catalog

. 3. How to check actual progress If you want to see if statistics are currently being collected or have finished for a specific table, use this command instead:

Copy code

sql
SHOW ANALYZE STATUS;
-- OR filter for a specific table
SHOW ANALYZE STATUS WHERE `Table` = 'your_table_name';

* SHOW ANALYZE JOB
is primarily for viewing custom or manual collection tasks you've created. * SHOW ANALYZE STATUS
shows the execution history and current progress of both automatic and manual tasks. Summary of useful commands: Goal | Command Check Settings |

ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';

Check Run History |

SHOW ANALYZE STATUS;

Check Table Health |

SHOW STATS META WHERE Table = 'name';

Manual Trigger |

ANALYZE TABLE your_table_name;

Rocky

09/03/2026, 6:54 AM

• sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md • sql-reference/sql-statements/cbo_stats/KILL_ANALYZE.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md

ansh.gupta2

09/03/2026, 6:56 AM

Rocky

09/03/2026, 6:56 AM

The reason you only see

default_catalog

in that output is that the job with

default_job_flag=true

is a built-in system task specifically designed to manage StarRocks internal tables. By default, StarRocks does not automatically "sweep" entire external catalogs (like Hive or Delta Lake) because they can be massive, and scanning them fully without user intent could cause significant performance overhead on your remote storage and CN nodes. Here is how statistics work for your other catalogs (

delta_dev

hive_prod

, etc.): 1. External Catalogs require explicit jobs (Pre-v3.4) For external tables, StarRocks does not create a global "ALL ALL" job. If you want automatic collection for an external table, you must create a specific job for it:

Copy code

sql
-- This will create a background job for a specific external table
CREATE ANALYZE TABLE hive_prod.your_db.your_table;

Once created, this job will appear in your

SHOW ANALYZE JOB

list alongside the default one. 2. Query-Triggered Collection (v3.4+) If you are on StarRocks v3.4 or later, there is a new feature called Query-Triggered ANALYZE. * How it works: When you run a query against a table in

hive_prod

delta_prod

that lacks statistics, the FE automatically triggers a background collection task for the columns used in that query. * Configuration: This is controlled by

enable_query_trigger_analyze

(which you can see is

true

in your

ADMIN SHOW

output if you scroll or check specific variables). * Visibility: These tasks are short-lived and triggered on-demand, so they don't show up as a persistent

PENDING

job in

SHOW ANALYZE JOB

. You can see their history using

SHOW ANALYZE STATUS

. 3. Key Differences for External Stats * Storage: Even though the data is in Hive/Delta, the statistics themselves are still stored inside StarRocks in the

default_catalog._statistics_.external_column_statistics

table. * No Sampling: For external tables, StarRocks currently only supports Full collection (it doesn't support the

SAMPLE

method used for internal tables). * Manual Trigger: You can always force a collection manually:

Copy code

sql
    ANALYZE TABLE hive_prod.db_name.table_name;

Summary The

default_catalog

job you see is the "housekeeper" for internal data. For your Hive and Delta catalogs, StarRocks takes a more "lazy" or "on-demand" approach to avoid unnecessary metadata traffic to your data lake. If you want to check if stats exist for an external table, run:

Copy code

sql
SHOW STATS META WHERE Table = 'your_external_table_name';

References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE_JOB.md • data_source/feature-support-data-lake-analytics.md References • using_starrocks/Cost_based_optimizer.md • data_source/feature-support-data-lake-analytics.md

ansh.gupta2

09/03/2026, 7:17 AM

@Rocky can statistics gave incurrate results? like performance degradition also possible?

Rocky

09/03/2026, 7:17 AM

Yes, statistics can be inaccurate, and when they are, performance degradation is the most common symptom. However, it is important to distinguish between data correctness and performance: * Data Correctness: Inaccurate statistics will never cause your query to return the wrong data. * Performance: Inaccurate statistics will often cause the Optimizer (CBO) to choose a "bad" execution plan, making queries run significantly slower. 1. How Inaccurate Stats Degrade Performance The Optimizer uses statistics to "guess" the cost of different paths. If the guess is wrong, it might: * Pick the wrong Join Order: It might try to join a 1-billion-row table into a 100-row table instead of the other way around. * Choose the wrong Join Type: It might pick a Shuffle Join (slow, moves data across network) when a Broadcast Join (fast, keeps data local) would have fit in memory. * Fail to prune data: If it thinks a column has values between 1–100 (stale stats) but the actual data is now 1000–2000, it may fail to optimize filters correctly. 2. Common Causes of Inaccurate Statistics * Stale Data: If you loaded 100 million rows but the statistics haven't updated yet, the Optimizer thinks the table is empty or small. * Sampling Errors: If you use

SAMPLE

collection on a table with high data skew (e.g., 90% of your users are from one country), the sample might miss the "long tail" or miscalculate the number of distinct values (NDV). * Correlation between Columns: By default, StarRocks assumes columns are independent. If you filter on

City='San Francisco'

AND

ZipCode='94105'

, the Optimizer might think this is a very tiny result set because it doesn't know those two columns are highly correlated. 3. How to detect "Stats Problems" If a query is slow, you can use these two commands to find "smoking guns": 1. EXPLAIN COSTS <query>
: Look at the

Estimates

section. Does it think a table has 100 rows when you know it has 100 million? 2. EXPLAIN ANALYZE <query>
: This is the best tool. It shows the Estimated Rows vs. Actual Rows. _ _If the Gap is huge (e.g., Estimated 1 row, Actual 1,000,000 rows), your statistics are inaccurate.* 4. How to Fix * Manual Update: Run

ANALYZE TABLE <name>

to force a fresh, full scan. * Histograms: If data is skewed, use histograms:

ANALYZE TABLE <name> UPDATE HISTOGRAM ON <column_name>

. * Multi-column Stats: For correlated columns, use

ANALYZE TABLE <name> (col1, col2)

. References • using_starrocks/Cost_based_optimizer.md • best_practices/query_tuning/query_planning.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • using_starrocks/Cost_based_optimizer.md • best_practices/query_tuning/query_planning.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cluster-management/explain.md • administration/management/FE_configuration.md References • best_practices/query_tuning/query_planning.md • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/03/2026, 7:41 AM

@Rocky how resource group is similar to trino , actually we have trino in production we are thinking to moving into starrocks

Rocky

09/03/2026, 7:42 AM

StarRocks Resource Groups are functionally very similar to Trino Resource Groups, as both use a "matching" system to assign queries to buckets that have specific CPU, memory, and concurrency limits. If you are coming from Trino, here is a comparison of the key concepts: 1. Matchers: Selectors vs. Classifiers * Trino (Selectors): You define rules based on

user

source

clientTags

, etc., to map a query to a group. * StarRocks (Classifiers): Uses a similar logic. You create Classifiers for each group based on

user

role

db

query_type

source_ip

, and even estimated cost (e.g., small vs. large query). * Similarity: Both systems pick the "best match." StarRocks uses a "degree of matching" score to decide which group a query belongs to if it matches multiple classifiers. 2. Hierarchy and Structure * Trino: Supports a tree-like hierarchy (parent groups and sub-groups). * StarRocks: Generally uses a flat structure for resource groups. However, you can achieve similar isolation by: * Global Limits: Setting cluster-wide limits that apply to everyone. * Memory Pools: Using

mem_pool

to group several resource groups so they share a single pool of memory. 3. Resource Controls Feature | Trino Equivalent | StarRocks Implementation CPU Control |

schedulingWeight

cpu_weight

(soft share) or

exclusive_cpu_cores

(hard isolation). Memory |

memoryLimit

mem_limit

(percentage of BE memory). Concurrency |

maxConcurrentQueries

concurrency_limit

. Queuing |

maxQueuedQueries

query_queue_concurrency_limit

. Queries wait in queue instead of failing (v3.1.4+). Big Query Kill | N/A (usually external) |

big_query_cpu_second_limit

big_query_mem_limit

automatically kills or limits "runaway" queries. 4. Configuration Method * Trino: Usually configured via a static

resource-groups.json

file or a relational database. Changes often require a reload or coordinator access. * StarRocks: Fully SQL-driven. You can create, alter, or drop resource groups dynamically without restarting anything:

Copy code

sql
    CREATE RESOURCE GROUP adhoc_group
    TO (user='alice', role='analyst')
    WITH (
        "cpu_weight" = "10",
        "mem_limit" = "20%",
        "concurrency_limit" = "5"
    );

Key Differences to Note for Migration 1. Workload Isolation: StarRocks (v3.3.5+) supports hard CPU isolation (

exclusive_cpu_cores

), which is often more robust than Trino's weighted scheduling if you need to guarantee performance for a specific high-priority tenant. 2. Pipeline Engine: StarRocks resource groups require the Pipeline Engine to be enabled (it is the default in newer versions). This engine is what allows StarRocks to finely slice CPU time between queries, similar to how Trino manages task scheduling. 3. Observability: In StarRocks, you can see which group a query hit in the

fe.audit.log

or by running

EXPLAIN VERBOSE <query>

. You can also monitor real-time usage with

SHOW USAGE RESOURCE GROUPS

. Summary for your team: If you are used to Trino's resource management, you will find StarRocks' version very intuitive. The biggest "upgrade" you'll notice is the ability to manage everything through standard SQL commands rather than JSON config files. References • administration/management/resource_management/resource_group.md

ansh.gupta2

09/03/2026, 7:43 PM

@Rocky this is resource group in trino +-------------------+------------------------+-------------------+------------+------------------------+------------------------+-------------------+-------------------+------------+----------------+----------------+--------+-------------+ | resource_group_id | name | soft_memory_limit | max_queued | soft_concurrency_limit | hard_concurrency_limit | scheduling_policy | scheduling_weight | jmx_export | soft_cpu_limit | hard_cpu_limit | parent | environment | +-------------------+------------------------+-------------------+------------+------------------------+------------------------+-------------------+-------------------+------------+----------------+----------------+--------+-------------+ | 8 | global | 90% | 600 | NULL | 100 | weighted | NULL | 1 | NULL | NULL | NULL | adhoc1 | | 9 | adhoc | 90% | 600 | NULL | 50 | weighted_fair | 1 | 1 | NULL | NULL | 8 | adhoc1 | | 10 | adhoc_${USER} | 90% | 20 | NULL | 2 | NULL | 1 | 1 | NULL | NULL | 9 | adhoc1 | | 11 | pipeline | 90% | 750 | NULL | 30 | NULL | 1 | 1 | NULL | NULL | 8 | adhoc1 | | 12 | pipeline_${USER} | 90% | 500 | NULL | 10 | NULL | NULL | 1 | NULL | NULL | 11 | adhoc1 | | 13 | admin | 30% | 100 | NULL | 10 | query_priority | NULL | 1 | 400h | 500h | NULL | adhoc1 | | 14 | analysis_maintainance | 50% | 100 | NULL | 50 | NULL | NULL | 1 | NULL | NULL | NULL | adhoc1 | | 87 | pipeline_mintreporting | 90% | 500 | NULL | 100 | NULL | NULL | 1 | 1000h | 1200h | NULL | adhoc1 | | 88 | pipeline_insights | 90% | 500 | NULL | 8 | NULL | NULL | 1 | 1000h | 1200h | NULL | adhoc1 | | 89 | pipeline_partnerportal | 90% | 500 | NULL | 20 | NULL | NULL | 1 | 1000h | 1200h | NULL | adhoc1 | +-------------------+------------------------+-------------------+------------+------------------------+------------------------+-------------------+-------------------+------------+----------------+----------------+--------+-------------+ how make similar in starrocks?

Rocky

09/03/2026, 7:43 PM

To migrate Trino's hierarchical resource groups to StarRocks, you need to adapt to StarRocks' flat structure. While Trino uses a parent-child tree, StarRocks uses Classifiers to map queries directly to "leaf" groups. Here is how you can recreate your Trino setup in StarRocks: 1. The Strategy: Flattening the Hierarchy In Trino, a query might fall into

global -> adhoc -> adhoc_bob

. In StarRocks, you create the specific group

adhoc_users

and use a classifier to send all "adhoc" users there. 2. Implementation SQL You can execute these commands in StarRocks to match your Trino table logic:

Copy code

sql
-- 1. Admin Group (High Priority)
CREATE RESOURCE GROUP admin_group
WITH (
    "cpu_weight" = "100", -- Higher weight = more CPU time
    "mem_limit" = "30%",
    "concurrency_limit" = "10",
    "query_queue_concurrency_limit" = "100"
)
CLASSIFIER (role = 'admin');

-- 2. Maintenance Group
CREATE RESOURCE GROUP analysis_maintenance
WITH (
    "cpu_weight" = "20",
    "mem_limit" = "50%",
    "concurrency_limit" = "50",
    "query_queue_concurrency_limit" = "100"
)
CLASSIFIER (user = 'maint_user');

-- 3. Specific Pipeline Groups (Equivalent to IDs 87, 88, 89)
CREATE RESOURCE GROUP pipeline_mintreporting
WITH (
    "cpu_weight" = "50",
    "mem_limit" = "90%", -- StarRocks groups share memory; 90% is the cap
    "concurrency_limit" = "100",
    "query_queue_concurrency_limit" = "500",
    "big_query_cpu_second_limit" = "3600" -- Matches Trino's CPU h limit logic
)
CLASSIFIER (user = 'mint_reporting_svc');

-- 4. General Adhoc Group (Replacing the ${USER} hierarchy)
-- Note: StarRocks applies limits to the group as a whole.
CREATE RESOURCE GROUP adhoc_users
WITH (
    "cpu_weight" = "10",
    "mem_limit" = "90%",
    "concurrency_limit" = "50", -- Total group concurrency
    "query_queue_concurrency_limit" = "600"
)
CLASSIFIER (role = 'analyst');

mem_limit

| In StarRocks, this is the percentage of BE memory the group can use. max_queued
|

query_queue_concurrency_limit

| Requires

enable_query_queue = true

in FE config. hard_concurrency_limit
|

concurrency_limit

| The maximum number of queries running at once. soft/hard_cpu_limit
|

big_query_cpu_second_limit

| Trino's

1000h

is a time quota. StarRocks uses this to kill queries that run too long. scheduling_weight
|

cpu_weight

| A relative value. If Group A has weight 10 and Group B has 20, B gets 2x CPU cycles. 4. Handling Trino's
${USER}
logic Trino creates a sub-group for every single user (

adhoc_${USER}

) to prevent one person from hogging all 50 slots. * In StarRocks: You currently cannot create dynamic groups-per-user automatically. * The Workaround: Use the global FE configuration

max_concurrency_per_user

. This works globally across all groups to ensure no single user can exhaust the

adhoc_users

group's 50 slots. 5. Enabling the Queuing (Important) To make

max_queued

work like Trino, you must enable the query queue in your FE configuration:

Copy code

sql
ADMIN SET FRONTEND CONFIG ("enable_query_queue" = "true");

Recommendation for Migration Start by creating the 4-5 main "types" of workloads you have (Admin, Pipeline, Adhoc, Maintenance). Use Classifiers based on the

role

db

to simplify management, rather than mapping every individual user. To see if your groups are working after migration, run:

Copy code

sql
SHOW USAGE RESOURCE GROUPS;

-- Or check which group a specific query used SELECT * FROM information_schema.be_threads;

Copy code

-- Check audit log to see which group each query was assigned to
-- (The group name is logged in fe.audit.log)

-- To see if your groups are working after migration, run: -- SELECT * FROM information_schema.resource_group_usage; ``` ## References

Rocky

09/03/2026, 7:43 PM

- [administration/management/resource_management/resource_group.md](https://docs.starrocks.io/docs/administration/management/resource_management/resource_group) - [administration/management/resource_management/query_queuing.md](https://docs.starrocks.io/docs/administration/management/resource_management/query_queuing)

ansh.gupta2

09/04/2026, 5:45 AM

@Rocky what does error means, I am doing load testing on starrocks via loadbalancer on FE using jmeter timeStamp,elapsed,label,responseCode,responseMessage,threadName,dataType,success,failureMessage,bytes,sentBytes,grpThreads,allThreads,URL,Latency,IdleTime,Connect 1788463798012,347216,JDBC Request - q0,200,OK,Thread Group 1-9,text,true,,116,0,10,10,null,347215,0,0 1788463794015,360753,JDBC Request - q0,200,OK,Thread Group 1-5,text,true,,120,0,9,9,null,360753,0,0 1788463790041,373898,JDBC Request - q0,200,OK,Thread Group 1-1,text,true,,112,0,8,8,null,373898,0,948 1788463799011,1042288,JDBC Request - q1,08S01 0,"com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,042,286 milliseconds ago. The last packet sent successfully to the server was 1,042,286 milliseconds ago.",Thread Group 1-10,text,false,,194,0,7,7,null,0,0,0 1788463796012,1106726,JDBC Request - q2,08S01 0,"com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,106,724 milliseconds ago. The last packet sent successfully to the server was 1,106,724 milliseconds ago.",Thread Group 1-7,text,false,,194,0,6,6,null,0,0,0 1788463795013,1165069,JDBC Request - q1,08S01 0,"com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,165,067 milliseconds ago. The last packet sent successfully to the server was 1,165,067 milliseconds ago.",Thread Group 1-6,text,false,,194,0,5,5,null,0,0,0 1788463793013,1167069,JDBC Request - q3,08S01 0,"com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,167,067 milliseconds ago. The last packet sent successfully to the server was 1,167,067 milliseconds ago.",Thread Group 1-4,text,false,,194,0,5,5,null,0,0,0 1788463791014,1478447,JDBC Request - q1,200,OK,Thread Group 1-2,text,true,,69,0,3,3,null,1478447,0,0 1788463797013,1481876,JDBC Request - q3,200,OK,Thread Group 1-8,text,true,,69,0,2,2,null,1481876,0,0 1788463792013,1988172,JDBC Request - q2,200,OK,Thread Group 1-3,text,true,,52,0,1,1,null,1988172,0,0

Rocky

09/04/2026, 5:45 AM

This error,

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

, means the TCP connection between JMeter and StarRocks was forcibly closed while JMeter was waiting for a response. Looking at your logs, there is a major red flag: Your query latencies are extremely high (ranging from 347 seconds to 1,988 seconds). 1. The Direct Cause: Timeout Mismatch The error occurs around the 1,000–1,100 second mark (approx. 17 minutes). This suggests that a network component has a timeout shorter than your query execution time. * Load Balancer Idle Timeout: This is the most likely culprit. Most Load Balancers (AWS NLB/ALB, HAProxy, Nginx) have an idle timeout. If a query takes 20 minutes to produce a result and the LB sees no data flowing for 15 minutes, it kills the connection. * StarRocks `wait_timeout`: StarRocks has a session variable

wait_timeout

(default is often 28800s, but check yours). If the connection is idle from the LB perspective, it might be closed. * StarRocks `query_timeout`: The default query timeout is usually 300 seconds (5 minutes). However, your logs show some queries succeeding at 1,400+ seconds, which means your

query_timeout

has likely already been increased, but the network/LB cannot hold the connection that long. 2. The Root Cause: Performance Degradation The fact that queries are taking 33 minutes (1,988,172 ms) in a load test suggests StarRocks is struggling. This ties back to your previous question about statistics. If the statistics are missing or inaccurate: 1. The Optimizer chooses a bad plan. 2. The query consumes 100% of CPU/Memory for a long time. 3. Other queries (q0, q1, q2) queue up behind it. 4. The Load Balancer times out because the "Communications link" is idle while the BE (Backend) nodes are churning on data. 3. How to Fix This Step A: Check the Load Balancer (Immediate Fix for the Error) * Increase the Idle Timeout on your Load Balancer to be higher than your longest query (e.g., set it to 3600 seconds). * Alternatively, bypass the Load Balancer and connect JMeter directly to one FE IP to see if the "Communications link failure" disappears. If it does, the LB is definitely the one dropping the connection. Step B: Optimize StarRocks (The Real Fix) You shouldn't have 30-minute queries in a StarRocks load test. * Run
ANALYZE TABLE <table_name>
on all tables involved in your JMeter test. * Check the Query Profile: Run one of the slow queries manually in a SQL client:

Copy code

sql
    SET analyze_level = 1;
    <your_query>;
    SHOW LAST QUERY PROFILE;

Look for "Scan Time" or "Join Time." If the Estimated Rows vs Actual Rows are vastly different, your statistics are the problem. Step C: Increase Session Timeouts In your JMeter JDBC connection string or a setup script, ensure these are high enough:

Copy code

sql
SET global query_timeout = 3600; -- Allow queries to run for 1 hour
SET global wait_timeout = 3600;  -- Allow idle connections for 1 hour

Summary Your "Communications link failure" is a symptom of the network giving up because StarRocks is taking too long to respond. Fix the query performance (via statistics and profiling) and the network errors will likely stop. disappearances are high that the connection errors will go away. References • administration/management/resource_management/query_queuing.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md

ansh.gupta2

09/04/2026, 5:49 AM

@Rocky is one FE sufficient why we need more than one FE?

Rocky

09/04/2026, 5:49 AM

While one FE (Front-End) is technically enough to run a cluster, it is not recommended for production or load testing for several critical reasons: 1. High Availability (Avoiding SPOF) With only one FE, you have a Single Point of Failure (SPOF). * If that FE process crashes, restarts, or the host machine fails, the entire cluster becomes inaccessible, even if your 100 BE nodes are perfectly healthy. * The 3-node rule: In production, StarRocks recommends 3 Follower FEs. This allows the cluster to use the Raft consensus protocol. If one FE dies, the remaining two will automatically elect a new "Leader" within seconds, and your Load Balancer will simply route traffic to the survivors. 2. Query Planning & Connection Bottlenecks In your current load test, the FE has to handle: * Parsing and Planning: Every SQL query must be analyzed and turned into a execution plan by the FE. For complex queries, this is CPU-intensive. * Metadata Locks: The FE manages metadata. If you have high concurrency, a single FE can become a bottleneck because of internal locks required to manage the catalog. * Connection Limits: A single FE has a maximum number of MySQL protocol connections it can maintain. Under heavy load (like JMeter), you can hit these limits quickly. 3. Separation of Concerns (Followers vs. Observers) When you add more FEs, you can assign them different roles: * Followers: Participate in the "election" to be the Leader. They keep a copy of the metadata. * Observers: These are "read-only" scale-out nodes. They don't participate in the election, so adding 10 of them won't slow down the metadata synchronization. They are perfect for Load Balancing high-concurrency query traffic. 4. How this relates to your "Communications link failure" Since you are using a Load Balancer, having 3 FEs is significantly better: * Graceful Failover: If you need to upgrade StarRocks or change a config that requires an FE restart, you can do it one-by-one. The Load Balancer will see one FE go down, health-check it as "failed," and move all JMeter traffic to the other two without your test failing. * Resource Distribution: If one query is extremely heavy and "hangs" the JVM of one FE (e.g., due to a very large metadata operation), the other FEs remain responsive to handle other queries. Recommendation For your migration from Trino: 1. Deploy 3 FEs (all as Followers). 2. Point your Load Balancer at all 3 FEs on the

query_port

(default 9030). 3. Use a Health Check in the Load Balancer that hits the FE HTTP port (default 8030) at

/api/health

. This setup will ensure that your load test reflects a real production environment rather than a "toy" setup that might be failing simply because the single FE is overwhelmed. References • deployment/plan_cluster.md • deployment/deploy_manually.md • administration/management/FE_configuration.md References • deployment/deploy_manually.md • faq/operation_maintenance_faq.md • deployment/plan_cluster.md • administration/Meta_recovery.md

ansh.gupta2

09/04/2026, 5:52 AM

@Rocky finally merging happen in one FE , like multiple FE wont stream result for same query ? only single FE will be responsible for single query?

Rocky

09/04/2026, 5:53 AM

Yes, you are correct: for a single query, a single FE is responsible for the final result delivery. Here is exactly how the data flows and why "merging" happens where it does: 1. The FE as the "Coordinator" When your JMeter client (or any MySQL client) connects to StarRocks through a Load Balancer, it establishes a persistent TCP connection with one specific FE. * That FE becomes the Coordinator for any query sent over that connection. * Only this FE can "talk" to your client for that query. Multiple FEs cannot "collaborate" to stream the same result set to one client because the MySQL protocol is a 1-to-1 connection. 2. Distributed Work on BEs StarRocks is an MPP (Massively Parallel Processing) engine. It does not send all raw data to the FE to be merged. It pushes as much work as possible to the BEs: * Partial Aggregation: If you run

SELECT count(*)

, every BE counts its own local data first. * Final Aggregation: The BEs might shuffle data among themselves to finish a join or a complex group-by. * The Result Sink: Once the BEs have the final rows ready, they send them to the "Result Sink," which points back to the Coordinator FE. 3. The Final "Gather" at the FE The FE performs the very last, lightweight steps: * Final Sort/Limit: If your query is

ORDER BY time LIMIT 100

, each BE sends its "top 100" to the FE, and the FE does one final sort of those combined results to find the global top 100. * Protocol Packaging: The FE converts the internal data format into MySQL packets. * Streaming: The FE streams these packets back to your client. Why do you still need multiple FEs? If a single FE does the final merging, why add more? 1. Concurrent Query Scaling: If you have 100 users running queries simultaneously, and you only have one FE, that FE will be overwhelmed by the CPU cost of planning 100 queries and the memory cost of merging 100 result sets. With 3 FEs, the Load Balancer distributes those 100 "Coordinators" across 3 nodes. 2. Metadata Redundancy: All FEs share the same metadata. If the one FE handling your query dies, you lose that specific query, but the Load Balancer can immediately route your next query to a healthy FE. 3. High Availability: As discussed, 3 FEs ensure the cluster stays alive even if the "Leader" FE fails. Summary * Single Query: 1 Client ↔️ 1 FE (Coordinator) ↔️ Many BEs. * Load Test: Many Clients ↔️ Load Balancer ↔️ Multiple FEs (each acting as a Coordinator for a subset of queries) ↔️ Many BEs. In your JMeter test, having multiple FEs ensures that the "final merging" and "network streaming" work is shared across multiple machines, preventing the single FE from becoming a bottleneck. References • quick_start/shared-nothing.md • introduction/Architecture.md • administration/management/FE_configuration.md • loading/StreamLoad.md • using_starrocks/Colocate_join.md

ansh.gupta2

09/04/2026, 7:34 AM

@Rocky how to what stats jobs are running?

Rocky

09/04/2026, 7:34 AM

To see which statistics collection jobs are currently running or have recently finished, you can use the following commands: 1. Check Current Status of All Jobs Run this command to see the history and current state of both manual (

ONCE

) and automatic (

SCHEDULE

) statistics collection:

Copy code

sql
SHOW ANALYZE STATUS;

To find only the ones currently running, use a filter:

Copy code

sql
SHOW ANALYZE STATUS WHERE status = 'RUNNING';

* Status column: Look for

RUNNING

PENDING

SUCCESS

, or

FAILED

. * Reason column: If a job failed, this will explain why (e.g., timeout or resource issues). 2. Check Custom/Scheduled Jobs If you have created specific recurring collection tasks (using

CREATE ANALYZE

), use:

Copy code

sql
SHOW ANALYZE JOB;

3. Check the "Health" of Table Stats If you want to see when the statistics were last updated and how "healthy" they are (how much the data has changed since the last update):

Copy code

sql
SHOW STATS META WHERE table = 'your_table_name';

* Healthy column: A value of

means the stats are perfectly up to date. A low value (e.g.,

< 60

) indicates the stats are stale and a new

ANALYZE

is likely needed. 4. How to Stop a Running Job If you find a statistics job that is consuming too many resources and you want to stop it, find its

Id

from

SHOW ANALYZE STATUS

and run:

Copy code

sql
KILL ANALYZE <job_id>;

Summary of what to look for: In your case, since you are experiencing very slow queries (30+ minutes), check

SHOW ANALYZE STATUS

. If you see many jobs with a status of

FAILED

or if the

EndTime

for your tables is very old, it confirms that the Optimizer is working with "blind" or stale information, causing the poor performance you're seeing in JMeter. References • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md • sql-reference/sql-statements/cbo_stats/KILL_ANALYZE.md References • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md

ansh.gupta2

09/04/2026, 7:37 AM

@Rocky starrocks doesnt statistcs for delta itself, on whichever query ran it run for that , how to confirm this?

Rocky

09/04/2026, 7:38 AM

Your observation is correct. For external tables like Delta Lake, StarRocks handles statistics differently than it does for internal tables. If you are using StarRocks v3.4.0 or later, what you are seeing is a feature called Query-Triggered Analyze. How to Confirm This You can confirm that StarRocks is collecting statistics "on-demand" by checking these three things: 1. Check the Trigger Variable The feature that causes statistics to be collected only when a query is run is controlled by this variable. Run this to see if it is `true`:

Copy code

sql
SHOW VARIABLES LIKE 'enable_query_trigger_analyze';

2. Check the Background Jobs When you run a query, StarRocks marks those tables/columns. A background thread then picks them up. You can see these "query-triggered" jobs in the status history:

Copy code

sql
SHOW ANALYZE STATUS WHERE status = 'SUCCESS' OR status = 'RUNNING';

Look for jobs where the columns match the ones you just queried. For Delta Lake, StarRocks will usually collect statistics for the entire table once a query triggers it. 3. Check the External Stats Table Unlike internal tables, external table stats are stored in a specific system table. You can verify if stats exist for your Delta table here:

Copy code

sql
SELECT * FROM _statistics_.external_column_statistics 
WHERE table_name = 'your_delta_table_name';

Why does it work this way? * For Internal Tables: StarRocks knows exactly when data changes (during

INSERT

LOAD

), so it can trigger background stats automatically. * For External Tables (Delta Lake): Since the data is modified outside of StarRocks (e.g., by Spark or Flink), StarRocks doesn't always know when the data has changed. * The "Lazy" Approach: To avoid wasting resources scanning massive external tables that might never be queried, StarRocks (by default in v3.4+) waits until a user actually queries the table before it spends the CPU/IO to analyze it. How to get stats before the first query If you don't want the first query to be slow or have a "bad plan," you have two options to be proactive: 1. Manual: Run

ANALYZE TABLE your_delta_table;

once after you've created the catalog. 2. Scheduled Job: Create a job that checks for updates periodically:

Copy code

sql
    CREATE ANALYZE TABLE your_delta_table
    PROPERTIES ("statistic_auto_collect_interval" = "3600"); -- Check every hour

Note: Delta Lake statistics support was introduced in v3.3.0, and the automatic "query-trigger" behavior was introduced in v3.4.0. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE_JOB.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/04/2026, 7:44 AM

@Rocky i am using 4.0.0 version, my usecase is quering delta external catalog tables, tell me everything i should know to productionize this considering at scale with concurrent load

Rocky

09/04/2026, 7:44 AM

StarRocks 4.0 (and the 4.x line) is designed to be a "Catalog-Centric" engine, meaning it treats external Delta Lake tables with nearly the same performance priority as internal tables. To productionize this at scale with high concurrency, you should focus on these four pillars: Architecture, Metadata & Stats, Concurrency Tuning, and Data Caching. 1. Architecture: Scaling the "Coordinator" As we discussed, one FE acts as the coordinator for a single query. At scale: * High Availability: Deploy 1 Leader + 2 Followers for metadata redundancy. * Read Scaling: Add Observer FEs to handle high concurrent query planning and result merging. Load balance your JMeter/Client traffic across all FEs (Followers + Observers). * Compute Nodes (CN): Consider using the Compute Node (Stateless) architecture if your workload is purely external queries. CNs don't store internal data, making them easier to auto-scale in Kubernetes based on CPU load. 2. Metadata: Reducing Latency to the Object Store Querying Delta Lake requires reading the

_delta_log

. In version 4.0, you must optimize this "pre-query" phase: * Background Refresh: Enable the FE to poll for Delta Lake changes so the first query isn't slow.

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("enable_background_refresh_connector_metadata" = "true");
    ADMIN SET FRONTEND CONFIG ("background_refresh_metadata_interval_millis" = "600000"); -- 10 mins

* Metadata Local Cache: StarRocks 4.0 caches deserialized Delta metadata in FE memory. Ensure your FE has enough RAM (at least 32GB+ for large catalogs) to keep these objects "hot." 3. Statistics: The Brain of the Optimizer Without stats, StarRocks will choose bad Join orders, leading to 30-minute queries. * Query-Triggered Analyze: In 4.0, this is ON by default (

enable_query_trigger_analyze=true

). When you run a query, StarRocks notes the missing stats and collects them in the background. * Proactive Collection: For production, don't wait for the first query. Create a manual or scheduled job for your largest tables:

Copy code

sql
    CREATE ANALYZE TABLE delta_catalog.db.large_table
    PROPERTIES ("statistic_auto_collect_interval" = "3600");

4. Concurrency & Performance Tuning High concurrency on external lakes often hits I/O bottlenecks. Use these session variables: * Parallelism (DOP):

pipeline_dop

controls CPU cores per query. For high concurrency, lower this (e.g., to

) so more queries can run simultaneously without context switching. * I/O Tasks:

connector_io_tasks_per_scan_operator

(default 16). If your object storage (S3/GCS) is fast, keep it high. If you see "I/O Wall" in your profiles, lower it to

. * Adaptive I/O:

enable_connector_adaptive_io_tasks

(default true) allows StarRocks to adjust I/O concurrency based on system load. 5. Storage Optimization: Data Cache This is the most important feature for productionizing external lake queries. * What it does: It caches Parquet data from Delta Lake onto the local NVMe/SSD of your BE/CN nodes. * Impact: Subsequent queries for the same data run at local disk speed, bypassing S3/HDFS latency entirely. * Configuration:

Copy code

sql
    -- Example during Catalog creation
    CREATE EXTERNAL CATALOG delta_prod
    PROPERTIES (
        "type" = "deltalake",
        "enabled_storage_cache" = "true"
    );

_Ensure your BE nodes have

storage_root_path

configured with sufficient disk space for the cache._ 6. Critical Delta Features to Verify StarRocks 4.0 supports advanced Delta Lake features that significantly improve performance: * Deletion Vectors (v3.4.1+): Ensure your Delta tables use these for updates/deletes; StarRocks 4.0 can read them efficiently to avoid scanning "dead" rows. * Column Mapping: Supported in 4.0, allowing you to rename columns in Delta without rewriting data. Summary Checklist for Production

Rocky

09/04/2026, 7:44 AM

1. FE: 3-node HA + Observers for concurrency. 2. Stats: Check

SHOW ANALYZE STATUS

to ensure your tables aren't "blind." 3. Cache: Enable Data Cache on BEs with fast local SSDs. 4. Monitor: Use

SET enable_profile = true;

on a slow query and look at the Scan Time vs Object Storage Wait Time. If the latter is high, your Data Cache or I/O tasks need tuning. References • data_source/catalog/deltalake_catalog.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • administration/Query_planning.md • data_source/feature-support-data-lake-analytics.md • using_starrocks/Data_cache.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/04/2026, 8:02 AM

@Rocky delta does not support delta write?

Rocky

09/04/2026, 8:02 AM

You are correct. As of StarRocks v4.0.0, the Delta Lake catalog does not support write operations (such as

INSERT INTO

UPDATE

, or

DELETE

). While StarRocks has recently added write support for other data lake formats (like Apache Iceberg and Hive), its support for Delta Lake is currently focused on high-performance read-only analytics. Current Capabilities for Delta Lake (v4.0) * Query/Read: Full support for querying Delta Lake tables via external catalogs without data movement. * Advanced Features: Support for reading tables with Deletion Vectors (added in v3.4.1), Column Mapping, and V2 Checkpoints. * Data Ingestion: You can use Delta Lake as a source to load data into StarRocks internal tables:

Copy code

sql
    INSERT INTO internal_db.olap_table
    SELECT * FROM delta_catalog.db.delta_table;

Why is it read-only? Writing to Delta Lake requires managing the Delta Transaction Log (

_delta_log

), which ensures ACID compliance. While StarRocks has implemented this for Iceberg, the Delta Lake write "sink" is not yet available in the 4.0 release. Recommended Production Workflow Since you are looking to productionize this: 1. Write Layer: Use an engine like Spark, Flink, or Databricks to perform your ETL/ELT and write the data into Delta Lake. 2. Read Layer: Use StarRocks as the blazing-fast serving layer. 3. Optimization: To get the best performance, enable the Data Cache on your StarRocks nodes so that the Delta Parquet files are cached on local SSDs after the first read. If your use case strictly requires StarRocks to be the writer, you might consider using Apache Iceberg, which StarRocks 4.0 supports for both reading and writing (

INSERT INTO iceberg_catalog...

). References • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md

ansh.gupta2

09/04/2026, 8:04 AM

@Rocky does it support delta kernal , able to read correct data that is whichever files are deleted in delta catalog it enusre that all

Rocky

09/04/2026, 8:04 AM

Yes, StarRocks does support Delta Kernel (specifically Delta Kernel Java), and it is designed to ensure you always read the "source of truth" from your Delta Lake catalog. Here is how it handles the "correctness" of your data, including deleted files and records: 1. Support for Delta Kernel StarRocks uses the Delta Kernel Java library to parse Delta Lake metadata. This is a game-changer for production because Delta Kernel is the official, standardized way to read Delta tables. It ensures that StarRocks follows the Delta protocol exactly as Databricks or Spark would, handling: * Transaction Log Parsing: Correct reading of the

_delta_log

to find the latest valid snapshot. * V2 Checkpoints: Efficiently loading metadata for massive tables. * Column Mapping: Correctly reading tables where columns have been renamed or dropped in the Delta catalog. 2. Handling Deleted Files When a file is "deleted" in Delta Lake (e.g., via a

VACUUM

or a

compaction/OPTIMIZE

job), it is not immediately removed from disk but is marked as "removed" in the Delta log. * Snapshot Consistency: Because StarRocks (via Delta Kernel) reads the transaction log first, it generates a "file list" for your query that only includes active files. * Isolation: Even if a background process is deleting or rewriting files while your query is running, StarRocks will stay locked to the snapshot version it started with, ensuring your query doesn't fail with "File Not Found" errors. 3. Handling Deleted Rows (Deletion Vectors) One of the most important features in StarRocks (starting from v3.4.1 and fully matured in v4.0.0) is support for Deletion Vectors. * What they are: In modern Delta Lake tables, when you delete a few rows, Delta doesn't rewrite the whole 1GB Parquet file. Instead, it writes a small "Deletion Vector" (a bitmap) that says "ignore rows 5, 10, and 22." * StarRocks Implementation: StarRocks is one of the few engines that can read these bitmaps at high speed. When you run a query, StarRocks automatically merges these "soft deletes" on the fly, so you never see data that was logically deleted in the source. 4. How to verify it is working correctly If you want to be 100% sure StarRocks is seeing your latest changes (deletes/updates) in production: 1. Check Metadata Refresh: Ensure StarRocks is aware of the latest version. You can manually refresh if you suspect a lag:

Copy code

sql
    REFRESH EXTERNAL TABLE your_delta_table;

2. Verify Row Counts: Compare a simple count between StarRocks and your source (Spark/Databricks). They should match exactly because StarRocks honors the same deletion logic.

Copy code

sql
    SELECT count(*) FROM your_delta_catalog.db.table;

3. Profile the Query: If you want to see if Deletion Vectors are being used, run

SET enable_profile = true;

before your query and look at the "Scan" node in the profile—it will show metrics for deleted rows being filtered. Summary By using Delta Kernel, StarRocks treats the Delta Lake log as the "law." It will not read files that have been marked for removal, and it will filter out individual rows marked by Deletion Vectors, ensuring 100% data consistency with your Delta Lake source. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md • administration/Query_planning.md References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md • administration/Query_planning.md References

Rocky

09/04/2026, 8:04 AM

• data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md

ansh.gupta2

09/04/2026, 8:06 AM

@Rocky but it can create hive table?

Rocky

09/04/2026, 8:06 AM

Yes, that is correct. There is a significant difference in how StarRocks 4.0 handles Hive versus Delta Lake: 1. Hive: Full Write Support Unlike Delta Lake, StarRocks has full "Sink" (write) capabilities for Hive catalogs. In StarRocks 4.0, you can: * Create Tables: Use

CREATE TABLE

CREATE TABLE AS SELECT

(CTAS), or

CREATE TABLE LIKE

to create new tables directly in your Hive Metastore (HMS) or Glue. * Insert Data: Use

INSERT INTO

(to append) or

INSERT OVERWRITE

(to replace) data in Hive tables. * Supported Formats: You can write data to Hive in Parquet, ORC, or Textfile formats. Example:

Copy code

sql
-- Create a Hive table from StarRocks
CREATE TABLE hive_catalog.db.new_hive_table (
    id INT,
    name STRING
) ENGINE=hive 
PROPERTIES ("format" = "parquet");

-- Sink data into it
INSERT INTO hive_catalog.db.new_hive_table SELECT id, name FROM internal_db.local_table;

2. Why the difference? * Hive/Iceberg: These formats have more mature "Sink" implementations in the StarRocks engine. StarRocks can act as the primary engine for your data lake if you use these formats. * Delta Lake: The Delta Lake protocol (the way the transaction log is written) is more complex to implement for a C++ engine like StarRocks. While StarRocks has perfected the Read path (using Delta Kernel) for maximum performance, the Write path for Delta is not yet available in version 4.0. 3. Summary of External Write Support (v4.0) Feature | Hive Catalog | Iceberg Catalog | Delta Catalog Read | ✅ Yes | ✅ Yes | ✅ Yes Create Table | ✅ Yes | ✅ Yes | ❌ No Insert Into | ✅ Yes | ✅ Yes | ❌ No Insert Overwrite | ✅ Yes | ✅ Yes | ❌ No Recommendation for your Production Setup If your production requirement requires StarRocks to write back to the lake: 1. Use Iceberg or Hive: If you have the flexibility to choose your lake format, these are the "first-class" citizens for two-way traffic (read/write) in StarRocks. 2. External ETL for Delta: If you must use Delta Lake, keep using StarRocks for the queries (read) and use Spark/Flink for the ingestion (write). References • data_source/catalog/hive_catalog.md • data_source/catalog/unified_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md References • data_source/catalog/hive_catalog.md • data_source/catalog/unified_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md References • data_source/catalog/hive_catalog.md • data_source/catalog/unified_catalog.md

ansh.gupta2

09/11/2026, 6:54 AM

@Rocky can we isolate workload in starrocks by number of CN given that is assign 10 CN to read and 5 CN to write in same 15 CN cluster?

Rocky

09/11/2026, 6:54 AM

Yes, you can physically isolate your workload by dedicating specific Compute Nodes (CN) to reading and others to writing. This is a core feature of the StarRocks Shared-Data (Cloud-Native) architecture, where it is known as Multi-Warehouse (or Compute Groups). In this architecture, data is stored centrally in object storage (like S3 or HDFS), allowing any CN to access any data. This enables you to create physically isolated "Warehouses" that do not compete for CPU or memory. How to set this up (v3.3+) To achieve your 10 CN (Read) and 5 CN (Write) split: 1. Create the Warehouses First, create two logical warehouses to act as your resource pools:

Copy code

sql
CREATE WAREHOUSE read_warehouse;
CREATE WAREHOUSE write_warehouse;

2. Assign CNs to Warehouses When you add your nodes to the cluster, you assign them to the specific warehouse. If you already have 15 nodes in the

default_warehouse

, you can move them, or add them specifically:

Copy code

sql
-- Add 5 nodes to the write pool
ALTER SYSTEM ADD COMPUTE NODE "10.0.0.1:9050", "10.0.0.2:9050", ... INTO WAREHOUSE write_warehouse;

-- Add 10 nodes to the read pool
ALTER SYSTEM ADD COMPUTE NODE "10.0.0.6:9050", ..., "10.0.0.15:9050" INTO WAREHOUSE read_warehouse;

3. Route Your Workload You can route queries and load jobs to these specific nodes in two ways: * Manual Routing (Session-based): For your ETL/Ingestion scripts:

Copy code

sql
    SET warehouse = 'write_warehouse';
    INSERT INTO your_table SELECT ...;

For your BI/Dashboard connections:

Copy code

sql
    SET warehouse = 'read_warehouse';
    SELECT * FROM your_table;

* Automatic Routing (Resource Groups): You can create Resource Groups with classifiers that automatically route based on

query_type

. For example, you can tell StarRocks to send all

INSERT

queries to the

write_warehouse

and all

SELECT

queries to the

read_warehouse

automatically. Important Considerations 1. Shared-Data Only: This physical node-level isolation is only available in Shared-Data mode. In the classic "Shared-Nothing" (BE-based) mode, data is tied to specific nodes, so you cannot easily designate "read-only" nodes without losing access to the data stored on the "write" nodes. 2. Cache Isolation: Each warehouse maintains its own Data Cache on its local SSDs. This means the

read_warehouse

will keep its cache "hot" for queries without being evicted by large write/ingest operations happening on the

write_warehouse

. 3. Scaling: You can scale the

read_warehouse

to 20 nodes during peak hours and back to 10 nodes later without any data movement or rebalancing. References • quick_start/shared-data.md • sql-reference/sql-functions/utility-functions/current_warehouse.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_COMPUTE_NODES.md • administration/management/Scale_up_down.md References • sql-reference/sql-statements/cluster-management/nodes_processes/ALTER_SYSTEM.md • administration/management/Scale_up_down.md • deployment/deploy_manually.md

ansh.gupta2

09/11/2026, 7:10 AM

@Rocky how to make starrocks collect stats for all the column instead of just only column it touch during query , as when different on same table ran it can give stats error?

Rocky

09/11/2026, 7:11 AM

In StarRocks, the behavior where only "touched" columns (known as Predicate Columns) are analyzed is a performance optimization designed to save system resources on very wide tables. By default, if a table has more than 32 columns, StarRocks will only collect statistics for columns that have been used in a

WHERE

clause or a

JOIN

condition in previous queries. If a new query uses a different column, you may encounter suboptimal query plans or "stats error" messages because the optimizer doesn't have data for that specific column yet. Here is how you can force StarRocks to collect statistics for all columns. 1. Increase the Automatic Collection Threshold (Recommended) You can change the global setting so that StarRocks collects statistics for all columns unless the table is extremely wide. This is controlled by the FE configuration

statistic_auto_collect_predicate_columns_threshold

. To increase this threshold (e.g., to 500 columns) across the entire cluster, run:

Copy code

sql
ADMIN SET FRONTEND CONFIG ("statistic_auto_collect_predicate_columns_threshold" = "500");

* Result: Any table with fewer than 500 columns will now have statistics collected for every column during automatic maintenance tasks. 2. Manually Trigger a Full Collection If you need to fix a specific table immediately, you can manually trigger a full analysis of all columns. * For all columns (v3.5 and above):

Copy code

sql
    ANALYZE TABLE your_table_name ALL COLUMNS;

* For versions below 3.5:

Copy code

sql
    -- Simply omitting the column list defaults to all columns
    ANALYZE TABLE your_table_name;

3. Create a Custom Analysis Task If you have specific critical tables that must always have full column stats regardless of the global threshold, you can create a permanent custom task for them:

Copy code

sql
CREATE ANALYZE FULL TABLE your_db.your_table;

This task will run in the background periodically and will prioritize all columns in that specific table. 4. Check the Status If you are still seeing errors, you can verify if the statistics were actually collected and for which columns using:

Copy code

sql
SHOW ANALYZE STATUS WHERE Table_name = 'your_table_name';
-- and to see the actual statistics data:
SHOW COLUMN STATISTICS your_db.your_table;

Summary of Key Settings FE Configuration | Default | Description

statistic_auto_collect_predicate_columns_threshold

| 32 | If columns > this value, only "Predicate Columns" are analyzed.

enable_predicate_columns_collection

| TRUE | Whether StarRocks should track which columns are used in queries.

enable_collect_full_statistic

| TRUE | Whether to enable automatic background collection. Pro Tip: If your "stats error" is specifically about "Sampled Statistics," ensure that

enable_collect_full_statistic

is set to

true

. If set to

false

, StarRocks defaults to sampled stats, which can sometimes be less accurate for complex joins. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md

Rocky

09/11/2026, 7:11 AM

• sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md

ansh.gupta2

09/11/2026, 7:12 AM

@Rocky or then how to avoid stats error like this 1788231357159,116,JDBC Request - q0,42000 1064,"java.sql.SQLSyntaxErrorException: only found column statistics: {2: id, 4: order_release_id_fk, 37: packed_on, 1085: cast, 14: courier_code, 1086: cast, 63: order_created_on, 1088: cast}, but missing statistic of col: 73: part_created_on.",Thread Group 1-6,text,false,,204,0,6,6,null,0,0,1

Rocky

09/11/2026, 7:13 AM

The error you are seeing—`only found column statistics: {...}, but missing statistic of col: ...`—is a known Cost-Based Optimizer (CBO) bug that has been reported in various StarRocks versions (including 3.3.x, 3.4.x, and 3.5.x). While you can try to collect stats for all columns (as discussed previously), this specific error often occurs because the optimizer is looking for statistics on internal expression slots (like the

1085: cast

in your log) that are generated during query planning, rather than actual physical table columns. Why this happens 1. Optimizer Bugs: Recent bug reports (e.g., #74791, #63840) show that certain "Join Reordering" or "Column Pruning" rules can accidentally drop a column's metadata while a predicate still needs its statistics. 2. Schema Changes: If you recently ran

ALTER TABLE ADD COLUMN

, the internal "Slot IDs" might have shifted, causing a mismatch in the statistics cache. 3. Complex Views/Casts: Queries involving complex views or multiple

CAST

operations sometimes create temporary internal columns that the CBO fails to calculate statistics for correctly. Immediate Workarounds If

ANALYZE TABLE ... ALL COLUMNS

did not solve it, try these steps in order: 1. Clear the Statistics Cache Sometimes the metadata in the cache is corrupted. Force a reload by dropping and re-collecting:

Copy code

sql
DROP STATS table_name;
ANALYZE TABLE table_name;

2. Disable Predicate Reordering (Common Fix) Many users have reported that disabling a specific optimizer rule stops this error from appearing:

Copy code

sql
-- Apply to the current session to test
SET enable_predicate_reorder = false;

-- If it works, apply globally
SET GLOBAL enable_predicate_reorder = false;

3. Disable the CBO (Last Resort) If the query is critical and keeps failing, you can force StarRocks to use the rule-based optimizer (RBO), which does not rely on these statistics:

Copy code

sql
SELECT /*+ SET_VAR(enable_cbo = false) */ * FROM your_table WHERE ...;

Permanent Solution: Upgrade This error is actively being patched. If you are on an early version of 3.3 or 3.4, check for a patch release: * Version 3.3.15+ and 3.5.x contain fixes for several "missing statistic of col" scenarios related to join reordering and expression mapping. * Check your

fe.log

for the specific "Slot ID" that is missing to confirm if it matches a fixed bug in the StarRocks Release Notes. Summary Table for FE Configs If you want to be extremely aggressive about background collection to prevent this: Config | Value | Purpose

statistic_auto_collect_predicate_columns_threshold

| Collects stats for all columns on wide tables.

enable_collect_full_statistic

true

| Ensures full stats are used instead of sampled stats.

statistic_collect_max_row_count_per_query

| Increase | Increases the limit of rows the analyzer processes. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cbo_stats/DROP_STATS.md References • sql-reference/sql-functions/aggregate-functions/std.md • sql-reference/sql-functions/cast.md • sql-reference/sql-functions/aggregate-functions/stddev.md

Rocky

09/11/2026, 7:13 AM

• using_starrocks/Cost_based_optimizer.md • sql-reference/sql-functions/aggregate-functions/count.md • sql-reference/information_schema/statistics.md • sql-reference/sql-functions/aggregate-functions/stddev_samp.md

ansh.gupta2

09/11/2026, 9:42 AM

@Rocky where does the query log store in starrocks shared data

Rocky

09/11/2026, 9:42 AM

In a StarRocks Shared-Data (Cloud-Native) cluster, query logs (known as Audit Logs) are stored similarly to the classic architecture. Even though your table data is in object storage (S3/HDFS), the logs remain local to the nodes for performance reasons. 1. Where are the logs located? There are two main types of logs you likely care about: * Audit Logs (SQL history): These record every SQL query sent to the cluster, who sent it, how long it took, and its status. * Location: On the FE (Frontend) nodes. * Default Path:

${STARROCKS_HOME}/fe/log/fe.audit.log

* CN/BE Logs (Execution details): These record the internal execution steps, warnings, and errors for the query fragments running on the compute nodes. * Location: On the CN (Compute Node) nodes. * Default Path:

${STARROCKS_HOME}/cn/log/cn.INFO

(and

.WARN

.ERROR

) 2. How to configure the location If you want to change where these logs are saved (e.g., to a larger attached volume), you can modify the configuration files: * For FE (Audit Logs): Edit

fe.conf

Copy code

conf
    # The directory where audit logs are stored
    audit_log_dir = /your/custom/path/log
    # How long to keep logs (default 30 days)
    audit_log_delete_age = 30d

* For CN (System Logs): Edit

cn.conf

Copy code

conf
    # The directory where CN system logs are stored
    sys_log_dir = /your/custom/path/log

3. How to query logs via SQL (Recommended) Since Shared-Data clusters often involve many ephemeral CN nodes, manually checking files is difficult. Most users use the AuditLoader plugin. 1. AuditLoader: This plugin automatically reads the

fe.audit.log

file and loads it into a StarRocks table (often in the

starrocks_monitor

database). 2. Once set up, you can query your logs like a normal table:

Copy code

sql
    SELECT * FROM starrocks_monitor.cluster_audit_log
    WHERE query_time > 1000
    ORDER BY start_time DESC;

4. Query Profiles If you are looking for the Query Profile (the deep technical breakdown of a specific query's execution plan), these are not stored as text files. They are kept in the FE's memory and can be accessed via: * Web UI:

http://<FE_IP>:8030/query

* SQL:

GET_QUERY_PROFILE('query_id');

Summary * Are logs stored in S3/HDFS? No, not by default. They are stored on the local disks of the FE and CN nodes. * Can I put them in S3? You can use the

AuditLoader

to save them into a Cloud-Native table, which will then physically reside in S3. References • administration/management/logs.md • administration/management/FE_configuration.md • administration/management/audit_loader.md • introduction/Architecture.md References • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md • quick_start/shared-data.md • introduction/Architecture.md • deployment/shared_data/s3.md

ansh.gupta2

09/11/2026, 9:43 AM

@Rocky can we store query log in cloud ?

Rocky

09/11/2026, 9:43 AM

Yes, you can store StarRocks query (audit) logs in the cloud. Since StarRocks does not have a "direct-to-S3" logging driver for its local files, there are two primary ways to achieve this: 1. The "StarRocks Native" Way (AuditLoader + Cloud-Native Table) This is the most common approach. It stores your logs in a StarRocks table, and if you are using a Shared-Data (Cloud-Native) cluster, that table’s data physically resides in your cloud storage (S3, GCS, or Azure Blob). 1. Install the AuditLoader Plugin: This plugin monitors the local

fe.audit.log

and automatically loads the data into a StarRocks table. 2. Create a Cloud-Native Table: In a shared-data cluster, create the audit table using the

CLOUD_NATIVE

engine (default in shared-data mode). 3. Result: Your logs are automatically moved from local disk into S3/Cloud Storage. You can then query them using standard SQL. Example Table Setup:

Copy code

sql
CREATE DATABASE starrocks_audit_db__;

CREATE TABLE starrocks_audit_db__.starrocks_audit_tbl__ (
  `queryId` VARCHAR(64),
  `timestamp` DATETIME NOT NULL,
  `queryType` VARCHAR(12),
  `user` VARCHAR(64),
  `queryTime` BIGINT,
  `stmt` VARCHAR(1048576)
  -- add other fields as needed
) ENGINE = OLAP
DUPLICATE KEY (`queryId`, `timestamp`)
PARTITION BY date_trunc('day', `timestamp`)
PROPERTIES (
  "replication_num" = "1" -- In shared-data, S3 handles redundancy
);

2. The "Cloud Infrastructure" Way (Log Shippers) If you want the logs in a dedicated cloud logging service (like AWS CloudWatch, Google Cloud Logging, or ELK), you should use a standard log agent. * Tools: AWS CloudWatch Agent, Fluentd, Logstash, or Vector. * Method: Point the agent to the

fe.audit.log

file on your FE nodes. * JSON Format: To make cloud parsing easier, enable JSON logging in your `fe.conf`:

Copy code

conf
    audit_log_json_format = true

* Benefit: This keeps your audit logs separate from your database storage and allows you to set up cloud-native alerts (e.g., alert if a query takes > 60 seconds). 3. Periodic Export (Cold Storage) If you already use AuditLoader but want to move old logs to a cheap "cold" bucket for compliance: * Use the

EXPORT

command to move data from your audit table to S3:

Copy code

sql
    EXPORT TABLE starrocks_audit_db*.starrocks_audit_tbl*
    PARTITION (p20231001)
    TO "<s3a://your-bucket/audit-archive/>"
    PROPERTIES ("format" = "parquet");

Summary Recommendation * For Analytics: Use AuditLoader. It makes the logs searchable via SQL within StarRocks. * For Compliance/Operations: Use a Log Shipper (like CloudWatch Agent) to ensure logs are preserved even if the StarRocks cluster is deleted. References • administration/management/audit_loader.md • administration/management/logs.md • sql-reference/sql-statements/table_bucket_part_index/EXPORT.md • deployment/shared_data/s3.md References • administration/management/audit_loader.md • administration/management/logs.md • deployment/shared_data/s3.md References • deployment/shared_data/s3.md • administration/management/logs.md • administration/management/audit_loader.md

ansh.gupta2

09/11/2026, 4:34 PM

@Rocky can running show create on table trigger stats collection?

Rocky

09/11/2026, 4:34 PM

No, running SHOW CREATE TABLE
does not trigger statistics collection in StarRocks.

SHOW CREATE TABLE

is a metadata-only operation. It simply retrieves the table's schema definition (columns, types, partitioning, and properties) from the Frontend (FE) leader's catalog. It does not involve the Cost-Based Optimizer (CBO) or any data scanning that would initiate a statistics gathering task. What actually triggers statistics collection? Statistics collection is triggered by the following events: 1. Automatic Background Tasks (Default): * Data Updates: When the amount of changed data exceeds a certain threshold (defined by

statistic_auto_collect_ratio

, default 0.8 or 80%). * Scheduled Intervals: The system periodically checks for "unhealthy" statistics every 10 minutes (defined by

statistic_collect_interval_sec

). 2. Data Loading (DML): * StarRocks triggers an asynchronous collection task at the end of an

INSERT INTO

INSERT OVERWRITE

statement to ensure the CBO has fresh data immediately after a load. 3. Manual Execution: * Running the

ANALYZE TABLE <table_name>

command explicitly. 4. Query-Triggered (External Tables only, v3.4.0+): * For external tables (Hive, Iceberg, etc.), if

enable_query_trigger_analyze

is true, a SELECT
query that needs missing statistics will trigger a background collection task. Even in this case, a

SHOW CREATE TABLE

would not trigger it because it doesn't "use" the statistics to build a plan. How to verify if stats are being collected If you are seeing statistics-related errors or want to see if a collection task was recently run, use:

Copy code

sql
-- Check the status of recent collection tasks
SHOW ANALYZE STATUS;

-- Check when statistics were last updated for a specific table
SHOW STATS META WHERE Table_name = 'your_table_name';

References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/11/2026, 4:44 PM

@Rocky i am using 4.0.0 version Permanent Solution: Upgrade This error is actively being patched. If you are on an early version of 3.3 or 3.4, check for a patch release: * Version 3.3.15+ and 3.5.x contain fixes for several "missing statistic of col" scenarios related to join reordering and expression mapping. * Check your

fe.log

for the specific "Slot ID" that is missing to confirm if it matches a fixed bug in the StarRocks Release Notes.

Rocky

09/11/2026, 4:44 PM

The "missing statistic of col" error in StarRocks 4.0.0 is typically a sign of an optimizer bug rather than a lack of actual statistics on your table. While the error message suggests the system is missing data, it usually happens because the Cost-Based Optimizer (CBO) incorrectly "prunes" (discards) a column during query planning that it actually needs later for a join or a predicate. Since you are specifically on 4.0.0, you are likely hitting a known issue related to join reordering or Materialized View (MV) rewrites that has been addressed in subsequent patch releases. 1. Why is this happening in 4.0.0? In early 4.0 releases, several logic bugs were identified where the optimizer: * Join Reordering: Dropped columns still referenced by predicates during join-reorder pruning (#74791). * MV Rewrite: Failed during cost estimation when a query aggregated the same base column twice (e.g.,

min(c)

and

max(c)

) via a synchronous MV or rollup (#75528). 2. Solutions Permanent Solution: Upgrade to 4.0.14+ or 4.1.x This is the recommended path. These specific "missing statistic" bugs were actively patched throughout the 4.0.x lifecycle. * Version 4.0.14+ contains the fix for the join-reorder column pruning error (#74791). * If you are moving to a newer branch, Version 4.1.1+ also includes these stability fixes. Short-Term Workarounds If you cannot upgrade immediately, you can try to bypass the specific optimization rule causing the crash: 1. Disable Join Reordering (Try this first): If the error happens during a complex JOIN, try disabling the join reorder rule for that session:

Copy code

sql
    SET SESSION enable_join_reorder = false;

2. Manually Refresh Stats: Even if the bug is logic-based, ensuring the metadata is fresh can sometimes "nudge" the optimizer into a different (successful) plan:

Copy code

sql
    ANALYZE TABLE <your_table_name> WITH SYNC;

3. Disable MV Rewrites: If the query involves Materialized Views, try:

Copy code

sql
    SET SESSION enable_materialized_view_rewrite = false;

3. How to verify the fix If you look at your FE logs (

fe.log

), you will see a stack trace for this error. If the trace mentions

JoinReorder

MaterializedViewRewrite

, it confirms you are hitting the specific bugs fixed in the 4.0.x patch sequence. References • release_notes/release-4.0.md (See fix for #74791, #75773, and #75528) • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • release_notes/release-4.0.md

ansh.gupta2

09/14/2026, 9:08 AM

@Rocky is trino hive is the central metadata catalog that is has sync all other catalog metadata so even if dont provide catalog in query it itself check from hive where that table located is it external hive table or delta table , can similar be done in starrocks?

Rocky

09/14/2026, 9:08 AM

In StarRocks, you can achieve a very similar experience to what you described using the Unified Catalog (introduced in v3.2+). While StarRocks generally follows a

catalog.database.table

hierarchy, it handles the "multi-format auto-detection" and "single entry point" requirements in the following way: 1. The Unified Catalog (Your "Central Catalog") The Unified Catalog is specifically designed to act as a single gateway for multiple data lake formats. If you point a Unified Catalog to a metadata service like Hive Metastore (HMS) or AWS Glue, StarRocks will automatically detect whether a table is Hive, Iceberg, Hudi, Delta Lake, Paimon, or Kudu. * Auto-Format Detection: You don't need separate catalogs for Hive and Delta. If your HMS contains both Hive and Delta tables, the Unified Catalog identifies the format on-the-fly and uses the correct reader. * No Redundant Configuration: You configure the connection (e.g., S3 credentials and HMS URI) once, and all supported formats under that metastore become accessible. 2. Can you query without providing the Catalog name? In StarRocks, you can avoid typing the catalog name in every query by setting your session context. This is similar to how you might select a default catalog in a SQL IDE. * Switch Context:

Copy code

sql
    SET CATALOG my_unified_catalog;
    USE my_database;
    SELECT * FROM my_table; -- No catalog or DB prefix needed

* Cross-Database Queries: Even if you are inside the Unified Catalog, you can query across its databases using

db.table

. You only need the 3-part name (

catalog.db.table

) if you are trying to join data from a different catalog (like the StarRocks internal

default_catalog

catalog.schema.table

catalog.database.table

Search Path |

sql.path

(can search schemas) | No global "search path" across different catalogs. You must

SET CATALOG

. Summary Recommendation If you want a "central" feel where you don't have to worry about where a table is located: 1. Create a Unified Catalog that points to your primary Hive Metastore or Glue. 2. Use
SET CATALOG <name>
at the start of your session. 3. StarRocks will then behave exactly as you described: it will check the metadata, identify the format (Hive/Delta/etc.), and execute the query without requiring explicit catalog prefixes. Example Setup:

Copy code

sql
CREATE EXTERNAL CATALOG my_central_hub
PROPERTIES (
    "type" = "unified",
    "unified.metastore.type" = "hive",
    "hive.metastore.uris" = "<thrift://xxx.xxx.xxx.xxx:9083>"
);

SET CATALOG my_central_hub;
SELECT * FROM some_delta_table; -- Works automatically

References • data_source/catalog/unified_catalog.md • data_source/catalog/catalog_overview.md • data_source/catalog/query_external_data.md References • data_source/catalog/unified_catalog.md • data_source/catalog/catalog_overview.md • data_source/catalog/query_external_data.md References • data_source/catalog/catalog_overview.md • data_source/catalog/query_external_data.md • data_source/catalog/default_catalog.md

Rocky

09/14/2026, 9:08 AM

• data_source/catalog/unified_catalog.md

ansh.gupta2

09/14/2026, 9:13 AM

@Rocky if i set delta_catalog then during query does it have search thorugh complete blob for folder location?

Rocky

09/14/2026, 9:13 AM

No, when you use a

deltalake_catalog

, StarRocks does not search through your entire blob storage to find folder locations. Instead, it uses a Metastore-driven approach combined with the Delta Lake Transaction Log. Here is how the path resolution works: 1. Metastore Lookup (HMS or Glue) When you create a Delta Lake catalog, you must provide a metastore (Hive Metastore or AWS Glue). * The metastore stores the base location of the table (e.g., `s3://my-bucket/data/my_table/`). _ StarRocks simply asks the metastore: _"Where is the folder for

database.table

?"* * The metastore returns the specific URI. StarRocks goes directly to that URI rather than scanning the bucket. 2. Delta Log Parsing (The
_delta_log
folder) Once StarRocks has the base location, it looks for the

_delta_log

directory at that path. * It reads the JSON and Checkpoint files within that folder to determine which Parquet files are part of the current table version. * This is a targeted read of specific metadata files, not a "list all files" operation on the entire storage. 3. Metadata Caching (Optimization) To avoid even these targeted metadata reads for every query, StarRocks (especially from v3.3.3+) uses an aggressive caching strategy: * Table Cache: Caches the table's metadata and structure. * Delta Log Cache: Caches the parsed JSON/Checkpoint files in memory. * Background Refresh: You can enable

enable_background_refresh_connector_metadata

so StarRocks periodically polls the metastore for changes. This ensures that when you run a query, the folder location and file list are already known and "ready to go" in the Frontend (FE) memory. Summary If you are worried about performance or API costs (like S3

LIST

calls): * StarRocks is efficient: It uses the metastore to jump straight to the correct folder. * It uses the Delta protocol: It only reads the specific Parquet files identified in the transaction log. * Caching reduces overhead: Frequent queries will often hit the local metadata cache, avoiding storage access for path resolution entirely. References • data_source/catalog/deltalake_catalog.md • data_source/catalog/catalog_overview.md References • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/iceberg_catalog.md

ansh.gupta2

09/14/2026, 9:16 AM

@Rocky does starricsks support that: This is likely the sharpest technical question. StarRocks' Delta Lake connector is newer/less mature than its Iceberg/Hive support. Be ready to answer: which Delta features are supported (deletion vectors, column mapping, schema evolution)? Did the replay hit any query failures or fallbacks due to unsupported Delta features? Any gaps found during POC should be disclosed proactively, not discovered by the board.

Rocky

09/14/2026, 9:16 AM

In StarRocks 4.0.0, the Delta Lake connector is significantly more capable than in early 3.x versions, but there are still specific gaps compared to the Iceberg implementation. 1. Supported Delta Features (The "Checks") StarRocks has recently added support for the most critical Delta protocol features to ensure query compatibility with modern Delta tables: * Deletion Vectors: Supported since v3.4.1. StarRocks can read Delta tables that use deletion vectors for "merge-on-read" style updates. * Column Mapping: Supported since v3.3.6. This allows StarRocks to query tables where the underlying Parquet file names don't match the Delta column names (e.g., after column renames). * Schema Evolution: Because column mapping is supported, most standard schema evolution scenarios (adding columns, renaming columns) are handled correctly during queries. * V2 Checkpoints: Supported since v3.3.0, improving the speed of reading the transaction log for large tables. 2. Known Gaps & "Sharp" Limitations These are the areas where you are most likely to encounter query failures or performance fallbacks: * No Time Travel: Unlike the Iceberg connector (which supports

FOR VERSION AS OF

), Delta Lake time travel is not currently supported in StarRocks. You can only query the latest snapshot. * Unsupported Data Types: StarRocks cannot query
MAP
and
STRUCT
types in Delta Lake. If your table contains these, queries referencing those columns will fail. * Write Limitations: While you can use

INSERT INTO

to append data to a Delta catalog, StarRocks does not support

UPDATE

DELETE

, or

MERGE

operations on external Delta Lake tables. * Advanced Clustering: There is no native support for Delta Liquid Clustering or Z-Ordering metadata. StarRocks will still query these tables, but it relies on its own Min/Max pruning and CBO rather than specifically leveraging Delta's Z-order indices. * Protocol Versions: If a table requires a "Reader Version" or specific feature beyond what is listed above (like certain Delta 3.x+ advanced features), the query may fail with a "protocol version not supported" error. 3. Recommendation for POC If you are moving from a Trino/Spark environment to StarRocks 4.0.0: 1. Check for Complex Types: Audit your Delta tables for

MAP

and

STRUCT

types; these are the most common cause of "Immediate Query Failure" during POCs. 2. Verify Deletion Vector Usage: Ensure your workloads that rely on Deletion Vectors are tested, as this is a relatively new path in the StarRocks storage engine. 3. Upgrade Path: Since you are on 4.0.0, ensure you move to at least 4.0.14+ to avoid the "missing statistics" bugs mentioned earlier, which often triggered during complex joins over Delta catalogs. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md • release_notes/release-3.4.md References • data_source/catalog/iceberg/iceberg_timetravel.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md • data_source/catalog/iceberg/iceberg_meta_table.md

ansh.gupta2

09/14/2026, 10:49 AM

@Rocky mysql> SHOW CATALOGS; +-----------------+-----------+------------------------------------------------------------------+ | Catalog | Type | Comment | +-----------------+-----------+------------------------------------------------------------------+ | default_catalog | Internal | An internal catalog contains this cluster's self-managed tables. | | delta_dev | Deltalake | NULL | | delta_prod | Deltalake | NULL | | hive | Hive | NULL | +-----------------+-----------+------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> SET CATALOG delta_prod; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM dev_ci.tagloop_raw LIMIT 1; +--------------------------------------+-------------+--------------+-----------------+---------------+---------+ | timeuuid | requestuuid | entityid | entityname | actiontime | checkid | +--------------------------------------+-------------+--------------+-----------------+---------------+---------+ | 8e1681be-648c-100f-89dd-5345d548ea12 | | 102060322486 | CheckAuditEntry | 1730796989890 | 19 | +--------------------------------------+-------------+--------------+-----------------+---------------+---------+ 1 row in set (7.77 sec) mysql> SELECT * FROM ^CIMIT 1; mysql> SET CATALOG hive; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM dev_ci.tagloop_raw LIMIT 1; ERROR 1064 (HY000): Failed to open the off-heap table scanner. java exception details: java.io.IOException: Failed to open the hive reader. at com.starrocks.hive.reader.HiveScanner.open(HiveScanner.java:227) Caused by: java.io.IOException: abfs://silver@mdpservingdevci.dfs.core.windows.net/prod-data/dev_ci/tagloop_raw/part-00058-cddbacca-2239-4034-b71a-e911ce26d0ba-c000.zstd.parquet not a SequenceFile at org.apache.hadoop.io.SequenceFile$Reader.init(SequenceFile.java:2036) at org.apache.hadoop.io.SequenceFile$Reader.initia mysql>

Rocky

09/14/2026, 10:49 AM

The error you are seeing—`java.io.IOException: ... not a SequenceFile`—is a classic sign of a metadata mismatch. It confirms exactly why you need the Unified Catalog instead of a standard Hive Catalog. What is happening: 1. Metadata Mismatch: When you use

SET CATALOG hive

, StarRocks treats the table as a standard Hive table. It looks at the Hive Metastore (HMS) and sees that the table is registered (likely with a default

TextInputFormat

SequenceFileInputFormat

). 2. Incorrect Reader: Because the catalog type is

hive

, StarRocks tries to use the standard Hive reader. When it encounters the actual data—which are Parquet files inside a Delta Lake structure—the reader gets confused and tries to parse the Parquet header as if it were a Hadoop SequenceFile, leading to the "not a SequenceFile" error. 3. Why Delta Catalog worked: When you used

delta_prod

, StarRocks ignored the generic Hive metadata and used the Delta Lake protocol to read the

_delta_log

. This allowed it to correctly identify the files as Parquet and read them. The Solution: Use the Unified Catalog In StarRocks, the

hive

catalog type is "strict"—it expects standard Hive tables. To get the Trino-like behavior where StarRocks auto-detects if a table is Hive or Delta regardless of the metadata, you must use the Unified Catalog. Create a new catalog like this:

Copy code

sql
CREATE EXTERNAL CATALOG central_hub
PROPERTIES (
    "type" = "unified",
    "unified.metastore.type" = "hive",
    "hive.metastore.uris" = "thrift://<your-hms-ip>:9083"
    -- Add your Azure ABFS credentials here
);

Why this fixes your issue: * Auto-Format Detection: The Unified Catalog does not blindly trust the

InputFormat

in the Hive Metastore. It inspects the table's properties and the file system. * Dynamic Routing: If it sees Delta Lake metadata, it uses the Delta reader. If it sees a standard Parquet/Hive table, it uses the Hive reader. * Single Entry Point: You can

SET CATALOG central_hub;

and query both Hive and Delta tables in the same database without ever switching catalogs again. Summary The error happened because you were using a "Hive-only" catalog to read a "Delta-formatted" table. Switch your

hive

catalog definition to

type = "unified"

Rocky

09/14/2026, 10:49 AM

• data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_timetravel.md • data_source/catalog/iceberg/iceberg_meta_table.md • data_source/catalog/default_catalog.md

ansh.gupta2

09/14/2026, 1:07 PM

@Rocky Why Delta Catalog worked: When you used

delta_prod

, StarRocks ignored the generic Hive metadata and used the Delta Lake protocol to read the

_delta_log

. This allowed it to correctly identify the files as Parquet and read them. does starrocks use hive to find table path?

Rocky

09/14/2026, 1:07 PM

Yes, StarRocks uses the Hive Metastore (HMS) or AWS Glue as a "pointer" to find the initial table path for a Delta Lake catalog. Here is the exact breakdown of how StarRocks uses Hive versus how it uses the Delta protocol: 1. Discovery Phase (Uses Hive) When you run a query like `SELECT * FROM delta_prod.dev_ci.tagloop_raw`: * Table Identification: StarRocks connects to the Hive Metastore defined in your catalog properties. * Path Retrieval: It asks the HMS for the

LOCATION

property of the table

tagloop_raw

. * Result: The HMS returns the base URI, for example:

<abfs://silver@mdpservingdevci.dfs.core.windows.net/prod-data/dev_ci/tagloop_raw/>

. 2. Execution Phase (Uses Delta Protocol) Once StarRocks has that path, it stops using the Hive Metastore's file-listing or format information. * Log Parsing: It goes directly to the storage (Azure ABFS in your case) and looks for the

_delta_log

folder at that location. * File Resolution: It reads the JSON/Checkpoint files in the

_delta_log

to find the exact Parquet files that make up the current version of the table. * Format Handling: It ignores whatever

InputFormat

(like

SequenceFileInputFormat

) might be incorrectly registered in the HMS and uses its internal Delta/Parquet reader. Why the
SET CATALOG hive
failed but
delta_prod
worked: * In `SET CATALOG hive`: StarRocks tries to be a "good Hive citizen." It sees the table in HMS and sees that HMS (or the tool that created it) says "This is a SequenceFile" or "This is a standard Hive table." It tries to read it using standard Hive logic, which fails because the actual files are Parquet/Delta. * In `SET CATALOG delta_prod`: StarRocks only uses Hive to get the folder path. After that, it says "I know this is a Delta table, I don't care what the Hive Metastore thinks the file format is," and it successfully reads the Delta log. Summary * Does it use Hive to find the path? Yes. * Does it use Hive to find the data files? No, it uses the Delta transaction log for that. * Can you bypass Hive? Not currently for a

deltalake

catalog; StarRocks requires a metastore (HMS or Glue) to map table names to paths. (Unlike the

ENGINE=file

table which allows hardcoding a path). References • data_source/catalog/deltalake_catalog.md • data_source/catalog/hive_catalog.md • data_source/catalog/catalog_overview.md References • data_source/catalog/deltalake_catalog.md • data_source/file_external_table.md

ansh.gupta2

09/14/2026, 1:12 PM

@Rocky how to replace standard hive with unified hive across three fe?

Rocky

09/14/2026, 1:12 PM

To replace a standard Hive catalog with a Unified catalog across a 3-FE cluster, you only need to execute the commands once on any FE (typically the leader). StarRocks automatically synchronizes catalog metadata across all FEs via its internal replication protocol (BDB JE). Because StarRocks does not support "altering" a catalog type, you must drop the old one and create the new one. Step 1: Backup Existing Properties Run

SHOW CREATE CATALOG hive;

to copy your existing credentials (ABFS keys, Hive URIs, etc.), as you will need them for the new definition. Step 2: Replace the Catalog Run these commands in your SQL client:

Copy code

sql
-- 1. Remove the old hive-only catalog
DROP CATALOG hive;

-- 2. Create the new Unified catalog using the same name
CREATE EXTERNAL CATALOG hive
PROPERTIES (
    "type" = "unified",
    "unified.metastore.type" = "hive",
    "hive.metastore.uris" = "thrift://<your-hms-ip>:9083",
    -- Include your existing Azure storage credentials here:
    "azure.adls2.storage_account_name" = "<account_name>",
    "azure.adls2.shared_key" = "<account_key>"
);

Why this works for your 3-FE cluster: * Automatic Sync: StarRocks treats catalogs as global metadata. When you run

CREATE EXTERNAL CATALOG

on the leader FE, it is written to the image and edit logs, which are then replayed by the other two FEs. * Metadata Refresh: When you drop and recreate the catalog with the same name, StarRocks clears the old metadata cache. The first time you query a table through the new

unified

catalog, it will perform a fresh discovery. Critical Considerations 1. Materialized Views: If you have any Materialized Views (MVs) that reference the old

hive

catalog, dropping the catalog will make those MVs invalid. You may need to refresh or recreate them. 2. Permissions: If you are using StarRocks' internal RBAC (Grant/Revoke), permissions are tied to the catalog name. If you use the same name (

hive

), most permissions should remain intact, but it is best practice to verify them with

SHOW GRANTS

. 3. Downtime: There is a brief "metadata gap" between the

DROP

and

CREATE

commands where queries against that catalog will fail. How to Verify After running the commands, check the status on any FE:

Copy code

sql
SHOW CATALOGS; -- Ensure 'type' now shows 'unified'
SET CATALOG hive;
SHOW DATABASES;
SELECT * FROM dev_ci.tagloop_raw LIMIT 1; -- This should now work without the SequenceFile error

References • data_source/catalog/unified_catalog.md • administration/management/FE_management.md • sql-reference/sql-statements/data-definition/CREATE_EXTERNAL_CATALOG.md

ansh.gupta2

09/14/2026, 1:25 PM

Rocky

09/14/2026, 1:25 PM

Success! You have successfully converted the

hive

catalog to the Unified type. As shown in your query result, the Unified catalog is now working as expected: 1. Auto-Detection: It correctly identified that

dev_ci.tagloop_raw

is a Delta Lake table, even though you accessed it via the Hive catalog. 2. Protocol Switching: It automatically bypassed the incorrect Hive "SequenceFile" metadata and used the Delta Lake reader to pull the Parquet files from Azure storage. 3. Unified Access: You can now query standard Hive tables and Delta Lake tables using this single

hive

catalog without needing to manually switch to

delta_prod

. The 4.60s execution time includes the initial metadata discovery; subsequent queries on the same table will likely be faster due to StarRocks' metadata caching.

ansh.gupta2

09/14/2026, 1:43 PM

@Rocky mysql> SELECT * FROM dev_ci.tagloop_raw LIMIT 1; ERROR 5501 (3F000): Getting analyzing error. Detail message: Unknown database 'dev_ci'. mysql> SET CATALOG hive ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM dev_ci.tagloop_raw LIMIT 1; +--------------------------------------+-------------+-------------+-----------------+---------------+---------+ | timeuuid | requestuuid | entityid | entityname | actiontime | checkid | +--------------------------------------+-------------+-------------+-----------------+---------------+---------+ | 576e9c4a-fe9c-1000-b020-f300a3ae18d4 | | 11378000783 | CheckAuditEntry | 1788701402232 | 47 | +--------------------------------------+-------------+-------------+-----------------+---------------+---------+ 1 row in set (4.65 sec) mysql> how to set hive as default catalog ?

Rocky

09/14/2026, 1:43 PM

To set your

hive

catalog as the default for every new session (so you don't have to run

SET CATALOG hive

every time), you can use one of the following methods depending on your StarRocks version and requirements. Method 1: Set Globally for all users (v3.2.4+) If you want every user who logs into the cluster to start in the

hive

catalog by default, run this command once:

Copy code

sql
-- Requires ADMIN or SYSTEM-level OPERATE privilege
SET GLOBAL catalog = 'hive';

* Effect: All future sessions (logins) will start in the

hive

catalog. * Current Session: This will not change your current session; you still need to run

SET CATALOG hive

once for the active window. Method 2: Set for a Specific User (v3.3.3+) If you only want a specific user (e.g.,

admin

) to default to the

hive

catalog:

Copy code

sql
ALTER USER 'admin' SET PROPERTIES ('session.catalog' = 'hive');

* Effect: This is the cleanest way to manage defaults without affecting other users or applications that might rely on the

default_catalog

. Method 3: JDBC Connection String If you are connecting via a BI tool or application and cannot change global settings, you can specify the catalog directly in your JDBC URL:

Copy code

text
jdbc:mysql://<FE_IP>:9030/hive.dev_ci

By including the catalog name as a prefix to the database in the connection string, the session will automatically initialize into that catalog. Note on
default_catalog
In StarRocks, there is always one internal catalog named

default_catalog

. You cannot delete it or rename an external catalog to

default_catalog

. Using the methods above simply changes the active catalog pointer for the session upon login. References • sql-reference/System_variable.md • sql-reference/sql-statements/account-management/ALTER_USER.md • data_source/catalog/catalog_overview.md References • sql-reference/System_variable.md • sql-reference/sql-statements/Catalog/SET_CATALOG.md • sql-reference/sql-statements/cluster-management/config_vars/SET.md • data_source/catalog/unified_catalog.md

ansh.gupta2

09/14/2026, 1:44 PM

@Rocky mysql> SET GLOBAL catalog = 'hive'; ERROR 1228 (HY000): Variable 'catalog' is a SESSION variable and can't be used with SET GLOBAL

Rocky

09/14/2026, 1:44 PM

You encountered that error because the

catalog

variable is a session variable (introduced in v3.2.4). In StarRocks, session variables cannot be set using

SET GLOBAL

. To set a default catalog that persists every time you log in, use one of the following methods: Method 1: For a specific user (Recommended, v3.3.3+) If your cluster is version v3.3.3 or higher, you can attach a default catalog and database directly to your user identity. This property is loaded automatically upon login.

Copy code

sql
-- Replace 'admin' with your username
ALTER USER 'admin' SET PROPERTIES ("catalog" = "hive");

-- You can also set a default database within that catalog
ALTER USER 'admin' SET PROPERTIES ("catalog" = "hive", "database" = "dev_ci");

Note: Run
SHOW PROPERTY FOR 'admin';
to verify the setting. Method 2: Via Connection String (Universal) If you are on an older version or connecting via a BI tool/IDE (like DBeaver or a JDBC application), you can specify the catalog and database directly in your connection string. * For JDBC:

jdbc:mysql://<FE_IP>:9030/hive.dev_ci

* For MySQL Client:

mysql -h <FE_IP> -P 9030 -u root -D hive.dev_ci

Method 3: One-line Switch (Current Session) If you just want to quickly switch at the start of your session, you can combine the catalog and database into a single

USE

command:

Copy code

sql
USE hive.dev_ci;

Why "SET GLOBAL" didn't work In StarRocks, there is no global system-wide setting to change the default catalog from

default_catalog

to an external one for all users at once. This is because

default_catalog

is reserved for internal StarRocks tables and materialized views. Use Method 1 for every user that needs

hive

as their starting point. References • sql-reference/sql-statements/account-management/ALTER_USER.md • sql-reference/System_variable.md • data_source/catalog/default_catalog.md References • data_source/catalog/default_catalog.md • quick_start/helm.md • data_source/catalog/jdbc_catalog.md • data_source/catalog/query_external_data.md

ansh.gupta2

09/14/2026, 6:11 PM

@Rocky do i need to setup JAVA_OPTS for CN and FE?

Rocky

09/14/2026, 6:11 PM

Yes, you need to configure JAVA_OPTS for both the FE and the CN nodes, though for different reasons. Since you are using external catalogs (Hive and Delta Lake), the CN configuration is especially important for performance and stability. 1. For FE (Frontend) The FE is a Java process. It handles query planning and metadata management, so JVM tuning is critical. * Heap Size: This is the most important setting. The metadata for your cluster and external catalogs is stored in the FE's memory. * Recommendation: Set

-Xmx

and

-Xms

to the same value. * Size Guide: * Small cluster (<1M tablets): 16GB * Large cluster (1M–2M tablets): 32GB * Example (
fe/conf/fe.conf
):

Copy code

bash
    JAVA_OPTS="-Xmx16g -Xms16g -XX:+UseG1GC"

2. For CN (Compute Node) The CN is primarily a C++ process, but it starts an embedded JVM to handle communication with external data sources like Hive, Delta Lake, and Hudi. * Why you need it: Without a properly configured JVM on the CN, you will likely encounter errors when trying to read Parquet/Orc files or connect to the Hive Metastore. * JVM Heap: If you query very large tables or many partitions at once, the JVM on the CN needs enough memory to handle the metadata. * Important Flags (StarRocks 3.5+): If you are on version 3.5.0 or later, you must add specific permission flags to the CN's

JAVA_OPTS

to allow it to access internal Java utilities used by the HDFS/Cloud clients. * Example (
cn/conf/cn.conf
):

Copy code

bash
    # Basic heap for the embedded JVM
    JAVA_OPTS="-Xmx4g"

    # Required for External Catalogs in v3.5+
    JAVA_OPTS="$JAVA_OPTS --add-opens=java.base/java.util=ALL-UNNAMED"

    # Required if you use Java UDFs
    JAVA_OPTS="$JAVA_OPTS --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/sun.nio.ch=ALL-UNNAMED"

3. Don't forget
JAVA_HOME
For the CN to actually use these options, it must know where your JDK is installed. * In your

cn/bin/start_cn.sh

(or

be/bin/start_be.sh

), ensure

export JAVA_HOME

points to a JDK (not just a JRE). Summary Checklist Node Type | Config File | Why? | Recommended Min Memory FE |

fe.conf

| Core process is Java. | 16GB+ CN |

cn.conf

| Needed for Hive/Delta/UDFs. | 4GB - 8GB References • administration/management/FE_configuration.md • data_source/catalog/hive_catalog.md • deployment/helm.md (contains JAVA_OPTS best practices) References • administration/management/FE_configuration.md

ansh.gupta2

09/14/2026, 6:22 PM

@Rocky apiVersion: starrocks.com/v1 kind: StarRocksCluster metadata: name: starrocks-shared-data namespace: ****** # Masked namespace spec: # ============================================================ # FE — metadata & query coordination (shared-data mode) # ============================================================ starRocksFeSpec: image: starrocks/fe-ubuntu:4.0.0 replicas: 3 # 3 FEs for production HA (quorum-based) requests: cpu: "58" memory: "480Gi" limits: cpu: "58" memory: "480Gi" nodeSelector: app: ****** # Masked internal node topology tolerations: - key: "app" operator: "Equal" value: "******" # Masked internal node topology effect: "NoSchedule" storageVolumes: - name: fe-meta storageClassName: ****** # Masked cloud provider storage class storageSize: 50Gi mountPath: /opt/starrocks/fe/meta service: type: ClusterIP configMapInfo: configMapName: starrocks-shared-data-fe-cm resolveKey: fe.conf # ============================================================ # CN — stateless compute nodes (query object storage) # No BE needed: all data lives in Azure Blob / ADLS Gen2. # ============================================================ starRocksCnSpec: image: starrocks/cn-ubuntu:4.0.0 replicas: 3 requests: cpu: "58" memory: "480Gi" limits: cpu: "58" memory: "480Gi" nodeSelector: app: ****** # Masked internal node topology tolerations: - key: "app" operator: "Equal" value: "******" # Masked internal node topology effect: "NoSchedule" storageVolumes: #- name: cn-cache #storageClassName: ****** # Masked cloud provider storage class #storageSize: 200Gi #mountPath: /opt/starrocks/cn/storage/cache - name: cn-spill storageClassName: ****** # Masked cloud provider storage class storageSize: 300Gi mountPath: /opt/starrocks/cn/storage/spill configMapInfo: configMapName: starrocks-shared-data-cn-cm resolveKey: cn.conf --- # ============================================================ # FE ConfigMap — shared-data mode pointing to Azure Blob # ============================================================ apiVersion: v1 kind: ConfigMap metadata: name: starrocks-shared-data-fe-cm namespace: ****** # Masked namespace data: fe.conf: | LOG_DIR = ${STARROCKS_HOME}/log http_port = 8030 rpc_port = 9020 query_port = 9030 edit_log_port = 9010 JAVA_OPTS = -Xmx64G -Xms64G -XX:+UseG1GC # --- Shared-data mode --- run_mode = shared_data cloud_native_meta_port = 6090 cloud_native_storage_type = azblob # Auto-create builtin_storage_volume from the conf below. # NOTE: only takes effect on a FRESH FE bootstrap (empty fe/meta). enable_load_volume_from_conf = true # --- Azure Blob Storage --- # New path (v3) for the rebuilt cluster; old v2 data is orphaned/abandoned. azure_blob_endpoint = ****** # Masked blob endpoint azure_blob_path = ****** # Masked blob path azure_blob_shared_key = ****** # Masked credentials # Timezone default_session_variable_time_zone = Asia/Kolkata query_timeout = 3600 --- # ============================================================ # CN ConfigMap # ============================================================ apiVersion: v1 kind: ConfigMap metadata: name: starrocks-shared-data-cn-cm namespace: ****** # Masked namespace data: cn.conf: | LOG_DIR = ${STARROCKS_HOME}/log thrift_port = 9060 be_http_port = 8040 heartbeat_service_port = 9050 brpc_port = 8060 # --- Data Cache: subsystem must stay ENABLED in shared-data mode --- # Disabling starlet_use_star_cache/datacache at the process level # segfaults the CN on startup (SIGSEGV / exit 139). To get cold / # no-cache-reuse query behavior, DON'T disable it here — instead use # session vars: SET GLOBAL enable_scan_datacache=false; # SET GLOBAL enable_populate_datacache=false; datacache_enable = false #storage_root_path = /opt/starrocks/cn/storage/cache #datacache_mem_size = 16G #datacache_disk_size = 180G #enable_datacache_disk_auto_adjust = false # --- Spill to disk (dedicated 300Gi cn-spill volume) --- spill_local_storage_dir = /opt/starrocks/cn/storage/spill this manifest for our starrock cluster , this we using for external catalog only that is hive and delta, and our workload is hitting million of queries in single day on massive table in production cluster we will increase the number of CN nodes, but we need to most optimized configuration for this cluster to serve most efficient in terms of cost and performance , can you validate this manifest and give you input on this, caching i cannot enable for query as we running dynamic queries, metadata caching can be done, also we are using starrocks 4.0.0 version. Let me know what other input you needed

Rocky

09/14/2026, 6:23 PM

To optimize your StarRocks 4.0.0 cluster for millions of daily queries on Hive and Delta Lake using Azure Blob, there are several critical gaps in your current manifest—specifically regarding JVM memory allocation and Data Cache strategy. 1. Critical Fix: CN
JAVA_OPTS
In your current CN ConfigMap,

JAVA_OPTS

is missing. For external catalogs (Hive/Delta), the CN uses an embedded JVM to handle metadata parsing and Parquet/ORC footers. Without this, your CN will likely crash under load or perform poorly. Recommended CN Change:

Copy code

yaml
# Inside cn.conf
JAVA_OPTS = "-Xmx32G -Xms32G --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/sun.nio.ch=ALL-UNNAMED"

Note: The
--add-opens
flags are mandatory for StarRocks 4.0 and 3.x to allow the CN's C++ core to interact with the JVM's HDFS/Azure libraries. 2. FE Memory & Metadata Caching You have 480Gi of RAM on the FE nodes but are only using 64Gi for the JVM. For "millions of queries" and "massive tables," the FE will be under heavy pressure to store and plan external table metadata. Recommended FE Change:

Copy code

yaml
# Inside fe.conf
JAVA_OPTS = "-Xmx128G -Xms128G -XX:+UseG1GC"

# External Catalog Performance Settings (v4.0.0+)
# Limits metadata cache to 10% of memory (ensure this fits in your Xmx)
remote_file_cache_memory_ratio = 0.2

3. Re-evaluating
datacache_enable = false
You mentioned disabling caching because your queries are "dynamic." In StarRocks 4.0.0, Data Cache is not just for row data—it is crucial for metadata and Parquet/ORC footers. Disabling it entirely forces every query to do multiple round-trips to Azure Blob just to find where the data is. Recommendation: Enable the cache but dedicate it specifically to metadata and hot blocks. Even for dynamic queries, the "tail" of Parquet files (footers) is frequently reused. * Performance: Enable it. * Cost: Reduce the

datacache_disk_size

if you want to save on managed disk costs, but keep the memory cache active.

Copy code

yaml
# Inside cn.conf
datacache_enable = true
# Use a smaller disk if cost is a concern, but keep it enabled for footers
datacache_mem_size = 32G 
datacache_disk_size = 100G

4. Concurrency Tuning for Millions of Queries With 58 CPUs, StarRocks' default parallelism might be too aggressive for a high-concurrency (millions of queries) workload, leading to CPU exhaustion. Add these to your global session variables or FE config: * `pipeline_dop`: Set this to

(auto) or a fixed number like

to prevent single queries from starving the CPU. * `enable_connector_adaptive_io_tasks = true`: (Default in 4.0) Helps manage I/O pressure to Azure. * Resource Groups: For millions of queries, you must use Resource Groups to ensure that heavy "massive table" scans don't kill smaller, faster queries. 5. Shared-Data Mode vs. External Catalogs You are using

run_mode = shared_data

. * Advantage: This allows you to create Asynchronous Materialized Views on your Hive/Delta tables that are stored in StarRocks (on Azure Blob). This is the single best way to reduce cost and increase performance for "massive tables." * Note: If you are not using StarRocks internal tables or MVs,

shared_data

mode doesn't provide additional benefit for external catalogs over

shared_nothing

. However, keeping it is fine as it gives you the flexibility to build MVs later. Summary Checklist for your YAML 1. CN: Add

JAVA_OPTS

with

--add-opens

flags. 2. FE: Increase

JAVA_OPTS

128G

(you have the RAM, use it to avoid GC pauses). 3. CN: Flip

datacache_enable

true

. This is the #1 performance booster for Data Lake analytics in StarRocks. 4. Security: Ensure the CN has Azure credentials (if not using Workload Identity) defined in the

cn.conf

or the Catalog properties.

Rocky

09/14/2026, 6:23 PM

Version Note: You listed image

4.0.0

. Ensure you are using the official release. If you see stability issues with external metadata, consider the latest

3.3.x

LTS version, as

4.0

introduces several new metadata parsing architectures. References • administration/management/BE_configuration.md • faq/Others.md • data_source/catalog/deltalake_catalog.md • sql-reference/sql-functions/JAVA_UDF.md • knowledge/usage/best-practice.md • data_source/catalog/iceberg/iceberg_catalog.md

ansh.gupta2

09/14/2026, 6:32 PM

@Rocky rest everything fine? also isnt run_mode =

shared_nothing

for disk read ? for external catalog isnt CN is use over BE?

Rocky

09/14/2026, 6:32 PM

To answer your questions about the architecture and node types: 1.
shared_data
vs.
shared_nothing
You are correct that

shared_nothing

is the traditional architecture where data is stored on local BE disks. However, for your specific manifest, run_mode = shared_data
is the correct choice. * Why
shared_data
? Even though your main workload is external (Hive/Delta), StarRocks still needs a place to store its own internal metadata, temporary segments, and Materialized Views. By using

shared_data

, you are telling StarRocks to store its "internal" root (the

builtin_storage_volume

) on Azure Blob instead of requiring large, expensive persistent disks on every node. * External Catalogs: For Hive and Delta Lake, the

run_mode

doesn't change how the data is read (it's remote either way), but

shared_data

mode allows you to scale your CNs up and down instantly without "rebalancing" data, which is perfect for your million-query-per-day workload. 2. CN vs. BE for External Catalogs Yes, CN (Compute Node) is the preferred choice for External Catalogs, especially in Kubernetes. * Statelessness: CNs are essentially "stateless BEs." Since Hive/Delta data lives in Azure Blob, the CN doesn't need to "own" any data on local disk. It just fetches, filters, and processes. * Ease of Scaling: In a production cluster with "millions of queries," you can set an HPA (Horizontal Pod Autoscaler) to spin up 20+ CNs during peak hours and scale down at night. If you used BEs, scaling would be slower because StarRocks would try to move internal data shards around. * StarRocks 4.0.0 Architecture: In the 4.0.0 shared-data architecture, the CN is the primary worker. It handles all the heavy lifting for the "Data Lakehouse" use case. 3. Final Validation of your Manifest Your manifest is a great start for a production-grade cloud-native cluster, but here are the final "Optimized" tweaks for your specific scale: Component | Recommendation | Why? CN
replicas
| Increase to 10+ (eventually) | 3 CNs for "millions of queries" on "massive tables" will likely hit CPU bottlenecks. Start with 3, but monitor

CPU Usage

. CN
JAVA_OPTS
| Must add | (As mentioned before) External catalogs use the JVM for Parquet/ORC metadata. Without this, your CNs will be unstable. FE
Xmx
| 128G | You have 480Gi per node. Giving the FE more RAM allows it to cache more Hive partition metadata, making query planning much faster. datacache_enable
| Set to
true
| CRITICAL: Even if you don't cache data, StarRocks uses this to cache Parquet "footers." This prevents thousands of tiny, slow network calls to Azure Blob for every single query. Azure Credentials | Use Managed Identity | Since you are on Azure, instead of

azure_blob_shared_key

(which is in plain text in your ConfigMap), use Workload Identity. It's more secure for production. Summary Recommendation Keep

run_mode = shared_data

and use CNs. They are designed exactly for this "Compute-Only" Data Lake scenario. Just ensure you enable the Data Cache (for metadata/footers) and configure the CN JVM to handle the heavy Hive/Delta metadata load. References • introduction/Architecture.md • deployment/shared_data/feature-support-shared-data.md • data_source/catalog/hive_catalog.md • administration/management/CN_configuration.md References • table_design/table_design.md • data_source/catalog/catalog_overview.md

Rocky

09/14/2026, 6:32 PM

• data_source/feature-support-data-lake-analytics.md • deployment/shared_data/feature-support-shared-data.md • data_source/catalog/iceberg/iceberg_catalog.md

ansh.gupta2

09/14/2026, 6:34 PM

@Rocky what CN JVM , starrocks is written in C++ how java come into picture and what value should i set it ?

Rocky

09/14/2026, 6:35 PM

Even though StarRocks' core engine is written in C++ for high performance, it uses an embedded JVM (Java Virtual Machine) inside the C++ process (CN or BE) via JNI (Java Native Interface) to interact with the broader data lake ecosystem. Why Java is in the picture StarRocks does not reinvent the wheel for every data source. It leverages existing, battle-tested Java libraries for the following tasks on the CN: 1. Metastore Communication: Talking to the Hive Metastore (HMS) or AWS Glue to get table schemas and partition locations. 2. Cloud Storage SDKs: Using the official Azure SDKs to authenticate and communicate with Azure Blob Storage / ADLS Gen2. 3. File Format Metadata: While StarRocks has a native C++ Parquet/ORC reader for data blocks, it often uses Java libraries to parse complex file footers and metadata before the scan begins. 4. Java UDFs: If you ever run custom Java functions, they execute within this embedded JVM. What value should you set? Since your CNs have 480Gi of RAM and you are handling millions of queries on massive tables, you must give the JVM enough "breathing room" to handle thousands of concurrent metadata requests without triggering frequent Garbage Collection (GC) pauses. Recommended Setting for your Scale: Add this to your

cn.conf

(via your ConfigMap):

Copy code

bash
JAVA_OPTS="-Xmx32G -Xms32G -XX:+UseG1GC --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/sun.nio.ch=ALL-UNNAMED"

Breakdown of the values: * -Xmx32G -Xms32G
: Sets the JVM heap to 32GB. For "millions of queries," 16GB is the minimum, but 32GB is the "sweet spot" for massive partition counts and heavy Delta Lake metadata. Since you have 480Gi total, 32GB is only ~7% of your RAM, leaving 448Gi for the C++ vectorization engine and Data Cache. * --add-opens...
: These are mandatory for StarRocks 3.x and 4.0 if you are using JDK 17+. They allow the C++ core to access internal Java utilities needed for high-speed data transfer between C++ and Java. * -XX:+UseG1GC
: Ensures low-latency garbage collection so your query latency stays consistent. One Final Warning:
JAVA_HOME
For these

JAVA_OPTS

to work, you must ensure the StarRocks process can find the JDK. In your Kubernetes setup, the

starrocks/cn-ubuntu:4.0.0

image usually has the JDK pre-installed, but double-check that your environment or

cn.conf

doesn't override

JAVA_HOME

incorrectly. It must point to a JDK, not just a JRE. References • data_source/catalog/deltalake_catalog.md • administration/management/CN_configuration.md • sql-reference/sql-functions/JAVA_UDF.md • data_source/catalog/jdbc_catalog.md • integrations/data_lakes.mdx • introduction/Architecture.md References • integrations/data_lakes.mdx • data_source/catalog/catalog_overview.md • data_source/catalog/jdbc_catalog.md • table_design/table_design.md • introduction/Architecture.md

Rocky

09/14/2026, 6:35 PM

You are correct in your intuition, but there are some important distinctions in how StarRocks uses these modes, especially for your "External Catalog only" use case on Kubernetes. 1.
run_mode = shared_nothing
vs.
shared_data
* shared_nothing
(Default): This mode is for when you want StarRocks to store its internal tables (the

default_catalog

) on the local disks of the nodes. It is "shared-nothing" because each node owns a piece of the data. * shared_data
(Your Manifest): This mode is for when StarRocks stores its internal tables and metadata on object storage (Azure Blob). * Why your choice is actually better: Even if you only use Hive and Delta (external catalogs), StarRocks still needs an internal storage mode for its own system tables, metadata, and potentially Materialized Views. By using

shared_data

, your CN nodes remain stateless. This makes scaling up/down to handle "millions of queries" much faster on K8s because you don't have to wait for data to rebalance between disks. 2. CN vs. BE for External Catalogs * CN (Compute Node): This is a stateless node designed specifically for compute-heavy workloads like yours. It is the "modern" version of a worker node. * BE (Backend): This is a node that handles both compute and persistent local storage. * The Verdict: For an External Catalog workload (Hive/Delta), a CN is superior to a BE. Since the data already lives in Hive/Delta, you don't need the BE's persistent local storage. CNs allow you to scale your compute power elastically based on the volume of your "millions of queries." 3. Critical Feedback on your Manifest While your architecture (Shared-Data + CNs) is the correct choice for a high-concurrency external catalog workload, your configuration has two major bottlenecks: A. The "No Cache" Performance Penalty You mentioned you disabled

datacache_enable

because queries are dynamic. In StarRocks 4.0, the Data Cache is vital for external catalogs, even for dynamic queries. * Why? Every time you query a Hive/Delta table, StarRocks must read the file footers (metadata inside the Parquet/ORC files). * The Impact: With cache disabled, StarRocks must fetch these footers from Azure Blob for every single query. This adds significant latency and Azure I/O costs. * Fix: Enable

datacache_enable = true

. Even if the data changes, the cache uses a TTL/LRU mechanism. Caching just the "footers" and "metadata" will make your queries 3-5x faster. B. JVM Memory Gap Your CNs have 480Gi of RAM, but your manifest doesn't give any of it to the JVM. For Hive/Delta, the CN uses a Java-based reader. * Current Risk: The CN will use a default (often tiny) heap, which will cause

OutOfMemory

errors or extreme slowness when scanning massive Hive tables. * Fix: Add

JAVA_OPTS

to your CN ConfigMap. Give it at least 32GB to 64GB since you have 480GB available. Revised CN Config Recommendations

Copy code

yaml
# Inside your starrocks-shared-data-cn-cm
data:
  cn.conf: |
    # ... your existing ports ...

    # 1. ADD JAVA_OPTS (Critical for Hive/Delta)
    JAVA_OPTS = "-Xmx64G -Xms64G --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/sun.nio.ch=ALL-UNNAMED"

    # 2. ENABLE DATA CACHE (Critical for Azure Blob Performance)
    datacache_enable = true
    # Since you have 480Gi RAM, give some to memory cache
    datacache_mem_size = 64G 
    # Use your spill volume for disk cache if possible, or a dedicated cache volume
    # datacache_disk_size = 200G 

    # 3. CONCURRENCY (For millions of queries)
    # Prevents one giant query from taking all 58 cores
    pipeline_dop = 16 
    # Helps with Hive/Delta I/O
    enable_connector_adaptive_io_tasks = true

Summary for your Production Cluster: 1. Keep `shared_data`: It’s the right way to run CNs on Kubernetes for elasticity.

Rocky

09/14/2026, 6:35 PM

2. Stick with CNs: They are the right tool for external data lake analytics. 3. Fix the JVM: Your CNs need memory allocated via

JAVA_OPTS

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

Rocky

09/14/2026, 6:35 PM

ansh.gupta2

09/15/2026, 2:25 PM

@Rocky what property resoposible for concurrent queries run atmost in starrocks

Rocky

09/15/2026, 2:25 PM

There are a few different properties in StarRocks that control the maximum number of concurrent queries or connections, depending on the level of control you need. 1. Global Query Concurrency (Recommended) The primary property to limit concurrent query execution is query_queue_concurrency_limit
. * Property:

query_queue_concurrency_limit

(System Variable) * Description: This sets the upper limit of concurrent queries allowed to run on a single BE (or CN) node. * Behavior: When this limit is reached, new queries will be placed in a Query Queue instead of being rejected immediately (if query queues are enabled). * Usage:

Copy code

sql
    SET GLOBAL query_queue_concurrency_limit = 100;

2. Resource Group Concurrency (Isolation) If you want to limit concurrency for specific workloads (e.g., giving "Large Queries" a smaller limit than "Small Queries"), use Resource Groups. * Property:

concurrency_limit

* Description: Limits the number of concurrent queries allowed for that specific group on each BE node. * Usage:

Copy code

sql
    CREATE RESOURCE GROUP group_name
    PROPERTIES (
        "concurrency_limit" = "20"
    );

3. User-Level Connection Limits If you want to limit how many simultaneous client connections (sessions) a single user can have open: * Property:

max_user_connections

* Description: Limits the number of TCP connections from the client to the FE. * Usage:

Copy code

sql
    ALTER USER 'username' SET PROPERTIES ("max_user_connections" = "100");

4. Advanced Scheduling (StarRocks 3.3.4+) In newer versions, StarRocks introduced a "V2" query queue that uses slots to manage resources more precisely. * FE Config:

query_queue_v2_concurrency_level

* Description: Controls the number of logical concurrency "layers" used to compute the system's total query slots. Higher values allow more concurrent queries but reduce the memory available per query slot. Summary of Differences Property | Level | Controls | Behavior if Limit Reached query_queue_concurrency_limit
| Global/Session | Executing Queries | Queues the query concurrency_limit
| Resource Group | Executing Queries | Queues/Throttles by Group max_user_connections
| User | Active Sessions | Rejects the connection References • administration/management/resource_management/query_queues.md • administration/management/resource_management/Query_management.md • sql-reference/System_variable.md References • administration/management/resource_management/query_queues.md • administration/management/FE_configuration.md • administration/management/resource_management/Query_management.md • sql-reference/System_variable.md • faq/Others.md

ansh.gupta2

09/16/2026, 7:03 AM

@Rocky what query does kill after global resource exhausted ? query taking most memroy at that time? is their any property for this?

Rocky

09/16/2026, 7:04 AM

In StarRocks, when global resources (specifically memory on a BE or CN node) are exhausted, the system does not randomly kill queries. Instead, it follows a specific victim selection logic driven by its internal memory trackers. 1. What query is killed during Global OOM? When a node hits its mem_limit
(the hard limit, default 90% of total RAM), the StarRocks OOM killer is triggered. * The Victim: StarRocks identifies and terminates the query that is currently consuming the most memory on that specific node. * Reasoning: Killing the largest consumer is the most efficient way to quickly reclaim a large block of memory and stabilize the process, preventing a full process crash (SIGSEGV or OS OOM-kill). * Error Message: You will typically see an error like:

Memory exceed limit. Used: ..., Limit: ... Mem usage has exceed the limit of query pool

. 2. Properties that control this behavior There are three main ways to manage or prevent this "aggressive" killing: A. Proactive Killing (Circuit Breakers) Instead of waiting for the global limit to hit, you can set a "circuit breaker" for individual queries using Resource Groups. * Property:

big_query_mem_limit

(set in a Resource Group) * Effect: If a single query exceeds this limit, it is killed immediately, even if the cluster has plenty of memory. This prevents a single "bad" query from ever threatening the global

mem_limit

. B. The "Soft" Limit and Spilling Before the system reaches the hard 90% limit, it hits a "soft limit" (default 80%). * Property:

query_pool_spill_mem_limit_threshold

(Default

1.0

, meaning 100% of the query pool memory). * Effect: If you enable Spill to Disk, StarRocks will start moving data from memory to disk when this threshold is hit, effectively preventing the OOM killer from needing to run. C. Prevention via Queuing Instead of killing queries, you can make them wait. * Property:

query_queue_mem_used_pct_limit

(Default

0.0

, disabled) * Effect: Set this to something like

0.8

(80%). When the node reaches 80% memory usage, new queries will be put into a queue instead of running and risking an OOM. 3. Summary of Key Parameters Level | Property | Default | Behavior Global Hard Limit |

mem_limit

(BE config) |

90%

| Hits this $\rightarrow$ Kills largest query. Per-Query Limit |

query_mem_limit

(Session) |

(unlimited) | Hits this $\rightarrow$ Kills that specific query. Circuit Breaker |

big_query_mem_limit

(RG) |

(unlimited) | Hits this $\rightarrow$ Kills that specific query. Queue Trigger |

query_queue_mem_used_pct_limit

| Hits this $\rightarrow$ Incoming queries wait in queue. Recommendation for your workload: Since you are running millions of queries, you should enable the query queue (

SET GLOBAL enable_query_queue_select = true;

) and set

query_queue_mem_used_pct_limit = 0.8

. This ensures that if a few "massive" queries start eating all the RAM, your millions of smaller queries will simply wait for a few seconds rather than being killed. References • sql-reference/System_variable.md • administration/management/resource_management/query_queues.md • administration/management/monitor_manage_big_queries.md • administration/management/resource_management/Memory_management.md

ansh.gupta2

09/16/2026, 7:09 AM

@Rocky how incremental stats collection work in starrocks for external catalog, it on query trigger for once but after that does it detect changes automatic in table and do incremental changes in stats collection without need to run query again to check? since i am using delta catalog dml/ddl operation cannot be done in starrocks they are done by spark or some other source , how does it detect changes?

Rocky

09/16/2026, 7:10 AM

In StarRocks, incremental statistics for external catalogs (like Delta Lake) work differently than for internal tables. Since you are using a Delta Lake catalog where data is modified by external engines (Spark, etc.), here is how StarRocks detects those changes and manages stats. 1. Detection Mechanism: The Delta Transaction Log StarRocks does not scan every file to find changes. Instead, it leverages the Delta Lake Transaction Log (
_delta_log
). * Version Checking: StarRocks tracks the latest Snapshot Version of the Delta table it has seen. When a check is triggered, StarRocks simply asks the storage/metastore for the latest version number. * Change Detection: If the version number in the

_delta_log

has increased, StarRocks knows a DML/DDL operation occurred (even if done by Spark) and marks the current statistics as "stale." 2. Is it "Automatic" (Query vs. Background)? By default, StarRocks provides two ways to collect these stats without you manually running

ANALYZE

every time: A. Query-Triggered Collection (Default since v3.4.0) This is the most common behavior. * How it works: When you run a query against a Delta table, the FE (Frontend) checks the last time stats were collected. * Threshold: If the table is "small" (<10M rows) and 2 hours have passed, or "large" and 12 hours have passed, StarRocks automatically adds an

ANALYZE

task to a background queue. * Advantage: You don't have to manage anything. * Limitation: It requires at least one query to "trigger" the check. If no one queries the table for a week, stats won't update. B. Automatic Background Collection (
CREATE ANALYZE
) If you want StarRocks to update stats without any query being run, you must explicitly create an Analyze Job once. * Syntax:

CREATE ANALYZE TABLE <delta_table_name>;

* Behavior: Once created, StarRocks starts a background thread that polls the Delta Lake metadata every 5 minutes (

statistic_collect_interval_sec

). * Action: If it detects a version change in the Delta log, it runs the collection task automatically. 3. The "Incremental" Nuance for Delta Lake It is important to distinguish between Detection and Collection: * Incremental Detection: Both methods above are incremental in detecting changes (they only check the version number). * Full Collection: Unlike Hive or Iceberg (where StarRocks can update stats only for changed partitions), for Delta Lake, StarRocks currently collects statistics for the entire table once a change is detected. It does not yet support updating only the "delta" of the statistics for Delta Lake tables. 4. Metadata Refresh vs. Stats Collection Since you mentioned Spark updates, ensure your Metadata Cache is also fresh. Statistics rely on the FE knowing the table structure. * Property:

enable_background_refresh_connector_metadata = true

(default). * Effect: The FE polls Azure Blob/Metastore for Delta log changes every 10 minutes. This ensures that even if you don't run

ANALYZE

, StarRocks knows about the new files Spark created. Summary for your Delta Lake use case: 1. If you want it fully hands-off: Do nothing. The

query_trigger_analyze

(v3.4+) will handle it when queries arrive. 2. If you want stats ready before the first query: Run

CREATE ANALYZE TABLE your_delta_table;

once. It will then monitor the Delta version in the background and update stats whenever Spark makes a change. 3. To see if it's working: Run

SHOW ANALYZE STATUS;

to see the history of background tasks triggered by the system. Key Configuration Variables Variable | Default | Purpose

enable_query_trigger_analyze

true

| Triggers stats update when a query hits a "stale" table.

statistic_collect_interval_sec

| How often the background thread checks for metadata changes.

enable_background_refresh_connector_metadata

true

| Periodically refreshes the Delta log version in the FE cache. References

Rocky

09/16/2026, 7:10 AM

• using_starrocks/Cost_based_optimizer.md • data_source/catalog/deltalake_catalog.md

ansh.gupta2

09/16/2026, 7:13 AM

@Rocky after one time collection stats , for delta it does not automatically collect stats

Rocky

09/16/2026, 7:13 AM

For Delta Lake (and other external catalogs), StarRocks handles statistics differently than for internal tables. The behavior you're seeing—where stats don't update after a one-time run—is likely because of how external table background tasks are configured. 1. Manual vs. Automatic Jobs If you previously ran

ANALYZE TABLE <table_name>

, that is a one-time manual task. It will not run again when data changes. To enable automatic background collection for an external table, you must explicitly create a recurring job using the

CREATE ANALYZE

statement:

Copy code

sql
-- This creates a persistent background job for the specific Delta table
CREATE ANALYZE TABLE <catalog_name>.<db_name>.<table_name>;

Important Notes: * External Tables Only: Unlike internal StarRocks tables (which have automatic collection enabled by default), external catalog tables require you to manually create this "Analyze Job" once. * Specific Tables: You cannot currently run

CREATE ANALYZE DATABASE

ALL

for external catalogs; you must create the job for each table individually. 2. How it detects changes (v3.3+) Once you have created the

CREATE ANALYZE

job: * The Check: StarRocks checks for updates every 5 minutes (governed by

statistic_collect_interval_sec

). * Delta Detection: For Delta Lake, StarRocks checks the Snapshot Version in the Delta Transaction Log (

_delta_log

). If the version has increased (meaning Spark or another tool performed a DML/DDL), StarRocks marks the stats as stale. * Full Collection: Because Delta Lake's data structure is handled as a whole by the FE, StarRocks currently performs a full collection of the entire table whenever it detects a change, rather than just the new files. 3. Query-Triggered Collection (v3.4.0+) If you are on StarRocks v3.4.0 or later, there is an additional feature that triggers collection even without a

CREATE ANALYZE

job. * Trigger: When a query hits a table, the system checks if the stats are "stale" or "expired." * Configuration: * `enable_query_trigger_analyze`: Must be

true

(default). * `connector_table_query_trigger_analyze_small_table_interval`: Default is 2 hours. * `connector_table_query_trigger_analyze_large_table_interval`: Default is 12 hours. * The "Gap": If you recently changed data and ran a query, it might not trigger immediately if the "interval" hasn't passed since the last collection. 4. Dependency: Metadata Refresh For the Stats Collector to "see" that Spark changed the Delta table, the FE's metadata cache must be updated first. * Ensure enable_background_refresh_connector_metadata
is

true

(default). * By default, the FE refreshes its view of external metadata every 10 minutes. If the FE doesn't know the table version has changed, the Analyze job won't trigger. Recommended Steps 1. Check existing jobs: Run

SHOW ANALYZE JOB;

to see if there is a persistent job for your Delta table. If you only see a "Manual" task in the history, that's why it stopped. 2. Create the persistent job:

Copy code

sql
    CREATE ANALYZE TABLE delta_catalog.your_db.your_table;

3. Monitor progress: Use

SHOW ANALYZE STATUS;

to see when the last background collection occurred and why it was triggered. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md • data_source/catalog/deltalake_catalog.md • administration/management/resource_management/Query_management.md • administration/management/FE_configuration.md

Rocky

09/16/2026, 7:13 AM

• sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md References • using_starrocks/Cost_based_optimizer.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md

ansh.gupta2

09/16/2026, 7:15 AM

@Rocky so delta does not support incremental stats collection it will do the entire table , also for that we have create analyze persistant job

Rocky

09/16/2026, 7:15 AM

Yes, you are correct. For Delta Lake catalogs, StarRocks' statistics collection behavior follows these rules: 1. No Incremental Collection (Full Table Only) Unlike Hive or Iceberg—where StarRocks can identify specific changed partitions and only update those—*Delta Lake only supports full table statistics collection.* * Even if only one row is added by Spark, StarRocks will re-scan the entire table (or the specified columns) to refresh the statistics. * Why? The internal logic for Delta Lake currently treats the table as a single unit for statistics purposes. 2. The Persistent Job (
CREATE ANALYZE
) For internal StarRocks tables, statistics are collected automatically in the background by default. For Delta Lake (and other external catalogs), this is not the case: * Manual Trigger:

ANALYZE TABLE <table_name>

runs once and then stops. * Automatic/Persistent Job: You must run

CREATE ANALYZE TABLE <table_name>;

once. This creates a stateful job in the FE. * Behavior: Once the job is created, StarRocks will periodically check the Delta Lake transaction log. If it detects a version change (even from Spark), it will automatically trigger a new full collection task. 3. Change Detection Logic StarRocks detects changes incrementally, even if the collection is full: * Version Polling: StarRocks polls the Delta Lake

_delta_log

every 5 minutes (controlled by

statistic_collect_interval_sec

). * Trigger: If the Delta version has increased since the last scan, the persistent

CREATE ANALYZE

job is moved to the "Pending" queue for execution. 4. Query-Triggered Collection (v3.4+) If you are on version 3.4.0 or later, there is a "fail-safe" called Query-Triggered Collection: _ If you _haven't* created a persistent job, StarRocks will still check for stale stats when you run a query. * If the stats are older than the threshold (e.g., 2 hours for small tables), it will trigger a background collection. * Crucial Note: For Delta Lake, this query-triggered task also collects the entire table, not just changes. Summary Table for Delta Lake Stats Feature | Behavior for Delta Lake Incremental Detection | Supported (Checks version in

_delta_log

). Incremental Collection | Not Supported (Always does a full table scan). Persistent Job Required? | Yes, run

CREATE ANALYZE TABLE

once for auto-updates. Metadata Refresh | Requires

enable_background_refresh_connector_metadata = true

to see Spark's changes. Recommendation: If your Delta tables are very large, full scans can be expensive. To minimize impact, you can limit the columns collected in your persistent job:

Copy code

sql
-- Only collect stats for columns used in JOINs or WHERE clauses
CREATE ANALYZE TABLE deltalake_catalog.db.large_table (col1, col2, col3);

References • using_starrocks/Cost_based_optimizer.md • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md

ansh.gupta2

09/16/2026, 7:21 AM

@Rocky what is histogram collection? can it give more impact?

Rocky

09/16/2026, 7:21 AM

Histogram collection is a more advanced form of statistics that provides a detailed "map" of how data is distributed across a column, rather than just knowing the simple Min, Max, and total number of distinct values (NDV). In StarRocks, histograms are equi-height histograms. This means the data is divided into multiple "buckets," where each bucket contains roughly the same number of rows. 1. What impact does it have? Standard statistics (Min/Max/NDV) assume that your data is uniformly distributed. If you have 1 million rows and 100 distinct values, the optimizer assumes every value appears exactly 10,000 times. Histogram collection provides impact in the following scenarios: * Data Skew: If one value (e.g., "USA") appears in 90% of your rows while other countries appear in 0.1%, standard stats will guess wrong. A histogram tells the optimizer exactly how "heavy" the "USA" value is. * Better Join Ordering: If the optimizer knows a filter on a skewed column will actually filter out 90% of the data (instead of a guessed 10%), it might choose to perform that Join earlier or use a different Join type (like a Broadcast Join instead of a Shuffle Join). * Range Predicates: For queries like

WHERE price BETWEEN 10 AND 50

, histograms help the optimizer estimate how many rows fall into that specific sub-range much more accurately than basic Min/Max. 2. Is it supported for Delta Lake? Yes. Since StarRocks v3.3.0, you can manually collect histograms for Delta Lake tables. * Manual Trigger: Unlike basic statistics, histograms are not collected automatically by the background thread. You must trigger them manually. * Full Scan: For Delta Lake, just like basic stats, a histogram collection will require a scan of the entire table (or the specified columns). 3. How to run it You specify which columns need a histogram. You should only do this for columns that are skewed and frequently used in WHERE or JOIN clauses.

Copy code

sql
-- Collect histogram for the 'user_id' and 'region' columns
ANALYZE TABLE delta_catalog.your_db.your_table 
UPDATE HISTOGRAM ON user_id, region 
WITH 64 BUCKETS;

4. Should you use it? Use it if: * You have a large Delta table where some data values are much more common than others. * The optimizer is currently choosing a "bad" query plan (e.g., a massive shuffle join when it should have been a small broadcast). Avoid it if: * Your data is evenly distributed (uniform). * The table is very small (basic stats are enough). * You are sensitive to the time it takes to run

ANALYZE

, as histograms are more resource-intensive to build than basic statistics. Summary While basic statistics give the optimizer the "boundaries" of your data, histograms give it the "shape." For skewed Delta Lake datasets, this often leads to much faster query execution because the optimizer can finally "see" the clusters of data. References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/16/2026, 7:27 AM

@Rocky so for delta it will do full scan , that mean query will for old stats until new stats not completed? how much performance difference we can expect not much if changes are little but if massive change is done then its impacted?

Rocky

09/16/2026, 7:27 AM

Yes, you have identified the trade-offs correctly. Here is how StarRocks handles queries and performance during and after those Delta Lake statistics refreshes: 1. Does the query wait for new stats? No, queries will not wait. * Asynchronous: When an automatic or query-triggered

ANALYZE

starts, it runs as a background task. * Old Stats are Used: Until the new scan is 100% complete and the results are written to the internal statistics table, the CBO (Cost-Based Optimizer) will continue to use the old, cached statistics. * Atomicity: Only once the new stats are fully ready does StarRocks "swap" them in and invalidate the old cache. This ensures queries never fail or hang just because a refresh is happening. 2. Performance Impact: Small vs. Massive Changes The impact of "stale" stats depends entirely on how much the distribution of your data has changed: * Small Changes (Low Impact): If you add 5% more rows and the data distribution (min/max, common values) stays the same, the old stats are still a very good "proxy." The Optimizer will likely pick the same (correct) plan, and you won't notice a performance difference. * Massive Changes (High Impact): If you perform a massive insert or overwrite that doubles the table size or introduces a new "heavy" value (data skew), the old stats become dangerously wrong. * The Risk: The Optimizer might think a table has 1 million rows (based on old stats) when it actually has 100 million. It might choose a Broadcast Join (thinking the table is small enough to fit in memory on all nodes), which could lead to an Out of Memory (OOM) error or extreme slowness as the nodes struggle to broadcast 100 million rows. * Plan Instability: You might see a query that used to take 10 seconds suddenly take 10 minutes because the Join order is now completely backwards. 3. How to mitigate the "Full Scan" cost Since Delta Lake requires a full scan for stats, you can manage the impact like this: * Limit Columns: Only run

CREATE ANALYZE

on columns actually used in

JOIN

WHERE

, or

GROUP BY

. Don't collect stats for "description" or "comment" columns. * Schedule/Interval: You can increase the

connector_table_query_trigger_analyze_large_table_interval

(default 12 hours) if you want to avoid frequent full scans on massive tables. * Manual Control: If you know you just finished a massive Spark job, you can manually trigger

ANALYZE TABLE <name>

immediately to ensure the next query gets the right plan. Summary If your Delta Lake data changes massively, the query performance will likely degrade (due to a bad plan) until that full-scan background collection finishes. If changes are incremental, you likely won't notice any difference during the refresh window. References • using_starrocks/Cost_based_optimizer.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/deltalake_catalog.md • using_starrocks/Cost_based_optimizer.md • loading/loading_introduction/troubleshooting_loading.md

ansh.gupta2

09/17/2026, 2:48 AM

@Rocky Old Stats are Used but does starrocks will know its using old stats? it will take some precautionary?

Rocky

09/17/2026, 2:48 AM

StarRocks does have mechanisms to "know" its statistics are old, but its primary "precautionary" action is to trigger a background update rather than changing how the Optimizer interprets the current (old) data. Here is how it works under the hood: 1. How StarRocks knows stats are old For Delta Lake, StarRocks uses two detection layers: * Version Detection: StarRocks polls the Delta Lake transaction log (

_delta_log

) every 5 minutes (via

statistic_collect_interval_sec

). If the Delta table version has increased, it marks the statistics as "unhealthy" or "stale." * Time-Based Check (v3.4+): Even if the version hasn't changed, the Query-Triggered Analyze feature checks if the existing stats are older than a specific interval (default 12 hours for large tables). If they are, it flags them as needing an update. 2. Does the Optimizer take "precautionary" measures? By default, the Optimizer does NOT apply a safety factor (like assuming more rows or more skew) just because stats are old. * Trusts Existing Stats: As long as stats exist in the

_statistics_.external_column_statistics

table, the CBO will use them at face value, even if they are from a version of the table that was 50% smaller. * The "Precaution" is Background Refresh: Instead of changing the plan logic, StarRocks immediately adds a new

ANALYZE

task to the

PendingTaskQueue

. The goal is to fix the stats for the next query, rather than second-guessing the current stats for the current query. 3. What you can do as a "Precaution" If you know you just performed a massive data change and you don't want the Optimizer to use "dangerous" old stats, you have two manual options: * Force Heuristics (Safer for massive changes): If the stats are so old that they are causing bad plans (e.g., trying to Broadcast a table that is now huge), you can manually drop them:

Copy code

sql
    DROP STATS <table_name>;

When no stats exist, the Optimizer falls back to Heuristics (default guesses based on file sizes). Heuristics are often "safer" than extremely wrong statistics. * Manual Sync Analyze: You can force the query to wait for new stats by using the

WITH SYNC

property:

Copy code

sql
    ANALYZE TABLE <table_name> WITH SYNC;

This will block your session until the full scan of the Delta table is complete, ensuring your subsequent queries use 100% accurate data. Summary of "Stale" Behavior Feature | StarRocks Action Detection | Yes (checks Delta Version & timestamp). Current Query | Uses old stats (no automatic "safety" buffer). Background Action | Triggers an asynchronous Full Table Scan to refresh stats. Next Query | Uses new stats once the background scan completes. Pro-tip: If you notice query plans becoming unstable after large Delta Lake updates, check

SHOW ANALYZE STATUS

to see if a background refresh is already running. References • using_starrocks/Cost_based_optimizer.md • data_source/catalog/deltalake_catalog.md • administration/management/FE_configuration.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/17/2026, 9:19 AM

@Rocky if i scale up CN nodes , can currently running queries go into new nodes or only new queries will go?

Rocky

09/17/2026, 9:19 AM

When you scale up CN (Compute Nodes) or BE (Backend) nodes in StarRocks, the behavior for running vs. new queries is as follows: 1. Currently Running Queries Currently running queries will NOT move to the new nodes. * Fixed Plan: When a query is submitted, the FE (Frontend) creates an execution plan and assigns specific fragments of that query to the nodes available at that exact moment. * No Mid-query Migration: StarRocks does not support "live migration" of query fragments. Once a node starts processing a part of a query, it must finish it. The query cannot "spill over" or redistribute its work to a newly added node while it is already in progress. 2. New Queries Only new queries will utilize the new nodes. * Immediate Availability: As soon as the FE registers the new CN nodes (which you can verify with

SHOW PROC '/compute_nodes'

), the scheduler will include them in the resource pool for any subsequent query. * Load Balancing: The FE will automatically begin distributing fragments of new queries to the new nodes to balance the CPU and memory load across the expanded cluster. 3. Difference between BE and CN Scaling While neither will move a running query, they handle "balance" differently: * CN Scaling: Since CNs are stateless (they don't store data), they start helping with new queries almost instantly. They are ideal for "elastic" scaling to handle sudden spikes in query volume. * BE Scaling: When you add a BE, StarRocks also starts a background Tablet Balancing process. It moves data replicas (tablets) from old nodes to the new node. This is a background data-movement task that ensures that, over time, the new node also handles its share of the I/O and storage load, not just the computation. Summary Table Feature | Running Queries | New Queries Uses New Nodes? | No | Yes Action | Finishes on the original nodes. | FE schedules fragments to the new nodes. Benefit | None (unless it's a multi-stage query where later stages haven't started, but even then, it's rare). | Immediate performance/concurrency boost. Recommendation: If you have a very long-running "big query" and you scale up to speed it up, the scaling will not help that specific query. You would need to

KILL

the query and restart it for it to take advantage of the new nodes. References • administration/management/Scale_up_down.md • administration/management/resource_management/query_queues.md References • administration/management/monitor_manage_big_queries.md • using_starrocks/Colocate_join.md • administration/data_migration_tool.md

ansh.gupta2

09/22/2026, 8:52 AM

@Rocky in the fe audit log does it tell memory of query cluster wide or per node?

Rocky

09/22/2026, 8:52 AM

In the StarRocks FE audit log (

fe.audit.log

), the memory usage metrics—specifically the

memCostBytes

(or

PeakMemoryUsage

in some versions)—represent the cluster-wide total memory usage for that specific query. Key points to keep in mind: * Aggregate Value: This value is the sum of the peak memory consumed by the query across all Backend (BE) or Compute Nodes (CN) involved in the execution. * Per-Node Estimation: If you need to estimate how much memory a query used on a single node, you would typically divide this total value by the number of nodes in your cluster (e.g.,

memCostBytes / number_of_BEs

). * Audit Table Querying: If you have enabled the StarRocks Audit Loader to store these logs in a table, you can easily find your most memory-intensive queries using:

Copy code

sql
    SELECT queryId, memCostBytes / 1024 / 1024 AS mem_mb
    FROM starrocks_audit_db*.starrocks_audit_tbl*
    ORDER BY memCostBytes DESC;

If you require the exact breakdown of memory per node, you would need to look at the Query Profile for that specific

QueryID

(using

get_query_profile

), which provides a detailed breakdown of resource consumption at the fragment and operator level for each node. References • best_practices/audit_log_resource_group.md • faq/operation_maintenance_faq.md • administration/management/logs.md References • administration/management/monitoring/metrics.md • faq/operation_maintenance_faq.md • best_practices/audit_log_resource_group.md

ansh.gupta2

09/22/2026, 8:55 AM

@Rocky ProgrammingError: (1064, 'Memory of process exceed limit. Pipeline Backend: starrocks-shared-data-cn-5.starrocks-shared-data-cn-search.starrocks-dev.svc.cluster.local, fragment: 01a0b3e8-3fe4-7f78-91a2-3ddadf69b2eb Used: 342136350494, Limit: 371085174374. Mem usage has exceed the limit of BE: BE:57482') getting this error for query but when check in fe audit log memory is close to this memory only , this happen because of the data skew ? some most CN read very less and few read a lot ?

Rocky

09/22/2026, 8:55 AM

Yes, your suspicion is correct. This is a very strong indicator of data skew. Here is how to interpret the numbers you are seeing and why they point to skew: 1. Audit Log vs. Error Message * fe.audit.log
(
memCostBytes
): This value is the cluster-wide total memory used by the query across all nodes. * Error Message (
Used
): This refers specifically to the memory consumption on the one node mentioned in the error (

starrocks-shared-data-cn-5

). If the Audit Log total is nearly identical to the "Used" value in the error, it means this single node was responsible for almost 100% of the memory consumption of the entire query. In a balanced query, the total memory (Audit Log) should be significantly higher than the memory used by any single node. 2. Why it hit the "Process" limit The error "Memory of process exceed limit" (Error 1064) means the node reached its global memory safety ceiling (usually 90% of the physical RAM, defined by the

mem_limit

BE/CN configuration). StarRocks fails the query here because the node is at risk of an OOM (Out Of Memory) crash. Because of the skew, this node was likely trying to build a massive Hash Table (for a Join or Aggregation) that was too big for its local RAM, while your other CNs remained idle. 3. How to Confirm and Fix Data Skew To confirm which part of your query is skewed, you can: 1. Check the Query Profile: Run

EXPLAIN ANALYZE <your_query>

or retrieve the profile of the failed query. Look for the

Scan

HashJoin

operators. If you see one

Instance

processing millions of rows while others process only thousands, that is your skew. 2. Verify Distribution Keys: Check the

DISTRIBUTED BY HASH(...)

key of the tables involved. If the join key or distribution key has many "High Cardinality" values (e.g., many NULLs or a single common ID), StarRocks will send all those rows to the same node. 3. Check for "Bucket Skew": Sometimes data is skewed in the underlying storage. You can check the row count per tablet:

Copy code

sql
    SHOW TABLETS FROM <table_name>;

Recommended Actions * Enable Spill to Disk: To prevent the query from failing when memory is tight, you can allow StarRocks to spill intermediate data to disk (available in v3.0+):

Copy code

sql
    SET enable_spill = true;

* Tune Distribution: If a specific value is causing the skew, consider changing the distribution key to a more balanced column. * Increase `query_mem_limit`: If the node actually has enough RAM but you've set a strict per-query limit, you can increase it (though the error you got is a process limit, meaning the node itself is actually full). References • administration/management/BE_configuration.md • administration/management/monitor_manage_big_queries.md • administration/management/resource_management/query_queues.md References • administration/management/BE_configuration.md • administration/management/logs.md • faq/operation_maintenance_faq.md References • administration/management/logs.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md

ansh.gupta2

09/22/2026, 9:44 AM

@Rocky get chunk from scanner Backend: starrocks-shared-data-cn-1.starrocks-shared-data-cn-search.starrocks-dev.svc.cluster.local, fragment: 01a0b3ee-db6e-71e0-9107-17799a55daa5 Used: 371085981064, Limit: 371085174374. Mem usage has exceed the limit of BE: file = abfs://silver-immutable@mdpingestionstream.dfs.core.windows.net/prod-data/stream_mdp_airbus_sz/stream_mdp_airbus_sz_dc1_patron_hyperlocal_warehouse_analytics_event_optimised/part_eventtimestamp_date=2026 1 | ProgrammingError: (1064, 'Memory of process exceed limit. Pipeline Backend: starrocks-shared-data-cn-29.starrocks-shared-data-cn-search.starrocks-dev.svc.cluster.local, fragment: 01a0b3ec-cdf0-7a2b-bcba-b7fd84f3aa66 Used: 371088392048, Limit: 371085174374. Mem usage has exceed the limit of BE: BE:57466') 1 | ProgrammingError: (1064, 'Memory of process exceed limit. try consume:163840 Backend: starrocks-shared-data-cn-3.starrocks-shared-data-cn-search.starrocks-dev.svc.cluster.local, Used: 355908380423, Limit: 371085174374. Mem usage has exceed the limit of BE: BE:57488') what these issue meant

Rocky

09/22/2026, 9:45 AM

These errors indicate that your Compute Nodes (CNs) have completely run out of memory (OOM). Unlike a "Single Query Limit" error, which only stops one query for using too much RAM, a "Memory of process exceed limit" error means the entire StarRocks process on that node has hit its safety ceiling (the

mem_limit

BE/CN configuration). Since this is happening across multiple nodes (

cn-1

cn-29

cn-3

), here is a breakdown of what this means and how to address it: 1. What the error details tell us * Process OOM: Your nodes are configured with a limit of ~345 GB (

371085174374

bytes). The "Used" value is slightly higher than this limit, which triggered the failure to prevent a Linux OOM killer from crashing the whole service. * Scanner-specific: The message

get chunk from scanner

and the file path

abfs://...

(Azure Blob Storage) indicate that the memory is being exhausted during the data reading phase. * Massive Scan: Because multiple nodes are hitting the limit simultaneously, it is likely that your query is scanning an extremely large amount of data (e.g., the

partition) from Azure, and the overhead of buffering, decoding Parquet/ORC files, and managing scanner threads is filling up the nodes. 2. Common Causes for External Scan OOM * High Scanner Concurrency: If you have many concurrent queries or one query with a very high degree of parallelism, the "Scanner Threads" will each reserve memory for buffers. * Large Parquet/ORC Row Groups: If the files in your Azure storage have very large row groups or a high number of columns, the scanner must allocate significant memory to decode them. * Data Fetching Buffers: Reading from remote storage (ABFS/S3) requires memory to buffer chunks before they are processed. 3. Recommended Troubleshooting Steps A. Check "Scan" Memory in Query Profile Retrieve the profile of the failed query and look for the ConnectorScanNode
. Check: * `PeakMemoryUsage`: How much memory the scanner itself used. * `NumProduceChunkTasks`: If this number is very high, it means many scanner threads were active. B. Tune Scanner Configuration (BE/CN parameters) You can limit the memory pressure from external scans by adjusting these parameters in your

cn.conf

(or via

ADMIN SET FRONTEND CONFIG

if they are dynamic): * max_hdfs_scanner_num
: (Default 50) This limits the number of concurrent remote scanners per node. Try reducing this to

to reduce memory spikes. * scanner_thread_pool_queue_size
: Reducing this can limit the number of pending scan tasks in the queue. C. Session Variables for the Query If this is a specific heavy query, try running it with reduced parallelism to lower the memory footprint:

Copy code

sql
SET pipeline_dop = 8; -- Reduce degree of parallelism (default is often 0, which means all cores)
SET connector_scan_use_query_mem_limit = true; -- Force the scanner to respect query-level limits

D. Enable Spilling While spilling primarily helps with Joins and Aggregations, it can prevent a node from crashing when memory is tight:

Copy code

sql
SET enable_spill = true;

E. Verify Node Resource Sizing You have ~345GB allocated to the StarRocks process. If your physical machine has 384GB or 512GB of RAM, this is a reasonable limit. However, if the query is truly scanning "2026" partition data that is massive, you may simply need more CN nodes or to filter the query more strictly (e.g., adding a

LIMIT

or more selective

WHERE

clauses). Summary Your cluster is not skewed; it is overwhelmed. The scanner is pulling data from Azure faster than the node can process it, or it's pulling so much metadata/buffer data that it's filling the 345GB process limit. Start by reducing

max_hdfs_scanner_num

and checking the

pipeline_dop

of your query. References • administration/management/BE_configuration.md

Rocky

09/22/2026, 9:45 AM

• faq/Sql_faq.md • administration/management/monitor_manage_big_queries.md References • administration/management/BE_configuration.md • faq/Sql_faq.md • administration/management/FE_configuration.md References • administration/management/BE_configuration.md • data_source/datalake_faq.md • unloading/Export.md • data_source/External_table.md • unloading/Flink_connector.md

ansh.gupta2

09/29/2026, 10:10 AM

@Rocky how to stats collection speed also , does it thread allocated can change runtime without CN restart?

Rocky

09/29/2026, 10:11 AM

To monitor the speed and status of statistics collection in StarRocks, and to tune performance without restarting nodes, you can use the following methods: 1. Monitoring Statistics Collection Speed There are three main ways to monitor the progress and performance of statistics (CBO) collection: * SHOW ANALYZE STATUS
: This is the primary command to check the progress. It provides the

StartTime

and

EndTime

, allowing you to calculate the duration (speed) of the task. It also shows the

Status

(PENDING, RUNNING, SUCCESS, FAILED).

Copy code

sql
    SHOW ANALYZE STATUS WHERE `table` = 'your_table_name';

* Enable Statistics Query Profiles: From v3.1.5+, you can see the actual execution profile of the background "analyze" queries (which are just internal SQL scans). By setting this FE config, you can find the Query IDs of stats tasks in the FE audit log and view their profiles using

get_query_profile

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("enable_statistics_collect_profile" = "true");

* Query Metadata: You can check the health and last update time of statistics for specific columns:

Copy code

sql
    SHOW STATS META WHERE `table` = 'your_table_name';

2. Tuning Speed at Runtime (No Restart Required) You can change the parallelism and behavior of statistics collection at runtime without restarting FE or CN/BE nodes. * Adjust Parallelism (BE Threads): Use the session variable

statistic_collect_parallel

to increase the number of threads used by a BE/CN to process a statistics collection task. Increasing this value speeds up the scanning of large tables.

Copy code

sql
    SET GLOBAL statistic_collect_parallel = 4; -- Default is 1

* Modify Global FE Configurations: Almost all statistics-related configurations in the FE can be changed using

ADMIN SET FRONTEND CONFIG

. These take effect immediately: * statistic_max_full_collect_data_size
: Threshold to switch from FULL to SAMPLE collection (Default 100GB). * statistic_collect_interval_sec
: How often the system checks for data updates to trigger auto-collection (Default 600s). * connector_table_query_trigger_analyze_max_running_task_num
: (v3.4+) Limits the number of concurrent query-triggered analyze tasks. Summary of Runtime Mutability Action | Command | Requires Restart? Increase Scan Threads |

SET GLOBAL statistic_collect_parallel = N

| No Change Sync/Async Mode |

ANALYZE TABLE ... WITH SYNC/ASYNC MODE

| No Tune FE Configs |

ADMIN SET FRONTEND CONFIG ("key" = "val")

| No If you are seeing slow collection on very large tables, the most effective runtime change is usually increasing

statistic_collect_parallel

or decreasing the

statistic_max_full_collect_data_size

to force Sampled Collection instead of Full Collection. References • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md

Rocky

09/29/2026, 10:11 AM

• sql-reference/information_schema/analyze_status.md References • using_starrocks/Cost_based_optimizer.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md

ansh.gupta2

09/29/2026, 10:17 AM

@Rocky how to speed up stats collection , please consider only external delta and hive table , than give the complete answer

Rocky

09/29/2026, 10:17 AM

To speed up statistics collection for external tables (Hive and Delta Lake) without restarting your cluster, follow these strategies. Since external data lives in remote storage (S3/ABFS/HDFS), the bottleneck is usually the I/O of scanning files. 1. Use Sampled Collection (Most Effective) For massive external tables, collecting statistics on 100% of the data (Full Collection) is extremely slow and resource-intensive. * Manual Sample: Specify a sample percentage or row count.

Copy code

sql
    ANALYZE EXTERNAL TABLE <table_name> WITH SAMPLE PERCENT 10;
    -- OR
    ANALYZE EXTERNAL TABLE <table_name> WITH SAMPLE ROWS 1000000;

* Automatic Threshold: Adjust the FE configuration to trigger sampling automatically on smaller tables (default is 100GB).

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("statistic_max_full_collect_data_size" = "10737418240"); -- 10GB

2. Increase Collection Parallelism (Threads) You can change thread allocation at runtime without a restart. There are two levels of parallelism: * Task Concurrency (FE level): How many separate tables or partitions are analyzed simultaneously.

Copy code

sql
    -- Change at runtime via FE config
    ADMIN SET FRONTEND CONFIG ("statistic_concurrency" = "5"); -- Increase from default 3

* Scan Parallelism (BE/CN level): How many threads each Compute Node uses to scan the data for a single analyze task.

Copy code

sql
    -- Change at runtime via Global Session Variable
    SET GLOBAL statistic_collect_parallel = 8; -- Increase from default 1

Note: Increasing this will speed up the scan but consumes more memory on your CNs. Given your previous OOM errors, increase this cautiously. 3. Target Specific Partitions If you are only adding new data to specific partitions (e.g.,

date=2026-01-01

), don't re-analyze the whole table.

Copy code

sql
ANALYZE EXTERNAL TABLE <table_name> PARTITIONS (partition_name);

4. Enable External Metadata Caching Stats collection for Hive/Delta requires listing files and fetching metadata from the Metastore. Caching this speeds up the start of the collection process.

Copy code

sql
-- FE Configs (Runtime)
ADMIN SET FRONTEND CONFIG ("enable_metastore_cache" = "true");

5. Tune Query-Triggered Analyze (For v3.4+) If you want StarRocks to automatically collect stats when it detects a query on an external table that lacks them:

Copy code

sql
ADMIN SET FRONTEND CONFIG ("connector_table_query_trigger_analyze_max_running_task_num" = "4");

6. Adjust Memory for Analyze Tasks If your analyze tasks are failing with the OOM error you saw earlier, it’s because the internal scan for statistics is hitting the memory limit. You can force the statistics collection query to use less memory:

Copy code

sql
-- This ensures the scanner for stats collection respects the memory limits
SET GLOBAL statistic_collect_query_timeout = 3600; -- Allow more time if you reduce parallelism

Summary of Runtime Changes Parameter | Command | Effect Task Concurrency |

ADMIN SET FRONTEND CONFIG ("statistic_concurrency" = "N")

| Analyzes more tables at once. Scan Threads |

SET GLOBAL statistic_collect_parallel = N

| Reads one table faster. Sampling Size |

ADMIN SET FRONTEND CONFIG ("statistic_max_full_collect_data_size" = "Bytes")

| Forces sampling on smaller files. Recommendation for your OOM issue: Use

WITH SAMPLE PERCENT 5

and keep

statistic_collect_parallel

low (e.g., 2 or 4). This speeds up the process by reading less data, rather than trying to read it faster with more threads, which would risk another "Memory of process exceed limit" again. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_EXTERNAL_TABLE.md

Rocky

09/29/2026, 10:17 AM

• administration/management/FE_configuration.md • data_source/datalake_faq.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • administration/management/BE_configuration.md • administration/management/resource_management/query_queues.md

ansh.gupta2

09/29/2026, 10:36 AM

@Rocky how many FE required for test cluster?

Rocky

09/29/2026, 10:36 AM

For a test cluster, you only need one (1) FE node. Here is the breakdown of FE node requirements based on the environment: 1. Test / Development Environment * Required: 1 FE * Role: This node will act as the Leader. * Note: Since there is only one node, there is no high availability. If the FE process stops, the cluster will be unavailable until it is restarted. * Minimum Resources: While production recommends 8 vCPU / 16GB RAM, for a lightweight test environment (e.g., using Helm/Docker), you can go as low as 1 vCPU and 2-4GB RAM. 2. Production / High Availability (HA) Environment * Required: 3 FEs * Role: These should be Follower nodes. * Reason: StarRocks uses the Raft protocol for metadata consensus. A quorum (more than half) must be alive for the cluster to function. * With 3 nodes, you can tolerate 1 failure. * With 2 nodes, you have no fault tolerance (if 1 fails, the remaining 1 cannot form a quorum). Summary Recommendation * Functional Testing: 1 FE + 1 BE/CN. * Performance Testing: 1 FE (to save resources) + multiple BEs/CNs (to test scaling). * Production: 3 FEs (Followers) + 3+ BEs. Tip: If you need to handle very high query concurrency in your test but don't care about metadata redundancy, you can add Observer FE nodes. They help scale query parsing without participating in the Raft voting process. References • deployment/plan_cluster.md • faq/Deploy_faq.md • quick_start/helm.md • faq/Others.md References • deployment/plan_cluster.md • faq/Deploy_faq.md • deployment/deploy_manually.md • faq/Others.md • administration/management/FE_configuration.md • quick_start/helm.md

ansh.gupta2

09/29/2026, 10:38 AM

@Rocky for using starrocks only as reading, number of FE just work as high avaiabilty right no point of leader and follower?

Rocky

09/29/2026, 10:38 AM

Even for a read-only StarRocks cluster, the distinction between Leader and Follower/Observer roles remains important. While High Availability (HA) is the most obvious reason for multiple FEs, it is not the only "point." Here is why roles matter even when you aren't writing data: 1. The Leader is Essential for Metadata "Writes" Even if you are not performing

INSERT

LOAD

operations, the Leader FE still handles internal "metadata writes" that are required for the cluster to function: * External Catalog Synchronization: If you are reading from Hive or Delta Lake, the Leader FE is responsible for synchronizing metadata from the remote metastore, managing the cache, and detecting schema changes. * Statistics Collection: Background statistics collection (Analyze) tasks are scheduled and coordinated by the Leader FE. * Cluster Management: The Leader monitors the health of BEs/CNs. If a node goes down, the Leader handles the re-routing and metadata updates to exclude that node from query plans. * Transaction IDs: Every query (even a read) relies on the metadata state. The Leader ensures all nodes are on the same "version" of metadata. 2. High Availability (HA) If you only have one FE and it fails, your entire cluster (including reading) stops. * In a 3-FE setup (1 Leader, 2 Followers), if the Leader fails, the two Followers elect a new Leader immediately. * Note: If you lose the "Quorum" (e.g., 2 out of 3 FEs fail), the remaining Follower will stop serving read queries by default to prevent reading stale/inconsistent data, unless you set

ignore_meta_check = true

fe.conf

. 3. Horizontal Scaling of Queries (Planning & Parsing) If your cluster has very high query concurrency (thousands of queries per second), a single FE can become a bottleneck during the SQL Parsing and Planning phase. * Multiple FEs (Followers and Observers) allow you to distribute the connection and planning load. * You can put a Load Balancer (like HAProxy or F5) in front of all FEs. The load balancer will send read queries to any available FE (Leader or Follower), spreading the CPU load of query planning across multiple machines. 4. Why use "Observers" for Read-Only Scaling? If your goal is only to scale read performance and you already have 3 Followers for HA: * Don't add more Followers. Adding more Followers increases the overhead of the Raft consensus protocol. * Add Observers. Observers behave exactly like Followers for reading data but do not participate in the Leader election. This allows you to scale to many FEs (for massive read concurrency) without slowing down the metadata synchronization between the Leader and Followers. Summary Recommendation Node Type | Recommended Count | Why? Followers | 3 | Provides HA. One is elected Leader to manage background metadata/node health. Observers | 0 to N | Add these only if your 3 Followers are hitting high CPU/Memory limits due to too many simultaneous query connections. In short: You need a Leader to manage the cluster's internal state (especially for external catalogs), and you need at least 3 Followers to ensure that Leader always exists. References • introduction/Architecture.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • deployment/plan_cluster.md References • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • table_design/table_design.md

Rocky

09/29/2026, 10:38 AM

• introduction/Architecture.md • administration/Meta_recovery.md

ansh.gupta2

09/29/2026, 10:47 AM

@Rocky does FE require in odd number?

Rocky

09/29/2026, 10:47 AM

Yes, for a production cluster, you should use an odd number of Follower FEs (typically 1 or 3). This is because StarRocks uses the Raft protocol (via BDBJE) for metadata consistency, which requires a majority (more than half) of Follower nodes to be alive to function. 1. The "Majority" Rule The cluster can only operate if

(Total Followers / 2) + 1

nodes are online. Total Followers | Majority Needed | Failures Tolerated 1 (Test) | 1 | 0 2 | 2 | 0 (If 1 fails, you lose the majority) 3 | 2 | 1 4 | 3 | 1 (If 2 fail, you only have 2 left, which is not a majority of 4) 5 | 3 | 2 2. Why an odd number is better As shown in the table above, 4 nodes provide the same fault tolerance as 3 nodes (both can only tolerate 1 failure), but 4 nodes require more resources and increase the network overhead for metadata synchronization. Therefore, adding a 4th or 6th Follower adds cost without increasing availability. 3. Followers vs. Observers * Followers: Participate in the "voting" and count toward the majority. You should keep this number odd (1, 3, or 5). * Observers: Do not participate in voting or the majority count. You can have any number of Observers (odd or even). They are used purely to scale out read performance. Summary for your setup: * Test Cluster: Use 1 FE. (Majority of 1 is 1). * Production Cluster: Use 3 FEs (all as Followers). This allows one node to fail without downtime. References • deployment/plan_cluster.md • administration/Meta_recovery.md • introduction/Architecture.md • faq/Deploy_faq.md References • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • deployment/plan_cluster.md • faq/Deploy_faq.md • administration/Meta_recovery.md

ansh.gupta2

09/29/2026, 11:03 AM

@Rocky mysql> SHOW STATS META; Empty set (0.00 sec) why its showing empty after restarting FE and its disk ? statistics store in object storage?

Rocky

09/29/2026, 11:03 AM

The behavior you are seeing—`SHOW STATS META` returning an empty set after a disk/FE restart—usually occurs because the FE has lost its metadata cache or the internal statistics tables are empty. Here is a breakdown of why this happens and where the data is actually stored. 1. Where are statistics stored? StarRocks stores statistics in two places: * Persistent Storage (Internal Tables): The actual statistics data (row counts, NDV, min/max) is stored in a hidden internal database named

_statistics_

. * Shared-Nothing (Standard): These tables are stored on the BE (Backend) local disks. * Shared-Data (Cloud-Native): These tables are stored in Object Storage (S3, OSS, etc.), just like your regular data. * FE Memory/Metadata: To speed up query planning, the FE (Frontend) loads a subset of this information (metadata about which tables have stats) into its memory and its own metadata journal (

meta/

directory). 2. Why is
SHOW STATS META
empty? If you can still see your regular databases and tables, but

SHOW STATS META

is empty, it usually means: * Metadata Loss: If you "restarted the disk" and specifically wiped or lost the FE's

meta/

directory in a single-node cluster, you lost the FE's record of which statistics tasks were performed. * Internal Tables are Empty: If the BEs were also wiped or the

_statistics_

database was lost, the actual stats data is gone. * Leader Transition: If you have multiple FEs, ensure you are connected to the Leader. Follower FEs may sometimes have a slight delay in syncing statistics metadata cache. 3. How to Verify Run these two commands to diagnose the issue: 1. Check the internal tables:

Copy code

sql
    SELECT * FROM _statistics_.column_statistics LIMIT 10;

* If this is empty: Your statistics are physically gone. You must re-run your

ANALYZE

tasks (e.g.,

ANALYZE TABLE <table_name>

). * If this has data: The statistics exist, but the FE's metadata cache is not reflecting them. 2. Check task history:

Copy code

sql
    SHOW ANALYZE STATUS;

This will show you the history of past statistics collection jobs. If this is empty, the FE has no record of any previous analysis. Summary: Do stats store in object storage? * Yes, if you are using a Shared-Data (Compute-Storage separated) cluster, the statistics tables are persisted in your object storage. * No, if you are using Shared-Nothing, they are on the BE disks. Recommendation: Since this is a test cluster and stats are easily recreated, the simplest fix is to trigger a manual collection:

Copy code

sql
ANALYZE TABLE <your_table_name>;

If you want to automate this for the future, ensure your

fe.conf

has

enable_statistic_collect = true

(which is the default). References • sql-reference/sql-statements/cbo_stats/SHOW_META.md • using_starrocks/Cost_based_optimizer.md • administration/Meta_recovery.md • faq/operation_maintenance_faq.md References • using_starrocks/Cost_based_optimizer.md • knowledge/admin_and_operation/shared_data_compaction.md

ansh.gupta2

09/29/2026, 11:04 AM

@Rocky it shared data mode only, stored stats for external table earlier

Rocky

09/29/2026, 11:04 AM

Yes, in Shared-Data mode, your statistics for external tables are indeed persisted in Object Storage (S3/OSS/etc.), but there is a reason why they might appear "empty" after an FE restart. 1. Where they are stored For external tables (Hive, Iceberg, Hudi, Delta Lake), StarRocks stores the collected statistics in a specific internal table:

default_catalog._statistics_.external_column_statistics

In a Shared-Data cluster, this table is treated like any other cloud-native table—its data is stored in your defined Object Storage volume. 2. Why
SHOW STATS META
is empty after a restart The

SHOW STATS META

command reads from the FE's in-memory metadata cache. If you restarted the FE and its disk (implying you might have cleared the FE's

meta/

directory or it's a fresh metadata environment): * The FE "forgot" the stats exist: The FE keeps a record of which tables have been analyzed in its metadata journal. If that journal was lost or if the FE hasn't yet re-synced with the internal statistics tables, it won't show anything in

SHOW STATS META

. * Cache Loading: The FE does not always proactively scan the

_statistics_

tables on startup to populate the cache. It often populates this cache when a query is first run against the table or when a background statistics job runs. 3. How to verify if the data is still there Since you are in Shared-Data mode, the data should still be in S3 even if the FE isn't showing it in the meta-view. You can check the internal table directly:

Copy code

sql
-- Check if the actual statistics data still exists in object storage
SELECT * FROM default_catalog._statistics_.external_column_statistics LIMIT 10;

* If this query returns rows: Your statistics are safe in object storage. Running a query against your external table should eventually trigger the FE to reload these stats into memory, or you can run a manual

ANALYZE TABLE <table_name>

to refresh the FE's metadata state. * If this query is empty: Then the statistics were truly deleted (perhaps the

_statistics_

database was dropped or the object storage path was cleared). 4. Important Note on External Table Stats Starting from v3.4.0, StarRocks supports Query-Triggered Analyze for external tables. This means even if the stats are missing or the cache is empty, the first time you query the Hive/Iceberg table, StarRocks will automatically trigger a background task to collect them and store them back in the object storage. Summary: Your stats are in object storage, but the FE's pointer to them was likely lost during the disk restart. Querying the

_statistics_

table directly will confirm if the data survived. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_META.md • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_META.md • administration/Meta_recovery.md • administration/management/FE_configuration.md

ansh.gupta2

09/29/2026, 11:07 AM

@Rocky mysql> SET CATALOG default_catalog; Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | statistics | | information_schema | | sys | +--------------------+ 3 rows in set (0.00 sec) mysql> USE statistics ; Database changed mysql> SHOW TABLES; Empty set (0.00 sec) mysql>

Rocky

09/29/2026, 11:07 AM

If you are seeing an empty table list in the

_statistics_

database and

SHOW STATS META

is empty after a disk restart, it indicates that your FE metadata (the
meta/
directory) was likely wiped or re-initialized. In StarRocks (even in Shared-Data mode), the FE's local metadata directory is the "brain" that stores the identity of the cluster and the map of all tables. 1. Why
SHOW TABLES
is empty The

_statistics_

database is a system database that is always "present" in the schema. however, the actual tables inside it (like

column_statistics

external_column_statistics

) are internal StarRocks tables. * Metadata Identity: If the FE disk was cleared, the FE started as a "fresh" cluster. It has no record of the tables that existed previously, even if the data files for those tables are still sitting in your Object Storage. * Orphaned Data: In Shared-Data mode, table data in S3/OSS is linked to Table IDs stored in the FE metadata. If the FE metadata is lost, the "map" is gone. The old statistics data in Object Storage is now orphaned because the new FE doesn't know those Table IDs exist. 2. Are statistics stored in Object Storage? Yes, in Shared-Data mode, the data for the statistics tables is persisted in Object Storage. However, without the FE metadata to point to them, they are inaccessible. 3. How to fix this Since you are likely working with external tables (Hive, Iceberg, etc.), the "source of truth" for your actual data is safe in your external catalog. You only lost the cached statistics. To restore functionality: 1. Trigger a new analysis: Run the

ANALYZE

command on one of your external tables.

Copy code

sql
    -- This will force the FE to recreate the internal tables in _statistics_
    ANALYZE TABLE <your_external_catalog>.<your_db>.<your_table>;

2. Verify the tables return: After running the command, check the database again:

Copy code

sql
    USE _statistics_;
    SHOW TABLES;
    -- You should now see 'external_column_statistics' or 'column_statistics'

3. Check stats again:

Copy code

sql
    SHOW STATS META;

Summary & Best Practices * FE Metadata is Critical: In a Shared-Data cluster, you must back up the FE
meta/
directory or use an odd number of FEs (3) to ensure metadata high availability. If you lose the FE metadata and have no backup, you lose the ability to access any internal StarRocks tables (though external catalog definitions can be recreated). * Statistics Re-collection: Since statistics for external tables are just a cache/optimization, re-running

ANALYZE

is the standard way to recover them if the metadata is lost. References • administration/Meta_recovery.md • using_starrocks/Cost_based_optimizer.md • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/29/2026, 12:34 PM

@Rocky In StarRocks' shared-data mode, the

default_catalog

uses the configured storage volume for data persistence. This means that the statistics, which are stored in internal tables within the

_statistics_

database of the

default_catalog

(e.g.,

_statistics_.external_column_statistics

), will reside in the cloud object storage (like S3, GCS, Azure Blob) or HDFS that your shared-data cluster is configured to use.

Rocky

09/29/2026, 12:34 PM

Your understanding is correct. In Shared-Data mode, statistics are indeed persisted in your configured Object Storage (S3, GCS, Azure Blob, or HDFS) because they belong to the

default_catalog

. However, the reason you see an empty set after the restart is likely due to how StarRocks handles the relationship between Metadata and Storage: 1. Metadata vs. Data * Data (Object Storage): The actual statistics files (Parquet/Segments) are safely stored in your S3/Object storage bucket. * Metadata (FE local disk): The "map" that tells StarRocks that these tables exist and which files in S3 belong to them is stored in the FE's local

meta/

directory. 2. Why it is showing empty If "restarting its disk" involved any data loss or if the FE was re-initialized (e.g., a new container with a fresh volume): * The FE has lost the record of those internal tables. * Even though the data still exists in S3, the new FE sees itself as a "fresh" cluster and doesn't know about the old Table IDs or data paths from the previous "life" of the cluster. * The

_statistics_

database you see is a system-reserved namespace, which is why the database exists but the tables inside it are missing. 3. How to recover Since statistics for external tables are essentially a cache of the external metadata, you don't need to manually link the orphaned S3 data. You can simply trigger the collection again: 1. Re-analyze a table:

Copy code

sql
    ANALYZE TABLE <catalog_name>.<db_name>.<table_name>;

2. Verify tables are recreated: After the first

ANALYZE

command, StarRocks will automatically recreate the necessary internal tables in the

_statistics_

database. You should then see them in

SHOW TABLES FROM _statistics_

. Summary While the data resides in Object Storage, it is not self-discovering. If the FE's local metadata (the

meta/

folder) is wiped or not persistent, the cluster loses the "pointer" to that data. In Shared-Data mode, it is critical to ensure the FE's local metadata directory is persistent and backed up. References • using_starrocks/Cost_based_optimizer.md • deployment/shared_data/feature-support-shared-data.md • administration/Meta_recovery.md • faq/shared_data_faq.md References • faq/shared_data_faq.md • administration/management/monitoring/metrics-shared-data.md • quick_start/shared-data.md

ansh.gupta2

09/29/2026, 12:39 PM

@Rocky SET GLOBAL statistic_collect_parallel = 8; can i set it to number of cores in CN node

Rocky

09/29/2026, 12:39 PM

Yes, you can set

statistic_collect_parallel

to the number of cores in your CN node, but it is generally not recommended to set it that high if the node is also serving production queries. What this parameter does

statistic_collect_parallel

(default:

) controls the number of concurrent statistics collection tasks that can run on the Backend (BE) or Compute Node (CN). Since collecting statistics is essentially running a series of specialized queries (scans, counts, and aggregations), increasing this value speeds up the

ANALYZE

process but increases resource consumption. Why setting it to the full core count is risky 1. CPU Contention: If you have 8 cores and set the parallel limit to 8, a large

ANALYZE

job could potentially saturate all CPU cores, causing significant latency spikes for your regular analytical queries. 2. Memory Usage: Each parallel task requires memory for data scanning and processing. If you run 8 tasks simultaneously on a node with limited RAM, you risk hitting the

query_mem_limit

or triggering OOM (Out of Memory) errors on the CN. 3. IO Bottleneck: For Shared-Data mode, multiple parallel collection tasks will pull data from Object Storage (S3/OSS) simultaneously. If the parallelism is too high, you might hit network bandwidth limits or Object Storage request rate limits. Recommended Settings * For Production Environments: Set it to a fraction of your cores, such as 1/4 or 1/2 of the core count. For an 8-core node, a value of 2 or 4 is a good balance. * For Maintenance Windows: If you are running a one-time heavy manual

ANALYZE

and no other queries are running, you can safely set it higher (e.g., to the full core count) to finish the job as quickly as possible. * Dedicated CNs: If you use Resource Groups to isolate statistics collection or have dedicated nodes for background tasks, you can afford a higher parallelism. How to set it You can set this globally for the entire cluster:

Copy code

sql
SET GLOBAL statistic_collect_parallel = 4;

Or just for your current session if you are running a manual `ANALYZE`:

Copy code

sql
SET statistic_collect_parallel = 8;
ANALYZE TABLE your_table;

Recommendation: Start with 4 and monitor the CPU/Memory usage on your CN nodes. If there is plenty of headroom during the collection, you can gradually increase it. References • using_starrocks/Cost_based_optimizer.md • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md

ansh.gupta2

09/29/2026, 1:11 PM

@Rocky mysql> ADMIN SET FRONTEND CONFIG ("statistic_concurrency" = "5"); ERROR 1064 (HY000): Config 'statistic_concurrency' does not exist or is not mutable mysql> SET GLOBAL statistic_concurrency=5; ERROR 1193 (HY000): Unknown system variable 'statistic_concurrency', the most similar variables are {'statistic_collect_parallel', 'wait_timeout', 'statistic_meta_collect_parallel'}

Rocky

09/29/2026, 1:12 PM

The errors you encountered are because statistic_concurrency
is not a valid parameter name in StarRocks. Depending on exactly what you are trying to control, you should use one of the following correct variables: 1. To control Parallelism (Cores per Task) If you want to increase the number of cores used for a single statistics collection task (to make it finish faster), use

statistic_collect_parallel

. This is a system variable, not an FE configuration. * Correct Command:

Copy code

sql
    -- To set it globally for the cluster
    SET GLOBAL statistic_collect_parallel = 8;

    -- Or just for your current session
    SET statistic_collect_parallel = 8;

Note: This is the variable the error message suggested as "most similar." 2. To control Concurrency for External Tables (Query-Triggered) If you are using External Tables (Hive, Iceberg, etc.) and want to limit how many background

ANALYZE

tasks run simultaneously when triggered by queries, use the following FE Configuration: * Correct Command:

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("connector_table_query_trigger_analyze_max_running_task_num" = "5");

(Default is 2. Introduced in v3.4.0) 3. To control Concurrency of the Meta Collector If you are specifically dealing with metadata collection for external catalogs, there is a variable called `statistic_meta_collect_parallel`: * Correct Command:

Copy code

sql
    SET GLOBAL statistic_meta_collect_parallel = 8;

Why your commands failed: 1. ADMIN SET FRONTEND CONFIG
failed because there is no configuration item named

statistic_concurrency

in the

fe.conf

or the internal config map. 2. SET GLOBAL
failed because

statistic_concurrency

is not a recognized MySQL-style system variable in StarRocks. Rocky's Tip: If you want to see all available statistics-related configurations to verify the names, you can run:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE "%statistic%";
-- and
SHOW VARIABLES LIKE "%statistic%";

References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • using_starrocks/Cost_based_optimizer.md • administration/management/BE_configuration.md

ansh.gupta2

09/29/2026, 1:46 PM

@Rocky statistic_analyze_task_pool_size what this for?

Rocky

09/29/2026, 1:46 PM

Based on the official StarRocks documentation and configuration parameters, statistic_analyze_task_pool_size
is not a recognized configuration variable in StarRocks. It is likely you are looking for one of the following parameters that control the thread pools and concurrency for statistics collection: 1.
statistic_cache_thread_pool_size
(FE Config) This is the most similar-sounding parameter. It defines the size of the thread pool used by the Frontend (FE) to refresh the statistics cache. * Default:

* Purpose: When the CBO (Cost-Based Optimizer) needs statistics, they are cached. This pool handles the background refreshing of those cached values. * Note: This is an FE configuration and requires

ADMIN SET FRONTEND CONFIG

(but is generally not mutable at runtime). 2.
statistic_collect_parallel
(Session/Global Variable) If you want to control how many CPU cores are used for a single

ANALYZE

task. * Default:

* Purpose: It adjusts the parallelism of the internal scan and aggregation queries used to gather statistics on the BE/CN nodes. 3.
connector_table_query_trigger_analyze_max_running_task_num
(FE Config) If you are working with External Catalogs (like Hive or Iceberg), this parameter controls the concurrency of background analyze tasks triggered by queries. * Default:

* Purpose: Limits how many automatic "query-triggered" analyze tasks can run at the same time to avoid overwhelming the cluster. 4.
max_task_runs_threads_num
(FE Config) While not specific to statistics, if you use the StarRocks Task Framework (

CREATE TASK

) to schedule manual

ANALYZE

commands, this parameter controls the overall thread pool for all scheduled tasks. * Default:

Summary Table If you are trying to speed up or limit statistics collection, use these instead: Goal | Correct Parameter | Type Parallelism within one task |

statistic_collect_parallel

| System Variable Cache refresh threads |

statistic_cache_thread_pool_size

| FE Config External table auto-analyze concurrency |

connector_table_query_trigger_analyze_max_running_task_num

| FE Config Metadata collection parallelism |

statistic_meta_collect_parallel

| System Variable If you saw

statistic_analyze_task_pool_size

in a specific guide or error message, please double-check the spelling, as it is not a standard StarRocks parameter. References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • administration/management/FE_configuration.md

ansh.gupta2

09/29/2026, 2:00 PM

Rocky

09/29/2026, 2:00 PM

No, the

hive

catalog (which you have configured as a Unified type) is not active by default. When you connect to StarRocks, the session always starts in the default_catalog
(the internal catalog). To use your unified catalog, you have two choices: 1. Switch the session context (Recommended for many queries) If you want to run multiple queries against the unified catalog without typing the catalog name every time, you must switch to it:

Copy code

sql
-- 1. Switch to the catalog
SET CATALOG hive;

-- 2. Switch to a database within that catalog
USE your_database_name;

-- 3. Query normally
SELECT * FROM your_table;

2. Use the three-part naming (Recommended for cross-catalog queries) You can query tables in your unified catalog from any session context by using the full path:

catalog.database.table

Copy code

sql
SELECT * FROM hive.your_database_name.your_table LIMIT 10;

Why is it called "Unified"? The "Unified" type in your

SHOW CATALOGS

output refers to the Unified Catalog connector (introduced in v3.2). It doesn't mean it is "globally unified" or "the default." Instead, it means that this single catalog (

hive

) can automatically detect and query multiple data formats (Hive, Iceberg, Hudi, Delta Lake, and Kudu) within the same metastore. This is why you see separate catalogs like

delta_dev

and

delta_prod

in your list—you could potentially consolidate those into one Unified catalog if they shared the same Metastore (HMS/Glue). Summary: * Default upon login:

default_catalog

. * To switch:

SET CATALOG hive;

. * To check where you are:

SELECT catalog();

Rocky

09/29/2026, 2:00 PM

• sql-reference/sql-statements/Catalog/SHOW_CATALOGS.md

ansh.gupta2

09/29/2026, 2:07 PM

@Rocky default.fact_purchase_invoices1 is a hive transactional table(full acid), sr didn't support it yet

Rocky

09/29/2026, 2:07 PM

You are correct. As of the current versions (including v3.3 and v3.4), StarRocks does not support reading Hive Transactional (ACID) tables, whether they are "Insert Only" or "Full ACID." If you try to query a Hive table with the table property

transactional=true

, you will likely encounter an error or see no data, because StarRocks cannot currently parse the delta files and base-file merging logic used by Hive's ACID implementation. Why is this the case? Hive ACID tables store data in a specific directory structure with

base

and

delta

folders and require the query engine to perform a complex merge-on-read operation. StarRocks is optimized for high-performance reading of standard Parquet, ORC, and Avro files but has not yet implemented the logic to handle Hive's specific transactional delta formats. Recommended Workarounds If you need to analyze this data in StarRocks, consider one of the following approaches: 1. Convert to Iceberg/Hudi/Delta Lake: These modern "Lakehouse" formats provide full ACID support and are deeply supported by StarRocks. Most teams are migrating Hive transactional tables to Apache Iceberg to gain better performance and compatibility with engines like StarRocks. 2. Export to Non-Transactional Tables: Create a standard, non-transactional Hive table (stored as Parquet or ORC) and periodically sync the data from your ACID table to the non-transactional one:

Copy code

sql
    -- In Hive
    INSERT OVERWRITE TABLE fact_purchase_invoices1_sr_ready
    SELECT * FROM fact_purchase_invoices1;

Then, point StarRocks at the

fact_purchase_invoices1_sr_ready

table. 3. Ingest into StarRocks (Internal Table): If you need low-latency analytics, use a StarRocks Primary Key table. You can load data from your Hive table into StarRocks using an

INSERT INTO ... SELECT

(if accessed via a tool that supports both) or a broker load. StarRocks' internal tables support full ACID semantics and high-concurrency updates. Rocky's Note: Support for Hive ACID tables is a frequent community request. You can check the StarRocks GitHub Issues or the roadmap for updates on future support for this feature. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/hive_catalog.md • loading/SQL_transaction.md References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/hive_catalog.md • loading/SQL_transaction.md References • loading/SQL_transaction.md • data_source/feature-support-data-lake-analytics.md • deployment/shared_data/feature-support-shared-data.md • table_design/table_types/table_capabilities.md

ansh.gupta2

09/30/2026, 2:55 AM

@Rocky CREATE analyze job command?

Rocky

09/30/2026, 2:55 AM

In StarRocks, there are two main ways to "analyze" a table. You use ANALYZE TABLE
for a one-time manual task and CREATE ANALYZE
to set up a recurring automatic job. 1. Manual (One-time) Task:
ANALYZE TABLE
Use this if you want to update statistics immediately. * Full collection:

Copy code

sql
    ANALYZE FULL TABLE table_name;

* Sampled collection (faster for large tables):

Copy code

sql
    ANALYZE SAMPLE TABLE table_name;

* Specific columns:

Copy code

sql
    ANALYZE TABLE table_name (col1, col2);

2. Automatic (Recurring) Job:
CREATE ANALYZE
Use this to customize how StarRocks automatically refreshes statistics for a database or table in the future. * For a whole database:

Copy code

sql
    CREATE ANALYZE DATABASE db_name;

* With custom properties (e.g., trigger when 30% of data changes):

Copy code

sql
    CREATE ANALYZE TABLE table_name
    PROPERTIES (
        "statistic_auto_collect_ratio" = "0.3"
    );

3. Monitoring Your Jobs After running or creating a job, you can check its status with these commands: * Check status of manual/running tasks:

Copy code

sql
    SHOW ANALYZE STATUS;

* View your defined automatic jobs:

Copy code

sql
    SHOW ANALYZE JOB;

SHOW ANALYZE STATUS

SHOW ANALYZE JOB

Note: StarRocks v2.4+ automatically collects full statistics by default every 5 minutes if data changes. You only need

CREATE ANALYZE

ansh.gupta2

09/30/2026, 2:59 AM

Rocky

09/30/2026, 2:59 AM

By default, ANALYZE TABLE
returns only when the collection is completed. In StarRocks, manual collection via

ANALYZE TABLE

is synchronous unless you explicitly tell it otherwise. Because your command returned

OK

after 1.33 seconds, it means StarRocks has finished scanning the metadata (and potentially sample data) for

bidb.dim_location

and updated the Cost-Based Optimizer (CBO) statistics. Key Points: * Synchronous (Default): The client waits for the task to finish. This is best for smaller tables or when you need the stats immediately for a following query. * Asynchronous: If you have a massive table and don't want your SQL session to hang, you can run:

Copy code

sql
    ANALYZE TABLE bidb.dim_product WITH ASYNC MODE;

In this mode, it returns "OK" immediately as soon as the task is received, and the work happens in the background. How to verify: Regardless of whether you ran it synchronously or asynchronously, you can always check the history and status of the collection using:

Copy code

sql
SHOW ANALYZE STATUS;

Look for the

State

column; it will show

FINISHED

Rocky

09/30/2026, 2:59 AM

• sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/30/2026, 3:02 AM

@Rocky what the table name of show analyze status in _statistics

Rocky

09/30/2026, 3:02 AM

The information displayed by the

SHOW ANALYZE STATUS

command is stored in the internal table analyze_status
within the *`*statistics*`* database. Specifically: * Database Name:

*statistics*

(Note the double underscores) * Table Name:

analyze_status

You can query this directly if you have the necessary administrative privileges:

Copy code

sql
SELECT * FROM __statistics__.analyze_status;

Related Internal Tables StarRocks uses several tables in the

*statistics*

database to manage CBO (Cost-Based Optimizer) metadata: * analyze_status
: Stores the execution history and current status of analyze tasks (queried via

SHOW ANALYZE STATUS

). * analyze_job
: Stores the definitions and status of recurring/custom analyze jobs (queried via

SHOW ANALYZE JOB

). * external_analyze_status
: Specifically used for analyze tasks on external tables (like Hive or Iceberg catalogs). * column_statistics
: Stores the actual column-level statistics (min, max, null count, etc.). * table_statistic_v1
: Stores table-level statistics like row count. For most users, it is recommended to use the

information_schema.analyze_status

view or the

SHOW ANALYZE STATUS

command rather than querying the

*statistics*

internal tables directly. References • sql-reference/information_schema/analyze_status.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • using_starrocks/Cost_based_optimizer.md References • sql-reference/information_schema/analyze_status.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md

ansh.gupta2

09/30/2026, 3:05 AM

Rocky

09/30/2026, 3:05 AM

Based on the list of tables you provided, the

analyze_status

information is not stored in a standard physical table within the

*statistics*

(or

_statistics_

) database in your version of StarRocks. While the

*statistics*

database stores the results of the analysis (like

column_statistics

and

table_statistic_v1

), the history and status of the analyze jobs themselves are managed internally by the Frontend (FE). To query the same information shown by

SHOW ANALYZE STATUS

using a SQL

SELECT

statement, you should use the system view in the information_schema
: The correct "table" (view) to query:

Copy code

sql
SELECT * FROM information_schema.analyze_status;

Why it's not in your list: 1. Metadata vs. Data: The tables you see in

*statistics*

(like

column_statistics

) are where StarRocks stores the actual data values (min, max, row counts) used by the optimizer. 2. Job Tracking: In modern StarRocks versions (v3.x), the status of manual and automatic analyze tasks is treated as system metadata. It is exposed via

information_schema

for convenience but doesn't reside in the

*statistics*

OLAP tables. 3. Retention: This status history is temporary. You can control how long this history is kept using the FE configuration

statistic_analyze_status_keep_second

(default is 3 days). Summary of where things are: * Job Status/History:

information_schema.analyze_status

(equivalent to

SHOW ANALYZE STATUS

) * Table Statistics (Row counts):

*statistics*.table_statistic_v1

* Column Statistics (Min/Max/Nulls):

*statistics*.column_statistics

* External Table Stats:

*statistics*.external_column_statistics

References • sql-reference/information_schema/analyze_status.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • using_starrocks/Cost_based_optimizer.md References • sql-reference/information_schema/analyze_status.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md References • sql-reference/information_schema/analyze_status.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md

ansh.gupta2

09/30/2026, 3:15 AM

@Rocky mysql> SELECT * FROM default_catalog.information_schema.analyze_status WHERE STATUS IN ('RUNNING'); +-------+---------+----------+------------------------------------+---------+------+----------+---------+---------------------+---------+------------+--------+ | Id | Catalog | Database | Table | Columns | Type | Schedule | Status | StartTime | EndTime | Properties | Reason | +-------+---------+----------+------------------------------------+---------+------+----------+---------+---------------------+---------+------------+--------+ | 14977 | hive | bidb | fact_core_item | ALL | FULL | ONCE | RUNNING | 2026-09-30 030253 | | {} | | | 14979 | hive | bidb | fact_inventory_item_transition_new | ALL | FULL | ONCE | RUNNING | 2026-09-30 030308 | | {} | | | 15026 | hive | bidb | fact_returns | ALL | FULL | ONCE | RUNNING | 2026-09-30 030907 | | {} | | +-------+---------+----------+------------------------------------+---------+------+----------+---------+---------------------+---------+------------+--------+ 3 rows in set (0.02 sec) only 3 in runnng we increased the concurrency?

Rocky

09/30/2026, 3:15 AM

Yes, the reason you only see 3 tasks in

RUNNING

status is because of a specific StarRocks Frontend (FE) configuration limit. The Limiting Parameter The maximum number of concurrent manual

ANALYZE TABLE

tasks is controlled by the following FE configuration: * Parameter:

statistic_collect_concurrency

* Default Value:

* Description: This parameter defines the maximum number of manual statistics collection tasks that can run simultaneously. Any additional tasks you trigger will stay in the

PENDING

state until one of the running tasks finishes. How to Increase Concurrency If you want to run more than 3 analyze tasks at once, you can increase this limit using the following command (requires admin privileges):

Copy code

sql
ADMIN SET FRONTEND CONFIG ("statistic_collect_concurrency" = "10");

(Replace
10
with your desired number of concurrent tasks.) Important Considerations for Hive/External Catalogs Since you are analyzing Hive tables (

Catalog: hive

), there are two other related settings depending on how the tasks were triggered: 1. Manual Tasks (Your Case): These are governed by

statistic_collect_concurrency

(Default: 3). 2. Query-Triggered Tasks: If the system automatically triggers an analyze task because of a query (StarRocks v3.4+), it is governed by: *

connector_table_query_trigger_analyze_max_running_task_num

(Default: 2). 3. Automatic Tasks: For system-triggered background auto-collection, there isn't a direct "concurrency" count in the same way, but it is managed by the background scheduler based on system health. How to Verify the Change After running the

ADMIN

command, you can verify the new setting with:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE 'statistic_collect_concurrency';

Keep in mind that increasing this value will consume more CPU and I/O resources on your cluster, as each

ANALYZE

task involves scanning data and calculating metadata. References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • sql-reference/information_schema/analyze_status.md References • administration/management/FE_configuration.md

ansh.gupta2

09/30/2026, 3:16 AM

@Rocky mysql> ADMIN SET FRONTEND CONFIG ("statistic_collect_concurrency" = "10"); ERROR 1064 (HY000): Config 'statistic_collect_concurrency' does not exist or is not mutable mysql>

Rocky

09/30/2026, 3:16 AM

The error message

Config 'statistic_collect_concurrency' does not exist or is not mutable

indicates that this specific configuration item either has a different name in your version or is a static configuration that cannot be changed via SQL and must be set in the

fe.conf

file. 1. Verify the Parameter Name and Mutability First, run this command to see the exact name and whether it can be changed dynamically (

IsMutable

column):

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE 'statistic_collect_concurrency';

* If the result is empty: The parameter has likely been renamed. Try searching with a broader pattern:

ADMIN SHOW FRONTEND CONFIG LIKE 'statistic%';

. * If
IsMutable
is `FALSE`: You cannot use

ADMIN SET FRONTEND CONFIG

. You must add the configuration to your

fe.conf

file and restart the Frontend (FE). 2. How to Increase Concurrency (Static Config) If the parameter is static, follow these steps: 1. Open the

fe.conf

file on your Leader FE node. 2. Add or modify the following line:

Copy code

conf
    statistic_collect_concurrency = 10

3. Restart the FE. (Note: For a high-availability cluster, perform a rolling restart, starting with Follower FEs and ending with the Leader). 3. Concurrency for Hive/External Tables Since you are specifically analyzing Hive tables, ensure you are not hitting the limits for query-triggered or background analysis, which have separate controls in newer versions (v3.4+): * connector_table_query_trigger_analyze_max_running_task_num
: (Default: 2) Controls the concurrency of analyze tasks triggered automatically by queries on external tables. This is dynamic:

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("connector_table_query_trigger_analyze_max_running_task_num" = "5");

* statistic_collect_parallel
: This is a session variable (not an FE config). It controls the parallelism within a single analyze task (how many BE threads work on one table). Increasing this can speed up a single large analyze:

Copy code

sql
    SET GLOBAL statistic_collect_parallel = 4;

Summary of Limits If you see exactly 3 tasks in

RUNNING

and others are

PENDING

, the system is definitely hitting the

statistic_collect_concurrency

limit. If the

ADMIN

command failed, your version requires the

fe.conf

update described above. References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • administration/management/FE_configuration.md • best_practices/audit_log_resource_group.md

ansh.gupta2

09/30/2026, 3:19 AM

@Rocky _ERROR 1064 (HY000): bidb.fact_core_item_smallint_tobe_deleted_20260616 is a hive transactional table(full acid), sr didn't support it yet_

Rocky

09/30/2026, 3:19 AM

This error confirms that StarRocks currently does not support reading Hive Full ACID (transactional) tables. As of StarRocks v4.0 and later, the system explicitly detects if a Hive table is configured with

transactional=true

. Instead of potentially returning incorrect or incomplete data (which could happen in older versions), it now returns this clear error message. Why this happens Hive Full ACID tables (which support

UPDATE

and

DELETE

) store data in a complex structure of "base" and "delta" directories with specific row-metadata columns. StarRocks' native Hive connector is optimized for high-speed scanning of standard formats (Parquet, ORC) but does not yet include the logic to merge these Hive ACID delta files on the fly. Workarounds If you need to analyze this data in StarRocks, you can use one of the following methods: 1. Create a non-transactional copy in Hive: The most reliable way is to create a standard Parquet or ORC table in Hive and sync the data:

Copy code

sql
    -- In Hive
    CREATE TABLE bidb.fact_core_item_flat
    STORED AS PARQUET
    AS SELECT * FROM bidb.fact_core_item_smallint_tobe_deleted_20260616;

Then, point StarRocks to this new table. 2. Use an External Table Sink: If you are using StarRocks v3.2+, you can try to read the data via Spark/DataX and write it directly into a new, non-transactional Hive table that StarRocks can then read. 3. Ingest into StarRocks: If the table isn't too large, you can ingest the data into a native StarRocks Primary Key table. This gives you StarRocks' own high-performance ACID capabilities while allowing you to query the data at maximum speed. Note on "Insert-Only" ACID StarRocks also recently disabled support for Insert-Only Hive ACID tables to ensure data correctness. If your table was recently converted to ACID or created that way by default (common in Hive 3.x), you may need to explicitly set

TBLPROPERTIES('transactional'='false')

in Hive when creating new tables intended for StarRocks. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/hive_catalog.md References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/hive_catalog.md References • data_source/feature-support-data-lake-analytics.md • deployment/shared_data/feature-support-shared-data.md • loading/SQL_transaction.md • data_source/catalog/hive_catalog.md

ansh.gupta2

09/30/2026, 3:24 AM

@Rocky to remove CBO which 4.1.0 version is correct?

Rocky

09/30/2026, 3:25 AM

As of StarRocks v4.1.0, the CBO (Cost-Based Optimizer) is the core query engine and has not been "removed." In fact, version 4.1.0 (released around April 2026) introduced significant enhancements to CBO, especially for data lake analytics. However, your question "to remove CBO" likely refers to one of the following scenarios: 1. Disabling CBO (The Legacy Planner) If you are trying to "remove" (disable) the CBO to use the Legacy Planner (RBO), please note: * The Legacy Planner is deprecated: StarRocks has moved entirely to the CBO architecture. In version 4.0 and 4.1, the legacy planner is either fully removed or no longer maintained for new features (like Hive/Iceberg catalogs). * The variable `enable_cbo`: In recent versions,

SET enable_cbo = false

may no longer be supported or may not bypass checks for external tables, as the CBO is required for Catalog features. 2. Resolving the "Hive Transactional Table" Error If your goal in "removing CBO" was to bypass the error

bidb.fact_core_item... is a hive transactional table(full acid), sr didn't support it yet

, disabling CBO will not solve this. * Limitation: StarRocks 4.1.0 still does not support reading Hive Full ACID tables directly because they require a complex merge-on-read logic that the native Parquet/ORC readers don't yet implement. * Fix: You must convert the table to a non-transactional format (standard Parquet/ORC) in Hive or wait for a future patch (though it is not listed as "fixed" in the 4.1.x series yet). 3. Missing/Removed Config (
statistic_collect_concurrency
) In your previous message, you saw an error that

statistic_collect_concurrency

does not exist. * In StarRocks 4.1.x, some statistics parameters have been renamed or integrated into the automatic statistics collection framework. * To see the correct parameters for your version, run:

Copy code

sql
    ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';

* In newer versions, concurrency for external table analysis is often managed by

connector_table_query_trigger_analyze_max_running_task_num

or is handled automatically by the system. Recommended Version If you are moving to the 4.1 series: * Avoid v4.1.0: There was a known unstable container image issue (BE process load order). * Use v4.1.1 or later: These versions fix the container startup issue. * Use v4.1.3: This version specifically includes CBO enhancements for external tables, such as estimating row counts from Iceberg manifests instead of scanning all files, which significantly improves query planning speed. Summary: You cannot (and should not) "remove" CBO in 4.1.0. If you are experiencing errors with Hive ACID tables or statistics collection, the solution is to adjust the table format or use the updated configuration names found in the

ADMIN SHOW FRONTEND CONFIG

list. References • using_starrocks/Cost_based_optimizer.md • release_notes/release-4.0.md • administration/management/FE_configuration.md • data_source/catalog/hive_catalog.md References • using_starrocks/Cost_based_optimizer.md • release_notes/release-4.0.md • release_notes/release-2.1.md

ansh.gupta2

09/30/2026, 3:26 AM

@Rocky there is one statistic CBO bug which is fixed in 4.1.0?

Rocky

09/30/2026, 3:26 AM

In StarRocks v4.1.0 (released April 13, 2026), several significant CBO and statistics-related bugs were fixed. Based on your previous errors regarding statistics collection and CBO, the specific bug you are likely referring to is: 1. The "Missing Statistic of Col" Bug (#74791) In versions prior to 4.1.0, a bug existed in the Join Reorder logic where the optimizer would prune columns that were still referenced by scan predicates. This caused the CBO to throw an error: "missing statistic of col" or results in an optimizer crash. This was officially fixed in the 4.1.0 release. 2. Other CBO & Statistics Fixes in 4.1.0 Version 4.1.0 included a major overhaul of how statistics are handled, particularly for Data Lake (Hive/Iceberg) environments: * External Table Row Count Estimation: Fixed a bug where row counts for Hive and Hudi tables were severely underestimated when using Parquet or ORC compression. * Iceberg Manifest Statistics: Improved the CBO's ability to estimate row counts directly from Iceberg manifests without requiring a full file scan, which was a performance "bug" in previous versions that caused slow planning on large tables. * Empty Range Join Crash (#75011): Fixed a planner crash that occurred when the CBO attempted to derive predicates from an empty range in a join. * Incorrect Aggregation Pushdown (#75037): Fixed a bug where the CBO would incorrectly push down aggregations over a

CASE

expression with a non-null constant

ELSE

, leading to wrong results. 3. Regarding
statistic_collect_concurrency
The error you encountered (

Config 'statistic_collect_concurrency' does not exist

) in v4.1.0 is due to the Deprecation/Renaming of legacy statistics parameters. * In the 4.1 series, the system has moved toward a more automated, metadata-driven approach for external tables (like Hive and Iceberg). * Manual concurrency for external table analysis is now often controlled by

connector_table_query_trigger_analyze_max_running_task_num

or handled by the internal

AnalyzeMgr

. * Recommendation: Use

ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';

to see the updated list of available parameters for your specific 4.1.x build. Summary If you are experiencing "missing statistic" errors or planner crashes, v4.1.0 is the correct version that addresses these CBO issues. However, because of a known container image startup issue in the original 4.1.0 release, it is highly recommended to upgrade to v4.1.1 or v4.1.3, which contain the CBO fixes plus critical stability patches for BE processes. References • release_notes/release-4.0.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md References • using_starrocks/Cost_based_optimizer.md • release_notes/release-4.0.md • sql-reference/sql-statements/cbo_stats/SHOW_COLUMN_STATS.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md • administration/management/FE_configuration.md • data_source/catalog/iceberg_catalog.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • data_source/External_table.md

Rocky

09/30/2026, 3:26 AM

• data_source/catalog/hive_catalog.md • loading/SQL_transaction.md

ansh.gupta2

09/30/2026, 3:27 AM

@Rocky for delta which is the best stable version

Rocky

09/30/2026, 3:27 AM

For Delta Lake support, the "best" version depends on whether you prioritize long-term stability or the latest protocol features (like Deletion Vectors). *1. Most Stable (LTS): StarRocks v3.3.x* If your priority is production stability and long-term support, v3.3 is the designated Long-Term Support (LTS) version. It is highly optimized for lakehouse integration and includes: * V2 Checkpoint Support (from v3.3.0) * Column Mapping (from v3.3.6) * Timestamp without Timezone (from v3.3.1) * Metadata Local Cache: Significantly improves query planning performance for large Delta tables. *2. Best for Modern Delta Features: StarRocks v4.1.1+* If you are using newer Delta Lake versions (Delta 3.x+) that utilize Deletion Vectors (for fast updates/deletes), you should use the v4.1 branch. * Deletion Vector Support: While introduced in v3.4.1, it is more mature and performant in the v4.1 engine. * Performance: v4.1 includes "Adaptive Layout" and enhanced CBO logic for data lakes that can significantly outperform the 3.3 branch for complex joins. * Important: Avoid v4.1.0 due to a known container startup bug. Use v4.1.1 or the latest 4.1.5 for the best experience. Feature Compatibility Matrix Feature | Supported From Basic Parquet Read | v2.5+ V2 Checkpoints | v3.3.0+ Column Mapping | v3.3.6+ Deletion Vectors | v3.4.1+ (Recommended v4.1+) Unified Catalog | v3.2+ Summary Recommendation * Use v3.3.x (LTS) if you want the most tested, "boring" stability for standard Delta tables. * Use v4.1.3 or 4.1.5 if you need support for Deletion Vectors, want the latest CBO bug fixes (like the "missing statistic" fix), or require the highest possible performance for large-scale federated queries. References • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md • release_notes/release-3.3.md • release_notes/release-4.1.md References • data_source/catalog/deltalake_catalog.md • data_source/feature-support-data-lake-analytics.md

ansh.gupta2

09/30/2026, 3:28 AM

@Rocky v4.1.1 or the latest 4.1.5 latest can have bugs?

Rocky

09/30/2026, 3:29 AM

Yes, the "latest" version can have bugs, but it is a trade-off. Here is how to think about v4.1.5 vs. v3.3.x (LTS): 1. The "Latest" (v4.1.5) = Most Fixes + Newest Features In the StarRocks versioning system, the third number (the

in 4.1.5) represents a patch release. * Why it's better: v4.1.5 contains hundreds of bug fixes that weren't in v4.1.0 or v4.1.1. For example, it fixes specific CBO crashes, memory leaks in the Delta Lake connector, and the container startup issue found in 4.1.0. * The Risk: Because the 4.1 branch is relatively new (released in early 2026), it includes "bleeding edge" features like Adaptive Layout and Iceberg Variant support. New features always carry a slightly higher risk of undiscovered edge-case bugs compared to an older, more "seasoned" branch. 2. The "Stable" (v3.3.x LTS) = Most Tested If your definition of "stable" is "no new features, only bug fixes for the last 2 years," then v3.3.21 (or the latest 3.3 patch) is the safest choice. * Pros: It has been battle-tested in thousands of production environments. * Cons: You miss out on newer Delta Lake optimizations like Deletion Vectors or the performance gains from the v4.1 CBO enhancements. Known Issues in the 4.1 Branch While v4.1.5 is very stable, there are some specific bugs reported in the 4.1 series that you should be aware of: * Bucket Shuffle Joins (#79859): There was a report in v4.1.3 regarding intermittent result drops when using

ROW_NUMBER()

followed by a bucket shuffle join. (This is a rare edge case, but documented). * Memory Management: Some users on the 4.1 branch have reported higher memory usage during complex "spill-to-disk" queries compared to the 3.3 branch, though 4.1.5 has improved this significantly. My Recommendation * If you are in Production and "Standard" Delta is enough: Use v3.3.21 (LTS). It is the most "boring" and reliable version. * If you need Delta Lake Deletion Vectors or the best performance: Use v4.1.5. While it is "newer," the bugs it fixes (especially the CBO "missing statistic" bugs you asked about earlier) usually outweigh the risk of new bugs for most users. Pro-tip: Before upgrading any "latest" version to a critical production cluster, always run your most complex SQL reports in a test environment first to ensure the CBO plan stability for your specific data. References • release_notes/release-4.1.md • release_notes/release-3.3.md • administration/management/Upgrade.md References • release_notes/release-4.0.md • release_notes/release-3.3.md • release_notes/release-3.4.md

ansh.gupta2

09/30/2026, 3:32 AM

@Rocky Normal Scheduled 32s default-scheduler Successfully assigned starrocks-dev/starrocks-shared-data-fe-0 to aks-clickhouse1-31600135-vmss00008y Normal SuccessfulAttachVolume 26s attachdetach-controller AttachVolume.Attach succeeded for volume "pvc-93b2271f-5066-4f2a-8c21-d045f1e5bfc7" Normal BackOff 21s kubelet Back-off pulling image "starrocks/fe-ubuntu:4.1.5" Warning Failed 21s kubelet Error: ImagePullBackOff Normal Pulling 9s (x2 over 23s) kubelet Pulling image "starrocks/fe-ubuntu:4.1.5" Warning Failed 8s (x2 over 22s) kubelet Failed to pull image "starrocks/fe-ubuntu:4.1.5": rpc error: code = NotFound desc = failed to pull and unpack image "docker.io/starrocks/fe-ubuntu:4.1.5": failed to resolve image: docker.io/starrocks/fe-ubuntu:4.1.5: not found Warning Failed 8s (x2 over 22s) kubelet Error: ErrImagePull

Rocky

09/30/2026, 3:32 AM

The

ImagePullBackOff

and

not found

errors indicate that the 4.1.5
tag has not yet been pushed to the official StarRocks Docker Hub repository, or there is a slight naming mismatch. Although the v4.1.5 source code was released (September 8, 2026), the Docker images occasionally lag behind by a few days. To resolve this and get the CBO fixes you need, please try the following: 1. Use the Latest Available Tag As of late September 2026, the most recent stable tags successfully pushed to Docker Hub for the 4.1 branch are 4.1.4
or 4.1.3
. * Update your manifest to:

starrocks/fe-ubuntu:4.1.4

(or

4.1.3

) * Update your BE to:

starrocks/be-ubuntu:4.1.4

(or

4.1.3

) 2. Verify the Repository Name Ensure you are using the correct repository. Depending on your base OS preference, the tags are usually: * Ubuntu-based:

starrocks/fe-ubuntu:<tag>

* CentOS-based (Deprecated after 4.1):

starrocks/fe-centos7:<tag>

* General/Default:

starrocks/fe:<tag>

3. Check Docker Hub Directly You can verify the exact available strings by visiting the StarRocks Docker Hub Tags page. Look for the most recent version that has an "Images" size listed. Why this version still works for you: The critical CBO bug you were looking for (the "missing statistic of col" issue) was fixed in v4.1.0. By using v4.1.4 or v4.1.3, you still receive that fix plus nearly all the stability improvements of the 4.1 branch, without the container startup issues that affected the original 4.1.0 release. Recommended Action: Change your image to

starrocks/fe-ubuntu:4.1.4

. If that also fails, fallback to

4.1.3

, which is confirmed to be stable in containerized environments.

ansh.gupta2

09/30/2026, 3:38 AM

@Rocky 4.1.4 worked now properties to speed up manual stats collections

Rocky

09/30/2026, 3:38 AM

To speed up manual statistics collection (

ANALYZE TABLE

) for your Delta Lake tables in StarRocks v4.1.4, you should focus on increasing parallelism and limiting the scope of the collection. 1. Increase Collection Parallelism By default, StarRocks uses low parallelism for statistics tasks to avoid impacting regular queries. You can increase this at the session level before running your

ANALYZE

command:

Copy code

sql
-- Increase the number of concurrent statistics tasks allowed on BEs
SET statistic_collect_parallel = 8;

Note: Setting this too high (e.g., > 16) may impact the performance of other concurrent queries on the cluster. 2. Analyze Specific Columns Only Instead of analyzing the entire table, only collect statistics for columns used in

JOIN

conditions,

WHERE

clauses, or

GROUP BY

statements. This is the single most effective way to speed up the process.

Copy code

sql
-- Fast: Only analyze columns used for filtering/joining
ANALYZE FULL TABLE delta_catalog.db.table_name (id, user_id, event_date);

-- Slow: Analyzes every single column (default)
ANALYZE FULL TABLE delta_catalog.db.table_name;

3. Use Async Mode For large Delta tables, manual collection can time out the client connection. Use

ASYNC

mode to let StarRocks handle it in the background.

Copy code

sql
ANALYZE FULL TABLE delta_catalog.db.table_name WITH ASYNC MODE;

You can then track the progress with:

Copy code

sql
SHOW ANALYZE STATUS WHERE `Table` = 'table_name';

4. Enable Query-Triggered (Auto) Collection Since you are on v4.1.4, StarRocks can automatically collect statistics for Delta tables based on your actual queries. This ensures you only collect stats for the columns that actually matter for your workload. Ensure these are enabled (they are by default in 4.x):

Copy code

sql
SET GLOBAL enable_query_trigger_analyze = true;

5. Identify Bottlenecks (Profiling) If collection is still slow, enable profiling to see if the delay is in the Delta Lake metadata parsing or the Parquet data scanning:

Copy code

sql
-- Admin only: Enable profile generation for stats tasks
ADMIN SET FRONTEND CONFIG ("enable_statistics_collect_profile" = "true");

After running an

ANALYZE

task, you can view the execution profile in the StarRocks Manager UI or via

SHOW PROFILELIST

. Important Limitation for Delta Lake For external tables like Delta Lake, ANALYZE SAMPLE
is not supported. You must use ANALYZE FULL
. This makes the column selection (Point #2) and parallelism (Point #1) even more critical for performance. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • using_starrocks/Cost_based_optimizer.md • data_source/catalog/deltalake_catalog.md

ansh.gupta2

09/30/2026, 3:40 AM

@Rocky how to increase concurrency of stats collection?

Rocky

09/30/2026, 3:40 AM

To increase the concurrency and speed of statistics collection in StarRocks v4.1.4, you need to adjust parameters at two levels: how many jobs run at once (Concurrency) and how many threads a single job uses (Parallelism). 1. Increase Job Concurrency (FE Level) This controls how many separate

ANALYZE

tasks can be in the

RUNNING

state at the same time. If you submit 20

ANALYZE

commands, but this limit is 5, only 5 will run while 15 wait in

PENDING

. * Manual Tasks:

Copy code

sql
    -- Increase the number of concurrent manual/automatic analyze jobs
    ADMIN SET FRONTEND CONFIG ("statistic_analyze_task_pool_size" = "10");

(Default is usually 5 or 10 depending on the minor version. Increasing this consumes more FE memory/CPU to manage the task states.) * Query-Triggered Tasks (for Delta Lake/External Catalogs):

Copy code

sql
    -- Increase concurrency specifically for tasks triggered by your queries
    ADMIN SET FRONTEND CONFIG ("connector_table_query_trigger_analyze_max_running_task_num" = "8");

2. Increase Single-Job Parallelism (Session Level) This controls how many threads a single

ANALYZE

job uses on the Backends (BEs). Increasing this makes one table's collection finish much faster by scanning more data in parallel.

Copy code

sql
-- Set this in your session before running ANALYZE
SET statistic_collect_parallel = 8;

* Default: 1 * Recommendation: Set this to a value matching your BE core count or a fraction of it (e.g., 8 or 16). 3. Practical Example for Delta Lake Since you are analyzing Delta Lake tables (which require

FULL

scans), a combination of these is best: 1. Set Session Parallelism:

SET statistic_collect_parallel = 16;

2. Run as Async:

ANALYZE FULL TABLE my_delta_table WITH ASYNC MODE;

3. Monitor:

Copy code

sql
    SHOW ANALYZE STATUS WHERE `Table` = 'my_delta_table';

Summary of Performance Properties When running

ANALYZE

, you can also pass properties to fine-tune the task's behavior: Property | Level | Goal

statistic_collect_parallel

| Session | Faster scanning (Parallelism)

statistic_analyze_task_pool_size

| FE Config | More simultaneous tables (Concurrency)

statistic_max_full_collect_data_size

| FE Config | Force Sample vs Full (Safety) Caution: If you increase both

statistic_analyze_task_pool_size

and

statistic_collect_parallel

significantly, you may see high CPU/IO spikes on your BE nodes. Monitor your cluster's health while these tasks are running. References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • administration/management/FE_configuration.md

ansh.gupta2

09/30/2026, 3:45 AM

@Rocky mysql> SELECT * FROM default_catalog.information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); +-------+---------+-------------+------------------------------------+---------+------+----------+---------+---------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------+--------+ | Id | Catalog | Database | Table | Columns | Type | Schedule | Status | StartTime | EndTime | Properties | Reason | +-------+---------+-------------+------------------------------------+---------+------+----------+---------+---------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------+--------+ | 10213 | hive | bidb | fact_core_item | ALL | FULL | ONCE | RUNNING | 2026-09-30 034153 | | {table_format=deltalake, partition_count=2922, column_count=198, scan_bytes_cap=2147483648, scan_files_cap=1000, scan_rows_cap=10000000} | | | 10215 | hive | bidb | fact_inventory_item_transition_new | ALL | FULL | ONCE | RUNNING | 2026-09-30 034155 | | {table_format=deltalake, partition_count=2625, column_count=14, scan_bytes_cap=2147483648, scan_files_cap=1000, scan_rows_cap=10000000} | | | 10248 | hive | clickstream | events | ALL | FULL | ONCE | PENDING | 2026-09-30 034408 | | {} | | | 10222 | hive | bidb | fact_order_live | ALL | FULL | ONCE | RUNNING | 2026-09-30 034210 | | {table_format=deltalake, partition_count=45, column_count=104, scan_bytes_cap=2147483648, scan_files_cap=1000, scan_rows_cap=10000000} | | | 10223 | hive | bidb | fact_returns | ALL | FULL | ONCE | PENDING | 2026-09-30 034211 | | {} | | | 10230 | hive | bidb | fact_style_funnel_clickstream | ALL | FULL | ONCE | PENDING | 2026-09-30 034254 | | {} | | | 10231 | hive | bidb | fact_user_lifetime | ALL | FULL | ONCE | PENDING | 2026-09-30 034254 | | {} | | | 10232 | hive | bidb | notif_personalize_hybrid | ALL | FULL | ONCE | PENDING | 2026-09-30 034254 | | {} | | | 10233 | hive | bidb | pricing_snapshot_history | ALL | FULL | ONCE | PENDING | 2026-09-30 034255 | | {} | | +-------+---------+-------------+------------------------------------+---------+------+----------+---------+---------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------+--------+ 9 rows in set (0.01 sec) still only 3 in running

Rocky

09/30/2026, 3:45 AM

To increase the concurrency of your statistics collection (moving those tasks from

PENDING

RUNNING

), you need to adjust the FE configuration parameters that govern the task pools. The limit you are seeing (3 tasks running) is likely due to the default pool sizes for manual and connector-based tasks. 1. Increase the Global Analyze Pool This parameter controls the maximum number of concurrent

ANALYZE

tasks (both manual and automatic) that the FE will execute.

Copy code

sql
-- Default is usually 5 or 10, but your environment might be restricted
ADMIN SET FRONTEND CONFIG ("statistic_analyze_task_pool_size" = "10");

2. Increase Connector-Specific Concurrency Since you are working with a Hive/Delta Lake catalog, StarRocks v3.4+ and v4.x have specific throttles for connector-triggered tasks. Even if you ran these manually, they often follow the connector pool limits.

Copy code

sql
-- Default is 2. This is likely why you are seeing very few running tasks.
ADMIN SET FRONTEND CONFIG ("connector_table_query_trigger_analyze_max_running_task_num" = "8");

3. Check for BE-Side Thread Limits If the tasks are "Running" but progressing slowly, check the parallelism of the individual scans. While the pool size (above) controls how many tables are analyzed at once, the following controls how many threads a single table scan uses:

Copy code

sql
-- Run this in your session before starting manual ANALYZE tasks
SET statistic_collect_parallel = 8;

Why are they still pending? If you apply the configs above and they stay

PENDING

, check for these conditions: * Resource Group Limits: If you have assigned your user to a specific Resource Group, the group might be capping your concurrent queries/scans. * FE Leader: Statistics are scheduled by the FE Leader. Ensure you are connected to the Leader FE or that the Follower FEs are properly communicating with the Leader. * Table Size Safety: If the table is extremely large, StarRocks might be delaying the task if it predicts a high impact on the cluster. You can force it by ensuring

statistic_max_full_collect_data_size

is large enough (default is 100GB). Recommended Step: Run the following to see the current effective limits on your cluster:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';
ADMIN SHOW FRONTEND CONFIG LIKE '%connector%analyze%';

This will confirm if

connector_table_query_trigger_analyze_max_running_task_num

is indeed set to a low value like

. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • data_source/feature-support-data-lake-analytics.md

ansh.gupta2

09/30/2026, 6:28 AM

@Rocky | | 10366 | hive | clickstream | session_style_daily | ALL | FULL | ONCE | FAILED | 2026-09-30 035020 | 2026-09-30 045050 | {table_format=deltalake, partition_count=1854, column_count=121, scan_bytes_cap=2147483648, scan_files_cap=1000, scan_rows_cap=10000000} | Analyze job timeout exceeded

Rocky

09/30/2026, 6:28 AM

The "Analyze job timeout exceeded" error you encountered at exactly 3630 seconds (1 hour + 30s buffer) indicates that your statistics collection task exceeded the default 1-hour execution limit. To fix this, you need to increase the timeout for statistics collection. In StarRocks 4.x, this is primarily controlled by a session variable and an FE configuration. 1. Increase the Collection Timeout Before running your

ANALYZE

command, set a higher timeout for the session. This value is in seconds:

Copy code

sql
-- Increase timeout to 4 hours (14400 seconds)
SET statistic_collect_query_timeout = 14400;

-- Then run your analyze command
ANALYZE FULL TABLE hive.clickstream.session_style_daily;

2. Increase Global FE Config (Optional) If you want this to apply to all background/automatic tasks, you can adjust the FE configuration. Note that you may need to check the exact name on your version:

Copy code

sql
-- Check current configuration first
ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%timeout%';

-- Increase the default timeout (if the config exists in your build)
ADMIN SET FRONTEND CONFIG ("statistic_collect_query_timeout" = "14400");

3. Why did it timeout? (Scan Caps) In your log, I noticed a specific property:

scan_bytes_cap=2147483648

(2GB). By default, StarRocks limits the amount of data scanned for statistics on external tables to prevent overwhelming the remote system. If your Delta Lake table is very large, the "Full" scan might be struggling to finish within the hour due to the remote I/O latency from Hive/S3. To speed it up and avoid the timeout, combine these settings:

Copy code

sql
-- 1. Increase parallelism (threads per BE)
SET statistic_collect_parallel = 16; 

-- 2. Increase the timeout
SET statistic_collect_query_timeout = 14400;

-- 3. Only analyze critical columns (Avoid ALL columns if not needed)
ANALYZE FULL TABLE hive.clickstream.session_style_daily (column1, column2, ...);

4. Adjust Scan Caps for External Tables If the task fails not because of time but because it didn't scan enough data to be accurate, you can increase the scan caps via FE configuration: * `statistic_max_full_collect_data_size`: (Default 100GB) The maximum size of a table for which FULL statistics can be collected. * `connector_table_query_trigger_analyze_max_running_task_num`: As mentioned before, increase this if you have many tables stuck in

PENDING

. References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/30/2026, 6:29 AM

@Rocky statistic_collect_query_timeout=3600 is low?

Rocky

09/30/2026, 6:29 AM

Yes, 3600 seconds (1 hour) is considered low for large external tables like yours (Delta Lake/Hive with 100+ columns and thousands of partitions). Why it is low for your case: 1. Remote Scanning: For Delta Lake and Hive tables,

ANALYZE FULL

requires StarRocks to scan data or metadata from remote storage (S3/HDFS). Network latency and the volume of metadata for thousands of partitions can easily exceed 60 minutes. 2. Default Parallelism: By default,

statistic_collect_parallel

is set to

, meaning it uses very few resources to avoid impacting production queries. This makes the collection process slow. 3. Large Column Counts: Your tables have 121 and 198 columns. Collecting stats for every column in a single pass takes significantly longer than a typical query. Recommendations: 1. Increase the Timeout Set the timeout to at least 4 hours (14400s) or more for these large catalogs:

Copy code

sql
-- For the current session
SET statistic_collect_query_timeout = 14400;

-- Globally for all future tasks
ADMIN SET FRONTEND CONFIG ("statistic_collect_query_timeout" = "14400");

2. Speed up the Scan (Parallelism) Instead of just waiting longer, make the job faster by using more BE threads. Since your tables are large, increasing parallelism is highly recommended:

Copy code

sql
-- Increase BE scan threads for the stats job (Default is 1)
SET statistic_collect_parallel = 8;

3. Targeted Collection If you don't need statistics for all 198 columns (e.g., you only filter or join on 10 of them), specify the columns. This will drastically reduce the data scanned and the time taken:

Copy code

sql
ANALYZE FULL TABLE hive.bidb.fact_core_item (col1, col2, col3);

4. Verify your Configs Run this to see your current effective limits:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';

If you see

statistic_collect_query_timeout

at 3600, that is definitely the cause of your "Analyze job timeout exceeded" error. References • administration/management/FE_configuration.md • sql-reference/System_variable.md • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

09/30/2026, 6:34 AM

@Rocky do i need to increase query timeout ?

Rocky

09/30/2026, 6:34 AM

No, you do not need to increase the standard

query_timeout

variable for your

ANALYZE

jobs. Since StarRocks v3.4.0, statistics collection has been decoupled from the

query_timeout

variable. For these tasks, the system specifically looks at statistic_collect_query_timeout
. Summary of what to change: * Increase this:

statistic_collect_query_timeout

(Controls the actual execution time of the analyze task). * Ignore this:

query_timeout

(Controls standard SELECT/DQL queries). Recommended Action Since your job timed out at 3600s, you should set the statistic-specific timeout to a much higher value (e.g., 4 or 8 hours) to accommodate the remote scanning of your Delta Lake/Hive tables:

Copy code

sql
-- Set for the current session before running ANALYZE
SET statistic_collect_query_timeout = 28800; 

-- Set globally so background/automatic tasks also benefit
ADMIN SET FRONTEND CONFIG ("statistic_collect_query_timeout" = "28800");

Why
query_timeout
doesn't help: The

query_timeout

variable (default 300s) is designed for short-lived user queries. Because

ANALYZE

on large data lakes can take hours, StarRocks uses the dedicated

statistic_collect_query_timeout

to prevent long-running stats jobs from being killed by the standard query limits. Note: If you are running

ANALYZE

manually and your SQL client (like DBeaver or MySQL Shell) disconnects, that is usually a client-side setting or the

wait_timeout

variable, but the task will still continue to run in the background on the StarRocks cluster until it hits the

statistic_collect_query_timeout

limit. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • faq/loading/Loading_faq.md • sql-reference/System_variable.md

ansh.gupta2

10/01/2026, 9:57 AM

@Rocky | | 12082 | hive | ucp | user_level_sends | ALL | FULL | ONCE | FAILED | 2026-09-30 063230 | 2026-10-01 063652 | {table_format=hive, partition_count=1348, column_count=14, scan_bytes_cap=2147483648, scan_files_cap=1000, scan_rows_cap=10000000} | Analyze job timeout exceeded again let increase timeout to 60 days]

Rocky

10/01/2026, 9:57 AM

Setting the timeout to 60 days is not recommended and likely won't solve the underlying problem. Your second job failed after exactly 24 hours (86,662 seconds), which implies you likely hit a 24-hour limit or already increased the timeout once. If a job analyzing only 14 columns and ~1,300 partitions takes more than 24 hours to scan the data, there is a performance bottleneck that a longer timeout will not fix. 1. The 60-Day Timeout (Not Recommended) While you can technically set

statistic_collect_query_timeout

to a very high number (it is a 64-bit integer in the backend), StarRocks session variables often have a ceiling (like

query_timeout

which is capped at 3 days). Even if allowed, a 60-day stats job will likely be killed by a cluster restart, FE leader change, or network instability before it finishes. 2. Why is it taking 24 hours? The

Properties

in your log show: *

scan_bytes_cap=2147483648

(2GB) *

scan_rows_cap=10000000

(10M rows) *

scan_files_cap=1000

If the job is limited to scanning only 2GB or 10M rows, it should finish in minutes, not 24 hours. If it's taking 24 hours, it means: * Metadata Bottleneck: It might be spending the entire time trying to list 1,348 partitions from Hive Metastore or S3. * Low Parallelism: The default

statistic_collect_parallel

is often 1. This means it's scanning the remote data one thread at a time. * Small Files: If your Hive table has thousands of tiny files, the I/O overhead is massive. 3. Better Solutions than a 60-Day Timeout A. Increase Parallelism (Crucial) Force the job to use more resources to finish faster:

Copy code

sql
-- Use 16 threads for the statistics collection
SET statistic_collect_parallel = 16;

B. Check for Metadata Latency If listing partitions is the bottleneck, ensure your Hive Metastore is responsive. You can also try to analyze specific partitions if the table is partitioned by time:

Copy code

sql
ANALYZE FULL TABLE hive.ucp.user_level_sends PARTITIONS (p_date='2026-09-30');

C. Adjust the "Caps" The properties show the system is capping the scan at 2GB. For a

FULL

analyze to be accurate on large tables, you might need to allow it to see more data, but paradoxically, this will take even longer if parallelism is low. Recommended Action: Instead of 60 days, try setting the timeout to 48 hours and increase parallelism:

Copy code

sql
-- Set timeout to 48 hours
ADMIN SET FRONTEND CONFIG ("statistic_collect_query_timeout" = "172800");

-- Increase parallelism before running the command
SET statistic_collect_parallel = 16;
ANALYZE FULL TABLE hive.ucp.user_level_sends;

If it still hits 24 hours and fails, please check the FE log (

fe.log

fe.warn.log

) for "Slow query" or "RPC timeout" messages during that window. It is likely that the connection to your Hive source is timing out or hanging. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • faq/Sql_faq.md References • using_starrocks/Cost_based_optimizer.md • administration/management/monitoring/metrics.md

ansh.gupta2

10/01/2026, 9:59 AM

@Rocky SET statistic_collect_parallel = 16; does setting this too high make it slower?

Rocky

10/01/2026, 10:00 AM

Yes, setting

statistic_collect_parallel

too high can eventually make the process slower and potentially impact the stability of your cluster. While increasing this value from the default of

is the correct way to speed up slow

ANALYZE

tasks, there are three main reasons why a very high value (e.g., 64 or 128) can backfire: 1. External Metadata Throttling (The "External" Risk) Since you are analyzing Hive/Delta Lake tables, StarRocks must communicate with your Hive Metastore (HMS) or Glue and your remote storage (S3/HDFS/ADLS). * High Parallelism = High Request Rate: If you set this to 64, StarRocks might attempt to list thousands of partitions and files simultaneously. * The Result: Your Hive Metastore may become unresponsive, or your object storage (S3) might start throttling requests. When the external service slows down or retries are triggered, the entire

ANALYZE

job will hang or fail, making it much slower than a moderate setting. 2. BE Resource Contention Statistics collection is essentially a large

SELECT

query that scans and aggregates data. * CPU & Memory: Each parallel task consumes CPU cycles and memory on your BE nodes. If set too high, it will compete with your production queries for resources. * Context Switching: Beyond a certain point (usually related to the number of CPU cores on your BEs), adding more threads introduces "context switching" overhead where the BE spends more time managing tasks than actually processing data. 3. FE Scheduling Overhead The Frontend (FE) has to manage the state and scheduling for every parallel fragment. For tables with 100+ columns and thousands of partitions, setting a massive parallelism creates a "storm" of small tasks that can cause the FE's JVM memory or CPU to spike. Recommended Approach Instead of jumping to a massive number, follow this "staircase" approach: 1. Start at 8 or 16: This is usually the "sweet spot" for large external tables. It provides a significant speedup without overwhelming the Hive Metastore. 2. Monitor BE/HMS: Check the CPU usage on your StarRocks BEs and the latency of your Hive Metastore. If they look healthy, you can try increasing it to 32. 3. Use Targeted Analyze: Instead of increasing parallelism to 100+, it is almost always more efficient to limit the columns you analyze:

Copy code

sql
    -- Analyze only the columns used in JOINs/WHERE clauses
    ANALYZE FULL TABLE hive.ucp.user_level_sends (col1, col2, col3)
    PROPERTIES("statistic_collect_parallel" = "16");

Summary: If

didn't solve it, the bottleneck is likely the metadata listing speed from Hive or the 60-minute timeout you had earlier, not a lack of internal parallelism. Stick to 8–16 and focus on increasing the

statistic_collect_query_timeout

. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • administration/management/monitoring/metrics.md • faq/Sql_faq.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md

ansh.gupta2

10/03/2026, 6:02 AM

@Rocky mysql> SHOW CATALOGS; +-----------------+-----------+------------------------------------------------------------------+ | Catalog | Type | Comment | +-----------------+-----------+------------------------------------------------------------------+ | default_catalog | Internal | An internal catalog contains this cluster's self-managed tables. | | delta_dev | Deltalake | NULL | | delta_prod | Deltalake | NULL | | hive | Unified | NULL | +-----------------+-----------+------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM default_catalog.information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql> SET CATALOG defautl_catalog; ERROR 5078 (42000): Unknown catalog 'defautl_catalog' mysql> SET CATALOG default_catalog; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM default_catalog.information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | statistics | | information_schema | | sys | +--------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql> SELECT * FROM information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql>

Rocky

10/03/2026, 6:02 AM

The error

Warehouse default_warehouse is not available

indicates that your StarRocks cluster is running in shared-data (Cloud Native) mode, where compute resources are managed via Warehouses. Even for metadata queries like

SELECT * FROM information_schema.analyze_status

, StarRocks requires an active compute warehouse to process the results. The

default_warehouse

is either not started, suspended, or has no active Compute Nodes (CNs) assigned to it. Step 1: Check Warehouse Status Run this command to see the state of your warehouses:

Copy code

sql
SHOW WAREHOUSES;

Look for the

State

column. If it says

SUSPENDED

or if there are no warehouses listed, this is why your query is failing. Step 2: Use an Available Warehouse If you see another warehouse that is

RUNNING

, switch to it for your session:

Copy code

sql
-- Replace 'your_warehouse_name' with a running warehouse from the SHOW command
SET WAREHOUSE = 'your_warehouse_name';

Step 3: Resume the Default Warehouse If

default_warehouse

is suspended, you can try to resume it:

Copy code

sql
ALTER WAREHOUSE default_warehouse RESUME;

Why did
SHOW CATALOGS
work but
SELECT
fail? * SHOW
commands: These are metadata-only operations that can often be handled entirely by the Frontend (FE) leader. * SELECT
commands: Even when querying internal system tables like

information_schema

, StarRocks treats this as a distributed query that must be executed by Compute Nodes (BE/CN). If no warehouse is active to provide those compute resources, the query is rejected. Summary of Commands to Fix:

Copy code

sql
-- 1. See what you have
SHOW WAREHOUSES;

-- 2. If it's suspended, start it
ALTER WAREHOUSE default_warehouse RESUME;

-- 3. Verify it's active
SELECT current_warehouse();

-- 4. Run your analyze status check again
SELECT * FROM information_schema.analyze_status WHERE status IN ('RUNNING','PENDING');

Note: If

SHOW WAREHOUSES

returns an empty set or

default_warehouse

won't start, you should check with your cluster administrator to ensure that Compute Nodes (CN) are correctly deployed and joined to the cluster. References • sql-reference/sql-functions/utility-functions/current_warehouse.md • quick_start/shared-data.md • faq/shared_data_faq.md • sql-reference/information_schema/warehouse_queries.md • sql-reference/sql-statements/cluster-management/storage_volume/SET_DEFAULT_STORAGE_VOLUME.md • sql-reference/information_schema/warehouse_metrics.md References • sql-reference/sql-statements/backup_restore/SHOW_REPOSITORIES.md • sql-reference/sql-statements/Database/SHOW_DATABASES.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • sql-reference/information_schema/warehouse_metrics.md • sql-reference/sql-statements/Database/SHOW_DATA.md • faq/Sql_faq.md

ansh.gupta2

10/03/2026, 6:03 AM

@Rocky mysql> SHOW CATALOGS; +-----------------+-----------+------------------------------------------------------------------+ | Catalog | Type | Comment | +-----------------+-----------+------------------------------------------------------------------+ | default_catalog | Internal | An internal catalog contains this cluster's self-managed tables. | | delta_dev | Deltalake | NULL | | delta_prod | Deltalake | NULL | | hive | Unified | NULL | +-----------------+-----------+------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM default_catalog.information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql> SET CATALOG defautl_catalog; ERROR 5078 (42000): Unknown catalog 'defautl_catalog' mysql> SET CATALOG default_catalog; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM default_catalog.information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | statistics | | information_schema | | sys | +--------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. mysql> SELECT * FROM information_schema.analyze_status WHERE status in ('RUNNING','PENDING'); ERROR 5904 (42000): Warehouse default_warehouse is not available. only one FE is there no CN nodes

Rocky

10/03/2026, 6:03 AM

If you have no CN nodes, the cluster has no compute resources to execute your SQL. Even queries against system tables like

information_schema

are treated as distributed queries and must be executed by a compute layer. The error

Warehouse default_warehouse is not available

confirms your cluster is running in Shared-Data (Cloud Native) mode. In this mode, the "Warehouse" is a logical group of Compute Nodes (CNs). Without CNs, the warehouse is effectively "empty" and unavailable. Why you need CN nodes 1. Query Execution: The FE (Frontend) only handles metadata, parsing, and planning. It does not execute the query. It needs CN nodes to actually read the data and process the results. 2. Statistics Collection: The

ANALYZE

job that failed earlier requires compute power to scan your Hive/Delta tables and calculate statistics. If your CNs went offline or were never joined, the job cannot run. How to verify and fix Run this command to check the status of your compute layer:

Copy code

sql
SHOW COMPUTE_NODES;

* If the list is empty, you have not added any CN nodes to the cluster. * If they are listed but

Alive

false

, the CN processes have crashed or cannot communicate with the FE. To fix this, you must: 1. Deploy CN nodes: Start the StarRocks CN process on your worker machine(s). 2. Add them to the cluster:

Copy code

sql
    -- Replace <cn_ip> with the actual IP of your CN node
    ALTER SYSTEM ADD COMPUTE NODE "<cn_ip>:9050";

Once at least one CN node is added and shows

Alive: true

SHOW COMPUTE_NODES

, your

SELECT

queries and

ANALYZE

jobs will begin working. If you are using Shared-Nothing (Standard) Mode If you intended to use standard BE (Backend) nodes instead of CN nodes, you would use:

Copy code

sql
SHOW BACKENDS;

However, the "Warehouse" error message is a specific indicator that your cluster is configured for Cloud Native/Shared-Data mode, which strictly requires CNs. References • deployment/deploy_manually.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_COMPUTE_NODES.md • quick_start/shared-data.md • faq/shared_data_faq.md • sql-reference/sql-functions/utility-functions/current_warehouse.md • administration/management/monitoring/metrics.md References • deployment/deploy_manually.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_COMPUTE_NODES.md • sql-reference/information_schema/be_metrics.md • sql-reference/information_schema/be_tablets.md • sql-reference/information_schema/be_logs.md

ansh.gupta2

10/12/2026, 12:22 PM

@Rocky property automaticall decide sample or full as per size like 100 gb in external delta table

Rocky

10/12/2026, 12:22 PM

For external tables (including Delta Lake), there isn't a single property that automatically switches between

SAMPLE

and

FULL

based on the 100 GB size, primarily because StarRocks does not support Sampled statistics collection for external tables yet (as of v3.3/v3.4). Only Full collection is supported for external data sources. However, there is a specific configuration that uses a 100 GB threshold to decide when to skip full collection to protect your cluster: 1. The 100 GB Threshold Property The property you likely encountered is statistics_max_full_collect_data_size
. * Default Value: 100 (GB) * Behavior: During automatic collection, if the data size of a partition exceeds this threshold, the system will discard the Full collection attempt. * For External Tables: Since Sampled collection is not supported for external tables, if the partition exceeds this 100 GB limit, StarRocks will simply not collect statistics for that partition/table automatically, rather than switching to sampling. 2. How to handle "Large" Delta Tables (>100 GB) Since you cannot use the automatic "sample vs full" switch for external tables, you have two options for your 100 GB Delta table: Option A: Force Manual Full Collection If you want statistics regardless of the size, you must run a manual

ANALYZE

task. Manual tasks ignore the 100 GB safety threshold:

Copy code

sql
ANALYZE FULL TABLE delta_catalog.db_name.table_name;

Option B: Adjust the Threshold If you want the automatic background job to process larger partitions, you can increase the threshold in your FE configuration (

fe.conf

Copy code

properties
# Increase to 200 GB if your environment can handle the scan
statistics_max_full_collect_data_size = 200

Summary of Support for External Tables (v3.3+) Feature | Supported for External (Delta/Hive/Iceberg)? Manual FULL Analyze | ✅ Yes Manual SAMPLE Analyze | ❌ No Automatic FULL Analyze | ✅ Yes (via

CREATE ANALYZE

) Query-Triggered Analyze | ✅ Yes (Since v3.4.0) Sampled Collection | ❌ No Recommendation: For a 100 GB Delta table, if you find

ANALYZE

is too slow or resource-heavy, instead of sampling, you should limit the columns being analyzed to only those used in your joins and filters:

Copy code

sql
CREATE ANALYZE TABLE delta_prod.db.large_table (important_col1, important_col2);

References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md • administration/management/FE_configuration.md References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md

ansh.gupta2

10/12/2026, 12:24 PM

@Rocky sample statisics not supported is that only for default catalog

Rocky

10/12/2026, 12:24 PM

Actually, it is the other way around: * Internal Tables (
default_catalog
): Support both

FULL

and

SAMPLE

statistics collection. * External Tables (Hive, Delta Lake, Iceberg, Hudi): Do NOT support

SAMPLE

collection. They currently only support

FULL

collection or

HISTOGRAM

collection. Summary of Support Catalog Type | Manual FULL | Manual SAMPLE | Automatic FULL default_catalog
(Internal) | ✅ Supported | ✅ Supported | ✅ Supported External Catalogs (Delta, Hive, etc.) | ✅ Supported | ❌ Not Supported | ✅ Supported Why is
SAMPLE
not supported for external tables? For internal tables, StarRocks can quickly sample data from its own optimized storage format. For external tables (like those in Delta Lake or Hive), StarRocks would have to scan the remote files to perform the sampling. Currently, the StarRocks optimizer is designed to perform a

FULL

scan (often only on specific partitions or columns) to ensure the accuracy of the statistics for external data. How to handle large external tables without `SAMPLE`: If your Delta Lake table is too large (e.g., the 100 GB mentioned earlier) and you are worried about the performance of a

FULL

analyze: 1. Analyze specific columns: Instead of the whole table, only analyze the columns used in JOINs, WHERE clauses, or GROUP BYs.

Copy code

sql
    -- Only collects stats for these 2 columns
    ANALYZE TABLE delta_prod.db.large_table (user_id, event_date);

2. Query-Triggered Analyze (v3.4+): If you are on StarRocks v3.4 or higher, you can enable

enable_query_trigger_analyze

. StarRocks will automatically collect statistics in the background for only the columns and partitions that your queries actually touch. 3. Increase the safety threshold: If you want automatic background collection to work for tables larger than 100 GB, increase this FE configuration:

Copy code

properties
    statistics_max_full_collect_data_size = 200 -- Set to 200GB

In short: If you are trying to use the

SAMPLE

keyword on a Delta Lake table, you will get an error. You must use

FULL

(which is the default if you just run

ANALYZE TABLE ...

). References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md References • using_starrocks/Cost_based_optimizer.md

ansh.gupta2

10/12/2026, 12:25 PM

@Rocky in newer version also? but u told me earlier in delta it is supported??

Rocky

10/12/2026, 12:25 PM

I want to clarify this to ensure there is no confusion. There is a distinction between running the
ANALYZE SAMPLE
command and StarRocks using statistics from Delta Lake. 1. Is
ANALYZE SAMPLE
supported for Delta Lake? No. In all current versions (v3.1 through v3.4), the

ANALYZE SAMPLE

command is not supported for external tables (Delta Lake, Hive, Iceberg). If you try to run it, you will receive an error. Only

FULL

and

HISTOGRAM

collection are supported via the

ANALYZE

command for external data. 2. Why might you have heard "Delta stats are supported"? There are two reasons why "Delta statistics support" is often discussed as a new feature: * Metadata-based Stats (v3.1+): StarRocks can automatically read native statistics (min/max, null counts, row counts) directly from the Delta Lake JSON/Parquet logs. This is extremely fast because it doesn't scan the data files. This happens automatically and doesn't require an

ANALYZE

command. * Official
ANALYZE
Support (v3.3+): v3.3.0 was the first version to officially support the

ANALYZE TABLE

command for Delta Lake. However, even with this new support, it only allows

FULL

collection. 3. How to handle your 100 GB table? Since the "auto-switch to sample" property (

statistic_max_full_collect_data_size

) only works for internal tables, here is how to handle a large Delta table: * Option A: Rely on Metadata Stats (Recommended): If your Delta Lake has its own stats (common if it was written by Spark), StarRocks will use them automatically. You don't need to run

ANALYZE

. * Option B: Analyze Specific Columns: Instead of the whole table, analyze only the columns you query frequently. This is much faster than a full table scan:

Copy code

sql
    ANALYZE TABLE delta_prod.db.large_table (id, event_time, user_id);

* Option C: Query-Triggered Analyze (v3.4+): If you are on v3.4, enable

enable_query_trigger_analyze

. StarRocks will see which columns you actually use in your queries and collect stats for just those columns in the background. Summary Table Feature | Internal Table | Delta Lake (External) ANALYZE FULL
| ✅ Supported | ✅ Supported (v3.3+) ANALYZE SAMPLE
| ✅ Supported | ❌ Not Supported Read Native Metadata Stats | N/A | ✅ Supported (v3.1+) Auto-switch (100GB) | ✅ Fallback to Sample | ❌ Skips or requires Full References * Gather statistics for CBO — See "Limits" section confirming Sampled collection is not supported for external tables. * Delta Lake Catalog — Feature support breakdown by version. References • using_starrocks/Cost_based_optimizer.md • loading/Loading_data_template.md

ansh.gupta2

10/12/2026, 12:27 PM

@Rocky but in statisics i saw sample

Rocky

10/12/2026, 12:28 PM

I understand the confusion. You likely saw "SAMPLE" in a command output (like

SHOW STATS META

SHOW ANALYZE STATUS

) or in the description of a configuration property. To clarify, here is exactly why you might have seen "sample" and how it actually works for Delta Lake: 1. Where you likely saw "Sample" * The
Type
Column: When you run

SHOW STATS META

SHOW ANALYZE STATUS

, there is a Type column that can display

FULL

SAMPLE

, or

HISTOGRAM

. This is a generic column for all table types. For Delta tables, however, it will currently only show

FULL

HISTOGRAM

. * FE Configuration Description: The property

statistic_max_full_collect_data_size

(default 100 GB) says in its description: "If the total size exceeds this value, then sampled collection is performed instead of full." * The Catch: This description applies to Internal (StarRocks) tables. * For External Tables (Delta Lake/Hive): Because StarRocks does not yet support the

SAMPLE

scan method for external data, it cannot "fall back" to a sample. If the table is larger than the limit, the automatic job will simply not collect statistics for that table to avoid crashing your FE/BE with a massive scan. 2. Does StarRocks use "Samples" from Delta Lake? No. StarRocks uses two things for Delta Lake statistics: 1. Metadata Statistics (Automatic): StarRocks reads the native statistics (min/max, null counts) directly from the Delta Lake JSON logs. This is very fast and "free." 2. Manual/Automatic FULL Analyze: If you need more detailed statistics (like Cardinality/NDV), you must run

ANALYZE TABLE

. Currently, this only supports
FULL
scans. 3. Summary of Support (Newer Versions 3.3+) Feature | Internal Table | Delta Lake (External) Manual
ANALYZE SAMPLE
| ✅ Supported | ❌ Not Supported Auto-switch (100GB limit) | ✅ Switches to Sample | ❌ Skips/Discards task Query-Triggered Analyze | ✅ Supported | ✅ Supported (since v3.4) What should you do for a 100 GB Delta Table? Since you cannot use "sample," if you want better statistics than the basic metadata: 1. Analyze only specific columns: Instead of

ANALYZE TABLE

, specify only the columns used in joins:

Copy code

sql
    ANALYZE TABLE delta_catalog.db.table (col1, col2);

2. Increase the safety limit: If you want the background job to run a

FULL

analyze even though it's 100 GB, increase the FE config:

Copy code

properties
    # Change from 100GB to 200GB (in fe.conf)
    statistic_max_full_collect_data_size = 214748364800

In short: You saw "sample" in the documentation or system columns because the framework supports it, but the Delta Lake connector specifically is currently limited to

FULL

collection or native metadata. References • using_starrocks/Cost_based_optimizer.md (See Limits section: "Sampled collection is not supported [for external tables]") • administration/management/FE_configuration.md (Search for

statistic_max_full_collect_data_size

) References • using_starrocks/Cost_based_optimizer.md • data_source/catalog/deltalake_catalog.md • sql-reference/sql-statements/cbo_stats/SHOW_META.md

ansh.gupta2

10/12/2026, 12:34 PM

@Rocky Option A: Rely on Metadata Stats (Recommended): If your Delta Lake has its own stats (common if it was written by Spark), StarRocks will use them automatically. You don't need to run

ANALYZE

. will that be that much effective

Rocky

10/12/2026, 12:35 PM

Relying on Option A (Metadata Stats) is a great first step, but whether it is "effective enough" depends on your query complexity. Here is the breakdown of what the optimizer gets from Delta Lake metadata versus what it gets from a full `ANALYZE`: 1. What Metadata Stats provide (Fast & Automatic) StarRocks reads these directly from the Delta Lake transaction logs. They are "effective" for: * Data Volume Estimation: Knowing the

row_count

helps the optimizer decide whether to use a Broadcast Join or a Shuffle Join. * Partition Pruning: If your query filters on a column with `min`/`max` values in the metadata, StarRocks can skip entire files without reading them. * Null Handling: Knowing the

null_count

helps with join and filter optimization. 2. What is MISSING (Where
ANALYZE
is better) The native metadata is usually missing NDV (Number of Distinct Values). * Why NDV matters: Without NDV, the optimizer doesn't know the cardinality of a column. For a 100 GB table, if you join on

user_id

, the optimizer needs to know if there are 1,000 users or 100 million users to pick the most efficient "Build" side for the join. * Skewed Data: Metadata doesn't show data distribution. If 90% of your rows have the same value, the optimizer will only know that if you run

ANALYZE

NDV

from an

ANALYZE

for the join keys. Data with heavy skew | No | You need a Histogram (via

ANALYZE

) to avoid performance bottlenecks. My Recommendation for 100 GB: 1. Start with Metadata: Do nothing and run your query. If it's fast, you're done. 2. If slow, check the Plan: Run

EXPLAIN [Query]

. If the "cardinality" estimates look wildly wrong (e.g., it thinks a 100GB table has 100 rows), metadata isn't enough. 3. Targeted Analyze: Don't analyze the whole 100 GB table. Only analyze the Join Keys:

Copy code

sql
    -- Fast because it only scans the specific column needed for the join
    ANALYZE TABLE delta_catalog.db.my_table (join_key_column);

Note for v3.4+: If you are on v3.4 or newer, you can enable

enable_query_trigger_analyze = true

. StarRocks will detect when a query is missing

NDV

and will automatically trigger a background

ANALYZE

for just those specific columns. References • using_starrocks/Cost_based_optimizer.md • data_source/catalog/deltalake_catalog.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md References • using_starrocks/Cost_based_optimizer.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/iceberg_meta_table.md • data_source/catalog/deltalake_catalog.md

ansh.gupta2

10/13/2026, 4:31 AM

@Rocky in CN when memory exceed limit which query will fail most memory taken or least memory taken?

Rocky

10/13/2026, 4:31 AM

When a Compute Node (CN) or Backend (BE) memory exceeds its limit, StarRocks uses a Query Killer mechanism to protect the node from crashing. The query that will fail is typically the one taking the most memory. Here is how the logic works: 1. Most Memory Taken (The "Big Query" Killer) When the node's total memory usage reaches its high-water mark (defined by

mem_limit

, usually 90% of RAM), StarRocks identifies the query currently consuming the largest amount of memory on that node and cancels it. * Why? Killing the largest memory consumer is the fastest way to reclaim a significant amount of memory and stabilize the node. It prevents the node from going into an Out-Of-Memory (OOM) state that would crash the entire process. * The Result: You will see an error like:

Memory of process exceed limit. [...] Mem usage has exceed the limit of BE

. 2. The "Last Straw" Query Sometimes, the query that fails is simply the one that tried to allocate more memory at the exact moment the limit was hit. Even if it isn't the absolute largest consumer, if its specific request for more memory (e.g., for a hash table or a shuffle buffer) cannot be satisfied because the process is at its limit, that query will fail immediately. 3. Resource Group Limits If you are using Resource Groups, the logic is even more specific: * If a specific Resource Group hits its own

mem_limit

, StarRocks will kill the largest query within that specific group, leaving queries in other groups untouched. Summary * Will the least memory query fail? No, StarRocks avoids killing small queries because they don't free up enough memory to solve the problem. * Will the most memory query fail? Yes. This is the primary target for the system to recover as much memory as possible. How to prevent this: * Increase `query_mem_limit`: If you have one specific big query that keeps failing, you can set

SET query_mem_limit = <large_value>

for that session, provided the physical node has enough RAM. * Enable Spill to Disk: In StarRocks v3.0+, you can enable

enable_spill = true

. This allows large queries to write intermediate data to disk instead of memory, preventing them from being killed for memory exhaustion. References • administration/management/resource_management/Memory_management.md • administration/management/monitor_manage_big_queries.md • administration/management/resource_management/spill_to_disk.md References • administration/management/monitor_manage_big_queries.md • administration/management/resource_management/Query_management.md

ansh.gupta2

10/13/2026, 4:40 AM

@Rocky you are wrong memory When a StarRocks Compute Node (CN) runs out of memory, queries can fail. There are a few common scenarios and error messages you might encounter: 1. Single query memory limit exceeded: ◦ Error:

Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit.

◦ Reason: A specific query has consumed more memory than allowed by the

exec_mem_limit

session variable. ◦ Solution: Increase the

exec_mem_limit

for that session or globally, but be cautious as this can impact other queries. 2. Query pool memory limit exceeded: ◦ Error:

Mem usage has exceed the limit of query pool

◦ Reason: The total memory consumed by queries within a specific query pool has exceeded its allocated limit. ◦ Solution: Optimize the SQL queries to reduce their memory footprint, or adjust the memory limits for the query pool if appropriate. 3. CN total memory limit exceeded: ◦ Error:

Mem usage has exceed the limit of BE

(Note: In shared-data architecture, CNs inherit many BE parameters and error messages might still refer to BE). ◦ Reason: The overall memory usage of the CN node has reached its system-wide limit. This can be due to a combination of many concurrent queries, or a few very memory-intensive queries. ◦ Solution: Analyze the memory usage of the CN (using tools like

curl -XGET -s <http://CN_IP>:CN_HTTP_PORT/metrics | grep "^starrocks_be_.*_mem_bytes\\|^starrocks_be_tcmalloc_bytes_in_use"

and

curl -XGET -s <http://CN_IP>:CN_HTTP_PORT/mem_tracker

). This will help identify what is consuming the most memory. You might need to: ▪︎ Reduce concurrency. ▪︎ Optimize memory-intensive queries. ▪︎ Add more CN nodes to distribute the workload. ▪︎ Increase the physical memory of the CN nodes. In general, when a CN's memory fills up, the query that attempts to allocate memory beyond the available limit will fail. It's not necessarily the largest memory-taking query that fails, but rather the one that pushes the system over the edge at that specific moment. However, queries that are inherently very memory-intensive are more likely to be the ones that trigger these limits. query that attempts to allocate memory beyond the available limit will fail it's not most memory

Rocky

10/13/2026, 4:40 AM

You are absolutely correct, and I appreciate the correction. I was mistaken in my previous explanation about the system automatically "hunting" for the largest query to kill when the global limit is reached. In StarRocks, memory management is reactive at the point of allocation. Here is the precise behavior: 1. The "Last Straw" Rule When a Compute Node (CN) or Backend (BE) total memory usage is near its limit (usually 90% of RAM), every query running on that node continues to request small chunks of memory as it processes data. * The Trigger: The moment a query (any query) attempts to allocate even a few bytes that would push the total process memory over the

mem_limit

, the Memory Tracker denies that request. * The Result: The specific query that made that "last straw" request fails immediately with the error:

Memory of process exceed limit. [...] Mem usage has exceed the limit of BE

. 2. Why it’s not necessarily the "Largest" query * Timing vs. Size: A small query that starts or attempts an allocation at the exact millisecond the limit is hit will fail, even if a massive query is sitting right next to it taking up 80% of the memory. * Likelihood: While the "last straw" can be any query, memory-intensive queries (like those doing large joins or aggregations) are much more likely to fail simply because they are requesting memory more frequently and in larger chunks, increasing their "surface area" for hitting the limit. 3. Summary of Failure Scenarios (as you noted) Scenario | Error Message | Logic Single Query Limit |

Mem usage has exceed the limit of single query

| Hits the per-query

exec_mem_limit

. Only this query is affected. CN/BE Total Limit |

Mem usage has exceed the limit of BE

| Total process memory hit. The allocating query (last straw) fails. Query Pool/Resource Group |

Mem usage has exceed the limit of query pool

| The group's specific quota is hit. The query making the next allocation within that group fails. How to accurately diagnose To see exactly which query was the "last straw" and how much it was trying to take, you can check the BE/CN logs (

<http://be.INFO|be.INFO>

be.WARN

). You will see a log entry like this:

Memory of process exceed limit. try consume: [X] Used: [Y], Limit: [Z]. Mem usage has exceed the limit of BE, query_id=[ID]

* try consume
: The amount of memory the "victim" query was trying to grab when it failed. * Used
: The total memory used by the node at that moment. Thank you again for the correction—you are right that it is a common misunderstanding to think the system proactively selects a victim based on size. It is indeed the query that pushes the system "over the edge" at that specific moment. References • administration/management/resource_management/Memory_management.md • administration/management/monitor_manage_big_queries.md • faq/Sql_faq.md References • administration/management/resource_management/Query_management.md • administration/management/monitor_manage_big_queries.md • administration/management/resource_management/Memory_management.md

2 Views

Open in Slack

Previous Next