http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> My table only has 1 replication ena...
# questions-and-troubleshooting
m
@Rocky My table only has 1 replication enabled. 
SHOW DATA FROM <table>
indicates only ~10.5 TB of storage used. However, actual disk used across all my BE nodes in my shared nothing cluster hovers between 17-18 TB, sometimes spiking down to 15TB but quickly back up. What can possibly explain this ~1.5x actual disk usage vs table disk usage? Surely indexes and metadata can't all add up to 7.5TB of data. Compaction runs on every tablet every 30 seconds. BE nodes checks for compaction every 10 seconds. My trash directory constantly stays below 30GB
r
The difference between your logical data size (
SHOW DATA
) and physical disk usage (17-18 TB) in a shared-nothing cluster is common, especially with the aggressive compaction settings you described. While ~7.5 TB seems high, the following factors likely explain the 1.5x–1.7x overhead: 1. MVCC and Stale Rowset Retention (Most Likely) StarRocks uses Multi-Version Concurrency Control (MVCC). When compaction runs, it merges old "rowsets" into new ones. The old rowsets are not deleted immediately; they are marked as "stale" but kept on disk to ensure that long-running queries can still access a consistent snapshot. * The Problem: You mentioned compaction runs every 30 seconds. This creates a high frequency of new versions. * The Setting: The BE configuration 
tablet_rowset_stale_sweep_time_sec
controls how long these stale files stay on disk before being moved to the trash. The default is often 300 to 1800 seconds (5 to 30 minutes). * The Math: If you are compacting every 30 seconds and keeping stale files for 30 minutes, you could effectively have dozens of versions of the same data segments co-existing on the physical disk, even if 
SHOW DATA
only reports the "latest" visible version. 2. Primary Key Table Persistent Indexes If your table is a Primary Key table, StarRocks stores a persistent index on disk to speed up updates. * Location: These are stored in the 
persist/
directory under your BE storage path (e.g., 
${storage_root_path}/persist/
). * Size: For tables with many unique keys or large key columns, the persistent index can consume substantial space—sometimes 20-50% of the data size itself. * Verification: Run 
du -h --max-depth=1
in your BE storage directory. If the 
persist
folder is several terabytes, this is your answer. 3. Compaction Write Amplification During the compaction process itself, the system must write out the newly merged segment files before it can safely mark the old ones as stale. * In a high-ingestion or high-compaction environment, the disk space will "spike" as new files are written. * The fact that your usage "spikes down but quickly back up" suggests that the system is successfully cleaning up old files, but the next wave of compaction or ingestion immediately fills it back up with newer versions. 4. Index and Metadata Overhead While you noted indexes shouldn't add up to 7.5 TB, StarRocks segment files (
.dat
) include several internal structures that might not be fully reflected in some 
SHOW DATA
accounting versions: * Ordinal indexes, Zone maps, Bloom filters, and Bitmap indexes are all stored within the segment files. * Segment Footers and Metadata: In clusters with millions of tablets or very small segment files (often caused by over-frequent compaction), metadata overhead can become non-trivial. Recommended Actions: 1. Check Stale Sweep Time: On your BE nodes, check the value of 
tablet_rowset_stale_sweep_time_sec
. If it is high (e.g., 1800), and you are compacting frequently, consider lowering it to 
300
or 
600
to reclaim space faster.
Copy code
bash
    curl http://<be_ip>:<be_http_port>/configs | grep tablet_rowset_stale_sweep_time_sec
2. Analyze Storage Folders: Run 
du -sh *
inside your BE storage path. * If 
data/
is the culprit, it is MVCC/Versioning. * If 
persist/
is the culprit, it is Primary Key index overhead. 3. Monitor Compaction Score: High disk usage vs logical size often correlates with a high "Compaction Score." Check this via:
Copy code
sql
    SHOW PROC '/compactions';
4. Reduce Compaction Aggression: Compacting every 30 seconds is extremely aggressive for 10TB of data. This causes high I/O and keeps many versions alive. Unless you have a specific real-time requirement causing segment explosion, consider letting the BE manage the compaction intervals naturally using default thresholds.
For further verification, you can check the 
information_schema.be_configs
to see if any non-default storage settings are active. References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • using_starrocks/caching/block_cache.md
m
@Rocky How to check how much space index occupies? Like primary key order keys?
r
Checking the exact space occupied by indexes in StarRocks depends on the type of index. There isn't a single SQL command like 
SHOW INDEX STORAGE
, but you can find this information using the methods below: 1. Primary Key Persistent Index (Disk) If your table is a Primary Key table with 
enable_persistent_index
= 
true
(the default), the index is stored as separate files on the BE disks. * Estimate via Formula: The persistent index size can be estimated using this formula from the StarRocks documentation: 
Index Size ≈ (Primary Key Size in Bytes + 8) _ Row Count _ 0.5
 (The 0.5 factor accounts for typical compression). Example: If you have 1 billion rows and a 16-byte Primary Key: 
(16 + 8) _ 1,000,000,000 _ 0.5 ≈ 12 GB
per replica. * Check Physical Disk Usage: You can see the actual size by logging into a BE node and checking the 
persist
directory within your storage path:
Copy code
bash
    # Go to your BE storage root (defined in be.conf as storage_root_path)
    du -sh <storage_root_path>/persist/
This folder contains the 
.pindex
files. If this directory is large, it confirms the persistent index is the source of your overhead. 2. Order Keys (Sort Keys) Order Keys are not stored as separate "index files." Instead, they determine the physical sorting of the data within the 
.dat
segment files. * Because they are part of the data sorting itself, their "size" is essentially the size of the columns themselves as reported in 
SHOW DATA
. * However, sorting columns improves the compression of those columns. If you change your order keys, you might see the overall 
DATA_SIZE
change due to better or worse compression. 3. Other Indexes (Bloom Filter, Bitmap) If you have created specific Bloom Filter or Bitmap indexes: * These are embedded inside the segment files (
.dat
). * StarRocks currently does not provide a SQL-level breakdown of "Data vs. Internal Index" size per table. * How to check: You can use the 
metadata_viewer
tool (an offline tool provided in the StarRocks binary 
bin/
directory) to inspect a specific segment file, which will show the size of the footer, bloom filters, and bitmaps within that file. 4. Why 
SHOW DATA
is lower than actual disk usage 
SHOW DATA
typically reflects the size of the visible rowsets (the data currently being queried). It often does not account for: 1. Stale Rowsets: Merged data that hasn't been deleted yet (controlled by 
tablet_rowset_stale_sweep_time_sec
). 2. Persistent Index Files: The 
persist/
directory is usually excluded from the 
SHOW DATA
calculation. 3. Temporary Files: Files created during ongoing load or compaction tasks. Summary Recommendation: To see if your 7.5 TB gap is the index, run 
du -sh
on the 
persist
folder of your BE nodes. If that folder is small, then your 1.5x overhead is almost certainly MVCC versioning caused by your 30-second compaction frequency, which keeps many old versions of your 10.5 TB data alive on disk simultaneously. References • administration/management/BE_configuration.md • faq/Others.md
m
@Rocky All my persistent directories are empty. I did enable persistent index. Does that mean l0_max_mem_usage being 200Mb is enough to house all index in memory?
r
To clarify why your 
persist
directories are empty and address the 7.5 TB storage gap, let’s look at how StarRocks handles persistent indexes and the likely sources of your disk overhead. 1. Why are the 
persist
directories empty? If you have set 
"enable_persistent_index" = "true"
but the 
${storage_root_path}/persist
directory is empty, there are three likely scenarios: * Property didn't take effect: The 
enable_persistent_index
property must be set at table creation. While you can sometimes alter it, it may only apply to new data or require a manual trigger to convert existing in-memory indexes to disk. Check your table definition:
Copy code
sql
    SHOW CREATE TABLE <table_name>;
* Checking the wrong path: In some StarRocks versions and configurations (specifically shared-nothing), the persistent index files (
.pindex
) may not be in a global 
/persist
folder but nested inside the 
data/<shard_id>/<tablet_id>/
directories. Search for any 
.pindex
files across your storage root:
Copy code
bash
    find /path/to/storage -name "*.pindex"
* Threshold not met: The parameter 
pk_index_eager_build_threshold_bytes
(default 100MB) controls when the index is persisted. However, with 10.5 TB of data, you should have far exceeded this. 2. Is 
l0_max_mem_usage
(200MB) enough? No, 200MB is almost certainly not enough to hold the entire index for a 10.5 TB table in memory. * How it works: 
l0_max_mem_usage
is a per-tablet limit for the "Level 0" (in-memory) part of a persistent index. * The Math: If your index is truly in memory (because persistence isn't working), a 10 TB table with billions of rows would likely require hundreds of GBs or even TBs of RAM (approx. 20–30 bytes per row). If your BE nodes aren't crashing with Out-of-Memory (OOM) errors, it means the index is either successfully persisting to disk (somewhere other than where you're looking) or your row count is unexpectedly low. 3. Explaining the 7.5 TB Gap (1.5x - 1.7x Overhead) Since your 
persist
and 
trash
directories are reportedly empty/small, the 7.5 TB difference is likely caused by MVCC Write Amplification due to your ultra-aggressive compaction: * The "Double Data" Effect: Compacting every 30 seconds creates a constant stream of new segment files. Because you have high ingestion/compaction, StarRocks keeps "stale" rowsets to support active queries. * Stale Rowset Retention: Check the BE config 
tablet_rowset_stale_sweep_time_sec
(default is often 300s or 1800s). If you compact every 30 seconds but keep old files for 30 minutes, you might have 60 versions of the same data physically on disk at once. * Invisible Bloat: 
SHOW DATA
only counts "visible" data. It does not count: 1. Rowsets currently being merged by compaction. 2. "Stale" rowsets waiting to be swept to trash. 3. Primary Key "Delete Vectors" (which track deleted rows in a PK table). Recommended Steps: 1. Locate the files: Run 
du -h --max-depth=2
on your storage root to see which specific sub-directories are consuming the 18 TB. If it's all in 
data/
, it is MVCC versioning. 2. Verify Index Type: Ensure you are using the Primary Key model. Only PK tables use the persistent index. 3. Check Memory Usage: Run 
curl http://<BE_IP>:<BE_HTTP_PORT>/mem_tracker?type=update
to see exactly how much memory the Primary Key indexes and delete vectors are consuming. 4. Relax Compaction: A 30-second compaction cycle is extremely frequent. Try increasing 
update_compaction_per_tablet_min_interval_seconds
to 
120
or 
300
to give the system more time to clean up stale versions, which should narrow the gap between logical and physical disk usage. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md
m
@Rocky Do sort keys physically occupy space? Dont they just control how data physically ordered
r
The short answer is no, sort keys do not occupy significant extra space. Your intuition is largely correct: they primarily control the physical arrangement of data within the segment files. However, there are three nuances regarding "space" when it comes to sort keys: 1. The Prefix Index (Tiny Overhead) While the sort key columns are stored only once as data, StarRocks automatically generates a Prefix Index (also called a Short-key Index) based on them. * What it is: A sparse index that takes the first 36 bytes of the sort key every 1024 rows. * Space impact: Extremely small. It is typically 1,000x smaller than the actual data and is designed to be small enough to stay entirely in memory. It is stored in the footer of each segment file. 2. Physical Compression (Space Saver) Counter-intuitively, an effective sort key often reduces your physical disk usage. * Because StarRocks is a columnar database, it uses encoding like RLE (Run-Length Encoding) and Dict (Dictionary). * When data is sorted, identical or similar values are grouped together. This allows the compression algorithms to work much more efficiently. * Example: A 10 TB table of unsorted data might shrink to 7 TB simply by picking a sort key that groups repeating values together. 3. Primary Key Table Distinction Since you are using a Primary Key table, there is one important distinction: * Before v3.0: The Primary Key was the sort key. You couldn't separate them. * v3.0 and later: You can specify a different 
ORDER BY
key. If you do this, the Primary Key Index (the 1.5x overhead we discussed earlier) still exists to handle updates, but the physical data on disk is ordered by your 
ORDER BY
columns. * The Index vs. The Sort: The "Primary Key Index" is what occupies the TBs of space. The "Sort Key" (the physical order) occupies almost zero extra space. Summary The 7.5 TB gap you are seeing is not caused by your sort keys. Sort keys are just an "ordering instruction" during the write process. As established, that 7.5 TB gap is almost certainly the Primary Key Persistent Index (the mapping of keys to row locations) and stale MVCC versions being held on disk by your 30-second compaction frequency. References • table_design/indexes/Prefix_index_sort_key.md • best_practices/table_clustering.md • best_practices/primarykey_table.md References • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md • best_practices/table_clustering.md • best_practices/primarykey_table.md • sql-reference/sql-functions/utility-functions/encode_sort_key.md
m
@Rocky Is it better to let starrocks engine decided bucket count for db or manually specify? This is for primary key table
r
For a Primary Key table, the choice between automatic and manual bucketing depends on your data volume and performance requirements. In general, Automatic Bucketing is recommended for most users to simplify maintenance, but Manual Bucketing is superior for large-scale production tables (like your 10.5 TB dataset). 1. When to use Automatic Bucketing (Omitting 
BUCKETS
) Since v2.5.7, StarRocks can automatically set the bucket count based on cluster resources and data volume. * Best for: Small to medium tables, or tables with many small partitions where calculating bucket counts manually is tedious. * The "100GB Rule": If the raw data size of a partition exceeds 100 GB, StarRocks documentation recommends switching to manual configuration. Auto-bucketing may not scale well enough for extremely large partitions. * Formula: For a new table, StarRocks typically sets 
tablet_num
based on the number of BE nodes ($X$): * $X \le 12 \rightarrow 2X$ * $X \le 24 \rightarrow 1.5X$ * $X > 36 \rightarrow \min(X, 48)$ 2. When to use Manual Bucketing (Specifying 
BUCKETS
) Manual bucketing is preferred for large-scale data and performance-critical workloads. * Best for: Large tables (>100GB per partition), tables requiring Colocate Joins, or tables with high ingestion rates. * Primary Key Table Tip: Since Primary Key tables maintain an index for every tablet, having too many tiny tablets creates metadata bloat and memory fragmentation. Having too few massive tablets makes compaction and recovery slow. * The Target Size: Aim for 1 GB to 10 GB of raw data per tablet. _ _Example:* If you expect 500 GB of raw data in a partition, set 
BUCKETS 50
to 
BUCKETS 100
. 3. Key Considerations for Primary Key Tables * Bucketing Key Selection: You must specify the bucketing columns for a Primary Key table. The bucketing columns must be a subset of the Primary Key. * Write Throughput: Higher bucket counts increase the parallelism of writes (ingestion). If your 10.5 TB table is updated very frequently, slightly more buckets (towards the 1GB per bucket end) can help distribute the CPU load of the Primary Key index updates. * Colocation: If you plan to join this 10.5 TB table with another large table, you must manually specify the same 
BUCKETS
count and use the same distribution key to enable "Colocate Joins," which significantly boosts performance. Summary Recommendation For a 10.5 TB table, you should manually specify the bucket count. 1. Estimate the raw data size of your largest partition. 2. Divide that size by 5–10 GB to get your bucket count. 3. Ensure the bucket count is at least equal to (or a multiple of) your number of BE nodes to ensure all nodes are utilized. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • knowledge/usage/best-practice.md References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • knowledge/usage/best-practice.md
m
@Rocky My partition size ~22GB. I plan to have 336 partitions (hourly, 2 weeks TTL)
r
For a 10.5 TB Primary Key table with hourly partitions of ~22 GB, you should manually specify the bucket count. While Automatic Bucketing is a good default for small tables, manual control is better for your scale to ensure the Primary Key Index (which is per-tablet) doesn't become a bottleneck or a memory hog. *1. Recommended Bucket Count: 4 to 8 Buckets* Given your partition size of 22 GB, setting 
BUCKETS 4
or 
BUCKETS 8
is ideal. * Why 4 to 8? This results in tablet sizes of ~3 GB to ~5 GB. * The Sweet Spot: StarRocks recommends keeping tablets between 1 GB and 10 GB. * If you go too small (e.g., 20 buckets), you'll end up with 1 GB tablets. While efficient for queries, it creates 6,720 tablets (336 partitions × 20). Each tablet in a PK table maintains its own persistent index metadata; having too many can inflate FE/BE metadata memory. * If you go too large (e.g., 1 bucket), you'll have 22 GB tablets. This can make compaction slower and recovery longer if a BE node fails. 2. The Total Tablet Calculation With 8 buckets per partition and 336 partitions, your total tablet count will be:
$336 \times 8 = 2,688$ tablets (per replica).
This is a very "healthy" number for StarRocks. It provides enough parallelism across your BE nodes without overwhelming the FE metadata or the BE memory trackers. 3. Critical Requirements for PK Tables When you manually specify the distribution, keep these two rules in mind: * The Bucketing Key: You must use a column (or columns) that are a subset of your Primary Key. * High Cardinality: Ensure the bucketing column has many unique values (like an 
ID
, 
user_id
, or 
order_id
). Never bucket by a low-cardinality column like 
gender
or 
status
, as this will cause data skew where one bucket (tablet) becomes massive while others stay empty. Summary Recommendation Use a manual specification in your 
CREATE TABLE
statement:
Copy code
sql
DISTRIBUTED BY HASH(your_pk_column) BUCKETS 8
This gives you predictable tablet sizes, efficient Primary Key index management, and enough parallelism for your 10.5 TB dataset. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • best_practices/partitioning.md • best_practices/primarykey_table.md References • faq/Deploy_faq.md • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md
m
@Rocky Is it better to separate 2 week TTL partitions by day then? So just 14 partitions but they are big
r
Based on StarRocks best practices and the scale of your data (10.5 TB total, ~22 GB per hour), sticking with Hourly partitions is the better choice. While 14 partitions sounds simpler than 336, daily partitions would create several performance and stability risks at your scale: 1. Partition Size (The 100 GB Rule) StarRocks documentation recommends keeping individual partitions under 100 GB for optimal performance. * Hourly: ~22 GB per partition (Excellent). * Daily: ~530 GB per partition (Too large). Large partitions make background compaction much heavier. When StarRocks needs to merge data (especially in a Primary Key table), it has to process much larger chunks of data, which increases CPU/IO spikes and can lead to "Too many versions" errors during heavy writes. 2. Query Pruning Efficiency With a 10.5 TB dataset, "Scan less data" is your primary performance lever. * If you query for the last 3 hours of data: * Hourly: StarRocks scans ~66 GB (3 partitions). * Daily: StarRocks scans ~530 GB (the entire current day's partition). * Even if you use a 
WHERE
clause on a timestamp, the engine still has to open and "peek" into the larger daily partition, whereas hourly partitions are skipped entirely at the metadata level. 3. TTL and Data Lifecycle Dropping an old partition is a metadata-only operation (instant and free). * Hourly: You can expire data exactly every hour. Your storage usage remains very stable. * Daily: You can only expire data once every 24 hours. This means you will always be carrying an extra ~500 GB of "stale" data that is waiting for the next daily cleanup cycle. 4. Metadata Overhead (Not a concern here) The "red flag" for partition counts usually starts at 10,000+ partitions per table. * Your 336 partitions is a very small, healthy number for the Frontend (FE) to manage. There is virtually no memory benefit to reducing 336 partitions down to 14. Recommendation Stay with Hourly partitions. Your current size of 22 GB per partition is in the "sweet spot" for StarRocks—it's large enough to be efficient for the filesystem but small enough to keep compaction, recovery, and query pruning extremely fast. References • best_practices/partitioning.md • table_design/data_distribution/Data_distribution.md • best_practices/primarykey_table.md References • best_practices/primarykey_table.md • administration/management/compaction.md
m
@Rocky But the default_wg will always exist with 16 weight. I want ingestion to be given high priority too. What is the alternate way if I want the application to use application specific user/role and not 'root' password so it is given default_wg?
r
To prioritize ingestion for a specific application user or role without using the 
root
account, you should use Resource Group Classifiers. Instead of letting the application fall into the 
default_wg
, you can create a dedicated high-priority resource group and "classify" your application's user or role into it. 1. Create a High-Priority Resource Group You can create a resource group with a high 
cpu_weight
. Note that 
cpu_weight
is relative. If your 
default_wg
has a weight of 16 (the number of cores), you can set your ingestion group to a higher value or lower the weight of the 
default_wg
.
Copy code
sql
-- Create a group with a higher relative weight for ingestion
CREATE RESOURCE GROUP ingestion_wg
TO (USER='app_ingestion_user') -- Maps this specific user to this group
WITH (
    "cpu_weight" = "32",      -- Higher than the default 16
    "mem_limit" = "40%"       -- Reserve memory for ingestion
);
2. Using Roles for Flexibility If you have multiple application users, it is better to use Roles. Any user with that role will automatically inherit the resource group's priority.
Copy code
sql
-- Create a role
CREATE ROLE ingestion_role;
GRANT ingestion_role TO app_user_1;

-- Create/Alter a resource group to match the role
CREATE RESOURCE GROUP ingestion_wg
TO (ROLE='ingestion_role')
WITH (
    "cpu_weight" = "32",
    "mem_limit" = "40%"
);
3. Specifically Prioritizing Ingestion Jobs If the same application user does both queries (SELECT) and ingestion (INSERT/LOAD), you can use the 
query_type
classifier to ensure only ingestion gets high priority:
Copy code
sql
CREATE RESOURCE GROUP ingestion_wg
TO (USER='app_user', query_type IN ('insert')) -- Only INSERT/LOAD tasks match
WITH (
    "cpu_weight" = "64"
);
_Note: 
query_type = 'insert'
covers 
INSERT INTO
, 
BROKER LOAD
, and 
ROUTINE LOAD
(from v2.5+)._ 4. Adjusting the 
default_wg
 If you find that the 
default_wg
still consumes too many resources because its default 
cpu_weight
is equal to the number of cores, you can manually lower it:
Copy code
sql
-- Lower the priority of everything else not explicitly classified
ALTER RESOURCE GROUP default_wg WITH ('cpu_weight' = '1');
Summary of the "Alternate Way" (Application Specific) 1. Create an App User: 
CREATE USER 'app_ingest' IDENTIFIED BY 'password';
2. Assign Permissions: 
GRANT INSERT ON db.* TO 'app_ingest';
3. Create the Classifier: Use 
CREATE RESOURCE GROUP ... TO (USER='app_ingest')
to bind that user to a high-priority group. This way, your application never needs the 
root
password, uses its own identity, and the StarRocks engine automatically routes its tasks to the high-priority resource pool. References • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md References • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md isolation_faq) ) References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md
m
@Rocky What is default_wg used for then if I assign the ingesting application ingest_wg?
r
Even if you assign your application to a dedicated 
ingest_wg
, the 
default_wg
 serves as the mandatory safety net and fallback for everything else in the cluster. In your specific setup, 
default_wg
is used for: 1. The "Catch-All" for Unclassified Traffic Any query or task that does not match your specific 
ingest_wg
classifiers (User, Role, DB, or Query Type) will automatically land in 
default_wg
. This typically includes: * Ad-hoc Queries: Analysts running 
SELECT
statements from their own accounts or BI tools. * Administrative Tasks: Metadata queries, monitoring checks, or manual troubleshooting commands. * Other Applications: Any other microservice or tool connecting to the database that you haven't explicitly moved to a resource group yet. 2. Preventing Resource Starvation The 
default_wg
ensures that the cluster remains usable. Without a default group, a query that doesn't match a classifier would have nowhere to run and would fail. * By default, 
default_wg
has a high 
cpu_weight
(equal to the number of BE cores). * If your 
ingest_wg
has a 
cpu_weight
of 32 and your 
default_wg
stays at 16, StarRocks will give your ingestion 2x more CPU cycles than all the "everything else" traffic combined when the system is under contention. 3. Separation from Materialized Views Note that StarRocks also has a 
default_mv_wg
. This is separate from 
default_wg
. * `default_wg`: Regular SQL traffic (Select/Insert). * `default_mv_wg`: Background asynchronous materialized view refreshes. How you should manage it Because 
default_wg
exists by default with a weight of 16, you have two choices to ensure your ingestion stays high priority: 1. Keep it as is and set 
ingest_wg
higher: Set your 
ingest_wg
cpu_weight
to something like 
64
or 
128
. This makes the 
default_wg
(16) look very small in comparison. 2. Downgrade the default: Explicitly lower the weight of the "everything else" traffic:
Copy code
sql
    ALTER RESOURCE GROUP default_wg WITH ('cpu_weight' = '1');
This ensures that if your ingestion application needs power, StarRocks will essentially "starve" any ad-hoc queries in the 
default_wg
to give your ingestion first priority. Summary: 
default_wg
is the "bin" for everything you haven't specifically categorized. By giving your app its own group, you are essentially pulling it out of that "bin" to give it its own dedicated lane and priority. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource_group/ALTER_RESOURCE_GROUP.md References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource_group/ALTER_RESOURCE_GROUP.md References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md
m
@Rocky Does Array<VARCHAR> vs VARCHAR for natural array types like 
deal_1, deal_2, deal_3
differ in query performance?
r
Yes, 
ARRAY<VARCHAR>
is significantly better for query performance than a comma-separated 
VARCHAR
string for natural array types. While a 
VARCHAR
containing 
deal_1, deal_2, deal_3
is just a single block of text to the database, an 
ARRAY
is a structured type that StarRocks stores and processes much more efficiently. 1. Filtering Performance: 
array_contains
vs. 
LIKE
 When you want to find rows containing a specific deal: * With `VARCHAR`: You must use 
LIKE '%deal_1%'
or 
find_in_set()
. This forces the engine to perform a full string scan for every single row, which is CPU-intensive and cannot be easily optimized. * With `ARRAY`: You use 
array_contains(col, 'deal_1')
. Because the data is structured, the engine can often use Low-Cardinality Optimization (introduced in v3.3.0). This allows StarRocks to work with integer "dictionary IDs" for each deal name instead of comparing the actual strings, making the check orders of magnitude faster. 2. Storage and I/O Efficiency * Compression: StarRocks stores 
ARRAY
elements as a sub-column. If your deals (like 
deal_1
, 
deal_2
) repeat across many rows, the dictionary-based compression on an 
ARRAY<VARCHAR>
column is much more effective than compressing one long, varied string. * Pruning: When querying an 
ARRAY
sub-field, the engine only reads the necessary element data. A 
VARCHAR
requires reading the entire string and all its delimiters into memory before it can be parsed. 3. Join Performance (The 
UNNEST
Advantage) If you need to join these "deals" against a 
deal_metadata
table: * VARCHAR: You are often forced into a slow 
find_in_set
join or a regex match. * ARRAY: You can use 
UNNEST
to flatten the array into rows during the join. StarRocks best practices explicitly recommend this approach over string-searching functions because it allows the optimizer to use Hash Joins, which are significantly faster for large datasets. 4. Indexing Support * Bloom Filters: You can apply Bloom Filter indexes to 
VARCHAR
columns to speed up 
=
and 
IN
queries. While you can't put a standard Bloom Filter on an entire 
ARRAY
column, you can use Generated Columns (v3.1+) to extract the most important element or a count from the array and index that, providing a workaround for high-speed lookups. * Inverted Indexes: From v3.3.0+, StarRocks supports full-text inverted indexes which are designed to speed up keyword searches. Using an 
ARRAY
makes it much cleaner for the engine to treat each element as a distinct "keyword." Summary Comparison Feature | 
VARCHAR
(Comma-separated) | 
ARRAY<VARCHAR>
 Search Function | 
LIKE
or 
find_in_set
| 
array_contains
Search Speed | Slow (Full string parsing) | Fast (Structured scan / Dictionary) Join Capability | Very Poor | Excellent (via 
UNNEST
) Storage | Delimiter overhead | Optimized columnar storage Best For | Human-readable notes | Tags, IDs, Categories, Lists Recommendation: If you have discrete elements like IDs or tags, always use 
ARRAY<VARCHAR>
. The only reason to use 
VARCHAR
is if the data is purely for display and you never intend to filter, aggregate, or join by the individual elements. References • sql-reference/data-types/semi_structured/Array.md • sql-reference/sql-functions/array-functions/unnest.md • administration/management/resource_management/resource_group.md (Low-cardinality optimization info) References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md
m
@Rocky I'm currently using 16 tablets (buckets) for my primary key table. Each tablet is about 1.3GB. 168 partitions live at any given time. Will increasing buckets help compaction score? Or not recommended since ~1GB is sweet spot for per tablet siz
r
Increasing the bucket count will likely help your compaction score, and it is safe to do so in your specific scenario. While ~1GB is a general "sweet spot" for many workloads, for Primary Key (PK) tables under high update/ingest pressure, smaller tablets (e.g., 200MB–500MB) are often more efficient. Why increasing buckets helps 1. Parallelism: Each tablet (bucket) is the smallest unit of compaction. By increasing the bucket count from 16 to 32 or 64, you allow the StarRocks BEs to parallelize compaction across more threads. If you have 16 tablets, you can only ever have 16 concurrent compaction tasks for that partition. 2. Task Size: Compacting a 1.3GB tablet with many "versions" (rowsets) is more CPU and memory-intensive than compacting a 500MB tablet. Smaller tablets result in shorter, more frequent compaction runs that are less likely to fall behind your ingestion rate. 3. PK Index Memory: In Primary Key tables, a portion of the PK index is kept in memory per tablet. Smaller tablets help keep the memory footprint per compaction task manageable. Addressing the "Sweet Spot" and Metadata * Tablet Size: 1.3GB is slightly on the high side for a PK table with high ingestion. Moving toward 500MB per tablet is a very healthy target. * Total Tablet Count: You currently have ~2,688 tablets (16 buckets * 168 partitions). This is very low for a StarRocks cluster. Even if you quadrupled your buckets to 64 per partition (10,752 tablets), you would still be well within the recommended metadata limits (modern StarRocks clusters easily handle 100k+ tablets). Recommendation 1. Increase Buckets: Try increasing the bucket count to 32 or 48 for new partitions. This will bring your tablet size down to the 400MB–650MB range. 2. Check Compaction Concurrency: If your score is still high after increasing buckets, you may need to give the BEs more "fuel" to process the extra parallel tasks. Adjust these in your `be.conf`: * `update_compaction_num_threads_per_disk`: Increasing this allows more concurrent PK compactions. * `compact_threads`: Ensure this is set to roughly 25% of your total CPU cores. 3. Monitor via `partitions_meta`:
Copy code
sql
    SELECT DB_NAME, TABLE_NAME, PARTITION_NAME, MAX_CS, AVG_CS
    FROM information_schema.partitions_meta
    WHERE TABLE_NAME = 'your_table_name'
    ORDER BY MAX_CS DESC;
Summary: Your current tablet count is low enough that there is no "metadata penalty" for increasing buckets, and the performance gains in compaction will outweigh the minor overhead of managing more files. References • best_practices/primarykey_table.md • table_design/data_distribution/Data_distribution.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky If I have 32 vCPU BE nodes, should I still use 32 buckets or 48 or even 64
r
If you have 32 vCPU BE nodes, you should aim for 48 or 64 buckets for your Primary Key table. While the "1GB per tablet" rule is a standard guideline for General/Duplicate tables to minimize metadata, Primary Key (PK) tables have different physics due to the high overhead of the delete-and-insert compaction process. Here is why 48 or 64 is better than 32 in your specific case: 1. Compaction Parallelism is the Bottleneck The "compaction score" is likely high because your BEs cannot process the 1.3GB tablets fast enough. * With 16 buckets: Only 16 threads can work on compacting a single partition at once. On a 32 vCPU node, you are leaving half the CPU idle during that partition's compaction. * With 64 buckets: You allow up to 64 concurrent compaction tasks per partition. This significantly increases the "throughput" of the compaction engine, allowing it to clear the version backlog much faster. 2. The "Goldilocks" Tablet Size for PK Tables For Primary Key tables, the ideal tablet size for performance is often between 200MB and 500MB. * 32 Buckets: ~650MB per tablet. (Good) * 48 Buckets: ~430MB per tablet. (Better) * 64 Buckets: ~325MB per tablet. (Best for Compaction) Smaller tablets mean the BE spends less time in a single "compaction lock" and uses less memory for the PK index during the merge process. This directly lowers the compaction score. 3. vCPU Alignment StarRocks generally recommends that the number of buckets per partition should be a multiple of the number of BE nodes. * If you have 3 BE nodes (each 32 vCPU), then 48 (3 x 16) or 96 (3 x 32) are mathematically ideal for even distribution. * If you have 2 BE nodes, then 64 (2 x 32) is ideal. 4. Query Parallelism (Internal Parallel) Since StarRocks v2.4, the parameter 
enable_tablet_internal_parallel
(usually 
true
by default) allows a single tablet to be scanned by multiple threads. This means you don't need a massive number of buckets just for query speed anymore. However, this setting does NOT apply to compaction. Compaction is still 1-thread-per-tablet. To lower your compaction score, you need more tablets. Recommendation For your 32 vCPU nodes and 1.3GB-per-tablet current state: 1. Use 64 Buckets for new partitions. This will bring your tablets to ~300MB, which is very healthy for PK tables. 2. Verify Total Tablet Count: With 168 partitions and 64 buckets, you'll have ~10,752 tablets. This is well within the safe limits for a cluster with 32 vCPU nodes (which can comfortably handle 100k+ tablets). Summary: Go with 64 buckets. It provides the best balance of compaction throughput, memory efficiency, and future-proofing as your data grows. References • best_practices/primarykey_table.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • faq/Deploy_faq.md References • table_design/data_distribution/Data_distribution.md • best_practices/partitioning.md • best_practices/bucketing.md
m
@Rocky I have an issue where I have a lot of sparse column updates. In partial update, if I specify columns in the stream load header, if a field is omitted from the json payload, starrocks primary key table will nullify it. Hence, I need to create different schemas and buffer pipelines for my update operations. I have around 17 different schemas now that flush every 30 seconds (each specifying their own unique set of columns they want to update). This is driving compaction score very high. What can I do to mitigate it? Would flushing them across all my nodes at the top of the minute be helpful? Or together at the top of every 30 seconds? Or would letting each schema pipeline flush on its own 30 second interval be better?
r
To mitigate your high compaction score and simplify your architecture, you should consolidate your 17 pipelines into a single buffer/pipeline. The "nullification" issue you are facing is caused by the fixed 
columns
header. In StarRocks (v3.1+), there is a way to handle sparse JSON updates where only the keys present in the JSON are updated, without needing different schemas. 1. The Solution: "Flexible" Partial Updates Starting in StarRocks v3.1, if you perform a Stream Load with 
partial_update: true
and 
format: json
but do NOT specify the 
columns
header, StarRocks will: 1. Automatically map the keys in your JSON object to the column names in your table. 2. Ignore any columns that are missing from a specific JSON record (i.e., it will NOT nullify them). How to implement: Change your Stream Load headers to:
Copy code
http
partial_update: true
format: json
strip_outer_array: true
# DO NOT include the "columns" header here
Now, if your pipeline flushes a batch containing: 
{"id": 1, "deal_1": "active"}
and 
{"id": 1, "deal_17": "closed"}
StarRocks will update 
deal_1
for the first record and 
deal_17
for the second, leaving the other 15 columns untouched. 2. Impact on Compaction The primary reason your compaction score is high is the number of rowsets (files) created per minute. * Current State: 17 pipelines × 2 flushes/min = 34 rowsets per minute. * Consolidated State: 1 pipeline × 2 flushes/min = 2 rowsets per minute. Compaction is a background process that merges these rowsets. By reducing the rowset creation rate by ~94%, you give the compaction engine significantly more breathing room to catch up. 3. Flushing Strategy Once consolidated, the flushing strategy becomes much simpler: * 30-second intervals are the sweet spot. Flushing more frequently (e.g., every 5-10s) generates too many small files. Flushing less frequently (e.g., every 5 mins) can cause memory pressure during the load. * Do not flush "together" at the top of the minute. If you have multiple BE nodes, staggering the flushes (or just letting them run on their own 30s timer) is actually better. It prevents a massive CPU and I/O spike across the cluster at the "top of the minute," which can lead to query latency jitters. 4. Additional Tuning: Row Mode vs. Column Mode For sparse updates across many columns, ensure you are using Row Mode (which is the default for Primary Key tables). * Row Mode: Optimized for "wide" tables where you update different columns in small batches. It keeps a hidden "delete bitmap" and is very efficient for your scenario. * Column Mode: Only recommended if you are updating the same 1-2 columns for millions of rows at once. Summary Recommendation: 1. Merge all 17 schemas into one pipeline. 2. Remove the 
columns
header from your Stream Load. 3. Flush every 30 seconds. 4. (Optional) If compaction is still high, increase your 
bucket
count as discussed previously to allow more parallel compaction tasks. References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • administration/management/compaction.md • table_design/table_types/primary_key_table.md References • loading/Json_loading.md • loading/Load_to_Primary_Key_tables.md • using_starrocks/Flat_json.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/StreamLoad.md
m
@Rocky THis is not possible. The Stream Load API rejects my partial update HTTP request if a column header is not specified. At the minimum, it says the primary keys must be specified in the columns header. Then, it will not update columns not specified in the header but in the body
r
The issue you are facing—where missing JSON fields are nullified when using a fixed 
columns
header—is a common friction point in earlier StarRocks versions or with specific configurations. To lower your compaction score, you must consolidate these 17 pipelines into one. This will reduce your rowset creation rate by 17x (from 34 files/min to 2 files/min), which is the most direct way to fix compaction health. Here is how to solve the "nullification" problem and the compaction issue: 1. Solve the "Sparse JSON" Nullification (v3.1+) Starting from StarRocks v3.1, you can perform Simple Mode Partial Updates with JSON. In this mode, StarRocks matches the keys in your JSON directly to the column names in the table. Crucially, if a key is missing from the JSON payload, the existing value in the table is not overwritten (it is ignored). To make this work in a single pipeline: 1. Remove the 
columns
header entirely. 2. Remove the 
jsonpaths
header entirely. 3. Ensure your JSON keys exactly match your table column names. 4. Include the Primary Key(s) in every JSON object. Example Request:
Copy code
http
curl --location-trusted -u root: \
    -H "partial_update: true" \
    -H "format: json" \
    -H "strip_outer_array: true" \
    -T data.json \
    http://<FE_HOST>:<FE_PORT>/api/db/table/_stream_load
If you are getting an error that "Primary Keys must be specified," double-check that you are on at least version 3.1. In some 2.x versions, the 
columns
header was mandatory for partial updates, which caused the nullification issue you described. 2. If you are on an older version (or Simple Mode fails) If you cannot use Simple Mode, you can use 
REPLACE_IF_NOT_NULL
 behavior, but this is only available in Unique Key tables. For Primary Key tables, if you are stuck with the 
columns
header: * Use the *`INSERT INTO ... SELECT * FROM FILES()`* syntax (available in v3.1+). This supports the 
BY NAME
clause, which is much better at handling sparse updates than Stream Load:
Copy code
sql
    INSERT INTO your_table BY NAME
    SELECT * FROM FILES(
        "path" = "s3://...",
        "format" = "json"
    );
This automatically handles sparse columns without nullifying missing ones. 3. Mitigating Compaction Score Once you consolidate into one pipeline, use these strategies to manage the score: * Flush Interval: Continue flushing every 30 seconds. * Staggered vs. Top-of-Minute: Do not flush everything at the "top of the minute." This creates a massive I/O and CPU spike across all BE nodes simultaneously, which can cause query timeouts and "Slow Down" errors. Let the pipelines run on their own 30s timers so the load is distributed over time. * Bucket Count: As discussed, your 32-vCPU nodes can handle more parallelism. If the score is still high after consolidation, increase to 64 buckets for new partitions. This allows more threads to work on compaction simultaneously. * Increase Compaction Threads: If your BEs have 32 vCPUs, ensure your 
be.conf
is tuned: * 
update_compaction_num_threads_per_disk = 4
(default is often 1-2). * 
compact_threads = 8
(to allow more concurrent merges). Summary Recommendation: Consolidate to one pipeline, use Simple Mode JSON (no 
columns
header) to avoid nullification, and keep your 30-second flush interval but avoid synchronized "top of the minute" spikes. References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • loading/Json_loading.md • administration/management/compaction.md • sql-reference/sql-statements/loading_unloading/INSERT.md References
Open in Slack
PreviousNext
Powered by