http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> IS shared-nothing soon going to be ...
# questions-and-troubleshooting
m
@Rocky IS shared-nothing soon going to be de-prioritized on the StarRocks open source roadmap
r
Shared-nothing is not being de-prioritized in the sense of being phased out or left behind, but there has been a significant shift in StarRocks' roadmap where shared-data is now the default architecture for new feature development and cloud-native innovation. Here is the breakdown of how the roadmap treats shared-nothing today: 1. Shared-Data is the New Default Starting with the 2025 roadmap and the StarRocks 3.x/4.x series, the project has officially moved to a shared-data-first approach. This means that major new architectural features—such as Multi-Warehouse (compute-compute isolation), Elastic Scaling, and *Native Data Lake Integration*—are designed primarily for the shared-data architecture. 2. Feature Parity is the Goal For the last several release cycles (3.x through 4.x), a primary goal of the open-source community has been to bring shared-data up to feature parity with shared-nothing. * In the past, shared-nothing was the "gold standard" for features like Primary Key tables and complex Materialized Views. * The current focus is ensuring these same features work just as well (or better) in the shared-data mode. 3. Shared-Nothing Remains the "Performance King" The roadmap still recognizes shared-nothing as the preferred choice for specific high-demand scenarios: * Ultra-Low Latency: Shared-nothing avoids the object storage network hop, making it the choice for sub-second real-time dashboards. * On-Premise Deployments: For users not running in the cloud (where S3/GCS isn't available), shared-nothing remains the primary deployment model. * Predictable Workloads: For workloads that don't require frequent scaling, the simplicity of shared-nothing is still highly valued. 4. Continued Maintenance and Updates If you look at the recent StarRocks 4.0 and 4.1 release notes, you will still see active work on shared-nothing, including: * Performance Optimizations: e.g., Adding thread pools for "Publish Version" transactions specifically for shared-nothing clusters to improve concurrency. * Bug Fixes: A significant portion of the community's effort still goes into maintaining stability for the large install base of shared-nothing users. Summary Shared-nothing isn't being abandoned; it is now considered a stable, mature architecture. Most "exciting" new development is happening in shared-data because that is where the industry is moving (toward decoupling and elasticity), but shared-nothing continues to be a core, supported part of the StarRocks open-source ecosystem for performance-critical and on-premise use cases.
m
@Rocky Does your BYOC solution run on k8s?
r
The answer depends on whether you are referring to the open-source StarRocks deployment or the managed BYOC (Bring Your Own Cloud) service provided by CelerData. 1. The Managed BYOC Solution (CelerData Cloud) The official managed BYOC solution (CelerData Cloud) primarily runs on Virtual Machine instances (such as AWS EC2, GCP Compute Engine, or Azure VMs) rather than a customer-managed Kubernetes cluster. * Infrastructure Management: When you use the BYOC service, the CelerData control plane orchestrates the deployment within your VPC using tools like CloudFormation or Terraform. It provisions dedicated VM instances to ensure maximum performance and avoid "noisy neighbor" issues common in shared container environments. * Storage-Compute Separation: It utilizes the shared-data architecture where compute nodes (CNs) can scale elastically. While these nodes are technically stateless, the BYOC service manages them as dedicated instance groups rather than K8s pods in your own cluster. * Simplicity: This approach allows you to get a managed, "SaaS-like" experience without needing to maintain or troubleshoot a Kubernetes control plane yourself. 2. Open-Source StarRocks on Kubernetes If your organization is standardized on Kubernetes and you want to run StarRocks as a BYOC-style deployment (meaning it’s in your own account and VPC), you can use the StarRocks Kubernetes Operator. * Full K8s Integration: The open-source project provides a robust Kubernetes Operator and Helm charts. This allows you to deploy FEs (Frontends), BEs (Backends), and CNs (Compute Nodes) as native K8s resources. * Autoscaling: You can use the Operator to integrate with Kubernetes Horizontal Pod Autoscalers (HPA) to scale compute nodes based on CPU/memory metrics. * Data Lake Integration: This is a popular choice for users who want to run "Elastic Data Lake Analytics" by deploying stateless Compute Nodes (CNs) on K8s to query data directly from S3, Iceberg, or Hudi. Summary Comparison Feature | Managed BYOC (CelerData Cloud) | Open-Source on K8s Orchestration | Managed by CelerData Control Plane | Managed by You (via K8s Operator) Compute Type | Dedicated Cloud VMs (EC2, etc.) | Kubernetes Pods Ease of Use | High (UI-based, automated upgrades) | Medium (Requires K8s expertise) Cloud VPC | Runs in your VPC | Runs in your K8s cluster/VPC In short: If you want a solution that runs specifically on your K8s cluster, you should use the open-source StarRocks Kubernetes Operator. If you want a managed service that handles the infrastructure for you within your cloud account, the BYOC solution will deploy and manage VM-based clusters for you.
m
@Rocky So the control plane runs on k8s. But for enterprise byoc solution no kubernetes? Just EC2 instances?
r
To clarify the architecture of the enterprise BYOC (Bring Your Own Cloud) solution (specifically CelerData Cloud): 1. The Control Plane The Control Plane is hosted and managed by the vendor (CelerData) as a SaaS. You access it via a web console to manage your clusters, set up scaling policies, and monitor performance. You do not need to provide or manage a Kubernetes cluster for the control plane. 2. The Data Plane (Your VPC) For the Enterprise BYOC solution, the data plane (where your StarRocks nodes live) runs on dedicated VM instances (e.g., Amazon EC2, Azure VMs, or GCP Compute Engine) within your VPC, rather than on a customer-managed Kubernetes cluster. Why no Kubernetes for the Enterprise Data Plane? * Performance & Isolation: Running directly on EC2 instances avoids the "noisy neighbor" effect and the networking overhead sometimes introduced by K8s overlays. This ensures the sub-second latency StarRocks is known for. * Ease of Management: By using VMs, the CelerData control plane can manage the "full stack" (OS, networking, disk) more predictably via CloudFormation or Terraform, without worrying about the specific configuration or constraints of a customer's internal K8s environment. * Simplicity: It eliminates the need for your team to have K8s expertise to run the database. 3. When is Kubernetes used? If your requirement is strictly to run everything on your own Kubernetes cluster, you shift from the "Managed BYOC Service" to the Open Source / Self-Managed model: * StarRocks Kubernetes Operator: You can deploy StarRocks on your own K8s cluster using the official Operator. In this case, both the FEs (Frontends) and BEs/CNs (Compute Nodes) run as Pods. * Full Control: You manage the upgrades, scaling (via HPA), and resource limits yourself. Summary * Enterprise BYOC (CelerData): Control Plane = SaaS; Data Plane = EC2/VMs in your VPC. * Self-Managed (Open Source): Control Plane = Your FE Pods; Data Plane = Your K8s Pods. In short: If you are buying the enterprise managed BYOC service, it will provision EC2 instances in your account, not pods in your K8s cluster.
m
@Rocky What does backup look like in starrocks
r
Backup in StarRocks looks very different depending on whether you are using the Shared-Nothing (standard) or Shared-Data (cloud-native) architecture. 1. Shared-Nothing Architecture (Traditional) In this mode, data is stored on the local disks of the Backend (BE) nodes. Backup involves taking a snapshot and copying it to external storage. * Workflow: 1. Create a Repository: You first define a remote storage location (AWS S3, HDFS, Google GCS, or Azure OSS) using the 
CREATE REPOSITORY
command. 2. Back up Data: Use the 
BACKUP SNAPSHOT
command to initiate an asynchronous backup. You can back up at the database, table, or even partition level. 3. Restore Data: Use the 
RESTORE SNAPSHOT
command. StarRocks can automatically recreate the table schema if it doesn't exist in the target cluster. * Key Command Example:
Copy code
sql
    BACKUP SNAPSHOT my_db.snapshot_v1
    TO my_s3_repo
    ON (my_table);
* Feature Support: As of v3.4.0, you can also back up metadata for external catalogs, views, and UDFs. 2. Shared-Data Architecture (Cloud-Native) In shared-data mode, your data is already living in object storage (like S3). Backup here focuses on capturing the FE Metadata and the state of the data at a specific point in time. * Cluster Snapshots: Instead of manual 
BACKUP
commands, you use the Cluster Snapshot feature. * Automation: You can enable automated snapshots that trigger every time the FE completes a metadata checkpoint (default is every 10 minutes).
Copy code
sql
    ADMIN SET AUTOMATED CLUSTER SNAPSHOT ON;
* Restoration: To restore, you start the StarRocks Frontend (FE) with a special flag (
--cluster_snapshot
). If you are restoring to a new cluster, you use a 
cluster_snapshot.yaml
configuration file to map the old storage paths to the new cluster. 3. Comparison of Approaches Feature | Shared-Nothing Backup | Shared-Data Snapshot Storage | Remote (S3, HDFS, etc.) | Same Object Storage as data Granularity | DB, Table, or Partition | Entire Cluster State Automation | Usually via external Cron/Scripts | Native (Automated Cluster Snapshot) Primary Use | Disaster Recovery / Migration | Disaster Recovery / Cluster Cloning 4. Alternative: Data Export If your goal isn't "system recovery" but simply moving data out of StarRocks for other uses, you can use the 
EXPORT
command or 
INSERT INTO SELECT
to an external table (like a Hive or Iceberg table). This writes the data out into formats like Parquet or ORC in your data lake. Which one should you use? * Use Shared-Nothing Backup if you are on-premise or need to move specific tables between clusters. * Use Shared-Data Snapshots if you are running a cloud-native cluster and want a "full-system" safety net. References • administration/cluster_snapshot.md • administration/management/Backup_and_restore.md • faq/Exporting_faq.md
m
@Rocky Where can I check background intensive processes? My `SHOW ALTER TABLE OPTIMIZE FROM requests;`indicate the operation is finished, but there is another sudden increase of CPU utilization and disk I/O util.
r
If you see a sudden spike in CPU and Disk I/O after an 
OPTIMIZE
task finishes, it is likely that Compaction, Statistics Collection, or Materialized View Refreshes have been triggered. Here is where you can check these specific background-intensive processes: 1. Compaction (The most common cause of post-load I/O) After an 
OPTIMIZE
or a large data load, StarRocks often triggers Cumulative or Base Compaction to merge small files. * View general status:
Copy code
sql
    SHOW PROC '/compactions';
* View tablet-level progress (Shared-data clusters):
Copy code
sql
    SELECT * FROM information_schema.be_cloud_native_compactions WHERE STATUS = 'RUNNING';
* Check BE Compaction Score: If the "compaction score" is high on a Backend (BE), it is aggressively merging files. You can check this in your Grafana dashboard or via:
Copy code
bash
    curl http://<BE_IP>:<BE_HTTP_PORT>/metrics | grep compaction_score
2. Background Statistics Collection StarRocks automatically collects statistics to optimize query plans. Large data changes often trigger a background 
ANALYZE
job. * Check analysis status:
Copy code
sql
    SHOW ANALYZE STATUS;
Look for jobs in the 
RUNNING
state. 3. Materialized View (MV) Refreshes If you have asynchronous Materialized Views, they may start refreshing automatically once the base table data changes. * Check running refresh tasks:
Copy code
sql
    -- Check active queries (MV refreshes appear as INSERT OVERWRITE tasks)
    SHOW PROC '/current_queries';

    -- Check specific MV refresh history/status
    SHOW MATERIALIZED VIEWS;
4. Background Data Ingestion (Routine Load) If you have Kafka/Routine Loads running, they might be committing a batch or handling a backlog. * Check status:
Copy code
sql
    SHOW ROUTINE LOAD;
5. Cluster Balancing and Tablet Repair If a node was recently added, restarted, or if disks are unevenly filled, the cluster might be moving data. * Check balancing status:
Copy code
sql
    SHOW PROC '/cluster_balance';
6. Deep Dive: Identify exact Tablet causing I/O If you want to know exactly which table/tablet is hammering your disks right now, you can run an I/O Profile directly on the Backend node:
Copy code
sql
-- Replace <backend_id> with the ID of the stressed BE
-- This samples I/O for 10 seconds and returns the top 3 tablets
ADMIN EXECUTE ON <backend_id> 'System.print(ExecEnv.io_profile_and_get_topn_stats("all", 10, 3))';
Summary of Commands Process | Command to Check Compaction | 
SHOW PROC '/compactions';
Stats Collection | 
SHOW ANALYZE STATUS;
Running Queries | 
SHOW PROC '/current_queries';
Data Movement | 
SHOW PROC '/cluster_balance';
I/O Profiling | 
ADMIN EXECUTE ON <id> 'System.print(ExecEnv.io_profile_and_get_topn_stats(...))';
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • using_starrocks/running_queries.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
m
@Rocky I am running shared-nothing architecture.
Copy code
Item,Number
balance_stat,1
cluster_load_stat,1
working_slots,8
sched_stat,1
priority_repair,0
pending_tablets,485
running_tablets,85
history_tablets,1000
all_tablets,570
r
Based on the output of your 
SHOW PROC '/cluster_balance';
, you are witnessing a cluster-wide tablet rescheduling/balancing event. In your case, you have 570 total tablets, and exactly 570 (485 pending + 85 running) are currently being managed by the Tablet Scheduler. This means 100% of your data is currently being moved or repaired, which explains the massive spike in CPU and Disk I/O. 1. Identify the "Why" To understand why every tablet is moving, run this command:
Copy code
sql
SHOW PROC '/cluster_balance/running_tablets';
Look at the 
Type
 and 
Status
 columns: * Type = REPAIR: StarRocks thinks data is missing or corrupted (e.g., a node is down, or you changed the 
replication_num
property). * Type = BALANCE: StarRocks is moving data because disks are unevenly filled or the replica count is skewed across nodes. Why did this happen after 
OPTIMIZE
? If your 
OPTIMIZE
command involved a table rewrite or significantly changed the size of the data on disk, it likely triggered a "Load Balance" event because the 
loadScore
(a mix of disk usage and tablet count) became uneven across your Backends. 2. Check for Tablet Health If the movement is due to a "Repair," you can see if there are unhealthy tablets cluster-wide:
Copy code
sql
SHOW PROC '/statistic';
Look for UnhealthyTabletNum. If this is non-zero, the system is frantically trying to recover data to meet your replication factor. 3. How to Throttle the I/O Impact If this background process is killing your cluster's performance for user queries, you can throttle it or pause it using FE dynamic configurations: A. To stop "Balancing" (moving data for evenness): If the tasks are type 
BALANCE
, you can stop them immediately:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("tablet_sched_disable_balance" = "true");
B. To slow down the speed (Throttle): If the tasks are 
REPAIR
(which you shouldn't disable entirely), you can reduce the number of concurrent tasks:
Copy code
sql
-- Decrease the number of tablets balanced at once (default is often 500)
ADMIN SET FRONTEND CONFIG ("tablet_sched_max_balancing_tablets" = "10");

-- Decrease the number of slots per disk (default is 2)
-- Setting this to 1 will reduce the I/O impact on each BE
ADMIN SET FRONTEND CONFIG ("tablet_sched_slot_num_per_path" = "1");
4. Summary of what to do next 1. Check the Type: Run 
SHOW PROC '/cluster_balance/running_tablets';
. 2. Verify Nodes: Run 
SHOW PROC '/backends';
to ensure all your nodes are 
Alive
. If one is dead, the 570 tablets are likely being "Repaired" because a replica was lost. 3. Monitor Progress: Watch the 
pending_tablets
count. Once it drops, your I/O will return to normal. 4. Check Compaction: If the movement is done but I/O is still high, check 
SHOW PROC '/compactions';
. New tablets arriving on a node often trigger "Base Compactions" immediately. References • administration/management/resource_management/Replica.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • administration/management/resource_management/Replica.md • administration/management/FE_configuration.md • using_starrocks/Colocate_join.md
m
@Rocky REPAIR type and all COLOCATE_MISMATCH. Should I just delete table and re-create
r
No, you should not delete and re-create the table yet. A 
COLOCATE_MISMATCH
with a 
REPAIR
type simply means that the tablets for tables in your Colocation Group are not physically located on the same set of Backend (BE) nodes. Since you just ran an 
OPTIMIZE
command, this is expected behavior: 
OPTIMIZE
often creates new tablets or rewrites data, and the system is now moving those new tablets to ensure they align with the other tables in the same group to maintain "local join" performance. Here is why this is happening and how to handle it: 1. Why did 
OPTIMIZE
cause this? When you run 
OPTIMIZE
, StarRocks essentially rewrites the table's data. If the table is part of a 
colocate_with
group, the new tablets must be placed on specific BE nodes to match the group's "Bucket Map." The 
REPAIR
tasks you see are the FE (Frontend) moving these tablets to their "rightful" homes. 2. Is "Delete and Re-create" better? Usually, no. * If you delete and re-create, you will have to reload all the data, which creates even more I/O and CPU load. _ The new table will _still* need to be balanced and placed according to the Colocation Group rules anyway. * The only exception: If the table is very small and you can reload it in seconds, re-creating it might be faster than waiting for the background scheduler to wake up and move 485 tablets. 3. A "Soft Reset" Alternative Instead of deleting the data, you can try to "reset" the colocation property. This sometimes forces the FE to recalculate the distribution more efficiently: 1. Remove from the group:
Copy code
sql
    ALTER TABLE <table_name> SET ("colocate_with" = "");
2. Add back to the group:
Copy code
sql
    ALTER TABLE <table_name> SET ("colocate_with" = "your_group_name");
Note: This will still trigger tablet movements, but it can clear "stuck" states. 4. How to monitor and verify Instead of just looking at the balance count, check the health of the Colocation Group itself:
Copy code
sql
SHOW PROC '/colocation_group';
* IsStable: If this is 
false
, the group is currently being repaired. * TableIds: Verify which tables are part of this group. Then, drill down into the specific group to see which buckets are misplaced:
Copy code
sql
-- Replace '10005.10008' with the GroupId from the previous command
SHOW PROC '/colocation_group/10005.10008';
5. Recommendation 1. Wait it out: Since you have 85 tablets already running, the system is actively working. Deleting now will waste the progress already made. 2. Throttle if needed: If the 
REPAIR
is making the cluster unusable, slow it down rather than stopping it:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("tablet_sched_slot_num_per_path" = "1");
3. Check for Disk/Node Issues: Ensure no BE is marked as 
Dead
in 
SHOW PROC '/backends';
. A single dead node can cause a permanent 
COLOCATE_MISMATCH
because the system can't place the required replicas. Summary: Your 
OPTIMIZE
finished the data rewrite, and now the system is doing the "cleanup" to keep your Joins fast. Let the 
pending_tablets
count drop to 0 naturally. References • using_starrocks/Colocate_join.md
m
@Rocky
Copy code
balance_stat,1
cluster_load_stat,1
working_slots,8
sched_stat,1
priority_repair,0
pending_tablets,271
running_tablets,0
history_tablets,1000
all_tablets,271
But colocation_group IsStable still false.
Copy code
BucketIndex,BackendIds
0,110783
1,11001
2,171908
3,203701
4,95876
5,203699
6,203697
7,203700
8,110783
9,11001
10,171908
11,203701
12,95876
13,203699
14,203697
15,203700
16,110783
17,11001
18,171908
19,203701
20,95876
21,203699
22,203697
23,203700
24,110783
25,11001
26,171908
27,203701
28,95876
29,203699
30,203697
31,203700
32,110783
33,11001
34,171908
35,203701
36,95876
37,203699
38,203697
39,203700
40,110783
41,11001
42,171908
43,203701
44,95876
45,203699
46,203697
47,203700
48,110783
49,11001
50,171908
51,203701
52,95876
53,203699
54,203697
55,203700
56,110783
57,11001
58,171908
59,203701
60,95876
61,203699
62,203697
63,203700
64,110783
65,11001
66,171908
67,203701
68,95876
69,203699
70,203697
71,203700
72,110783
73,11001
74,171908
75,203701
76,95876
77,203699
78,203697
79,203700
80,110783
81,11001
82,171908
83,203701
84,95876
85,203699
86,203697
87,203700
88,110783
89,11001
90,171908
91,203701
92,95876
93,203699
94,203697
95,203700
96,110783
97,11001
98,171908
99,203701
100,95876
101,203699
102,203697
103,203700
104,110783
105,11001
106,171908
107,203701
108,95876
109,203699
110,203697
111,203700
112,110783
113,11001
114,171908
115,203701
116,95876
117,203699
118,203697
119,203700
120,110783
121,11001
122,171908
123,203701
124,95876
125,203699
126,203697
127,203700
r
The fact that your 
pending_tablets
is 271 but 
running_tablets
is 0 indicates that the Tablet Scheduler has identified the mismatch but is not actively executing the repair tasks. In a shared-nothing architecture, this "stuck" state for Colocation Groups usually happens for one of the following reasons: 1. Check if Colocation Repair is Disabled If the scheduler isn't moving tablets, it might be globally disabled in your FE configuration. Run this to check:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%colocate%';
Look for: * 
disable_colocate_relocate
: If this is 
true
, StarRocks will never move tablets to fix a colocation mismatch. Set it to `false`:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("disable_colocate_relocate" = "false");
* 
disable_colocate_balance
: If this is 
true
, it won't balance buckets across nodes. 2. Inspect the Error Message To see why the 271 tablets aren't moving, look at the error column in the pending queue:
Copy code
sql
SHOW PROC '/cluster_balance/pending_tablets';
Scroll to the far right and look at the 
ErrMsg
 column. * "unable to find source replica": This is critical. Since your bucket map shows you only have 1 replica per bucket, if that replica is on a node that is currently down or the file is corrupted, the system cannot "repair" it because there is no source to copy from. * "no available backend": The target node in your colocation group might be full or offline. 3. Verify Backend Health Your bucket map lists specific Backend IDs (e.g., 
110783
, 
11001
, 
171908
). You must verify that all these IDs are currently 
Alive
and not in 
Decommission
state:
Copy code
sql
SHOW PROC '/backends';
If any of the IDs in your bucket map are missing from this list or 
Alive
is 
false
, the colocation group will stay 
IsStable = false
forever because it can't place data on a dead node. 4. Manually Force a "Balance" (The Reset) If the backends are healthy and configs are correct but it's still stuck, you can force the FE to re-evaluate the group. Note: Use the 
GroupId
found in 
SHOW PROC '/colocation_group';
(e.g., 
10005.10008
). Option A: Mark as Stable (Only if you are sure data is correct) If the 
pending_tablets
list is empty but 
IsStable
is still false, you can force it via the HTTP API:
Copy code
bash
curl -XPOST -u root: 'http://<FE_IP>:<FE_HTTP_PORT>/api/colocate/group_stable?db_id=<db_id>&group_id=<group_id>'
Option B: Re-calculate the Bucket Map If the cluster changed (nodes added/removed) and the colocation group is confused, you can try to re-link the table.
Copy code
sql
-- Replace 'group_name' with your actual group name
ALTER TABLE <table_name> SET ("colocate_with" = "");
ALTER TABLE <table_name> SET ("colocate_with" = "group_name");
5. Why 
OPTIMIZE
caused this (Specific to your case) Since you have Replication Number = 1, 
OPTIMIZE
is very risky. 1. 
OPTIMIZE
creates a new version of the data. 2. For a split second, it may have created those tablets on "non-optimal" nodes to finish the rewrite quickly. 3. Now, because it's a 
COLOCATE
table, it realizes they are in the wrong place. 4. Because you have 0 running tasks, the FE is likely waiting for a 
tablet_sched_repair_delay_factor_second
(default delay) or the scheduler is throttled. Recommendation: Check the 
ErrMsg
first. If it says anything about "slots" or "throttling," increase the concurrency:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("tablet_sched_slot_num_per_path" = "4");
If the 
ErrMsg
is empty and 
disable_colocate_relocate
is 
false
, a restart of the Master FE is a last-resort way to jumpstart the Tablet Scheduler. References • using_starrocks/Colocate_join.md • administration/management/FE_configuration.md
m
@Rocky [42000][1064] Getting syntax error at line 1, column 45. Detail message: No viable statement for input 'SHOW FRONTEND'. Still 271 pending tablets
r
The syntax error occurred because the word 
ADMIN
 is mandatory for that command. To view the configuration, use:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%colocate%';
Why are 271 tablets still pending? When 
pending_tablets
is non-zero but 
running_tablets
is 0, the scheduler has identified the problem but is refusing to start the move. Here is how to find the specific blocker: 1. Check the Error Message (Most Important) The system will tell you exactly why it's not moving these tablets. Run this and scroll to the far right to find the 
ErrMsg
 column:
Copy code
sql
SHOW PROC '/cluster_balance/pending_tablets';
* Common Error: "unable to find source replica" – Since you are running a Colocation group with (likely) a replication factor of 1, if the data was rewritten to a node that is now struggling or if the original replica is "lost" during the swap, the system has no source to copy from. * Common Error: "no available backend" – The target node for that specific bucket (from your bucket map) might be out of disk space or marked as "Dead". 2. Verify your Backend IDs are "Alive" Your bucket map shows specific Backend IDs (e.g., 
110783
, 
11001
). If even one of those IDs is not 
Alive
, the colocation group cannot be repaired.
Copy code
sql
SHOW PROC '/backends';
Check the 
Alive
column for every BE ID mentioned in your 
BucketIndex
list. 3. Check for Throttling or Disabled Repairs If the 
ErrMsg
is empty, the scheduler might be paused or throttled. Run these to check:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%tablet_sched%';
ADMIN SHOW FRONTEND CONFIG LIKE '%colocate%';
Ensure these are set correctly: * 
disable_colocate_relocate
should be 
false
. * 
tablet_sched_slot_num_per_path
(default is 2). If this is 0, no repairs will happen. Recommendation: Force a "Relink" If the backends are all alive and the config is correct but it's still stuck, the most effective way to jumpstart a Colocation Group after an 
OPTIMIZE
is to temporarily break and re-establish the link. This forces the FE to re-examine every tablet: 1. Find the group name (run 
SHOW PROC '/colocation_group';
). 2. Unlink and Relink:
Copy code
sql
    -- 1. Remove the table from the group
    ALTER TABLE <your_table_name> SET ("colocate_with" = "");

    -- 2. Wait 10 seconds

    -- 3. Add it back to the group
    ALTER TABLE <your_table_name> SET ("colocate_with" = "your_group_name");
Summary: Your tablets are in a "waiting room." Use 
SHOW PROC '/cluster_balance/pending_tablets'
to see the exit reason in the 
ErrMsg
column. References • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SHOW_CONFIG.md • administration/management/resource_management/Replica.md • using_starrocks/Colocate_join.md References • using_starrocks/Colocate_join.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky wait txn before 2312320 to be finished. ErrMsg all these kinds of messages. With different transaction IDs
r
This 
ErrMsg
confirms that your tablet repair is being blocked by a "zombie" or slow transaction. In StarRocks, a tablet cannot be moved or repaired while it is participating in an open transaction (like a load job, 
INSERT
, or 
OPTIMIZE
). The system is essentially saying: "I want to move this tablet, but I'm waiting for Transaction ID 2312320 to finish so I don't corrupt the data." Since you previously ran 
OPTIMIZE
, it likely created these transactions, and they may be "stuck" or still cleaning up. Step 1: Identify the Transaction Find out what that transaction is doing. Run this command (replace the ID with the one from your error):
Copy code
sql
-- Replace with the ID from your ErrMsg
SHOW TRANSACTION WHERE ID = 2312320;
What to look for in the output: * 
TransactionStatus
: If it is 
PREPARE
, it is still running. If it is 
COMMITTED
but not 
VISIBLE
, it is stuck in the final publishing phase. * 
Label
: This will often tell you what started it (e.g., 
optimize_...
or 
insert_...
). * 
Coordinator
: The FE node handling the transaction. Step 2: Clear the Blockage If the transaction is very old (e.g., started hours ago when you ran 
OPTIMIZE
), you need to clear it: A. If it's a Load/Insert job: If the 
Label
looks like a load job, use the 
CANCEL
command:
Copy code
sql
-- Use the Label found in the previous step
CANCEL LOAD FROM your_db_name WHERE LABEL = "your_transaction_label";
B. If it's a "Zombie" Transaction: If 
SHOW TRANSACTION
shows the status as 
VISIBLE
or 
ABORTED
but the tablet repair is still waiting for it, the FE metadata might be lagging. 1. Check 
SHOW PROC '/cluster_balance/running_tablets';
. If there are truly 0 running, but 
pending
tablets still show the same 
txn
error, the FE's Tablet Scheduler is stuck on a stale lock. 2. Restart the Master FE: This is the most reliable way to clear stale transaction locks in the scheduler without losing data. Step 3: Why did 
OPTIMIZE
do this? 
OPTIMIZE
is a massive transaction. If you have many tablets (you had 271), it creates a transaction for the entire rewrite. If the rewrite was interrupted or the "Publish" phase (making data visible) is taking a long time, the tablets are "locked." Summary Checklist: 1. Run 
SHOW TRANSACTION WHERE ID = [ID];
for several different IDs in your error list. 2. Check if they all belong to the same 
Label
(likely your 
OPTIMIZE
job). 3. If they are old, try to 
CANCEL LOAD
using that label. 4. If they are already finished/aborted but the repair is still blocked, restart your Leader/Master FE to reset the scheduler. Do not delete the table. Deleting a table while it has active transactions can sometimes cause metadata inconsistencies in older versions of StarRocks. Clear the transactions first. References • using_starrocks/Colocate_join.md • knowledge/trouble_shooting/fe_deadlock_problems.md
m
@Rocky I identified an unhealthy tablet and dropped the partition it belonged to.
Copy code
Item,Number
balance_stat,1
cluster_load_stat,1
working_slots,8
sched_stat,1
priority_repair,0
pending_tablets,0
running_tablets,0
history_tablets,1000
all_tablets,0
This is now my cluster balance stats but colocation_group still says 
IsStable
is false?
Copy code
`CREATE TABLE IF NOT EXISTS ` + requestsDBName + `.` + requestsTableName + `(
		received_hour DATETIME NOT NULL,
		auction_id STRING NOT NULL,
		imp_key STRING NOT NULL,
		received DATETIME NOT NULL
	)
	PRIMARY KEY (received_hour, auction_id, imp_key)
	PARTITION BY date_trunc('hour', received_hour)
	DISTRIBUTED BY HASH (auction_id) BUCKETS 128
	ORDER BY (received, auction_id)
	PROPERTIES (
		"enable_persistent_index" = "true",
		"partition_live_number" = "24",
        "replication_num" = "1"
	);`
This DDL is executed every time my application is started up but it shouldn't affect the table since it already exists right? Should I just force mark it as stable?
r
Since your 
all_tablets
count is now 0 (likely because you dropped the only partition containing data), the Tablet Scheduler has nothing left to "repair," which is why 
pending_tablets
dropped to 0. However, the Colocation Group metadata in the FE often stays in the 
Unstable
state until it is explicitly re-validated or manually reset. Since there are no tablets to trigger an automatic "success" signal, it remains stuck. Here is how to resolve this and answers to your DDL question: 1. Does the DDL affect the table? No. 
CREATE TABLE IF NOT EXISTS
is a no-op if the table already exists. It will not update the schema, partitions, or properties (like 
colocate_with
) of the existing table. * Caution: If your application logic relies on this DDL to "ensure" the table is colocated, but the table was originally created without the 
colocate_with
property (or if it was removed), this DDL will not fix it. You should check the current status:
Copy code
sql
    SHOW CREATE TABLE requests;
If 
"colocate_with"
is missing from the 
PROPERTIES
, the table is not part of a group, even if the application thinks it is. 2. How to fix 
IsStable = false
(with 0 tablets) Since the cluster is "clean" (0 tablets), you can safely force the group back to a stable state. This is often necessary when you've manually deleted data to fix a corruption. Step A: Get the Group ID Run this to find your 
db_id
and `grp_id`:
Copy code
sql
SHOW PROC '/colocation_group';
Look for your group name and note the IDs (e.g., 
10005.10008
). Step B: Force Mark as Stable Use the HTTP API to clear the unstable flag. This tells the FE to stop treating this group as "under repair":
Copy code
bash
curl -XPOST -u root:password 'http://<FE_IP>:<FE_HTTP_PORT>/api/colocate/group_stable?db_id=<your_db_id>&group_id=<your_grp_id>'
(Replace 
root:password
with your credentials and use the leader FE's IP). 3. Why did 
IsStable
stay false? When you have 
replication_num
= 1, the system is extremely sensitive. 1. When you dropped the partition, the "unhealthy" tablets were removed. 2. The Colocation Group was already marked "Unstable" because it noticed the missing/misplaced replicas. 3. Because there is now no data, the FE balancer doesn't "run" on that group to check if it's fixed—it just sees an empty, previously broken group. 4. Recommendation for the DDL Since you are using Colocation, it is highly recommended to explicitly include the 
colocate_with
property in your DDL so that if the table is ever dropped and recreated, it remains in the group:
Copy code
sql
CREATE TABLE IF NOT EXISTS ...
PROPERTIES (
    "colocate_with" = "requests_group", -- Add this
    "enable_persistent_index" = "true",
    "partition_live_number" = "24",
    "replication_num" = "1"
);
Final Check: After running the 
group_stable
API call, run 
SHOW PROC '/colocation_group';
again. 
IsStable
should now be 
true
. When you start inserting data again (creating new partitions), StarRocks will place the new tablets according to the colocation bucket map correctly. References • administration/management/FE_configuration.md • using_starrocks/Colocate_join.md
The 
500 Internal Server Error
and the message 
For input string: "<tel:44945.6511660|44945.6511660>"
indicate that your shell or terminal interpreted the numbers as a phone number and added "tel:" formatting, or you have an HTML-encoded character (
&
) in your command. To fix this, you need to: 1. Use only the second part of the Group ID for the 
group_id
parameter. 2. Clean the URL to remove the Slack/auto-formatting and the 
&
. Corrected Command Given your Group ID is 
<tel:44945.6511660|44945.6511660>
, the 
db_id
is 44945 and the 
group_id
is 6511660. Run this command:
Copy code
bash
curl -XPOST -u root: '<http://starrocks-core-services-fe-1.us-east-1.stackadapt:8030/api/colocate/group_stable?db_id=44945&group_id=6511660>'
(If you have a password for root, include it after the colon, e.g., 
-u root:password
). What went wrong in your previous attempt: * The 
group_id
parameter: In the API, 
group_id
expects just the numeric ID (the part after the dot), not the full 
db.group
string. * The `&`: This is an HTML entity for 
&
. When using 
curl
in a terminal, you must use a literal 
&
to separate query parameters. * The 
<tel:...>
error: This is a common issue when copy-pasting from chat applications like Slack. The application sees a number with a dot/hyphen and wraps it in a "telephone" link, which your terminal then sends to StarRocks. StarRocks tries to turn the string 
<tel:44945...>
into a number and fails. After running the command: Check the status again in StarRocks:
Copy code
sql
SHOW PROC '/colocation_group';
IsStable
should now be 
true
. Since your tablet count is 0, any new partitions created by your application's DDL will now be placed correctly according to the group's bucket map. References • using_starrocks/Colocate_join.md ) References • using_starrocks/Colocate_join.md
m
@Rocky Now that I forced colocation_group IsStable to be true, how do I verify there's no colocation mismatch and joins will work very quickly?
r
To verify that your colocation group is working correctly and that your joins will be "local" (fast), you should use the following two methods. 1. The Source of Truth: 
EXPLAIN
Plan The most reliable way to verify a colocation join is to look at the execution plan of your query. Run your join query with 
EXPLAIN
before it:
Copy code
sql
EXPLAIN SELECT * 
FROM table1 
JOIN table2 ON table1.auction_id = table2.auction_id;
What to look for in the output: * 
colocate: true
: Look for the 
HASH JOIN
node. If colocation is working, it will explicitly say 
colocate: true
. * No 
EXCHANGE
nodes: In a colocation join, there should be no 
EXCHANGE
(shuffle) nodes between the table scans and the join. The data stays on the same BE, so no network transfer is needed. * 
colocate: false
: If it says 
false
, it will usually provide a reason, such as: * 
reason: group is not stable
(The FE still thinks things are moving). * 
reason: join condition does not match distribution columns
(You are joining on columns other than 
auction_id
). 2. Verify Bucket Mapping via 
SHOW PROC
 Since you force-marked the group as stable, you should verify that the FE's internal mapping of buckets to BEs is actually consistent across the group. 1. Check the Group status:
Copy code
sql
    SHOW PROC '/colocation_group';
2. Inspect the Bucket Map: Find your 
GroupId
from the previous command (e.g., 
<tel:44945.6511660|44945.6511660>
) and run:
Copy code
sql
    -- Replace with your specific IDs
    SHOW PROC '/colocation_group/44945.6511660';
This will show you a table where each row is a 
BucketIndex
and the columns show which 
BackendId
holds that bucket. * Success state: Every table in the group must share this exact same map. Since they are in the same group, StarRocks enforces this. * What to verify: Ensure that for each bucket, there are enough active Backends listed to satisfy your 
replication_num
. Since your 
replication_num
is 1, you should see exactly one Backend ID per bucket. 3. Potential "Mismatch" Gotchas Even if 
IsStable
is true, a join might not be colocated if: * Data Types: The 
auction_id
in Table A is a 
VARCHAR
but in Table B it is a 
STRING
(or different lengths). Even if the values look the same, different types can result in different hash distributions. * Bucket Count: If one table was created with 
BUCKETS 128
and another with 
BUCKETS 64
, they cannot be colocated. * Join Key: You must join on exactly the columns defined in 
DISTRIBUTED BY HASH(...)
. If you distributed by 
(auction_id)
, but your join is 
ON a.auction_id = b.auction_id AND a.other_col = b.other_col
, it will still work as a colocate join (the extra condition is just a filter), but if you join only on 
other_col
, it will fail to colocate. Summary If 
EXPLAIN
shows 
colocate: true
, your setup is perfect. If it says 
false
, check that your join keys match your distribution keys exactly and that the data types are identical. References • using_starrocks/Colocate_join.md • using_starrocks/skew_join_v2.md
m
@Rocky colocation_group IsStable reverted back to false again. If I wait for the 24 hour TTL of table to be over, will it be fixed? All fresh data then
r
Waiting for the 24-hour TTL to expire might fix the issue, but only if the instability is caused specifically by the tablets in those older partitions. If the root cause is deeper (like a cluster topology change), it will keep reverting to 
false
. Here is the breakdown of why it’s flipping back and how to check: 1. Why it keeps reverting to 
false
 The FE runs a background process (Tablet Scheduler) every 20 seconds. Even if you "force" it to stable via the API, the next scan will see that the actual location of your tablets does not match the Colocation Bucket Map. Because you are using 
replication_num = 1
, there is zero redundancy. If the single BE that is "supposed" to own Bucket #5 is different from where the tablet actually lives, the group is marked unstable. 2. Will the 24h TTL fix it? * Scenario A (Yes): If you recently moved/added/removed BEs, the "Bucket Map" might be trying to place data on nodes that are now gone or different. If the "misplaced" tablets only exist in your old partitions, then once they are dropped (TTL), the group will have 0 tablets again. At that point, new partitions will be created according to the map, and it will stay stable. * Scenario B (No): If your FE's internal Colocation Map is pointing to a BE that is offline, decommissioned, or disk-full, StarRocks will be forced to place new tablets on "wrong" BEs. This means your fresh data will immediately trigger the 
IsStable = false
flag again. 3. How to verify the root cause Before waiting 24 hours, run these two checks: A. Check for other tables in the group A Colocation Group is shared. If any table in that group is unstable, all of them are.
Copy code
sql
-- Replace with your IDs from earlier
SHOW PROC '/colocation_group/44945.6511660';
Look at the list of tables. If there are tables other than the one you dropped, check their tablet health. B. Check the FE logs This is the most definitive way to see why it's reverting. Search your 
fe.log
for the Group ID:
Copy code
bash
grep "<tel:44945.6511660|44945.6511660>" fe.log | grep "Colocate"
You will likely see a message like: * 
replica is missing on backend [10002]
* 
tablet [12345] is not on the target backend
4. Recommended Action If you don't want to wait 24 hours to find out: 1. Check your Backends: Run 
SHOW BACKENDS;
. If any BE is 
Alive = false
, that is your culprit. With 
replication_num = 1
, the colocation group cannot be stable if any BE in its map is down. 2. Relocate the Group: If your BEs have changed, you can tell the group to re-calculate its map based on the currently active BEs:
Copy code
bash
    # This triggers a re-balance of the group map
    curl -XPOST -u root: 'http://<FE_IP>:8030/api/colocate/balance?db_id=44945&group_id=6511660'
Note: Since you have 1 replica, StarRocks will physically move your existing data to the new nodes to match the map. Warning: Running with 
replication_num = 1
is very risky for Colocation. If a single BE fails, the entire group stays unstable indefinitely until the data is manually dropped or the BE comes back. Forced Map is updated. Considerately points elsewhere. If you have the space, 
replication_num = 3
 is highly recommended to allow the system to self-heal without human-free "self-heal" stability issues. References • using_starrocks/Colocate_join.md
m
@Rocky What if I currently only have one table in that colocation group. Does that cause IsStable to be false?
r
No, having only one table in a colocation group does not cause it to be unstable. In fact, every colocation group starts with a single table when it is first created. If 
IsStable
is reverting to 
false
for a single-table group, it is because of where the data is, not how many tables are in the group. Why a single-table group becomes unstable: 1. Backend (BE) Mismatch: StarRocks has an internal "Bucket Map" for the group that says: "Bucket 1 must be on BE 1001, Bucket 2 must be on BE 1002...". If your table's tablets are physically located on different BEs than what this map dictates, the FE marks it as unstable. 2. The 
replication_num = 1
Factor: Since you have no replicas, the system has no "wiggle room." If a BE goes down or if the cluster was recently scaled/rebalanced, the tablets might be stuck on the "wrong" nodes according to the colocation map. 3. Tablet Movement: If the FE is currently trying to move those tablets to the "correct" BEs to match the map, the status will remain 
false
until the move is finished. Will the 24h TTL fix it? Yes, likely. If you only have one table and you are waiting for its partitions to be deleted via TTL: * Once all partitions are gone, the table will have zero tablets. * With zero tablets, there is nothing "misplaced." * When your application creates a new partition, StarRocks will look at the Colocation Group's map first and place the new tablets on the "correct" BEs from the start. * The group should then stay 
Stable = true
. How to confirm if it's a "Ghost" problem: Run this to see which specific buckets are causing the instability:
Copy code
sql
SHOW PROC '/colocation_group/44945.6511660';
If you see 
BackendIds
assigned to buckets, but 
SHOW TABLETS FROM your_table
shows the tablets are on different Backends, that is why it is flipping to 
false
. Summary: You don't need a second table. The instability is just a sign that your current table's physical data distribution doesn't match the group's "master plan." Letting the old data age out (TTL) is a clean way to "reset" this distribution without manual intervention. References • using_starrocks/Colocate_join.md • administration/management/FE_configuration.md References • using_starrocks/Colocate_join.md • administration/management/FE_configuration.md
m
@Rocky What do you mean by tablets are on different backends? Tablets naturally are distributed across all backends.
r
You are right that in a normal StarRocks table, tablets are distributed across all backends to balance the load. However, for a Colocation Group, there is a much stricter rule called the Bucket Map. Think of it this way: * Normal Tables: StarRocks just wants your 100 tablets to be "spread out" generally. If Tablet #5 is on BE-A or BE-B, it doesn't matter much as long as the cluster is balanced. * Colocation Tables: StarRocks creates a "Master Plan" (the Bucket Map) that says: * Bucket 0 MUST be on BE-1. * Bucket 1 MUST be on BE-2. * ...and so on. What "Tablets are on different backends" actually means: When your group is 
IsStable = false
, it means there is a conflict between the Master Plan and Physical Reality. For example: 1. The Master Plan says Bucket #5 must live on BE-1. 2. But for some reason (a past node failure, a disk being full, or a manual move), the Physical Tablet for Bucket #5 is currently sitting on BE-2. Even if you only have one table, if that table's tablets aren't exactly where the "Master Plan" says they should be, the FE marks the group as Unstable. Why does this happen? * BE Changes: If you added or removed a Backend recently, StarRocks updated the "Master Plan" to include the new node, but it hasn't finished physically moving your existing tablets to the new locations yet. * Replication Num = 1: This is the biggest reason for your specific case. Because you have no replicas, StarRocks cannot "seamlessly" switch to a backup. If the BE that was supposed to hold a bucket is busy or was briefly down when the tablet was created, the tablet might have been placed on a "wrong" BE just to keep the data alive. Now, the Colocation Group is "stuck" because the tablet is in the wrong place. How the 24h TTL fixes it: When you wait for the 24-hour TTL: 1. The "wrongly placed" tablets are eventually deleted along with the old data. 2. The table becomes empty. 3. When the next batch of data arrives and creates a new partition, StarRocks looks at the Master Plan first. 4. It places those new tablets exactly where they are supposed to be (e.g., Bucket #5 goes to BE-1). 5. Now Physical Reality matches the Master Plan, and 
IsStable
flips to 
true
and stays there. Summary "Distributed across backends" is good, but "Colocated" means "Distributed across backends according to a very specific, unchanging map." Any deviation from that map—even by a single tablet—results in 
IsStable = false
. References • using_starrocks/Colocate_join.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • table_design/StarRocks_table_design.md
m
@Rocky How do I map bucket index to tabletID?
r
To map a Bucket Index (the logical bucket number used by the colocation group) to a Tablet ID (the physical data unit), you need to use the StarRocks 
PROC
system. Standard SQL commands like 
SHOW TABLET
do not explicitly display the bucket index. Here is the step-by-step process: Step 1: Get the required IDs You need the Database ID, Table ID, Partition ID, and Index ID. You can find these with a single command:
Copy code
sql
-- Replace 'your_table' with your table name
SHOW TABLET FROM your_table LIMIT 1;
Look at the DbId, TableId, PartitionId, and IndexId columns in the result. Step 2: Access the PROC path Once you have the IDs, run the following command to see the tablet distribution for that specific partition:
Copy code
sql
-- Format: SHOW PROC '/dbs/<DbId>/<TableId>/partitions/<PartitionId>/<IndexId>';
SHOW PROC '/dbs/44945/6511660/partitions/6511661/6511662';
Step 3: Interpret the Result In the output of the command above: 1. Row Order is the Bucket Index: The rows are returned in order of their logical bucket index, starting from 0. * The 1st row is Bucket Index 0. * The 2nd row is Bucket Index 1. * The nth row is Bucket Index n-1. 2. TabletId Column: The first column in this result is the TabletId. Example Mapping If you see the following in your 
SHOW PROC
output: TabletId | ReplicaId | BackendId | ... 50001 | 50002 | 10001 | ... 50005 | 50006 | 10002 | ... * Bucket 0 maps to Tablet 50001. * Bucket 1 maps to Tablet 50005. Why this matters for Colocation If your Colocation Group is unstable, you can compare this mapping against the Colocation Group Map:
Copy code
sql
-- Replace with your group ID
SHOW PROC '/colocation_group/44945.6511660';
If the Colocation Group Map says Bucket 0 should be on BE 1003, but your table's Tablet 50001 (which is Bucket 0) is actually on BE 1001, the group will stay unstable until StarRocks moves that tablet. Note: If you are waiting for a 24-hour TTL to expire, once those old partitions are deleted, the "misaligned" tablets will disappear, and new tablets will be created according to the correct map automatically. References • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • faq/operation_maintenance_faq.md
m
@Rocky TableId is not in show tablets from requests
r
Apologies, you are absolutely correct. The 
SHOW TABLET FROM <table_name>
command returns physical replica details but omits the internal metadata IDs like 
TableId
or 
DbId
. To map a Bucket Index to a Tablet ID, use this two-step process to get the correct metadata IDs first: Step 1: Get the Metadata IDs Since 
SHOW TABLET FROM
doesn't show them, you can find them by querying a specific tablet's metadata: 1. Run 
SHOW TABLET FROM <table_name> LIMIT 1;
to get any TabletId. 2. Run 
SHOW TABLET <the_tablet_id_you_just_got>;
The output of the second command will look like this: DbName | TableName | PartitionName | IndexName | ***DbId*** | ***TableId*** | ***PartitionId*** | ***IndexId*** example_db | my_table | p1 | my_table | 1001 | 1002 | 1003 | 1004 Step 2: Use the PROC system to see the Bucket Map Now that you have those IDs, you can look at the logical bucket structure:
Copy code
sql
-- Format: SHOW PROC '/dbs/<DbId>/<TableId>/partitions/<PartitionId>/<IndexId>';
SHOW PROC '/dbs/1001/1002/partitions/1003/1004';
Step 3: Map the Index to the Tablet In the resulting table: 1. Row Position = Bucket Index: The logical Bucket Index starts at 0 for the first row, 1 for the second row, and so on. 2. TabletId Column: The first column shows the physical TabletId assigned to that bucket. TabletId | ReplicaId | BackendId | ... 80001 | 80002 | 1001 | (Row 1 = Bucket Index 0) 80005 | 80006 | 1002 | (Row 2 = Bucket Index 1) Why this is useful for your Colocation issue: If your group is unstable, you can now check if Bucket 0 (Tablet 80001) is actually sitting on the Backend that the Colocation Group map expects. * Check Table's physical location: Use the 
SHOW PROC
above. * Check Group's expected location: 
SHOW PROC '/colocation_group/<GroupId>';
If the 
BackendId
in the first command doesn't match the 
BackendId
in the second command for the same index, that is why 
IsStable
is false. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • administration/management/FE_configuration.md References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • sql-reference/information_schema/partitions.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md
m
@Rocky Will colocation join work if IsStable is false from colocation_group
r
Yes, the query will still work and return correct results, but it will not perform a Colocate Join. Instead, StarRocks will automatically degrade the operation to a normal Join (typically a Shuffle Join or Broadcast Join). What happens when `IsStable = false`: 1. Loss of Locality: The core benefit of a Colocate Join is that data from both tables is already on the same node, so no data needs to be sent over the network. When the group is unstable, some tablets are in the "wrong" place, so StarRocks can no longer guarantee that the matching data is on the same machine. 2. Automatic Shuffle: To ensure the query still returns correct results, StarRocks will add an EXCHANGE step to the execution plan. It will shuffle the data across the network to align it on the fly, just like a standard table. 3. Performance Impact: Because of the network overhead (shuffling), the query will be significantly slower than a true Colocate Join, especially for large datasets. How to verify this: You can see this happening in real-time by running an 
EXPLAIN
on your query:
Copy code
sql
EXPLAIN SELECT * FROM tableA JOIN tableB ON tableA.key = tableB.key;
* If Stable: You will see 
colocate: true
under the 
HASH JOIN
node, and there will be no 
EXCHANGE
nodes for the join. * If Unstable: You will see 
colocate: false
and a reason like 
group is not stable
. You will also see 
EXCHANGE
nodes added to the plan. Summary When 
IsStable
is false, the "Colocation" is effectively disabled for that group until the background balancing/repair process finishes and the tablets are back in their "Master Plan" locations. References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/data-manipulation/EXPLAIN.md • administration/management/FE_configuration.md References • using_starrocks/Colocate_join.md
m
@Rocky
Copy code
3:HASH JOIN
  |  join op: INNER JOIN (BROADCAST)
"  |  colocate: false, reason: "
  |  equal join conjunct: 2: auction_id = 519: auction_id
r
When you see 
colocate: false
with an empty 
reason: ""
and a join operation like 
BROADCAST
or 
SHUFFLE
, it means the query planner has determined that the tables are not eligible for a Colocate Join before even checking if the group is stable. Here are the most common reasons why this happens: 1. The tables are not in the same Colocation Group For a Colocate Join to work, both tables must be explicitly assigned to the same group in their table properties. * Check: Run 
SHOW CREATE TABLE tableA
and 
SHOW CREATE TABLE tableB
. * Requirement: Both must have the exact same property: 
PROPERTIES ("colocate_with" = "your_group_name")
. 2. The Join keys are not the Distribution keys A Colocate Join only works if you join on the columns used for bucketing (
DISTRIBUTED BY HASH(...)
). * Your Snippet: You are joining on 
auction_id
. * Check: Verify that both tables are bucketed by 
auction_id
. If Table A is bucketed by 
auction_id
but Table B is bucketed by 
user_id
, a Colocate Join is impossible. 3. Distribution Key Data Type Mismatch Even if both columns are named 
auction_id
, their data types must be identical. * Check: If Table A uses 
INT
and Table B uses 
BIGINT
(or even 
VARCHAR
vs 
INT
), the hash values will not align, and the planner will disable colocation. 4. Group Configuration Mismatch Tables in the same group must have identical bucketing configurations: * Bucket Count: Both tables must have the same number of 
BUCKETS
. * Replication Num: Both tables must have the same number of replicas. * Check: Run 
SHOW PROC '/colocation_group';
to see if the tables you expect are listed together under the same 
GroupId
. 5. Join strategy override The snippet shows 
INNER JOIN (BROADCAST)
. If the optimizer thinks one table is small enough to broadcast, it might pick that, but usually, it would still prefer a Colocate Join if available. If it says 
colocate: false
, it's because the physical distribution (the "Colocation Group" metadata) isn't there. ────────── Troubleshooting Steps: 1. Verify Group Membership:
Copy code
sql
    SHOW PROC '/colocation_group';
Find your group and check the 
TableIds
column. If one of your table IDs is missing, it’s not in the group. 2. Verify Join Columns: Ensure 
auction_id
is the first and only distribution column if you are joining on just that column. If you distribute by 
(auction_id, site_id)
, you must join on both for colocation to work. 3. Check Variable: Ensure colocation hasn't been disabled for your session:
Copy code
sql
    SELECT @@disable_colocate_join; -- Should be 0 (false)
Summary: If the reason is blank, the planner doesn't even see these tables as "Colocated" tables. The "group is not stable" reason only appears when the tables are in a group, but the data is currently being moved or repaired. References • administration/management/FE_configuration.md • using_starrocks/Colocate_join.md • sql-reference/System_variable.md
m
@Rocky Does adding BE node cause temporary colocation_group IsStable to be false
r
Yes, adding a BE node will cause 
IsStable
to become 
false
 for your Colocation Groups, but it is temporary. Here is exactly why and what happens: 1. Automatic Rebalancing When you add a new BE node, StarRocks detects that the cluster's load is no longer balanced. To utilize the new hardware, the ColocateTableBalancer background process will begin moving some "Buckets" (logical units of a colocation group) from existing nodes to the new node. 2. Why it becomes "Unstable" Unlike normal tables where replicas move individually, a Colocation Group moves all replicas of a Bucket Index together across all tables in that group. * As soon as the balancer decides that "Bucket Index 5" should move from BE-1 to the new BE-3, the entire Colocation Group is marked 
IsStable = false
. * It stays unstable until all tables in that group have successfully moved their respective data for that bucket index to the new target node and the metadata is updated. 3. Impact during this period * Query Performance: While 
IsStable
is false, any query joining tables in that group will degrade to a normal Join (Shuffle or Broadcast). You will see 
colocate: false
in the 
EXPLAIN
plan. * Duration: The group remains unstable until the data migration (replica repair/balance) is 100% complete for all buckets in that group. 4. How to manage it If you are adding nodes during a high-traffic period and don't want your joins to degrade immediately, you can temporarily disable the automatic balancing of colocation groups:
Copy code
sql
-- Disable automatic colocation balancing (run on FE)
ADMIN SET FRONTEND CONFIG ("tablet_sched_disable_colocate_balance" = "true");
Note: If you disable it, the new BE node will remain empty for those colocation tables until you set the config back to 
false
. Once you re-enable it, the rebalancing will start and the group will become 
Unstable
again until finished. How to check progress: You can monitor the status using:
Copy code
sql
SHOW PROC '/colocation_group';
Look for the 
Unstable
groups. Once the data movement finishes, the system will automatically mark them as 
IsStable = true
, and your Colocate Joins will resume automatically. References • using_starrocks/Colocate_join.md • administration/management/FE_configuration.md References • using_starrocks/Colocate_join.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md
m
@Rocky I just added 4 more ec2 instances as my BE nodes. How do I register them with the FE node? I have SSH access
r
To register your 4 new EC2 instances as Backend (BE) nodes, you need to execute a SQL command from a MySQL client connected to your Leader FE node. Here is the step-by-step process: 1. Identify the Private IPs Since you are on EC2, use the Private IP of each instance (e.g., 
172.31.x.x
). Do not use Public IPs, as internal cluster communication should stay within the AWS VPC for performance and security. 2. Run the Registration Command Connect to your StarRocks cluster using a MySQL client (usually on port 9030):
Copy code
bash
mysql -h <FE_IP> -P9030 -uroot
Then, run the 
ALTER SYSTEM
command. You can add all 4 nodes in a single statement:
Copy code
sql
ALTER SYSTEM ADD BACKEND 
  "172.31.1.10:9050",
  "172.31.1.11:9050",
  "172.31.1.12:9050",
  "172.31.1.13:9050";
* 9050 is the default 
heartbeat_service_port
. If you changed this in your 
be.conf
, use your custom port. 3. Verify the Status After running the command, check if the FE can successfully "heartbeat" with the new nodes:
Copy code
sql
SHOW BACKENDS\G
Look at the 
Alive
column. It should turn 
true
within a few seconds. ────────── Important Checklist for EC2: Before the nodes will show as 
Alive
, ensure the following are configured on the 4 new instances: * Security Groups: Ensure the FE and the existing BEs can reach the new instances on these default ports: * 
9050
(Heartbeat) * 
9060
(BE Port) * 
8040
(HTTP Port) * 
8060
(BRPC Port) * priority_networks: If your EC2 instances have multiple network interfaces, you must set this in 
be.conf
so the BE picks the correct Private IP:
Copy code
conf
    # In be/conf/be.conf
    priority_networks = 172.31.0.0/16
* Start the Service: If you haven't started the process yet on the new nodes, run:
Copy code
bash
    cd /path/to/starrocks/be/bin
    ./start_be.sh --daemon
What happens next? Once the nodes are 
Alive
, StarRocks will automatically notice the new capacity. As discussed earlier, your Colocation Groups will temporarily show 
IsStable = false
as the system begins moving data to these new nodes to balance the load. References • deployment/deploy_manually.md • administration/management/Scale_up_down.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_BACKENDS.md • faq/Deploy_faq.md References • deployment/deploy_manually.md • administration/stargo.md • administration/management/Scale_up_down.md • faq/Deploy_faq.md • administration/management/BE_blacklist.md
Open in Slack
PreviousNext
Powered by