http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> In EKS shared-data cluster INSERT I...
# questions-and-troubleshooting
d
@Rocky In EKS shared-data cluster INSERT INTO S3 based table fail with: ErrorMsg: type:LOAD_RUN_FAIL; msg:Cancelled, msg: [E104]Fail to read from Socket{id=187 fd=284 addr=172.31.8.225806045618} (0x0xfffeb0a502c0): Connection reset by peer [R1][E112]Not connected to 172.31.8.225:8060 yet, server_id=187 [R2][E112]Not connected to 172.31.8.225:8060 yet, server_id=187 [R3][E112]Not connected to 172.31.8.225:8060 yet, server_id=187: BE:14582 It works fine up until the end and then load fails. What can be the root of the issue? Node crash mid-run? OOM? What would be the way to diagnose / mitigate.
r
The error message 
[E104] Connection reset by peer
and 
[E112] Not connected to ... yet
indicates that the Backend (BE) or Compute Node (CN) involved in the data load unexpectedly terminated or closed the connection. Since you mentioned it happens "at the end of the run," this strongly suggests a resource-related crash occurring during the final commit, data shuffle, or index building phase. Potential Causes 1. OOM (Out of Memory) Killer: This is the most frequent cause in EKS. During the final phase of 
INSERT INTO
, the BE may consume significant memory to sort data or finalize transaction metadata. If the memory usage exceeds the Kubernetes pod limit, the K8s OOMKiller will send a 
SIGKILL
, causing an immediate connection reset. 2. StarRocks Memory Limit: If the BE hits its own 
mem_limit
, it usually attempts to cancel the query gracefully. however, if the allocation is sudden and massive (e.g., a large shuffle), the process may crash or be killed by the OS. 3. Process Crash (Segfault): A bug or an unhandled exception could cause the BE process to exit. 4. Local Disk Space: Even in shared-data mode, StarRocks may use local storage for temporary spill data or intermediate results. Running out of disk space on the BE's data volume can cause process failure. Diagnostic Steps 1. Check Pod Status (K8s): Run 
kubectl describe pod <pod_name>
for the BE/CN node at 
172.31.8.225
. Look for: * 
State: Terminated
* 
Reason: OOMKilled
* 
Exit Code: 137
(indicates SIGKILL, often due to OOM). 2. Check StarRocks Logs: * 
be.out
: This is the most critical log for crashes. It often contains the stack trace or "Out of memory" messages that don't make it into 
<http://be.INFO|be.INFO>
. * 
<http://be.INFO|be.INFO>
: Search for the 
query_id
or the timestamp of the failure. Look for "Memory limit exceeded" or "failed to reserve memory" messages leading up to the reset. 3. Audit Log: Check the FE 
audit.log
for the 
INSERT
statement. Review the 
PeakMemoryBytes
field to see if the memory usage was near your BE/CN pod limits. Mitigation & Tuning * Enable Spill-to-Disk: For large 
INSERT INTO SELECT
operations, you can enable spilling to prevent OOM:
Copy code
sql
    SET spill_mode = 'auto';
* Adjust Memory Limits: * Increase the Kubernetes 
limits.memory
for your BE pods. * Ensure the 
mem_limit
in 
be.conf
is correctly set (typically 90% of the container limit). * Reduce Parallelism: Lowering the parallelism can reduce memory pressure:
Copy code
sql
    SET pipeline_dop = 8; -- Adjust based on your core count
* Increase Timeout: If the "end of run" is a long commit phase, ensure the timeout isn't being reached:
Copy code
sql
    SET insert_timeout = 14400; -- default 4 hours
If the node did not restart (the pod is still "Running" with a long uptime), the "Connection reset" might be a network-level event (e.g., AWS NLB timeout or VPC networking issue), but given the timing at the end of a load, a process crash/restart is much more likely. References • faq/shared_data_faq.md • deployment/shared_data/s3.md • quick_start/shared-data.md
d
@Rocky does spill have effect on CN nodes? is it same parameters? is it off by default? how spill can be monitored
r
Yes, the spill-to-disk feature works on Compute Nodes (CN) exactly as it does on Backend (BE) nodes. Since CN nodes are often used in EKS (shared-data) environments for elastic scaling, enabling spill is a common way to handle large 
INSERT INTO
or 
SELECT
operations that would otherwise trigger OOM restarts. 1. Does spill have an effect on CN nodes? Yes. While CN nodes do not store tablet data, they perform the heavy lifting for query execution (Aggregations, Joins, Sorts). Spilling allows these operators to use local disk (or object storage) as temporary "overflow" memory. 2. Is it the same parameters? Yes. The configuration is identical for both CN and BE: * Node Config (
cn.conf
): You must define a local directory for the spill data.
Copy code
properties
    # In cn.conf
    spill_local_storage_dir=/path/to/fast/disk/spill
* Session Variables:
Copy code
sql
    SET enable_spill = true; -- Off by default
    SET spill_mode = 'auto'; -- 'auto' triggers based on memory pressure, 'force' spills everything
3. Is it off by default? Yes. 
enable_spill
is set to 
false
by default. You must explicitly enable it at the session or global level. Additionally, it will not function unless 
spill_local_storage_dir
is configured in the node's configuration file. 4. How can spill be monitored? You can monitor spilling activity through the Query Profile or Prometheus metrics: * Query Profile: In the profile of an 
INSERT
or 
SELECT
statement, look for: * 
QuerySpillBytes
: The total amount of data written to disk for the entire query. * Operator Metrics: Under operators like 
AGGREGATE_NODE
or 
SORT_NODE
, you will see: * 
SpillWriteTime
/ 
SpillReadTime
* 
SpillWriteBytes
/ 
SpillReadBytes
* 
SpillWriteCount
* Prometheus Metrics: StarRocks exports metrics that indicate if spilling is happening across the cluster: * 
starrocks_be_spill_write_bytes_total
* 
starrocks_be_spill_read_bytes_total
Important Note for EKS Users If you are using CN nodes in EKS, ensure the 
spill_local_storage_dir
points to a volume with sufficient IOPS and capacity (like an Amazon EBS volume or Instance Store). Spilling to a slow or network-throttled disk will significantly increase the latency of your 
INSERT INTO
operations. References • administration/management/resource_management/spill_to_disk.md • best_practices/query_tuning/query_profile_operator_metrics.md • administration/management/BE_configuration.md • sql-reference/System_variable.md References • best_practices/query_tuning/query_profile_operator_metrics.md • sql-reference/System_variable.md • administration/management/monitoring/metrics.md • administration/management/BE_configuration.md
d
@Rocky how can I access FE HTTP UI, it asks for credentials, same MySQL creds (root) do not work
r
The StarRocks FE HTTP UI (accessible at 
http://<fe_ip>:8030
) uses Basic Authentication. It shares the same user and password system as the MySQL protocol. If your MySQL 
root
credentials are not working for the Web UI, here are the likely reasons and how to fix them: 1. Required Privileges To log in to the Web UI, the user must have the 
cluster_admin
 role. The 
root
user has this by default. However, if you are using a different user, ensure the role is granted:
Copy code
sql
GRANT cluster_admin TO 'your_user';
2. Password Encoding/Special Characters If your password contains special characters, some browsers may not encode them correctly during the Basic Auth prompt. * Test with 
curl
: Verify if the credentials work via the command line to rule out browser issues:
Copy code
bash
    curl -u root:your_password http://<fe_ip>:8030/api/bootstrap
(Note: If the password is empty, use 
curl -u root: http://...
) 3. Check for Default Password in EKS/Helm Since you are on EKS, if you deployed via the StarRocks Operator or Helm chart, a password might have been automatically generated or set via a Kubernetes secret. * Check your Helm 
values.yaml
for a 
password
field or a secret reference like 
starrocks-root-pass
. * If you haven't set a password yet, the default is typically empty (leave the password field blank in the browser). 4. Verify the Port The default HTTP port is 8030. If you are hitting a different port (like 9030, which is the MySQL port), the credentials will not work for HTTP. 5. Account Lockout or Authentication Disable Check your 
fe.conf
. If 
enable_auth_check
is set to 
false
, it might behave unexpectedly with UI logins. It should generally be 
true
(the default). Recommendation: Try logging in with the username 
root
and an empty password first. If that fails, and you are sure of your MySQL password, try creating a new user with the 
cluster_admin
role via MySQL and use that for the UI:
Copy code
sql
CREATE USER 'web_admin' IDENTIFIED BY 'password123';
GRANT cluster_admin TO 'web_admin';
References • administration/user_privs/authorization/privilege_faq.md • deployment/post_deployment_setup.md • administration/management/FE_configuration.md • quick_start/helm.md References • administration/user_privs/authentication/native_authentication.md • deployment/deploy_manually.md • deployment/post_deployment_setup.md • quick_start/helm.md • administration/user_privs/authorization/privilege_faq.md • administration/management/FE_configuration.md
d
@Rocky load finished but CN are still busy with something, CPU and mem usage is relatively high. I assume they’re performing background tasks like vacuum / statistics - how that can be diagnosed what CN nodes are busy with (metrics, SQL commands)
r
After a large data load, high resource usage on Compute Nodes (CN) is typically caused by Compaction, Statistics Gathering (Analyze), or Garbage Collection (Vacuum). Since you are in a shared-data (EKS) environment, the CN nodes are responsible for executing these background tasks as scheduled by the FE. 1. Compaction (Most Likely) Compaction merges small files into larger ones to optimize read performance. In shared-data mode, this is very common after an 
INSERT INTO
. * View Overall Status:
Copy code
sql
    SHOW PROC '/compactions';
This shows which partitions have active compaction transactions (
TxnID
). * View Detailed Tablet Progress:
Copy code
sql
    SELECT * FROM information_schema.be_cloud_native_compactions
    WHERE STATUS = 'OK' AND PROGRESS < 100;
Look for nodes with high 
PROGRESS
activity. * Check Compaction Scores: If the "Compaction Score" is high, the nodes are working hard to catch up.
Copy code
sql
    SELECT DATABASE_NAME, TABLE_NAME, PARTITION_NAME, MaxCS, AvgCS
    FROM information_schema.partitions_meta
    ORDER BY MaxCS DESC;
2. Statistics Gathering (Analyze) By default, StarRocks automatically triggers an 
ANALYZE
task after data changes to update the cost-based optimizer (CBO) stats. * Check Status:
Copy code
sql
    SHOW ANALYZE STATUS;
Look for tasks with 
STATE
as 
PENDING
or 
RUNNING
. Large tables can consume significant CPU during this phase. 3. Vacuum / Garbage Collection In shared-data mode, StarRocks must clean up old metadata and data versions from S3. * Diagnosis: Currently, Vacuum is largely an internal FE-driven process that instructs CNs to delete files. There isn't a direct 
SHOW
command for "Vacuum progress," but you can monitor the BE/CN logs (
<http://be.INFO|be.INFO>
) for keywords like 
vacuum
, 
gc
, or 
delete
to see if the node is busy with file deletions. 4. Thread-Level Diagnosis If you want to see exactly what the CPU is doing right now at a code level: * Print Stack Traces: Find the 
BackendId
of the busy CN from 
SHOW BACKENDS;
, then run:
Copy code
sql
    -- Replaces <be_id> with the actual ID
    ADMIN EXECUTE ON <be_id> 'System.print(ExecEnv.get_stack_trace_for_all_threads())';
Check the FE 
fe.out
or the result of the query to see which threads (e.g., 
compaction_thread
, 
analyze_thread
) are active. 5. Key Metrics for Monitoring If you have Prometheus/Grafana set up, look for: * `starrocks_be_compaction_mem_bytes`: Memory consumed by compaction. * `starrocks_be_unused_index_directory_size`: Can indicate cleanup activity. * `starrocks_be_resource_group_cpu_limit_ratio`: If you use resource groups, see if background tasks are hitting limits. Mitigation: If background tasks are impacting your queries, you can limit compaction concurrency in 
cn.conf
by adjusting 
max_compaction_threads
, though it's generally better to let them finish to ensure query performance isn't a performance debt for future queries. References • administration/management/compaction.md • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/data-manipulation/SHOW_ANALYZE_STATUS.md • knowledge/admin_and_operation/shared_data_compaction.md References • deployment/shared_data/feature-support-shared-data.md • knowledge/admin_and_operation/shared_data_compaction.md
d
@Rocky Any reason why 
starrocks_be_spill_write_bytes_total
* 
starrocks_be_spill_read_bytes_total
Are not in prometheus?
r
The reason you might not see 
starrocks_be_spill_write_bytes_total
or 
starrocks_be_spill_read_bytes_total
in your Prometheus output is typically due to one of the following three reasons: 1. Minified Metrics (Default Behavior) By default, the StarRocks 
/metrics
endpoint operates in a minified mode. To reduce the payload size and performance overhead, StarRocks often omits metrics with a value of 0. * Verification: If no query has actually triggered a spill since the CN/BE node started, these metrics will not appear in the 
/metrics
output at all. * Test: Run a query with 
SET enable_spill = true; SET spill_mode = 'force';
to force a spill, then check the metrics again. 2. Version-Specific Metric Names The exact naming of these metrics has evolved. In some versions, they may not have the 
starrocks_
prefix in the raw BE output, or they might be named differently: * Check 
be_metrics
: You can query the internal table to see exactly what metrics your nodes are currently reporting:
Copy code
sql
    SELECT * FROM information_schema.be_metrics WHERE NAME LIKE '%spill%';
* Check the CN Endpoint: Ensure you are scraping the CN node's HTTP port (default 
8040
), as spill metrics are local to the execution engine. 3. Authentication & Detailed Metrics As of recent versions, certain detailed metrics require an authorized request to the 
/metrics
endpoint. If you are scraping anonymously, you might only see a subset of "General" metrics. * To see if this is the case, try accessing the metrics with credentials:
Copy code
bash
    curl -u root:password "http://<cn_ip>:8040/metrics"
Summary of Monitoring Options If you are trying to confirm if spilling is happening right now, the Prometheus metrics are often too coarse. Use these instead: 1. Information Schema (Cluster-wide):
Copy code
sql
    -- View active spills on all nodes
    SELECT * FROM information_schema.be_metrics WHERE NAME = 'starrocks_be_spill_write_bytes_total';
2. Query Profile: After running a heavy query, run 
get_query_profile('<query_id>')
and look for: * 
QuerySpillBytes
(Total bytes spilled for the query) * 
SpillWriteTime
/ 
SpillReadTime
3. Logs: Search the CN logs (
<http://be.INFO|be.INFO>
) for the string 
spill
. When a spill occurs, the engine logs the creation of spill files. References • administration/management/monitoring/metrics.md • administration/management/resource_management/spill_to_disk.md • sql-reference/information_schema/be_metrics.md • administration/management/monitoring/Monitor_and_Alert.md References • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/metrics-shared-data.md
d
@Rocky Is there a way to adjust SUBMIT TASK / LOAD parallelism? I want to submit 10 tasks but only 2 to be active at any time. Is there a way to know currently how many tasks allowed? I only want to limit INSERT queries
r
To adjust the parallelism of your tasks and loads, StarRocks provides two primary mechanisms depending on whether you are using the Asynchronous Task Framework (
SUBMIT TASK
) or standard INSERT commands. 1. Limiting Asynchronous Tasks (
SUBMIT TASK
) If you use 
SUBMIT TASK AS INSERT INTO...
, the parallelism is controlled by global Frontend (FE) configurations. * Adjust Concurrency: To limit active tasks to 2, you must modify the FE configuration. This is a cluster-wide setting.
Copy code
sql
    -- Check current value (default is 4)
    ADMIN SHOW FRONTEND CONFIG LIKE 'task_runs_concurrency';

    -- Note: This is a static FE configuration.
    -- You must add it to your fe.conf and restart the FE,
    -- or if supported in your version, set it via ADMIN SET CONFIG:
    ADMIN SET CONFIG ("task_runs_concurrency" = "2");
* Queue Length: You can also adjust 
task_runs_queue_length
(default 500) to control how many tasks can sit in the 
PENDING
state before the system rejects new ones. 2. Limiting Standard 
INSERT INTO
(Resource Groups) If you want to limit the number of active 
INSERT
queries (not just tasks), you should use Resource Groups. This allows you to isolate "Load" workloads from "Query" workloads. * Create a Resource Group for Inserts:
Copy code
sql
    CREATE RESOURCE GROUP insert_group
    TO (user='your_user') -- Or use roles/db names
    WITH (
        "concurrency_limit" = "2",
        "type" = "short_query" -- 'short_query' is a general type that supports INSERT
    );
* Enable Query Queuing: To ensure queries above the limit are queued rather than failed, you must enable the query queue:
Copy code
sql
    SET GLOBAL enable_query_queue_load = true; -- For INSERT/LOAD
    SET GLOBAL query_queue_concurrency_limit = 2; -- Global BE limit
_Note: From v3.1.4+, you can enable 
enable_group_level_query_queue
to make the 
concurrency_limit
in the resource group act as a queue trigger._ 3. Diagnosing Current Limits & Status To see what is currently allowed and what is running: * View FE Configs:
Copy code
sql
    ADMIN SHOW FRONTEND CONFIG LIKE '%task_runs%';
    ADMIN SHOW FRONTEND CONFIG LIKE '%load_job_concurrency%';
* Check Task Status:
Copy code
sql
    -- See how many are RUNNING vs PENDING
    SELECT state, COUNT(*)
    FROM information_schema.task_runs
    GROUP BY state;
* Check Resource Group Usage:
Copy code
sql
    SHOW USAGE RESOURCE GROUPS;
Summary Recommendation To submit 10 tasks and have only 2 active: 1. Use 
SUBMIT TASK
: This is built for background ETL. 2. Set 
task_runs_concurrency = 2
 in your 
fe.conf
. 3. Submit all 10: The first 2 will enter 
RUNNING
state, and the remaining 8 will stay in 
PENDING
until the active ones finish. References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • administration/management/resource_management/resource_group.md • administration/management/resource_management/query_queues.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md References • administration/management/resource_management/query_queues.md • administration/management/monitor_manage_big_queries.md
d
@Rocky if I have spill enabled with datacache default 80% - I assume together they can go over 100% if I don’t adjust settings, is that correct?
r
The confusion often stems from whether the 80% refers to Memory or Disk. In StarRocks, these are handled by different limits, but they both live under the same process cap (
mem_limit
). 1. The "80%" Default is likely Disk, not Memory By default, StarRocks allocates 80% of Disk Space for the Data Cache (controlled by 
starlet_star_cache_disk_size_percent
or 
disk_safe_level
). * Memory Usage: The 
datacache_mem_size
(memory-based Page Cache) actually defaults to 0 (or a very small percentage for metadata). Most of the Data Cache lives on your SSDs/NVMEs. * Spill Usage: When a query spills to disk, it writes to the directory defined in 
spill_local_storage_dir
. The Risk: If your Data Cache and your Spill directory share the same physical disk, they will compete for space. If the Data Cache occupies 80% and a large spill needs more than the remaining 20%, the query will fail with a "No space left on device" error. 2. Memory interaction (The 
mem_limit
Ceiling) If you have manually adjusted 
datacache_mem_size
to 80% of RAM: * The BE/CN process has a global 
mem_limit
(default 90% of total system RAM). * If Data Cache takes 80% of RAM, you only have 10% left for everything else (metadata, connection overhead, and query execution). * Spilling and Memory: Spilling is triggered by memory pressure. If your memory is already at 80% due to cache, StarRocks will trigger spilling almost immediately for any non-trivial query because there is so little "working memory" left. 3. Can they go over 100%? * Of Server RAM? No. The StarRocks process will strictly respect the 
mem_limit
(90%). If the sum of (Data Cache Memory + Query Memory + Metadata) hits that 90%, the system will either evict cache, kill the query, or spill. * Of Disk? Yes. StarRocks does not currently have a unified "Disk Resource Manager" that balances Data Cache vs. Spill files. If the disk hits 100% usage because both are writing to it, the process will encounter I/O errors. Recommendations 1. Check your Disk: Ensure 
spill_local_storage_dir
and 
storage_root_path
(used by Data Cache) are either on different disks or that you have enough head-room. 2. Adjust Data Cache Disk Limit: If you expect heavy spilling, lower the cache disk limit:
Copy code
sql
    -- Example: Limit Data Cache to 60% of disk to leave room for Spilling
    ADMIN SET CONFIG ("starlet_star_cache_disk_size_percent" = "60");
3. Monitor Memory: Use 
SHOW BACKENDS
to see the 
DataCacheMetrics
. If you see the Memory quota is very high (close to your total RAM), you should lower 
datacache_mem_size
to ensure queries have enough room to execute without constant spilling. References • administration/management/BE_configuration.md • data_source/data_cache.md • administration/management/resource_management/spill_to_disk.md • administration/management/resource_management/Memory_management.md References • administration/management/resource_management/Memory_management.md • administration/management/BE_configuration.md • data_source/data_cache.md • administration/management/resource_management/spill_to_disk.md References • administration/management/BE_configuration.md
d
@Rocky I have a table with 
PARTITION BY date_trunc('day', _arrival_ts)
- is that the optimal way of partitioning timestamps by day? is 
date(..)
better? Also does SR optimize statements like 
SELECT date_trunc('day', _arrival_ts), ... FROM ... GROUP BY 1
where partition expression is part of the query? it seems that queries take long ish time anyway, for example COUNT(*) per partition expression query I suspect can use partition statistics directly
r
1. Optimal Way: 
date_trunc
vs. 
date
 For daily partitioning in StarRocks, 
date_trunc('day', _arrival_ts)
is the recommended and standard approach. * Syntax Support: In StarRocks' Expression Partitioning (introduced in v3.0/v3.1), 
date_trunc
and 
time_slice
are the two explicitly supported "simple" functions for automatic partition management. * Versatility: 
date_trunc
is preferred because you can easily change granularity (e.g., to 
'hour'
or 
'month'
) without changing the function itself. * Pruning: Both 
date_trunc('day', ts)
and 
date(ts)
will trigger partition pruning if used in a 
WHERE
clause. However, since 
date_trunc
is the canonical example in the documentation for expression partitioning, it ensures the best compatibility with the partition manager and lifecycle features like 
partition_live_number
. Note on v3.4+: From version 3.4 onwards, StarRocks supports "Complex Expression Partitioning," which allows functions like 
date()
, 
from_unixtime()
, or 
cast()
. If you are on a version older than 3.4, you must use 
date_trunc
. ────────── 2. Query Optimization for 
GROUP BY 1
 StarRocks does optimize queries where the grouping expression matches the partition expression, but there are nuances: Partition Pruning If your query has a filter like 
WHERE _arrival_ts >= '2023-01-01'
, the optimizer will prune partitions. Even if you use 
WHERE date_trunc('day', _arrival_ts) = '2023-01-01'
, StarRocks can prune the partitions effectively. Query Cache (The "Long Ish" Time Fix) The reason your 
GROUP BY 1
query might still feel slow is that even with pruning, StarRocks usually performs a parallel scan of the tablets within those partitions to get an exact 
COUNT(*)
. To speed this up significantly: 1. Query Cache (v2.5+): StarRocks can cache results at the partition level. If you run 
SELECT date_trunc('day', _arrival_ts), COUNT(*) ... GROUP BY 1
, StarRocks can cache the count for "yesterday's" partition. When you run the query again today, it only scans the "today" partition and pulls the rest from the cache. 2. Metadata-only queries: Currently, StarRocks can return a global 
COUNT(_)
from metadata (e.g., 
SELECT COUNT(_) FROM table
) very quickly. However, for a 
GROUP BY
query, it typically does not yet "stitch together" individual partition row counts from metadata to answer the query; it still executes a scan to ensure consistency across versions/compaction. Why is it still slow? If it's slow, check the following: * Bucket Count: If you have too many buckets (tablets) per partition, the metadata overhead for the scan increases. Aim for tablets to be 1GB–10GB in size. * Data Skew: Use 
SHOW TABLE STATUS
or check the profile to see if one tablet is taking much longer than others. * Index usage: Ensure 
_arrival_ts
is the first column in your 
DUPLICATE KEY
or 
PRIMARY KEY
to enable prefix index scanning. Summary Recommendation Keep 
PARTITION BY date_trunc('day', _arrival_ts)
. To make the 
COUNT(*)
per day query faster, ensure Query Cache is enabled:
Copy code
sql
SET GLOBAL enable_query_cache = true;
This will allow the engine to reuse pre-calculated counts for historical (immutable) partitions. References • table_design/data_distribution/expression_partitioning.md • using_starrocks/caching/query_cache.md • best_practices/partitioning.md • sql-reference/sql-functions/date-time-functions/date_trunc.md References • table_design/data_distribution/expression_partitioning.md
d
@Rocky got this error on query: scanned 783900250996 bytes > ceiling 500000000000 Where is this configured?
r
This error indicates that your query has been terminated because it exceeded a scan data limit (guardrail) configured in your cluster. In your case, the query scanned ~783 GB, which exceeded a "ceiling" (limit) of 500 GB (
500,000,000,000 bytes
). This is typically configured in one of two places: 1. Resource Group Limits (Most Likely) StarRocks allows you to set "Big Query" limits on resource groups to prevent a single query from scanning too much data and impacting cluster stability. * To check: Run 
SHOW RESOURCE GROUPS;
and look for the 
big_query_scan_bytes_limit
column. * To adjust: If your query is hitting a resource group named 
analytics_group
, you can increase the limit (e.g., to 1 TB) using:
Copy code
sql
    ALTER RESOURCE GROUP analytics_group WITH (
        "big_query_scan_bytes_limit" = "1099511627776" -- 1 TB in bytes
    );
Note: Set it to 
0
to disable the limit entirely for that group. 2. Session Variables There are session-level variables that can trigger this protection, often used to prevent "accidental" full table scans. * To check: Run 
SHOW VARIABLES LIKE '%scan%';
* Variable name: Check for 
query_scan_bytes_limit
or 
big_query_scan_bytes_limit
. * To adjust (temporary):
Copy code
sql
    SET query_scan_bytes_limit = 0; -- Disable for current session
3. Frontend (FE) Global Configuration If this is happening for all users and no resource groups are explicitly configured with this limit, it might be a global FE default. * To check: Run 
ADMIN SHOW FRONTEND CONFIG LIKE '%scan%';
* Key: Look for 
big_query_scan_bytes_threshold
(used for logging) or any parameter containing 
max_scan
. Why did it happen? The "ceiling" of 500 GB is a very common default for organizations trying to prevent runaway costs or I/O saturation. If you didn't expect to scan 783 GB, you should check your query for: * Missing partition filters: Ensure you are filtering on your 
_arrival_ts
or other partition columns. * Missing bucket filters: Ensure you are filtering on your distribution key if applicable. * Select \*: Specify only the columns you need to reduce the scan volume. References • administration/management/resource_management/resource_group.md • administration/management/monitor_manage_big_queries.md • sql-reference/System_variable.md • administration/management/FE_configuration.md References • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • administration/management/FE_configuration.md
d
@Rocky is it possible to have incremental MV based on Iceberg table, where SR tracks Iceberg updates
r
Yes, it is possible to have an incremental Materialized View (MV) based on an Apache Iceberg table. StarRocks supports this through two primary mechanisms depending on your version and requirements: 1. Row-Level Incremental Refresh (v3.3+) Starting from v3.3, StarRocks supports "true" incremental MVs for Iceberg tables. This means StarRocks can track specific record-level appends rather than just refreshing whole partitions. * How it works: StarRocks leverages Iceberg’s Snapshot mechanism. When a refresh is triggered, StarRocks identifies the new snapshots created since the last refresh and processes only that incremental data. * Requirement: You must set 
refresh_mode
= 
INCREMENTAL
in the MV properties. * Limitation: This currently supports append-only changes. If the base Iceberg table undergoes a 
DELETE
, 
UPDATE
, or 
OVERWRITE
, the incremental refresh will fail (or fall back to a full/partition refresh if configured to do so). 2. Partition-Level Refresh (PCT) (v3.1.4+) If your MV is partitioned and you are on a version earlier than 3.3 (or if you perform 
OVERWRITE
operations), StarRocks uses Partition-based Cost-effective Tracking (PCT). * How it works: StarRocks tracks the version/timestamp of each partition in the Iceberg table metadata. * Refresh behavior: When the base table changes, StarRocks detects which specific partitions have been updated and only refreshes those partitions in the MV. * Optimization: This is significantly faster than a full refresh but less granular than row-level incremental refresh. Important Configuration To ensure StarRocks tracks these updates efficiently: 1. Metadata Polling: StarRocks periodically polls the Iceberg catalog to check for new snapshots. This is controlled by FE configurations like 
background_refresh_metadata_interval_millis
. 2. Partition Alignment: If your Iceberg table uses Partition Transforms (like 
days(ts)
or 
months(ts)
), StarRocks (since v3.2.3) can align the MV partitions with these transforms automatically. Example Syntax (Incremental)
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_mv
PARTITION BY (dt)
REFRESH ASYNC START('2024-01-01 00:00:00') EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "refresh_mode" = "INCREMENTAL" -- Enable row-level incremental refresh
)
AS 
SELECT id, name, dt 
FROM iceberg_catalog.db.table 
WHERE dt > '2023-01-01';
Summary: If you want the most efficient tracking, use v3.3 or higher with 
refresh_mode = INCREMENTAL
. If you are on an older version, StarRocks will still track updates but at the partition level (refreshing any partition that has a new snapshot). References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
2 Views
Open in Slack
PreviousNext
Powered by