@Rocky what is the best perfromant way to insert data trought stream load, with transaction o without it?

In StarRocks, every Stream Load is transactional by default to ensure atomicity and consistency. You cannot technically run a Stream Load "without" a transaction, but you can choose how those transactions are managed to achieve the best performance. The most performant approach depends on your data volume and concurrency: 1. For Large Data Volumes (High Throughput) If you have large files (e.g., 100MB to 10GB), the Standard Stream Load is the most efficient. * How it works: Each

curl

or API call creates its own transaction, writes the data, and commits automatically. * Best Practice: Aim for batch sizes between 100MB and 1GB. Very small batches (KB or low MB) create too many "data versions," which leads to "Too many versions" errors and slows down the system due to background compaction pressure. 2. For High-Concurrency Small Batches (v3.4.0+) If your application sends many small requests (high concurrency, small data per request), use Merge Commit. * Why: It automatically merges multiple concurrent Stream Load requests into a single physical transaction on the server side. * Benefit: This significantly reduces the number of data versions and metadata overhead, preventing the "Too many versions" bottleneck. * How to enable: Use the HTTP header

"merge_commit: true"

. 3. For Exactly-Once & Multi-Step Loads (v2.4+) Use the Stream Load Transaction Interface (2PC) if you need to coordinate multiple data writes into one single commit (e.g., from Flink or a custom orchestrator). * Pros: You can call

/api/transaction/load

multiple times and then one

/api/transaction/commit

. This is excellent for merging mini-batches from a program to improve performance and ensure "exactly-once" semantics. * Cons: More complex to implement than a standard

curl

call. Summary: Which is "Best"? Scenario | Best Method | Performance Focus Large files / Batching | Standard Stream Load | High throughput, simple. High concurrency, small files | Merge Commit (

v3.4+

) | Reduces system overhead & "too many versions". Custom app / Multi-table | Transaction Interface (2PC) | Efficiently groups multiple API calls into one txn. Performance Tips: * Compression: Use

lz4

or

gzip

(headers like

"compress_type: LZ4"

) to reduce network transfer time. * Parallelism: Don't overload a single BE. StarRocks automatically distributes the load, but you should generally aim for a concurrency level that matches your cluster's CPU/IO capacity. * Format: CSV is generally faster to parse than JSON. If using JSON, ensure the structure is simple. References • knowledge/usage/load/shared_data_batch_ingestion.md • loading/StreamLoad.md • loading/Stream_Load_transaction_interface.md

@Rocky i am using starrocks 4.1.1

In StarRocks 4.1.1, all Stream Loads are transactional by default to ensure atomicity and consistency. You cannot technically run a Stream Load "without" a transaction, but you can choose how those transactions are managed. The most performant approach in your version depends on your workload: *1. High Concurrency, Small Batches: Use Merge Commit* If your application sends many small requests (e.g., many concurrent 1MB–10MB loads), Merge Commit is the most performant option. * How it works: It merges multiple concurrent Stream Load requests into a single physical transaction on the server side. * Why use it: It prevents the "Too many versions" error and significantly reduces background compaction pressure and metadata overhead. * Usage: Add the HTTP header

"merge_commit: true"

to your curl or API call. *2. Large Data Volumes: Standard Stream Load* If you are loading large files (e.g., 100MB to 10GB), use the standard synchronous Stream Load. * Best Practice: Aim for batch sizes between 100MB and 1GB. * Note: Avoid very large single files (over 10GB) if possible, as a failure would require a full retry of the massive file. *3. Multi-Table Atomicity: Stream Load Transaction Interface (v4.0+)* In StarRocks 4.1.1, the transaction interface supports Multi-table Transactions. * Scenario: If you need to load data into multiple tables (e.g.,

table_A

and

table_B

) and ensure they commit or fail together. * Performance: This is more efficient than running separate loads for each table if they are logically linked, as it reduces the number of commit operations on the FE (Frontend). * Workflow: 1. Begin transaction:

/api/transaction/begin

2. Load data:

/api/transaction/load

(repeated for different tables) 3. Commit:

/api/transaction/commit

Summary Recommendation for v4.1.1 Load Pattern | Recommended Method | Performance Key Many small concurrent requests | Merge Commit | Header:

merge_commit: true

Bulk/Large files | Standard Stream Load | Batching (100MB+ per call) Cross-table atomicity | Transaction Interface | Multi-table 2PC Pro Tip for v4.1.1: Ensure you are using LZ4 compression (header:

"compress_type: LZ4"

) and CSV format instead of JSON where possible, as CSV parsing is significantly faster in StarRocks' vectorized engine. References • loading/Stream_Load_transaction_interface.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Stream_Load_transaction_interface.md • loading/StreamLoad.md

i am upseting 4 tables every 30 seconds

@Rocky is there a config for max stream load size?

Yes, there are several configurations that control the maximum size of a Stream Load, depending on the file format and the system component. 1. General File Size Limit The primary setting for the maximum size of a data file is a BE (Backend) configuration: * Parameter:

streaming_load_max_mb

* Default: 102,400 MB (100 GB) in modern versions like 4.1.1. * How to change it: You can update it dynamically without a restart:

bash
    curl -XPOST http://<be_host>:<be_http_port>/api/update_config?streaming_load_max_mb=204800

Note: While you can increase this, it is generally recommended to keep individual loads under 10GB for better stability and easier retries. 2. JSON-Specific Limits If you are loading JSON data, there are additional constraints: *

streaming_load_max_batch_size_mb

: (BE config) Limits the JSON body size. Default is 100 MB. * How to bypass: Instead of changing the BE config, you can simply add the header

"ignore_json_size: true"

to your Stream Load request. * Hard Limit: A single JSON object within the file cannot exceed 4 GB. This is a hard limit of the parser and cannot be changed. 3. Timeout Considerations When increasing the load size, you must also ensure the job doesn't time out. If the load is very large, it may exceed the default 600 seconds timeout. * FE Configuration:

stream_load_default_timeout_second

(Default: 600). * Request Header: You can set a custom timeout per request using the

"timeout: <seconds>"

header (e.g.,

"timeout: 3600"

for one hour). * FE Max Limit: The

max_stream_load_timeout_second

(Default: 259,200) on the FE limits the maximum value you can pass in the header. Summary of Best Practices for 4.1.1: * Preferred Batch Size: 100 MB to 1 GB is the "sweet spot" for performance. * Avoid Huge Files: Loading 100 GB in one call is possible but risky; if it fails at 99%, you have to restart the entire 100 GB transfer. * Compression: Always use

"compress_type: LZ4"

for large loads to reduce network overhead. References • administration/management/BE_configuration.md • loading/StreamLoad.md • faq/loading/Stream_load_faq.md References • administration/management/BE_configuration.md • faq/loading/Stream_load_faq.md • loading/StreamLoad.md • administration/management/FE_configuration.md

@Rocky how do you stream load something that isn't json?

For StarRocks 4.1.1, the Stream Load HTTP API (

_stream_load

) natively supports two main formats: CSV and JSON. If you want to load something that isn't JSON, you are most likely looking for CSV (which includes TSV, pipe-separated, or any delimited text format). 1. Loading CSV (The most common non-JSON format) CSV is generally the most performant way to load data via Stream Load because it requires less CPU overhead to parse than JSON. Key Headers for CSV: * `column_separator`: The character between columns (default is

\t

). * `row_delimiter`: The character between rows (default is

\n

). * `skip_header`: Number of rows to skip at the top (e.g.,

1

if your file has column names). * `trim_space`: Set to

true

to remove leading/trailing whitespace from values. * `enclose`: If your values are wrapped in quotes (e.g.,

"value1"

), set this to

"

. Example command:

bash
curl --location-trusted -u <username>:<password> \
    -H "label:my_label_01" \
    -H "column_separator:," \
    -H "skip_header:1" \
    -H "columns: id, name, score" \
    -T data.csv -XPUT \
    http://<fe_host>:<fe_http_port>/api/mydatabase/table1/_stream_load

2. What about Parquet or ORC? In StarRocks 4.1.1, the Stream Load HTTP API does not natively support Parquet or ORC (these are currently "To be supported"). If your data is in Parquet or ORC format, you have two better options: * For Cloud/Remote Storage: Use

INSERT INTO ... SELECT * FROM FILES()

. This is the modern, recommended way to load Parquet/ORC from S3, HDFS, or GCS. * For Local Files: Use Broker Load with the

file:///

protocol. Although it's called "Broker Load," it can read local files directly from the BE's disk if the file is accessible to the backend process. 3. Performance Tip: Compression Regardless of the format, always compress your data for the best performance. StarRocks 4.1.1 handles decompression on the fly. * CSV + LZ4: Use the header

-H "compress_type: LZ4"

. LZ4 offers a great balance of high speed and good compression ratio, making it the most performant choice for high-throughput streaming. Summary of Format Support for Stream Load (v4.1.1) Format | Supported? | Best Use Case CSV | Yes | Maximum speed, simplest parsing. JSON | Yes | Semi-structured data, flexible schema. Parquet | No* | Use

INSERT INTO ... FILES()

instead. ORC | No* | Use

INSERT INTO ... FILES()

instead. _\_Note: While the

_stream_load

API doesn't support Parquet/ORC, StarRocks supports these formats via almost every other loading method (Broker Load, Pipe, Spark Load).* References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/loading_introduction/feature-support-loading-and-unloading.md References • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/hdfs_load.md • loading/StreamLoad.md • loading/Loading_intro.md • loading/s3.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md

@Rocky should I throttle stream loads to a table?

In StarRocks 4.1.1, you should generally throttle the frequency of stream loads rather than the absolute number of concurrent loads, though both are important for cluster stability. If you load too frequently (many small files), you will hit the "Too many tablet versions" error, as the system cannot compact the data as fast as you are ingesting it. 1. When to Throttle You should consider throttling if you see: * Error:

too many tablet versions

or

close index channel failed

. * Performance: High "Publish Version" latency in your load profiles. * Resource Pressure: High CPU usage from background compaction tasks. 2. Best Way to "Throttle" (Merge Commit) Since you are on v4.1.1, the best way to handle high-concurrency loads without manually building a complex throttling layer in your app is to use Merge Commit. * What it does: It allows StarRocks to automatically group many small, concurrent Stream Load requests into a single physical transaction. * How to use: Add the header

"merge_commit: true"

to your requests. * Benefit: This effectively "throttles" the number of versions created on disk while still allowing your application to send data as it arrives. 3. Built-in Configuration Limits StarRocks has built-in safety valves to prevent a single database or BE from being overwhelmed. You can adjust these if you need harder limits: *

max_running_txn_num_per_db

(FE Config): * Default: 1000. * Limits the total concurrent load transactions for a single database. If exceeded, new requests are rejected. *

stream_load_max_txn_num_per_be

(FE Config): * Default: -1 (Unlimited). * You can set this to a positive integer to limit how many concurrent Stream Loads a single Backend (BE) can handle. This prevents a single node from running out of memory during bursts. *

load_process_max_memory_limit_percent

(BE Config): * Default: 30% of BE memory. * This is the ultimate throttle. If the memory used by all active loads hits this limit, new loads will fail or wait to ensure the BE doesn't crash from OOM. Recommended Strategy for v4.1.1 1. Batching: Aim for at least 100MB per load or once every 5–10 seconds per table. 2. Use Merge Commit: If you cannot easily batch in your application, use the

merge_commit: true

header. 3. Monitor Versions: Run

SHOW TABLETS FROM <table_name>

and check the

VersionCount

column. If it consistently exceeds 500-700, you are loading too fast and should throttle the frequency. References • faq/loading/Loading_faq.md • administration/management/FE_configuration.md • loading/loading_introduction/loading_considerations.md • loading/StreamLoad.md References • administration/management/FE_configuration.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/loading_introduction/loading_considerations.md

@Rocky i'm getting this error a lot "Status": "Fail", "Message": "Load is aborted, reason: Load execution failed. errorCode=INTERNAL_ERROR, message=[E1008]Reached timeout=300000ms @

The error

[E1008]Reached timeout=300000ms

indicates that an internal RPC (Remote Procedure Call) between StarRocks nodes timed out after exactly 300 seconds (5 minutes). This usually happens during the Write or Commit phase of a Stream Load when the system is unable to process the data fast enough, often due to high disk I/O, CPU pressure, or heavy background compaction. 1. Increase the Load Timeout Even if your total data size is small, individual internal tasks can hit this 300s limit if the cluster is busy. * Per Request: Add a

timeout

header to your

curl

command. Set it significantly higher (e.g., 1200 seconds).

bash
    -H "timeout: 1200"

* Global Default: If you don't want to specify it every time, increase the default on the FE:

sql
    ADMIN SET FRONTEND CONFIG ("stream_load_default_timeout_second" = "1200");

2. Check for "Too Many Tablet Versions" This is the most common "hidden" cause. If you are loading data too frequently (e.g., every 1 second), StarRocks creates too many small files (versions). The system then struggles to merge them, causing the write RPCs to hang and eventually time out at the 300s mark. * Check version count:

sql
    SHOW TABLETS FROM <table_name>;

Look at the

VersionCount

column. If it is over 500-700, you are loading too fast. * Solution: Use Merge Commit to group small loads:

bash
    -H "merge_commit: true"

3. Check for Flink/Connector Defaults If you are using the StarRocks Flink Connector, it has its own default timeout of 300000ms (300s). You must explicitly increase it in your Flink DDL/Configuration: * Option:

sink.properties.timeout

=

600s

* Option:

sink.properties.prepared_timeout

=

600s

(if using Exactly-once/v2) 4. Hardware/Load Bottlenecks If the error persists after increasing timeouts, check the health of the BE node mentioned in the error (e.g.,

@10.15.15.25:8060

): * Disk I/O: Is the disk utilization (

util%

) at 100%? (Use

iostat -x 1

). * CPU: Is the BE process pinned at 100% CPU? * Compaction: Check

show proc "/compactions"

to see if there is a massive backlog of background merges. Summary Checklist 1. Add

-H "timeout: 1200"

to your load command. 2. Add

-H "merge_commit: true"

to reduce version pressure. 3. If using Flink, set

sink.properties.timeout

to a higher value. 4. Verify that the BE node has sufficient disk performance (SSD is highly recommended). References • faq/loading/Loading_faq.md • loading/StreamLoad.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md References • loading/StreamLoad.md • administration/management/FE_configuration.md • faq/loading/Loading_faq.md • administration/management/BE_configuration.md • loading/loading_introduction/loading_considerations.md • faq/loading/Stream_load_faq.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/RoutineLoad.md • loading/Flink_connector_starrocks.md

@Rocky I have one super noisy JDBC insert running still, could that cause instability that leads to this?

Yes, a "noisy" JDBC insert (meaning many frequent, small

INSERT INTO ... VALUES

statements) is a very likely culprit for cluster instability and the

[E1008]

timeouts you are seeing. In StarRocks, every single

INSERT

statement—no matter how small—is treated as a full ACID transaction. Here is how that noisy process is likely breaking your Stream Loads: 1. Tablet Version Exhaustion StarRocks creates a new "version" of data for every transaction. If your JDBC process is inserting rows one by one or in tiny batches multiple times per second: * It creates a massive backlog of small files on the Backends (BEs). * The background Compaction process tries to merge these files but can't keep up. * When your Stream Load tries to write, it gets stuck waiting for internal resources or coordination, eventually hitting that 300s RPC timeout. 2. Transaction Lock Contention There is a limit to how many concurrent transactions StarRocks can handle per database (default

max_running_txn_num_per_db = 1000

). * If your JDBC process is "noisy" and holding many short-lived connections, it can exhaust the transaction slots. * New Stream Load requests might be forced to wait for a slot, or the Frontend (FE) might become sluggish in coordinating the "Commit" phase, leading to the timeout. 3. CPU & Memory Pressure JDBC inserts are generally less efficient than Stream Load. * The FE has to parse every SQL statement, plan it, and then coordinate the write. * A high volume of small SQL inserts causes the FE CPU to spike and the BEs to spend more time on metadata overhead than actual data processing. ────────── How to Fix It 1. Batch your JDBC inserts (Immediate fix) If you are doing this:

sql
INSERT INTO table VALUES (1, 'a'); -- Statement 1
INSERT INTO table VALUES (2, 'b'); -- Statement 2

Change it to a single batched statement (at least 1,000+ rows at a time):

sql
INSERT INTO table VALUES (1, 'a'), (2, 'b'), ... (1000, 'z');

2. Check the "Version Count" Run this query while the noisy insert is running:

sql
SHOW TABLETS FROM <your_table_name>;

Check the

VersionCount

column. If it is over 500–1,000, your noisy JDBC insert is definitely the cause. The system is struggling to compact data, which is slowing down all other operations. 3. Move from JDBC to Stream Load If the data volume is high, JDBC

INSERT

is the wrong tool. StarRocks is designed for high-throughput stream loading. You should migrate that "noisy" process to use the Stream Load API or a StarRocks Connector (like the Flink or Java connector), which handle batching and transaction management much more efficiently. Summary The

[E1008]

error is an internal timeout. The "noise" from your JDBC inserts is likely creating so much background work (compaction and metadata management) that the nodes cannot respond to each other within the default 300-second window. Batching the JDBC inserts or stopping them temporarily will likely resolve the Stream Load timeouts immediately. References • loading/InsertInto.md • faq/loading/Insert_into_faq.md • loading/loading_introduction/loading_considerations.md • administration/management/FE_configuration.md

@Rocky i don't see

VersionCount

with show tablets

If you don't see the

VersionCount

column when running

SHOW TABLETS FROM table_name

, it is likely because you are running a Shared-data (Cloud-native) cluster. In this architecture, the output of

SHOW TABLETS

is simplified and does not include the specific version count field. However, you still need to monitor the "Compaction Score" to determine if your noisy JDBC process is overwhelming the table. 1. The Best Way to Check (v4.1.1) Since you are on a modern version, use the

information_schema

to check the Max Compaction Score (MAX_CS). This score effectively represents how many un-compacted versions (rowsets) you have. Run this query:

sql
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    MAX_CS,      -- High score here means too many versions
    VISIBLE_VERSION 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_table_name' 
ORDER BY MAX_CS DESC;

* MAX_CS < 20: Healthy. * MAX_CS > 50: Backlog is building up. * MAX_CS > 100: Critical. This is almost certainly why your Stream Loads are timing out with the

[E1008]

error. The system is struggling to merge the "noise" from your JDBC inserts. 2. Alternative: Using

SHOW PROC

If you prefer the administrative view, you can drill down into the partition metadata: 1. Find your database and table IDs (or just use the names in the path). 2. Run:

sql
   SHOW PROC '/DBS/your_db_name/your_table_name/partitions';

3. Look for the

MaxCS

column at the far right. Why this confirms your "Noisy JDBC" theory In StarRocks, the Compaction Score increases with every small insert. If your JDBC process is hitting the table with many small commits, the

MAX_CS

will skyrocket. When a Stream Load tries to commit, it sees this high pressure and can get "stuck" behind internal compaction locks or metadata updates, leading to that 300,000ms (300s) timeout. Recommendation: Stop the noisy JDBC process or batch its inserts into groups of 1,000+ rows, then watch the

MAX_CS

start to drop as background compaction finally catches up. References • knowledge/admin_and_operation/shared_data_compaction.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • administration/management/Manage_tablet.md References • knowledge/admin_and_operation/shared_data_compaction.md