http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have starrocks shared cluster wit...
# questions-and-troubleshooting
m
@Rocky I have starrocks shared cluster with 4.0.6 version deployed with k8s with cache/log/spill volumes. At some point of time, I can see all traffic is going to one compute node only and queries are timedout as one compute node busy with 90%+ cpu utilization and rest other compute nodes are very low utlized. After restarting that compute node, it again back to normal but after some hours, it is back to same state. How can I debug this? what causes this kind of issues?
r
This behavior suggests that the Frontend (FE) Scheduler has essentially pinned all query fragments to a single Compute Node (CN), likely because it perceives the other nodes as unhealthy, blacklisted, or less "ideal" due to cache locality. Since you are on version 4.0.6, you are using a version that introduced several advanced scheduling and node management features (like node blacklisting). Here is how to debug and the most likely causes: 1. Identify the Scheduling State First, determine if the FE actually sees the other nodes as available for work. * Check Node Status: Run 
SHOW COMPUTE NODES;
(or 
SHOW BACKENDS;
). Check the 
Alive
and 
SystemDecommissioned
columns. If they are 
false
, the FE will not send traffic to them. * Check for Blacklisting (New in 4.x): StarRocks 4.0 introduced automatic node blacklisting. If a node performs poorly or has high RPC failure rates, the FE may temporarily stop sending queries to it. * Action: Check the FE leader logs (
fe.log
and 
fe.warn.log
) for messages containing 
"blacklist"
, 
"unhealthy"
, or 
"remove from scheduler"
. * Check Warehouse Assignment: If you are using the multi-warehouse feature, ensure your user/session is not restricted to a specific warehouse that accidentally contains only one active node. Run 
SHOW WAREHOUSES;
. 2. Analyze the Query Load If all nodes appear "Alive," the issue is likely in how the fragments are distributed. * 
SHOW PROCESSLIST;
: While the cluster is under load, run this to see the 
Backend
column. Confirm if all active query IDs are associated with the same IP address. * Get a Query Profile: For a timed-out or slow query, retrieve the profile:
Copy code
sql
    -- Get the last query ID
    SELECT last_query_id();
    -- Get the profile (you may need to use the web UI or a tool like get_profile.py)
Look at the Fragment distribution. If the 
Fragment
count is high on one node and zero on others, the FE scheduler is intentionally avoiding the other nodes. 3. Likely Causes in Shared-Data (4.0.6) * Cache Locality Skew: In shared-data mode, StarRocks tries to send queries to the node that already has the relevant data in its Data Cache to avoid remote storage latency. If one node "wins" the initial cache for a hot dataset, the scheduler may continue to prefer it, creating a "hotspot" cycle where the node stays busy, gets more cache, and thus attracts more queries. * Consistency Hash Issues: StarRocks uses a consistent hashing algorithm to assign tablets to CNs. If the number of tablets is too small (e.g., a table with only 1-4 buckets), the fragments cannot be distributed across many nodes. * Debug: Check 
SHOW TABLET FROM <table_name>;
to see if tablets are balanced across your 4 CNs. * ShardManager Lock Contention: There is a known behavior in 3.5/4.0 where heartbeat failures can trigger a write lock in the metadata manager (
ShardManager
). If one node has network flakiness, it can cause the FE to block updates for other nodes, potentially leading to a state where only one node is considered "stable." * Resource Group Restrictions: If you use Resource Groups, check if the queries are hitting a group that is restricted to a specific set of nodes or is configured such that it saturates one node before spilling. 4. Debugging Steps via K8s * Check for Clock Skew: Shared-data clusters are sensitive to time differences. Check if the pods have significant clock skew:
Copy code
bash
    kubectl exec -it <fe-pod> -- date
    kubectl exec -it <cn-pod-1> -- date
* Connection Termination: If you have a K8s Service (LoadBalancer/ClusterIP) in front of the FE, ensure 
sessionAffinity
is NOT set to 
ClientIP
. If the FE is busy, it might be the app pinning connections. But if the CN is busy, it's an internal StarRocks scheduling issue. * Examine CN Metrics: Check if the busy node is actually doing Compaction rather than user queries. ```sql
SHOW PROC '/compactions';
Copy code
If one node is stuck in a heavy compaction loop, it can hit 90% CPU, causing subsequent query timeouts.

*Recommended Fixes to Try:*
1.  *Increase Buckets:* If your tables have few buckets, recreate them with more buckets (at least 2-4x the number of CNs) to allow better distribution.
2.  *Adjust Scheduler Settings:* Try disabling "preferred" cache locality temporarily to see if traffic balances:
    ```sql
    SET GLOBAL enable_schedule_by_cache = false;
3. FE Logs: Look for 
Drop node <IP> from query as it is not alive
in 
fe.log
. This is the smoking gun for heartbeat/K8s networking issues. References • knowledge/trouble_shooting/fe_deadlock_problems.md • deployment/deploy_manually.md
m
@Rocky here is the network received by cns , check once
r
The network traffic skew and CPU spike on a single Compute Node (CN) in a 4.0.6 shared-data cluster often points to a scheduling bottleneck or a background process loop (like Compaction). Since you are seeing high network RX (received) on only one node, that node is likely pulling massive amounts of data from object storage (S3/OSS/HDFS) or receiving all query fragments from the FE. 1. Check for Node Blacklisting (Primary Cause in 4.x) In StarRocks 4.0+, the FE automatically blacklists CNs if it detects RPC timeouts or connection failures. If 3 out of 4 nodes are blacklisted, 100% of the traffic will go to the remaining node, causing a CPU spike and subsequent timeouts. * Command: 
SHOW COMPUTE NODE BLACKLIST;
* What to look for: If you see your other 3 nodes here with 
AddBlackListType = AUTO
, the FE has stopped sending them work. * Reason: This often happens in K8s if there is intermittent network jitter between the FE pod and CN pods. Restarting the "healthy" node doesn't fix the underlying blacklist state of the others, but restarting the "bad" nodes (or the FE leader) often resets the state temporarily. 2. Check for Compaction Skew In shared-data mode, the FE schedules compaction tasks on CNs. If one node is assigned a massive compaction task (e.g., for a very large table with many small files), it will saturate its network RX to download data and its CPU to merge it. * Command: 
SHOW PROC '/compactions';
* Look for: Check the 
BackendId
or 
IP
column. If almost all active compaction tasks are on that one busy node, it's a compaction hotspot. * Debug: Check the 
MaxCS
(Max Compaction Score) in 
information_schema.partitions_meta
. If it's very high (>100) for certain partitions, those are triggering heavy compaction. 3. Cache Locality "Hotspots" StarRocks uses Cache-Aware Scheduling. It prefers to send queries to the node that already has the data in its local disk cache. * If one node happens to cache a very popular "hot" dataset, the FE will keep sending all queries for that data to that node. * The node stays busy, caches more data, and attracts even more queries—a feedback loop. * Test: Disable cache-aware scheduling temporarily to see if traffic balances:
Copy code
sql
    SET GLOBAL enable_schedule_by_cache = false;
4. Tablet/Bucket Distribution If your tables have a very small number of buckets (e.g., 
DISTRIBUTED BY HASH(...) BUCKETS 1
), StarRocks cannot split the work across multiple nodes. * Command: 
SHOW TABLET FROM <table_name>;
* Check: Ensure the number of buckets is at least 2-4x your number of CNs (e.g., 8-16 buckets for a 4-node cluster). 5. Check FE Leader Logs If the FE is unable to "see" the other nodes for specific query fragments, it will log the reason. Search the FE leader's 
fe.log
for: * 
Drop node <IP> from query as it is not alive
* 
failed to get shards
* 
ShardManager
related errors. Summary Checklist for you: 1. Run 
SHOW COMPUTE NODE BLACKLIST;
. If nodes are there, check K8s network stability between pods. 2. Run 
SHOW PROC '/compactions';
to see if that node is overwhelmed by background merges. 3. Check 
SHOW COMPUTE NODES;
and ensure the 
Alive
status is 
true
for all. 4. Try 
SET GLOBAL enable_schedule_by_cache = false;
to force the balancer to ignore the cache and spread the load. References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky how tablets are distributed to cns ? If I have a table with only 3 tablets, how are they distributed? Also, can I increase buckets for a table by altering?
r
In StarRocks Shared-Data mode (which uses Compute Nodes/CNs), tablet distribution and the ability to change bucket counts work differently than in traditional shared-nothing clusters. 1. How Tablets are Distributed to CNs In shared-data mode, data is stored in remote object storage (like S3), and tablets are logically distributed among CNs. * Logical Mapping: StarRocks uses a "Shard Manager" on the FE to map tablets to CNs. Unlike shared-nothing where a tablet is physically tied to a disk, a CN can technically compute any tablet. * Cache Locality (The "Hotspot" Cause): To optimize performance, StarRocks uses Cache-Aware Scheduling. The FE tries to schedule a query for a specific tablet to the same CN that handled it previously. This ensures that the data is likely already in that CN's local disk cache. * The 3-Tablet Scenario: If you have 3 tablets and 4 CNs: * Only 3 CNs will ever be assigned to process these tablets for queries. * The 4th CN will remain idle for any query involving that table. * If your query includes a filter on the bucketing key (e.g., 
WHERE id = 123
), it may only hit 1 tablet, meaning only 1 CN will do 100% of the work. This explains why you see one node at 90% CPU while others are low. 2. Can you increase buckets for an existing table? In version 4.0.6, you have a few options, but there are limitations for existing partitions in Shared-Data mode: * For New Partitions: You can change the "default" number of buckets for any future partitions created in that table. This is supported in 4.0.1+:
Copy code
sql
    ALTER TABLE <table_name> DISTRIBUTED BY HASH(<columns>) DEFAULT BUCKETS <new_num>;
* For Existing Partitions: * Shared-Nothing: Supports 
ALTER TABLE ... DISTRIBUTED BY ... BUCKETS ...
, which triggers a background data reshuffle. * Shared-Data: As of version 4.0.x, directly altering the bucket count for existing data partitions is often restricted or requires a full table rewrite. If you try to run the 
ALTER TABLE ... BUCKETS
command on an existing partition in shared-data mode and it fails with an "Unsupported" error, the standard workaround is: 1. Create a new table with the correct bucket count (e.g., 8 or 16 buckets for your 4 CNs). 2. Run 
INSERT INTO new_table SELECT * FROM old_table;
. 3. Use 
ALTER TABLE ... SWAP WITH ...
to swap them. 3. Immediate Debugging Steps for your Skew Since you see traffic pinning to one node, try these steps to verify if cache-locality is the cause: 1. Test Balance (Disable Cache Locality): Run this in your session and check if the load spreads:
Copy code
sql
    SET enable_schedule_by_cache = false;
If the load balances across all nodes, the issue is that your table has too few tablets (3) or a "hot" tablet that all your queries are hitting. 2. Verify Tablet Count:
Copy code
sql
    SHOW TABLETS FROM <table_name>;
Count the number of tablets. If it's 3, you cannot effectively utilize 4 CNs for that table. 3. Check for "Pinned" Nodes: Run 
SHOW COMPUTE NODE BLACKLIST;
. If 3 of your nodes are blacklisted due to network issues, the FE will force all traffic to the only "healthy" node left. Recommendation For a 4-node CN cluster, your table should have at least 8–16 buckets (2x to 4x the number of CNs). This allows the FE to distribute fragments more granularly and prevents a single hot bucket from overwhelming a single node. References • knowledge/usage/best-practice.md • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/shared_data_faq.md
m
@Rocky how to check which tablets are mapped to which compute node?
r
In a StarRocks Shared-Data cluster (v4.0.6), tablets are not physically tied to a specific node's disk, but they are logically mapped to a Compute Node (CN) by the Frontend (FE) for query execution and cache management. Here are the ways to check that mapping: 1. Check mapping for a specific table To see exactly which 
BackendId
(Compute Node) is currently assigned to each tablet in a table:
Copy code
sql
SHOW TABLET FROM <db_name>.<table_name>;
* Result Column: Look at the 
BackendId
column. * In Shared-Data: This represents the node that the FE has designated to process this tablet (usually for cache locality). If you see the same 
BackendId
repeated for all tablets, it confirms that all traffic for this table is pinned to one node. 2. Check tablet counts per node To quickly see if the total number of tablets is distributed evenly across all CNs in your cluster:
Copy code
sql
SHOW COMPUTE NODES;
-- OR --
SHOW BACKENDS;
* Result Column: Look at the 
TabletNum
column. * Analysis: If one node has a significantly higher 
TabletNum
than others, the FE is concentrating the workload there. 3. Check detailed mapping for a specific tablet If you have a specific 
TabletId
from a query profile or error log, you can find its location details:
Copy code
sql
SHOW TABLET <tablet_id>;
This returns a 
DetailCmd
column with a internal path like: 
SHOW PROC '/dbs/<db_id>/<table_id>/partitions/<partition_id>/<index_id>/<tablet_id>';
Running that resulting command will show the 
BackendId
currently "owning" that tablet. 4. Check Compaction Task Mapping If the node is busy with background work (which also follows tablet mapping), check where compaction is happening:
Copy code
sql
SELECT * FROM information_schema.be_cloud_native_compactions WHERE STATUS = 'OK';
Look at the 
BE_ID
and 
TABLET_ID
columns to see which node is processing which tablet's background merge. ────────── Why is this happening in your cluster? If 
SHOW TABLET FROM <table_name>
shows all tablets mapped to one `BackendId`: 1. Too few tablets: If your table only has 1 bucket, it only has 1 tablet per partition. That 1 tablet can only be on 1 node. 2. Node Blacklisting: If the FE perceives other nodes as "unhealthy" (check 
SHOW COMPUTE NODE BLACKLIST;
), it will reassign all tablets to the remaining healthy node. 3. Cache Locality: StarRocks 4.0 tries to keep tablets on nodes where the data is already cached. If one node "won" the race to cache the data, the FE may keep sending work there to avoid a "cache miss." You can test this by setting 
SET GLOBAL enable_schedule_by_cache = false;
. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_COMPUTE_NODES.md • faq/operation_maintenance_faq.md • faq/Sql_faq.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • administration/management/resource_management/Replica.md
m
@Rocky If I scale down compute to 2 nodes from 10, does it rechange the backend node Id for tablets and if it again scale up to 10 from 2, does it change?
r
In a StarRocks Shared-Data cluster (using Compute Nodes/CNs), tablet mapping is dynamic and is designed to handle elastic scaling. Here is what happens to the 
BackendId
and tablet distribution during scaling: 1. Does the BackendNodeId change? Yes. In shared-data mode, the 
BackendId
you see in 
SHOW TABLET
is a logical assignment made by the FE's Shard Manager. It is not a permanent physical home for the data. * Scale Down (10 → 2): When you remove 8 nodes, the FE detects they are gone. It must reassign the tablets that were mapped to those 8 nodes to the remaining 2 nodes. The 
BackendId
for those tablets will change to the IDs of the 2 surviving nodes. * Scale Up (2 → 10): When you add 8 nodes back, the FE (by default) will perform tablet rebalancing. It will move the logical "ownership" of some tablets from the 2 busy nodes to the 8 new nodes to spread the load. The 
BackendId
for many tablets will change again to point to the new nodes. 2. Is there data migration? No physical data migration occurs. This is the core advantage of shared-data: * The actual data (S3/OSS/HDFS files) stays exactly where it is in remote storage. * Only the metadata mapping in the FE changes. This happens in seconds. * However, the Local Disk Cache will be lost for those tablets. The new node will have to pull the data from remote storage again to fill its local cache, which usually causes a temporary performance dip (increased 
IOTimeRemote
) immediately after scaling. 3. Controlling this behavior The FE has a specific parameter that controls whether it automatically redistributes these "BackendId" mappings when you scale up: * Parameter: 
lake_enable_balance_tablets_between_workers
(Default: 
true
). * If set to 
true
, the FE will ensure that each of your 10 nodes has a roughly equal number of tablets (e.g., if you have 1000 tablets, each node gets ~100). * If set to 
false
, the FE will not move existing mappings to new nodes automatically. This prevents cache misses on the original nodes but leaves the new nodes idle until you create new data or manually trigger a balance. 4. Why you see "Skew" during scaling If you scale down to 2 nodes and then scale back to 10, but the 
BackendId
for a table doesn't seem to move to the new nodes: 1. Too few tablets: If your table only has 3 tablets (buckets), only 3 nodes can ever be assigned a 
BackendId
for that table. The other 7 nodes will have 0 tablets for that table. 2. Balancing hasn't finished: The FE moves mappings in background batches. You can check the progress by looking at the 
TabletNum
column in 
SHOW COMPUTE NODES;
. 3. Cache-Aware Scheduling: Even if a tablet is mapped to Node A, StarRocks might try to keep it there if Node A has a "warm" cache. Summary When you scale, the BackendId mapping changes automatically to maintain balance. The only "cost" is that the new nodes start with an empty local cache and must download data from remote storage for their first few queries. References • deployment/shared_data/feature-support-shared-data.md • administration/management/resource_management/Replica.md • data_source/data_cache.md • introduction/Architecture.md
m
@Rocky can I get tablet number by compute node in prometheus metrics?
r
Yes, you can monitor the tablet count per Compute Node (CN) using Prometheus metrics. 1. The Primary Metric The specific metric you need is exposed by the Frontend (FE), as the FE is the component responsible for maintaining the mapping of tablets to nodes. * Metric Name: 
starrocks_fe_tablet_num
* Description: Indicates the total number of tablets assigned to each Backend/Compute Node. * Labels: This metric includes a label (typically 
backend
) that identifies the specific CN host and port. * Endpoint: 
http://<FE_IP>:<FE_HTTP_PORT>/metrics
(Default port: 8030) 2. How to use it in Prometheus/Grafana You can query this metric to see the distribution across your nodes. For example:
Copy code
promql
# Show total tablets per node
starrocks_fe_tablet_num
If you see one node with a much higher value than others, it confirms a data skew or an imbalance in how the FE is assigning shards. 3. Alternative: SQL-based Check If you want to verify this via SQL (which you can also pull into monitoring tools via SQL exporters), you can query the `information_schema`:
Copy code
sql
SELECT BE_ID, COUNT(TABLET_ID) 
FROM information_schema.be_tablets 
GROUP BY BE_ID;
Or use the management command:
Copy code
sql
SHOW COMPUTE NODES; -- Look at the TabletNum column
Summary for Monitoring For your Prometheus dashboard, always scrape the FE metrics endpoint for tablet distribution data. While individual CNs (Backends) have their own 
/metrics
endpoints, they primarily report local resource usage (CPU, Memory, Cache hit rates) rather than the global tablet metadata. References • administration/management/monitoring/metrics.md • sql-reference/information_schema/be_tablets.md • administration/management/monitoring/Monitor_and_Alert.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_COMPUTE_NODES.md References • administration/management/monitoring/metrics.md • sql-reference/information_schema/be_tablets.md
m
@Rocky this is not giving starrocks_fe_tablet_num for shared data architecutre, it is giving for shared nothing. Also, how can I make sure the one compute node doesn't overload(reading 10-20x) data and trying to process always 90%+ and still queries getting timedout. Got to know if could be due to rebalancing tablets after scale and few tablets (3) in most tables. Mostly with cn 0, cn1 as these were not scaling down causing it to skew. What can I do to fix? If I increase the tablets to 12 from 3, will it redistribute data in bucket as well and remove adding any bucket number for table is prefferred?
r
The behavior you are seeing is a direct result of having too few tablets (3) for your cluster size. In a StarRocks Shared-Data architecture, the mapping between tablets and Compute Nodes (CN) is flexible, but it is still limited by the number of tablets you have defined for your table. 1. Why CN0 and CN1 are overloaded If your tables only have 3 tablets (buckets), StarRocks can only assign those tablets to a maximum of 3 nodes. * When you have 2 nodes: One node gets 2 tablets, the other gets 1. Both are busy. * When you scale to 10 nodes: Only 3 nodes out of the 10 can process that table's data at any given time. The other 7 nodes will stay idle for queries on that table. * The "Stickiness": CN0 and CN1 are likely overloaded because they were the first nodes in the cluster. Even after scaling up, the FE often keeps the mapping on the "warm" nodes to utilize the Local Data Cache. Since there are only 3 tablets to move, the FE doesn't see a reason to spread them thinly across 10 nodes. 2. Prometheus Metrics for Shared-Data In Shared-Data (v4.0.x), the FE metric 
starrocks_fe_tablet_num
should still report counts, but it might appear as 
0
if the FE hasn't finished the logical assignment or if you are looking at the "shared-nothing" version of the metric. For Shared-Data, use these alternatives to monitor distribution: * SQL (Best for accuracy):
Copy code
sql
    SELECT BE_ID, COUNT(*) FROM information_schema.be_tablets GROUP BY BE_ID;
* FE Metric: Check for 
starrocks_fe_lake_tablet_num
(if available in your specific v4.0.x build) or 
starrocks_fe_tablet_num
specifically with the label 
type="lake"
. * Compute Node Metric: On the CNs themselves, monitor 
starrocks_be_data_cache_bytes_total
to see which nodes are actually doing the "reading" and caching. 3. Will increasing buckets to 12 fix it? Yes, absolutely. This is the recommended fix. * Redistribution: If you increase buckets to 12, StarRocks will be able to use all 10 nodes in your cluster (each node will handle 1 or 2 tablets). This will resolve the "90% CPU" bottleneck on CN0/CN1 because the processing load will be fanned out. * How to do it: * WARNING: In StarRocks Shared-Data (Cloud-native) mode, the 
ALTER TABLE ... BUCKETS
command is not supported in v4.0.6 for data redistribution. It only changes the bucket count for future partitions. * To redistribute existing data, you must perform a "Reinsert":
Copy code
sql
        CREATE TABLE table_new LIKE table_old;
        -- Set the new bucket count
        ALTER TABLE table_new DISTRIBUTED BY HASH(...) BUCKETS 12;
        -- Migrate data (this rewrites the data into 12 buckets)
        INSERT INTO table_new SELECT * FROM table_old;
        -- Atomic swap
        ALTER TABLE table_old SWAP WITH table_new;
4. Recommendation: Remove "Manual" Bucket Numbers If you don't want to keep managing this as you scale, use Automatic Bucketing: 1. When creating or altering the table, omit the 
BUCKETS
number:
Copy code
sql
    ALTER TABLE my_table DISTRIBUTED BY HASH(join_key); -- No BUCKETS N
2. StarRocks will then automatically calculate the bucket count based on your current node count and the data volume (targeting ~1GB–10GB per tablet). 3. Note: This "auto" count is set at the time the partition is created. If you scale the cluster significantly later, you still need to use the 
SWAP
method above to "re-bucket" the old data to match your new capacity. Summary Checklist 1. Check Skew: Run 
SHOW TABLET FROM <table_name>
to confirm all 3 tablets are stuck on CN0/CN1. 2. Verify Scaling: Run 
SHOW COMPUTE NODES
and look at the 
TabletNum
column to see if the nodes you added actually have any work assigned. 3. Fix: Recreate the table with 12+ buckets (or use 
DISTRIBUTED BY RANDOM
if it's a Duplicate Key table) to ensure all 10 nodes can share the load. References
m
@Rocky If I just alter the table to 10 buckets from 3, does the new data distribute to 10buckets but old data will be in 3 buckets? or is it related to partition as well?
r
In StarRocks Shared-Data (Lake) architecture, there is a very important distinction between how 
ALTER TABLE
handles bucket counts compared to standard clusters. 1. Does it redistribute old data? No, not automatically in Shared-Data mode. While StarRocks recently added the ability to alter bucket counts for existing data in "Shared-Nothing" clusters, the documentation explicitly states that Shared-Data mode does not yet support redistributing existing data via the 
ALTER TABLE
command. If you run:
Copy code
sql
ALTER TABLE my_table DISTRIBUTED BY HASH(id) BUCKETS 10;
* Result: This will likely return an error or simply fail to trigger any background redistribution for your existing 3 buckets. * Old Data: Remains stuck in 3 tablets (3 files in S3/OSS), continuing to overload your CN0 and CN1. * New Data: Even if you change the setting, if the table is not partitioned, new data is appended to the existing 3 tablets. 2. Relation to Partitions If your table is partitioned (e.g., by date), you have more flexibility: * Future Partitions: If you run 
ALTER TABLE my_table DISTRIBUTED BY HASH(id) DEFAULT BUCKETS 10;
(available in v4.0.1+), any new partitions created tomorrow will have 10 buckets. Your old partitions (e.g., from yesterday) will still have only 3 buckets. * Specific Existing Partitions: You can try to alter specific partitions, but again, in Shared-Data mode, the redistribution of existing data files is not supported. 3. How to actually fix the "3 Bucket Skew" Since your 3 tablets are the bottleneck, the only way to spread that data across your 10 nodes is to rewrite the data into 10 buckets. Use the "Swap" method: 1. Create a temporary table with the correct bucket count:
Copy code
sql
    -- Use 10 or 20 buckets to utilize all 10 CNs
    CREATE TABLE table_new LIKE table_old;
    ALTER TABLE table_new DISTRIBUTED BY HASH(your_key) BUCKETS 10;
2. Migrate the data (this physically reads the 3 old files and writes 10 new files to remote storage):
Copy code
sql
    INSERT INTO table_new SELECT * FROM table_old;
3. Atomic Swap:
Copy code
sql
    ALTER TABLE table_old SWAP WITH table_new;
    DROP TABLE table_new;
4. Recommendation for Scaling If you plan to scale up and down frequently, avoid hardcoding bucket numbers. * Use Automatic Bucketing: 
ALTER TABLE table_new DISTRIBUTED BY HASH(your_key);
(omit the 
BUCKETS
keyword). * StarRocks will automatically choose a bucket count based on your current node count. * Note: Even with "Auto," it only calculates the number at the moment a partition is created. If you scale from 2 nodes to 10 nodes, you still need to perform the "Swap" or "Reinsert" step above to take advantage of the new nodes for your old data. Summary * 
ALTER TABLE ... BUCKETS 10
will not move your old data from 3 to 10 buckets in Shared-Data mode. * The "overload" on CN0/CN1 will persist until you use the 
INSERT INTO ... SELECT
method to re-bucket the data. * Always aim for a bucket count that is a multiple of your node count (e.g., 10, 20, or 30 buckets for 10 nodes). References • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md References • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md
m
@Rocky but nodes can be autoscaled, is it better to not give any buckets or use 2x or 4x bucket count for primary key tables? For non partition, keeping distributed by random is good or 2x-4x bucket count is recommended?
r
For auto-scaling environments, the best strategy depends heavily on the Table Model you are using (Primary Key vs. Duplicate Key). 1. Primary Key Tables (Cannot use "Random") Primary Key tables must use 
DISTRIBUTED BY HASH
. You cannot use 
RANDOM
distribution for this model. * Recommendation: Use Automatic Bucketing (omit the 
BUCKETS
keyword). * Why: When you omit the number, StarRocks automatically calculates it based on your current node count (usually 2x the number of BE/CN nodes for small clusters). * Is 2x or 4x better? If you plan to autoscale significantly (e.g., from 10 nodes to 40 nodes), manual 4x (e.g., 40 buckets for 10 nodes) is safer. * Reason: If you have 10 nodes and set 10 buckets, and then scale to 20 nodes, 10 of your new nodes will have zero data and will be idle during scans for that table. Setting 40 buckets ensures that even after scaling to 40 nodes, every node has at least 1 tablet to work on. 2. Duplicate Key Tables (Non-Partitioned) If your table is a Duplicate Key model, you have two great options for auto-scaling: * Option A: Distributed by Random (Highly Recommended) Since v3.2, you can use 
DISTRIBUTED BY RANDOM
with a 
bucket_size
property:
Copy code
sql
    ALTER TABLE my_table SET ("bucket_size" = "1073741824"); -- 1GB per bucket
This is the "ultimate" auto-scaling setting. StarRocks will dynamically increase the number of tablets as your data grows or as you scale your cluster. It removes the need for "2x or 4x" math entirely. * Option B: 2x–4x Manual Buckets If you prefer 
HASH
(for join performance/colocation), use 2x–4x your maximum expected node count. ────────── 3. Summary Comparison for Auto-scaling Scenario | Best Distribution Strategy | Why? Primary Key Table | 
HASH(...)
(No bucket count) | Uses StarRocks' automatic calculation ($2 \times Nodes$). Duplicate Key Table | 
RANDOM
+ 
bucket_size
| Supports Dynamic Tablet Splitting as data grows. Expected Huge Scale | 
HASH(...) BUCKETS 40+
| Ensures high parallelism even after the cluster grows 4x. 4. Important: The "Existing Data" Trap Even if you change the bucket count to 10, 20, or 40 today using 
ALTER TABLE
, your existing 3 tablets will not split automatically in Shared-Data mode. The "90% CPU on CN0/CN1" issue will persist for your old data until you rewrite it. You must use the "Swap" method to force the redistribution: 1. 
CREATE TABLE new_table LIKE old_table;
2. 
ALTER TABLE new_table DISTRIBUTED BY HASH(...) BUCKETS 20;
(or omit BUCKETS for auto). 3. 
INSERT INTO new_table SELECT * FROM old_table;
4. 
ALTER TABLE old_table SWAP WITH new_table;
Final Advice For your Primary Key tables, use Automatic Bucketing (don't specify a number). If you find that the auto-calculated number is too small for your future scaling needs, manually set it to 4x your current node count. This provides a good balance between "parallelism for queries" and "metadata overhead for the FE." References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • table_design/table_types/primary_key_table.md References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • best_practices/bucketing.md
m
@Rocky how many tablets a shared data cluster with 6 compute nodes each with 16vcpu & 100GB ram can handle? and what is general preferred tablet size/
r
For a Shared-Data (Lake) cluster with 6 Compute Nodes (16 vCPU, 100GB RAM each), the capacity for tablets is quite high, but the "healthy" range for performance is specific. 1. Preferred Tablet Size The general recommendation for StarRocks is to keep your tablets within the 1 GB to 10 GB range (raw data). * 1–5 GB per tablet: Ideal for low-latency queries and smaller tables. * 10 GB per tablet: Recommended for large-scale data (100TB+) to keep metadata manageable. Since v2.4.0, StarRocks supports Parallel Scanning (
enable_tablet_internal_parallel=true
), which allows multiple threads to scan a single tablet. This means you no longer need hundreds of tiny tablets to get high CPU utilization. 2. How many tablets can your cluster handle? The limit on the number of tablets is usually dictated by the FE (Front-End) memory, not the CN (Compute Node) memory. * Total Cluster Capacity: With a standard FE configuration (16GB RAM for FE), StarRocks can comfortably manage up to 1 million tablets across the entire cluster. * Per Node Capacity: For your 16 vCPU / 100GB nodes, each node can easily handle 10,000+ tablets without significant overhead. * The "Goldilocks" Zone: For a 6-node cluster, aim for 12 to 30 tablets per table (or per partition). * Minimum: 6 tablets (so every node does at least some work). * Recommended: 12–24 tablets (2x to 4x the node count). This allows for better distribution and accommodates future scaling to 10 or 12 nodes without needing to re-bucket immediately. 3. Why your 3 tablets are causing 90% CPU on CN0/CN1 In a Shared-Data architecture, even though data is in S3/Object Storage, the Compute Node assignment is 1:1 with tablets for a specific query fragment. * With only 3 tablets, only 3 nodes can ever participate in a scan for that table. * The other 3 nodes in your 6-node cluster will sit idle (0% CPU) while CN0, CN1, and CN2 are hammered at 90%+. * Because the FE tries to maintain "Data Cache locality," it will repeatedly send those 3 tablets to the same nodes that already have the data cached. This creates the "hot node" effect you are seeing. 4. Recommendation for your setup To fix the overload and timeouts on your 16vCPU nodes: 1. Re-bucket to 12 or 24: This ensures all 6 nodes (and their 16 vCPUs) can process the data in parallel. 2. Enable Parallel Scan: Ensure 
SET GLOBAL enable_tablet_internal_parallel = true;
is on. This allows each node to use multiple cores to scan its assigned tablets. 3. Use Automatic Bucketing: If you don't want to calculate this manually, define your table without a bucket number:
Copy code
sql
    ALTER TABLE my_table DISTRIBUTED BY HASH(join_key); -- Omit BUCKETS X
StarRocks will automatically choose a number (typically $2 \times Nodes$) based on your cluster size at that moment. Summary: Your cluster can handle hundreds of thousands of tablets, but your performance issue is caused by having too few (3). Increase to 12–24 to distribute the 90% CPU load across all 6 nodes. References • table_design/data_distribution/Data_distribution.md • faq/Deploy_faq.md • faq/shared_data_faq.md nodes.md)6 nodes. References • faq/Deploy_faq.md • faq/Others.md • administration/management/BE_configuration.md
m
@Rocky If I don't specify bucket count when creating a table and cluster has 10 compute nodes , it creates some tablets and these tablets can be merged to less number if tablet size is very small? If I scale down cluster to 2nodes, does it merge any tablets? I see it can increase tablets if it grows
r
No, StarRocks never automatically merges tablets into a smaller number, even if you scale your cluster down or the tablets are very small. Here is how it works in detail for the scenarios you mentioned: 1. Scaling Down (e.g., 10 nodes to 2 nodes) If you created a table on 10 nodes and StarRocks chose 20 tablets ($2 \times 10$ nodes), those 20 tablets will remain even if you scale down to 2 nodes. * Result: The remaining 2 nodes will simply take over the 18 orphaned tablets. Each node will then manage 10 tablets instead of 2. * Performance: This is actually fine and often better than having only 2 tablets, as it allows for more parallel processing on those two nodes. 2. Tablets with Very Small Data If you have 20 tablets but only 10MB of data, StarRocks will not merge them into 1 tablet. * Internal Merging (Compaction): StarRocks does merge small data files (segments) within each tablet to improve performance. This is called "Compaction." * Bucket Count: The number of buckets (tablets) is a logical structural property. Once a partition is created with 20 buckets, it stays at 20 buckets. 3. "Growth Only" for Random Distribution For Duplicate Key tables using 
DISTRIBUTED BY RANDOM
, StarRocks can split tablets if they grow too large (provided you set a 
bucket_size
), but it cannot shrink/merge them back. * Auto-Increase: As data grows, 1 tablet becomes 2, then 4. * No Auto-Decrease: If you delete 90% of the data, those 4 tablets will remain as 4 small tablets forever. Summary Comparison Action | Hash Distribution (Primary/Unique/Agg) | Random Distribution (Duplicate Key only) Initial Count | Fixed at creation (Auto-chosen or manual). | Fixed at creation (Auto-chosen or manual). Automatic Split | No. | Yes (if 
bucket_size
is set and data grows). Automatic Merge | No. | No. Cluster Scaling | Tablets move nodes; count stays the same. | Tablets move nodes; count stays the same. Recommendation If you accidentally created a table with too many tablets (e.g., you created a table on a 100-node cluster and now only have 3 nodes), you must manually fix it using the Swap method: 1. Create a new table with a lower bucket count. 2. 
INSERT INTO new_table SELECT * FROM old_table;
3. 
ALTER TABLE old_table SWAP WITH new_table;
This rewrite is the only way to physically reduce the number of tablets in StarRocks. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • administration/management/compaction.md References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md
m
@Rocky while creating table, if bucket size is configured with bucket_size = 1GB property, does it not create more than 1GB tablets?
r
The 
bucket_size
property behaves differently depending on the table model and distribution type. Here is the breakdown: 1. It only works for "Random Distribution" (Duplicate Key Tables) The 
bucket_size
property was introduced in v3.2 specifically for Duplicate Key tables using 
DISTRIBUTED BY RANDOM
. * How it works: It acts as a trigger for dynamic splitting. If you set 
bucket_size = 1GB
, StarRocks monitors the data volume during loading. When the data in a partition grows, StarRocks will automatically increase (split) the number of buckets to try and keep each tablet around that 1GB target. * Is it a hard limit? No. It is a "soft target." Because data is loaded in batches, a tablet might temporarily grow to 1.1GB or 1.2GB before the system triggers the creation of new buckets for subsequent data. 2. It does NOT work for "Hash Distribution" (Primary/Unique/Aggregate) If you use 
DISTRIBUTED BY HASH
, the 
bucket_size
property is ignored. * In Hash tables, the number of buckets is fixed when the partition is created. * If you omit the bucket count (Automatic Bucketing), StarRocks calculates a fixed number (e.g., 20) based on your node count at that moment. * The Problem: If that partition later grows to 100GB, those 20 tablets will each become 5GB. They will not split into more tablets just because they crossed a 1GB threshold. 3. Comparison of "Auto" Behaviors Feature | Hash Distribution (Primary Key) | Random Distribution (Duplicate Key) Initial Count | Auto-calculated (e.g., $2 \times Nodes$). | Auto-calculated. Dynamic Splitting | No. Count is fixed for that partition. | Yes. If 
bucket_size
is set. Tablet Size | Grows indefinitely as you add data. | Stays close to 
bucket_size
by adding buckets. Best for Scaling | Good, but requires manual re-bucketing later. | Excellent. Perfectly handles data growth and node scaling. Summary Recommendation * For Duplicate Key tables: Always use 
DISTRIBUTED BY RANDOM
and set 
PROPERTIES("bucket_size" = "1073741824")
. This is the most "hands-off" way to manage scaling. * For Primary Key tables: You cannot use 
bucket_size
. You should either use the default Automatic Bucketing (which scales with the number of nodes) or manually set a high bucket count (e.g., 4x your node count) to allow for future data growth without tablets becoming too huge (aim for 1GB–10GB per tablet). References • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • best_practices/bucketing.md References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • knowledge/usage/best-practice.md • faq/operation_maintenance_faq.md
m
@Rocky If I have 6 compute nodes and 3 are alive and 3 are down, If I create pk table without mentioning number of buckets, how many tablets does it create?
r
If you have a 6-node cluster but only 3 nodes are alive, StarRocks will calculate the automatic bucket count based on the available (Alive) nodes at the moment of table creation. For a Primary Key (PK) table with no bucket count specified, it uses the following formula: 1. The Calculation The formula for automatic bucketing (since v2.5.7) is: * If $X \le 12$: 
Buckets = 2 * X
* If $12 < X \le 24$: 
Buckets = 1.5 * X
_ _(where $X$ is the number of Alive Compute/BE nodes)* In your scenario: * $X = 3$ (Alive nodes) _ $3 \le 12$, so 
Buckets = 2 _ 3
* Result: 6 Tablets (Buckets) 2. Why it uses Alive nodes When you execute 
CREATE TABLE
, the Front-End (FE) must immediately assign the new tablets to active nodes to initialize them. It ignores nodes in a 
Dead
or 
Offline
state to ensure the table creation doesn't fail or wait for timed-out nodes. 3. Important Consequences * Performance Bottleneck: If you later bring the other 3 nodes back online (restoring your 6-node cluster), the table you created will still have only 6 tablets. * Query Impact: With 6 nodes and 6 tablets, you have a 1:1 ratio. This means each node handles exactly 1 tablet. If you increase your cluster to 12 nodes later, 6 of those nodes will sit completely idle during scans for this table because there are no tablets for them to process. * The "3 Tablet" trap: If you had only 1 node alive and created the table, it would have created only 2 tablets. Even when you fixed the cluster, that table would be stuck at a very low level of parallelism. Recommendation If you know your cluster is currently degraded but will eventually have 6 or more nodes, do not use automatic bucketing while nodes are down. Instead, manually specify the count:
Copy code
sql
DISTRIBUTED BY HASH(pk_column) BUCKETS 12; -- or 18/24 for better headroom
This ensures that once your nodes are healthy, the table is already optimized to use the full power of all 6 nodes (and allows for future growth). References • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • knowledge/usage/best-practice.md • table_design/data_distribution/Data_distribution.md
m
@Rocky when I create table without mentioning bucket number, at that time lets say it has 5nodes and it created 10 tablets per partition and when new partition gets created, lets say the compute nodes are 2 only. How many tablets will be there in new partition? or when new partitoin gets created, it is has 10nodes, how will be the behaviour?
r
For Hash-distributed tables (like your Primary Key table) using Automatic Bucketing, the number of tablets is calculated at the moment the partition is created, not just once when the table is created. If you do not specify a bucket count, StarRocks uses the current cluster state to decide the number of tablets for every new partition. Here is how it would behave in your specific scenarios: 1. Initial State: 5 Nodes Alive * Action: You create the table and the first partition. * Calculation: StarRocks sees 5 alive nodes. Using the formula $2 \times 5 = 10$. * Result: The first partition gets 10 tablets. 2. Scaling Down: 2 Nodes Alive * Action: You add a new partition (or it's created automatically via Dynamic Partitioning) while 3 nodes are down. * Calculation: StarRocks checks the cluster and sees only 2 alive nodes. * Result: The new partition will have only 4 tablets ($2 \times 2 = 4$). * Impact: This partition will have lower scan parallelism than your first one. 3. Scaling Up: 10 Nodes Alive * Action: You add a new partition after expanding the cluster to 10 nodes. * Calculation: StarRocks sees 10 alive nodes. * Result: The new partition will have 20 tablets ($2 \times 10 = 20$). * Impact: This partition is now optimized for the larger cluster. ────────── How StarRocks "Learns" (The 5-Partition Rule) To prevent creating too many or too few tablets, StarRocks actually uses a "learning" logic for automatic bucketing: 1. For the first 5 partitions: It strictly uses the formula based on the current alive nodes at that specific time. 2. After 5 partitions: It starts looking at the actual data volume in the previous 5 partitions. * If those partitions were very small, it might keep the bucket count low even if you have many nodes. * If those partitions were very large, it will increase the bucket count to ensure each tablet stays within the recommended size (roughly 100MB–1GB). Summary Table Cluster State | Calculation Basis | Resulting Tablets (New Partition) 5 nodes alive | $2 \times 5$ | 10 tablets 2 nodes alive | $2 \times 2$ | 4 tablets 10 nodes alive | $2 \times 10$ | 20 tablets Warning: Because different partitions can have different bucket counts, your performance might be inconsistent across time ranges (e.g., queries on "last week's data" might be slower if they were created during a node outage). Recommendation: If you want consistency despite node fluctuations, it is safer to manually specify the bucket count (e.g., 
DISTRIBUTED BY HASH(id) BUCKETS 20
) so that every partition is identical regardless of the cluster state at that moment. References • knowledge/usage/best-practice.md • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md • best_practices/bucketing.md
m
@Rocky what are best ways to handle data skew in pk tables and decision of buckets?
r
Handling data skew and choosing the right number of buckets is critical for Primary Key (PK) tables because they rely exclusively on Hash Distribution, which is more prone to "hot spots" than Random Distribution. 1. Handling Data Skew in PK Tables Data skew occurs when one tablet becomes significantly larger than others, causing a single BE node to do all the work while others sit idle. * Choose High Cardinality Columns: The distribution key must be a subset of your Primary Key. Pick columns with many unique values (e.g., 
user_id
, 
order_id
, 
device_sn
). * Use Composite Distribution Keys: If a single column has some "hot" values (e.g., a specific 
city_id
that has 50% of your traffic), distribute by multiple columns: 
DISTRIBUTED BY HASH(city_id, user_id)
. This mixes the data more thoroughly. (Limit this to 2-3 columns for join efficiency). * Avoid "Default" Values: If your distribution key often contains 
0
, 
-1
, or 
NULL
, all those rows will land in the same bucket. Filter these out or use a more specific key. * Modify Keys After Creation: Since v3.2, you can change the distribution columns of an existing table using 
ALTER TABLE
. If you realize your initial choice was bad, you can fix it without recreating the table. 2. Decision Logic for Buckets For PK tables, you have two choices: Automatic or Manual. A. When to use Automatic Bucketing (Recommended) Use this if you are unsure of your final data volume or if your cluster size might change. * How it works: StarRocks calculates the bucket count based on the number of Alive BE nodes and the data volume of the last 5 partitions. * Limitation: If a single partition is expected to exceed 100GB, StarRocks recommends switching to Manual bucketing for better control. B. When to use Manual Bucketing Use this for large-scale production tables where performance must be predictable. * The "Size" Rule: Aim for 100MB to 1GB per tablet (after compression). Tablets larger than 10GB can cause slow compactions and long recovery times. * The "Parallelism" Rule: Set the bucket count to a multiple of your BE node count (e.g., $2\times$ or $4\times$ the number of BEs). This ensures every node participates equally in a scan. _ _Example:* For a 6-node cluster, use 12, 18, or 24 buckets. * The "High-Concurrency" Exception: If you have thousands of small queries per second (point lookups), fewer buckets are better because they reduce the "fan-out" overhead across the cluster. 3. How to Detect Skew If you suspect skew, run this command:
Copy code
sql
SHOW TABLETS FROM my_table;
Look at the 
DataSize
column. if some tablets are 10GB and others are 100MB, you have a skew problem. You should then consider changing your distribution keys to higher cardinality columns. Summary Checklist for PK Tables Goal | Strategy Prevent Skew | Use high-cardinality columns (IDs/UUIDs) as distribution keys. Optimize Parallelism | Set buckets to $2\times$ or $4\times$ the number of BE nodes. Manage Size | Keep tablets between 100MB and 1GB. Flexible Growth | Use Automatic Bucketing unless a partition exceeds 100GB. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md
m
@Rocky if my table is partitioned by month and buckets are 30, if my initial date partitions are very small, I can merge the partitions. If I merge 2 partitoins as one, how will it take buckets? is it same number of buckets for merged partition or double?
r
When you merge partitions in StarRocks (e.g., merging daily partitions into a monthly one), the number of buckets in the resulting partition depends on how you configured the table's distribution. 1. If you have a Fixed Bucket Count (e.g., 30) If your table was created with 
DISTRIBUTED BY HASH(...) BUCKETS 30
, then the merged partition will have 30 buckets. * It does not double. A partition is a physical container. When you merge Partition A (30 buckets) and Partition B (30 buckets), StarRocks creates a new Partition C and re-shuffles all data from A and B into the buckets of C. * Because your table definition specifies 30 buckets, every new partition created—including those created by a merge operation—will strictly follow that rule. 2. If you are using Automatic Bucketing If you created the table without specifying the number of buckets (letting StarRocks decide), the behavior is more dynamic: * Recalculation: StarRocks treats the merge as the creation of a brand-new partition. It will calculate the bucket count based on the total data volume of the partitions you are merging. * Result: * If the merged data is still relatively small, it might stay at the same bucket count (e.g., 30). * If the merged data volume is significantly larger (e.g., you merge 30 small partitions into one large one), StarRocks may increase the bucket count for that monthly partition to keep tablet sizes within the healthy range (100MB–1GB). * It will never simply "double" (30+30=60) unless the data volume calculation happens to land exactly on that number. Summary Configuration | Original Partitions | Result of Merge Fixed Buckets (30) | 30 buckets each | 30 buckets Automatic Bucketing | 30 buckets (based on daily volume) | Recalculated (often higher if volume is large) Pro-Tip for Merging If you are merging partitions because the initial ones were "very small" (as you mentioned), the Fixed 30 count is likely perfectly fine. However, if the merged monthly partition becomes very large (e.g., >100GB), you should check the tablet size after the merge:
Copy code
sql
SHOW TABLETS FROM my_table;
If the tablets in the new partition are exceeding 10GB, you may want to manually increase the bucket count for that specific partition after the merge:
Copy code
sql
ALTER TABLE my_table PARTITION p_monthly 
DISTRIBUTED BY HASH(key) BUCKETS 60;
References • table_design/data_distribution/expression_partitioning.md • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/data_distribution/expression_partitioning.md • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md • best_practices/bucketing.md References • table_design/data_distribution/Data_distribution.md • faq/operation_maintenance_faq.md
Sorry, I encountered an error while processing your request: 503 UNAVAILABLE. {'error': {'code': 503, 'message': 'The service is currently unavailable.', 'status': 'UNAVAILABLE'}}
m
@Rocky give recommended number of buckets for partition size and best practices when dealing with pk tables on number of buckets. How many tablets can a compute node with 16vcpu and 96GB node can handle efficiently?
r
For a StarRocks Primary Key (PK) table, your bucketing strategy directly impacts ingestion stability and query performance. Below are the recommendations for bucket sizes and best practices for your specific hardware configuration. 1. Recommended Number of Buckets & Tablet Size The goal is to keep each tablet (the physical unit of a bucket) within a "sweet spot" for size. * Ideal Tablet Size: 100 MB to 1 GB (compressed data). * Primary Key Table Specifics: Because PK tables maintain a primary key index (which can be memory-intensive), it is often better to lean toward the 1 GB size rather than many small 100 MB tablets. * Max Limit: Avoid letting tablets exceed 10 GB. Large tablets make data recovery (tablet migration) and compaction slower. * Calculation: $$\text{Buckets per Partition} = \frac{\text{Estimated Partition Data Size (compressed)}}{\text{Target Tablet Size (e.g., 1GB)}}$$ If you expect 30 GB of compressed data per monthly partition, 30 buckets is a perfect choice. 2. Best Practices for PK Tables * High Cardinality: Always use columns with high unique counts (e.g., 
user_id
, 
order_id
) as distribution keys. * Subset of PK: The 
DISTRIBUTED BY HASH(...)
columns must be a subset of the columns defined in your 
PRIMARY KEY
. * Avoid Skew: If one value (like a "default" ID or a very large tenant) appears in 20% of your rows, do not use that column alone for distribution. Use a composite key like 
HASH(tenant_id, order_id)
. * Automatic Bucketing: For tables where you aren't sure of the volume, StarRocks (v2.5.7+) can set buckets automatically. However, for large production tables (>100GB/partition), manual bucketing is still recommended for better control. 3. Node Capacity: 16 vCPU & 96 GB RAM Your node is a solid "standard" production specification. Here is how it handles tablets efficiently: * Efficient Tablet Range: A node of this size can typically handle 10,000 to 30,000 tablets comfortably. * Factors Limiting Efficiency: * Memory (96GB): PK tables are memory-hungry. Each tablet requires memory for the Primary Key Index (especially if not using Persistent Index on SSD) and Memtables during ingestion. If you have too many tablets (e.g., >50k), the metadata and index overhead will eat into your query memory. * CPU (16 vCPU): CPU is consumed by Compaction. Every tablet eventually needs "cumulative" and "base" compactions. Having 100,000 small tablets will cause "compaction storms," where the CPU is 100% utilized just merging small files, causing query latency to spike. * Metadata: Excessive tablets increase the burden on the FE (Frontend). For a cluster with 16GB RAM on the FE, you should keep the total tablet count (sum across all BEs) under 1 million. Summary Checklist for your 16vCPU / 96GB Node: * Aim for tablet sizes of ~1GB to keep the total count low. * Target <20,000 tablets per BE to ensure background tasks (compaction/checksums) don't starve your queries of CPU. * If you see memory issues: Check 
tablet_meta
and 
PK index
memory usage. If it's high, you likely have too many small tablets and should increase your bucket size (reduce bucket count) for new partitions. References • best_practices/bucketing.md • faq/Deploy_faq.md • table_design/data_distribution/Data_distribution.md • administration/management/BE_configuration.md References • faq/Deploy_faq.md • administration/management/BE_configuration.md • faq/Others.md
2 Views
Open in Slack
PreviousNext
Powered by