@Rocky Hi, I’m testing a large StarRocks Broker Load from Parquet files in S3. The load starts fast but progressively slows down. CPU remains underutilized, disk I/O is relatively low during most of the load, and then spikes near the end. The profile shows significant writer backpressure (

OutputFullTime

/

WaitWriteTime

). Increasing

pipeline_dop

improved initial throughput, but increasing

flush_thread_num_per_store

did not help and may have made it slightly worse. Could this be caused by MemTable/segment-writer backpressure, page-cache writeback, or storage sync latency? What metrics or configuration would you recommend checking to improve sustained load throughput and keep disk utilization more consistent?

The behavior you're describing—initial high speed followed by a progressive slowdown and a disk I/O spike at the end—is a classic symptom of buffer-driven backpressure. Data is being ingested into memory (MemTables) faster than the storage engine can persist it to disk, eventually hitting memory limits or triggering a massive end-of-load "commit" phase. Based on the

OutputFullTime

and

WaitWriterTime

metrics in your profile, here is an analysis and recommended tuning: 1. Identify the Bottleneck Source Check the Load Profile (via

ANALYZE PROFILE

) and look specifically at the

LoadChannel

operator: *

WaitFlushTime

>

WaitWriterTime

: The bottleneck is the physical flushing of MemTables to disk. Since you already increased

flush_thread_num_per_store

without success, the bottleneck is likely the disk I/O bandwidth/latency itself or too many small files being created. *

WaitWriterTime

is high: The storage engine's "Writer" is saturated. This often happens in Primary Key tables (due to metadata management/locking) or when there is significant data skew. * High

CloseWaitTime

: This explains the "spike at the end." StarRocks buffers data until the end of the load transaction; if the ingestion rate exceeded the flush rate throughout the load, the system must finish all pending I/O before the transaction can commit. 2. Recommended Configuration Adjustments Memory & Buffering (BE Config) If you have sufficient RAM, allow StarRocks to buffer more data to prevent early backpressure: *

load_process_max_memory_limit_percent

: (Default 30%). Increase this (e.g., to 50%) to give the load more headroom before it's forced to throttle. *

write_buffer_size

: (Default 100MB). For large Parquet loads, increasing this to 200MB or 512MB can help create larger, more efficient segments and reduce the number of I/O operations, which often mitigates the end-of-load spike. Concurrent Writing (BE Config) *

number_tablet_writer_threads

: If

WaitWriterTime

is high, increase this to allow more concurrent tablet write operations. * Check for Data Skew: If your data is skewed toward specific partition keys or bucket keys, a single BE or disk might be overwhelmed. In the profile, compare

AddChunkCount

across different BE addresses. If one BE has significantly higher counts, consider adjusting your

DISTRIBUTED BY

key. OS-Level Tuning The disk I/O spike can indeed be caused by the OS Page Cache reaching its dirty ratio limit. *

vm.dirty_ratio

/

vm.dirty_background_ratio

: If these are high, the OS will buffer writes in RAM until a threshold is hit, then freeze processes to flush everything to disk. Try lowering

vm.dirty_background_ratio

(e.g., to 5) to encourage the OS to start background flushing earlier and more consistently. 3. Key Metrics to Monitor To confirm these theories, monitor the following during the load: *

be_mem_usage_load

: Check if it hits the limit defined by

load_process_max_memory_limit_percent

. *

be_active_memtable_count

: A rising count indicates flushes are not keeping up with ingestion. *

iostat -x 1

: Watch the

%util

and

avgqu-sz

. If

%util

stays low then jumps to 100%, it points to the OS page cache writeback or the final segment sync. Summary of Recommendations 1. Increase

write_buffer_size

to 512MB to reduce file count and improve I/O efficiency. 2. Increase

load_process_max_memory_limit_percent

to 50% if BE memory allows. 3. Lower

vm.dirty_background_ratio

at the OS level to smooth out disk writes. 4. Verify Data Skew in the profile to ensure load is balanced across all disks/distributed. References • loading/loading_introduction/troubleshooting_loading.md

@Rocky In my case,

WaitFlushTime

is close to zero and the MemTable flush queue is empty, while async delta writer pending time and

WaitWriteTime

are high. The table is a Duplicate Key table, load memory is well below its limit, and data distribution is balanced across BEs. Would increasing

number_tablet_writer_threads

from its effective default of 16 be the most appropriate next experiment?

Yes, increasing

number_tablet_writer_threads

is the most appropriate next step given your specific profile metrics. In StarRocks, the

WaitWriteTime

(seen in the

OlapTableSink

) and the Async Delta Writer

pending

time represent the time a write request spends waiting in a thread pool queue before it can even begin processing. Why this is the correct lever: * High

pending

+ Low

WaitFlushTime

: This combination confirms that the bottleneck is not the physical disk I/O or the flushing process itself, but rather the concurrency of the ingestion entry point. The "Delta Writer" thread pool is saturated, so new chunks of data from your Broker Load are sitting in a queue. * Duplicate Key Table: Since you aren't using a Primary Key table, you don't have the overhead of primary key index lookups or "delete-and-insert" logic, which typically happens inside the

execute

phase. Your bottleneck is purely the task-scheduling capacity. * Resource Headroom: Since your CPU and Memory are underutilized, you have the necessary "budget" to increase worker concurrency without risking system stability. Recommended Action Increase the thread pool size in your

be.conf

. Since your current effective default is 16, try doubling it as a first step:

properties
# be.conf
number_tablet_writer_threads = 32

Note: This parameter is dynamic in v3.1.7+, so you can also set it at runtime via:

curl -XPOST http://<be_ip>:<be_http_port>/api/update_config?number_tablet_writer_threads=32

Related Metrics to Monitor To verify the fix, monitor these specific BE metrics: 1.

async_delta_writer_queue_count

: This should decrease. If it stays high despite increasing the threads, it means each individual write task is still too slow (check

execute

time in the profile). 2.

be_active_memtable_count

: As you increase writer threads, more MemTables will be created simultaneously. Ensure this doesn't lead to memory exhaustion. 3.

tablet_writer_concurrency

: High values here will confirm the BE is now processing more write tasks in parallel. A Note on the "End-of-Load" Spike Even with more threads, the disk I/O spike at the end of the load is expected for large Broker Loads. This is because StarRocks performs a final

close()

and

fsync()

on all open file segments before the transaction commits. However, by increasing

number_tablet_writer_threads

, you should see a more consistent "execute" rate throughout the load, which may help distribute the I/O more evenly over time. References • Troubleshooting Data Loading • BE Configuration - number_tablet_writer_threads References • administration/management/monitoring/metrics.md • loading/loading_introduction/troubleshooting_loading.md • best_practices/query_tuning/query_profile_operator_metrics.md • knowledge/trouble_shooting/fe_deadlock_problems.md