http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> after running the following in star...
# questions-and-troubleshooting
f
@Rocky after running the following in starrocks: 
ALTER TABLE voicemails DISTRIBUTED BY HASH(account_key) BUCKETS 10;
I get the following error:
Copy code
SHOW ALTER TABLE OPTIMIZE;
+----------+------------+---------------------+---------------------+-----------------------------------------------+---------------+-----------+-------------------------------------------------------------+----------+---------+
| JobId    | TableName  | CreateTime          | FinishTime          | Operation                                     | TransactionId | State     | Msg                                                         | Progress | Timeout |
+----------+------------+---------------------+---------------------+-----------------------------------------------+---------------+-----------+-------------------------------------------------------------+----------+---------+
| 16742920 | voicemails | 2026-10-23 01:53:01 | 2026-10-23 01:54:35 | com.starrocks.sql.ast.OptimizeClause@5e5ea3a9 | 16712146      | CANCELLED | optimize table failed p20261016 rewrite task execute failed | 53       | 86400   |
+----------+------------+---------------------+---------------------+-----------------------------------------------+---------------+-----------+-------------------------------------------------------------+----------+---------+
how do i fix this?
r
The error message 
optimize table failed p20261016 rewrite task execute failed
indicates that the background job responsible for redistributing your data (the "rewrite task") failed specifically for the partition named 
p20261016
. In StarRocks 3.2+, changing the distribution (re-bucketing) is treated as an asynchronous 
OPTIMIZE
operation. It creates new tablets, reads data from the old ones, and writes it to the new ones based on the new HASH key and bucket count. How to Fix This 1. Check BE Logs (Most Important) The 
SHOW ALTER TABLE OPTIMIZE
output provides a high-level summary, but the specific reason for the failure (e.g., Out of Memory, Disk Full, or RPC timeout) is recorded in the Backend (BE) logs. * Log into your BE nodes and search the 
<http://be.INFO|be.INFO>
or 
be.WARNING
logs for the partition name 
p20261016
or the JobId 
16742920
. * Look for errors like 
failed to write tablet
, 
memory limit exceeded
, or 
no space left on device
. 2. Check Disk Space Re-bucketing requires temporary disk space to store the new version of the data before the old version is deleted. Ensure your BE nodes have at least 2x the size of the largest partition in free disk space. 3. Run the Operation Partition by Partition If the table is large, running the 
ALTER
on the entire table can be resource-intensive. You can isolate the problem or reduce load by altering one partition at a time:
Copy code
sql
ALTER TABLE voicemails 
PARTITIONS (p20261016) 
DISTRIBUTED BY HASH(account_key) BUCKETS 10;
If this succeeds, you can proceed with other partitions. If it fails again, the error in the BE logs will be much easier to find. 4. Check for Concurrent Loads Heavy data ingestion during an 
ALTER TABLE
operation can cause resource contention or transaction conflicts. If possible, pause data loading for this table and retry the command. 5. Verify BE Health Check if any BE nodes restarted during the process:
Copy code
sql
SHOW BACKENDS;
If a BE crashed (likely due to OOM during the rewrite), the 
LastStartTime
will be very recent. You may need to increase the memory limit for the BE or the 
memory_limitation_per_thread_for_schema_change
configuration (though usually, the global 
mem_limit
is the bottleneck). Summary of Next Steps 1. Locate the error in 
storage/log/be.INFO
on your backend nodes. 2. Retry the operation on just the failing partition: 
ALTER TABLE ... PARTITIONS (p20261016) ...
. 3. If you find a specific error like "Memory limit exceeded" in the logs, consider increasing the BE memory or performing the task during a low-traffic window. References • faq/Sql_faq.md • faq/operation_maintenance_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md
f
@Rocky the error is 
Limit: 6957847018. Mem usage has exceed the limit of BE
. is it safe to restart the backend with more memory and retry?
r
Yes, it is safe to restart the Backend (BE) with more memory and retry the operation. In fact, increasing memory is the standard solution for the 
Mem usage has exceed the limit of BE
error. The error message indicates that the BE process reached its overall memory limit (in your case, approximately 6.5 GB), which is quite low for a StarRocks node performing data redistribution. Why this happened When you run 
ALTER TABLE ... DISTRIBUTED BY ...
, StarRocks performs an asynchronous "Optimize" task. This process: 1. Creates new tablets for the new distribution. 2. Reads data from old tablets into memory. 3. Sorts and re-hashes the data. 4. Writes it to the new tablets. This "rewrite" task is memory-intensive. If your BE node doesn't have enough headroom, the process will trigger its own safety limits and cancel the job to prevent the node from crashing. Recommended Steps to Fix 1. Increase BE Memory If your physical machine has more RAM available, you should increase the limit in 
be.conf
. * Locate 
be.conf
: Usually in the 
conf/
directory of your BE installation. * Adjust 
mem_limit
: This parameter sets the maximum memory the BE can use. You can set it as a percentage or a specific value.
Copy code
bash
    # Example: Set to 80% of total physical RAM
    mem_limit = 80%
    # Or set a hard limit (e.g., 16GB)
    # mem_limit = 16G
* Restart the BE process: It is safe to restart. If you have multiple replicas, the cluster will remain available. If it's a single-node setup, the service will be temporarily offline. 2. Tune Schema Change Memory (Optional) If you still see failures after increasing the BE limit, you may need to increase the memory allowed specifically for the redistribution task: * In 
be.conf
, increase: 
memory_limitation_per_thread_for_schema_change = 4
(default is 2GB). This gives the re-bucketing task more room to sort data in memory. 3. Retry the Operation Once the BE is back online with more memory, simply run the original command again:
Copy code
sql
ALTER TABLE voicemails DISTRIBUTED BY HASH(account_key) BUCKETS 10;
The system will start a new job. You can monitor its progress using:
Copy code
sql
SHOW ALTER TABLE OPTIMIZE;
Quick Safety Checks * Concurrent Loads: If you are running heavy data ingestion while altering the table, the load processes compete for memory. Try pausing ingestion temporarily during the 
ALTER
if memory is tight. * Disk Space: Ensure you have at least 1.5x to 2x the table size in free disk space, as StarRocks keeps the old data until the new distribution is fully committed. References • administration/management/BE_configuration.md • faq/Sql_faq.md • administration/management/resource_management/Memory_management.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md References • administration/management/BE_configuration.md • faq/Sql_faq.md • faq/operation_maintenance_faq.md • developers/build-starrocks/Build_in_docker.md
f
@Rocky is there a way to roll back the changes? writes are currently blocked
r
The 
ALTER TABLE ... DISTRIBUTED BY
operation is an asynchronous process. If the state is already 
CANCELLED
 (as shown in your 
SHOW ALTER TABLE OPTIMIZE
output), the system has technically already "rolled back" because the new bucket configuration was never applied, and the table should still be using its original distribution. If writes are currently blocked, it is likely because the table metadata is still stuck in a "busy" state (like 
ALTER
) or there is a leftover lock. 1. Check for Active Jobs Even if one job is cancelled, ensure there aren't other concurrent jobs (like a Column Change or Rollup) preventing writes:
Copy code
sql
SHOW ALTER TABLE COLUMN;
SHOW ALTER TABLE ROLLUP;
SHOW ALTER TABLE OPTIMIZE; -- Verify no other job is in 'RUNNING' or 'PENDING'
If you find a running job that is blocking you, you can stop it manually:
Copy code
sql
CANCEL ALTER TABLE OPTIMIZE FROM voicemails;
2. Verify Tablet States If the table state is "not normal," writes will be rejected. Check if any tablets are stuck in the 
ALTER
state:
Copy code
sql
SHOW TABLET FROM voicemails WHERE State="ALTER";
If this returns rows, it means some tablets haven't finished cleaning up from the failed operation. Usually, the Frontend (FE) will clean these up automatically after a few minutes. 3. Check for Write Errors When you say "writes are blocked," what is the specific error message? * "Table's state is not normal": The table is still internally marked as undergoing a schema change. Wait 5–10 minutes for the FE to finish the cleanup. * "Waiting for the tablet to be ready": Replicas might be inconsistent. Check 
SHOW BACKENDS
to ensure all nodes are alive. * "Transaction conflict": If a job was cancelled but its transaction hasn't timed out, new writes might conflict. 4. How to "Roll Back" Since StarRocks uses a "shadow tablet" approach for re-bucketing, there is no manual "undo" command needed. If the job fails or is cancelled: 1. The new (shadow) tablets are deleted. 2. The original tablets remain active. 3. The table metadata remains unchanged. To restore write access immediately: 1. Restart the FE (Leader): If the table is stuck in an 
ALTER
state indefinitely due to a metadata bug, restarting the Leader FE will force a refresh of the table state. 2. Clear the state: In some versions, you can try running a "no-op" alter to clear the flag, though this is rarely needed. 3. Check FE Logs: Look for 
[TABLE_STATE_NOT_NORMAL]
in the 
fe.log
to see why the FE still thinks the table is busy. Summary: If the job is 
CANCELLED
, wait 5 minutes for the background cleanup to finish. If writes are still blocked after that, check 
SHOW TABLET FROM voicemails
to ensure no tablets are stuck in the 
ALTER
state. References • faq/Sql_faq.md • sql-reference/sql-statements/table_bucket_part_index/CANCEL_ALTER_TABLE.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/trouble_shooting/fe_deadlock_problems.md • administration/management/compaction.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/KILL_ANALYZE.md • sql-reference/sql-statements/cbo_stats/DROP_ANALYZE.md
Open in Slack
PreviousNext
Powered by