http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Does primary key table support hype...
# questions-and-troubleshooting
m
@Rocky Does primary key table support hyper log log? What would be the most efficient way to count distinct values in a column?
r
Yes, Primary Key tables support the HLL (HyperLogLog) data type as a value column. The most efficient way to count distinct values in a column depends on whether you require an exact count or an approximate one. 1. Approximate Count (Most Efficient) If you can tolerate an error rate of about 1%, HLL is the most efficient method because it uses significantly less memory and CPU than an exact count. * Using `APPROX_COUNT_DISTINCT`: You can use this function on any column. It uses the HLL algorithm internally without requiring you to change your table schema.
Copy code
sql
    SELECT APPROX_COUNT_DISTINCT(user_id) FROM your_pk_table;
* Storing HLL objects: If you are performing extremely high-frequency queries, you can store pre-calculated HLL objects in a column. Since Primary Key tables use a "replace" semantic for updates, you would typically calculate the HLL object upstream (e.g., in Spark or Flink) and load it into StarRocks. 2. Exact Count If you need a 100% accurate count, your options are: * `COUNT(DISTINCT col)`: This is the standard approach. It works well for millions of rows but can become resource-intensive (shuffling data across nodes) as cardinality reaches hundreds of millions or billions. * Bitmap (Integer only): For exact counts on very high-cardinality columns, Bitmaps are the most efficient. * Primary Key tables support the 
BITMAP
type. * Limitation: Bitmaps only work directly with integer types (
TINYINT
, 
SMALLINT
, 
INT
, 
BIGINT
). For strings, you would need to implement a global dictionary to map strings to integers first.
Copy code
sql
    -- Example: count distinct using a bitmap column
    SELECT BITMAP_UNION_COUNT(user_bitmap_col) FROM your_pk_table;
Summary Recommendation Requirement | Method | Efficiency Approximate | 
APPROX_COUNT_DISTINCT(col)
| Highest Exact (Integers) | 
BITMAP
type + 
BITMAP_UNION_COUNT
| High Exact (Any type) | 
COUNT(DISTINCT col)
| Standard Note on PK Tables: In a Primary Key table, if you use 
HLL
or 
BITMAP
types as value columns, remember that an update to a row with the same primary key will replace the existing HLL/Bitmap object rather than merging it. To merge/aggregate these values across different primary keys, you use functions like 
HLL_UNION_AGG
or 
BITMAP_UNION_COUNT
in your SELECT`. References • using_starrocks/distinct_values/Using_HLL.md • using_starrocks/distinct_values/Using_bitmap.md • table_design/indexes/Bitmap_index.md
m
@Rocky What can I check for why some updates could be silently failing in my primary key table? I am comparing impressions updated to existing records in the table (setting has_won = 1 column) and my StarRocks table is consistently undercounting elastic search by 20%. I do not see anything wrong with my existing metrics. My update apply queue count is spiky but doesnt grow beyond 200, and it always go back down very quickly). WHere can I diagnose dropped updates
r
In a StarRocks Primary Key (PK) table, "missing" updates are rarely silent failures in the engine itself; they are typically the result of specific table configurations or data ingestion logic that filters or deduplicates records by design. To diagnose why your 
has_won = 1
updates aren't appearing as expected, check these four areas: 1. Conditional Updates (
merge_condition
) If your table was created with a version column and uses 
merge_condition
, StarRocks will silently ignore any update where the incoming version is less than or equal to the version already in the table. * The Scenario: If an "impression" arrives with 
timestamp: 100
and then a "won" update arrives with 
timestamp: 90
(due to out-of-order delivery), the update will be dropped to prevent old data from overwriting new data. * How to Check: Verify if your table schema includes a property like 
"merge_condition" = "version_col"
. * Diagnosis: Compare the timestamps/version numbers of the records in Elasticsearch vs. StarRocks. 2. Partial Update "Invisibility" If you are using 
partial_update: true
, and an update for 
has_won = 1
arrives for a Primary Key that does not yet exist in the table: * The Behavior: StarRocks will create a new row, set 
has_won = 1
, and fill all other columns with default values (usually 
NULL
or 
0
). * The Pitfall: If your "impression count" query filters for 
impression_id IS NOT NULL
or some other field that isn't present in the "won" update, these rows will exist in the table but won't show up in your metrics. * How to Check: Query the table for rows where your metrics columns are NULL but 
has_won = 1
. 3. Duplicate Keys in a Single Batch StarRocks deduplicates data at the batch level. * The Scenario: If you send two updates for the same Primary Key in a single Stream Load or Flink batch, only the last one in the file is applied. * Diagnosis: Check your source data for duplicate IDs within the same ingestion window. If Elasticsearch is counting every "hit" but StarRocks is deduplicating by ID, you will see a discrepancy. 4. Actual Dropped/Filtered Records If records are failing due to data type mismatches or "quality" issues, they are tracked as "Filtered Rows." * Check Load Stats: Run 
SHOW LOAD
and look at the 
RowsFiltered
column for your recent jobs. * Enable Load Profile: To see exactly what happened during a load, enable the profile for your table:
Copy code
sql
    ALTER TABLE your_table SET ('enable_load_profile' = 'true');
    -- After a load, find the QueryID
    SHOW PROFILELIST;
    -- Analyze the specific load
    ANALYZE PROFILE FROM 'query_id';
Look for 
RowsFiltered
or 
NumRowsAbnormal
in the output. Why your Apply Queue looks fine The fact that your 
update_apply_queue_count
is spiky but returns to 0 quickly (and stays under 200) indicates that the BE (Backend) is processing the updates successfully. It is not a bottleneck or a crash; the data is being written, but it is likely being overwritten or ignored based on the Primary Key logic described above. Recommendation: Query a few specific IDs that you know are in Elasticsearch but "missing" in StarRocks. Check if the row exists with 
NULL
values for other columns, or if it has a 
version
higher than the update you tried to send. References • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md • loading/loading_introduction/troubleshooting_loading.md • administration/management/monitoring/metrics.md References
m
@Rocky I thought for partial updates, I'
r
You likely intended to say: "I thought for partial updates, the record had to already exist." In StarRocks Primary Key tables, this is a common point of confusion. Here is the behavior you need to know to find those "missing" 20%: 1. Partial Updates are actually "UPSERTS" By default, if you send a partial update (e.g., just 
id
and 
has_won
) for a Primary Key that does not yet exist in the table, StarRocks will not ignore it. Instead, it will: * Create a new row. * Set 
has_won = 1
. * Fill all other columns with default values (usually 
NULL
or 
0
). The Problem: If your logic for counting impressions looks like 
SELECT count(impression_id) FROM table
, and the "won" event arrived before the "impression" event, the 
impression_id
(or other identifying columns) will be NULL. This row exists in the table, but it won't be counted by your metric query because of the NULL values. 2. The 
merge_condition
(Version) Trap If you are using a version column (like a timestamp) to handle out-of-order data: * If a "won" event arrives with a timestamp of 
10:00:00
, but an "impression" event for that same ID already exists with a timestamp of 
10:00:01
, the update will be silently dropped. * StarRocks logic: 
New Value >= Old Value
. If the incoming update has a lower version than what is already stored, the engine assumes it's an old update and ignores it to prevent data regression. 3. "Column Mode" vs. "Row Mode" * Row Mode (Default): Best for frequent, small updates. It performs better when you update many columns. * Column Mode: Much more efficient for updating a single column (like 
has_won
) across a huge number of rows. _ _Note:* If you are on a version older than v3.1.3, you cannot use 
partial_update
and 
merge_condition
(the version column) at the same time. If you try, the version check might not behave as you expect, leading to dropped or overwritten data. Troubleshooting Steps: 1. Check for "Ghost" Rows: Run a query to see if there are rows where your key metrics are NULL but your update flag is set:
Copy code
sql
    SELECT count(*) FROM your_table
    WHERE has_won = 1 AND some_impression_col IS NULL;
If this count is high, your "missing" 20% are actually these "incomplete" rows created by "won" events that arrived before the "impression" data. 2. Verify Versioning: Check if your source "won" events in Elasticsearch have smaller or equal timestamps compared to the "impression" events. If they do, they are being filtered by the 
merge_condition
. 3. *Check the 
*op
field:* If you are using a connector (like Flink or Spark), ensure you aren't accidentally sending a 
DELETE
operation (
*op = 1
) for some records. References • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md • faq/loading/Loading_faq.md References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • faq/Others.md
m
@Rocky I thought for partial updates, I can specify a superset of column I want to update in the headers. Then, for the payload, it can be sparse in the sense each record only specifies the columns it wants to update. For records with the column absent in json payload, shouldnt it default to leacving the field alone? For instance
Copy code
curl -X PUT \
  --location-trusted \
  -H "Expect:100-continue" \
  -H "label:partial_update_has_won_001" \
  -H "format: json" \
  -H "partial_update: true" \
  -H "partial_update_mode: column" \
  -H "columns: received, auction_id, imp_key, has_won, has_click" \
  -u root: \
  '<http://starrocks-core-services-fe-1.us-east-1.stackadapt:8030/api/requests/test/_stream_load>' \
  -d '{"received":"2025-01-01 10:00:00","auction_id":"auction_001","imp_key":"imp_001","has_won":1}'
Reset my has_click field from 0 to NULL. I was hoping to leave it as 0.
r
In StarRocks, the 
columns
header in a Stream Load does not act as a "superset" of potential columns; it defines the exact schema for every record in that ingestion batch. Here is why your 
has_click
field was reset to `NULL`: 1. The 
columns
Header vs. JSON Payload When you specify 
-H "columns: received, auction_id, imp_key, has_won, has_click"
, you are telling StarRocks: "Every record in this payload contains these five fields." When the JSON parser encounters a record like 
{"received":..., "auction_id":..., "imp_key":..., "has_won":1}
, it sees that 
has_click
is missing. In StarRocks, a missing key in a JSON payload for a column defined in the header defaults to 
NULL
 (unless a different default is specified in the mapping). 2. The Overwrite Behavior Because 
has_click
is included in your 
columns
header, the Primary Key engine receives an update instruction for that row that looks like this: * 
has_won = 1
* 
has_click = NULL
Since you are using 
partial_update: true
, the engine updates every column listed in your header. It doesn't know that the 
NULL
for 
has_click
was "accidental" due to a missing JSON key; it assumes 
NULL
is the intended new value and overwrites the existing 
0
. ────────── How to fix this To prevent 
has_click
from being overwritten when it isn't in your payload, you have two options: Option A: Remove the column from the header (Recommended) If your entire batch only intends to update 
has_won
, remove 
has_click
from the 
columns
header. The Primary Key engine will then only touch the columns you explicitly listed, leaving 
has_click
at its current value in the table.
Copy code
bash
-H "columns: received, auction_id, imp_key, has_won"
Option B: Use different loads for different update types If you have some records that need to update 
has_won
and others that need to update 
has_click
, you cannot safely put them in the same Stream Load batch using a single "superset" header. You should: 1. Split the data: Send one Stream Load for "won" updates and a separate one for "click" updates, each with its own specific 
columns
header. 2. Ensure payload match: Always ensure the keys in your JSON exactly match the columns listed in your 
-H "columns: ..."
header. A Note on "Column Mode" You are using 
partial_update_mode: column
. This is the most efficient way to update a single column (like 
has_won
) in a Primary Key table. However, its efficiency comes from only writing to the specific column files you've identified in the header. By including 
has_click
in the header, you forced StarRocks to perform a write operation on the 
has_click
column file for every row in that batch. Summary: Only include the columns you actually want to change in the 
columns
header. If a column is in the header but missing in the JSON, it will be updated to 
NULL
. References • loading/Load_to_Primary_Key_tables.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md References • loading/Json_loading.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
m
@Rocky Does this mean I have to maintain a separate pipeline for every event I need to update my StarRocks DB column with? On a follow-up,. It doesn't make sense to use column mode updates then right? I have many different sets of columns I need to update
r
You don't necessarily need separate infrastructure "pipelines," but with the way StarRocks handles Partial Updates, you do need to separate your Stream Load requests by their specific update schemas. Here is the breakdown of why this is happening and how to manage it without over-engineering your setup. 1. The "Single Schema per Batch" Rule The 
columns
header in a Stream Load is a contract for the entire batch. * If you include 
has_click
in the header, StarRocks expects 
has_click
to be provided for every row in that specific request. * If a JSON record is missing that key, the parser defaults it to 
NULL
. * The Primary Key engine then treats that 
NULL
as a valid update instruction and overwrites your existing 
0
. 2. Do you need separate pipelines? You don't need separate Kafka topics or separate Flink/Spark jobs, but you should group your updates by their schema signature before sending them to StarRocks: * Option A: Grouping in your app (Recommended): If you are using a custom ingestion script, buffer your events and group them by "event type" (e.g., all "Won" events together, all "Click" events together). Flush each group as its own Stream Load with a header that matches only those columns. * Option B: Aggregate Table with `REPLACE_IF_NOT_NULL`: If your updates are extremely sparse (e.g., hundreds of possible columns where any given event only updates 1-2), consider using an Aggregate Table instead of a Primary Key table. * In an Aggregate table, you can define columns with the aggregate function 
REPLACE_IF_NOT_NULL
. * When you send a 
NULL
(due to a missing JSON field), StarRocks will ignore it and keep the existing value. _ _Trade-off:* Aggregate tables use "Merge-on-Read," so complex queries may be slower than on a Primary Key table. 3. Does Column Mode still make sense? Yes, but specifically for high-volume single-column updates. * When to use Column Mode: Use it for your most frequent updates that hit a very small number of columns (like 
has_won
or 
has_click
). It is 10x+ faster for these "skinny" updates because it only rewrites the files for those specific columns. * When it fails: It doesn't make sense if you try to create a "catch-all" header with 20 columns. By including columns you aren't actually updating, you lose the performance benefit and risk the 
NULL
overwrite issue you just experienced. Diagnostic Checklist for your 20% discrepancy: 1. Check for "NULL" overwrites: Query for rows where 
has_won = 1
but other fields you expected to stay are now 
NULL
. This confirms the "superset header" issue. 2. Filter for "Won-before-Impression": If a "Won" event arrives before the "Impression" event is ever created, the 
partial_update
will create a new row where only the 
has_won
and PK columns are populated. Everything else will be 
NULL
. 3. Check `merge_condition`: If you are using a version/timestamp column, ensure the "Won" event doesn't have an older timestamp than the existing record. If it does, the update is silently ignored by design. Recommendation: Remove the "superset" header. Split your ingestion into separate Stream Load calls—one for "Impression" (full row), one for "Win" (PK + 
has_won
), and one for "Click" (PK + 
has_click
). This will stop the 
NULL
overwrites and likely recover your missing 20%. References • loading/Load_to_Primary_Key_tables.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • table_design/table_types/primary_key_table.md References • loading/Load_to_Primary_Key_tables.md
m
@Rocky Would it make sense to only enable column partial update mode for my schema that has highest volume updates? About 8k QPS. And others stick to row mode? On a different note, how would partial updates by schema work for routine loads?
r
Mixing update modes for the same table is a valid and often recommended strategy. Here is how to handle your specific scenario: 1. Mixing Column and Row Mode Yes, it makes perfect sense to use Column Mode for your 8k QPS updates and Row Mode for others. In StarRocks, the "mode" is not a table-level setting; it is a per-load property. You can have one pipeline using Column Mode and another using Row Mode simultaneously on the same Primary Key table. * Column Mode (High Volume, Few Columns): At 8k QPS, if those updates only touch 1 or 2 specific columns (like 
has_won
), Column Mode is significantly more efficient. It avoids the "read-modify-write" penalty of Row Mode by only updating the relevant column files. * Row Mode (Lower Volume or Many Columns): If your other updates are less frequent or update many columns at once, stick to Row Mode. It is better for "small batch, many column" updates. A note on 8k QPS: While "8k QPS" sounds like high frequency, Column Mode actually performs best when those updates are batched. If you send 8,000 separate 
curl
requests per second, you will overwhelm the system regardless of the mode. Ensure you are batching these updates (e.g., thousands of rows per Stream Load) to see the performance benefits. 2. Partial Updates in Routine Load Routine Load has some specific limitations regarding partial updates compared to Stream Load. The Column Mode Limitation As of the current stable versions (up to v3.3), Routine Load only supports Row Mode for partial updates. It does not yet support 
partial_update_mode: column
. * If you absolutely need Column Mode for that 8k QPS stream, you should use Stream Load (e.g., via a Flink/Spark connector or a custom script that reads from Kafka and flushes in batches). How "Partial Updates by Schema" works in Routine Load Routine Load is "schema-rigid" per job. To handle different event types (different sets of columns) from Kafka, you have two options: Option A: Multiple Routine Load Jobs (Recommended) If your Kafka topic contains different event types, you can create multiple Routine Load jobs pointing to the same topic and table, but with different 
WHERE
filters and 
COLUMNS
mappings.
Copy code
sql
-- Job 1: Only handles "Win" events
CREATE ROUTINE LOAD win_updates ON my_table
COLUMNS(auction_id, has_won)
PROPERTIES ("partial_update" = "true")
FROM KAFKA (
  "kafka_topic" = "my_topic",
  "where" = "event_type = 'win'" -- Assuming your JSON has an event_type field
);

-- Job 2: Only handles "Click" events
CREATE ROUTINE LOAD click_updates ON my_table
COLUMNS(auction_id, has_click)
PROPERTIES ("partial_update" = "true")
FROM KAFKA (
  "kafka_topic" = "my_topic",
  "where" = "event_type = 'click'"
);
Option B: Single Job with Sparse JSON (The "NULL" risk) You can have one Routine Load job with a wide 
COLUMNS
list, but you run into the exact same problem you saw with Stream Load: if a JSON record is missing a field defined in the 
COLUMNS
list, StarRocks will interpret that as 
NULL
and overwrite your existing data. _ _Note:* Unlike Stream Load, Routine Load does not have a "superset" header logic; it relies strictly on the 
COLUMNS
mapping you define in the 
CREATE ROUTINE LOAD
statement. Summary Recommendation 1. For the 8k QPS 
has_won
updates: Use Stream Load (with batching) and set 
partial_update_mode: column
. This gives you the performance you need. 2. For other updates: Use Routine Load or Stream Load in Row Mode. 3. Schema separation: Always ensure that the 
columns
specified in your load (whether Stream or Routine) match exactly the keys in your JSON payload to avoid accidental 
NULL
overwrites. References • loading/Load_to_Primary_Key_tables.md
• sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/loading_introduction/feature-support-loading-and-unloading.md References • loading/Load_to_Primary_Key_tables.md • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/StreamLoad.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/Spark-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • administration/management/monitoring/metrics.md • knowledge/usage/best-practice.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • table_design/table_types/primary_key_table.md • table_design/table_types/table_capabilities.md References • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Spark-connector-starrocks.md • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/Kafka-connector-starrocks.md
m
@Rocky Can I just stream load partial updates with merge commit enabled but dont bother batching? Since similar header will be grouped. Any downsides?
r
You can use the Merge Commit feature (introduced in v3.4.0) to avoid manual batching, but there are significant performance and operational trade-offs you should consider—especially at 8k QPS. 1. How grouping works with Merge Commit Merge Commit does exactly what you're hoping for: it groups concurrent Stream Load requests into a single transaction. However, it is strictly homogeneous: * Identical Headers Required: Requests will only be merged if their parameters are exactly identical. This includes the 
columns
list, 
partial_update
, 
partial_update_mode
, and format settings. * Different Headers = Different Transactions: If you have one request updating 
[col1, col2]
and another updating 
[col1, col3]
, they will not be merged together. They will create separate transactions, defeating the purpose of the optimization. 2. The Downsides of Skipping Manual Batching While Merge Commit solves the "too many versions" and "compaction pressure" issues at the storage level, it does not solve the HTTP/Network overhead at the entry point: * FE Bottleneck: Processing 8,000 individual HTTP requests per second puts a massive CPU load on your Leader FE (authentication, connection management, and parsing the request headers). Even with Merge Commit, the FE still has to "receive" 8k individual calls. * All-or-Nothing Failures: If any single record in a merged transaction fails (e.g., a data type mismatch or a JSON parsing error), the entire merged transaction is rolled back. At 8k QPS with a 1-second merge window, one bad record could cause 8,000 legitimate updates to fail. * Latency vs. Throughput: * Synchronous mode: Your client will wait for the entire merge window (e.g., 
merge_commit_interval_ms=1000
) before getting a "Success" response. * Asynchronous mode: Your client gets a response immediately, but you lose the guarantee that the data was actually written. You would have to implement separate logic to check transaction status later. 3. Recommendation For your 8k QPS scenario: 1. Don't skip batching entirely: Try to get your requests down from 8,000/sec to something like 10–50/sec at the application level if possible. 2. Use Merge Commit as a "Safety Net": Enable Merge Commit to handle the concurrency between your different load workers, but still perform light batching (e.g., 100–500 rows per request) to protect the FE from request overhead. 3. Ensure Header Consistency: Group your data in your application by the specific columns being updated so that the 
columns
header remains identical for large chunks of data, allowing StarRocks to merge them effectively. Example Header for Merge Commit:
Copy code
bash
-H "enable_merge_commit: true" \
-H "merge_commit_interval_ms: 1000" \
-H "partial_update: true" \
-H "partial_update_mode: column" \
-H "columns: auction_id, has_won"
References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Flink-connector-starrocks.md References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md
m
@Rocky Is partial update mode column compatiebl with merge commit and merge commit async? If so, link me documentation proving so,
r
Yes, Partial Update (Column Mode) is compatible with both Merge Commit and Merge Commit Async. According to the official documentation, Merge Commit (introduced in v3.4.0) merges "homogeneous" requests. Requests are considered homogeneous if their parameters are identical, specifically including 
opt_properties
, where 
partial_update_mode
is defined. Proof of Compatibility 1. Homogeneous Requirement: The Stream Load documentation states: > "Merge Commit only supports merging homogeneous loading requests... 'Homogeneous' indicates that the Stream Load parameters are identical, including: common parameters, JSON format parameters, CSV format parameters, opt_properties, and Merge Commit parameters." 2. 
partial_update_mode
is an 
opt_property
: In the same Stream Load parameter reference, 
partial_update_mode
is explicitly listed under the 
opt_properties
section. 3. Async Support: 
merge_commit_async
is a sub-parameter of the Merge Commit feature. There are no documented restrictions preventing it from working alongside Column Mode. Summary of Requirements For this to work at your 8k QPS, your requests must be identical in their headers: * Identical Columns: 
-H "columns: id, col1, col2"
* Identical Mode: 
-H "partial_update: true" -H "partial_update_mode: column"
* Identical Merge Params: 
-H "enable_merge_commit: true" -H "merge_commit_async: true"
Important Documentation Links * Stream Load: Merge Commit Parameters (Shows the "homogeneous" rule including 
opt_properties
). * Primary Key Table: Partial Updates (Explains Column Mode vs. Row Mode). Note on Routine Load: While compatible with Stream Load, be aware that Routine Load does not support Column Mode partial updates; it only supports Row Mode. Since Routine Load lacks the Merge Commit feature anyway, this is usually why high-QPS users stick to Stream Load. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/System_variable.md • administration/management/BE_configuration.md • loading/Load_to_Primary_Key_tables.md • loading/Kafka-connector-starrocks.md • administration/management/monitoring/metrics.md • loading/Spark-connector-starrocks.md
m
@Rocky But my starrocks_be_column_partial_update_apply_total staying as zero.
Copy code
req.SetBasicAuth(c.username, c.password)
	req.Header.Set("Expect", "100-continue")
	req.Header.Set("format", "JSON")
	req.Header.Set("label", label)
	req.Header.Set("strip_outer_array", "true")
	req.Header.Set("ignore_json_size", "true")
	req.Header.Set("enable_merge_commit", "true")
	req.Header.Set("merge_commit_async", "true")
	req.Header.Set("merge_commit_interval_ms", "10000")
	req.Header.Set("merge_commit_parallel", "8")
	if options.partialUpdate {
		req.Header.Set("partial_update", "true")
		req.Header.Set("columns", strings.Join(options.columns, ","))
		if options.partialUpdateColumnMode {
			req.Header.Set("partial_update_mode", "column")
		}
	}
What is wrong? I already ensured partial_update_mode set to column for high throughput event
r
If your 
starrocks_be_column_partial_update_apply_total
metric is staying at zero, it means the StarRocks storage engine is falling back to Row Mode for your updates, even though you have set the 
partial_update_mode: column
header. There are three likely reasons for this behavior when using Merge Commit at high QPS: 1. Table Configuration: 
column_with_row
 If your table was created with the 
storage_type
set to 
column_with_row
(often used to optimize point queries), Column Mode partial updates are explicitly disabled. * Check: Run 
SHOW CREATE TABLE <your_table_name>;
* Look for: 
storage_type
or 
column_with_row
in the properties. If it is enabled, StarRocks will always use Row Mode to ensure the row-store index stays in sync. 2. Header Homogeneity & Merge Commit Failures Merge Commit only works if the requests are perfectly identical. If your 8k QPS traffic has even slight variations in headers between different requests, they won't be merged. * In your code: Ensure that 
options.columns
always contains the exact same columns in the exact same order for every request in that stream. If one request specifies 
id, has_won
and another specifies 
id, has_won, timestamp
, they will not merge, and you will effectively be doing 8,000 separate transactions per second, which may cause the system to ignore the Column Mode hint to save resources. 3. Feature Limitations with JSON & Merge Commit While Column Mode is theoretically compatible with Merge Commit, there are edge cases where the system falls back to Row Mode: * Complex Expressions: If your 
columns
header contains any functional mappings (e.g., 
col1 = upper(json_col)
), Column Mode is often disabled because it requires a "Read-Modify-Write" cycle that Row Mode is better at handling. * Small Batches: If Merge Commit is merging a very small number of rows (despite high QPS), the overhead of creating Delta Column Group (DCG) files (used in Column Mode) might be deemed too high by the backend, leading to a Row Mode fallback. * Shared-Data (Cloud-Native) version: If you are on the Shared-data (Lake) architecture, Column Mode metrics might be reported differently or have stricter requirements for the background "Apply" task. How to Verify the Actual Mode Since metrics can sometimes be delayed or misleading (especially with 
merge_commit_async: true
), you can verify the mode by checking the Load Profile: 1. Get the Label of one of your successful loads. 2. Run: 
GET /api/_load_profile?label=<your_label>
(or check the "Loads" tab in the StarRocks Manager/Dashboard). 3. Look for the 
PartialUpdateMode
field in the profile; it will explicitly state whether 
ROW
or 
COLUMN
was used for that specific transaction. Recommendation 1. Verify the Table Storage: If 
column_with_row
is enabled, you cannot use Column Mode. 2. Increase Batching: Even with Merge Commit, 8k QPS is extremely high for individual HTTP requests. Try to batch at least 100-500 rows per 
PUT
request in your Go code. This reduces the FE overhead and makes the "homogeneity" check more reliable. 3. Check StarRocks Version: If you are on a version earlier than v3.4.0, Merge Commit is not supported. If you are on v3.4.x, ensure you have the latest patch, as there were fixes for 
merge_condition
and partial update interactions in earlier v3.4 patches. References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • administration/management/monitoring/metrics.md References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md
m
@Rocky Does the fact that I have a separate manually generated label per stream load mean merge commit doesnt consider it homogenous?
r
No, the fact that you have a separate manual label does not break homogeneity. However, it is effectively useless when Merge Commit is enabled. According to the StarRocks documentation: * Labels are ignored: "The server automatically generates labels for transactions. They will be ignored if specified." * Homogeneity Check: While the documentation states that "common parameters" must be identical, it specifically calls out that the system-generated label overrides any user-provided label. In practice, because StarRocks ignores the label you send, it does not use it to differentiate "batches" for the homogeneity check. Why your metric is still zero Since the labels aren't the issue, your 
starrocks_be_column_partial_update_apply_total
staying at zero is likely due to one of the following: 1. Read-Modify-Write Fallback: If your JSON data contains nested structures or if you are using specific 
jsonpaths
, StarRocks may decide it's more efficient to perform a Row-mode "Read-Modify-Write" rather than a Column-mode "Delta Column Group" write. 2. Request Headers Mismatch: Check if any other headers are accidentally varying. For example, if 
options.columns
is a slice/list, ensure it's sorted before 
strings.Join
. If one request is 
id,col1,col2
and another is 
id,col2,col1
, they are not homogeneous and will be processed as separate (non-merged) transactions. Non-merged transactions at 8k QPS will likely default to Row Mode to handle the pressure. 3. Check 
partial_update_mode
visibility: In some versions, if the merge window is still open or the transaction is 
PREPARED
(but not yet 
VISIBLE
), the metrics might not increment immediately. 4. Backend "Plan" Choice: StarRocks sometimes ignores the 
column
mode hint if the number of rows being updated is very small (e.g., merging only a few rows per 10s window). Practical Test To prove Merge Commit is actually working despite the labels, check your FE logs or use the following SQL to see if the number of transactions is significantly lower than your 8k QPS:
Copy code
sql
-- Check the number of successful loads in the last few minutes
SELECT count(*) FROM information_schema.loads 
WHERE database_name = 'your_db' AND state = 'FINISHED' 
AND create_time > now() - interval 1 minute;
If this count is low (e.g., closer to 6 per minute with your 10s interval), Merge Commit is working, and the "Zero Metric" issue is purely down to the storage engine deciding Row Mode is more appropriate for the specific data/schema. Recommendation: Ensure your 
options.columns
are strictly ordered in your Go code to guarantee homogeneity.
Copy code
go
sort.Strings(options.columns) // Ensure identical header across all requests
req.Header.Set("columns", strings.Join(options.columns, ","))
References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md • loading/Load_to_Primary_Key_tables.md • administration/management/monitoring/metrics.md References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Flink-connector-starrocks.md • loading/Stream_Load_transaction_interface.md
The increase in your compaction score is directly caused by the shift from a single "sparse" update to 15+ "schema-based" updates. Because Merge Commit only merges homogeneous requests, your 15 different schemas (different 
columns
headers) are being treated as 15 separate, parallel ingestion streams. Even if they all flush every 30 seconds, StarRocks is forced to create 15 separate rowsets (versions) per commit interval. Why this isn't sustainable In StarRocks (especially Primary Key tables), each transaction creates a new version (rowset). * Old "Sparse" Way: 1 transaction every 30s = 2 rowsets per minute. * New "Schema-based" Way: 15 transactions every 30s = 30 rowsets per minute. * Result: You are generating rowsets 15x faster than before. At 8,000 QPS, these rowsets are likely small, leading to the "Small File Problem" and high compaction pressure (high 
MaxCS
). How to Fix It 1. Consolidate Schemas (Recommended) You should group your 15 schemas into as few "Unified Schemas" as possible. Merge Commit will then be able to bundle events from different categories into a single transaction. * Strategy: Create a "Unified Conversion Schema" that includes all columns from 
ConvPrimary
, 
ConvLastTouch
, 
ConvSecondary
, etc. * How to handle missing fields: If you use Row Mode (
partial_update_mode: row
), StarRocks only updates the columns actually present in the JSON row. If you provide a unified 
columns
header but a specific JSON row is missing a key, StarRocks will preserve the existing value in the table rather than overwriting it with NULL. _ _Note:* You mentioned 8k QPS. While Column Mode is generally faster for writes, Row Mode combined with Merge Commit is often more than enough because Merge Commit reduces the transaction overhead by 100x. 2. Use a "Mega-Header" with Column Mode If you must stay on Column Mode, you can still use a unified header, but you must ensure your ingestion logic doesn't NULL out data. * If you send 
columns: pk, has_click, has_won
but the data only contains 
has_click
, StarRocks Column Mode might try to write a NULL for 
has_won
. * Workaround: Check if your events are disjoint. If a row only ever receives a "Click" OR a "Win" (and never both), you can use a unified header. If they overlap, Schema Consolidation with Row Mode is safer. 3. Increase Merge Interval If your latency allows, increase the BE-side merge window to match or exceed your client-side flush. * Set 
-H "merge_commit_interval_ms: 30000"
(30s) or higher. _ This won't solve the "15 streams" problem, but it ensures that if multiple clients are sending the _same* event type, they are bundled even more aggressively. 4. Tune Compaction for Primary Key Tables If you cannot reduce the number of schemas, you must tell the Backends to compact faster: * 
cumulative_compaction_num_threads_per_disk
: Increase this (e.g., to 4 or 8) to allow more parallel compaction. * 
base_compaction_num_threads_per_disk
: Increase this to help merge those cumulative rowsets into the base data. * 
update_compaction_ratio
: Lower this threshold to trigger compaction earlier. Summary Recommendation To restore your compaction score, unify your headers. Instead of 15 
partialUpdateSchemaSpecs
, try to bring it down to 3–4 broader specs. For example, combine all "Conversion" events into one spec and all "AdContext" events into another. This will allow Merge Commit to collapse 15 streams into 3, reducing your rowset creation rate by 80%. Documentation Proof on Homogeneity:
"Homogeneous indicates that the Stream Load parameters are identical, including: common parameters... and opt_properties [where 
columns
and 
partial_update_mode
live]."
— StarRocks Stream Load Docs
m
@Rocky How to handle missing fields: If you use Row Mode (
partial_update_mode: row
), StarRocks only updates the columns actually present in the JSON row. If you provide a unified 
columns
header but a specific JSON row is missing a key, StarRocks will preserve the existing value in the table rather than overwriting it with NULL. This is not true. I did this curl command
Copy code
curl -X PUT \
  --location-trusted \
  -H "Expect:100-continue" \
  -H "format: json" \
  -H "partial_update: true" \
  -H "partial_update_mode: row" \
  -H "columns: received, auction_id, imp_key, has_won, has_click" \
  -u root: \
  '<http://starrocks-core-services-fe-1.us-east-1.stackadapt:8030/api/requests/test/_stream_load>' \
  -d '{"received":"2025-01-01 10:00:00","auction_id":"auction_001","imp_key":"imp_001","has_won":0}'
It nullified my has_click column from 1 to 0
r
You are correct, and I apologize for the earlier oversight. In Row Mode partial updates, if a column is listed in the 
columns
header but is missing from the JSON record, StarRocks fills it with a NULL (or the column's default value). The storage engine then treats this as an intentional update, overwriting your existing data. This creates a conflict between performance (Merge Commit) and correctness (Partial Updates): 1. Merge Commit requires identical 
columns
headers to bundle requests. 2. Partial Updates require specific 
columns
headers to avoid nullifying data you don't have. If you have 15 different schemas, you are effectively running 15 separate ingestion streams. At 8,000 QPS, this generates rowsets 15x faster than a single stream, which is why your Compaction Score (MaxCS) has doubled. How to fix the Compaction Score To resolve this without losing data, you should move away from 15 high-frequency "micro-streams" and focus on Client-Side Batching. 1. Implement Client-Side Batching (The "Real" Fix) Merge Commit is a safety net for high-concurrency small writes, but it is not a replacement for efficient batching. Instead of sending each event as an HTTP request: * In your Go code, create 15 internal buffers (one for each schema). * Buffer events for 5–10 seconds or until you reach 5 MB of data. * Flush the buffer as a single 
PUT
request with the correct 
columns
header for that schema. * Result: You go from 8,000 requests per second to 1.5 requests per second (15 schemas × 1 flush every 10s). The compaction system will easily handle 1.5 rowsets per second, and your MaxCS will drop significantly. 2. Group Homogeneous Events Look at your 
partialUpdateSchemaSpecs
. Some appear to be subsets of each other: * 
EventUpdateSchemaLoss
(
bid_is_loss
, 
bid_loss_reason
) * 
EventUpdateSchemaLossMinBid
(
bid_is_loss
, 
bid_loss_reason
, 
min_bid_to_win
) If you can modify your "Loss" event to always include a 
null
or a specific value for 
min_bid_to_win
, you can merge these into a single schema. This reduces the number of unique "streams" the compaction engine has to track. 3. Why 
partial_update_apply_total
is Zero Your metric is zero because the StarRocks storage engine is likely defaulting to Row Mode. Column Mode is only used when the system determines it is more efficient (usually when updating < 30% of a table's columns in a very large batch). * Merge Commit + Row Mode: This is the standard behavior for high-concurrency small updates. * Primary Key Persistence: Check if your table has 
enable_persistent_index = true
. This is required for high-performance partial updates at your scale. 4. Tune Compaction Threads If you cannot reduce the number of streams, you must allow StarRocks to compact faster. Adjust these in your BE configuration (
be.conf
): * `cumulative_compaction_num_threads_per_disk`: Increase this (e.g., to 4 or 8) to merge those 15 streams into one faster. * `base_compaction_num_threads_per_disk`: Increase this to 2 or 4. * `update_compaction_ratio`: Lower this (e.g., to 0.3) to trigger compaction more aggressively for Primary Key tables. Summary The "Zero Metric" isn't your main problem—the rowset explosion is. By batching your events by schema in your Go application for just 5-10 seconds, you will drastically reduce the pressure on StarRocks, and your compaction score will return to normal levels. References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • administration/management/monitoring/metrics.md • loading/StreamLoad.md
m
@Rocky If I assign my stream loading application a resource group having cpu_weight being 1, what process of stream load will be affected? To my knowledge once it reaches the BE, it detects stream load as a system job, hence use default_wg. What will the application resource group be used for?
r
If you assign your Stream Load user to a resource group with 
cpu_weight: 1
, the loading process will be affected, but only the parts associated with the "active" ingestion task. The idea that Stream Load is always a "system job" using 
default_wg
is a common misconception. While StarRocks has internal system tasks, the Load Plan (the actual execution logic on the BE) is treated as a query-type workload and follows your Resource Group classifiers. 1. What processes WILL be affected? When a Stream Load hits a resource group (via 
query_type = 'insert'
or a 
user
classifier), the following BE activities are governed by that group's `cpu_weight`: * Data Parsing & Vectorization: The CPU time used to parse your JSON/CSV data and convert it into StarRocks' internal vectorized blocks. With a weight of 1, if the CPU is busy with other queries, your parsing will slow down significantly. * Primary Key Indexing: For PK tables, the lookups and updates to the Primary Key index (calculating which rows are updates vs. inserts) happen within the load's execution context. * Data Shuffling (RPC): The processing of bRPC requests to move data between BE nodes (if your table is distributed across multiple nodes) is linked to the load task's priority. * Segment Writing: The final step of formatting data into the storage engine's segment format. 2. What processes will NOT be affected? Resource groups primarily manage the Pipeline Engine (active execution). Background storage engine tasks are generally excluded from user-defined resource weights: * Compaction: This is a background "System Job." It does not run inside your Stream Load's resource group. It has its own internal scheduling and thread pools (
cumulative_compaction_num_threads_per_disk
). * Tablet Metadata Management: Background tasks like tablet cloning, replica repair, and checkpointing. * Publish Version: The final transaction commit phase (handled by the FE and BE metadata threads). 3. Why 
cpu_weight: 1
is dangerous In StarRocks, 
cpu_weight
is a relative share. * The 
default_wg
(which handles all unclassified queries) usually has a weight equal to the number of CPU cores on the BE. * If your BE has 64 cores, 
default_wg
has a weight of 64. * If you give your load group a weight of 1, and the BE is under full load, your ingestion will only receive ~1.5% (1 / 65) of the CPU time. Summary: What is the application resource group used for? It is used to throttle the ingestion's impact on concurrent queries. If your goal is to ensure that 8,000 QPS of Stream Loads don't "steal" CPU from your sub-second analytical dashboard, then putting them in a low-weight resource group is correct. However, setting it to 1 may cause your Stream Load buffers to fill up or time out because the BE isn't giving the parsing/indexing logic enough cycles to keep up with the incoming HTTP data. How to verify: You can check which resource group your loads are hitting by looking at the 
ResourceGroup
column in the FE audit log (
fe.audit.log
). If it shows your custom group name, your 
cpu_weight
is actively limiting that load's execution. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/BE_configuration.md • best_practices/audit_log_resource_group.md • loading/StreamLoad.md References • administration/management/resource_management/resource_group.md
m
@Rocky THis only applies to stream load data loading, not partial updates.
r
The user is correct that there is a significant distinction between the Loading Phase and the Apply Phase (partial updates) when it comes to resource group isolation. While my previous answer holds for the initial stages of a Stream Load, your observation hits on a critical implementation detail for Primary Key (PK) tables: 1. The Resource Group Boundary A Resource Group (with a classifier like 
query_type = 'insert'
) primarily governs the Pipeline Engine tasks. For a Stream Load, this includes: * Data Parsing: Converting your JSON/CSV to internal blocks. * Data Shuffling: Sending data across the network to the correct BE nodes. * Memtable Flush: Writing the initial data into temporary rowsets. However, Partial Updates on Primary Key tables involve a secondary, highly intensive phase called the Apply Phase. 2. Why Partial Updates bypass CPU Weight In Primary Key tables, once the data is "loaded" (written to a temporary rowset), it must be "applied" to the table. This is where StarRocks performs the PK Index lookup and generates Delete Vectors. * The "Apply" Phase is Global: This phase is currently handled by a global backend thread pool, not the individual Pipeline tasks associated with your Stream Load request. * Worker Threads: The concurrency of this phase is controlled by the BE setting 
transaction_apply_worker_count
(which defaults to the number of CPU cores). * System Priority: Because the "Apply" phase is part of the transaction's 
publish_version
step, it is treated as a critical system task. If the Apply phase were throttled by a user's low 
cpu_weight
, it would delay the transaction commit for the entire table, potentially causing version pile-ups and blocking other users. 3. Row Mode vs. Column Mode Difference * Row Mode (Your current mode): The "Apply" logic is very CPU-intensive because it involves checking every incoming row against the PK Index. Since this happens in the global 
apply
pool, your 
cpu_weight: 1
will not effectively throttle the CPU spikes caused by these index lookups. * Column Mode: This mode actually shifts some of the burden back into the "Loading Phase" (specifically when generating Delta Column Groups). In this case, more of the work would be captured by the resource group, but the final index update still hits the global pool. How to actually throttle Partial Updates If you want to limit the CPU impact of your 8k QPS partial updates, you cannot rely solely on Resource Groups. You must use these BE configurations (
be.conf
): 1. 
transaction_apply_worker_count
: Lower this (e.g., to 
4
or 
8
) to cap how many threads can perform PK index lookups simultaneously. This is the "hard throttle" for partial update CPU usage. 2. 
transaction_publish_version_worker_count
: Similarly, lowering this limits the concurrency of the final commit phase. 3. Client-Side Batching (Again): As discussed before, reducing the number of transactions is the only way to reduce the total amount of "Apply" work the system has to do. 15 streams flushing every 30s means the global 
apply
pool is constantly being hammered. Summary You are right: Resource Groups do not effectively throttle the "Apply" logic of Primary Key partial updates. They only throttle the data ingestion/parsing part. To control the CPU usage of your updates, you must tune the global 
transaction_apply_worker_count
or reduce the update frequency.
m
@Rocky I mean the simple mode partial update you mentioned to solve my sparse payload issues (workaround using schema pipelines now) does not work. Simple mode only applies to data insertion, not partial updates. It doesnt solve my compaction score issue
r
You are absolutely right. I apologize for the confusion—*"Simple Mode"* (where you omit the 
columns
header and let StarRocks auto-map JSON keys) is indeed designed for standard UPSERT/INSERT operations. It does not solve the sparse payload issue for partial updates because 
partial_update: true
requires an explicit 
columns
list to know which fields to "touch" and which to "ignore." Your dilemma is the classic trade-off between Ingestion Concurrency and Correctness: 1. Unified 
columns
header: Allows Merge Commit to bundle all 8,000 QPS into one stream, which keeps your MaxCS (Compaction Score) low. However, missing fields are treated as NULLs, corrupting your data. 2. Schema-specific 
columns
headers (15 versions): Correctly updates only the fields you have. However, Merge Commit cannot group different headers, resulting in 15 parallel ingestion streams. At 8,000 QPS, this creates rowsets faster than the compaction engine can merge them, causing your MaxCS to spike. The Solution: Schema-Aware Client-Side Batching Since StarRocks' Merge Commit cannot currently merge requests with different 
columns
headers, the "safety net" of Merge Commit isn't working for your multi-schema use case. To lower your compaction score without losing data, you should move the batching logic to your application: 1. Batch by Schema: In your Go code, maintain 15 separate buffers (one for each schema). 2. Flush by Time/Size: Instead of sending each event as a single HTTP request, flush each buffer every 5–10 seconds (or when it reaches 5 MB). 3. Result: Instead of 8,000 tiny requests per second, you will send roughly 1.5 large requests per second (15 schemas × 1 flush every 10s). * MaxCS: Will drop dramatically because you are only creating ~1.5 rowsets per second total. * Correctness: Each request uses the specific 
columns
header for its schema, so no data is nullified. * Performance: Large batches are significantly more efficient for the BE storage engine than many small "Merge Commit" batches. Why "Simple Mode" and Resource Groups don't help here: * Simple Mode: As you found, it maps all keys. If you use it for a partial update, StarRocks doesn't know which keys are "missing but should be preserved" vs "missing and should be NULL." It defaults to overwriting with NULLs or default values. * Resource Groups: These only throttle the Pipeline Tasks (parsing and shuffling). The most CPU-intensive part of your updates—the Apply Phase (checking the PK Index and generating Delete Vectors)—runs in a global system thread pool (
transaction_apply_worker_count
) and ignores your 
cpu_weight
. Summary Recommendation Stop relying on Merge Commit for this specific use case. It is not designed to unify multiple different sparse schemas. Client-side batching is the industry standard for your scale (8k QPS) and is the only way to keep your Compaction Score healthy while maintaining 15 different update schemas. References • loading/StreamLoad.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Json_loading.md • administration/management/BE_configuration.md References • using_starrocks/Flat_json.md • release_notes/release-3.3.md
m
@Rocky I already do client side batching. I flush the data every 30 seconds. My compaction score is still very high (200-300). My starrocks_be_max_tablet_rowset_num is 40-50 now
r
The reason your compaction score (MaxCS) is 200–300 despite having only 40–50 rowsets and flushing every 30 seconds is likely due to the default compaction throttling and the size-tiered calculation logic used for Primary Key tables. In StarRocks Primary Key tables, a rowset count of 50 is actually quite high for a healthy system, and the "Score" is calculated differently than in other table models. 1. The 120-Second "Wait" (The most likely culprit) By default, the StarRocks BE has a safety throttle for Primary Key compaction: * Parameter: 
update_compaction_per_tablet_min_interval_seconds
* Default: 
120
(seconds) Even if you flush every 30 seconds, StarRocks will, by default, only attempt to compact a specific tablet once every 2 minutes. * In your case: 15 schemas × 4 flushes (in 120s) = 60 potential rowsets created per tablet before the background thread is even allowed to touch them again. * Action: Lower this value in your 
be.conf
to match your ingestion frequency (e.g., set to 
20
or 
30
). 2. Why Score (300) > Rowset Num (50) In PK tables, the compaction score is not a 1:1 count of rowsets. It uses a Size-Tiered strategy (controlled by 
enable_pk_size_tiered_compaction_strategy
): * Rowsets are grouped by size (e.g., small, medium, large). * The score is calculated based on the number of files in the most crowded size tier. * If your batches are small (e.g., you are sending 15 different schemas), they all fall into the same "Small" tier. A high number of small files in one tier triggers a much higher score to signal the BE that the table is becoming fragmented. * If your rowsets have multiple segments (data files within one rowset), each segment contributes to the score. 3. Recommendations to Lower the Score To bring that 200–300 score down, you need to allow the BE to be more aggressive with Primary Key compaction: Modify 
be.conf
on all nodes: 1. Lower the interval: 
update_compaction_per_tablet_min_interval_seconds = 30
(This allows compaction to trigger as soon as your 30s flush finishes). 2. Increase concurrency: 
update_compaction_num_threads_per_disk = 2
(or more, depending on CPU) (This gives the BE more "workers" to handle the 15 parallel streams). 3. Check segment count: Check if your rowsets have many segments. If one 30s batch is creating 10+ segments, you might need to increase 
push_write_mbytes_per_rowset
or check your memory limits. Summary Your 30-second batching is good, but the BE is currently ignoring 3 out of every 4 batches you send because of the 120s default interval. Reducing 
update_compaction_per_tablet_min_interval_seconds
is the direct fix for a high compaction score in a high-concurrency partial update scenario. References • best_practices/primarykey_table.md • administration/management/BE_configuration.md • administration/management/compaction.md • administration/management/monitoring/metrics.md • loading/StreamLoad.md References • administration/management/BE_configuration.md • best_practices/primarykey_table.md
2 Views
Open in Slack
PreviousNext
Powered by