@Rocky I need your help brainstorming around setting up a StarRocks cluster (4.1) for highly concurrent traffic and low query latency using using the remote Iceberg REST catalog. More details to follow shortly.

StarRocks 4.1 introduces several breakthrough features specifically designed to handle high-concurrency, multi-tenant traffic on data lakes like Iceberg. To achieve low latency and high concurrency with a remote Iceberg REST catalog, I recommend focusing on these four pillars: 1. Leverage "Adaptive Layout" (New in 4.1) StarRocks 4.1 addresses the "static layout" problem where performance degrades as specific tenants grow. * Automatic Tablet Splitting: In shared-data (cloud-native) clusters, StarRocks now observes data distribution changes at runtime and automatically adjusts the physical layout (e.g., splitting tablets for hot tenants). This prevents resource hotspots and keeps p95 latency stable under high concurrency. * Recommendation: Use the shared-data architecture for this deployment to benefit from this automatic load balancing and cost-effective scaling of compute pods. 2. Optimize the Iceberg REST Catalog Since you are using a REST catalog (likely Polaris or Tabular), metadata retrieval is a potential bottleneck. * Vended Credential Caching: 4.1 introduces improved caching for vended credentials in REST catalogs. This prevents the "AWS Rate Exceeded" errors common in high-concurrency environments by refreshing credentials on access rather than bypassing the cache. * Metadata Polling: Ensure

enable_background_refresh_connector_metadata

is

true

(default). This allows StarRocks to poll the REST catalog periodically (default 10 mins) to refresh the FE's internal manifest cache, keeping queries "local" to the FE for planning. * Distributed Planning: For tables with massive numbers of manifest files, ensure

plan_mode

is set to

distributed

(or

auto

) to spread the metadata parsing work across your BE/CN nodes. 3. Data Latency & Concurrency Acceleration * Data Cache (on SSDs): This is non-negotiable for low latency. Map your BE/CN local SSDs to the Data Cache. Since you are using Iceberg, the first query will fetch from remote storage, but subsequent concurrent queries will hit the SSD cache. * Asynchronous Materialized Views (MV): For "extreme" concurrency (thousands of QPS), raw Iceberg scans might still hit storage bottlenecks. Use StarRocks MVs to pre-aggregate or join Iceberg data into StarRocks' native storage. The Transparent Rewrite feature ensures your users' SQL remains unchanged while StarRocks automatically routes them to the MV. * Query Queue & Resource Groups: Use StarRocks' Resource Groups to isolate traffic. You can set a

concurrency_limit

and

cpu_core_limit

for specific tenants or apps to ensure one heavy user doesn't spike latency for others. 4. Handling Semi-Structured Data * Native Variant Type: If your Iceberg data contains JSON or dynamic fields, use the new Variant type in 4.1. Unlike the old

JSON

type, the Variant type in 4.1 is integrated into the vectorized engine, allowing for predicate pushdown and high-performance execution directly on semi-structured Iceberg data. Next Steps for Brainstorming: * What is your target QPS and p99 latency requirement? * Are your queries mostly point-lookups, range scans, or heavy aggregations? * Do you have specific security requirements (e.g., OAuth2/JWT for the REST catalog)? References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md

@Rocky Here are the details: • JOIN-heavy queries (7-10 tables joins are not rare) • We are running on AWS (m6a.8xlarge, amd64) • Latency SLO would be under 3s • Cost is not really an issue (within reason) • Read-only queries • Compaction is done separately and a spark job is running every 1h • For now, here are the things we want to experiment with: • #1 – Cluster Provisioning— Scaling up/out while keeping an FE-to-BE ratio improves query latency under concurrent load. Goal: find cost-to-performance sweet spot. • #2 – Shared-Nothing vs. Shared-Data— Shared-Data architecture (with cache) is more performant than Shared-Nothing for remote tables. • #3 – FE Observer Nodes— Past 3 FE nodes, scaling with Observer nodes (vs. Follower) is better for performance and concurrency by avoiding Raft consensus overhead. • #4 – Prepared Statements— Using prepared statements improves execution efficiency (available since StarRocks v3.2). • #5 – Data Spill— Enabling`enable_spill`prevents OOM and improves stability under load, but at the cost of higher individual query latency due to disk IO. • #6 – Query Parallelism— Tuning`pipeline_dop`and`connector_io_tasks_per_scan_operator`significantly impacts concurrent traffic handling. • #7 – Distributed Planning— Switching`plan_mode`to`distributed`or`local`(vs. default`auto`) could impact query performance, especially with many manifest files. • #8 – Lean Read-Path— Stripping non-essential write-on-read side effects (bookkeeping, profiling, auditing) improves read latency/throughput under concurrent load. Has 4 variants (Control + 3 treatments varying datacache mode). Anything else worth looking into?

Your experimental plan for StarRocks 4.1 is very well-aligned with the architecture's strengths. Based on the 4.1 capabilities and your Iceberg REST catalog requirement, here is a detailed breakdown and additional suggestions for your brainstorming. 1. Validation of Your Experiments * #1 Cluster Provisioning (FE-to-BE Ratio): For high concurrency, the FE is often the bottleneck (SQL parsing/planning). On

m6a.8xlarge

, a 1:5 or 1:8 FE-to-CN ratio is common. Monitor the FE's CPU and Java Heap; if planning latency spikes under load, scale the FEs. * #2 Shared-Data vs. Shared-Nothing: Validated. Internal benchmarks show Shared-Data (with Data Cache) can be 1.2x faster than Shared-Nothing for data lake queries because it eliminates the overhead of managing local replicas while providing SSD-speed access via the unified Data Cache. * #3 FE Observer Nodes: Validated. Use 3 Followers (for HA/Raft) and then scale with Observers to handle the read-only REST API and SQL planning traffic without impacting the Leader's metadata operations. * #4 Prepared Statements: Essential for high-concurrency "point lookups" or simple queries. In 4.1, this drastically reduces the FE overhead per request. * #5 Data Spill:

enable_spill

is a safety net. For an SLO < 3s, you should aim to avoid spilling. Use Resource Groups to kill queries that exceed memory limits rather than letting them spill to disk, as disk I/O on

m6a

(EBS) will likely break your 3s SLO. * #6 & #7 Parallelism & Planning: * Set

plan_mode = 'distributed'

for your Iceberg tables if they have thousands of manifest files. * Tuning

connector_io_tasks_per_scan_operator

is critical; however, in 4.1,

enable_connector_adaptive_io_tasks

(default

true

) usually handles this. Only manual-tune if you see uneven BE CPU usage. * #8 Lean Read-Path: This likely refers to minimizing "write-on-read" overhead. * Recommendation: Disable

enable_profile

and

enable_audit_log

(or set to

SLOW_QUERY

only) in production to save CPU cycles. * DataCache Variants: Experiment with

enable_datacache_async_populate_mode

. Setting this to

false

(synchronous) ensures data is cached on the first read, which is better for "warming" but adds latency to the very first query. ────────── 2. Missing "Heavy Artillery" for 7-10 Table Joins Given your join-heavy workload, these three 4.1 features are arguably more impactful than the ones above: A. Asynchronous Materialized Views (The "Silver Bullet") For 7-10 table joins on Iceberg, raw scans will eventually hit AWS S3/REST API throughput limits. * Strategy: Create an Async MV in StarRocks that pre-joins these tables. * Transparent Rewrite: Your users still query the Iceberg catalog, but StarRocks' optimizer automatically rewrites the query to hit the local, pre-joined MV. * Benefit: Reduces 10 remote scans to 1 local SSD scan. Latency will drop from seconds to milliseconds. B. Global Runtime Filters (RF) In massive joins, RFs are the primary mechanism StarRocks uses to prune data early. * Tip: Ensure

enable_global_runtime_filter = true

. * Experiment: Check your query profile for

RuntimeFilterBloomFilter

and

RuntimeFilterMinMax

. If your dimension tables are small, ensure they are being broadcast to build these filters for the large fact table scans. C. The New "Variant" Type (if applicable) If your Iceberg tables contain JSON or semi-structured data, the 4.1 Variant type allows for high-performance vectorized execution on dynamic columns, which was a bottleneck in 3.x. ────────── 3. Iceberg REST Catalog Specifics (4.1) * Vended Credential Caching: 4.1 introduces optimized caching for vended credentials. Ensure your catalog is configured with

aws.s3.use_vended_credentials = true

. This prevents the FE from hammered the REST API for every single query's file access.

@Rocky Tell me more about the resource groups and their own in this context. Also please tell me more about query_mem_limit and related variables. I also want to know more about trying different instance types, e.g., m6id.* (nvme) and the importance of nvme over ebs for anything cache related, and what cache(s) are you thinking of specifically?

For your StarRocks 4.1 Iceberg setup on AWS, focusing on resource management and caching hardware will be the deciding factor for hitting that <3s SLO under high concurrency. 1. Resource Groups: The "Traffic Controller" In StarRocks 4.1, Resource Groups are your primary mechanism for multi-tenancy and stability. Think of them as a "governor" for each BE's resources. *

concurrency_limit

: This is critical for your SLO. If you expect 50 users but your BEs can only handle 20 heavy 10-table joins concurrently, setting this to

20

will trigger the Query Queue. It’s better for a user to wait 1 second in a queue than for all 50 queries to contend for CPU and fail the 3s SLO. *

cpu_weight

vs

exclusive_cpu_cores

: * Use

cpu_weight

for "Fair Sharing." If group A and B both have weight 100, they split CPU 50/50 when busy. _ Use

exclusive_cpu_cores

(Hard Isolation) if you have a "Gold" tenant that _must* have 16 cores available regardless of other traffic. *

big_query_mem_limit

: Set this to prevent a single "rogue" query (e.g., a massive join without filters) from eating the entire group's memory. If a query hits this, it’s killed or spilled, protecting the other concurrent "small" queries. 2. Memory Limits:

query_mem_limit

vs.

exec_mem_limit

In the StarRocks Pipeline Engine (v3.x and v4.1),

query_mem_limit

is the modern session variable. * How it works: It sets the memory limit for a single query on a single BE node. * The Relation: You might see

exec_mem_limit

in older docs or logs; in 4.1,

query_mem_limit

is the preferred alias. If both are set,

query_mem_limit

usually takes precedence in the pipeline engine. * Best Practice: Set

query_mem_limit

to roughly

Resource Group Mem Limit / Expected Concurrency

. For example, if a group has 40GB and you expect 4 concurrent queries, set

query_mem_limit = 10G

. 3. Instance Types: Why NVMe (m6id) is the Game Changer You mentioned

m6a.8xlarge

(EBS-only). For a 3s SLO on 10-table joins, I strongly recommend moving to

m6id

or

r6id

(local NVMe). The "Data Cache" (Block Cache) StarRocks 4.1 uses a Block Cache that splits Iceberg files into small chunks (default 1MB). * The EBS Bottleneck: EBS volumes (even GP3) have higher latency (~ms) and limited IOPS compared to local NVMe (~μs). In a 10-table join, the engine is doing massive amounts of random reads across these blocks. * NVMe Advantage: Local NVMe provides hundreds of thousands of IOPS and sub-millisecond latency. This is the difference between a join finishing in 1.5s (NVMe) vs. 5s (EBS) when the cache is being hammered by 20 concurrent threads. The "Other" Caches When you use NVMe for the Data Cache, you are actually benefiting from three layers: 1. Page Cache (In-Memory): StarRocks uses a portion of BE RAM to cache the most frequently accessed blocks. 2. Data Cache (Local NVMe): This is where the 1MB Iceberg blocks live. This is persistent across BE restarts in 4.1. 3. Metadata Cache (Manifest Cache): Managed by the FE. It stores Iceberg manifest files. NVMe doesn't affect this directly (it's in RAM), but fast BEs mean the FE spends less time waiting for I/O reports. 4. Refining Your Experiment Treatments For your "Lean Read-Path" experiment (#8), consider these specific 4.1 "treatments" for the Data Cache: * Treatment A (Strict Performance): *

enable_datacache_async_populate_mode = true

(Don't slow down the first query to write to cache). *

datacache_eviction_policy = 'SLRU'

(Better for preventing "scan-heavy" queries from evicting "point-lookups"). * Treatment B (Aggressive Warming): *

populate_datacache_mode = 'always'

(Force every scan to cache, regardless of StarRocks' internal cost-model). *

enable_datacache_async_populate_mode = false

(First query will be slower, but subsequent ones are guaranteed hits). * Treatment C (IO Resilience):

@Rocky What new experiment would you suggest running on around

concurrency_limit

and

query_mem_limit

For your StarRocks 4.1 Iceberg setup, where 7-10 table joins are common and latency must be <3s, I suggest an experiment that targets the "Thrashing Point." Since your 10-table joins are memory-intensive (Hash Joins/Shuffle), the goal is to find the tipping point where adding one more concurrent query causes the OS/StarRocks to start context switching or memory-evicting, which spikes P99 latency. The "Dynamic Saturation" Experiment Instead of just testing high vs. low concurrency, test the Precision of the Query Queue. The Setup: 1. Baseline: Set

concurrency_limit

to a safe number (e.g., 10) and

query_mem_limit

to

(BE_Total_RAM * 0.8) / 10

. 2. The Stressor: Use a tool (like JMeter or

k6

) to send a steady stream of your 10-table joins at 3x the baseline concurrency. 3. The Variable: Gradually increase the Resource Group's

concurrency_limit

while monitoring the

QueryQueue

metrics and

BE CPU/Memory Usage

. 1. Experiment: "Queue Wait vs. Execution Speed" In 4.1, the Leader FE manages the query queue centrally. This experiment determines if your 3s SLO is better served by queries waiting in line or queries running with less memory. * Treatment A (Strict Queuing): Set

concurrency_limit = 8

. Queries will wait in the FE queue. Measure total latency (Wait Time + Execution Time). * Treatment B (High Concurrency / Low Memory): Set

concurrency_limit = 24

but lower the session

query_mem_limit

to a level that forces StarRocks to use smaller Hash Tables. * Treatment C (The "Big Query" Circuit Breaker): Use the 4.1

big_query_mem_limit

within the Resource Group. Set it to 20% of BE RAM. If a join query tries to build a hash table larger than this, it is killed instantly. What to look for: * The "Queue Sweet Spot": At what point does

Wait Time

+

Execution Time

stay under 3s? Often, a query that waits 0.5s in a queue and runs in 1.5s is more stable than a query that starts immediately but takes 4s due to resource contention. * Hash Join Memory Hits: Check

EXPLAIN ANALYZE

or the query profile. If

query_mem_limit

is too low, joins might fallback to "Serialized" or "Spill" modes. Avoid any treatment where spilling occurs, as it will break your 3s SLO. 2. Experiment:

query_mem_limit

vs.

big_query_mem_limit

There is a nuanced difference here that is worth testing: *

query_mem_limit

(Session Variable): A "soft" limit that triggers a warning or error at the operator level. *

big_query_mem_limit

(Resource Group Property): A "hard" governor. If a query hits this, it’s killed to save the group. Suggested Test: Set

query_mem_limit

higher than

big_query_mem_limit

. This allows individual queries to be "ambitious" with memory, but ensures the Resource Group kills them if they threaten the BE's stability. 3. The "Cache-Aware" Concurrency Test (m6id NVMe) If you move to

m6id

(NVMe), your

concurrency_limit

can likely be much higher. * The Experiment: Run the same 10-table joins concurrently. Monitor the Data Cache Hit Rate and NVMe Disk IOPS. * Why: With EBS, high concurrency leads to "IO Wait" as multiple threads fight for the limited EBS bandwidth. With NVMe, you want to find the concurrency level where the Local Data Cache starts to get evicted too quickly (LRU thrashing). If your working set for the joins is 100GB and your NVMe is 200GB, you can handle high concurrency. If the working set is 500GB, you need to tighten the

concurrency_limit

to prevent the cache from constantly reloading from S3. Recommended Metric Monitoring During these experiments, watch these specific 4.1 internal metrics: * `starrocks_fe_query_queue_pending_queries`: Are queries piling up? * `starrocks_be_resource_group_cpu_limit_ratio`: Is the Resource Group actually throttling CPU? * `starrocks_be_data_cache_read_throughput`: Is your NVMe/EBS hitting a ceiling? Summary for your Brainstorm:

@Rocky What are the metrics to look for re: query queues

To monitor query queues and evaluate if your concurrency/memory limits are set correctly, you should look at metrics across three levels: Prometheus (monitoring), SQL (real-time debugging), and Audit Logs (post-hoc analysis). 1. Prometheus / Grafana Metrics (Best for Trends) These are the most important metrics to track during your experiments to see if the cluster is "choking" or just "queuing." *

starrocks_fe_query_queue_pending

: The current number of queries waiting in the global queue. If this is consistently > 0, your cluster is at capacity. *

starrocks_fe_resource_group_query_queue_pending

: (Use the

name

label) This tells you which specific resource group is hitting its limit. This is vital if you have different SLAs for different users. *

starrocks_fe_query_queue_timeout

: The number of queries that were killed because they waited in the queue longer than

query_queue_pending_timeout_second

. If this is > 0, your users are seeing errors. *

starrocks_fe_query_err_cnt

: Monitor this alongside queue metrics to ensure queuing isn't causing upstream application timeouts. 2. SQL Commands (Best for Real-time Debugging) When you notice latency spiking during a test, run these: *

SHOW RUNNING QUERIES;

: This is the most detailed view in v3.1+. It shows: * `State`:

PENDING

(in queue) vs

RUNNING

. * `PendingTimeout`: When the query will "give up" and error out. * `ResourceGroupId`: Which group is responsible for the queue. *

SHOW PROCESSLIST;

: Check the

IsPending

column. If it's

true

, the query has been planned but is waiting for resources to start execution. *

SHOW PROC '/backends';

: Check the

NumRunningQueries

column for each BE. If one BE has significantly more queries than others, you might have a data skew issue or an unbalanced FE-to-BE mapping. 3. FE Audit Log (Best for SLO Analysis) To calculate your 3s SLO accurately, you must differentiate between Execution Time and Wait Time. *

PendingTimeMs

: This field in

fe.audit.log

tells you exactly how many milliseconds the query sat in the queue. *

QueryTimeMs

: This is the Total Time (Wait + Execution). The Calculation: If

QueryTimeMs

is 4000ms and

PendingTimeMs

is 2500ms, your StarRocks engine is actually very fast (1.5s execution), but your cluster is under-provisioned for the concurrent load. This indicates you should Scale Out (Add BEs) or Increase

concurrency_limit

if memory allows. Summary Checklist for your Experiments: Goal | Metric to Watch Avoid User Errors |

starrocks_fe_query_queue_timeout

(Keep at 0) Check Group Pressure |

starrocks_fe_resource_group_query_queue_pending

Optimize SLO |

PendingTimeMs

vs

QueryTimeMs

in Audit Logs Identify Bottlenecks |

NumRunningQueries

in

SHOW PROC '/backends'

Rocky's Tip: If you see

PendingTimeMs

increasing while

CpuUsedPct

(from

SHOW PROC '/backends'

) is low, it means your

concurrency_limit

is set too conservatively—you are queuing queries even though the hardware has room to work! References • administration/management/resource_management/query_queues.md • administration/management/monitoring/metrics.md • administration/management/resource_management/resource_group.md

Going back to the Shared-Nothing vs. Shared-Data models. What are the considerations / differences between the two models when creating StarRocks MVs from the iceberg rest catalog? Is it working exactly the same way for both architectures? What are the nuances? @Rocky

When creating Materialized Views (MVs) on an Iceberg REST Catalog, the functional logic (SQL syntax, incremental refresh, and partition tracking) is nearly identical between the two models. However, the operational nuances regarding where data lives and how it is cached are significantly different. 1. Storage Location and Durability * Shared-Nothing (SN): When you create an MV, the data is stored on the local disks of your BE nodes. To ensure durability, StarRocks replicates this data (usually 3 replicas). Scaling the MV storage means adding more BEs and rebalancing the data. * Shared-Data (SD): The MV is treated as a "Cloud-Native" table. The data is written directly to S3 (or your object store). * Nuance: There is no cross-BE replication for the MV data in SD because S3 provides the durability. This makes the "Refresh" process faster/cheaper because it only writes one copy of the data to S3. 2. Resource Isolation for Refreshes * Shared-Nothing: The same BEs that serve your <3s queries also perform the heavy lifting of the MV refresh (reading from Iceberg/S3, computing joins, and writing to local disk). A heavy refresh can steal CPU/Memory from your active users. * Shared-Data (Compute Groups): This is the biggest advantage for your Iceberg setup. You can create a separate Warehouse (Compute Group) dedicated solely to refreshing MVs. * Nuance: You can spin up a "Refresh Warehouse," trigger the Iceberg-to-MV refresh, and then shut it down or scale it back. Your "Query Warehouse" remains untouched and performant. 3. The "Cache Penalty" (Cold vs. Warm) * Shared-Nothing: Once an MV is refreshed, the data is on the BE's local disk. It is effectively "always warm" (subject to OS page cache). * Shared-Data: Because the MV data lives on S3, you are back to relying on the Data Cache (NVMe). * Nuance: If you refresh an MV using "Warehouse A" but your users query it using "Warehouse B," Warehouse B will have a cold cache for that new MV data. You may need to run a "warm-up" query or wait for the first few users to populate the NVMe cache to hit your <3s SLO. 4. Incremental Refresh & Iceberg REST Both architectures interact with the Iceberg REST Catalog via the FE (Frontend). * Starting from v3.1.4, StarRocks can detect partition-level changes in Iceberg tables by comparing snapshot IDs via the REST API. * Nuance: In both models, the FE uses an "Adaptive Metadata Retrieval" strategy. For large Iceberg tables (10-table joins), the FE may distribute the manifest file parsing tasks to the BEs. This works more elastically in Shared-Data because the BEs (CNs) are stateless and can be scaled quickly to handle massive metadata spikes. 5. Summary Table: MV on Iceberg Feature | Shared-Nothing (SN) | Shared-Data (SD) MV Storage | Local BE Disks (Replicated) | Object Storage / S3 Refresh Impact | Shares CPU/RAM with Queries | Can be isolated to a separate Warehouse Performance | Consistent (Local Disk) | Cache-dependent (NVMe) Synchronous MV | Fully Supported | Supported since v3.4.0 Scaling | Manual/Heavy (Data Shuffling) | Instant (Stateless Compute) Rocky’s Recommendation: If your 10-table joins are very complex and you plan to use MVs to "pre-join" the hottest data, Shared-Data with NVMe (m6id) is superior. It allows you to isolate the "write-heavy" refresh tasks from your "latency-sensitive" queries, which is the most reliable way to maintain a <3s SLO in a high-concurrency environment. References • deployment/shared_data/feature-support-shared-data.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • faq/shared_data_faq.md

@Rocky what are all the metrics I should keep an eye on per experiment (list mentioned previously) Just the bullet points please

To monitor the success of your experiments for the 10-table Iceberg join scenario, keep an eye on these specific metrics, categorized by the experiments we discussed: 1. Dynamic Saturation Experiment *

starrocks_fe_resource_group_query_queue_pending

: Tracks if queries are stacking up in the FE before execution starts. *

resource_group_running_queries

: Actual concurrent query count per BE (cross-reference with

concurrency_limit

). *

starrocks_fe_query_resource_group_latency

: Monitor the

99_quantile

to see exactly when latency breaks your 3s SLO. *

resource_group_cpu_limit_ratio

: Tells you if the Resource Group is actually hitting its CPU ceiling or if it’s idling. *

resource_group_mem_inuse_bytes

: Check if memory usage is nearing the Resource Group’s

mem_limit

. 2. Queue Wait vs. Execution Speed *

PendingTimeMs

(Audit Log): The "hidden" latency. If this is > 500ms, your queue is becoming a bottleneck. *

QueryTimeMs

(Audit Log): Total end-to-end time. Compare this with

PendingTimeMs

to see if the engine is fast but the queue is full. *

starrocks_fe_resource_group_query_queue_timeout

: If this count increases, your

query_queue_pending_timeout_second

is too short or the cluster is overloaded. *

starrocks_fe_query_resource_group_err

: Watch for spikes in errors as concurrency increases (indicates resource exhaustion). 3.

query_mem_limit

vs.

big_query_mem_limit

*

resource_group_bigquery_count

: Number of queries killed for exceeding the "hard"

big_query_mem_limit

. *

resource_group_concurrency_overflow_count

: Frequency of queries being rejected before entering the queue. *

SpillWriteBytes

/

SpillReadBytes

(Query Profile): If these are non-zero, your

query_mem_limit

is too low, forcing slow disk I/O for joins. *

starrocks_be_mem_usage_pct

: Overall BE memory health to ensure the Resource Group isn't starving the rest of the system. 4. Cache-Aware Concurrency (m6id NVMe) *

block_cache_hit_rate

: Should ideally be > 0.80 for repetitive 10-table joins. *

DataCacheReadBytes

vs.

BytesRead

: If

DataCacheReadBytes

is low compared to

BytesRead

, you are pulling too much from S3 (Cache Miss). *

fslib_read_io_latency

: Specifically monitors the NVMe drive latency. If this spikes, you are saturating the local disk IOPS. *

fslib_star_cache_data_disk_size

: The "Fullness" of your NVMe cache. If it's always at 100%, you are likely thrashing (evicting data as fast as you load it). 5. MV Refresh Nuances (Shared-Data) *

starrocks_fe_mv_refresh_total

: Count of successful vs. failed MV refreshes. *

starrocks_fe_mv_refresh_latency

: Time taken to refresh the MV from the Iceberg REST catalog. *

starrocks_be_data_cache_write_throughput

: Monitor this during an MV refresh to see how much "Write" pressure it puts on your NVMe cache. References • best_practices/query_tuning/query_profile_operator_metrics.md • sql-reference/System_variable.md • administration/management/monitoring/metrics.md • administration/management/resource_management/spill_to_disk.md

@Rocky awesome. And more generally for these experiments: #1 – Cluster Provisioning— Scaling up/out while keeping an FE-to-BE ratio improves query latency under concurrent load. Goal: find cost-to-performance sweet spot. #2 – Shared-Nothing vs. Shared-Data— Shared-Data architecture (with cache) is more performant than Shared-Nothing for remote tables. #3 – FE Observer Nodes— Past 3 FE nodes, scaling with Observer nodes (vs. Follower) is better for performance and concurrency by avoiding Raft consensus overhead. #4 – Prepared Statements— Using prepared statements improves execution efficiency (available since StarRocks v3.2). #5 – Data Spill— Enablingenable_spillprevents OOM and improves stability under load, but at the cost of higher individual query latency due to disk IO. #6 – Query Parallelism— Tuningpipeline_dopandconnector_io_tasks_per_scan_operatorsignificantly impacts concurrent traffic handling. #7 – Distributed Planning— Switchingplan_modetodistributedorlocal(vs. defaultauto) could impact query performance, especially with many manifest files. #8 – Lean Read-Path— Stripping non-essential write-on-read side effects (bookkeeping, profiling, auditing) improves read latency/throughput under concurrent load. Has 4 variants (Control + 3 treatments varying datacache mode). Setting aside the queue topic

For your set of performance experiments, here are the core metrics to track for each. Use Prometheus/Grafana for cluster-wide trends and the Query Profile (via

get_query_profile()

) for deep-dives into individual SQL behavior. #1 – Cluster Provisioning (Scaling) *

starrocks_be_cpu_utilization

: Ensure even load across new nodes; look for "hot" nodes indicating skew. *

starrocks_fe_query_latency

(P99/P95): The primary success metric for scaling. *

starrocks_fe_query_total

(QPS): Verifies if the cluster can handle more concurrent requests after scaling. *

starrocks_be_mem_usage_pct

: Tracks if scaling up (larger nodes) provides enough headroom for your 10-table joins. #2 – Shared-Nothing vs. Shared-Data *

block_cache_hit_rate

: Crucial for Shared-Data; if this is low (<80%), you are bottlenecked by S3/Object Store. *

starrocks_be_data_cache_read_throughput

: Measures the speed of the NVMe cache in Shared-Data mode. *

s3_get_object_latency

: Monitor this in Shared-Data to identify external cloud provider spikes. *

starrocks_be_disk_read_latency

: Compare this (Shared-Nothing local disk) against S3 latency to justify the model choice. #3 – FE Observer Nodes *

starrocks_fe_query_total

(per node): Verify that your load balancer is actually distributing traffic to Observers. *

starrocks_fe_jvm_gc_sec

: High GC on Follower nodes indicates Raft consensus stress; Observers should show lower/more stable GC. *

starrocks_fe_connection_total

: Ensures connection limits aren't being hit on the Leader FE. *

starrocks_fe_edit_log_write_latency

: Ensures adding nodes doesn't slow down metadata synchronization. #4 – Prepared Statements *

PlanTimeMs

(Audit Log): This should drop significantly (often <1ms) as the FE reuses plans. *

starrocks_fe_query_latency_plan

: Prometheus metric to see the aggregate reduction in planning overhead. * FE CPU Utilization: Should decrease at high concurrency due to reduced parsing/binding cycles. *

Parser

time (Query Profile): Specifically look for the parser phase to disappear or minimize. #5 – Data Spill *

SpillWriteBytes

/

SpillReadBytes

: If > 0, the query successfully spilled to disk instead of crashing with OOM. *

SpillWriteDuration

: The latency "penalty" paid for spilling. *

starrocks_be_process_mem_usage

: Should remain stable (flat-topped) during heavy joins rather than spiking and crashing. *

starrocks_be_query_err_cnt

: Specifically watch for a decrease in "Memory Limit Exceeded" errors. #6 – Query Parallelism (

pipeline_dop

) *

DegreeOfParallelism

(Query Profile): Confirms the query is actually running with the intended

pipeline_dop

. *

ActiveTime

vs

ScheduleTime

: If

ScheduleTime

is high, your

pipeline_dop

is too high, causing CPU context-switching overhead. *

ScanTime

(Scan Operator): Higher DOP should reduce this for Iceberg scans, up to the limit of S3 throughput. *

starrocks_be_cpu_utilization

: Watch for 100% saturation; if hit, increasing DOP further will degrade performance. #7 – Distributed Planning *

DeployTime

(Query Profile): Measures the time the FE takes to send fragments to BEs. *

PlanTimeMs

: For Iceberg tables with thousands of manifest files, distributed planning should significantly lower this. *

DeployDataSize

: Monitors the size of the metadata being sent; if too large, it can cause network jitter. * FE Network Out: High metadata traffic can sometimes bottleneck the FE NIC during distributed planning. #8 – Lean Read-Path (Optimization) *

QueryTimeMs

(P99): The end-to-end impact of stripping auditing/profiling side effects. *

DataCacheReadBytes

: Essential for comparing the three cache treatment variants (to see if "Lean" impacts cache efficiency). *

starrocks_fe_audit_log_latency

: If using a lean path that reduces audit logging, monitor this to see the performance gain.