http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have a routine load that was unst...
# questions-and-troubleshooting
c
@Rocky I have a routine load that was unstable and paused for almost 24 hours. After changing 
routine_load_unstable_threshold_second
to 86400 and resuming, I see the load as RUNNING but its stats remain frozen at what they were before and nothing seems to be loading.
r
The behavior you are describing—where a Routine Load job shows as 
RUNNING
but statistics (like 
totalRows
and 
loadedRows
) remain frozen—typically indicates that no tasks are successfully finishing or even being dispatched. Increasing 
routine_load_unstable_threshold_second
only changes the condition for marking a job as "UNSTABLE" (which happens when there is significant lag); it does not resolve the underlying reason why the tasks are failing or stuck. Here is how to troubleshoot and resolve this: 1. Inspect the Task Queue The most important step is to see if StarRocks is actually generating and dispatching tasks to BE nodes. Run:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name"\G
Check these specific fields in the output: * 
BeId
: If this is 
-1
, the Frontend (FE) is failing to schedule tasks to any Backend. This often happens if the cluster-wide limit for Routine Load tasks is reached or if the FE cannot communicate with the BEs. * 
ExecuteStartTime
: If this is 
NULL
, the task has been scheduled but hasn't actually started on the BE. * 
Message
: Look for errors here. Common messages include: _ _"there is no new data in kafka..."*: StarRocks thinks it has consumed everything. Verify your Kafka offsets. _ _"failed to submit task..."*: Usually a resource or connectivity issue. _ _"Broker: Offset out of range"*: Your job has been paused for so long (24h+) that the Kafka retention policy may have deleted the messages at your current offset. 2. Verify Kafka Connectivity and Offsets Since the job was unstable for 24 hours, the consumer offsets might now be invalid or the consumer group might be stuck. * Check Lag: Check your Kafka consumer group lag using external tools (
kafka-consumer-groups.sh
). If lag is 0 but you expect data, the job is "up to date" in its own view. * Check Offset Retention: If your Kafka 
log.retention.hours
is short, the messages StarRocks is looking for might be gone. If 
SHOW ROUTINE LOAD
shows an offset that no longer exists in Kafka, you may need to:
Copy code
sql
    PAUSE ROUTINE LOAD FOR your_job_name;
    ALTER ROUTINE LOAD FOR your_job_name
    PROPERTIES ("kafka_default_offsets" = "OFFSET_END"); -- Or a specific valid offset
    RESUME ROUTINE LOAD FOR your_job_name;
3. Check FE Resource Limits If many jobs were restarted at once, you might be hitting cluster-wide limits. * 
max_routine_load_job_num
: Check if you have too many total jobs (default is 100). * 
routine_load_thread_pool_size
: If all threads are occupied by other jobs, this one will wait indefinitely. * Check the FE leader log (
fe.log
) for any 
WARN
or 
ERROR
messages containing "RoutineLoad". It will explicitly state why a task failed to schedule. 4. Why increasing 
unstable_threshold
might have hidden the issue A Routine Load job is marked 
UNSTABLE
if the difference between the message timestamp in Kafka and the current time exceeds 
routine_load_unstable_threshold_second
. * By setting it to 86400 (24 hours), you told StarRocks: "Don't warn me or change state even if I'm 23 hours behind." * If the job was "unstable" because it was slowly failing or repeatedly timing out, increasing the threshold just keeps the status as 
RUNNING
instead of 
UNSTABLE
, but the underlying failure (e.g., data format error, network timeout) still prevents data from loading. Summary Checklist 1. Run 
SHOW ROUTINE LOAD TASK
: If 
BeId
is 
-1
, check 
fe.log
. 2. Run 
SHOW ROUTINE LOAD
: Check 
ReasonOfStateChanged
(even if it says RUNNING now, there might be a historical error message). 3. Check Kafka: Ensure the 
Progress
(offset) shown in StarRocks still exists in your Kafka topic. 4. Check BE Logs: If a 
BeId
is assigned, check 
<http://be.INFO|be.INFO>
on that specific BE for task execution node for "Stream Load" or "Routine Load" errors. References
c
@Rocky where do I find the job_name?
r
You can find the 
job_name
by listing the Routine Load jobs currently configured in your database. Run the following command in your MySQL client:
Copy code
sql
-- List all routine load jobs in the current database
SHOW ROUTINE LOAD;
If you know the specific table the job is loading into, you can narrow it down:
Copy code
sql
-- List routine load jobs for a specific table
SHOW ROUTINE LOAD FOR your_table_name;
What to look for in the output: * Name: This column contains the 
job_name
you need for other commands (like 
SHOW ROUTINE LOAD TASK
). * State: Confirms if the job is actually 
RUNNING
or 
PAUSED
. * Statistic: Look at 
totalRows
and 
loadedRows
here to confirm if they are indeed stuck. * ReasonOfStateChanged: Even if the job is "RUNNING", this field might contain a legacy error message from when it was last unstable/paused, which can provide a clue. Once you have the name from the Name column, you can proceed with the detailed task inspection:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "the_name_you_found"\G
References • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md
c
@Rocky do I need to change it to camle case? ERROR 1064 (HY000): Getting analyzing error. Detail message: analysis show routine load task error: show routine load job only support one equal expr which is sames like JobName="ILoveStarRocks".
r
Yes, you must use CamelCase for the filter key. The error message you received is very specific: StarRocks expects the filter to be exactly 
JobName
. If you use 
job_name
, 
jobname
, or any other variation, the parser will fail. The Correct Syntax The command should look exactly like this:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name";
Key Requirements for this Command: 1. Case Sensitivity: The filter key must be 
JobName
(Capital J, Capital N). 2. No extra conditions: You cannot add other filters (like 
AND State = 'RUNNING'
) to this specific command. It only supports a single equality expression for the job name. 3. Quotes: Use double quotes 
" "
or single quotes 
' '
around your job name. Why this happens: Unlike standard SQL queries on tables where column names are often case-insensitive, StarRocks' administrative 
SHOW
commands have a stricter parser. It looks for the literal keyword 
JobName
to identify which job's tasks you want to inspect. Once you run it correctly, pay attention to these two columns in the output: * 
BeId
: If this is 
-1
, the tasks aren't being assigned to any worker nodes. * 
Message
: This will usually contain the exact reason why the task is stuck (e.g., "all backends are down" or "no new data in kafka"). References • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md References • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md
c
@Rocky All tasks show "previous task aborted because of timeout by txn manager"
r
The error message "previous task aborted because of timeout by txn manager" confirms that your Routine Load tasks are being killed by the StarRocks Frontend (FE) because they are taking too long to complete. Why this is happening now Since your job was paused for nearly 24 hours, there is a massive backlog of data in Kafka. When you resumed the job: 1. StarRocks started a task to consume the backlog. 2. The task either tried to pull a very large batch of data or simply took longer than the default 60 seconds to process and commit the data. 3. The Transaction Manager in the FE reached its timeout limit and forcefully aborted the transaction to prevent "zombie" tasks. 4. The scheduler then tried to start a new task, saw the previous one failed, and reported the "aborted because of timeout" message. How to Fix It 1. Increase the Task Timeout You need to give the tasks more time to handle the accumulated data. You can increase the timeout for this specific job:
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES (
    "task_timeout_second" = "300" -- Increase from 60s to 300s (5 minutes)
);
2. Adjust Batch Sizes (Optional) If the tasks are still timing out even with a higher timeout, it may be because they are trying to pull too much data at once. You can limit the amount of data processed per task to make them finish faster:
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES (
    "max_routine_load_batch_size" = "104857600", -- Limit to ~100MB per task
    "routine_load_task_consume_second" = "15"    -- Limit consumption time to 15s
);
Note: StarRocks uses the smaller of these limits to finish a task. By reducing these, you force the job to commit smaller chunks more frequently. 3. Monitor the Progress After applying the 
ALTER
statement, wait 1-2 minutes and run:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name"\G
* Check if 
TxnStatus
changes from 
UNKNOWN
or 
ABORTED
to 
COMMITTED
. * Check if 
BeId
is now assigned (not -1). If stats remain "frozen" If you see the tasks are now committing but 
totalRows
in 
SHOW ROUTINE LOAD
is still not moving: * Check Kafka Offsets: Ensure the 
Progress
shown in 
SHOW ROUTINE LOAD
is actually moving. If the offset is stuck, the job might be reading from a partition that has no new data, or it's stuck on a specific "poison pill" message. * Check BE Logs: If tasks are still aborting, look at the 
<http://be.INFO|be.INFO>
logs on the BE node assigned to the task. Search for the 
TaskId
or 
TxnId
to see if there is a specific error during the "Tablet Writer" phase (e.g., memory limits or disk pressure). References • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md References • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/RoutineLoad.md
c
@Rocky I see many aborted transactions, but no sign of why
Copy code
I20260511 19:38:51.070440 137491245463104 lake_service.cpp:631] Aborting transactions. request=skip_cleanup: true
txn_infos {
  txn_id: 2051616
  commit_time: 0
  combined_txn_log: true
  txn_type: TXN_NORMAL
  force_publish: false
  gtid: 0
}
r
The log entry from 
lake_service.cpp
you are seeing is a confirmation, not the root cause. In a StarRocks Shared-data (Lake) cluster, when the Frontend (FE) decides a transaction has failed (usually due to a timeout), it sends an RPC to the Backend (BE) to "Abort" the local task. * 
Aborting transactions
: This means the BE is simply following an order from the FE to stop work on that specific 
txn_id
. * 
skip_cleanup: true
: This indicates that the BE shouldn't delete the temporary data files immediately; the system likely intends to clean them up later via its garbage collection (AutoVacuum) process to ensure atomicity. * 
combined_txn_log: true
: This refers to a performance optimization in newer versions where metadata updates are bundled. Where the "Why" is Hidden Because the FE is the "Manager" that decided to kill the task, the detailed error isn't in the BE logs. You need to look at the Frontend (FE) Logs: 1. Check 
fe.warn.log
: Search for the 
txn_id
(e.g., 
2051616
) or the Job Name in the 
fe.warn.log
file on your leader FE node. You are looking for a message that explains why the Transaction Manager timed it out. Common FE errors: * `transaction commit timeout`: The BE took too long to write the data to S3/Object Storage. * `tablet commit error`: There was a conflict or a failure writing to the object store. * `publish version timeout`: The FE couldn't update the metadata fast enough after the data was written. The Most Likely Cause in Lake Mode Since you are in Shared-data (Lake) mode, every Routine Load task must: 1. Read data from Kafka. 2. Upload that data to your Object Storage (S3/OSS/GCS). 3. Commit the metadata to the FE. If you have a massive backlog (from being paused 24h), the BE is likely trying to upload a huge amount of data to S3. If this upload takes longer than the default 60 seconds, the FE's Transaction Manager kills it. Recommendation If you haven't already, increase the timeout significantly to let the backlog clear. In Lake mode, S3 latency can occasionally spike, so a 60s timeout is often too aggressive for large backlogs.
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES (
    "task_timeout_second" = "600" -- Set to 10 minutes
);
Also, verify your Object Store health: Ensure there are no "Slow Down" errors or rate-limiting (HTTP 503) from your S3/Object Storage provider, as this is the most common reason for "silent" aborts in StarRocks Lake clusters. References • administration/management/FE_configuration.md • loading/RoutineLoad.md • faq/shared_data_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md References • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • faq/loading/Loading_faq.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • faq/Sql_faq.md • loading/loading_introduction/troubleshooting_loading.md
c
@Rocky I also have an inactive ASYNC MV on the table. I see it is in error state. Could that be having an impact on the sync MV?
r
The short answer is no, an inactive or error-state Async MV will not directly block your Routine Load or the functionality of a Sync MV. In StarRocks, these two types of Materialized Views behave very differently during data loading: 1. The Relationship: Sync vs. Async MVs * Sync MV (Rollup): This is updated synchronously as part of the same transaction as your base table load. If your Routine Load is taking too long (timing out), the Sync MV is actually part of that "work" the BE is doing. A complex Sync MV makes the load task heavier and slower. * Async MV: This is a separate background task. It does not participate in the Routine Load transaction. When data is committed to the base table, the Async MV is notified that it should refresh, but the load doesn't wait for it. If the Async MV is "Inactive" or in an "Error" state, it simply stops trying to refresh. It won't "hold back" the base table or the Sync MV. 2. Why you might see both having issues Even though they are independent, they can be affected by the same root causes: * Schema Changes: If you recently altered the base table (added/dropped columns), this often sets dependent Async MVs to Inactive. A schema change could also cause loading issues if your Kafka data format no longer matches the table. * Resource Contention: If your cluster is under heavy pressure (CPU, Memory, or S3 IO in Lake mode) because of the 24-hour backlog, the Sync MV might be taking so long to calculate that the transaction times out. Simultaneously, the Async MV might have failed its last refresh because of the same resource shortage. * Metadata Locks: In very rare cases of heavy DDL activity, there could be metadata lock contention, but this usually manifests as "Wait Lock Timeout" errors, not "Transaction Timeout." 3. Troubleshooting Steps Since your primary problem is the Routine Load Timeout, you should focus there first: 1. Ignore the Async MV for now: It is a symptom, not the cause. Once your base table data is flowing again, you can fix it with 
ALTER MATERIALIZED VIEW <mv_name> ACTIVE;
. 2. Evaluate the Sync MV: If the Sync MV is performing complex aggregations on a massive backlog of data, it is likely the reason the Routine Load tasks are exceeding their 60-second limit. 3. Increase Timeout: As discussed, use 
ALTER ROUTINE LOAD
to increase 
task_timeout_second
to 
300
or 
600
. This gives the BE enough time to calculate the Sync MV and upload the results to S3. Summary of impact Feature | Impacted by Inactive Async MV? | Impact on Routine Load? Sync MV | No. They use different update mechanisms. | High. It adds overhead to every load task. Base Table | No. The base table "doesn't care" if the Async MV is broken. | None. Routine Load | No. Tasks won't fail because an Async MV is inactive. | N/A Recommendation: Focus entirely on clearing the backlog by increasing your 
task_timeout_second
. Once the Routine Load status shows 
RUNNING
and the 
Progress
(offsets) are moving, you can turn your attention to the Async MV and reactivate it. References • faq/loading/Routine_load_faq.md • loading/loading_introduction/loading_considerations.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/BE_configuration.md • loading/InsertInto.md
c
@Rocky in fe log I see 
2026-05-11 19:47:14.013Z WARN (pool-26-thread-1232|1541915) [RoutineLoadTaskScheduler.lambda$submitToSchedule$1():170] schedule routine load task failed
for each task. But all I see in tasks is eventual timeout
r
That 
schedule routine load task failed
warning in your FE logs, paired with the 
timeout by txn manager
error, points to a cycle where the FE (Frontend) is failing to properly "plan" or "dispatch" the task to a Backend (BE). When you see the specific log 
RoutineLoadTaskScheduler.lambda$submitToSchedule$1():170] schedule routine load task failed
, there is almost always a Java Stack Trace immediately following it in the 
fe.log
. Most Likely Causes 1. Metadata/Planning Conflict (The "Sync MV" Factor) Since you mentioned you have a Sync MV (Rollup), this is the most likely culprit. Unlike Async MVs, a Sync MV is updated in the same transaction as the load. * If the Sync MV has become "stale" or "invalid" due to a schema change on the base table, the FE will fail to generate a "Plan" for the Routine Load task. * Result: The scheduler tries to start the task, fails to plan it (logging the error you see), and then the transaction eventually "times out" because it never actually started on a BE. Action: Check if your Sync MV is healthy. Try to drop and recreate the Sync MV if you suspect it's stuck.
Copy code
sql
-- Check the status of rollups/sync MVs
SHOW ALTER TABLE COLUMN; 
-- or 
SHOW ALTER TABLE ROLLUP;
2. Resource/Slot Exhaustion The FE might be failing to schedule the task because it can't find a BE with an available "slot." * StarRocks limits how many Routine Load tasks can run per BE (default is often small). * If you have many concurrent Routine Load jobs or your BEs are overloaded, the scheduler will fail to "submit" the task. Action: Check your BE load and thread settings:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE "max_routine_load_task_num_per_be";
3. Hidden Exception in FE Logs The log line you found is just the "wrapper." You need to look at the next 5–10 lines in the 
fe.log
to see the actual Exception. Common ones include: * `java.lang.NullPointerException`: Often caused by a bug when using functions like 
now()
or complex 
COLUMNS
mappings in Routine Load. * `java.lang.IllegalStateException`: Can happen if the table schema and the Routine Load mapping are out of sync. * `ConnectException`: FE cannot reach the BEs to hand off the task. Recommended Next Steps 1. Find the Stack Trace: Run this command on your FE leader node to see the specific error:
Copy code
bash
    grep -A 20 "schedule routine load task failed" fe.log | head -n 30
Look for the line starting with 
java.lang...
 2. Verify Table/MV Health: Ensure the base table isn't in a "Schema Change" state:
Copy code
sql
    SHOW BACKENDS; -- Ensure all are Alive: true
    SHOW PROC '/cluster_balance/tablet_scheduler'; -- Ensure no massive backlog of repairs
3. Check if "Safe Mode" is on: If your disk space is low, the cluster might enter a read-only "Safe Mode" which prevents new load transactions from scheduling. Summary: The Async MV being inactive isn't the problem, but if your Sync MV is in an error state or is incompatible with the current table schema, it will cause the Routine Load planner to crash, leading to these scheduling failures. References • loading/RoutineLoad.md • administration/management/FE_configuration.md • faq/loading/Routine_load_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • administration/management/BE_configuration.md References
c
@Rocky the scheduling problem was because the job becoame unstable again. Fixed that. I see it as running but oddle CPU is 0% on all backends. How can I find a backend for given be id?
r
To find the backend server associated with a specific ID, you can use the following command in your MySQL client: 1. Mapping 
be_id
to an IP Run the command below. The 
BackendId
column will match your 
be_id
, and the 
IP
column will tell you which physical server it is.
Copy code
sql
SHOW BACKENDS;
*Note: In Shared-data (Lake) mode, your workers might be Compute Nodes (CN) instead of traditional Backends. If you don't see the ID in the above list, try:*
Copy code
sql
SHOW COMPUTE NODES;
────────── 2. Why is CPU 0% if Routine Load is "RUNNING"? If the job status is 
RUNNING
but backends show 0% CPU and your backlog isn't moving, it usually means the tasks are being scheduled but failing immediately or are stuck in a "Waiting" state. Here is how to diagnose the "Silent Stall": Check the current tasks The Job status 
RUNNING
only means the "Manager" is active. To see what the "Workers" are doing right now, run:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name";
* Look at the 
BeId
column: This confirms which backends are actually supposed to be working. * Look at the `StartTime`: If the start times are only a few seconds ago and keep rotating, the tasks are crashing/timing out and restarting in a loop. This is why CPU stays at 0%—the BE never gets enough time to actually process data. Common reasons for 0% CPU in Lake Mode: 1. Object Storage Latency: In Lake mode, the BE spends most of its time waiting for the Object Store (S3/OSS) to acknowledge data writes. If the network is throttled or the Object Store is slow, the BE is "I/O bound" and uses almost zero CPU. 2. Kafka Connection Issues: If the BEs cannot reach your Kafka brokers, the task will sit idle waiting for a connection timeout, using no CPU, and then fail. 3. Small Batch Sizes: If 
max_routine_load_batch_size
is too small, the BE spends more time "committing" (talking to the FE) than actually "processing" (CPU work). What to do next: 1. Check BE Logs: Now that you have the IP address of the backend, log into that server and check 
<http://be.INFO|be.INFO>
. Search for the 
txn_id
you saw earlier. 2. Increase Batch Size: Since you have a 24h backlog, allow the tasks to take more data at once so they spend more time processing and less time "chatting":
Copy code
sql
    ALTER ROUTINE LOAD FOR your_job_name
    PROPERTIES (
        "max_batch_interval" = "30",
        "max_batch_rows" = "500000"
    );
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_BACKENDS.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • administration/management/BE_configuration.md • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • loading/loading_introduction/loading_concepts.md References • administration/management/BE_configuration.md • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • quick_start/routine-load.md • administration/management/monitoring/metrics.md
c
@Rocky it seems like no tasks ever start consuming from Kafka. I have verified that the topics do contain offsets earlier than the last offsets consumed. But it seems like the tasks never receive anything. I don't think this is because the jobs are too resource intensive
r
If the job is 
RUNNING
but no data is moving and CPU is at 0%, the "pipeline" is likely stuck at the very first step: the connection between the Backend (BE) and Kafka. Even if the job says it's running, the individual tasks might be failing to fetch data or timing out silently. Here is how to pinpoint the blockage: 1. Check the "Task Message" Run the following command and look closely at the 
Message
and 
DataSourceProperties
columns:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name" \G
* Look for this message: 
there is no new data in kafka, wait for 20 seconds to schedule again
. * If you see this but know there is data, the BE is looking at a specific offset that Kafka claims doesn't exist or is in the future. * Look at `DataSourceProperties`: This shows exactly which partitions and offsets the task is currently trying to read. Compare these numbers to your actual Kafka offsets. 2. Verify Kafka Connectivity from the BE StarRocks BEs use a C++ library (
librdkafka
) to talk to Kafka. A common issue is that the FE can reach Kafka (to check offsets), but the BEs cannot. * Hostname Resolution: Kafka often advertises brokers by hostname, not IP. Every StarRocks BE node must be able to resolve and ping the Kafka broker hostnames. _ _Fix:* Add the Kafka broker hostnames to 
/etc/hosts
on all BE servers. * Port Access: Ensure the BEs can reach the Kafka ports (usually 9092). _ _Test:* From the BE shell: 
telnet <kafka_broker_host> 9092
3. Inspect the BE Logs (The Source of Truth) Since the CPU is 0%, the BE is likely "idle" because it's waiting on a network timeout or a library error. 1. Log into the BE server (the one you found using 
SHOW BACKENDS
). 2. Go to the log directory (usually 
storage/log/
or 
be/log/
). 3. Search for RoutineLoad or Kafka errors:
Copy code
bash
    grep -i "routine_load" <http://be.INFO|be.INFO> | tail -n 100
    grep -i "kafka" <http://be.INFO|be.INFO> | tail -n 100
* Look for: 
Local: Timed out
, 
Broker: Leader not available
, or 
SSL handshake failed
. 4. Common "Silent" Killers * 
routine_load_kafka_timeout_second
: By default, StarRocks waits 10 seconds for Kafka to respond. If your Kafka is slow or under load, the BE might be timing out before it even gets one message. _ _Check:* 
ADMIN SHOW FRONTEND CONFIG LIKE "routine_load_kafka_timeout_second";
* Consumer Group Lock: If you have another tool (like a console consumer or another StarRocks job) using the exact same 
group.id
, Kafka might be constantly rebalancing, preventing the BE from starting consumption. _ _Fix:* Change the 
group.id
in your Routine Load job properties to something unique. Summary Checklist 1. Does 
SHOW ROUTINE LOAD TASK
show the correct partitions? 2. Does the 
Message
column say "no new data"? 3. Can the BE server ping the Kafka broker hostnames? 4. Are there any 
librdkafka
errors in the 
<http://be.INFO|be.INFO>
log? Note on the Sync MV: If the CPU is 0%, the Sync MV is not the problem yet. The Sync MV only consumes CPU after data has been fetched from Kafka. Since you're not even seeing "Received Bytes" increase in 
SHOW ROUTINE LOAD
, the problem is strictly between the BE and Kafka. References • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • administration/management/BE_configuration.md • quick_start/routine-load.md • loading/RoutineLoad.md References • administration/management/BE_configuration.md
c
@Rocky the problem seems to be that the job is not getting data from kafka. I'm pretty sure that this is because it is requesting older offsets. I set FE 
routine_load_kafka_timeout_second
to 120 seconds but that does not seem to help. Any advice before I resume from latest instead?
r
If the job is 
RUNNING
but not fetching data and you suspect it’s stuck on old/expired offsets, resuming from 
latest
should be your last resort, as it will cause you to lose all the data currently in your Kafka backlog. Before you jump to 
latest
, perform these specific checks to see exactly where the disconnect is: 1. Compare "Progress" vs "Latest" Run the following and look at the end of the output:
Copy code
sql
SHOW ROUTINE LOAD FOR your_job_name \G
* 
Progress
: This is the last offset StarRocks successfully committed. * 
LatestSourcePosition
: This is the latest offset currently available in Kafka (as seen by the FE). * The Gap: If 
LatestSourcePosition
is much higher than 
Progress
, StarRocks knows there is data, but the BEs are failing to pull it. 2. Check for "Offset Out of Range" (Expired Data) If the offsets in 
Progress
are older than your Kafka retention period (e.g., they were produced 8 days ago but your retention is 7 days), the BE will request data that no longer exists. * The Symptom: Usually, the job would enter a 
PAUSED
state with 
Broker: Offset out of range
. * The Manual Check: Use a Kafka tool to find the earliest available offset:
Copy code
bash
    # Using Kafka's built-in tool
    kafka-run-class.sh kafka.tools.GetOffsetShell --broker-list <brokers> --topic <topic> --time -2
* The Fix (instead of 
latest
): If the data is expired, don't jump to 
latest
. Instead, jump to the earliest available offset to preserve as much data as possible:
Copy code
sql
    STOP ROUTINE LOAD FOR your_job_name;

    ALTER ROUTINE LOAD FOR your_job_name
    PROPERTIES (
        "kafka_partitions" = "0,1,2", -- Specify all your partitions
        "kafka_offsets" = "OFFSET_BEGINNING" -- This tells SR to grab the oldest data STILL available
    );

    RESUME ROUTINE LOAD FOR your_job_name;
3. Check the "Message" in the Task The FE might be failing to even hand the task to the BE. Run:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name" \G
If you see 
Message: there is no new data in kafka...
 but your 
LatestSourcePosition
shows there is data, this indicates a Metadata Sync issue. The FE thinks the BE has already consumed everything. * Potential Cause: A duplicate 
group.id
. If another consumer (or another StarRocks job) is using the same Kafka 
group.id
, the offsets might be getting committed outside of StarRocks' control. * Fix: Add a unique 
group.id
to your job:
Copy code
sql
    ALTER ROUTINE LOAD FOR your_job_name
    PROPERTIES ("property.group.id" = "starrocks_unique_group_v2");
4. Check BE Logs for "librdkafka" Errors Since you increased the timeout and it didn't help, the BE might be hitting a protocol error. Log into the BE and run:
Copy code
bash
grep -E "librdkafka|RoutineLoad" <http://be.INFO|be.INFO> | tail -n 50
Look for: * `Receive failed: Disconnected`: Network issues. * `Local: Timed out`: The BE can't reach the brokers. * `SSL handshake failed`: If you are using SASL/SSL. Summary Advice 1. If 
Progress
< 
Earliest Available in Kafka
: You MUST jump forward. Use 
OFFSET_BEGINNING
in an 
ALTER
statement to grab the oldest available data. 2. If 
Progress
is valid but stuck: Change the 
group.id
to force a fresh connection. 3. If you truly don't care about the backlog: Only then use 
OFFSET_LATEST
. References • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md
c
@Rocky I get the error 
ERROR 1064 (HY000): Getting analyzing error. Detail message: property.group.id is invalid property.
r
The error 
property.group.id is invalid property
occurs because of the placement of that property in your SQL statement. In StarRocks, there is a strict distinction between Job Properties (like 
max_batch_size
) and Data Source Properties (Kafka-specific settings). 
property.group.id
belongs to the Data Source section. The Fix Move 
property.group.id
out of the 
PROPERTIES (...)
block and into the 
FROM KAFKA (...)
block. If you are using `ALTER ROUTINE LOAD`:
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
FROM KAFKA (
    "property.group.id" = "starrocks_new_unique_group_v2"
);
If you are using `CREATE ROUTINE LOAD`:
Copy code
sql
CREATE ROUTINE LOAD my_db.my_job ON my_table
PROPERTIES (
    "desired_concurrent_number" = "3"
)
FROM KAFKA (
    "kafka_broker_list" = "localhost:9092",
    "kafka_topic" = "my_topic",
    "property.group.id" = "starrocks_unique_group_v1" -- Correct location
);
Why this happens: 1. 
PROPERTIES (...)
: This block is for StarRocks internal engine settings (parallelism, error limits, timeouts, JSON paths). 2. 
FROM KAFKA (...)
: This block is passed to the Kafka consumer (
librdkafka
). Any property starting with 
property.
(like 
property.group.id
, 
property.security.protocol
, etc.) must go here. A Note on Resuming from "Latest" If you still intend to resume from the latest offsets because the old ones are causing issues, you can combine the 
group.id
change with the offset change in one command:
Copy code
sql
PAUSE ROUTINE LOAD FOR your_job_name;

ALTER ROUTINE LOAD FOR your_job_name
FROM KAFKA (
    "property.group.id" = "fresh_start_group",
    "kafka_offsets" = "OFFSET_LATEST"
);

RESUME ROUTINE LOAD FOR your_job_name;
References • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/RoutineLoad.md • faq/loading/Routine_load_faq.md References • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md
c
@Rocky would it help to stop the routine load, then create a new one resuming from the offsets?
r
While you can stop and recreate the job, using 
ALTER ROUTINE LOAD
is usually the preferred and cleaner way to fix offset issues. However, if the job feels "stuck" despite your best efforts, a fresh start can act as a hard reset. Here is the comparison and the steps for both: Option 1: The "Clean Reset" (Stop & Create New) This is helpful if you suspect there is something wrong with the job's internal metadata or if you want to ensure a brand-new Kafka 
group.id
is used. 1. Stop the old job:
Copy code
sql
    STOP ROUTINE LOAD FOR your_old_job_name;
2. Create a new job with a different name: (Using a new name guarantees a fresh 
group.id
and avoids any metadata overlap.)
Copy code
sql
    CREATE ROUTINE LOAD your_new_job_name ON your_table
    PROPERTIES (
        "format" = "json"
    )
    FROM KAFKA (
        "kafka_broker_list" = "your_broker:9092",
        "kafka_topic" = "your_topic",
        "property.group.id" = "fresh_start_v3", -- Explicitly set a new group
        "kafka_partitions" = "0,1,2",           -- List your partitions
        "kafka_offsets" = "12345, 67890, 11223" -- Specify the exact offsets you want
    );
Option 2: The "In-Place Fix" (Pause & Alter) This is faster and keeps your monitoring history for that job name. 1. Pause the job:
Copy code
sql
    PAUSE ROUTINE LOAD FOR your_job_name;
2. Update the offsets and group ID:
Copy code
sql
    ALTER ROUTINE LOAD FOR your_job_name
    FROM KAFKA (
        "kafka_partitions" = "0,1,2",
        "kafka_offsets" = "12345, 67890, 11223", -- Point to the valid offsets
        "property.group.id" = "a_new_unique_group_id"
    );
3. Resume:
Copy code
sql
    RESUME ROUTINE LOAD FOR your_job_name;
Which one should you choose? * Use 
ALTER
 if you have specific offsets you want to target and want to keep your existing job configuration. * Use 
STOP
+ 
CREATE NEW
 if the 
ALTER
command keeps failing or if 
SHOW ROUTINE LOAD TASK
shows that tasks are still trying to fetch old offsets even after you ran an 
ALTER
. Important Tips: * Offset Validation: Before running either command, verify the "Low Watermark" (the oldest available offset) in Kafka for each partition. If you request an offset lower than what Kafka has retained, the job will immediately pause again with 
Broker: Offset out of range
. * Partition Matching: If you specify 
kafka_partitions
, you must provide a matching number of 
kafka_offsets
. * The "Latest" shortcut: If you don't need the historical data and just want it to start working now, use 
"kafka_offsets" = "OFFSET_LATEST"
. Summary: If 
ALTER
hasn't worked for you yet, I recommend Option 1 (New Job Name). It is the most reliable way to clear the state and force StarRocks to start fresh. References • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/RoutineLoad.md • faq/loading/Routine_load_faq.md References • faq/loading/Routine_load_faq.md • quick_start/routine-load.md • loading/RoutineLoad.md
c
@Rocky I created a new routine load and I now see the proper offset lag and last source position. However I still get timeouts with no CPU activity. I am wondering if all the generated fields I added somehow preclude teh routine load from running as configured
r
"No CPU activity" combined with "timeouts" strongly suggests that the BE (Backend) nodes are failing to reach the Kafka brokers, or they are getting stuck in the network/socket layer while trying to pull data. If your "generated fields" (derived columns in the 
COLUMNS
clause) were the bottleneck, you would see high CPU usage on the BEs while they struggle to calculate those expressions. Since CPU is idle, the task is likely timing out while waiting for a response from Kafka that never comes. Here is how to troubleshoot this: 1. Verify BE-to-Kafka Connectivity It is a common pitfall where the FE can see Kafka (to get offsets and partition info), but the BEs cannot. * Log into one of the BE nodes. * Try to 
telnet
or 
nc
to the Kafka broker IP and port:
Copy code
bash
    telnet <kafka_broker_ip> 9092
* If this fails, your BEs are blocked by a firewall or are in a subnet that cannot reach Kafka. 2. The Timeout Parameter Mix-up You mentioned setting 
routine_load_kafka_timeout_second
on the FE. However: * 
routine_load_kafka_timeout_second
 is actually a BE-side configuration. It must be set in the 
be.conf
file and requires a BE restart to take effect. * 
routine_load_task_timeout_second
 is the FE-side configuration that controls the overall task heartbeat. If your network is slow or the Kafka brokers are under heavy load, the BE might be hitting its default 10-second internal timeout before it can return any data to the FE. 3. Check "SHOW ROUTINE LOAD TASK" Run this and look at the 
BeId
and 
Message
columns:
Copy code
sql
SHOW ROUTINE LOAD TASK WHERE JobName = "your_job_name" \G
* If 
BeId
is -1: The FE is failing to even schedule the task (could be due to lack of resources or too many running jobs). * If 
BeId
is a valid ID: Note the ID and log into that specific BE. Check 
be/log/be.INFO
for errors like 
Receive failed: Disconnected
or 
Failed to fetch metadata
. 4. Could the "Generated Fields" be the issue? Generated fields (transformations) only run after data is successfully pulled into the BE's memory. If 
SHOW ROUTINE LOAD
shows 
LoadedRows
is 0 and CPU is 0, the engine hasn't even reached the step where it processes your generated fields. One exception: If you have a massive number of columns or very complex JSON paths, the "Planning" phase (where the FE creates the task plan) might be timing out. You can check this by looking for "planner" errors in the 
fe.log
. Recommended Next Steps: 1. Simplify for testing: Temporarily create a dummy Routine Load job against the same topic without the generated fields. _ If it _still* times out: It is 100% a network or Kafka connectivity issue. _ If it _works*: There may be an issue with how the generated fields are mapped to the Kafka schema (though this usually results in "filtered rows" rather than a "timeout"). 2. Increase BE timeout: Add 
routine_load_kafka_timeout_second = 60
to your 
be.conf
and restart the BEs. 3. Check 
max_batch_size
: If you set this too high (e.g., several GBs), the task might time out before the BE can finish downloading the full batch. Try reducing it to 
102400
(100MB) for testing. References • faq/loading/Routine_load_faq.md • administration/management/BE_configuration.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • loading/RoutineLoad.md References • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md
c
@Rocky is it be.conf for compute nodes?
r
Yes, for Compute Nodes (CN), the configuration file is 
cn.conf
, not 
be.conf
. However, because StarRocks Compute Nodes are essentially stateless Backend (BE) services, they share the same directory structure. You will typically find the file at: 
path_to_your_cn_installation/be/conf/cn.conf
Key points for Compute Nodes (CN): 1. Inheritance: Most parameters (including 
routine_load_kafka_timeout_second
) are inherited from the BE configuration. You add them to 
cn.conf
exactly as you would in 
be.conf
. 2. Location: Even though it's a "Compute Node," the binary package is often the same as the BE, so the folder is still named 
be/
, but the config file is 
cn.conf
. 3. Routine Load on CNs: In shared-data clusters, Routine Load tasks are scheduled to run on CNs. If your CNs are idle (no CPU usage) while the load is timing out, it confirms that the CNs themselves are struggling to reach the Kafka brokers. How to update it: 1. Open 
be/conf/cn.conf
on each Compute Node. 2. Add the line:
Copy code
properties
    routine_load_kafka_timeout_second = 60
3. Restart the CN service:
Copy code
bash
    ./be/bin/stop_cn.sh
    ./be/bin/start_cn.sh --daemon
Why your "Generated Fields" probably aren't the cause: If your generated fields (transformations in the 
COLUMNS
clause) were causing a problem, you would typically see one of two things: * High CPU: The BE/CN is working hard to calculate the fields. * Filtered Rows: The data is being consumed, but the transformation results in 
NULL
or fails a 
WHERE
clause, leading to 
ErrorRows
or 
FilteredRows
in 
SHOW ROUTINE LOAD
. Since you see 0% CPU and timeouts, the CN is likely stuck at the very first step: calling the Kafka 
poll()
API and waiting for a response that never arrives because of a network block or a wrong broker address. Final Check: Run 
SHOW ROUTINE LOAD TASK WHERE JobName = 'your_job_name'
. * Look at the 
BeId
. * Find which node that ID belongs to using 
SHOW COMPUTE NODES
(or 
SHOW BACKENDS
). * Go to that specific node and check 
be/log/cn.INFO
for any 
librdkafka
errors. References • deployment/deploy_manually.md • administration/management/BE_configuration.md • loading/loading_introduction/loading_concepts.md • faq/loading/Routine_load_faq.md References • loading/loading_introduction/loading_concepts.md • deployment/deploy_manually.md • quick_start/routine-load.md • faq/loading/Routine_load_faq.md • loading/RoutineLoad.md
c
@Rocky using the operator I added confogMapInfo with a resolveKey of cn.info, but the data goes into be.info in both conf/be and conf/cn
r
This is expected behavior with the StarRocks Operator. Even though you are deploying a Compute Node (CN), the Operator's internal templates for mounting configurations are often shared with the Backend (BE) component, which frequently defaults the destination filename to 
be.conf
. Here is why this happens and why it is generally okay: 1. Why it goes into 
be.conf
 The StarRocks Operator uses a standardized pod template for both BE and CN. In many versions of the Operator, the 
mountPath
for the configuration volume is hardcoded to 
/opt/starrocks/be/conf/be.conf
. * The Key Mapping: When you set 
resolveKey: cn.conf
, the Operator looks for the data inside the key 
cn.conf
in your ConfigMap. * The Destination: It then writes that data into the container at the path 
/opt/starrocks/be/conf/be.conf
. 2. Why it appears in both 
conf/be
and 
conf/cn
 In the official StarRocks Docker images, the directories for 
be
and 
cn
are often symlinked or the entrypoint script mirrors the configuration to ensure compatibility. Since a CN is essentially a "stateless BE," the binary is often the same and is capable of reading from either file. 3. Will my changes work? Yes. Even if the file is named 
be.conf
inside your Compute Node pod, the process will still read it. The 
routine_load_kafka_timeout_second = 60
parameter you added will be picked up by the CN service upon startup. How to Verify the Config is Applied To be 100% sure the CN is actually using your new timeout value, you can check it via SQL: 1. Check the dynamic configuration:
Copy code
sql
    SELECT * FROM information_schema.be_configs WHERE NAME LIKE '%kafka_timeout%';
_Note: Despite the name 
be_configs
, this table includes results from Compute Nodes (CNs) as well._ 2. Check the CN logs: If you describe the pod or check the logs, you should see the service starting up. You can also exec into the pod and check the process command line to see which config file it was pointed to:
Copy code
bash
    kubectl exec -it <cn-pod-name> -- ps aux | grep starrocks
Recommendation If you find the mismatch between the name in the ConfigMap (
cn.conf
) and the file on disk (
be.conf
) confusing, you can simply rename the key in your ConfigMap to 
be.conf
and update the 
resolveKey
to match. This won't change the behavior, but it makes the manifest more intuitive. Summary: You don't need to fix the filename. As long as your 
routine_load_kafka_timeout_second
is present in that 
be.conf
file inside the CN pod, the timeout should be extended as requested. References • administration/management/BE_configuration.md • deployment/deploy_manually.md • loading/RoutineLoad.md • faq/loading/Routine_load_faq.md References • administration/management/BE_configuration.md • quick_start/shared-data.md • deployment/deploy_manually.md
c
@Rocky It looks like it is not being read
Copy code
root@admanager-data-starrocks-cn-11:/opt/starrocks/be/conf# cat be.conf
routine_load_kafka_timeout_second = 60
sys_log_level = INFO

be_port = 9060
be_http_port = 8040
heartbeat_service_port = 9050
brpc_port = 8060
starlet_port = 9070
root@admanager-data-starrocks-cn-11:/opt/starrocks/be/conf# cd ../../cn/
root@admanager-data-starrocks-cn-11:/opt/starrocks/cn# cat be.conf
cat: be.conf: No such file or directory
root@admanager-data-starrocks-cn-11:/opt/starrocks/cn# cd conf/
root@admanager-data-starrocks-cn-11:/opt/starrocks/cn/conf# cat be.conf
routine_load_kafka_timeout_second = 60
sys_log_level = INFO

be_port = 9060
be_http_port = 8040
heartbeat_service_port = 9050
brpc_port = 8060
starlet_port = 9070
and
Copy code
mysql> SELECT * FROM information_schema.be_configs WHERE NAME LIKE '%kafka_timeout%';
+---------+-----------------------------------+-------+-------+---------+---------+
| BE_ID   | NAME                              | VALUE | TYPE  | DEFAULT | MUTABLE |
+---------+-----------------------------------+-------+-------+---------+---------+
| 6466020 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6466019 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6466023 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6466022 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572205 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572214 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572228 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572210 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572203 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572227 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572226 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572225 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572204 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6466021 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
| 6572215 | routine_load_kafka_timeout_second | 10    | int32 | 10      |       0 |
+---------+-----------------------------------+-------+-------+---------+---------+
r
The reason your configuration is not being applied is that Compute Nodes (CN) specifically look for a file named 
cn.conf
, but the StarRocks Operator is currently mounting your configuration as 
be.conf
. Even though a CN is technically a "stateless Backend," the startup script (
start_cn.sh
) and the binary are hardcoded to prioritize 
cn.conf
. If that file is missing or doesn't contain the parameter, the process falls back to internal defaults (which is why you see 
10
in 
information_schema.be_configs
). Why this is happening In the StarRocks Operator, the 
starRocksCnSpec
often uses a template that defaults the mount destination to 
be.conf
because it shares logic with the BE component. When you provide a 
resolveKey
of 
cn.conf
, the Operator fetches that key but still writes it to the default path: 
/opt/starrocks/be/conf/be.conf
. How to fix it Option 1: Use the correct 
resolveKey
(Recommended) Ensure your ConfigMap has a key named 
cn.conf
 and your 
StarRocksCluster
YAML specifically maps it. Depending on your Operator version, you might need to ensure the Operator knows this is for a CN. If the Operator is stubbornly naming the file 
be.conf
, you can try to trick the search order or use the 
cnEnvVars
to set the variable (though 
routine_load_kafka_timeout_second
is a static config and usually requires the file). Option 2: Symbolic Link (Workaround) If you cannot change how the Operator mounts the file, you can add a post-start command or an init container to symlink 
be.conf
to 
cn.conf
. However, a cleaner way is to check your 
StarRocksCluster
CRD spec. In recent versions of the Operator, you should define it like this:
Copy code
yaml
starRocksCnSpec:
  configMapInfo:
    configMapName: your-cn-config
    resolveKey: cn.conf  # The key in your ConfigMap
If you've done this and it's still appearing as 
be.conf
, you can use 
cnEnvVars
 to inject the configuration directly. Many StarRocks configurations can be passed as environment variables with the prefix `CONFIG_`:
Copy code
yaml
starRocksCnSpec:
  cnEnvVars:
    - name: CONFIG_routine_load_kafka_timeout_second
      value: "60"
Note: Environment variables typically override file-based configurations in StarRocks containers. Verification Steps 1. Check the Process: Run 
kubectl exec -it <cn-pod> -- ps aux | grep starrocks
. Look for the 
--conf
flag or the working directory. It will show you which config file it's actually using. 2. Check for the file: Inside the pod, check if 
/opt/starrocks/be/conf/cn.conf
exists. If it doesn't, the settings in 
be.conf
are being ignored by the CN process. 3. Confirm via SQL: After restarting the pods with the new env var or file name, run your query again:
Copy code
sql
    SELECT * FROM information_schema.be_configs WHERE NAME = 'routine_load_kafka_timeout_second';
Summary on your "Generated Fields" concern Since the 
VALUE
is still 
10
, the "timeout" you are seeing is almost certainly the Kafka consumer timing out at the default 10-second mark. Once you successfully get the CN to recognize the 
60
second limit, the "No CPU activity" should resolve (as the node will have more time to establish the connection and pull data). The generated fields are calculated in-memory after data is fetched, so they are not the cause of an idle-CPU timeout. References • deployment/deploy_manually.md • administration/management/BE_configuration.md • faq/loading/Routine_load_faq.md References • quick_start/shared-data.md • deployment/deploy_manually.md • administration/management/logs.md
c
@Rocky I see after restart:
Copy code
root@admanager-data-starrocks-cn-13:/opt/starrocks# env | grep routine
CONFIG_routine_load_kafka_timeout_second=60
but it is still set to 10!
r
The behavior you're seeing—where the configuration is mounted as 
be.conf
despite you specifying a 
cn
related key—is a known quirk in certain versions of the StarRocks Operator. The Operator's internal logic often defaults to naming the configuration file 
be.conf
because Compute Nodes (CN) and Backends (BE) share much of the same codebase. However, the CN process is specifically looking for a file named 
cn.conf
 at startup. Since it only finds 
be.conf
, it ignores your custom settings and falls back to the hardcoded default of 
10
. The Solution: Explicitly mount 
cn.conf
 To force the Operator to name the file correctly, you should move away from 
configMapInfo
and use the 
configMaps
 (plural) field. This allows you to specify the exact 
mountPath
and 
subPath
. Update your 
StarRocksCluster
YAML for the 
starRocksCnSpec
as follows:
Copy code
yaml
starRocksCnSpec:
  # ... other settings ...
  configMaps:
    - name: your-configmap-name  # The name of your ConfigMap
      mountPath: /opt/starrocks/cn/conf/cn.conf
      subPath: cn.conf           # The key inside your ConfigMap
Why your environment variable didn't work Even though you see 
CONFIG_routine_load_kafka_timeout_second=60
in the environment, the StarRocks entrypoint script for CN nodes typically looks for this to write it into 
cn.conf
. If the script is misconfigured or if the file is being overwritten by the ConfigMap mount, the environment variable is ignored. Manual file mounting via 
subPath
is the most reliable way to ensure the setting is applied. How to verify it's working 1. Apply the change and wait for the CN pods to restart. 2. Check the file inside the pod:
Copy code
bash
   kubectl exec -it <cn-pod-name> -- cat /opt/starrocks/cn/conf/cn.conf
You should now see your timeout value in this specific file. 3. Verify via SQL:
Copy code
sql
   SELECT * FROM information_schema.be_configs WHERE NAME = 'routine_load_kafka_timeout_second';
The 
VALUE
column should now show 
60
. Summary Checklist * Filename: Must be 
cn.conf
. * Path: 
/opt/starrocks/cn/conf/cn.conf
. * Restart: Required (the Operator will handle this automatically when the CRD is updated). * Config Type: Static (this parameter is not dynamic, so the process must read it at boot time). Once this value is correctly showing as 
60
, your Routine Load should stop timing out during the Kafka connection phase. References • quick_start/shared-data.md • deployment/deploy_manually.md • deployment/sr_operator.md
c
@Rocky sadly, even at routine_load_kafka_timeout_second = 120 it still times out
r
The reason your configuration is still showing as 
10
(the default) is that Compute Nodes (CN) are ignoring 
be.conf
. Even if the Operator mounts it there, the CN process explicitly looks for a file named 
cn.conf
 in its configuration directory. Because the CN doesn't find its expected configuration file, it starts with hardcoded defaults. This is why your environment variables and 
be.conf
changes appear to have no effect. 1. Fix the Configuration Mount To force the StarRocks Operator to name the file correctly, you must use the 
configMaps
field with a specific 
mountPath
. The 
resolveKey
approach often defaults to 
be.conf
, which works for Backends but fails for Compute Nodes. Update your 
StarRocksCluster
YAML under `starRocksCnSpec`:
Copy code
yaml
starRocksCnSpec:
  # ... other settings ...
  configMaps:
    - name: your-configmap-name        # The name of your existing ConfigMap
      mountPath: /opt/starrocks/cn/conf/cn.conf
      subPath: cn.conf                 # The key in your ConfigMap containing the data
2. Verify the Change After the pods restart, run these two checks to confirm the CN is actually "listening": * Check the process argument:
Copy code
bash
    kubectl exec -it <cn-pod-name> -- ps aux | grep starrocks
Look for the 
-conf
flag. It should point to 
/opt/starrocks/cn/conf/cn.conf
. * Check the running config:
Copy code
sql
    SELECT * FROM information_schema.be_configs WHERE NAME = 'routine_load_kafka_timeout_second';
If this still says 
10
, the CN has not successfully read your file. 3. Why it might still "Time Out" at 120s If you successfully get the value to show as 
120
in SQL but the job still fails, the problem is likely Network/Broker reachability, not the timeout duration: * Kafka Advertised Listeners: If your Kafka brokers are returning hostnames that the CN pods cannot resolve (DNS issue), the connection will hang until it hit the limit. * Security Groups/Firewalls: Ensure the CN pods can reach the Kafka brokers on the advertised ports (usually 9092 or 9094). * FE vs BE/CN Timeout: There is also an FE configuration called 
routine_load_task_timeout_second
(default 60s). If your Kafka connection takes 120s but the FE task times out at 60s, the job will fail. You can increase this globally:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("routine_load_task_timeout_second" = "180");
Summary: The CN node is not seeing your config because it's named 
be.conf
. Once you rename/mount it as 
cn.conf
using the 
subPath
method above, the 
be_configs
table will reflect the change. If it still fails, investigate the DNS resolution and the FE-side task timeout. References • faq/loading/Routine_load_faq.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • loading/loading_introduction/troubleshooting_loading.md • faq/loading/Loading_faq.md
c
@Rocky I have been able to set the cn.conf and it is being recognized. Timeout is definitely 120.
r
That’s great progress—getting the CN to recognize the config is the biggest hurdle! If the job is still timing out even with 
routine_load_kafka_timeout_second = 120
, it usually means you are hitting a second layer of timeouts or a network resolution issue. Here is how to troubleshoot the next level: 1. Check the FE Task Timeout StarRocks has two different timeout settings for Routine Load that must be coordinated: * BE/CN Config (
routine_load_kafka_timeout_second
): How long the node waits for Kafka to respond to a poll. (You set this to 120s). * FE Config (
routine_load_task_timeout_second
): The maximum time the Frontend allows a single load task to run before killing it. The Problem: The default FE task timeout is 60 seconds. If your Kafka connection takes 70 seconds to respond, the FE will kill the task at 60 seconds, regardless of your 120s BE setting. The Fix: Run this on your FE to increase the global task timeout:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("routine_load_task_timeout_second" = "180");
_Note: You can also set 
task_timeout_second
specifically for one job inside the 
PROPERTIES
block of your 
CREATE ROUTINE LOAD
statement._ 2. Verify the "Reason of State Changed" Run the following command and look at the 
ReasonOfStateChanged
column:
Copy code
sql
SHOW ROUTINE LOAD FOR your_job_name\G
* If it says "Reached timeout": This confirms the FE/BE timeout mismatch mentioned above. * If it says "Broker: Leader not available" or "DNS resolution failed": This means the CN node can't "see" the Kafka brokers. 3. The "Hostname" Trap Kafka brokers often advertise their hostnames rather than IPs. 1. Inside your CN pod, try to ping the Kafka broker hostnames exactly as they appear in your 
kafka_broker_list
. 2. If the pod cannot resolve them, you must add the Kafka hostnames to your 
/etc/hosts
or update your K8s CoreDNS. 3. Even if the connection is "open," if the CN spends 120 seconds trying to resolve a hostname that doesn't exist, it will eventually throw a timeout error. 4. Check for Load Concurrency If your CN nodes are very busy, the task might be stuck in a queue. Check the status of your CN threads:
Copy code
sql
SHOW BACKENDS; -- Check if CNs are 'Alive' and 'OK'
If 
TOO MANY TASKS
appears in your logs, you may need to increase the thread pool: * BE/CN Config: 
routine_load_thread_pool_size
(Default is 10). Summary Recommendation: 1. Set the FE timeout: 
ADMIN SET FRONTEND CONFIG ("routine_load_task_timeout_second" = "180");
2. Check 
SHOW ROUTINE LOAD
for the specific error string. 3. Verify the CN pod can resolve and reach the Kafka broker hostnames via 
telnet
or 
nc
. References • administration/management/BE_configuration.md • faq/loading/Routine_load_faq.md • quick_start/routine-load.md
c
@Rocky the culprit was these generated fields
Copy code
/* `gen_event_minute` datetime NULL AS date_trunc('minute', `event_timestamp`) COMMENT "", */
      /* `gen_event_hour` datetime NULL AS date_trunc('hour', `event_timestamp`) COMMENT "" */
r
It makes complete sense that those generated columns were the root cause. While generated columns are great for query acceleration, they come with a "tax" at ingestion time that is often underestimated in high-throughput Routine Load scenarios. Why 
date_trunc
Generated Columns cause Timeouts In StarRocks, generated columns are not just metadata; they are materialized. This means for every single row arriving from Kafka: 1. Parsing: The BE/CN node parses the 
event_timestamp
. 2. Computation: The node executes 
date_trunc('minute', ...)
AND 
date_trunc('hour', ...)
for every record. 3. Storage: The result is written to disk alongside the original data. The Bottleneck: If your Kafka topic has high throughput (e.g., thousands of rows per second), the CPU overhead of running these functions twice per row can slow down the ingestion process. If the time it takes to (Consume + Parse + Compute + Write) exceeds the 
routine_load_task_timeout_second
, the task will fail with a timeout error, even if the network connection to Kafka is perfect. Recommendations 1. Use "Virtual" Truncation (Query Time) StarRocks is exceptionally fast at executing 
date_trunc
during queries. Unless you are querying billions of rows and absolutely need every millisecond of performance, it is usually better to calculate these at query time or via a View:
Copy code
sql
CREATE VIEW v_my_table AS 
SELECT *, 
       date_trunc('minute', event_timestamp) as gen_event_minute,
       date_trunc('hour', event_timestamp) as gen_event_hour
FROM my_table;
This removes all overhead from your Routine Load and keeps your ingestion pipeline lean. 2. Increase Task Timeout (If you must keep the columns) If you decide you must keep the materialized columns, you need to give the BE/CN more "room" to do the work. You can set the timeout specifically for that job:
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name 
PROPERTIES (
    "task_timeout_second" = "300"
);
_(Note: You may also need to increase the FE global 
routine_load_task_timeout_second
as mentioned previously.)_ 3. Check for Data Quality (The "Null" Trap) If your 
event_timestamp
contains invalid strings or 
NULL
values, the 
date_trunc
function might be throwing internal exceptions or returning nulls, which can sometimes slow down the vectorized execution engine. * Check if your Kafka data has empty strings in the timestamp field. * Check the 
ErrorLogUrls
from 
SHOW ROUTINE LOAD
to see if any rows were rejected during the computation phase. Summary By commenting out those fields, you removed the per-row CPU cost of the transformation. Since your Routine Load is now stable, it confirms that the "timeout" was actually the system being too busy computing those dates to finish the batch on time. References • faq/loading/Routine_load_faq.md • faq/Sql_faq.md • sql-reference/sql-statements/generated_columns.md • loading/loading_introduction/troubleshooting_loading.md • sql-reference/sql-functions/date-time-functions/date_trunc.md
c
@Rocky overhead should not be the problem. The load fails with just the date_truncate gen fields but works fine with 20 gen fields pulled from json
r
It is highly likely that the "timeout" isn't caused by the CPU cost of 
date_trunc
, but by blocking metadata operations or partition management triggered by those specific fields. While your 20 JSON-based fields are likely simple string/integer extractions, 
date_trunc
is almost always used for Partitioning or Sorting. Here are the three most probable reasons why these specific fields are causing your Routine Load to hang and timeout: 1. The "Partition Explosion" (Most Likely) If your table uses Expression Partitioning (e.g., 
PARTITION BY date_trunc(...)
), StarRocks must create new partitions on the fly as data arrives. * The Problem: If your Kafka data contains "dirty" timestamps (e.g., 
0001-01-01
, 
9999-12-31
, or even just a few dates from several years ago), StarRocks will attempt to create a partition for every unique hour/minute it encounters. * The Timeout: Creating partitions is a synchronous metadata operation on the Frontend (FE). If one batch of data triggers 50 new partitions, the BE/CN node will wait for the FE to finish. If the FE is under load or the lock takes too long, the Routine Load task will hit its 
task_timeout_second
and fail. * How to check: Run 
SHOW PARTITIONS FROM your_table_name;
. If you see thousands of partitions or dates that shouldn't be there, this is your culprit. 2. Timezone / Casting Lockup Unlike simple JSON string extraction, 
date_trunc
requires a valid 
DATETIME
object. * The Problem: If 
event_timestamp
is being parsed from a JSON string, StarRocks must apply timezone logic. If there is a mismatch between the Kafka data's format and the StarRocks session timezone, or if the string is in an unexpected format (e.g., ISO8601 with 'Z' vs. space-separated), the internal C++ parser might be struggling or failing silently for each row. * The Difference: Your 20 other fields are likely using 
get_json_string
which is extremely "loose" and just returns NULL on failure. 
date_trunc
on a generated column is stricter and happens at a different stage of the write pipeline. 3. Metadata Lock Contention Generated columns that are also used in the 
ORDER BY
(Sort Key) or 
PARTITION BY
clauses force the system to perform additional sorting and validation during the "Shuffle" phase of the load. * If your 20 JSON fields are just "along for the ride" (not part of the sort key), they have zero impact on the data distribution. * If 
gen_event_minute
is part of the Sort Key, every batch from Kafka must be fully sorted by that value before being written. If your Kafka data is not perfectly time-ordered, this sorting adds significant latency to the "commit" phase of the load, leading to a timeout. Recommended Troubleshooting Steps: 1. Check for "Bad" Data: Temporarily change the generated column to allow NULLs and use `try_cast`:
Copy code
sql
    -- If your version supports it, check if the source strings are valid
    AS date_trunc('minute', try_cast(event_timestamp as datetime))
2. Verify Partition Count: Ensure you aren't accidentally creating hundreds of partitions. You can limit this with the FE config:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("auto_partition_max_creation_number_per_load" = "20");
If the load then fails with "too many partitions," you know you have dirty data in Kafka. 3. Check the FE Log: Look at 
fe.log
for 
create partition timeout
or 
lock wait timeout
. If those exist, the issue is metadata management, not function overhead. Summary: The fact that 20 JSON fields work but 
date_trunc
doesn't strongly suggests that 
date_trunc
is triggering Auto-Partitioning or Sorting logic that is blocking the load. References • faq/Sql_faq.md • faq/loading/Routine_load_faq.md
c
@Rocky I fixed it! The problem was that event_timestamp was created from a double field as part of the projection and could not be used as a gen field. Now my question is, can I convert a generated field into a regular field?
r
Great job finding that! You've hit a specific limitation in StarRocks: Generated columns in a table schema can only reference physical columns stored in that table. They cannot "see" virtual columns or transformations created inside the 
COLUMNS
mapping of a Routine Load (projections). To your question: No, you cannot "convert" a generated column into a regular one with a single command (like dropping the 
AS
expression). You have to recreate the column as a regular field. Depending on whether you need to keep the data already in that column, here are the two ways to fix it: Option 1: The "Fresh Start" (Fastest if you don't mind losing current data) If you are still in the testing phase and don't care about the data currently in those generated columns, simply drop and re-add them: 1. Drop the generated column:
Copy code
sql
    ALTER TABLE your_table DROP COLUMN gen_event_minute;
2. Add it back as a regular column:
Copy code
sql
    -- Note: Regular columns must be added BEFORE generated columns.
    -- StarRocks usually handles the position automatically.
    ALTER TABLE your_table ADD COLUMN gen_event_minute DATETIME;
3. Update your Routine Load: Now that the column is "regular," you must tell the Routine Load how to fill it. Add the logic to your 
COLUMNS
clause:
Copy code
sql
    COLUMNS (
      raw_timestamp,
      event_timestamp = from_unixtime(raw_timestamp),
      gen_event_minute = date_trunc('minute', from_unixtime(raw_timestamp))
    )
────────── Option 2: The "Migration" (If you must keep existing data) If the table is already in production and you need to preserve the data, use the Rename-Add-Update pattern: 1. Rename the old column:
Copy code
sql
    ALTER TABLE your_table RENAME COLUMN gen_event_minute TO old_gen_minute;
2. Add the new regular column:
Copy code
sql
    ALTER TABLE your_table ADD COLUMN gen_event_minute DATETIME;
3. Sync the data (Only works on Primary Key tables):
Copy code
sql
    UPDATE your_table SET gen_event_minute = old_gen_minute;
*If you are using a Duplicate Key table, 
UPDATE
is not supported. You would need to use 
INSERT INTO ... SELECT
into a temporary table or simply re-ingest the data from Kafka.* 4. Drop the old column:
Copy code
sql
    ALTER TABLE your_table DROP COLUMN old_gen_minute;
Why this is actually better for you: By making these regular columns and handling the 
date_trunc
inside the Routine Load 
COLUMNS
mapping: * No Metadata Locks: You avoid the "Partition Explosion" or FE locking issues that generated columns can sometimes cause during high-concurrency loads. * Predictable Performance: The BE/CN node does the math once during the initial ingestion phase, and the result is written directly to disk. References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • loading/RoutineLoad.md • sql-reference/sql-statements/data-manipulation/UPDATE.md References • sql-reference/sql-statements/generated_columns.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
c
@Rocky If I create a sync MV on a table and rename the table does the SYNC MV follow?
r
Yes, if you rename a table, a Synchronous Materialized View (Sync MV) follows it automatically without any issues. Here is why: 1. Sync MVs are part of the table In StarRocks, a Synchronous MV (the kind used for single-table rollups) is not a separate standalone object; it is implemented as a Rollup Index inside the base table's metadata. * When you run 
ALTER TABLE old_name RENAME new_name;
, you are renaming the entire container. * Since the Sync MV is just a "special index" inside that container, it remains perfectly valid and attached to the new table name. 2. ID-based referencing StarRocks internal metadata tracks dependencies using unique IDs, not just name strings. Even for Asynchronous MVs (which are separate objects), the MV tracks the base table by its internal ID. Key differences to keep in mind: * Synchronous MVs: They "follow" the rename and remain active. You don't need to do anything. You can verify this by running 
SHOW CREATE TABLE new_table_name;
and you will still see the MV definition at the bottom. * Asynchronous MVs: While they also reference base tables by ID, a rename or a 
SWAP
operation will often set an Async MV to Inactive status. You would then need to run 
ALTER MATERIALIZED VIEW mv_name ACTIVE;
to re-enable it. Summary: For your Sync MV, the rename is a zero-effort operation. It will continue to work, refresh, and provide query rewrites under the new table name immediately. References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
c
@Rocky I will be doing a large backfill on the table. Is it best to defer creating the sync MV until after the backfill?
r
For a large backfill, it is generally best to defer creating the Sync MV until after the backfill is complete. Here is the breakdown of why this is the more efficient approach: 1. Faster Ingestion Throughput A Synchronous Materialized View (Sync MV) is updated in real-time during the data loading process. If you create it before the backfill: * Double Write/Compute: For every batch of data you load, StarRocks must compute the aggregations and write the data for the MV index alongside the base table data. * Increased Latency: This adds CPU and I/O overhead to every transaction, which can significantly slow down the total time it takes to finish a massive backfill. 2. Efficiency of "Build-After" If you load the data first and then run 
CREATE MATERIALIZED VIEW
, StarRocks treats this as a background Rollup Task: * Batch Processing: It will read the already-compacted base table data and build the index in a single, highly optimized batch process. * Less Contention: You avoid the overhead of updating the MV index for every individual segment or tablet during the ingestion phase. 3. Avoiding "Load Failure" Risks Since you previously had issues with timeouts and generated columns, adding a Sync MV during a heavy backfill adds another layer of complexity. If the FE (Frontend) or BE (Backend) nodes become overloaded by the backfill, the additional work of maintaining the Sync MV could trigger more timeouts or "version count" issues. The Trade-off * Create BEFORE: The data is ready for queries immediately after the backfill finishes, but the backfill itself takes much longer. * Create AFTER: The backfill finishes quickly. However, after you run the 
CREATE
command, there will be a period where the cluster uses background resources to build the MV. During this time, queries won't be accelerated by the MV until the state is 
FINISHED
. Recommended Strategy: 1. Backfill the data into your table with the regular columns you just fixed. 2. Optimize the table (Wait for compaction to settle or run a manual compaction if necessary). 3. Create the Sync MV:
Copy code
sql
    CREATE MATERIALIZED VIEW mv_name AS
    SELECT col1, col2, sum(col3)
    FROM your_table
    GROUP BY col1, col2;
4. Monitor the progress:
Copy code
sql
    SHOW ALTER TABLE ROLLUP; -- Sync MVs are tracked as Rollup jobs
Summary: Unless you have a strict requirement that queries must be accelerated the instant the backfill finishes, load the data first. It is safer and more performant for the cluster. References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/async_mv/Materialized_view.md • faq/loading/Routine_load_faq.md References • using_starrocks/async_mv/Materialized_view.md • knowledge/usage/load/shared_data_batch_ingestion.md • faq/shared_data_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
c
@Rocky For a 32 partition Kafka cluster I am running 2 routine loads of 15 task num each. There is about 1.7B records in the backlog and with a load rate of 1500 with generated fields I am afraid the backlog is insurmountable.
r
1.7B records is a significant backlog, but it is definitely surmountable in StarRocks. If your current load rate is 1,500 records/sec per task (across 30 tasks), you are processing ~45,000 rows/sec, which would clear the backlog in about 10.5 hours. However, if 1,500 rows/sec is your total aggregate rate, that would take ~13 days. To accelerate this, you need to move from "real-time" settings to "throughput" settings. 1. Tune for Throughput (The "Batching" Knobs) Routine Load performance is often limited by how frequently it "commits" (creating small versions). To clear a backlog, you want fewer, larger transactions. Adjust these FE Dynamic Parameters: * 
max_routine_load_batch_size
: Increase this (default is 4GB). If your messages are large, the default might be hit too early. * 
routine_load_task_consume_second
: Increase this to 20 or 30 seconds (default is 15). This forces the task to stay open longer and collect more rows before committing a version to the BE nodes. * 
max_batch_interval
: In your 
CREATE ROUTINE LOAD
job properties, ensure this is set to at least 10-20 seconds. Setting it lower (like 5s) during a backfill will cause "Version Too Many" errors and slow down the BEs due to constant compaction. 2. Max Out Parallelism You have 32 Kafka partitions. You should aim for 32 concurrent tasks (one per partition) to maximize Kafka's read throughput. * Check your FE config 
max_routine_load_task_concurrent_num
. It defaults to 5. If you haven't changed this, your 
desired_concurrent_number = 15
is being capped at 5. * Set 
ADMIN SET CONFIG ("max_routine_load_task_concurrent_num" = "32");
* Then 
ALTER
your two jobs to have 
desired_concurrent_number = 16
each (totaling 32). 3. Scale the BE Thread Pool If you increase concurrency to 32, ensure your Backend (BE) nodes can handle the task load. * Check the BE config 
routine_load_thread_pool_size
(default is often 10 or 20). * If you have a large cluster, increase this to allow more concurrent loading threads: 
routine_load_thread_pool_size = 64
(this requires a BE restart or using the 
curl
config API). 4. The "Backfill" Alternative: StarRocks Kafka Connector If Routine Load is still too slow, the StarRocks Kafka Connector (running on Kafka Connect) is generally more performant for high-throughput backfills. It uses the 
Stream Load
interface under the hood and can often reach much higher MB/s than Routine Load because it bypasses some of the internal Routine Load scheduling overhead. Summary Checklist for your Backfill: 1. Parallelism: Set 
max_routine_load_task_concurrent_num
to 32 in FE. 2. Batching: Increase 
routine_load_task_consume_second
to 30s. 3. Backpressure: Check 
SHOW ROUTINE LOAD
and look at 
Statistic
. If 
ErrMsgs
is high, the BEs are rejecting data. If 
CmtMsgs
is low, you aren't batching enough. 4. Schema: Since you moved your "generated fields" logic to regular columns in the 
COLUMNS
mapping, you've already removed a significant CPU bottleneck. References • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • administration/management/FE_configuration.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md References • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md
c
@Rocky I have "max_batch_rows" = "200000". what should it be?
r
The short answer is: You can leave it at 200,000. The common misconception is that 
max_batch_rows
controls how many rows are processed per transaction. In StarRocks, it does not affect performance or throughput. What 
max_batch_rows
actually does: This parameter is strictly used to define the Error Detection Window. _ StarRocks uses a rolling window to track data quality. The size of this window is calculated as 
10 _ max_batch_rows
. * With your setting of 
200,000
, your error window is 2,000,000 rows. * If the number of "bad" rows (rows that StarRocks cannot parse) exceeds your 
max_error_number
within any 2-million-row sequence, the job will pause. What you SHOULD tune for your 1.7B backlog: To clear a massive backlog, you care about Throughput (Bytes/sec) and Commit Efficiency, not the error window. You should focus on these FE dynamic parameters instead: 1. 
max_routine_load_batch_size
 (Default: 4GB): * This is the real throughput knob. It defines the maximum bytes a single task will consume before committing. * If your rows are small, you might never hit 4GB, and the task will time out instead. 2. 
routine_load_task_consume_second
 (Default: 15s): * Increase this to 30 or 60 for the backfill. * This forces each of your 30 tasks to stay open longer and accumulate more data into a single "version." This significantly reduces the overhead on the BE nodes and prevents "Too many versions" errors during heavy ingestion. 3. 
max_batch_interval
 (Default: 10s): * In your 
PROPERTIES
, ensure this is set to at least 
20
. This ensures you aren't committing small batches of data every few seconds. Summary recommendation for your backfill: * Leave 
max_batch_rows
at 200,000. * Increase 
routine_load_task_consume_second
to 30 or 60 via 
ADMIN SET CONFIG
. * Increase 
desired_concurrent_number
to 32 (to match your partitions) and ensure 
max_routine_load_task_concurrent_num
in FE config is also set to 32. If you do this, each task will pull more data per commit, and you will see your aggregate rows-per-second climb significantly. References • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • loading/RoutineLoad.md References • loading/loading_introduction/loading_considerations.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • knowledge/usage/load/shared_data_batch_ingestion.md
c
@Rocky 
max_routine_load_task_concurrent_num can go no higher than 15
r
There is no hard architectural limit of 15 for 
max_routine_load_task_concurrent_num
in StarRocks. If you are seeing it capped at 15, it is likely due to one of three things: your current FE configuration, the number of BE nodes, or the way partitions are distributed. Here is how to break through that 15-task limit to handle your 1.7B record backlog: 1. Increase the FE Global Limit The parameter 
max_routine_load_task_concurrent_num
is a global FE limit that defaults to 5. If you have already increased it to 15 but can't go higher, it might be because you are trying to set 
desired_concurrent_number
in your 
CREATE
statement without first raising the FE's global ceiling. Run this to allow jobs to scale up to 32 (matching your Kafka partitions):
Copy code
sql
ADMIN SET FRONTEND CONFIG ("max_routine_load_task_concurrent_num" = "32");
2. Check the "Min" Formula StarRocks calculates the actual task count using this formula: 
min(alive_be_number, partition_number, desired_concurrent_number, max_routine_load_task_concurrent_num)
 If any of these is 15, your parallelism will stay at 15: * 
alive_be_number
: If you only have 15 BE nodes, your parallelism for a single job will be capped at 15 (one task per BE). * 
partition_number
: Since you have 32 partitions, this isn't your bottleneck unless you specifically limited the job to a subset of partitions. * 
desired_concurrent_number
: Ensure you 
ALTER
your job to increase this:
Copy code
sql
    ALTER ROUTINE LOAD FOR job_name PROPERTIES ("desired_concurrent_number" = "16");
3. Check the "Tasks per BE" Limit There is another parameter that might be causing a "TOO MANY TASKS" error if you try to go higher: * 
max_routine_load_task_num_per_be
: Since v3.1, this defaults to 16. * If you have a very small cluster (e.g., only 1 or 2 BEs) and you are running multiple Routine Load jobs, you might be hitting the limit of how many total tasks a single BE can handle. * If you have 2 jobs and 1 BE, and both want 16 tasks, you need 32 total slots on that BE. You can increase this with:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("max_routine_load_task_num_per_be" = "64");
Summary for your Backfill: To get your 32 partitions working at full speed (16 tasks per job for 2 jobs): 1. FE Global: 
ADMIN SET FRONTEND CONFIG ("max_routine_load_task_concurrent_num" = "32");
2. Job Level: 
ALTER ROUTINE LOAD FOR job_name PROPERTIES ("desired_concurrent_number" = "16");
3. Batching (Crucial): Set 
ADMIN SET FRONTEND CONFIG ("routine_load_task_consume_second" = "60");
to ensure these 32 tasks aren't creating a storm of small files. References • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md References • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/RoutineLoad.md
c
@Rocky following all of the above gets me nowhere, in fact I am losing ground. Supposing that kafka is a bottelneck, perhaps I should load the data without generated fields into a staging table, then backfill from there once caught up?
r
Yes, your intuition is correct. If tuning Routine Load parameters hasn't stopped the backlog from growing, the "Staging Table" approach is the most reliable way to clear a massive 1.7B record backlog. Why the "Staging Table" approach works In StarRocks, Generated Columns (the table feature) and Transformations (logic in your 
COLUMNS
mapping) are computed at load time by the Backend (BE) nodes. For 1.7B records, this adds significant CPU overhead and increases the "holding time" of each load transaction. By moving to a staging table: 1. Raw Ingestion Speed: You strip the Routine Load job down to a pure "dump" of data. No functions, no regex, no generated column calculations. 2. Decoupling: You separate the IO-bound task (pulling from Kafka) from the CPU-bound task (calculating fields). 3. Batch Processing: Once the data is in StarRocks, you can use 
INSERT INTO ... SELECT
to move data from the staging table to the final table. This is significantly faster than Routine Load because StarRocks can use its full MPP (massively parallel processing) power to calculate those fields in bulk. Recommended Strategy 1. Create a Staging Table Create a table that matches your Kafka JSON/CSV schema exactly, using the 
Duplicate Key
model. Use only raw data types (VARCHAR, etc.) and no generated columns. 2. Run a "Lean" Routine Load Create a new Routine Load job for this staging table. * Do not use functions in the 
COLUMNS
clause. * Keep your concurrency high (32 tasks for 32 partitions). * Keep your batching settings: 
routine_load_task_consume_second = 60
. 3. Backfill via MPP Once the data is landing in the staging table, run the backfill in chunks (e.g., by hour or by ID range) into your final table:
Copy code
sql
INSERT INTO final_table (col1, col2, generated_col)
SELECT col1, col2, some_complex_function(col1)
FROM staging_table
WHERE ...;
This operation will be much faster than Routine Load because it happens entirely within the StarRocks cluster without the overhead of Kafka acknowledgments and small-batch transaction commits. One Alternative: The Kafka Connector If you don't want to manage a staging table, consider the StarRocks Kafka Connector (Sink Connector for Kafka Connect). * Why: It is architecturally more efficient than Routine Load for extreme throughput. It buffers data in Kafka Connect memory and uses the 
Stream Load
interface to send massive chunks. * When to use: Use this if you find that the bottleneck is the StarRocks Routine Load scheduler itself rather than the BE computation. Summary If 1.7B records are overwhelming your CPU during load, switch to a staging table today. Ingesting raw data is almost always the fastest path to "catching up" to the Kafka head. Kafka," and you can process the 1.7B records into their final form using high-speed internal SQL once they are safely on StarRocks-resident. References • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/alert.md • faq/fe_mem_faq.md
c
@Rocky Since I am currently loading without the minute rollup sync mv, I am beginning to think that I should abandon the idea of a sync mv at minute granularity. The overhead looks like it is just too high.
r
You are 100% correct to abandon the Sync MV (Rollup) for a 1.7B record backlog. In StarRocks, a Synchronous MV is updated atomically during every single load transaction. For a high-throughput Routine Load, this creates a massive bottleneck for two reasons: 1. CPU Write Amplification: Every row must be processed twice (once for the base table and once for the rollup) by the BE nodes before the transaction is committed. 2. Increased Latency: If your rollup involves complex expressions (like 
date_trunc('minute', ...)
), it significantly increases the "Holding Time" of each batch, which is likely why you are losing ground on your Kafka lag. The Better Path: Asynchronous Materialized View (Async MV) Instead of a Sync MV, use an Async MV. This will decouple your ingestion from your aggregation. * How it works: Your Routine Load writes raw data into the base table at maximum speed. The Async MV is a separate physical table that refreshes on a schedule (e.g., every 1 or 5 minutes). * Why it's faster for backfills: The 1.7B rows land in the base table with zero aggregation overhead. Once they are in StarRocks, the Async MV uses the full MPP power of the cluster to aggregate them in large, efficient blocks rather than row-by-row during ingestion. Recommended "Catch-up" Strategy: 1. Drop the Sync MV: This will immediately lower the CPU usage on your BE nodes and increase your Routine Load throughput. 2. Catch up to the Head: Let the Routine Load run "lean" until your Kafka lag is near zero. 3. Create an Async MV for the Rollup:
Copy code
sql
    CREATE MATERIALIZED VIEW minute_rollup_mv
    REFRESH ASYNC EVERY(INTERVAL 1 MINUTE)
    AS
    SELECT
        date_trunc('minute', event_time) as minute,
        count(*) as event_count,
        sum(price) as total_price
    FROM your_base_table
    GROUP BY 1;
4. Backfill the MV: Once created, StarRocks will automatically start the first refresh to populate the 1.7B records. Because this is a bulk internal operation, it will be orders of magnitude faster than doing it via Routine Load. Summary Sync MVs are for low-volume, high-freshness needs. For high-volume backfills, they are a performance trap. Abandoning the Sync MV is the right move to clear your 1.7B record backlog. References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/Materialized_view.md • faq/loading/Routine_load_faq.md • loading/RoutineLoad.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md • sql-reference/System_variable.md
c
@Rocky can I drop multiple columns in a single statement
r
Yes, you can drop multiple columns in a single 
ALTER TABLE
statement. In StarRocks, you should do this by separating each 
DROP COLUMN
clause with a comma. Syntax Example
Copy code
sql
ALTER TABLE your_table_name 
DROP COLUMN column_1, 
DROP COLUMN column_2, 
DROP COLUMN column_3;
Why you should use a single statement It is highly recommended to combine these operations into one statement for two reasons: 1. Concurrency Limit: StarRocks only allows one ongoing schema change operation per table at a time. If you run them as separate statements, the second one will fail with an error like 
Table 'xxx' is under schema change
until the first one completes. 2. Efficiency: Each 
ALTER TABLE
statement triggers a new asynchronous job. Combining them allows StarRocks to process the column removals in a single coordinated task. Important Restrictions * Partition Columns: You cannot drop a column that is used as a partition key. * Materialized Views: If the column you are dropping is used in a Synchronous Materialized View (Rollup), the operation will fail. You must drop the Materialized View first. * Primary Key Tables: You cannot drop columns that are part of the 
PRIMARY KEY
. * Asynchronous Nature: Column drops are asynchronous. After running the command, you can check the progress using:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Materialized_view-single_table.md • table_design/Schema_change.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
c
@Rocky I reactivated an async materialized view that had been inactive for few days. It has 
"partition_refresh_number" = "6"
but it seems like it has taken on more than that trying to catch up. Can you please interpret attached for me?
mv.txt
r
When you reactivate an asynchronous Materialized View (MV) after it has been inactive for several days, StarRocks must "catch up" by refreshing all partitions that became stale during the downtime. Based on your observation that it is processing more than 6 partitions, here is how to interpret the behavior and the properties you've set: 1. 
partition_refresh_number
vs. Total Backlog The property 
"partition_refresh_number" = "6"
does not limit the total number of partitions the MV will eventually refresh to catch up. Instead, it limits the batch size of a single task run. * How it works (Strict Mode): If you have 100 stale partitions and set this to 
6
, StarRocks will not refresh all 100 in one massive, memory-heavy transaction. It will trigger a task run for the first 6, then another for the next 6, and so on, until the backlog is cleared. * What you might see in the logs: If you are looking at the 
information_schema.task_runs
view or the 
EXTRA_MESSAGE
field: * 
mvPartitionsToRefresh
: This field should show only 6 partitions for the current task run. * 
PROGRESS
: If you see a progress like 
0/100
, the 
100
represents the total number of stale partitions identified for the entire catch-up process, while the specific task run only executes 6 at a time. 2. Check the 
partition_refresh_strategy
 If the task run is actually processing more than 6 partitions simultaneously (e.g., you see 20+ partitions in the 
mvPartitionsToRefresh
list for a single run), check this property: * 
"partition_refresh_strategy" = "adaptive"
: If this is set, StarRocks may ignore your 
6
and automatically increase the batch size based on system resources and data volume to clear the backlog faster. * 
"partition_refresh_strategy" = "strict"
: This is the default. It strictly enforces your limit of 
6
. 3. Interpreting the Task Run Details When you look at the refresh status (likely via 
SELECT * FROM information_schema.task_runs
), look for these fields in the 
EXTRA_MESSAGE
column: * 
mvPartitionsToRefresh
: These are the partitions currently being worked on in this specific batch. If there are 6, the system is respecting your setting. * 
nextPartitionStart
: If this field is present, it confirms that the refresh was split. It shows the starting point for the next batch of 6. * 
adaptivePartitionRefreshNumber
: If you see a number here other than 
-1
, the system has overridden your manual setting to optimize performance. Why is it "taking on more"? If you are seeing high resource usage despite the limit of 6, it is likely because even 6 partitions of a "minute rollup" can be heavy if those minutes contain a high volume of data from your 1.7B record backlog. Recommendation: If the system is struggling, you can temporarily set 
"partition_refresh_number" = "1"
to force the most granular, safest catch-up possible:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("partition_refresh_number" = "1");
References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
2 Views
Open in Slack
PreviousNext
Powered by