http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have broker load with 450million ...
# questions-and-troubleshooting
m
@Rocky I have broker load with 450million dataset with parquet of 450 files having 13 mb each. with this settings SET GLOBAL pipeline_dop = 8; ADMIN SET FRONTEND CONFIG ("load_parallel_instance_num" = "4"); PROPERTIES ( "timeout" = "7200", "max_filter_ratio" = "0.0", "load_mem_limit" = "6442450944" ) table properties: ) ENGINE=OLAP PRIMARY *KEY*(col1, col2) DISTRIBUTED BY HASH(col1) ORDER BY (col1) PROPERTIES ( "enable_persistent_index" = "true", "datacache.enable" = "false" ); In show load it shows 26 mins - FINISHED ETL:100%; LOAD:100% BROKER NORMAL 462224198 however in UI, finished query tab - Load 2026-05-22 043202 2026-05-22 043759 5m56s Finished> what is the time taken here 5 mins or 26 mins? where are the time spent ? could you please let us know in details thanks.
r
The discrepancy in time occurs because 
SHOW LOAD
 and the UI Query Tab measure different stages of the data loading lifecycle. 1. Which is the actual time? The 26 minutes shown in 
SHOW LOAD
is the total time it took for the data to be fully processed and available for querying. The 5 minutes 56 seconds in the UI represents only the "Write Stage" (the execution of the data scan and sink fragments). 2. Where was the time spent? For a Primary Key table, the loading process is split into two major phases: Phase 1: Write Stage (UI Time: ~6 mins) * What happened: StarRocks read the 450 Parquet files from the broker, parsed them, and sent the data to the Backend (BE) nodes. * Storage: The BEs wrote the raw data into new "rowsets" (segment files) on disk. * Observation: This is what you see in the UI "Finished Query" tab. It shows how long the "Load Fragment" was actively running on the CPU/Network to move data. Phase 2: Commit & Apply Stage (Gap Time: ~20 mins) * What happened: Once the raw data was written, the transaction entered the "Apply" phase. This is much heavier for Primary Key tables than for other table types. * Indexing 462M Rows: Because you have 462 million rows, StarRocks must: 1. Load the Primary Key Index: Check each of the 462M keys against the existing index. 2. Deduplication: If keys already exist, it marks the old locations as deleted in the DelVector. 3. Persistent Index Update: Since you have 
enable_persistent_index = true
, the system must update the index structure on disk. 4. Metadata Finalization: The FE coordinates the "Publish Version" to make the new data visible. * Observation: In 
SHOW LOAD
, the progress will often stay at 99% during this entire phase. It only flips to 100% and 
FINISHED
once the index updates and metadata commits are complete. Summary of Time Spent Stage | Duration | Location | Purpose Write Stage | 5m 56s | UI Query History | Reading Parquet files & writing raw data to disk. Apply/Commit | ~20 mins | 
SHOW LOAD
(Gap) | Indexing 462M rows, updating Persistent Index, and committing transaction. Total | 26 mins | 
SHOW LOAD
| Total wall-clock time until data is searchable. How to optimize? Given you have 462M rows in a single load: * Persistent Index: Writing to the persistent index is I/O intensive. Ensure your BEs are using SSD storage. * Apply Threads: You can check the BE configuration 
transaction_apply_worker_count
. If your BEs have high CPU headroom, increasing this (carefully) can speed up the "Apply" phase. * Memory: Your 
load_mem_limit
is 6GB. For 462M rows, the primary key index requires significant memory or I/O. If the BE is memory-constrained, it may trigger more frequent disk swaps for the persistent index, slowing down the process. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md
m
this change didn't help to reduce the total time taken, still the same 26 mins to complete the table load process.
@Rocky fyi
r
Thank you for the additional information. Given the details of your 450 million row dataset and the discrepancy you are seeing, here is the breakdown of why the loading process took 26 minutes even though the UI only reported ~6 minutes. 1. The Time Discrepancy Explained * UI Query Tab (5m 56s): This measures the Write Stage. It tracks how long the execution fragments were running on the BEs to read the Parquet files from your broker, parse them, and write them into temporary segment files. Once this "write" is done, the query fragment itself is considered "Finished" in the UI. * 
SHOW LOAD
(26m): This measures the Total Transaction Time. In a Primary Key table, a load is not complete after the data is written. It must then enter the Apply/Commit Stage, which is often the most time-consuming part for large datasets. 2. Where the ~20 minutes was spent For a Primary Key table with 462 million rows, the system spent the remaining 20 minutes on the following "Apply" tasks: 1. Index Loading/Lookup: The system must check the Primary Key of every one of the 462M new rows against the existing index to see if they are new inserts or updates to existing rows. 2. Persistent Index Updates: Since you have 
enable_persistent_index = true
, the BE must write these index updates to disk. If your BE disks (SSDs) are under high I/O or if the memory is tight, this takes time. 3. Generating Delete Vectors: For any rows that were updates, StarRocks must find the old version of the row and mark it as deleted in the 
DelVector
. 4. Transaction Publish: The FE coordinates with all BEs to "Publish" the version, making the 462M rows visible to queries. 3. Optimization Insights for your 450M Row Load * Memory Pressure: Your 
load_mem_limit
is set to 6GB. For 462M rows, the metadata and index structures are quite large. If the memory is too low, the BE may have to swap parts of the persistent index between disk and memory frequently during the Apply phase, significantly increasing the time. _ _Tip:* Check the BE logs for "memory limit exceeded" or "low memory" warnings, or monitor the 
update
mem_tracker on the BE. * Small File Overhead: You have 450 files of only 13MB each. While StarRocks can handle this, 13MB is considered very small. This creates many small scan tasks. If possible, merging these into larger files (e.g., 100MB–200MB) can reduce the overhead of the Write stage. * Apply Parallelism: You can speed up the 20-minute gap by increasing the concurrency of the apply phase in your `be.conf`: * `transaction_apply_worker_count`: (Default is the number of CPU cores). If your CPU usage is low during the "gap" period, you can try increasing this to allow more parallel index updates. Summary: The 26 minutes is the true time until your data is ready for use. The gap is the mandatory "Apply" logic required to maintain the Primary Key constraints for 462 million records. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • faq/loading/Loading_faq.md
m
@Rocky we have not seen memory/cpu being consumed during the Phase 2: Commit & Apply Stage and also, total time taken is still 26 mins so what else is causing the performance degradation. help me here
r
If your CPU and memory usage are low during the "Apply" phase, it indicates the system is not compute-bound but is likely waiting on I/O or artificially delayed by a safety mechanism. Here are the three most likely causes for a 20-minute gap when CPU/Memory appear idle: 1. Disk I/O Bottleneck (Persistent Index) Since you have 
enable_persistent_index = true
, StarRocks stores the 462M primary keys on disk rather than in RAM. During the Apply phase, the system must perform a massive number of random reads and writes to the disk to check if keys exist and update their locations. * Why CPU/Mem is low: The threads are in a "Wait" state (I/O Wait) while the disk controller processes these requests. * Check: Monitor your disk I/O utilization and latency (e.g., 
iostat -x 1
or Grafana). If 
%util
is near 100% or 
await
is high, your disks are the bottleneck. * Solution: Ensure you are using NVMe SSDs. If you are using Cloud EBS or HDD, the persistent index will be very slow. 2. High Compaction Score (Intentional Delay) StarRocks has a "flow control" mechanism. If a table has too many un-compacted versions (rowsets), the system will deliberately slow down the commit of new loads to prevent query performance from collapsing. * The Cause: You are loading 450 files. If those 450 files are creating 450 small rowsets, the "compaction score" will spike. * Check: While the load is running, execute:
Copy code
sql
    SHOW PROC '/transactions/<your_db_name>/running';
Look at the 
ErrMsg
column. If you see "Partition's compaction score is larger than 100.0, delay commit for XXXms", then StarRocks is intentionally making you wait. * Solution: Increase compaction threads in `be.conf`:
Copy code
properties
    update_compaction_num_threads_per_disk = 4
    update_compaction_per_tablet_min_interval_seconds = 60
3. Metadata Locking / FE Publish In some cases, the Frontend (FE) can become a bottleneck when "Publishing" the transaction if there are many tablets. * The Cause: If your table has a very high number of Buckets (Tablets) multiplied by the number of replicas, the FE has to coordinate thousands of metadata updates. * Check: Check your tablet count with 
SHOW TABLETS FROM <table_name>
. If you have thousands of tablets for a 450M dataset, the overhead is high. * Solution: Aim for tablet sizes between 10GB–50GB. If your tablets are very small (e.g., < 1GB), reduce the number of buckets. Recommendation 1. Check the Transaction Status: Run 
SHOW PROC '/transactions/<db>/running';
immediately during the next load. This is the "smoking gun" for artificial delays. 2. Profile the Load: Run 
SET enable_profile = true;
before starting the load, then use 
SHOW PROFILELIST;
and 
ANALYZE PROFILE FROM '<query_id>';
after it finishes. This will show exactly where the "Apply" time was spent. 3. Merge Files: 13MB files are very small. Try merging your 450 files into 10–20 larger Parquet files before loading. This significantly reduces the number of rowsets created, lowering the pressure on the compaction system. References • best_practices/primarykey_table.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • loading/Flink-connector-starrocks.md • table_design/table_types/primary_key_table.md
m
@Rocky please help me if i make below changes will it work with improving broker load timings. 1*Add `BUCKETS 32`* to CREATE TABLE • Remove 
ORDER BY
 on PK table - StarRocks 3.5.13 silently accepts and ignores 
ORDER BY
on PK tables (some versions do) • It's actually applying a redundant sort that wastes work Add 
write_buffer_size = 1 GB
 + 
load_process_max_memory_limit_percent = 50
Bump load_mem_limit to 8 GB
Enable datacache.enable = true - The persistent index requires index lookups for every batch (to detect new vs existing keys). With datacache.enable = false, every index page lookup goes to remote S3. For 460M rows in 32 batches = many lookups, all hitting S3.
update_compaction_per_tablet_min_interval_seconds to 300
r
Your proposed changes target the correct areas, but some require caution as they might have the opposite effect depending on your environment. Here is an evaluation of each change: 1. 
datacache.enable = true
(Highly Recommended) This is likely your "silver bullet." Since you mentioned index lookups hitting remote S3, you are likely running a Shared-Data (Cloud-Native) cluster. * Why it matters: In Shared-Data mode, the Persistent Index can be stored in S3 (since v3.3.2). If 
datacache.enable
is 
false
, StarRocks cannot cache the index pages on the BE's local disk. * The Impact: Every one of your 462 million rows requires an index lookup during the "Apply" phase to check for duplicates. If every lookup is a network round-trip to S3, the "Apply" phase will be extremely slow while the CPU remains idle (waiting on network I/O). * Action: Set this to 
true
immediately. 2. 
write_buffer_size = 1 GB
and Memory Bumps (Recommended) * `write_buffer_size`: Increasing this from the default (100MB) to 1GB is excellent. It allows the BE to buffer more data before flushing a "rowset" to disk. This will result in fewer, larger rowsets. * `load_process_max_memory_limit_percent = 50`: This gives the loading process more breathing room. * `load_mem_limit = 8 GB`: A good incremental step. However, for 462M rows, if you still see "Apply" delays, consider 16GB if your BE hardware allows it. 3. 
BUCKETS 32
(Recommended) * Currently, if you don't specify buckets, StarRocks uses "Automatic Bucketing." For 450M rows, 32 buckets results in ~14M rows per tablet. * This is a healthy size (typically you want 100MB–1GB per tablet). It ensures that the workload is distributed across your BEs without creating "too many" small tablets that overwhelm the metadata/commit process. 4. Removing 
ORDER BY (col1)
(Clarification Needed) * The Myth: It is not true that StarRocks "silently ignores" 
ORDER BY
on Primary Key tables. Since v3.0, the Sort Key is decoupled from the Primary Key. * The Reality: * If you use 
ORDER BY (col1)
, StarRocks sorts the data by 
col1
and builds the Prefix Index on 
col1
. * If you remove it, StarRocks defaults to the Primary Key 
(col1, col2)
as the sort key. * Performance: Sorting by two columns instead of one actually takes more CPU work, not less. If your queries only filter by 
col1
, keeping the 
ORDER BY (col1)
is actually more efficient. It does not cause "redundant work" during the load. 5. 
update_compaction_per_tablet_min_interval_seconds = 300
(Use Caution) * Warning: This increases the time between compactions (default is 120s). * If your 26-minute delay is caused by a "High Compaction Score" (the system waiting for background cleanup), increasing this will make the problem worse because old versions will stay around longer. * Better Alternative: Keep the default interval and instead increase the threads to clean up faster:
Copy code
properties
    update_compaction_num_threads_per_disk = 4
Summary of Recommended Actions: 1. Enable Data Cache: 
datacache.enable = true
is mandatory for Primary Key performance in Shared-Data clusters. 2. Optimize BE Configs: Apply the 
write_buffer_size
and memory percent changes. 3. Monitor the Gap: During your next load, run 
SHOW PROC '/transactions/<db>/running';
. * If you see "delay commit," the issue is Compaction. * If you see no error but it's still slow, the issue was S3 I/O (fixed by Data Cache). by Data Cache). References • administration/management/BE_configuration.md • deployment/shared_data/feature-support-shared-data.md • table_design/table_types/primary_key_table.md References
m
@Rocky few clarification - 450M rows, each parquet file size of 200mb so how to determine the no of buckets to create a table, is there no of records per tablets in a table ?
r
To determine the number of buckets for your table with 450 million rows and 90GB of data (450 files × 200MB), you should follow these guidelines based on StarRocks' architecture: 1. The "Size per Tablet" Rule StarRocks generally recommends that each tablet (a bucket within a partition) should contain between 1 GB and 10 GB of raw data. * Total Data Size: ~90 GB. * Targeting 3 GB per tablet: 90 GB / 3 GB = 30 Buckets. * Targeting 10 GB per tablet: 90 GB / 10 GB = 9 Buckets. 2. The "Records per Tablet" Rule While size is the primary metric, for Primary Key tables, the number of rows matters because the index lookup and "Apply" overhead grow with the number of rows per tablet. * Recommendation: Aim for 10 million to 20 million rows per tablet. * Calculation: 450 million rows / 15 million rows = 30 Buckets. 3. How to Decide for Your Setup * If you have NO Partitions: Set 
BUCKETS 32
. This keeps your tablets around 14M rows and ~3GB each, which is an ideal "sweet spot" for balancing load parallelism and query performance. * If you HAVE Partitions (e.g., by day): The 
BUCKETS
value applies to each partition. * If each daily partition has ~15M rows, use 
BUCKETS 1
or 
2
. * If you have 450M rows total across 10 partitions, use 
BUCKETS 3
or 
4
(so each partition has ~45M rows split into ~4 tablets). 4. Why your Parquet file size (200MB) matters The Parquet file size is the compressed size. StarRocks' "10 GB rule" refers to uncompressed/raw data. * Parquet is highly compressed (often 5x or more). * Your 90GB of Parquet files likely represents 400GB - 500GB of raw data. * Revised Calculation: 500GB raw data / 10GB per tablet = 50 Buckets. Final Recommendation: For a 450M row dataset with 90GB of Parquet files: 1. Use 
BUCKETS 32
or 
BUCKETS 48
 if you are not using partitions. This ensures you have enough parallelism for your BE nodes to process the "Apply" phase without creating tablets that are too large (which slows down compaction) or too small (which bloats metadata). 2. Enable Automatic Bucketing (Recommended): Since StarRocks v2.5.7, you can simply omit the 
BUCKETS
keyword:
Copy code
sql
    DISTRIBUTED BY HASH(pk_col) -- No BUCKETS specified
StarRocks will automatically calculate the number of buckets based on your cluster size and the volume of data you are loading. This is the safest way to avoid manual estimation errors. Note: If your cluster has many BE nodes (e.g., 10+), ensure your total bucket count (Buckets × Partitions) is at least 1-2x the number of CPU cores in the cluster to maximize parallel processing. References • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • table_design/table_types/primary_key_table.md • administration/management/BE_configuration.md • loading/loading_introduction/loading_considerations.md References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • table_design/table_types/primary_key_table.md
m
@Rocky do i need to set these properties in fe.config file -- 2. If you are running multiple tables concurrently, expand the global query transaction coordinator pool ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "200"); ADMIN SET FRONTEND CONFIG ("load_parallel_instance_num" = "4"); SET GLOBAL pipeline_dop = 8;
r
These properties mostly affect the Scanning & Loading phase (Phase 1), not the Commit & Apply phase (Phase 2) where you are currently seeing the 20-minute delay. Here is the breakdown of whether you should use them: 1. 
max_running_txn_num_per_db
 * Do you need it? Likely No. * Reason: This limits how many different load jobs can run at the same time. Since StarRocks v3.1, the default is already 1000. Setting it to 
200
actually makes your limit stricter than the default. It will not make a single load job run faster. 2. 
load_parallel_instance_num
 * Do you need it? Maybe, but it won't fix the 20-minute "Apply" gap. * Reason: This increases the number of parallel scanner instances on each BE. Since you have 450 files, increasing this to 
4
will help read those files faster during the first phase of the load. * Note: This is an FE Configuration. You can set it at runtime without restarting:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("load_parallel_instance_num" = "4");
3. 
SET GLOBAL pipeline_dop = 8;
 * Do you need it? No (Keep it at 
0
). * Reason: In StarRocks 3.x, the default is 
0
, which means "Adaptive." The system automatically uses half of your available CPU cores. If your BE nodes have 32 cores, 
pipeline_dop = 0
will use 16. Setting it to 
8
manually might actually slow down your load by limiting parallelism. * Note: This is a System Variable, not an FE config. ────────── Why these won't fix your current problem The "Phase 2: Commit & Apply" stage is where StarRocks updates the Primary Key Index. Because your CPU and Memory are idle during this 20-minute gap, the bottleneck is almost certainly I/O Latency (waiting for the disk/S3) or Compaction backpressure. If you want to reduce the 26-minute total time, focus on the Primary Key Apply settings instead: 1. Enable Data Cache (Crucial for Shared-Data): If you are on a shared-data cluster, the Persistent Index lives in S3.
Copy code
properties
    # be.conf
    datacache.enable = true
Without this, the "Apply" phase is stuck waiting for S3 network round-trips for every key check. 2. Increase Apply Threads: Tell the BE to use more threads specifically for the "Apply" phase of Primary Key tables:
Copy code
properties
    # be.conf
    apply_done_worker_count = 10
    transaction_publish_worker_count = 16
3. Check for "Wait" in Transaction: While the load is in that 20-minute "idle" state, run:
Copy code
sql
    SHOW PROC '/transactions/<your_db>/running';
If the 
ErrMsg
says anything about "compaction score too high" or "wait for publish", then the system is intentionally pausing the load to let background cleanup catch up. Summary: Only set 
load_parallel_instance_num = 4
if you want to speed up the initial data reading. To fix the 20-minute "Apply" hang, focus on Data Cache and Disk I/O. References • sql-reference/System_variable.md • administration/management/FE_configuration.md • loading/StreamLoad.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md
m
@Rocky after making all these changes i ran into another error type:LOAD_RUN_FAIL; msg:Memory of process exceed limit. Pipeline Backend: starrocks-cn-0.starrocks-cn-search.data-box-261-2-10-ms-149367-snapshot.svc.cluster.local, fragment: 019e57ae-a174-7c05-9ea3-ab3604b64ad2 Used: 22526917912, Limit: 20873541058. Mem usage has exceed the limit of BE: BE:10006
r
The error "Memory of process exceed limit" occurred because your new configuration (
write_buffer_size = 1 GB
) is too aggressive for the amount of RAM allocated to your Compute Nodes (CNs). Why this happened According to your error message: * Limit: ~19.4 GB (This is the total memory the StarRocks process is allowed to use). * Used: ~21.0 GB (The process tried to use more than its limit). When you set 
write_buffer_size = 1 GB
, StarRocks allocates up to 1 GB of RAM per tablet (bucket) being written to on that node. * You have 32 buckets. * If you have 3 nodes, each node handles ~10 buckets. * 10 buckets × 1 GB = 10 GB just for the write buffers. * Add the memory for the Primary Key Index, the Data Cache, and the base process requirements, and you quickly exceed the 19.4 GB limit. How to fix it 1. Reduce 
write_buffer_size
 For a node with only 20 GB of RAM, 1 GB buffers are too large. Reduce this to a more balanced value:
Copy code
properties
# be.conf / cn.conf
write_buffer_size = 256 MB
This still gives you 2.5x the default (100MB) but won't crash your node. 2. Adjust 
load_process_max_memory_limit_percent
 You set this to 50%, which is fine, but remember that the Primary Key "Persistent Index" also needs memory. If the load process takes 50% and the Index/Cache takes the other 50%, the node has zero room for queries or overhead. * Suggestion: Keep it at 
50
, but only if you lower the 
write_buffer_size
as suggested above. 3. Increase the CN Memory (If possible) A 20 GB limit for a StarRocks node handling 450M rows is quite small. If you are running on Kubernetes, consider increasing the 
resources.limits.memory
and 
resources.requests.memory
for your CN pods to at least 32 GB or 64 GB. 4. Lower 
load_mem_limit
for the job In your Broker Load statement, you bumped the limit to 8 GB. Since the node's total limit is only ~19 GB, a single load taking 8 GB is very risky. * Suggestion: Use 
4 GB
or 
6 GB
until you have more physical RAM. Recommended "Safe" Configuration for 20GB Nodes: 1. 
write_buffer_size
: 
256MB
(or 
512MB
if you have very few concurrent loads). 2. 
load_process_max_memory_limit_percent
: 
40
(to leave room for the Persistent Index). 3. 
datacache.enable
: Keep this 
true
, but ensure 
datacache.mem_cache_limit
isn't set too high (it defaults to a small % of RAM, which is fine). Summary: The crash was caused by the combination of 32 buckets and 1GB buffers on a machine with only 20GB of RAM. Lowering the buffer size will resolve the "Memory exceed limit" error while still keeping the performance benefits of the other changes. References • administration/management/BE_configuration.md • loading/loading_introduction/loading_considerations.md • administration/management/resource_management/Memory_management.md • faq/loading/Loading_faq.md • best_practices/primarykey_table.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md References • faq/loading/Loading_faq.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • best_practices/primarykey_table.md • loading/loading_introduction/loading_considerations.md
m
write_buffer_size = 1 GB is this the value in cn.conf file or what is the actual value?
@Rocky help me understand is this correct - I was running a performance testing with huge table (450 millions dataset), while in the PREPARED state, another task trigger to load, which started running but previous task was taking long time to finish hence we should wait until the task fully FINISHED then initiate another task is give enough room to process comfortably.
r
Yes, your understanding is correct. For a performance test involving massive datasets (450M rows), waiting for a task to reach the 
FINISHED
 state before starting the next one is the best practice. Here is a breakdown of why the 
PREPARED
 state is a critical "waiting room" and why overlapping tasks caused your memory crash: 1. What "PREPARED" actually means In a StarRocks Broker Load, the state transition is: 
PENDING
$\rightarrow$ 
LOADING
$\rightarrow$ 
PREPARED
 $\rightarrow$ 
FINISHED
. * 
LOADING
: The BE nodes are actively reading the Parquet files and writing them into temporary segments. * 
PREPARED
: The transaction has been committed. However, the data is not yet visible. The system is now in the "Apply" or "Publish" phase. * For Primary Key tables, this is the most resource-intensive part. The BE must: 1. Check the Primary Key Index for every one of those 450M rows. 2. Mark old versions of data as deleted. 3. Update the Persistent Index on disk or S3. 4. Finalize the new data segments. 2. The Risk of Overlapping Loads If you trigger a second 450M-row load while the first is in the 
PREPARED
state: * Memory Contention: The first task is still using memory for its Persistent Index updates and "Apply" buffers. The second task starts its 
LOADING
phase and immediately demands Write Buffers (the 
write_buffer_size
you configured) and its own share of memory. This is exactly what led to your 
Memory of process exceed limit
error. * I/O and CPU Bottleneck: Both tasks will fight for disk I/O and CPU threads to handle index lookups. This causes the "Apply" phase of the first task to stretch even longer. * Compaction Backpressure: StarRocks limits how many data versions can exist at once. Overlapping huge loads creates many small versions quickly, which can trigger a "Compaction Score" limit that pauses all incoming writes. 3. Recommendation for Performance Testing To get clean, reliable performance metrics and avoid crashes: 1. Serialize the Tasks: Wait for 
State = FINISHED
and 
Progress = 100%
. This ensures all background index updates and metadata publishing are complete. 2. Monitor the Gap: If you see a long gap between 
PREPARED
and 
FINISHED
, it means your "Apply" phase is slow. This is usually fixed by increasing BE memory or optimizing disk I/O (like enabling the Data Cache), not by starting more tasks. 3. Check Visibility: Data only becomes queryable after the state turns to 
FINISHED
. Summary: Your intuition is right. Starting a second massive load while the first is committed but still "applying" (PREPARED) is like trying to pour more water into a funnel that is already full—it won't go faster and will eventually overflow (crash). References • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md • loading/Load_to_Primary_Key_tables.md • administration/management/BE_configuration.md • table_design/table_types/primary_key_table.md References • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md
m
@Rocky After making all these changes bellow broker load failed with this error. so help me how to resolve this ErrorMsg: type:LOAD_RUN_FAIL; msg:Memory of Query019e6134-75dd-7ace-a4a2-fa08cbae617a exceed limit. Pipeline Backend: starrocks-cn-2.starrocks-cn-search.data-box-261-2-10-ms-149367-snapshot.svc.cluster.local, fragment: 019e6134-75dd-7ace-a4a2-fa08cbae617b Used: 1076547536, Limit: 1073741824. Mem usage has exceed the limit of single query, You can change the limit by set session variable query_mem_limit.: BE:282791, TrackingLog: No tracking log available", LOAD LABEL db1.shadow_temp1_insert_20260525_w476567 ( DATA INFILE(*"s3://bucket1/test/*"*) INTO TABLE shadow_temp1 FORMAT AS "parquet" SET (
col1
 = md5(
col1
), 
col2
 = *ifnull*(
col2
, 'nan'), 
col3
 = 
col3
, 
col4
 = 
col4
, 
col5
 = 
col5
) ) WITH BROKER ( "aws.s3.region" = "us-west-2", "aws.s3.use_instance_profile" = "false", "aws.s3.use_aws_sdk_default_behavior" = "true" ) PROPERTIES ( "timeout" = "7200", "max_filter_ratio" = "0.0", "load_mem_limit" = "1073741824" -- replace this with anything that we need -- );
r
The error occurred because you explicitly set a very tight memory limit of 1 GB (
1073741824
bytes) in your load properties. Your load process used 1.002 GB, which tripped the limit and caused the failure. For a table with 450 million rows and transformations like 
md5()
, 1 GB is not enough memory to buffer the data and handle the computation on each node. How to resolve this 1. Increase 
load_mem_limit
in the Broker Load Statement You should increase this value to at least 4 GB or 8 GB. Given your nodes have 20 GB of RAM, 4 GB is a safe and effective starting point. Update your 
PROPERTIES
block like this:
Copy code
sql
PROPERTIES
(
    "timeout" = "7200",
    "max_filter_ratio" = "0.0",
    "load_mem_limit" = "4294967296" -- Set to 4GB (4 * 1024 * 1024 * 1024)
);
2. Why 1 GB was too small * Transformations: The 
md5(col1)
function requires memory to process the strings during the loading phase. * Buffering: StarRocks buffers data in memory before flushing it to disk as segments. If you have many buckets (e.g., 32), the memory is split across those buffers. * Primary Key Overhead: While much of the index is on disk (if using Persistent Index), the loading "fragment" still needs memory to coordinate the upsert logic. Important Notes * Per-Node Limit: The 
load_mem_limit
is per BE/CN node, not a total for the whole cluster. If you have 3 nodes and set it to 4 GB, the job can use up to 12 GB total across the cluster. * Session Variables vs. Properties: Although the error message mentions 
query_mem_limit
, for a 
BROKER LOAD
, the value you provide in the 
PROPERTIES
block (
load_mem_limit
) takes precedence. You do not need to set a session variable. * Previous Configs: Since you recently set 
write_buffer_size = 256MB
, ensure that 
load_mem_limit
is significantly higher than that. If 
load_mem_limit
is too close to your buffer sizes, the job will fail as soon as it starts writing data. Recommendation: Set 
load_mem_limit
to 
4294967296
(4GB). If it fails again with the same error, move it to 
8589934592
(8GB). References • faq/loading/Loading_faq.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • best_practices/primarykey_table.md • faq/loading/Broker_load_faq.md
m
@Rocky mulitple errors are popping up now. after setting this also so not sure what's going on. PROPERTIES ( "timeout" = "7200", "max_filter_ratio" = "0.0", "load_mem_limit" = "6442450944" -- replace this with anything that we need -- ); errormsg: type:load_run_fail; msg:memory of process exceed limit. pipeline backend: starrocks-cn-0.starrocks-cn-search.data-box-261-2-10-ms-149367-snapshot.svc.cluster.local, fragment: 019e6571-11c4-76f4-8d2f-427a488bbcee used: 20878889272, limit: 20873541058. mem usage has exceed the limit of be: be:10006, trackinglog: no tracking log available", errormsg: type:load_run_fail; msg:memory limit exceeded, please reduce load frequency or increase config 
load_process_max_memory_hard_limit_ratio
or add more be nodes: be26001f14325d16045adc007: be:282791, trackinglog: no tracking log available",
r
The appearance of these two errors simultaneously indicates that your Compute Nodes (CNs) are being pushed beyond their physical capacity. You are hitting two different ceilings: 1. Process Limit: The entire StarRocks process tried to use more than the ~21GB limit (90% of your 23-24GB RAM). 2. Load Hard Limit: The sum of all loading tasks hit the "hard limit" (Soft Limit × Hard Ratio), causing the system to reject new load requests. 1. The Root Cause: Overlapping Loads + Large Index The error message 
memory limit exceeded, please reduce load frequency
is a smoking gun. It means you are likely triggering a new load while the previous one is still in the "Apply" (PREPARED) phase. For 450 million rows on a Primary Key table: * The Index is huge: Even with a "Persistent Index," StarRocks needs memory to cache the index entries. If you don't have the Persistent Index enabled, 450M rows could easily take 12–16 GB of RAM just for the index metadata. * The "Apply" phase: When a load reaches the 
PREPARED
state, it consumes significant memory to update this index. If you start a second load (which asks for another 6 GB via 
load_mem_limit
), the node crashes because 
Index Memory + Load 1 + Load 2 > 21 GB
. 2. Immediate Fixes A. Lower the Memory Request Your 
load_mem_limit
of 6 GB is too high for a machine with only 20 GB of usable RAM when dealing with 450M rows. * Change to: 
"load_mem_limit" = "4294967296"
(4 GB). * Why: This gives the "Apply" phase and the Primary Key Index more room to breathe. B. Adjust BE/CN Configurations You need to re-balance how the 20GB is shared between the "Active Loading" and the "Index/System". Update your 
cn.conf
(or 
be.conf
) and restart the nodes:
Copy code
properties
# Reduce the soft limit for loading to leave room for the Index
load_process_max_memory_limit_percent = 30 

# Allow the load to briefly spike during the Apply phase
load_process_max_memory_hard_limit_ratio = 3

# Ensure you are using the Persistent Index (CRITICAL for 450M rows)
# This is a table property, but ensure the BE can handle it
C. Enable Persistent Index (If not already) If your table does not have 
enable_persistent_index = true
, your 20GB nodes cannot handle 450M rows. The index will live entirely in RAM and crash the node. * Check your table schema. If it's missing, you should recreate the table with: 
PROPERTIES ("enable_persistent_index" = "true")
3. Strategy for your Performance Test To stop the "multiple errors popping up": 1. Wait for `FINISHED`: Do not trigger the next load until 
SHOW LOAD
shows the previous label as 
FINISHED
. The 
PREPARED
state is not "done"—it's the most memory-intensive part. 2. Clean up: If you have many failed/stuck loads, they might still be holding some "zombie" memory. Restarting your CN nodes will clear the slate. 3. Check Bucket Count: If you have 32 buckets, each bucket requires a small amount of metadata. On 20GB nodes, this is fine, but don't increase it further. Summary: Your 
load_mem_limit
of 6GB is competing with the memory needed to manage 450M rows. Drop the limit to 4GB and ensure you are not overlapping the load tasks. References • administration/management/BE_configuration.md • loading/loading_introduction/loading_considerations.md • table_design/table_types/primary_key_table.md • administration/management/resource_management/Memory_management.md • faq/loading/Loading_faq.md • best_practices/primarykey_table.md References
Open in Slack
PreviousNext
Powered by