http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> These are the be configs that has w...
# questions-and-troubleshooting
m
@Rocky These are the be configs that has worked for me in my shared-nothing cluster
Copy code
update information_schema.be_configs set value = 4 where name = 'pindex_major_compaction_limit_per_disk';
update information_schema.be_configs set value = 12 where name = 'compact_threads';
update information_schema.be_configs set value = 10 where name = 'update_compaction_per_tablet_min_interval_seconds';
update information_schema.be_configs set value = 5 where name = 'update_compaction_check_interval_seconds';
update information_schema.be_configs set value = 100 where name = 'max_cumulative_compaction_num_singleton_deltas';
update information_schema.be_configs set value = 4 where name = 'update_compaction_num_threads_per_disk';
It was previously for 8 r6in.4xlarge BE nodes on 4 EBS (Maintaining 4 vCPU: 1EBS ratio). However, I recently upgraded them all to r6in.8xlarge. The number of EBS is still the same. Is there anything I should tune to make sure my partial update operations are fully utilizing capabilities of the new machines?
r
Great upgrade! Moving from 
r6in.4xlarge
to 
r6in.8xlarge
doubles your CPU (16 to 32 vCPUs) and RAM (128GB to 256GB). Since your storage configuration (4 EBS volumes) remains the same, your main objective is to let the software utilize the additional compute and memory resources to handle the Primary Key table "Apply" and "Compaction" phases more efficiently. Here are the recommended tunings for your new setup: 1. Scale Up Update and Apply Parallelism Since your vCPU count doubled, you should ensure the thread pools responsible for applying transactions and managing the Persistent Index (pindex) are scaling accordingly. * 
transaction_apply_worker_count
: In StarRocks 3.2+, this defaults to 0 (which auto-detects CPU cores). If you previously had this explicitly set to a low value, you should update it or set it to 
0
to allow it to use all 32 cores.
Copy code
sql
    -- Set to 0 to auto-use all 32 cores
    update information_schema.be_configs set value = 0 where name = 'transaction_apply_worker_count';
* 
get_pindex_worker_count
: This controls the parallelism of loading Persistent Index data. Setting this to 32 (matching your cores) will speed up the "Apply" phase of your partial updates.
Copy code
sql
    update information_schema.be_configs set value = 32 where name = 'get_pindex_worker_count';
2. Adjust Compaction Threads You currently have 
compact_threads = 12
. The general best practice is to set this to roughly 25% of your CPU cores. * Recommendation: With 32 cores, 25% is 8. Since you were already at 12, you can keep it at 12 or even increase it slightly (up to 16) if your CPU utilization is low, but be mindful of your EBS I/O limits. 3. Deepen Update Compaction (Per Disk) You have 4 disks and 
update_compaction_num_threads_per_disk = 4
, giving you 16 concurrent update compaction tasks. * Recommendation: You can try increasing this to 8 (total 32 threads) since you now have the CPU cores to support more concurrent background merging. However, monitor your EBS "Volume Queue Length" and "Disk Read/Write Throughput" in AWS CloudWatch. If the disks are already saturated, increasing this won't help.
Copy code
sql
    update information_schema.be_configs set value = 8 where name = 'update_compaction_num_threads_per_disk';
4. Leverage the Increased Memory (256GB) Partial updates on Primary Key tables involve reading and merging columns. With 256GB of RAM, you can increase the memory limits for these operations to reduce I/O churn. * 
partial_update_memory_limit_per_worker
: This limits the memory used by a single worker to assemble chunks for partial updates. Increasing this allows for larger chunks to be processed in memory.
Copy code
sql
    -- Increase to 4GB (default is 2GB)
    update information_schema.be_configs set value = 4294967296 where name = 'partial_update_memory_limit_per_worker';
* 
l0_max_mem_usage
: This controls the maximum memory usage of the Persistent Index per tablet. If you have a very large number of tablets, increasing this can improve performance by keeping more of the index in memory.
Copy code
sql
    -- Default is 100MB (104857600). You can try 200MB if your RAM allows.
    update information_schema.be_configs set value = 209715200 where name = 'l0_max_mem_usage';
5. Summary of Recommended Adjustments Based on your hardware upgrade, here is a suggested configuration block to try: ```sql -- Scale worker counts to match 32 cores update information_schema.be_configs set value = 32 where name = 'transaction_apply_worker_count'; update information_schema.be_configs set value = 32 where name = 'get_pindex_worker_count'; -- Increase update compaction concurrency per disk (if EBS I/O allows) update information_schema.be_configs set value = 8 where name = 'update_compaction_num_threads_per_disk'; update information_schema.be_configs set value = 8 where name = 'pindex_major_compaction_limit_per_disk';
-- Utilize more RAM for partial updates update information_schema.be_configs set value = 4294967296 where name = 'partial_update_memory_limit_per_worker'; ``` Pro-Tip: Monitor the 
update
category in the BE's 
mem_tracker
(e.g., 
http://<be_ip>:8040/mem_tracker?type=update
) to see how much memory your Primary Key indexes and partial update processes are consuming after these changes. References • administration/management/BE_configuration.md • knowledge/usage/best-practice.md
m
@Rocky My company is a DSP, we sample 100% bids and 1% no bids to dataset. Only bids receive updates to their records like impressions, clicks, engagement events etc. Would increasing no bid sampling to 10% affect update performance? My theory is that these additional primary key indexes would contend with the bids primary key indexes to live in tablet memory (currently configured to 200MB per tablet) to reduce disk lookup for updates.
r
Your theory is spot on. Increasing the sampling of "no bids" from 1% to 10% will indeed affect update performance for your "bids" due to index and cache contention. In StarRocks, a Primary Key (PK) table maintains a mapping of 
Primary Key -> (File_ID, Row_ID)
. Even though your "no bid" records are never updated, they still require an entry in this index to ensure uniqueness and to allow for potential future updates or deletes. Here is exactly how the 10x increase in "no bid" records will impact your "bid" update performance: 1. Persistent Index (pindex) "Dilution" The Persistent Index is essentially a large disk-based hash table. When you update a "bid" record, StarRocks must look up its primary key to find the old row's location (so it can mark it as deleted). * The Cache Issue: While you have 
l0_max_mem_usage
set to 200MB, this acts as a write buffer and a small lookup cache. By increasing the total number of keys by 10x, the "bid" keys become a much smaller percentage of the total index. * Disk I/O: If a "bid" update occurs and its key isn't in the 200MB memory buffer, StarRocks must fetch the index page from disk. With a 10x larger index file on disk, the chance that the specific page you need is already in the OS Page Cache or StarRocks' internal Block Cache drops significantly. You will likely see an increase in 
Internal Disk Read
metrics during the "Apply" phase. 2. L0 Buffer Pressure during Ingestion Every time you import a batch of 10% "no bids," they must be "Applied" to the tablet. * The system checks if the "no bid" key already exists. * These new keys fill up the 
l0_max_mem_usage
buffer. * Because you are now sending 10x more keys, this 200MB buffer will flush to disk 10x more frequently, creating more small index files that then need to be merged (pindex compaction). This adds background CPU and I/O pressure. 3. Memory & Metadata Overhead Each tablet tracks its own index metadata. With 10x more rows, the index files on disk grow. * Bloom Filters: If you use them, they will grow or become less effective unless resized. * Memory Tracking: Monitor the 
update
category in your BE 
mem_tracker
. You will see the memory usage for 
index_metadata
and 
delete_vector
increase. ────────── Recommendations for the 10% Sampling Shift Since you've upgraded to 
r6in.8xlarge
(256GB RAM), you have the headroom to mitigate this: 1. Increase 
l0_max_mem_usage
significantly: Since you are worried about contention, give the index more room to breathe. If you have a reasonable number of tablets (e.g., a few hundred per BE), you can safely increase this.
Copy code
sql
    -- Try 512MB or even 1GB if your tablet count per BE is low (< 100)
    update information_schema.be_configs set value = 1073741824 where name = 'l0_max_mem_usage';
2. Monitor the "Apply" Latency: Use the 
be_pindex_lookup_latency
and 
be_apply_latency
metrics. If these spike after changing the sampling, it confirms that the larger index is causing slower lookups. 3. Consider "Warm" and "Cold" Data Split: If "no bids" truly never receive updates and aren't part of the same primary key range as "bids," you could technically store them in a Duplicate Key table or a separate PK table. However, if they must be in the same table: * Ensure your 
PARTITION BY
strategy allows old "no bids" to eventually move to colder storage or be ignored by the index (StarRocks releases pindex memory for tablets that haven't been updated for a while). 4. Persistent Index on SSD: Since you are on EBS, ensure you are using gp3 with high IOPS/throughput. The Persistent Index performance is extremely sensitive to random read latency. If the index grows 10x, your IOPS demand for updates will scale similarly. References • table_design/table_types/primary_key_table.md
m
@Rocky Does separating events into a different table help upsert performance? Let's say it is around 8.5 times smaller than my requests dataset. But both tables live in the same database and cluster. My hunch is that since they share same resources, the strain for pindex lookup, delvector marking etc. is the same. Different table does not help even if new tables a lot smaller?
r
Your hunch is actually incorrect: separating the smaller dataset into its own table significantly improves its upsert performance, even though both tables share the same BE resources. While they share the global thread pools and CPU, the data structures that govern upsert performance (Persistent Index and Delete Vectors) are managed at the tablet level. By splitting the data, you isolate the "hot" metadata of the smaller table from the "massive" metadata of the larger one. Here is why separating them helps: 1. Index Search Space & Cache Efficiency Upsert performance is primarily gated by how quickly StarRocks can look up a key in the Persistent Index (pindex). * One Big Table: If events and requests are together, the pindex file is massive. When you update an "event," StarRocks has to search a huge index. Even with the 
l0_max_mem_usage
cache, the "event" keys are competing with the "request" keys for space. The chance of a cache miss (requiring a disk read) is high. * Two Tables: The "events" table has its own set of tablets and its own dedicated index files. Since it is 8.5x smaller, its index is 8.5x smaller. A much larger percentage (or even 100%) of this smaller index can fit into the OS Page Cache or the l0 memory buffer. This results in near-instant lookups for the events table, regardless of how slow the requests table is. 2. Isolation of the "Apply" Phase The "Apply" phase (where StarRocks marks old rows as deleted in the Delete Vector) happens per tablet. * Reduced IOPS Contention: By separating tables, you are effectively separating the physical files on your EBS volumes. When the smaller table performs an upsert, it is reading and writing to its own small Delete Vector files and Index files. This avoids the "metadata bloat" of the large table, where even a small update might trigger larger internal merges or lookups. * Parallelism: StarRocks can process the 
transaction_apply_worker_count
across different tablets. If they are in the same table, they might share the same tablet (depending on your bucket key). If they are in different tables, they are guaranteed to be in different tablets, allowing for better parallel execution without lock contention at the tablet level. 3. Tailored Bucket and Partition Strategy When the tables are separate, you can optimize the Bucket Count for each based on their size: * For the 8.5x smaller table, you can use fewer buckets. This keeps the tablets a healthy size (~10GB–50GB) and ensures that the index per tablet is compact and efficient. * If they were combined, the bucket count would likely be optimized for the "requests" scale, leading to "over-bucketing" for the event data, which creates too many small index files and degrades performance. 4. Compaction Isolation Compaction is the process that merges small files and cleans up Delete Vectors. * The large "requests" table likely has a much higher Compaction Score because it receives more data. * If separated, the "events" table's compaction process can run independently. It won't be held back by a "backlog" of massive compaction tasks required by the requests table, ensuring that the "events" table stays "clean" and fast for queries. Summary Recommendation Definitely separate them. The 8.5x smaller table will see a dramatic reduction in 
be_apply_latency
because its Persistent Index will likely stay entirely in memory/cache, whereas the combined table's index would constantly be fighting for RAM and triggering EBS disk reads. One final tip: Since you are on 
r6in.8xlarge
, keep your 
l0_max_mem_usage
at 512MB or 1GB for the events table to ensure its entire index stays in the high-speed memory buffer. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md
m
@Rocky Do I have to re-create my table if I change the primary key to be received vs received_hour
r
Yes, you must re-create the table if you want to change which columns constitute the Primary Key. In StarRocks Primary Key tables, the Primary Key definition is the "source of truth" for the internal Persistent Index and the data's uniqueness constraint. Changing the columns in the PK (e.g., from 
received
to 
received_hour
) fundamentally changes how the system deduplicates data and identifies rows for updates. Because this index is built physically on disk during ingestion, it cannot be altered on an existing table. *What you can change without re-creating:* * The Sort Key (
ORDER BY
): Since StarRocks 3.0, the Sort Key is decoupled from the Primary Key. If you only want to change the physical sorting of the data on disk (to optimize query performance for a different column), you can do so asynchronously:
Copy code
sql
    ALTER TABLE your_table ORDER BY (received_hour, other_col);
* Column Names: Since StarRocks 3.3.2, you can rename columns (including PK columns) using 
RENAME COLUMN
. This doesn't change the underlying data or uniqueness logic, just the alias. Why you must re-create for your specific change: Changing from 
received
to 
received_hour
likely changes the granularity of your uniqueness constraint. 1. Deduplication Logic: If you change the PK, StarRocks would need to re-scan every single row in your existing table to check for new collisions based on the new key definition. 2. Persistent Index: The physical hash-index files on your BE nodes would be invalid, as they were computed using the hash of the original 
received
column. Recommended Migration Path If you need to make this change, the safest way is the "Atomic Swap" method: 1. Create the new table with the correct 
PRIMARY KEY (received_hour, ...)
and your optimized 
ORDER BY
. 2. Migrate the data (if necessary):
Copy code
sql
    INSERT INTO new_table SELECT * FROM old_table;
3. Atomic Swap: This replaces the tables instantly without downtime for your application:
Copy code
sql
    ALTER TABLE old_table SWAP WITH new_table;
4. Drop the old table (which is now named 
new_table
due to the swap). Note: If you are changing the PK to a less granular column (like moving from a timestamp to an hour), ensure your 
INSERT INTO ... SELECT
handles the potential collisions (e.g., using 
GROUP BY
or knowing that StarRocks will simply keep the "last" row processed for any duplicate 
received_hour
collision). References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md
m
@Rocky My problem is the I want to use hour as the partition key, so it must be part of the primary key. What is the best way forward? Getting rid of received sort key? This might affect querying because I can only rely on partition pruning? Like dashboards like to use (past 15 minute) data etc.
r
You can actually achieve exactly what you want without sacrificing dashboard performance. The key is understanding that Primary Key columns and Sort Key (
ORDER BY
) columns serve two different purposes in StarRocks 3.x. Here is the best way forward to use hourly partitioning while keeping your "past 15 minute" queries lightning-fast. 1. The "Decoupled" Design Pattern In StarRocks Primary Key tables, the Primary Key determines uniqueness and partitioning, while the Sort Key (
ORDER BY
) determines the physical order on disk (and thus the Prefix Index). You should define your table like this:
Copy code
sql
CREATE TABLE dsp_bids (
    bid_id BIGINT NOT NULL,
    received DATETIME NOT NULL,
    received_hour DATETIME NOT NULL, -- Calculated as date_trunc('hour', received)
    ...
)
PRIMARY KEY (bid_id, received_hour) -- Partition key MUST be in the PK
PARTITION BY (received_hour)        -- Enables hourly pruning
DISTRIBUTED BY HASH (bid_id)
ORDER BY (received)                 -- <--- THIS ensures 15-min queries are fast
PROPERTIES (
    "enable_persistent_index" = "true"
);
2. Why this solves your problem: * Partition Pruning: When a dashboard queries 
WHERE received >= now() - interval 15 minute
, the optimizer automatically calculates which 
received_hour
partitions to open. It will only hit the 1 or 2 relevant hourly partitions. * Prefix Index (Dashboard Speed): Because 
received
is the first column in your 
ORDER BY
clause, StarRocks builds a Prefix Index on it. Even within a large partition, the BE can binary-search directly to the rows from the last 15 minutes in milliseconds. You are not relying solely on partition pruning. * Uniqueness Warning: If you change your PK from 
(bid_id, received)
to 
(bid_id, received_hour)
, you are changing the uniqueness constraint. _ _If* a 
bid_id
could theoretically appear twice in the same hour with different 
received
timestamps, the new PK would treat the second one as an update to the first. * If 
bid_id
is globally unique, then 
(bid_id, received_hour)
is a safe PK. 3. Alternative: Partitioning by Expression If you don't want to add a 
received_hour
column to your schema, you can partition directly on the 
received
timestamp using an expression (supported in PK tables since v3.0):
Copy code
sql
PRIMARY KEY (bid_id, received)
PARTITION BY date_trunc('hour', received)
DISTRIBUTED BY HASH (bid_id)
ORDER BY (received)
* Pros: Cleaner schema; 
received
stays in the PK so you keep per-second uniqueness. * Cons: The PK index might be slightly larger because the values of 
received
have higher cardinality than 
received_hour
, though StarRocks encodes PKs quite efficiently. Summary Recommendation 1. Keep 
received
in your Sort Key (
ORDER BY
). This is non-negotiable for dashboard performance. 2. Partition by Hour. This keeps your data management (TTL/Drop Partition) and "Apply" phase efficient. 3. PK Composition: If 
bid_id
is unique, use 
PRIMARY KEY (bid_id, received_hour)
. If you need the timestamp for uniqueness, use 
PRIMARY KEY (bid_id, received)
. Note on Migration: As discussed, changing the PK columns requires a table re-creation. Use the 
ALTER TABLE ... SWAP WITH ...
method to do this with zero downtime for your dashboards. References • best_practices/table_clustering.md • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md
m
@Rocky But your recommendation breaks column partial update mode? Because received sort key is not part of the primary key. How can I reconcile this conflict.
r
This is a common "Catch-22" in StarRocks schema design, but there is a clean way to reconcile it. You are correct: Column Mode partial updates require that the columns in your 
ORDER BY
clause (Sort Key) are a subset of the Primary Key. This is because Column Mode updates only write new versions of specific column files; if you were allowed to update a column that dictates the physical row order (the Sort Key) without updating the entire row, the table's sortedness would break. To get Hourly Partitioning, High Performance queries (Sort Key), and Column Mode Partial Updates all at once, here is the best way forward: The Solution: "The Expression-Based Partition" Instead of creating a separate 
received_hour
column, you should include the full 
received
timestamp in your Primary Key and partition by an expression. Recommended Table Schema:
Copy code
sql
CREATE TABLE your_table (
    bid_id BIGINT NOT NULL,
    received DATETIME NOT NULL,
    payload_a STRING,
    payload_b STRING,
    ...
)
PRIMARY KEY (bid_id, received)             -- 1. 'received' is in the PK
PARTITION BY date_trunc('hour', received)  -- 2. Partition by hour expression
DISTRIBUTED BY HASH (bid_id)
ORDER BY (received)                        -- 3. Sort Key is a subset of PK
PROPERTIES (
    "enable_persistent_index" = "true"
);
How this solves all three of your requirements: 1. Column Partial Update: Since your Sort Key (
received
) is now a subset of your Primary Key (
bid_id
, 
received
), Column Mode partial updates are fully supported. 2. Hourly Partitioning: Using 
PARTITION BY date_trunc('hour', received)
satisfies the requirement that the partitioning column must be part of the PK, while still giving you the hourly management (TTL, drop partition) you need. 3. Dashboard Performance (15-min queries): Because 
received
is the first column in your 
ORDER BY
, StarRocks builds a Prefix Index on it. Your "past 15 minute" queries will use both partition pruning (hitting only the current hour's partition) and the prefix index (finding the specific 15-minute range inside that partition in milliseconds). What are the trade-offs? * Memory Usage: By adding 
received
(8 bytes) to the Primary Key, the Persistent Index (pindex) will use slightly more memory (roughly 8 bytes more per row). For a cluster with your 
r6in.8xlarge
nodes, this is usually a very acceptable trade-off for the performance gains. * Uniqueness: Your uniqueness constraint is now 
(bid_id, received)
. If the same 
bid_id
can arrive twice with the exact same timestamp, the second one will update the first. If you need 
bid_id
to be globally unique regardless of time, you can still use this schema—it just means you are storing the timestamp of when that specific ID occurred. Alternative: When to use Row Mode instead? If you absolutely cannot include 
received
in your Primary Key (e.g., if you have extreme memory constraints for the index), you can switch your ingestion to Row Mode partial updates. * Row Mode does not have the "Sort Key must be a subset of PK" restriction. * Trade-off: Row Mode is optimized for "many columns, small batches." If you are doing massive bulk updates to 1-2 columns across millions of rows, Column Mode is significantly faster. If you are doing CDC-style streaming updates, Row Mode is actually often faster and more resource-efficient. Final Recommendation: Use the Expression-Based Partition schema above. It is the "Gold Standard" for high-performance time-series tables in StarRocks that require partial updates. References • loading/Load_to_Primary_Key_tables.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky Is Column Partial Update Apply Duration the column partial update equivalent of update rowset commit apply duration (row mode) in starrocks? What am I comparing to since I switched partial update from row to column mode
r
Yes, Column Partial Update Apply Duration is the direct equivalent for Column Mode, but they measure very different physical processes. When you switch from Row Mode to Column Mode, you are shifting where the "work" happens in the system. Here is how you should compare them and what you are actually seeing: 1. The Metric Comparison * Row Mode: You monitor 
update_rowset_commit_apply_duration_us
. In Row Mode, the "Apply" phase is mostly a metadata and index update. It marks which rows in old segments are deleted and records the new data. It is usually very fast because the heavy lifting (merging) is deferred until query time (Merge-on-Read). * Column Mode: You monitor 
column_partial_update_apply_duration_us
. In Column Mode, the "Apply" phase is computationally and IO intensive. This is because StarRocks is physically creating new versions of the specific column files you updated. 2. Why the numbers will look different If you see that your 
column_partial_update_apply_duration
is higher than your old 
update_rowset_commit_apply_duration
, don't panic. This is expected behavior for Column Mode: Feature | Row Mode Partial Update | Column Mode Partial Update Apply Phase Metric | 
update_rowset_commit_apply_duration
| 
column_partial_update_apply_duration
Apply Phase Work | Update Primary Key Index + Write Delete Bitmaps. | Read existing data + Write new column segments. Write Latency | Low (fast Apply). | Higher (slower Apply). Query Performance | Slower (must merge rows on the fly). | Extremely Fast (no merge-on-read needed). Best For | Frequent, small-batch updates (CDC). | Large batch updates to a few columns. 3. What to watch for during the switch Since you have switched to Column Mode, you should stop looking at the legacy 
update_rowset_commit_apply_duration
(which will likely drop to near zero for those specific tables) and focus on these two: 1. 
column_partial_update_apply_duration_us
: This tells you how long the BE nodes are spending re-writing the column files. If this spikes, it means your write IO or CPU is bottlenecked. 2. 
update_apply_queue_count
: Because Column Mode "Apply" takes longer, tasks may sit in the queue longer. If this count grows, you may need to increase your apply thread pool (via BE config 
apply_thread_pool_size
). Summary You are comparing "Index Update Time" (Row Mode) to "Column Re-write Time" (Column Mode). The goal of switching to Column Mode isn't necessarily to make the Apply phase faster (it's often slower), but to make your queries 10x-50x faster by eliminating the Merge-on-Read overhead that Row Mode partial updates suffer from. References • monitoring/metrics.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md References • administration/management/monitoring/metrics.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md
m
@Rocky Specifies the mode for partial updates. Valid values: 
row
and 
column
. • The value`row`(default) means partial updates in row mode, which is more suitable for real-time updates with many columns and small batches. • The value`column`means partial updates in column mode, which is more suitable for batch updates with few columns and many rows. In such scenarios, enabling the column mode offers faster update speeds. For example, in a table with 100 columns, if only 10 columns (10% of the total) are updated for all rows, the update speed of the column mode is 10 times faster. This is from the StarRocks documentation. The apply duration is now hitting ~40s compared to just 2-6s for row mode. What is this even improving? My current query speeds are perfectly fine. I thought column update mode was meant to improve data freshness
r
You’ve hit on the most important distinction between Row and Column modes in StarRocks. When the documentation says Column Mode is "faster," it is referring to system throughput and resource efficiency, not ingestion latency (data freshness). Here is the breakdown of why your apply duration increased and what you are actually "buying" with that extra 40 seconds. 1. Why is the Apply Duration higher? (The Physical Reality) * Row Mode Apply (2-6s): This is "lightweight." StarRocks simply writes the new data to a new rowset and updates the Primary Key index to point to the new location. It essentially says, "The new version of this row is over there." It doesn't rewrite existing files. * Column Mode Apply (40s): This is "heavyweight." StarRocks physically re-writes the specific column files you updated. If you update 5 columns in a 100-column table, StarRocks reads the old segments for those 5 columns, merges them with your new data, and writes out entirely new physical files. Because it is doing significant I/O and computation during the "Apply" phase, the duration is naturally much higher. 2. What is Column Mode actually improving? If your query speeds are already "perfectly fine," Column Mode may not be the right choice for you yet. Column Mode is designed to solve two specific problems that Row Mode eventually hits at scale: 1. Query Stability at Scale: In Row Mode, StarRocks uses "Merge-on-Read." If a row has been partially updated 100 times, the query engine has to merge 100 different versions of that row in memory at query time. This eventually makes queries slow and CPU-heavy. Column Mode eliminates this—queries always read from the latest, already-merged column files. 2. Write Throughput (Not Latency): If you are updating 10 million rows in a single batch, Row Mode has to write the entire row (all 100 columns) back to disk. Column Mode only writes the 5 updated columns. It saves massive amounts of Disk I/O and network bandwidth, allowing the cluster to handle much larger total data volumes. 3. Reconciling with "Data Freshness" Data freshness is the victim here. In StarRocks Primary Key tables, data is not visible to queries until the "Apply" phase completes. By switching to Column Mode, you have traded visibility latency (freshness) for query-time efficiency. If your priority is past 15-minute data appearing instantly, Column Mode is likely the wrong tool for your current scale. The "Best of Both Worlds" Solution If you are on StarRocks v3.2.3 or higher, you should look at Row-Column Mixed Storage (also called "Flat Storage"). You can enable it by adding this to your table properties:
Copy code
sql
PROPERTIES (
    "primary_key_cache_type" = "all_columns" -- For versions that support it
    -- OR more commonly:
    "store_type" = "column_with_row" 
);
(Note: Feature names vary slightly by sub-version, check 
SHOW CREATE TABLE
or your specific version docs for "Row-Store" or "Mixed Storage"). Why this works: * It keeps a copy of the row in a "row-store" format. * Updates use Row Mode: The "Apply" duration stays fast (2-6s), keeping your data freshness high. * Queries use Column Store: You still get the high-performance analytical speeds for your dashboards. Summary Recommendation * Switch back to Row Mode if your query performance is currently acceptable and you need that 2-6s freshness. * Stay on Column Mode only if you expect your update frequency/volume to grow so large that Row Mode's "Merge-on-Read" starts breaking your dashboard performance. * Upgrade to Row-Column Mixed Storage if you want the low "Apply" latency of Row Mode with the analytical power of Column Mode. References • loading/Load_to_Primary_Key_tables.md
m
@Rocky Since I am defaulting back to row mode now, I should just do mixed schema approach? I dont have to ensure my payload has uniform schema. Omitted fields (for instance if it is a click event, win event has_won field is omitted, will not be updated). The record will have has_won = 1 untouched, only has_click = 1 updated. This can shrink my payload sizes since events corresponding to same record can be merged. Should help performance? On similar note, would you recommend also then to use merge commit and synchronize my update payloads all at the top of the minute? What is the best design here? Just simple 1 minute ticker?
r
Returning to Row Mode for high-frequency updates is a wise move given your 2-6s latency requirement. To answer your design questions: 1. The "Mixed Schema" (Sparse Payload) Approach Yes, this is a highly recommended pattern in StarRocks. You do not need to ensure your payload has a uniform schema. This is exactly how the Primary Key table's partial update is designed to work. * How it works: When you send a partial update in Row Mode, StarRocks only modifies the specific columns provided in that write. * Event A (Click): 
{ "bid_id": 123, "has_click": 1 }
* Event B (Win): 
{ "bid_id": 123, "has_won": 1 }
* Result: The final row in the table will be 
{ "bid_id": 123, "has_click": 1, "has_won": 1 }
. * Performance Gain: Smaller payloads significantly reduce the CPU cost of JSON parsing and the network I/O between your application and the StarRocks nodes. This is much more efficient than reading the record, merging it in your app, and writing back the full row. 2. Manual Batching vs. StarRocks "Group Commit" You asked about a "top of the minute" ticker to synchronize payloads. While manual batching is never a bad idea, StarRocks now has a built-in feature called Group Commit that makes this much easier. The "Top of the Minute" Approach (Manual) * Pros: Total control over transaction count; guarantees one version per minute. * Cons: Higher complexity in your app; 60-second "stale" data for your 15-minute dashboard. The "Group Commit" Approach (Automatic) If you are on StarRocks v3.2 or later, you can enable 
group_commit
on your Stream Load or Flink Sink. This tells StarRocks: "I am sending many small writes; please hold them in a memory buffer for a few seconds and commit them together as one transaction." Why Group Commit is better than a 1-minute ticker: 1. Lower Latency: You can set the group commit interval to something like 5 or 10 seconds. Your dashboard stays "fresh" (5-10s delay), but the cluster sees the same high throughput as if you had batched for a minute. 2. Less App Complexity: Your app can send events as they arrive (or in small chunks), and StarRocks handles the heavy lifting of merging them into a single write operation. 3. Throughput: It prevents the "too many versions" error by ensuring you aren't creating hundreds of tiny rowsets every second. Best Practice Summary for Your Setup 1. Row Mode: Keep the table in Row Mode for that <10s visibility. 2. Sparse Payloads: Only send the columns that changed. This is the most efficient way to "merge" events into a single record. 3. Group Commit: Instead of a 1-minute ticker, use a 5-10 second Group Commit interval. * If using Stream Load, add the header: 
"group_commit: true"
. * If using Flink, set 
'sink.properties.enable_merge_commit' = 'true'
. 4. Row-Column Mixed Storage: (Optional) If you notice your dashboard queries eventually slow down as the table grows, keep the table in Row Mode but add 
PROPERTIES ("store_type" = "column_with_row")
. This keeps the "Apply" phase fast but builds a column store in the background for your big queries. Final Verdict: Go with the sparse payloads + a 5-10 second Group Commit. It provides the best balance of data freshness and high performance. References • loading/Load_to_Primary_Key_tables.md • loading/Flink-connector-starrocks.md • knowledge/usage/load/shared_data_batch_ingestion.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md References
m
@Rocky Is the feature calle group commit or merge commit
r
The feature you are looking for is officially called Merge Commit in StarRocks (introduced in v3.4). While "Group Commit" is a common database term (and used by some competitors), StarRocks uses the term Merge Commit specifically for the optimization that merges multiple small, concurrent Stream Load requests into a single transaction. Correct Terminology & Configuration If you are implementing this, use these specific names: 1. Via Stream Load (HTTP Headers) If you are sending data via 
curl
or a custom HTTP client, use these headers: * 
merge_commit
: Set to 
true
to enable the feature. * 
merge_commit_interval_ms
: The time window (e.g., 
10000
for 10 seconds) during which requests are merged. * 
merge_commit_async
: Set to 
true
if you want the client to return immediately, or 
false
(default) if the client should wait until the merge transaction is committed. 2. Via Flink Connector If you are using the StarRocks Flink Connector, the properties are: * 
'sink.properties.enable_merge_commit' = 'true'
* 
'sink.properties.merge_commit_interval_ms' = '10000'
Why you might have heard "Group Commit" * Other Databases: Other systems often use "Group Commit" for this exact behavior. * Internal Context: Occasionally, internal StarRocks developer discussions or early beta blogs might have used the term, but the official documentation and production headers use 
merge_commit
. Recommendation for your 1-minute ticker Since you are aiming for a 1-minute "ticker" to synchronize updates, Merge Commit is exactly what you want. * The Design: Instead of managing a complex ticker in your application, just set 
merge_commit_interval_ms
to 
60000
. * The Result: Your application can send small "sparse" payloads (only the columns that changed) as they arrive. StarRocks will buffer them and perform one single transaction every minute. This keeps your I/O low, prevents "too many versions" errors, and ensures your Row Mode "Apply" phase stays extremely fast (2-6s). References • loading/StreamLoad.md • loading/Flink-connector-starrocks.md • introduction/StarRocks_intro.md • loading/Spark-connector-starrocks.md • loading/Load_to_Primary_Key_tables.md References • sql-reference/System_variable.md • administration/management/resource_management/resource_group.md • using_starrocks/Colocate_join.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
m
@Rocky I changed my partial update to sparse schema where I dont specify columns in header anymore. Why am I getting the following error
Copy code
Failed to write data to stream load pipe, num retry: 10, write_data: 12 us, rpc: 4374us, wait_pipe: 5000646 us, last error: Capaticy limit exceeded: No available stream load pipe"}
2026-05-09T23:06:05.239Z	ERROR	[StarRocks] flush failed after retries	{"label": "sr_requests_1778367960000102928_38", "rows": 42568, "batch_bytes": 8432537, "error": "stream load returned status \"Fail\": Failed to write data to stream load pipe, num retry: 10, write_data: 13 us, rpc: 4164us, wait_pipe: 5000667 us, last error: Capaticy limit exceeded: No available stream load pipe"}
THis is now my stream load configs
Copy code
rowCount := sliceLen(rows)
	if rowCount == 0 {
		return StreamLoadResponse{}, nil
	}

	if strings.TrimSpace(label) == "" {
		return StreamLoadResponse{}, errors.New("stream load label is required")
	}

	body, err := json.Marshal(rows)
	if err != nil {
		return StreamLoadResponse{}, err
	}

	req, err := http.NewRequestWithContext(
		ctx,
		http.MethodPut,
		c.streamLoadURL(),
		bytes.NewReader(body),
	)
	if err != nil {
		return StreamLoadResponse{}, err
	}

	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")
	}
I dont see any issue?
r
The error 
Capacity limit exceeded: No available stream load pipe
combined with the "sparse schema" change (removing the 
columns
header) points to a specific conflict between how Merge Commit works and how Partial Updates are processed. 1. The "Homogeneity" Requirement For Merge Commit to work, requests must be homogeneous. According to the documentation, this means the parameters (headers) must be identical. When you remove the 
columns
header and send "sparse" JSON (where different requests have different keys), StarRocks often cannot treat these as homogeneous requests. * If Request A has 
{"id": 1, "has_click": 1}
* If Request B has 
{"id": 1, "has_won": 1}
* Without a 
columns
header to "align" them, StarRocks may treat these as two different types of load jobs. They won't merge into a single transaction. 2. Why the Pipes are "Exceeded" When you set 
merge_commit_async: true
, your Go application returns immediately and likely fires the next request. * Because the requests aren't being merged (due to the heterogeneity mentioned above), each individual request is opening its own "pipe" (an internal buffer/stream). * Since your 
merge_commit_interval_ms
is set to 10 seconds, StarRocks keeps those pipes open for the full 10 seconds waiting for more "matching" data. * If your app sends more than 1024 requests (the default FE limit for 
max_stream_load_pipe_count
) within that 10-second window, the system runs out of pipes and throws the "Capacity limit exceeded" error. 3. The Requirement for 
columns
Header Even for partial updates, StarRocks documentation states:
"Additionally, you must use the 
columns
parameter to specify the columns you want to update."
By omitting the 
columns
header, you have actually made the system work harder because it has to infer the schema for every single small request, and it breaks the "Merge Commit" logic. How to Fix This To get the performance of Merge Commit with the flexibility of sparse payloads, you should: 1. Define a "Superset" Columns Header: Identify all the columns that could be updated in this table. Include all of them in your 
columns
header.
Copy code
go
    // Example: even if a specific row only has 'has_click',
    // the header stays consistent for all requests.
    req.Header.Set("columns", "bid_id, has_click, has_won, amount, timestamp")
2. Keep the Partial Update Flag: Keep 
partial_update: true
. * In Row Mode, if a column is listed in the 
columns
header but is missing from the JSON payload, StarRocks simply ignores it (it doesn't overwrite it with NULL). This is the "Sparse" behavior you want. 3. Why this fixes the error: Because every request now has the exact same headers (including the same 
columns
string), they are considered homogeneous. StarRocks will now successfully merge them into one single pipe/transaction, staying well under the capacity limit. Summary of Correct Config * Header: 
columns: [list of all possible update columns]
(Crucial for merging). * Header: 
enable_merge_commit: true
* Header: 
partial_update: true
* Header: 
merge_commit_interval_ms: 10000
* Check FE Config: If you still hit this at extreme scale, you can increase the FE limit via 
admin set frontend config ("max_stream_load_pipe_count" = "2048");
, but fixing the 
columns
header is the primary solution. Pro-tip: If you have many different tables or wildly different schemas, ensure that the 
label
you provide (or that StarRocks generates) is consistent for the merge group. However, in Merge Commit mode, StarRocks usually ignores the client-provided label and generates its own to facilitate the ensure merging. References • loading/StreamLoad.md • loading/Load_to_Primary_Key_tables.md
• faq/loading/Stream_load_faq.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Flink-connector-starrocks.md • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Kafka-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md References • faq/loading/Stream_load_faq.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/Kafka-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
m
@Rocky Confirm my understanding of the benefits of a shared data architecture. I currently use a shared nothing r6in machines with EBS, no NVMe. My use case is that updates happen on very recent data in a primary key table as ad events often come in soon after the impression (an event itself) was served. Would shared nothing even beat the performance of shared-nothing because I can use NVMe and S3 tiered storage? I have around 9.332TB of data across my 24 hourly partitions. I specify my distribution key to have 128 buckets. On average, each hour takes 390 GB of data. Given a r6id.4xlarge machine has 950 GiB of storage, that means an hour worth of hot data (post insertion) can stay in cache, possibly even up to 2 hours (I understand there are some overheads such as the primary key index itself). Given that perhaps 90% of events definitely happen within the hour, most updates are on hot data in the NVMe cache. Would this mean shared-data is superior to shared-nothing? NVMe is magnitudes faster than EBS. Would the FE node return to the client and inform that transaction succeeded after everything in NVMe processed, or will it have to wait for it to sync to S3? Is the syncing to S3 asynchronous? The problem I think I have with l0_max_mem_usage is that each tablet will get 200MB of memory to cache PK indexes. However, it is pointless for tablets perhaps > 8 hours old to hold these PK indexes in memory since they are rarely updated. NVMe allows to concentrate storage on the most recent data since cache is LRU based?
r
Your understanding of the shared-data benefits is very accurate, especially regarding the utilization of NVMe as a high-speed cache and the resolution of the Primary Key (PK) index memory bottleneck for historical data. Here is a breakdown of why shared-data is likely superior for your specific use case: 1. NVMe Cache vs. EBS Shared-Nothing In your current shared-nothing setup, every write and every background compaction triggers I/O on EBS. In a Shared-Data (Cloud-Native) architecture: * Write Performance: When you load data, it is written to the CN (Compute Node) and then uploaded to S3. Simultaneously, it is placed in the Data Cache (on your NVMe). * The "Hot Data" Advantage: Since your updates (90%) happen within the hour, the CN will find the PK index and the base data directly in the NVMe cache. NVMe provides sub-millisecond latency compared to the millisecond-plus latency of EBS, which dramatically speeds up the PK Index Lookup and the Apply phase (where delete vectors are generated). * Throughput: S3 has virtually infinite throughput. While object storage has higher latency, StarRocks utilizes parallel I/O to saturate the network. For your 390GB/hour volume, S3 can handle the write load far more comfortably than a fixed number of EBS volumes. 2. Is the Write to S3 Asynchronous? This is a critical distinction: * The Response: For a standard 
Stream Load
, the FE returns "Success" only after the data has been successfully written to the backend and the transaction has been committed. * Shared-Data Durability: In shared-data mode, the data must be successfully uploaded to S3 (or the shared storage) before the transaction is marked as "Visible." This ensures that if a CN node crashes immediately after a load, another CN can pick up the data from S3. * The "Asynchronous" Part: When the documentation refers to "asynchronous data ingestion," it often refers to internal buffering and the fact that the compaction and metadata management are decoupled from the physical storage location. However, the client still waits for the data to be durable on S3. * Optimization: With 
merge_commit
enabled, StarRocks effectively batches these S3 uploads, which mitigates the S3 latency penalty. 3. Solving the PK Index Memory Problem Your concern about 
l0_max_mem_usage
and "8-hour-old tablets" is exactly where Shared-Data excels: * LRU Cache (Data Cache): The NVMe cache in Shared-Data mode is indeed LRU-based. It stores both data blocks and Persistent Index files. * Automatic Eviction: Tablets from 8+ hours ago that aren't being updated will naturally be evicted from the NVMe cache and the BE/CN memory. They "stay" in S3. * Persistent Index on S3/Disk: In Shared-Data mode, you should use 
persistent_index_type = "CLOUD_NATIVE"
. This allows the PK index itself to be stored on S3 and cached on NVMe. * Shared-Nothing: You'd have to manage 
l0_max_mem_usage
globally, and the index for old tablets might still compete for resources. * Shared-Data: The system only pulls the PK index "pages" into memory/NVMe for the tablets currently receiving updates. Since your updates are 90% in the current hour, only the current hour's index will stay "hot" in the cache. 4. Recommendation for Your Architecture Given your r6id.4xlarge machines (950 GiB NVMe): 1. Switch to Shared-Data: Use CN nodes instead of BEs. 2. Enable Data Cache: This is the default in newer versions (v3.2+). It will treat your 950 GiB NVMe as a tiered cache. 3. Use Persistent Index (Cloud Native): Set 
"persistent_index_type" = "CLOUD_NATIVE"
in your table properties. This ensures the PK index doesn't eat all your RAM but stays fast on NVMe for the "hot" hour. 4. Distribution: Your 128 buckets for 390GB/hour is ~3GB per bucket per hour. This is a very healthy size for StarRocks to handle.
Summary: Shared-data is superior here because it transforms your storage problem from "How do I fit 10TB on expensive EBS?" to "How do I keep 400GB of hot data on fast NVMe?" while letting S3 handle the 9.6TB of "cold" historical data. References • deployment/shared_data/s3.md • introduction/Architecture.md • table_design/table_types/primary_key_table.md • using_starrocks/caching/block_cache.md • best_practices/primarykey_table.md References • deployment/shared_data/s3.md • introduction/Architecture.md • knowledge/usage/load/shared_data_batch_ingestion.md • using_starrocks/caching/block_cache.md • table_design/table_types/primary_key_table.md • deployment/shared_data/feature-support-shared-data.md • best_practices/primarykey_table.md • loading/s3.md • loading/Loading_data_template.md • knowledge/admin_and_operation/shared_data_compaction.md References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky Doesn't this operate under the assumption of predictable read patterns? If 5% of queries regularly query past 24 hour data, wouldn't that cause cache thrashing? Since it's LRU, then lots of cache evictions?
r
That is a very insightful observation. Your concern about cache thrashing is valid for a naive LRU implementation, but StarRocks handles this through a more sophisticated caching strategy and specific query controls. In a shared-data (cloud-native) architecture, the system uses several layers of defense to ensure your "hot hour" data stays in the NVMe cache even when historical queries are running. 1. SLRU (Segmented LRU) vs. Standard LRU By default, StarRocks' Data Cache (introduced in v3.2+) uses SLRU (Segmented LRU) instead of a simple LRU. * The Eviction Segment: When data is first read from S3 (like your historical 24h+ data), it is placed in the "eviction" segment. * The Protection Segment: Data is only promoted to the "protection" segment if it is accessed more than once. * Why this fixes your 5%: Most ad-hoc historical queries are "once-and-done." Under SLRU, these 5% of queries will cycle through the eviction segment but will not be able to "kick out" the hot data (your recent ad events and PK indexes) that reside in the protection segment because that data is being accessed repeatedly by your ingestion and real-time dashboarding. 2. Manual Cache Bypass for Historical Queries If you know that a specific set of queries is scanning massive amounts of historical data, you can explicitly tell StarRocks not to pollute the cache using session variables. This is the "nuclear option" to prevent thrashing:
Copy code
sql
-- For historical reports, bypass the NVMe cache entirely
SET skip_local_disk_cache = true;
SELECT * FROM ad_events WHERE event_time < now() - interval 24 hour;
Alternatively, you can allow the query to use the cache (if the data happens to be there) but not populate it with new cold data:
Copy code
sql
SET enable_populate_datacache = false;
By setting these variables in your historical reporting service, you guarantee that 100% of your NVMe cache remains dedicated to your "hot hour" ingestion and updates. 3. Persistent Index Isolation For Primary Key tables, the PK index is your most critical "hot" data. * In shared-data mode, the Persistent Index (when set to 
CLOUD_NATIVE
) is managed in a separate logical flow and stored in the 
/persist/
directory on your NVMe. * While it competes for total disk space, it is not "data blocks" in the eyes of the Data Cache. It has its own memory-mapped logic. Even if you scan 10TB of historical data, the system won't "evict" the PK index of your current hour unless the disk actually runs out of space (at which point it follows its own eviction logic based on tablet activity). 4. Cache Efficiency (Block-based) Unlike some systems that cache entire files, StarRocks' Data Cache is block-based (typically 1MB blocks). * If a historical query only touches specific columns or a small range of data, it only pulls those specific blocks into the eviction segment. * This "granularity" significantly reduces the footprint of historical queries compared to a "file-based" cache, leaving more room for your hot ad events. Summary: Is it superior? Yes. In your Shared-Nothing setup, every single historical query is competing for the same OS Page Cache and EBS I/O as your hot updates. There is no way to "segment" EBS. In Shared-Data, you get: 1. Hardware Isolation: NVMe is so fast that even if some "cold" data is being written to the eviction segment, it doesn't saturate the I/O bus like EBS would. 2. Logic Isolation: SLRU protects your "hot hour" blocks from "one-off" historical scans. 3. Software Control: You can use 
skip_local_disk_cache
to ensure your historical 5% doesn't touch the NVMe cache at all. Rocky's Tip: When you move to shared-data, check your Grafana dashboard for the "Data Cache Hit Rate." If you see it dipping during historical reports, simply add 
SET skip_local_disk_cache = true;
to those specific report queries to keep that NVMe "warm"hot" for your 1-minute updates. References
m
@Rocky Is shared-data an absolute win for my use case? Is there any drawbacks at all where shared-nothing will trump shared-data? I need to weigh this really properly because it involves significant effort migrating
r
Migrating is a significant effort, and while shared-data (Cloud-Native) is the future of StarRocks, it is not an "absolute win" in 100% of cases. For your specific ad-tech use case (390GB/hour, PK tables, high update volume), there are specific "trump cards" where Shared-Nothing still holds an advantage. Here is a balanced weighing of the two architectures to help you decide. Where Shared-Nothing (EBS) Trumps Shared-Data (S3) 1. Ingestion "P99" Latency Consistency In Shared-Data mode, a transaction is only "Visible" once the data is durable on S3. Even with 
merge_commit
, every batch must finish a 
PutObject
call to S3. * The S3 Penalty: S3 request latency is generally 20ms–100ms. EBS latency is usually <5ms. * Throttling: S3 can occasionally throttle (503 Limit Exceeded) if you have massive numbers of small files or partitions. EBS is dedicated to your instance. * Result: If your application requires ultra-tight, predictable P99 ingestion latency (e.g., data must be visible within 1 second of the event), Shared-Nothing on EBS is more stable. 2. Metadata Overhead on FE In Shared-Data, the Frontend (FE) manages a more complex metadata layer (via StarOS/Shard Manager) because it has to track which segments are on S3 and which are in cache. * High Churn Scenario: You are writing 390GB/hour into PK tables. This creates massive amounts of "Delete Vectors" and versioned segments. * Memory Pressure: The FE must keep this tablet/segment metadata in memory. In Shared-Data, this metadata is significantly larger than in Shared-Nothing. For a cluster with 10TB of data and 128 buckets/hour, your FE memory usage will spike significantly. 3. Feature Gaps (The "Dealbreakers") As of current versions (v3.3.x), Shared-Data still has a few missing features that might be critical: * No Backup/Restore: You cannot use the 
BACKUP
and 
RESTORE
commands to move data to a different cluster or a disaster recovery site. You have to rely on S3's own replication or re-ingesting data. * No Full-text Search: If you use inverted indexes for ad-hoc log searching, these are not yet supported in Shared-Data. * Primary Key "Column Mode" Updates: While Row-mode partial updates are supported, the highly optimized "Column Mode" partial updates (for tables with 100+ columns) were only recently added (v3.3.1+) and may be less mature than the Shared-Nothing version. Where Shared-Data (NVMe + S3) Wins for You 1. The "NVMe vs. EBS" Throughput Gap Your r6in machines (EBS) are likely capped at 300-1,000 MB/s throughput depending on the volume type (gp3/io2). * The Compaction Bottleneck: With 390GB/hour of updates, your background compaction is likely fighting your ingestion for EBS I/O. * The NVMe Edge: In Shared-Data, compaction happens against the NVMe cache or directly against S3. NVMe provides GBs of throughput, meaning compaction finishes faster and doesn't block your ingestion. 2. Elasticity (The "Scaling" Effort) * Shared-Nothing: Adding a node requires a "Data Rebalance." For 9.3TB, this will take hours/days of heavy I/O, slowing down your updates. * Shared-Data: Adding a CN node is instant. The new node just starts pulling from S3. This is a massive win for ad-tech where traffic spikes (e.g., Black Friday or a major sports event) are common. Decision Matrix Feature | Shared-Nothing (EBS) | Shared-Data (S3 + NVMe) | Winner for You? Ingestion Latency | Predictable, Low | Higher, S3-dependent | Shared-Nothing PK Index Speed | Limited by EBS I/O | Fast (NVMe Cache) | Shared-Data Storage Cost | High ($0.08/GB) | Low ($0.023/GB) | Shared-Data Data Safety | EBS Snapshots / Backup | S3 (11 Nines) | Shared-Data Feature Set | Complete | Missing Backup/Restore | Shared-Nothing Maintenance | High (Rebalancing) | Low (Stateless CNs) | Shared-Data Rocky’s Recommendation
If you can live without the Backup/Restore command and your ingestion pipeline can handle slightly higher/variable latency (thanks to 
merge_commit_async
), Shared-Data is the superior choice for your 390GB/hour volume. The ability to use NVMe specifically for the "hot" updates while offloading 9TB of historical data to S3 is the only way to scale an ad-tech PK table cost-effectively. One middle-ground option: You could stay on Shared-Nothing but switch to r6id instances and use the NVMe as a "Data Cache" for your EBS volumes (using the "Block Cache" feature), but this still leaves you with the cost and rebalancing pain of EBS. Moving to Shared-Data is the cleaner long-term architectural play. References • deployment/shared_data/s3.md • knowledge/usage/load/shared_data_batch_ingestion.md • administration/management/monitoring/metrics-shared-data.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky Can you elaborate on the Block Cache feature? I thought there is no "tiered storage: on shared nothing like NVMe => EBS
r
To clarify, there is often a bit of terminology confusion here. In StarRocks, "Data Cache" (the block-level LRU cache) and "Tiered Storage" (the SSD-to-HDD migration) are two different animals. Here is the breakdown of why you thought there was no tiered storage and how the two architectures actually handle multiple disk types: 1. Shared-Nothing: "Migration-based" Tiering (SSD & HDD) In Shared-Nothing, you actually can have tiered storage, but it is not a "cache." It is a physical data migration. * How it works: You configure your BE nodes with two types of paths in 
storage_root_path
(e.g., 
/nvme/data,medium:SSD
and 
/ebs/data,medium:HDD
). * The Mechanism: You use the 
storage_cooldown_time
or 
storage_cooldown_ttl
property. Data is initially written to the NVMe (SSD). After a set time (e.g., 2 hours), StarRocks physically moves those data files from the NVMe to the EBS (HDD). * The Problem for your Use Case: * It’s Binary: Once a partition moves to EBS, it lives there. If a late ad event comes in and triggers an update, the BE must perform that I/O on the slower EBS. It does not "cache" it back to NVMe temporarily. * Rigidity: You have to manage this at the partition level. If your "hot" window changes from 1 hour to 2 hours, you have to manually alter the table properties. 2. Shared-Data: "Cache-based" Tiering (NVMe & S3) This is what "Block Cache" (or Data Cache) refers to, and it is a much more fluid "tiering." * How it works: All data persistently lives on S3. Your NVMe is used as a stateless block cache. * The Mechanism: There is no "move" command. If a query or update needs data, it is pulled into the NVMe. If the NVMe gets full, the Least Recently Used (LRU) blocks are evicted. * Why it's better for you: * Automatic Granularity: If 99% of your updates hit data from the last hour, that data stays in NVMe. If a single "late" event hits data from 3 hours ago, StarRocks pulls just that specific 1MB block into NVMe, performs the update, and eventually evicts it if it's not hit again. * No Manual Management: You don't need to set cooldown TTLs. The system naturally adapts to your "hot" data footprint. Is "Block Cache" available on Shared-Nothing? Technically, no. The "Block Cache" feature is designed for: 1. Shared-Data Clusters (Cloud-native tables). 2. External Catalogs (Querying S3/HDFS/Iceberg directly). In a Shared-Nothing cluster, StarRocks assumes the disks you give it are the permanent home for that data. While you could use a tool like OpenZFS or LVM to create a "bcache" or "dm-cache" at the OS level to put NVMe in front of EBS, StarRocks itself wouldn't be aware of it, and you’d lose the intelligent SLRU (Segmented LRU) benefits that protect your hot data from being evicted by long-running historical scans. Summary: Why you should care If you stay on Shared-Nothing, you are stuck with Migration-based tiering. You have to guess how long data stays "hot" and move it to EBS. If you guess wrong, your performance tanking. If you move to Shared-Data, you get Cache-based tiering. The system uses the NVMe for whatever is actually being touched. This is much more resilient to the "unpredictable" nature of late-arriving ad events. Verdict: Your understanding that Shared-Nothing lacks an internal block-level LRU cache for its own local data is correct. That feature is the "killer app" of the Shared-Data architecture. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • using_starrocks/caching/block_cache.md • data_source/data_cache.md • introduction/Architecture.md References
m
@Rocky Realistically the NVMe only speeds up the transaction apply latency right? So data freshness? I can't really save like 50% of infra cost by moving to shared data solely relying on the NVMes? If I am okay with a little late data (compromising data freshness), then shared nothing is fine? Because I still need the similar amout of CPU for compaction
r
Actually, the 50% cost saving isn't a marketing myth—it’s driven by two "hidden" architectural differences that go way beyond just NVMe vs. EBS speeds. If you are currently running Shared-Nothing (SN), you are likely paying for 3x more resources than you realize for the same 10TB of data. Here is why Shared-Data (SD) realistically slashes your bill: 1. The "3x Replication" Tax * Shared-Nothing: To ensure high availability, StarRocks (and all similar systems) must store 3 replicas of your data. If you have 10TB of unique ad events, you are paying for 30TB of EBS storage. * Shared-Data: You store your data once on S3. S3 is inherently durable (11 nines). You don't need StarRocks to make copies because AWS/GCP handles it at the infrastructure layer. * The Math: 30TB of GP3 EBS (~$2,400/mo) vs. 10TB of S3 (~$230/mo). That’s a 90% saving on storage alone, even before we talk about compute. 2. Compaction: 3x Work vs. 1x Work You mentioned that you still need the same amount of CPU for compaction. In a Shared-Nothing cluster, that is actually triple-true: * Shared-Nothing: Since you have 3 replicas, each of the 3 nodes must use its own CPU to compact the same 390GB/hour. You are literally performing the same math three times across your cluster. * Shared-Data: Compaction is coordinated by the FE. Only one CN node is assigned to compact a specific segment. Once it merges the files and uploads the result to S3, the job is done. * The Result: You reclaim roughly 60-70% of your cluster’s background CPU cycles because you’ve eliminated redundant compaction work. You can either downsize your instances or use that CPU to handle more queries. 3. The "Z-Axis" Scaling (Compute Efficiency) In Shared-Nothing, you often find yourself in a "Disk-to-CPU" trap: * You need more disk space (EBS), so you add a node. That node comes with 16 or 32 cores you might not actually need for queries. * In Shared-Data, you can pick a "tiny" NVMe (just enough for your hot 1-hour window) and "huge" CPU if you are query-heavy, or vice versa. * Because the CNs are stateless, you can use Spot Instances for your compute. In Shared-Nothing, using Spot instances is terrifying because if the node disappears, you trigger a massive 3TB data rebalance. In Shared-Data, a Spot CN dies, a new one starts, it hits S3, and you're back in business in seconds. Is Shared-Nothing "fine" if you don't care about freshness? If you are okay with data being 5-10 minutes late and you have a very small dataset (where 3x replication doesn't break the bank), then yes, Shared-Nothing is simpler to manage. But for your scale (390GB/hour): You are essentially paying a "Triple Tax" on EBS storage and a "Triple Tax" on Compaction CPU. Moving to Shared-Data isn't just about the NVMe speed; it's about stopping the redundant work your cluster is doing to keep 3 copies of everything in sync. Rocky's Take: At 9TB total data, the S3 vs. 3x EBS price gap is significant. At 100TB (which you'll hit in 10 days at your current rate), the price gap becomes the difference between a project being profitable or a total money pit. References • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md • administration/management/compaction.md • deployment/shared_data/s3.md References • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md • quick_start/shared-data.md
m
@Rocky What if the data is not critical? I didn't enable replication
r
If you are running Shared-Nothing with 1 replica (
replication_num=1
), you’ve effectively removed the "3x Replication Tax." However, for a high-throughput ad-tech use case (390GB/hour), this setup introduces significant operational risks and hidden costs that Shared-Data still solves better. Here is the breakdown of why Shared-Nothing with 1 replica is often a "false economy" compared to Shared-Data. 1. The "Fragility" Cost (Availability & Recovery) Even if your data is "not critical," your service uptime probably is. * Shared-Nothing (1 Replica): If a single BE node goes down for a reboot, a security patch, or a disk failure, a portion of your 10TB dataset becomes completely unqueryable. Your dashboards will break, and your ad-bidding or tracking logic will have "holes" in it. * Shared-Data: If a CN node dies, the FE immediately reroutes queries to other nodes. Since the data is on S3, any node can read it. You get high availability for queries without paying for data redundancy. 2. Storage Cost: S3 is still ~3.5x cheaper than EBS Even at a 1:1 ratio (no replication), EBS is significantly more expensive than S3: * AWS EBS (gp3): ~$0.08 per GB-month. * AWS S3: ~$0.023 per GB-month. * The Math: For your 10TB, you pay $800/mo for EBS vs. $230/mo for S3. * Durability: A single EBS volume has a ~0.1%–0.2% annual failure rate. At your scale, you will eventually lose a volume. S3 is designed for 99.999999999% durability. Losing 1/Nth of your ad-tech data might not be a disaster, but the engineering time spent fixing the "broken" table and re-syncing data is a hidden cost. 3. Compaction: "The Hot Node" Problem In a Shared-Nothing cluster with 1 replica, each node is an island: * The Bottleneck: If one node gets a disproportionate amount of updates (e.g., a specific ad campaign ID is trending), that node's CPU will redline trying to handle compaction. No other node can help it because only that node has the data. * Shared-Data "Global" Compaction: Because all CN nodes can see all data on S3, the FE can intelligently schedule compaction tasks. If Node A is busy with queries, the FE can tell Node B to perform the compaction for Node A’s data. This load balancing of background work prevents "hot nodes" from stalling your ingestion. 4. Scaling Effort (The "Migration" Trap) This is the biggest reason to choose Shared-Data now: * Shared-Nothing: If you need to add a node (because 390GB/hour is outgrowing your CPU), StarRocks must rebalance the data. Even with 1 replica, it has to move TBs of data across the network to the new node. This I/O spike often degrades query performance for hours. * Shared-Data: You just spin up a new CN. It doesn't need to "receive" any data. It just starts querying S3. It is truly elastic. Summary: When should you stick with Shared-Nothing (1 Replica)? You should stay on Shared-Nothing only if: 1. You have zero S3 access: Your environment (e.g., on-premise without MinIO) physically doesn't support object storage. 2. Absolute lowest latency is everything: You need sub-10ms response times and you are willing to risk periodic "partial data" outages to get the speed of local EBS without the overhead of the S3 commit. Rocky’s Recommendation: For ad-tech, where data volume grows exponentially, Shared-Data is the "Correct" architecture. Even with 1 replica, Shared-Nothing ties your data to specific hardware. Shared-Data decouples them, giving you the ability to use Spot Instances for compute and S3 for storage, which is the ultimate cost-saving combo for "non-critical but high-volume" data. Comparison Table (No Replication Case) Feature | SN (1 Replica + EBS) | Shared-Data (S3 + NVMe) | Winner Storage Cost | ~$80/TB | ~$23/TB | Shared-Data Uptime | Node failure = Data Gap | Node failure = No impact | Shared-Data Compaction | Isolated to one node | Distributed across cluster | Shared-Data
Scaling | Hours of data movement | Seconds (Stateless) | Shared-Data Complexity | Simple (No S3 setup) | Medium (Needs S3/IAM) | Shared-Nothing References • administration/management/resource_management/Replica.md • knowledge/usage/best-practice.md • deployment/shared_data/s3.md • introduction/Architecture.md References • administration/management/resource_management/Replica.md • knowledge/usage/best-practice.md
m
@Rocky Service availability aside, storage cost, and ease of scaling aside, the primary benefit of shared data would be the NVMe providing fresher data due to lower transaction apply latency?
r
It is a very sharp observation. If you strip away the cost and scaling arguments, you are touching the technical "heart" of why Shared-Data was built: High-performance ingestion on high-latency storage. You are largely correct. In a Shared-Data architecture, the NVMe (Data Cache) is the engine that prevents the high latency of S3 from destroying your Data Freshness. Here is how the NVMe specifically speeds up the "Apply" and "Freshness" cycle compared to a Shared-Nothing setup on EBS: 1. The Primary Key "Apply" Bottleneck For your ad-tech use case (390GB/hour with many updates), you are likely using Primary Key (PK) tables. * The Problem: When you load a new batch of data, StarRocks must check if those keys already exist to perform an update or delete. This requires reading the PK Index and the Delete Vector. * In Shared-Data: If that index is on S3, your "Apply" latency would be seconds (or minutes), making real-time data impossible. However, StarRocks caches the PK Index and metadata on the local NVMe. * The Result: The "Apply" phase happens at NVMe speeds (microseconds). This is why Shared-Data can maintain sub-second data freshness despite the "backend" being S3. 2. The "Write-Through" Freshness In StarRocks Shared-Data, when you enable 
datacache.enable=true
on a table: * As data is being ingested and written to S3, it is simultaneously written to the local NVMe cache. * This means the very next query hitting that data doesn't have to wait for an S3 download. The data is already "warm." * In a Shared-Nothing (SN) setup on EBS, you are limited by the EBS burst balance and IOPS. If you hit your EBS limit during a heavy ingestion spike, your "Apply" latency spikes, and your data becomes "stale." 3. Compaction "Acceleration" Data freshness isn't just about the first write; it's about how fast the system can "clean up" (compact) the data so it remains fast to query. * Shared-Data: Compaction reads source files from the NVMe cache, merges them, and writes the result to S3. Because the "read" part of compaction is hitting NVMe instead of EBS or S3, it finishes much faster. * The Freshness Link: Faster compaction = fewer data versions = faster queries. If compaction lags (common in SN with slow disks), query performance degrades, which users often perceive as "stale" or "slow" data. 4. Is it the Primary benefit? Technically, if you are already running Shared-Nothing on local NVMe (not EBS), then Shared-Data won't give you a freshness advantage—it might even be slightly slower due to the S3 commit overhead. However, almost no one runs Shared-Nothing on local NVMe because those disks are ephemeral. If the node dies, the data is gone forever. To get persistence in SN, you usually use EBS. The real "Magic" of Shared-Data is this combination: 1. S3 Throughput: S3 can handle 390GB/hour of write bandwidth much more cheaply and easily than a cluster of EBS volumes. 2. NVMe Latency: The local cache handles the "Apply" logic and PK lookups at speeds EBS can't touch. Summary If you are strictly looking at performance: Yes, the NVMe's primary job is to hide the S3 latency. It ensures that the "Transaction Apply" (PK lookups) and "Immediate Read" (Querying just-loaded data) happen at memory-like speeds. If you tried to do 390GB/hour on Shared-Nothing with standard EBS, you would likely hit an "I/O Wall" where the disks can't keep up with the compaction and apply logic, causing your data to become minutes or hours late. Shared-Data avoids this by using the NVMe as a high-speed "workspace" for those exact tasks. References • best_practices/primarykey_table.md • deployment/shared_data/feature-support-shared-data.md
2 Views
Open in Slack
PreviousNext
Powered by