http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have observed 2 compute nodes doi...
# questions-and-troubleshooting
m
@Rocky I have observed 2 compute nodes doing most job, trying to read so much of data from bucket compared to other nodes(checked fs_io_read and also network_receive_bytes), using shared data with 4.0.8 version. most of the queries are hitting one or two nodes mostly but tablets are almost equally distributed to all compute nodes. what could happen here?
r
In a StarRocks shared-data (cloud-native) cluster, the behavior you're seeing—where only two nodes handle the majority of I/O and network traffic despite tablets being evenly distributed—is typically caused by the Query Scheduling and Cache Affinity mechanism. 1. Consistent Hashing and Cache Affinity In shared-data mode, any Compute Node (CN) can theoretically read any tablet because all data resides in the bucket (S3/GCS/MinIO). However, to achieve performance comparable to local storage, StarRocks caches "hot" data on the CN's local disks. To maximize the cache hit rate, the FE uses Consistent Hashing to map each tablet ID to a specific CN. * The Result: A specific tablet will always be scheduled to the same CN node. * The Imbalance: If your active queries only target a few "hot" partitions or specific tablets (due to query pruning or low bucket counts), only the 1 or 2 CN nodes mapped to those tablets will perform the work. Even if you have 10 nodes, if the query only hits 2 tablets, only 2 nodes will be active. 2. Tablet Bucketing and Query Pruning If your table has a small number of buckets (e.g., 8 buckets) and you have a larger number of CNs (e.g., 10 nodes), some nodes will naturally have no tablets assigned to them for that specific table. * Recommendation: Check your 
DISTRIBUTED BY HASH(...) BUCKETS X
setting. In shared-data mode, it is generally recommended to have a larger number of buckets (roughly *node_count * 2 to 4*) to ensure better distribution during query execution. * Check Skew: If your bucketing key has low cardinality (e.g., a status column with only 2 values), data will be unevenly distributed among buckets, leading to "hot" tablets. 3. StarRocks 4.0.x Specific Features In version 4.0.8, several features might exacerbate or explain this observation: * File Bundling: 4.0 introduced file bundling to reduce S3 API costs. Some nodes might act as coordinators for bundling or metadata caching. * Warehouse/Resource Groups: If you are using the Multi-Warehouse feature, ensure that your queries are not being pinned to a specific warehouse that only contains those two nodes. * Query Queue V2: Check if 
enable_query_queue_v2
is enabled. If concurrency limits are hit, the scheduler might be concentrating tasks on specific available slots. Diagnostic Steps To confirm what is happening, examine the Query Profile for your most frequent queries: 1. Check `BackendAddresses`: See if the query fragments are indeed only being sent to those two specific IPs. 2. Check 
ScanBytes
& `CompressedBytesReadRemote`: If 
CompressedBytesReadRemote
is high on these two nodes, they are pulling data from the bucket. If it's 0 but 
ScanBytes
is high, they are reading from their local Data Cache. 3. Check Tablet Mapping: Run 
SHOW TABLET FROM <table_name>
and check the 
ComputeNode
assignment column (or use 
SHOW PROC '/compenent_mapping'
) to see how the FE has hashed your tablets to the CNs. Potential Solutions * Increase Bucket Count: If you find only a few tablets are being accessed, consider increasing the bucket count of the table to spread the "hot" data across more nodes. * Check Bucketing Keys: Ensure you are bucketing on a high-cardinality column (like a primary ID) rather than a column with few unique values. * Random Scheduling (Emergency only): You can technically disable cache affinity to force a random distribution, but this will significantly degrade performance as every query will likely result in a cache miss and a remote read from the bucket. References • administration/management/resource_management/Load_balance.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky for primary key tables, I'm giving pk as hash id and 95% of my cluster has pk tables only and min tablet num is 3 if tables has very less data till 2-3GB. I will check distributed keys. some tables, I've given week partition with 6buckets only does this can have this issue?
r
In a StarRocks shared-data (cloud-native) cluster, the behavior you are observing—where only two nodes are handling the majority of I/O despite tablets being "equally distributed"—is a direct consequence of how Cache Affinity and Consistent Hashing work together with low bucket counts. Here is the breakdown of why this is happening and how to fix it: 1. Consistent Hashing & Cache Affinity In shared-data mode, any Compute Node (CN) can read any data from the bucket. To ensure high performance, StarRocks uses Consistent Hashing to assign each 
Tablet_ID
to a specific CN. * The Goal: Ensure that a specific tablet is always processed by the same node so that its data can be cached on that node's local disk. * The Problem: If you have a partition with only 6 buckets, only 6 nodes (at most) can participate in scanning that partition. If you have a 10-node cluster, 4 nodes will always be idle for that specific query. 2. Why "Only 2 Nodes" are busy? Even if you have 6 buckets, you might see only 2 nodes doing work because: * Query Pruning: If your queries include a filter on the Primary Key (which is also your distribution key), StarRocks performs tablet pruning. A query like 
WHERE pk = 123
will hit exactly one tablet. If many of your queries are point-lookups or filter on the PK, they will only ever hit the specific nodes assigned to those few tablets. * Hashing "Clumping": With a very small number of buckets (like 3 or 6), the probability that multiple "hot" tablets from different tables hash to the same two CNs is relatively high. Consistent hashing is statistical; it balances better as the number of tablets increases. * Data Skew: Even if every node has 1 tablet, if 80% of your rows hash into just 2 of those tablets (due to the nature of your PK values), those 2 nodes will do 80% of the work. 3. The "Bucket Count" Bottleneck You mentioned using 3 buckets for small tables and 6 buckets for weekly partitions. These numbers are likely too low for a modern StarRocks cluster: * Parallelism: The bucket count is the hard limit for query parallelism for a single table/partition scan. * Recommendation: In shared-data mode, we recommend a higher bucket count to ensure tablets are spread across more nodes. A good rule of thumb is *`number of CN nodes * (2 to 4)`*. For example, if you have 4 CNs, use at least 8–16 buckets even for smaller partitions. This allows StarRocks to move tablets more granularly if a node becomes overloaded. 4. Diagnostics & Verification To confirm this, perform these two checks: 1. Check Tablet-to-Node Mapping: Run the following command for your "busy" tables:
Copy code
sql
    SHOW TABLET FROM <table_name>;
Look at the 
ComputeNode
(or 
BackendId
) column. If many tablets you are querying are mapped to the same 2 IPs, you've confirmed a hashing/bucket count bottleneck. 2. Analyze Query Profile: Get the profile of a heavy query and look at the 
ScanNode
 section: * 
TabletNum
: How many tablets were actually scanned? If it's only 1 or 2, then pruning is the reason only 1 or 2 nodes are busy. * 
PeakMemoryUsage
/ 
CpuTime
: Are these concentrated on specific 
BackendAddresses
? How to Fix 1. Increase Bucket Counts: For your weekly partitions, try increasing the bucket count to at least 12 or 24 (depending on your node count). This increases the chance that work is spread across all nodes. _ _Note: For existing partitions, you cannot change the bucket count easily without re-inserting data or using 
ALTER TABLE
(which performs a schema change/re-distribution).* 2. Check PK Cardinality: Ensure your Primary Key has high cardinality (many unique values). If many rows share similar "prefixes" and you are bucketing on a subset, you might have data skew.
3. Enable Internal Parallelism: Ensure 
enable_tablet_internal_parallel = true
is set. This allows one node to use multiple threads to scan a single tablet, though it won't help spread the load to other nodes—only more buckets can do that. Summary: Your 6-bucket and 3-bucket settings are limiting your cluster's ability to distribute work. Increase the bucket count for new partitions to at least double your node count to better utilize the whole cluster. References • table_design/data_distribution/Data_distribution.md • faq/Sql_faq.md • best_practices/bucketing.md
m
@Rocky DataSize: 0B StorageSize: 0B RowCount: 0 these values are showing 0 for all partitions of a table , what might be the problem?
r
In a StarRocks shared-data cluster (v4.0.8), seeing 
DataSize
, 
StorageSize
, and 
RowCount
as 
0
while data is actually present is typically a metadata reporting lag or a specific characteristic of how Primary Key tables manage statistics. Here are the most common reasons and how to address them: 1. Metadata Reporting Lag (Shared-Data) In shared-data mode, the Compute Nodes (CN) perform the data ingestion, but the Frontend (FE) manages the metadata. * The Issue: Statistics like 
RowCount
and 
DataSize
are updated asynchronously. Even if a transaction is committed and the data is queryable (the 
VisibleVersion
has increased), the FE may not have updated the partition-level statistics yet. * Check: Run 
SELECT count(*) FROM your_table
. If this returns the correct number of rows, your data is safe; the 
0
in 
SHOW PARTITIONS
is purely a display/reporting delay in the FE's catalog. 2. Primary Key Table Characteristics Primary Key tables use a "Delete+Insert" (upsert) model. * Row Counting: Unlike Duplicate Key tables where row counts are straightforward additions, PK tables must account for 
Delete Vectors
. The FE sometimes waits for a Compaction or a Persistent Index update to accurately calculate the logical row count. * Storage Size: In 4.0.x, StarRocks uses "File Bundling" and object storage metadata. Sometimes the physical size isn't reported back to the FE immediately after every small write to save on RPC overhead. 3. Statistics Collection vs. Physical Metadata There is a difference between the physical metadata (shown in 
SHOW PARTITIONS
) and optimizer statistics (used for query planning). * If the optimizer thinks the table is empty, it might generate bad query plans. * Fix: You can manually trigger a statistics update to force the FE to refresh its view of the data:
Copy code
sql
    ANALYZE TABLE <table_name>;
Note: In v4.0+, the FE should eventually do this automatically, but a manual trigger can verify if the stats are "stuck". 4. Known Metadata Display Issue There is a known behavior in some recent versions (including reports in the 4.0.x line) where 
SHOW PARTITIONS
fails to display 
RowCount
correctly for certain table types or after specific cluster restarts. * Verification: Check the system table instead to see if the data exists there:
Copy code
sql
    SELECT * FROM information_schema.partitions_meta
    WHERE table_name = 'your_table_name';
* Version Note: You are on 4.0.8. While this is a stable release, if the issue persists even after an 
ANALYZE
and significant data ingestion, it might be worth checking the StarRocks GitHub for specific fixes in later 4.0.x patches (like 4.0.11+) regarding metadata reporting. Summary Checklist 1. Query the data: Run 
SELECT COUNT(_)
and 
SELECT _ LIMIT 10
. If they work, there is no data loss. 2. Check Versioning: Run 
SHOW PARTITIONS
. If 
VisibleVersion
is greater than 1, data has definitely been committed. 3. Wait or Force: Wait 5-10 minutes for the background report thread. If it still shows 0, run 
ANALYZE TABLE <name>
. 4. Compaction: Check 
MaxCS
(Compaction Score) in 
SHOW PARTITIONS
. If it's very high, the system might be busy merging files and hasn't updated the final row count yet. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • administration/management/Statistics.md • deployment/shared_data/feature-support-shared-data.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky but all my tables are showing like that. what could be the problem?
r
If all tables in your cluster are showing 
0
for 
DataSize
and 
RowCount
, it is likely not a simple loading delay but a characteristic of how StarRocks handles metadata in shared-data (cloud-native) mode or a known reporting issue in the 4.0.x series. Here are the most likely reasons why this is happening across your entire cluster: 1. Known Metadata Bug in 4.0.x There is a known issue (tracked in StarRocks GitHub and recent release notes) where 
SHOW PARTITIONS
and 
SHOW TABLET
incorrectly return 
0
for 
DataSize
and 
RowCount
in shared-data environments. * The Cause: In shared-data mode, the FE (Frontend) doesn't "own" the physical data files; they are on S3/object storage. The CN nodes must report these stats back to the FE. There have been bugs in the reporting thread that cause these values to stay at zero even after successful loads. * Status: Fixes for this have been rolling out in later maintenance releases (e.g., 4.0.10+). Since you are on 4.0.8, you might be hitting this specific metadata reporting bug. 2. Primary Key Table Stats Collection Since 95% of your cluster is Primary Key tables, the row count calculation is more expensive than other table types. * In shared-data mode, StarRocks often calculates logical row counts (which account for deletes/updates) during the Compaction process. * If your cluster is new or compaction hasn't run significantly yet, the "Visible" row count might not have been pushed to the FE's metadata cache. 3. Check 
information_schema
 Sometimes the 
SHOW PARTITIONS
command uses a cached path that is stuck, but the system tables have the correct data. Try running this query:
Copy code
sql
SELECT 
    TABLE_NAME, 
    SUM(ROW_COUNT) as total_rows, 
    SUM(DATA_SIZE) as total_size 
FROM information_schema.partitions_meta 
GROUP BY TABLE_NAME;
If this also returns 0, the FE's metadata repository is definitely not receiving updates from the CN nodes. 4. FE Configuration for Reporting Check if the background reporting thread is active on your Leader FE. You can check your FE configuration:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%report%';
Ensure 
tablet_stat_report_interval_second
is set (default is usually 300s). If this was accidentally set to a very high value or disabled, stats will never update. Does this impact query performance? Yes, potentially. * The Cost-Based Optimizer (CBO) uses these row counts to decide on join types (e.g., Broadcast Join vs. Shuffle Join). * If the CBO thinks all your tables have 
0
rows, it may choose very inefficient execution plans, which could contribute to the node imbalance you mentioned earlier (e.g., choosing a plan that doesn't parallelize well). Recommended Actions: 1. Manual Refresh: Run 
ANALYZE TABLE <table_name>
for one of your main tables. This forces the FE to re-calculate statistics. Check if 
SHOW PARTITIONS
updates after this. 2. Verify Data: Confirm the data is actually there with 
SELECT COUNT(*) FROM <table_name>
. If the count is correct, your data is safe; the issue is purely a display/metadata bug. 3. Upgrade Path: If 
ANALYZE
doesn't fix the display, consider planning an upgrade to a later patch version of the 4.0 branch (like 4.0.12) where multiple shared-data metadata reporting fixes have been merged. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • administration/management/Statistics.md • introduction/Architecture.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky data is there in table. how to update metadata for all tables?
r
In StarRocks, "updating metadata" for row counts and data sizes generally refers to two different things: Optimizer Statistics (for query performance) and Physical Metadata (what you see in 
SHOW PARTITIONS
). Because there is no single "Refresh All" command for internal tables, here is how you can update both for every table in your cluster: 1. Update Optimizer Statistics (CBO Stats) To force the system to recalculate row counts, min/max values, and data distributions for all tables across all databases, use the automatic analyze task:
Copy code
sql
-- This creates a persistent background job that covers every table
CREATE ANALYZE FULL ALL;
* How it works: This triggers StarRocks to scan your tables and update its internal statistics catalog. * Check Status: You can see if the tasks are running or finished with:
Copy code
sql
    SHOW ANALYZE STATUS;
2. Force Refresh Physical Metadata The 
DataSize
and 
RowCount
shown in 
SHOW PARTITIONS
are reported asynchronously from the Compute Nodes (CN) to the Frontend (FE). If they are stuck at 
0
for all tables, you can try to force a faster reporting cycle by temporarily lowering the reporting interval:
Copy code
sql
-- Set FE to check for tablet updates every 10 seconds (default is 300)
ADMIN SET FRONTEND CONFIG ("tablet_stat_update_interval_second" = "10");
Note: After you see the numbers update, it is recommended to set this back to 
300
to avoid unnecessary RPC overhead. 3. Manual Batch Update (Scripted) If you want to manually trigger a one-time update for every table without creating a persistent automatic job, you can generate the SQL commands using the information schema:
Copy code
sql
-- Run this to generate the ANALYZE commands for every table
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', '_statistics_');
Copy the output and execute it to update every table one by one. Why are they still 0? (Important for Shared-Data) If you run 
ANALYZE
and wait 10 minutes, but the values in 
SHOW PARTITIONS
or 
information_schema.partitions_meta
are still `0`: 1. Reporting Bug: In StarRocks 4.0.8, there is a known issue where physical metadata reporting from CNs can fail in shared-data mode. If 
SELECT COUNT(*)
returns the correct number but the metadata doesn't, this is a display bug. 2. Compaction: In Primary Key tables, the final "logical" row count is often updated only after a Compaction task merges the historical versions and delete vectors. Check your compaction status:
Copy code
sql
    SHOW PROC '/dbs/<db_id>/<table_id>/partitions';
    -- Look at the 'MaxCS' (Compaction Score) column.
    -- High values mean compaction is pending.
3. Upgrade Recommendation: Multiple fixes for metadata reporting in shared-data mode were released in later 4.0 patches (4.0.10, 4.0.11, and 4.0.12). If this is a persistent issue across all tables, a patch update is highly recommended. Summary Recommendation Start by running 
CREATE ANALYZE FULL ALL;
. If that doesn't fix the numbers in 
SHOW PARTITIONS
within 10 minutes, use 
SELECT COUNT(*)
to verify your data is actually there, and consider the display issue fixed in later 4.0.x patches. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky here is attached, can see 2nodes always receiving data over network(scans/netowrk) but 2nodes almost idle and queries fragments looks going to busy nodes, causing queries to timeout, how can I debug this?
r
In a StarRocks shared-data cluster (v4.0.8), seeing two nodes busy while others are idle is almost always a result of uneven task scheduling or imbalanced data distribution. Here is a step-by-step guide to debugging why your query fragments are concentrating on specific nodes: 1. Check Tablet (Bucket) Counts In shared-data mode, the number of tablets (buckets) determines the maximum parallelism for scanning. * The Problem: If your table only has 2 buckets, only 2 CN nodes can scan that data at any given time. The other 2 nodes will remain idle during the scan phase. * How to check:
Copy code
sql
    SHOW PARTITIONS FROM your_table_name;
Look at the Buckets column. For a 4-node cluster, you should generally have at least 16–32 buckets per partition (or ~4–8 per node) to ensure the scheduler can balance the load. 2. Identify the Skew with 
EXPLAIN ANALYZE
 This is the most powerful tool for your situation. Run the problematic query with the 
ANALYZE
prefix:
Copy code
sql
EXPLAIN ANALYZE SELECT ... FROM your_table ...;
What to look for in the output: * Fragment Distribution: Look at the 
Fragment
sections. It will list the IP addresses of the nodes that executed that fragment. Are only two IPs appearing? * Scan Row Count Skew: Check the 
OlapScanNode
. If one node is processing 10 million rows while another processes 10 thousand, you have data skew (the bucket keys are not distributing data evenly). * Network Time: If the busy nodes show high 
NetworkSendTime
or 
Exchange
overhead, they might be acting as the "sink" for joins, pulling data from the other nodes. 3. Check "Preferred Node" (Cache Affinity) Scheduling In shared-data mode, StarRocks uses a Preferred Node strategy. It tries to send the same tablet to the same CN node every time so that it can reuse the Local Data Cache. * The Problem: If you are querying a specific range of data (e.g., the last 2 days) and all those tablets happened to be assigned to CN-1 and CN-2, then CN-3 and CN-4 will stay idle. * Check Hit Rates: Run 
SHOW BACKENDS
(or 
SHOW COMPUTE NODES
) and look at the cache metrics. If the busy nodes have high cache usage and the idle ones have 0, the scheduler is favoring cache affinity over pure load balancing. * Tuning: You can try changing the scheduling strategy to be more aggressive about balancing (though this may decrease cache hits):
Copy code
sql
    -- Default is 'balance'. 'elastic' uses consistent hashing.
    SET GLOBAL lake_service_scheduler_type = 'balance';
4. Verify Node Health and Connectivity If the FE (Frontend) thinks a node is "unhealthy" or slow, it will stop sending it work. * Check FE's View:
Copy code
sql
    SHOW PROC '/compute_nodes';
Ensure all 4 nodes show 
Alive = true
. Check if the 
LastHeartbeat
is recent for all of them. * Check FE Logs: Search the 
fe.log
for keywords like 
slow query
, 
backend
, or 
exclude
. The FE might be blacklisting the "idle" nodes for specific queries if they failed a previous fragment execution. 5. Data Skew on Distribution Keys If your 
DISTRIBUTED BY HASH(column)
key has many duplicate values (e.g., distributing by 
city_id
where 80% of data is from one city), that data will live in a single bucket. * How to check:
Copy code
sql
    -- Check if one bucket has significantly more rows than others
    SELECT bucket_id, COUNT(*)
    FROM your_table
    GROUP BY 1
    ORDER BY 2 DESC;
_(Note: This requires the 
_row_number
or similar internal hidden column if available, or just check the row counts in 
SHOW TABLETS FROM table
)._ Summary Checklist: 1. Too few buckets? Increase the bucket count in the 
DISTRIBUTED BY
clause. 2. Data skew? Change the distribution key to a column with higher cardinality (like a UUID or ID). 3. All fragments on same nodes? Use 
EXPLAIN ANALYZE
to confirm if it's the 
SCAN
phase or the 
JOIN
phase causing the bottleneck.
4. Healthy nodes? Ensure all nodes are reported as 
Alive
in 
SHOW PROC '/compute_nodes'
. References • knowledge/admin_and_operation/shared_data_compaction.md • deployment/deploy_manually.md
m
@Rocky all nodes are healthy and couldn't know the table casuing this, how can I find?
r
To find the specific table causing the imbalance where two nodes are busy and two are idle, you can use StarRocks' system metadata tables to detect physical data skew or scheduling concentration. Since you are on version 4.0.8, run the following diagnostics: 1. Identify the Table with the Most Data Skew This query ranks tables by how unevenly their data is distributed across your backends. If a table has 90% of its data on only two nodes, query fragments for that table will naturally concentrate there.
Copy code
sql
SELECT 
    db_name,
    table_name,
    COUNT(DISTINCT be_id) as node_count,
    SUM(data_size) / 1024 / 1024 / 1024 as total_size_gb,
    STDDEV(data_size) / AVG(data_size) as skew_index -- Higher means more imbalanced
FROM information_schema.be_tablets t
JOIN information_schema.tables_config c ON t.table_id = c.table_id
GROUP BY 1, 2
ORDER BY skew_index DESC
LIMIT 10;
* Skew Index > 0.5: Indicates significant physical imbalance. The table likely has too few buckets or a distribution key with very low cardinality. 2. Identify Currently Running Heavy Scans If the nodes are busy right now, use the 
current_queries
process list to see which tables are being scanned and on which nodes.
Copy code
sql
-- This shows rows scanned and memory usage for all active queries
SHOW PROC '/global_current_queries';
Look for queries with high 
ScanRows
or 
ScanBytes
. The 
feIp
tells you which FE coordinated it, but more importantly, you can then take the 
QueryId
and run:
Copy code
sql
-- Replace <QueryID> with the ID from the previous command
EXPLAIN ANALYZE <QueryID>;
In the output, look for the 
OlapScanNode
. It will list the active 
Instances
. Check if the IP addresses in the instance list are only the two "busy" ones. 3. Check for "Too Few Buckets" If a table is small but queried frequently, and it only has 1 or 2 buckets, it will only ever run on 1 or 2 nodes.
Copy code
sql
-- Find tables with very low bucket counts relative to your 4-node cluster
SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    BUCKETS 
FROM information_schema.partitions_meta 
WHERE BUCKETS < 8 -- For 4 nodes, you usually want at least 8-16 buckets
ORDER BY BUCKETS ASC;
4. Check for Cache Affinity (Shared-Data Specific) In shared-data mode, StarRocks tries to keep data "sticky" to a node's local disk cache. If you have one extremely "hot" table that fits into the cache of only two nodes, the scheduler will keep sending queries there. You can check which tables are consuming the most local cache on specific nodes:
Copy code
sql
-- Run this on the busy nodes if you have access to metrics, 
-- or check the FE's view of tablet distribution
SHOW PROC '/compute_nodes';
How to Fix It Once Found: 1. Increase Buckets: If the table has too few buckets, use 
ALTER TABLE ... SET ("bucket_size" = "...")
or recreate the table with more buckets (e.g., 16 or 32 for a 4-node cluster). 2. Change Distribution Key: If the 
skew_index
was high, your 
DISTRIBUTED BY HASH(col)
key is likely the culprit. Change it to a column with more unique values (high cardinality). 3. Random Distribution: For smaller tables where you don't do many joins, consider 
DISTRIBUTED BY RANDOM
to ensure even spread across all 4 nodes. References • loading/loading_introduction/troubleshooting_loading.md
m
@Rocky If my tables size is <100MB, do I still need 8buckets because 4nodes are there?
r
For a 100MB table in a 4-node cluster, the short answer is yes, 8 buckets is the recommended minimum, primarily to solve the "idle node" problem you are seeing. While 100MB is tiny for StarRocks, here is why you should use at least 4–8 buckets instead of 1: 1. The "Idle Node" Problem (Parallelism) In StarRocks, a single bucket (tablet) is the smallest unit of scheduling. * If you have 1 bucket, only 1 node can scan that data. The other 3 nodes will stay 0% idle. * If you have 4 buckets, each node gets 1 bucket. All 4 nodes can participate in the query. * If you have 8 buckets (StarRocks' default recommendation for a 4-node cluster), each node gets 2 buckets. This provides better load balancing if one node is slightly slower than others. 2. Why "8" specifically? StarRocks uses an automatic calculation for bucket counts (when you don't specify them): * Formula: 
Number of Nodes * 2
(for clusters with $\le$ 12 nodes). * For your 4-node cluster, this equals 8 buckets. * This ensures that even for small tables, there are enough tasks to keep every CPU core across all nodes busy during a scan. 3. When to use FEWER buckets (< 8) You should only reduce the bucket count if you have thousands of tiny tables. * Metadata Overhead: Every bucket creates metadata that the Frontend (FE) must manage. If you have 10,000 tables, each with 8 buckets, you have 80,000 tablets. This can slow down the FE and increase the time it takes to create checkpoints or restart the cluster. * Rule of Thumb: If you have a few hundred tables, stick with 8 buckets. If you have 5,000+ tables that are all <100MB, you can drop them to 4 buckets (1 per node) or even 1 bucket to save metadata memory. 4. Shared-Data Mode Tip Since you are in shared-data mode, remember that the "Local Data Cache" on the nodes is also partitioned by bucket. If you have 8 buckets, parts of your 100MB table will be cached on all 4 nodes. If you have only 1 bucket, that 100MB will only ever be cached on one node, forcing all queries for that table to go to that specific node every time. Recommendation: * For your busy/idle node issue: Ensure your tables have at least 4 or 8 buckets. * To fix existing tables:
Copy code
sql
    -- In StarRocks 3.2+, you can change bucket counts without recreating the table
    ALTER TABLE my_table SET ("bucket_size" = "12582912"); -- ~12MB per bucket for 8 buckets
    -- OR simply recreate the table with:
    DISTRIBUTED BY HASH(id) BUCKETS 8;
Summary: For a 100MB table, 8 buckets (approx. 12.5MB per bucket) is perfectly fine and will ensure your 4-node cluster isn't sitting idle. References • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md
m
@Rocky looks like the nodes which are busy are always busy even though no queries are running in cluster and when query is fired, alwyas going to those nodes and spike can seee in those nodes only. is something happening with cluster?
r
If your nodes remain busy even when no queries are active, and new queries consistently target those same nodes, the cluster is likely experiencing Compaction Lag combined with Preferred Node Scheduling (Cache Affinity). In StarRocks shared-data mode (v4.0.x), this is a common "self-reinforcing" cycle where specific nodes become "responsible" for certain data. 1. The Background Process: Compaction In shared-data clusters, the FE schedules Compaction to merge small files into larger ones. This process uses significant CPU and Network (reading from S3/OSS, writing back). * The Problem: If you have high-frequency data loading, the system might be stuck in a "compaction storm" on specific nodes. * How to Verify:
Copy code
sql
    -- Check if any compaction tasks are running right now
    SHOW PROC '/compactions';

    -- Check the "Compaction Score" for your partitions
    -- Look for 'MaxCS' (Max Compaction Score). If it's > 10, the node is struggling.
    SELECT * FROM information_schema.partitions_meta ORDER BY Max_CS DESC;
If 
MaxCS
is high on specific nodes, they are busy rewriting data files in the background, which is why they appear busy even with "no queries." 2. The Query Concentration: Preferred Node Scheduling StarRocks uses a Preferred Node strategy in shared-data mode. To maximize performance, the FE remembers which CN node has the data cached on its local disk and always tries to send query fragments for that data to that specific node. * The Problem: If the "busy" nodes are the ones that happen to have the hot data in their cache, the FE will keep sending new queries to them to avoid a "cache miss" on the idle nodes. * Why this happens: This is common if your tables have too few buckets. If a table has only 2 buckets, those 2 buckets will be "assigned" to 2 preferred nodes. Queries on that table will never touch the other 2 nodes. 3. How to Debug and "Force" Balance To confirm if this is the issue and try to break the cycle, follow these steps: A. Check the Query Profile for "Preferred Node" usage Run a query and check the profile:
Copy code
sql
SET enable_profile = true;
-- Run your query
-- Get profile with: SHOW LAST QUERY; (or check in FE UI)
Look for the 
ScanNode
 metrics. If you see 
DataCacheHitBytes
is high on the busy nodes and the idle nodes aren't even listed in the execution fragment, it confirms Cache Affinity is pinning the query to those nodes. B. Temporarily Force Load Balancing (for testing) You can tell the FE to ignore cache affinity and distribute fragments evenly across all healthy nodes. If your performance improves and the "idle" nodes wake up, you have confirmed the scheduling imbalance:
Copy code
sql
-- Change scheduling from 'Preferred Node' (default) to 'Balance'
SET GLOBAL lake_service_scheduler_type = 'balance';
Note: This might cause a temporary query slowdown as the idle nodes fetch data from remote storage for the first time. C. Identify "Sticky" Metadata If the nodes are busy with no queries, they might be handling metadata tasks or data cleanup. Check the compute node status:
Copy code
sql
SHOW PROC '/compute_nodes';
Look at the 
TabletNum
column. If the "busy" nodes have thousands of tablets while others have very few, the FE is overloading them with metadata management. Summary Checklist to Fix: 1. Too few buckets? Ensure your 
DISTRIBUTED BY
clause results in at least 8-16 buckets (for 4 nodes). Re-partitioning with more buckets will naturally spread the load. 2. Stuck Compaction? If 
SHOW PROC '/compactions'
shows many tasks failing or running for hours, you may need to increase 
compact_threads
on the CN nodes or reduce the loading frequency. 3. Data Skew? If one specific value in your distribution key (e.g., a "NULL" or a default ID) contains 50% of your data, that node will always be 100% busy. Use the 
skew_index
query from my previous response to find the table. References
4 Views
Open in Slack
PreviousNext
Powered by