http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> After copying a lot of tables into ...
# questions-and-troubleshooting
s
@Rocky After copying a lot of tables into new colocated tables, I notice a sustained baseline of network I/O between BE pod (shared-nothing mode in k8s) and it does not seem like there's compaction backlog. Can help me troubleshoot this. This has been going on for approximately 1 hour after the copying was completed.
r
Sustained network I/O after a large data migration or table copy (even when compaction is caught up) usually points to background tablet rebalancing or automatic statistics collection. Since you are using colocated tables, the system is likely moving entire "buckets" to satisfy colocation constraints and cluster load balance. Here is how to troubleshoot this: 1. Check for Colocation Group Rebalancing Colocated tables are balanced at the bucket level rather than the replica level. If the cluster determines the new tables have made the distribution "unstable" or "unbalanced," it will migrate buckets between BEs. * Check the Colocation Status: Run this command on your FE host to see if any groups are currently unstable (migrating):
Copy code
bash
    curl http://<fe_host>:<fe_http_port>/api/colocate
Look for the 
"unstableGroups"
list in the JSON output. If it contains group IDs, StarRocks is actively moving data to rebalance those groups. * Check Tablet Balance Stat: You can check if a specific table is considered balanced:
Copy code
sql
    SHOW PROC '/dbs/<db_id>/<table_id>/partitions/<partition_id>';
Look at the 
TabletBalanceStat
column. It will show if the table is 
balanced: true
or 
false
and if there are 
expectedBes
that differ from 
currentBes
. 2. Check Active Tablet Scheduling (Cloning) If the system is moving data, you will see active "Clone" tasks. These tasks use network bandwidth to replicate tablets to new BE nodes. * Check Running Tasks:
Copy code
sql
    SHOW PROC '/cluster_balance/running_tablets';
* Check Pending Tasks:
Copy code
sql
    SHOW PROC '/cluster_balance/pending_tablets';
If you see many tasks with the type 
BALANCE
or 
REPAIR
, this confirms the sustained network I/O is due to data being shuffled across the pods. 3. Check Automatic Statistics Collection After copying "a lot of tables," the CBO (Cost-Based Optimizer) will notice these tables lack statistics and trigger automatic collection. This involves scanning the data and sending samples/summaries to the FE. * Check Analyze Status:
Copy code
sql
    SHOW ANALYZE STATUS;
Look for tasks in the 
RUNNING
state. Large tables can trigger "Full" or "Sampled" collection tasks that generate significant read and network I/O shortly after data is loaded. 4. Verify Replica Consistency Checks StarRocks performs background consistency checks. While usually low-impact, if you just created many new replicas via copying, the FE might be performing version checks across BEs. * Check FE logs (
fe.log
) for 
Tablet scheduler
or 
Colocate balancer
entries to see if it is repeatedly trying to fix a specific group. Recommendations * If it's Rebalancing: This is normal and will stop once the cluster reaches a balanced state. If you need to stop it immediately to save bandwidth, you can temporarily disable it:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("disable_colocate_balance" = "true");
    ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");
* If it's Statistics: You can check if 
enable_statistic_collect
is 
true
in your FE config. If stats are the cause, the I/O will stop once all new tables are analyzed. References • using_starrocks/Cost_based_optimizer.md
s
@Rocky Are there SQL variant of the curl commands
r
Yes, there are SQL equivalents for checking colocation and cluster balance using the 
SHOW PROC
syntax. These commands allow you to query the internal state of the cluster directly via your MySQL client. 1. SQL Equivalent for Colocation Status Instead of the 
/api/colocate
curl command, you can use:
Copy code
sql
SHOW PROC '/colocation_group';
* What to look for: Check the 
IsStable
column. If it is 
false
, StarRocks is currently rebalancing that specific group to satisfy the colocation constraints. * Columns: 
GroupId
, 
GroupName
, 
TableIds
, 
BucketsNum
, 
ReplicationNum
, 
DistCols
, 
IsStable
. 2. SQL Equivalent for General Cluster Balance To see why the network I/O might be high across the entire cluster (not just for colocated tables), you can use the cluster balance procedures: * View overall balance status:
Copy code
sql
    SHOW PROC '/cluster_balance';
* View active data movement (Cloning tasks): These are the tasks actually using the network.
Copy code
sql
    SHOW PROC '/cluster_balance/running_tablets';
* View queued rebalancing tasks:
Copy code
sql
    SHOW PROC '/cluster_balance/pending_tablets';
3. SQL Equivalent for Statistics Tasks If the network I/O is caused by the FE gathering statistics for the new tables, check the "Analyze" status:
Copy code
sql
SHOW ANALYZE STATUS;
Look for tasks in the 
RUNNING
state. Summary Table Goal | Curl Command | SQL Equivalent Colocation Health | 
/api/colocate
| 
SHOW PROC '/colocation_group';
Active Rebalancing | N/A | 
SHOW PROC '/cluster_balance/running_tablets';
Tablet Health | 
/api/tablet_health
| 
SHOW PROC '/statistic';
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
s
@Rocky Everything like good. 
disable_colocate_balance
and 
disable_balance
to false changed nothing. Is this cluster_balance output good ?
Copy code
SR StressLoad [root]> SHOW PROC '/cluster_balance';
+-------------------+--------+
| Item              | Number |
+-------------------+--------+
| balance_stat      | 1      |
| cluster_load_stat | 1      |
| working_slots     | 6      |
| sched_stat        | 1      |
| priority_repair   | 0      |
| pending_tablets   | 0      |
| running_tablets   | 0      |
| history_tablets   | 493    |
| all_tablets       | 0      |
+-------------------+--------+
9 rows in set (0.022 sec)
r
The 
SHOW PROC '/cluster_balance'
output confirms that StarRocks is not currently performing any data rebalancing or replica repairs: * 
running_tablets: 0
: There are no active data movement tasks (clones) happening. * 
pending_tablets: 0
: There are no tasks waiting in the queue. * 
history_tablets: 493
: This shows that 493 tablets were recently moved or repaired, likely as part of the initial colocation placement or balancing, but that process is now finished. Since internal balancing is ruled out, a sustained baseline of network I/O between BE pods after a large copy operation is most commonly caused by one of the following background processes: 1. Automatic Statistics Collection (Most Likely) After you copy "a lot of tables," StarRocks triggers automatic 
ANALYZE
tasks to collect statistics for the Cost-Based Optimizer (CBO). For large tables, this involves scanning data on the BEs and sending sampled or full data across the network. * How to check:
Copy code
sql
    SHOW ANALYZE STATUS;
Look for any tasks with a 
State
of 
RUNNING
. If you see many tasks there, this explains the network I/O. It will stop automatically once the initial analysis of the new tables is complete. 2. Primary Key Index Persistence If your new tables are using the Primary Key storage engine, StarRocks builds and persists primary key indexes. While this is primarily disk I/O, there is metadata synchronization and potentially some RPC traffic involved if there are many small write batches or pending metadata updates. * How to check: Look at the BE metrics for 
starrocks_be_primary_key_index_mem_bytes
. If this is high or fluctuating, index building is active. 3. Check for Long-Running Transactions or Queries Even if "copying was completed," check if any 
INSERT
or 
PUBLISH
transactions are still technically open or if a large background query (like a Materialized View refresh) is running. * Check transactions: 
SHOW PROC '/transactions';
* Check queries: 
SHOW PROC '/current_queries';
4. BE-Level I/O Profiling If the above SQL commands don't show active tasks, you can use the BE's internal profiler to see exactly what "Tag" the I/O is associated with (e.g., 
QUERY
, 
LOAD
, 
COMPACTION
). Run this on a BE pod (or via 
admin execute
if you have the BE IDs):
Copy code
bash
# Sample 20 seconds of I/O activity
curl http://<be_host>:8040/ioprofile?seconds=20
In the output, look at the TAG column. * If you see 
LOAD
, there is still ingestion activity. * If you see 
PKINDEX
, it's the Primary Key index. * If you see 
QUERY
, it's likely statistics collection or a background query. Summary The 
cluster_balance
output is "clean" (good), meaning the cluster is structurally stable. The I/O is likely Statistics Collection. I recommend checking 
SHOW ANALYZE STATUS
first. If it is running, you can either let it finish or temporarily disable it:
Copy code
sql
SET GLOBAL enable_statistic_collect = false;
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • using_starrocks/Colocate_join.md • administration/management/resource_management/Replica.md
s
@Rocky 
SHOW ANALYZE STATUS;
-> none running
r
Since 
SHOW PROC '/cluster_balance'
shows 0 running/pending tablets and 
SHOW ANALYZE STATUS
is empty, we can rule out standard rebalancing and automatic statistics. Given this is a shared-nothing cluster and you see network I/O between BE pods, it suggests data or metadata is being synchronized or compared across replicas. Here are the remaining likely causes and how to verify them: 1. Background Replica Consistency Checks StarRocks periodically performs background consistency checks to ensure all replicas of a tablet are identical. After a large data copy, the system may be verifying the newly created replicas. * How to check:
Copy code
sql
    SHOW PROC '/statistic';
Look at the 
InconsistentTabletNum
column. If this is non-zero, StarRocks is actively checking or repairing replicas. * How to verify timing: Check the 
LstConsistencyCheckTime
in 
SHOW TABLET <id>
for one of your new tablets to see if it was recently checked. 2. BE-Level I/O Tagging (The "Smoking Gun") The most direct way to identify the source of the I/O is to use the BE's internal profiler. This will tell you exactly which internal StarRocks component is generating the traffic. Run this command from inside a BE pod or from a machine that can reach the BE's web port (default 8040):
Copy code
bash
curl --location-trusted <http://localhost:8040/ioprofile?seconds=20>
Look at the 
TAG
column in the output: * 
CLONE
: Data is still being moved for balance/repair (even if 
cluster_balance
missed the snapshot). * 
COMPACTION
: In shared-nothing, this is usually local disk I/O, but if you have custom storage, it might show here. * 
PKINDEX
: If you are using Primary Key tables, the BEs may be synchronizing or persisting the primary key indexes for the new data. * 
QUERY
: If this shows up, some background process (like a Materialized View refresh or an internal query) is running. 3. Lingering "Publish" or Transaction Phase If you used 
INSERT INTO ... SELECT
to copy the tables, the data might be loaded, but the cluster could be finishing the Publish version phase, where metadata is updated across all replicas. * How to check:
Copy code
sql
    SHOW PROC '/transactions';
Look for any transactions in the 
PREPARE
or 
COMMITTED
state that haven't transitioned to 
VISIBLE
. 4. BE Tablet Reporting After a massive metadata change (like adding many tables/tablets), BEs report their state to the FE. If the volume of tablets is extremely high (e.g., millions of tablets), this reporting can cause a sustained baseline of RPC traffic. * FE Config to check: 
tablet_stat_report_interval_second
(default is 60s). If the I/O matches this interval, it's metadata reporting. Summary of next steps: 1. Run the 
ioprofile
 curl command on one of the BEs. The 
TAG
column will give you the definitive answer. 2. Check 
SHOW PROC '/statistic'
 for any consistency check activity. 3. Check 
SHOW PROCESSLIST
 to ensure no copy tasks are still "hanging" or performing cleanup. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/BE_configuration.md • knowledge/trouble_shooting/trouble_shooting_scripts.md References • knowledge/trouble_shooting/trouble_shooting_scripts.md • administration/management/compaction.md • loading/loading_introduction/troubleshooting_loading.md • faq/Sql_faq.md
s
@Rocky
Copy code
% curl '<http://10.193.11.19:8040/ioprofile?seconds=20>'
    Tablet        TAG       read_bytes      ops      write_bytes      ops      total_bytes      ops
  45474844    PKINDEX          7694617      111          7339660        1         15034277      112
  45474868    PKINDEX          5319477       62          5107288        1         10426765       63
  45474860    PKINDEX           400106      114          2998144        3          3398250      117
  45474852    PKINDEX           359390      103          2614556        3          2973946      106
  45474884    PKINDEX           234837       80          2431212        1          2666049       81
  45474868       LOAD          1060085       24          1186501        1          2246586       25
  45474876    PKINDEX           123897       38          2080332        1          2204229       39
  45665573      QUERY          2112799      118                0        0          2112799      118
  45474860       LOAD           984458       24          1105068        1          2089526       25
  45665488      QUERY          2078915       83                0        0          2078915       83
How do you interpret this
r
The 
ioprofile
output is a "snapshot" of what the Backend (BE) storage engine is doing at a low level. It confirms that your cluster is heavily occupied with Primary Key Index operations. Here is the breakdown of what those tags and numbers mean: 1. 
TAG
Interpretations * 
PKINDEX
(The Main Culprit): This stands for Primary Key Persistent Index activity. * StarRocks Primary Key tables maintain an index to map every primary key to its physical location on disk. * The 
write_bytes
you see are the BEs persisting this index to disk. The 
read_bytes
occur when the BE has to load parts of the index to check for duplicates or update the mapping. * Why this causes network I/O: Even though 
PKINDEX
shows as disk I/O here, in a multi-replica (shared-nothing) cluster, every replica must independently build and update this index during the "Publish" phase or during background Compaction. The coordination between replicas and the FE for version management generates sustained network traffic. * `LOAD`: This indicates active data ingestion. Even if your main script "finished," there are often lingering background "Publish" tasks where the BEs are finalising the data version and moving it from memory to the final storage format. * `QUERY`: These are read operations. If you aren't running queries, these are likely internal metadata scans or the FE gathering information about the new tablets. 2. Why is this happening "after" the load? In Primary Key tables, work continues after the 
INSERT
returns "Success": 1. Persistent Index Building: If you loaded a large volume of data, the BEs are now writing the index files to disk so they don't have to keep everything in memory. 2. Compaction: After a massive load, you have many small "rowsets" (files). StarRocks immediately starts background Compaction to merge these into larger, more efficient files. For Primary Key tables, Compaction requires updating the PK index, which explains the high 
PKINDEX
I/O. 3. Is the output "good"? The output is normal for a cluster that has just received a massive amount of data into Primary Key tables. It shows the system is healthy and doing its "housekeeping" (building indexes and compacting data). Recommendation To see if Compaction is the root cause of the sustained background work (which is very likely), run:
Copy code
sql
-- Check the overall compaction status across the cluster
SHOW PROC '/compactions';
Look at the 
Finish
vs 
Error
columns and the number of active tasks. If the cluster is busy compacting, the network and disk I/O will remain high until the "Compaction Score" drops to a healthy level (usually < 10). Summary: Your cluster is busy managing the Primary Key Persistent Index and likely performing background Compaction. This is expected behavior after a large data load and will subside once the data is fully merged and indexed. References • administration/management/BE_configuration.md • using_starrocks/Primary_key_table.md • administration/management/compaction.md References • administration/management/compaction.md • administration/management/BE_configuration.md • using_starrocks/Primary_key_table.md • knowledge/trouble_shooting/trouble_shooting_scripts.md References • best_practices/query_tuning/query_profile_operator_metrics.md
Open in Slack
PreviousNext
Powered by