http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> we are using a medallion architectu...
# questions-and-troubleshooting
c
@Rocky we are using a medallion architecture where the base table is a JSON column plus a few identifiers, a base view that shreds the JSON, then some nested MVs. We would like to index some high cardinality identifiers in the base table JSON. Is that possible?
r
Yes, it is possible to index high-cardinality identifiers stored within a JSON column in StarRocks, although you cannot create an index directly on the JSON field itself. Since you are using a medallion architecture (Base Table -> View -> MVs), you have two primary ways to optimize this in the base table: 1. Generated Columns + Bloom Filter (Recommended) This is the most effective way to handle high-cardinality identifiers in JSON for point lookups (e.g., 
WHERE id = '123'
). * How it works: You define a "Generated Column" that extracts the identifier from the JSON. StarRocks stores this as a physical column and keeps it in sync automatically. * Indexing: Once extracted, you can create a Bloom Filter index on this generated column. Bloom filters are specifically designed for high-cardinality columns to quickly skip data blocks that don't contain the value. Example:
Copy code
sql
CREATE TABLE base_table (
    id BIGINT,
    raw_json JSON,
    -- Extract the identifier as a generated column
    user_id_ext VARCHAR(64) AS json_string(json_query(raw_json, "$.user_id"))
) 
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    -- Add a Bloom Filter to the extracted identifier
    "bloom_filter_columns" = "user_id_ext"
);
_Note: StarRocks' optimizer will automatically rewrite queries that use 
json_query(raw_json, "$.user_id")
to use the indexed 
user_id_ext
column instead._ 2. Flat JSON (Automatic Optimization) Starting from version 3.3 (enabled by default in v4.0), StarRocks supports a Flat JSON feature. * How it works: When you enable 
flat_json.enable = "true"
, StarRocks automatically detects frequently accessed fields within the JSON during data ingestion and "flattens" them into internal sub-columns. * Performance: These sub-columns benefit from column pruning and vectorized execution, which significantly speeds up filtering on high-cardinality fields without requiring you to manually manage generated columns. * Configuration:
Copy code
sql
    PROPERTIES (
        "flat_json.enable" = "true"
    )
Which one should you choose? * Use Generated Columns + Bloom Filter if you have a specific, high-priority identifier that is used in almost all 
WHERE
clauses for point lookups. This gives you the most explicit control and the best "needle in a haystack" search performance. * Use Flat JSON if you have many different identifiers or fields in the JSON and you want a general performance boost across all of them without manually defining every column. Rocky's Tip: Since you're using MVs downstream, remember that if you filter the base table using these indexes, your MV refresh (which often involves scanning the base table) will also become much faster! References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md • best_practices/query_tuning/schema_tuning.md • table_design/indexes/Prefix_index_sort_key.md
c
@Rocky can we add "flat_json.enable" = "true" to an existing table, and can we combine both approaches?
r
Yes, you can add this property to an existing table, and you can absolutely combine both approaches. Here is how they work together: 1. Adding 
flat_json.enable
to an Existing Table Starting from StarRocks v4.0, you can enable this at the table level using an 
ALTER TABLE
statement:
Copy code
sql
ALTER TABLE base_table SET ("flat_json.enable" = "true");
Key behaviors to note: * Version 3.3: The feature exists but is controlled globally via BE configurations (
enable_json_flat
) rather than per-table. * Historical Data: Enabling this does not immediately rewrite your old data. New data being ingested will be flattened immediately. Historical data will gradually be flattened as compaction occurs or if you perform a manual update/overwrite. * No Schema Change: Unlike adding a column, this doesn't change your table's visible schema; it's an internal storage optimization. 2. Combining Both Approaches You can use both Flat JSON and Generated Columns on the same table. This is actually a very powerful "best of both worlds" strategy: * Flat JSON (The Safety Net): It automatically optimizes the top ~100 most frequent fields in your JSON. This ensures that even if you don't manually index a field, it still gets decent performance (vectorized execution and column pruning). * Generated Column + Bloom Filter (The Precision Tool): For your specific high-cardinality identifiers, Flat JSON alone might not be enough for "point lookup" performance. By creating a Generated Column with a Bloom Filter index, you ensure that queries looking for a specific ID can skip nearly all irrelevant data blocks entirely. Example of the combined setup:
Copy code
sql
-- 1. Enable automatic flattening for all common JSON fields
ALTER TABLE base_table SET ("flat_json.enable" = "true");

-- 2. Add a specific generated column for your highest-priority ID
ALTER TABLE base_table ADD COLUMN user_id_idx VARCHAR(64) 
AS json_string(json_query(raw_json, "$.user_id"));

-- 3. Add a Bloom Filter to that specific column for O(1)-style lookups
ALTER TABLE base_table SET ("bloom_filter_columns" = "user_id_idx");
Summary Recommendation * Use Generated Columns + Bloom Filters for the 2–3 "critical" identifiers you use for filtering most often. * Enable Flat JSON for everything else in the JSON blob so that ad-hoc queries on other fields remain performant without you having to manage dozens of extra columns. Rocky's Tip: After enabling Flat JSON, you can run 
SELECT flat_json_meta(your_json_column) FROM your_table[_META_];
to see which fields StarRocks has decided to flatten internally! References • using_starrocks/Flat_json.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/generated_columns.md • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md References • using_starrocks/Flat_json.md • sql-reference/sql-statements/generated_columns.md
c
@Rocky my alter table for the generated column is going very slowly and I do not see much CPU utilization. To mitigate conflicts, I added three days of partitions into the future, but at the rate it is going (8/5928 after one hour) it looks like it will take more than 3 days. This is a shared data cluster.
r
Adding a generated column to a large JSON table in a shared-data (cloud-native) cluster is a heavy operation. While StarRocks optimizes this by only writing the new column data (and not rewriting the whole table), it still requires reading the source JSON column for every row, evaluating the expression, and writing the result back to object storage. The slowness and low CPU utilization you are seeing are almost certainly due to concurrency throttling and I/O wait from object storage. By default, StarRocks is very conservative with the number of background threads it uses for schema changes to avoid impacting query performance. Here is how you can speed this up: 1. Increase Schema Change Parallelism (Most Impactful) The default number of threads for schema change is only 3. For a table with nearly 6,000 tablets, this is the primary bottleneck. You can increase this dynamically without restarting the cluster. On every CN (Compute Node): Run this to increase the number of tablets processed in parallel on the backend:
Copy code
sql
UPDATE information_schema.be_configs 
SET value = "16" -- Increase from 3 to 16 or higher based on your CN core count
WHERE name = "alter_tablet_worker_count";
On the FE (Frontend): If the FE is slow at dispatching these tasks, you can increase the handler threads:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("alter_max_worker_threads" = "16");
2. Verify Progress and Health You can check exactly what is happening with the job to ensure it hasn't run into errors or specific slow tablets:
Copy code
sql
SHOW ALTER TABLE COLUMN\G
* State: Should be 
RUNNING
. * Progress: If the number of finished tablets is increasing faster after the config change above, you're on the right track. 3. Why is it slow despite adding future partitions? Adding future partitions prevents write conflicts (so your loaders don't fail), but it doesn't speed up the processing of the existing 5928 tablets. StarRocks must iterate through every historical tablet to compute and store the new generated column. 4. Shared-Data Specific Considerations (Lake Tables) In a shared-data cluster, every "write" of a new column involves a "Publish Version" task and interaction with object storage (S3/GCS/Azure). * Small Files: If your JSON data was ingested in many small batches, the schema change has to open many small files. * Lake Optimization: In StarRocks 3.3+, ensure 
enable_fast_schema_evolution = true
is set in your FE config (it is usually on by default). This allows StarRocks to handle the metadata portion of the schema change much faster. Summary Checklist to Mitigate: 1. Up the Workers: Set 
alter_tablet_worker_count
to at least 16 or 24 on all CNs. 2. Monitor CPU: Watch the CPU utilization after increasing the workers. If it’s still low, you can push the worker count even higher (e.g., 32). 3. Wait for Compaction: If the table has a very high "version count" (many small files), the schema change will be slower. Running a manual compaction before a massive schema change can sometimes help, but it's usually better to just increase the worker threads for the schema change itself. Rocky's Tip: Since this is a shared cluster, keep an eye on the I/O throughput to object storage. If you set the worker count too high, you might hit the rate limits of your cloud provider's object store! References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/generated_columns.md
c
@Rocky I now have 15 CNs of 32 core each. I would like to mazimize the speed of the ALTER command , so 16 is not enough. WHat do you suggest? This is not a production cluster but it is ingesting a large Kafka topic (32 partitions, 2 routine loads)
r
Since this is a non-production cluster and you want to maximize the speed of the 
ALTER
command on your 480-core (15 CN × 32 cores) cluster, you can be much more aggressive with your settings. The low CPU utilization indicates that the system is "waiting" rather than "working." This is usually because the FE isn't dispatching enough tasks or the CNs aren't processing enough tablets concurrently. 1. Maximize Worker Threads (CN Level) With 32 cores per CN, setting 
alter_tablet_worker_count
to 16 only uses half your capacity. You can safely push this to match your core count since you aren't worried about production traffic. Update all 15 CNs:
Copy code
sql
UPDATE information_schema.be_configs 
SET value = "32" 
WHERE name = "alter_tablet_worker_count";
Why: This allows each CN to process 32 tablets simultaneously. Across 15 CNs, you will have 480 tablets being processed at any given moment. 2. Maximize Dispatcher Parallelism (FE Level) Even if your CNs are ready to work, the FE (Frontend) might be the bottleneck if it isn't scheduling the tasks fast enough. Update FE Configuration:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("alter_max_worker_threads" = "32");
Why: This increases the number of threads the FE uses to manage and dispatch the 5,928 tablet tasks to the CNs. 3. Lake-Specific Optimization (Version 3.3+) If you are on StarRocks 3.3 or higher, there is a specific optimization for shared-data (lake) tables that reduces the metadata overhead during schema changes.
Copy code
sql
ADMIN SET FRONTEND CONFIG ("lake_enable_tablet_creation_optimization" = "true");
Note: This is most effective if you were to restart the job, but it's good practice to have enabled for large metadata operations in shared-data mode. 4. Why the Routine Load matters Your Routine Load (32 partitions) is creating new "versions" (data files) every few seconds. * The Conflict: Every time the Routine Load commits, it creates a new version. The 
ALTER
job has to process all existing versions. * Recommendation: If you can afford a temporary ingestion gap, PAUSE the Routine Loads. This stops the "moving target" of new data versions being added while the 
ALTER
job is trying to catch up. Once the 
ALTER
is done, resume them; the generated column will be populated automatically for the backlog of data. 5. Monitoring the "Real" Speed After applying the above, monitor the progress again:
Copy code
sql
SHOW ALTER TABLE COLUMN;
Look at the 
CreateTime
vs. 
FinishTime
for the tablets. If the number of finished tablets starts climbing by hundreds per minute, you've cleared the bottleneck. Summary for your specific hardware: * CNs: 
alter_tablet_worker_count
= 32 * FE: 
alter_max_worker_threads
= 32 * Ingestion: Pause Routine Load temporarily to maximize I/O and CPU for the schema change. Rocky's Tip: In shared-data mode, the bottleneck can often shift to the S3/Object Storage request rate limits (TPS). If you see errors like 
Internal Error
or 
S3 Slow Down
in the BE logs, dial the 
alter_tablet_worker_count
back down to 24. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/compaction.md • best_practices/query_tuning/schema_tuning.md References • best_practices/query_tuning/schema_tuning.md
c
@Rocky do I need to restart backends after changing 
alter_tablet_worker_count
r
No, you do not need to restart the backends (CNs) after changing 
alter_tablet_worker_count
. It is a dynamic (mutable) configuration. Since you are using a cluster with 15 CNs, here is the most efficient way to apply these changes and ensure they have taken effect: 1. Apply the BE Change (No Restart Needed) The SQL command you used is the most convenient way to update all 15 nodes at once:
Copy code
sql
UPDATE information_schema.be_configs 
SET value = "32" 
WHERE name = "alter_tablet_worker_count";
To verify it worked: Run 
SELECT * FROM information_schema.be_configs WHERE name = 'alter_tablet_worker_count';
and check that the 
value
column shows 
32
for all nodes. 2. A Note on the FE Change For the FE configuration 
alter_max_worker_threads
, StarRocks documentation currently lists this as not mutable (immutable) in some versions. * Try running: 
ADMIN SET FRONTEND CONFIG ("alter_max_worker_threads" = "32");
* If the command returns an error or if you check 
ADMIN SHOW FRONTEND CONFIG;
and the value hasn't changed, it means a restart of the FE is required for that specific parameter. However, the BE change (
alter_tablet_worker_count
) is usually the much bigger bottleneck, so even if you can't restart the FE right now, increasing the BE workers will still provide a massive speedup. 3. Recommended "Aggressive" Strategy Since you have 32 cores per node and are in a non-prod environment: 1. Set BE workers to 32: (as done above). 2. Pause Routine Loads: This is critical. Schema changes in StarRocks (especially in shared-data mode) have to reconcile "versions." Every 10-20 seconds, your Routine Load creates a new version. Pausing them removes the "moving target" and lets the 
ALTER
job finish much faster. 3. Check Task Queue: If you still see low CPU after upping the workers to 32, check 
SHOW ALTER TABLE COLUMN;
. If the 
State
is 
FINISHED
for most tablets but the total count isn't moving, it might be stuck on a few very large tablets or waiting on FE scheduling. Summary: No restart for BE. Just update the config and the change is immediate. References • administration/management/BE_configuration.md • faq/Deploy_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • faq/Deploy_faq.md • administration/management/BE_configuration.md
c
@Rocky at 
alter_tablet_worker_count = 32
and 
alter_max_worker_threads = 128
(I boosted it past your advice) half of my CNs remain idle. Should I add more FE threads?
r
The fact that half of your CNs are idle even after boosting the BE-side workers to 128 suggests the bottleneck has officially moved to the Frontend (FE) Scheduler. In StarRocks, the FE is responsible for dispatching tablet tasks and, more importantly, committing/finalizing them once the CNs finish. If the FE cannot schedule or commit tasks as fast as your 15 CNs can process them, the CNs will sit idle waiting for new work. 1. The FE Dispatch Bottleneck The most likely culprit is 
alter_max_worker_threads
. * Default value: 4 threads. * The Problem: With only 4 threads in the FE managing a queue of 5,928 tablets, the FE cannot keep the "pipeline" full for 15 CNs (each trying to run 128 concurrent tasks). * The Catch: This parameter is not mutable. You cannot change it with 
ADMIN SET CONFIG
. You must add it to your 
fe.conf
and restart the FE. Recommendation: 1. Add 
alter_max_worker_threads = 64
to 
fe.conf
. 2. Add 
alter_max_worker_queue_size = 8192
to 
fe.conf
(to accommodate your ~6k tablets). 3. Restart the FE. 2. Check for Tablet "Skew" in Assignment Even though this is a shared-data cluster, the FE assigns specific tablet tasks to specific CNs. If your tablets are not evenly distributed in the FE's metadata, or if some CNs are being prioritized, you'll see idle nodes. Run this to see if the tasks are actually being distributed:
Copy code
sql
-- Check which BEs/CNs are currently handling the most ALTER tasks
SELECT BE_ID, COUNT(*) 
FROM information_schema.be_cloud_native_compactions 
WHERE STATUS = 'RUNNING' 
GROUP BY BE_ID; 
-- Note: While this command is for compactions, schema changes in Lake 
-- tables often follow similar scheduling logic in the logs.
If you see tasks only on 7-8 nodes, the FE is likely throttling the "concurrency per node" or total global concurrency. 3. Shared-Data (Lake) Specific Limits In shared-data mode, there is often a hidden limit on how many concurrent "Remote" tasks (I/O to S3/Object Store) can be active to prevent overwhelming the object store or the FE's transaction manager. Check if these are visible/changeable in your version (use 
ADMIN SHOW FRONTEND CONFIG
): * 
lake_alter_tablet_parallelism
: Some versions use this specifically for lake tables. * 
lake_max_concurrent_tasks
: Total tasks the FE will allow across the cluster. 4. Why 128 might be "Too High" Setting 
alter_tablet_worker_count
to 128 on a 32-core machine is very aggressive. While it might show low CPU, it causes massive lock contention within the CN's tablet manager and creates thousands of simultaneous S3 connections. * Suggested Sweet Spot: Dial it back to 32 or 48. You want the FE to give you tasks more efficiently, rather than having one CN try to do 128 things at once. Summary of Next Steps: 1. Restart FE: This is the most likely "fix." Set 
alter_max_worker_threads = 64
in 
fe.conf
and restart. 2. Verify Object Storage: Ensure you aren't hitting S3/Blob storage request limits. (Check CN logs for 
SlowDown
or 
403
errors). 3. Pause Routine Load: If you haven't yet, pause the 2 Routine Loads. Every time they commit a new version, the 
ALTER
job has to do extra work to "catch up" to the newest version of that tablet. This is a common cause of schema changes "stalling" at the end. Rocky's Tip: If you restart the FE, the 
ALTER
job will resume from where it left off. You won't lose the progress you've already made! References • faq/operation_maintenance_faq.md • table_design/data_distribution/Data_distribution.md • faq/Sql_faq.md • administration/management/resource_management/Replica.md
The math suggests you have hit a global task bottleneck on the Frontend (FE). Because you have 15 CNs and each has 32 workers (
alter_tablet_worker_count = 32
), your cluster is capable of processing 480 tablets simultaneously (15 * 32). However, your FE is currently capped at 128 threads. 1. Why 128 FE Threads is not enough The FE's 
alter_max_worker_threads
acts as a global "concurrency cap" for the entire cluster's schema change operations. * The Math: If the FE only has 128 threads to manage and finalize tasks, it can only keep 4 CNs fully busy (128 threads / 32 workers per CN = 4 nodes). * The Result: The remaining 11 CNs will sit idle or only pick up a few tasks intermittently. This perfectly explains why "half" (or more) of your nodes are idle. Recommendation: Set 
alter_max_worker_threads
to at least 512 (slightly above your 480 worker total) to ensure the FE can saturate every single thread on every single CN. 2. IMPORTANT: Is the change actually active? In most StarRocks versions (including 3.x), 
alter_max_worker_threads
is a static (immutable) parameter. * If you ran 
ADMIN SET FRONTEND CONFIG ("alter_max_worker_threads" = "128");
and did not restart the FE, it likely did not take effect. * Verify it: Run 
ADMIN SHOW FRONTEND CONFIG LIKE 'alter_max_worker_threads';
. If the 
Value
is still 
4
(the default), your "boost" never happened. * Fix it: You must add 
alter_max_worker_threads = 512
to your 
fe.conf
and restart the FE. 3. Shared-Data (Lake) Specific Tuning Since you are on a Lake (shared-data) cluster, the schema change uses a task framework that has its own limits. Check these two additional FE parameters: * 
lake_compaction_max_tasks
: Even though this says "compaction," Lake schema change often relies on the same internal task manager. _ Default: 
-1
(which is 
number_of_CNs _ 16
). For you, this is 240. _ _Suggestion:* Set to 512 to match your worker threads. _ _Command:* 
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "512");
(This one is mutable). * 
create_tablet_worker_count
(CN Side): Schema changes create new tablet versions. If this is too low on the CN, the "creation" phase will bottleneck before the "conversion" phase starts. _ _Suggestion:* Push this to 32 on all CNs via 
UPDATE information_schema.be_configs
. 4. The "Routine Load" Factor Since you are ingesting a large Kafka topic, the FE is also busy managing "Publish Version" tasks for the Routine Load. * Every time a Routine Load task commits, it competes for the same FE metadata locks used by the 
ALTER
job. * If you haven't yet, PAUSE the Routine Load. This frees up the FE to focus 100% of its metadata management on finishing the 5,928 tablets. Summary Checklist to Maximize Speed: 1. FE: Add 
alter_max_worker_threads = 512
to 
fe.conf
and restart FE. 2. FE: Run 
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "512");
. 3. CNs: Ensure 
alter_tablet_worker_count
is 32 (don't go to 128, it causes too much I/O contention). 4. CNs: Set 
create_tablet_worker_count
to 32. 5. Ingestion: Pause Routine Load until the 
ALTER
job state reaches 
FINISHED
. Rocky's Tip: Check the FE log (
fe.log
) for "Reach max worker threads" or "RejectedExecutionException." If you see those, it's the smoking gun that your FE thread pool is full! References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • administration/management/compaction.md • faq/Sql_faq.md
c
@Rocky I have all of above set and still some nodes are idle. Is there something else that could be bottlenecking?
r
If you have already increased the FE threads and BE workers and still see idle nodes, the issue is likely that several of the parameters you changed are immutable (static), meaning they did not actually take effect unless you restarted the services. Here are the hidden bottlenecks that commonly cause idle nodes during a large schema change in StarRocks: 1. The "Immutable" Trap (Check your status) Both 
alter_max_worker_threads
(FE) and 
alter_max_worker_queue_size
(FE) are not mutable. Even if you ran an 
ADMIN SET CONFIG
command, the FE will ignore it until a restart. * Action: Run 
ADMIN SHOW FRONTEND CONFIG LIKE 'alter_max_worker%';
. * What to look for: If the 
Value
for 
alter_max_worker_threads
is still 
4
(default), your boost to 128 never happened. You must add these to 
fe.conf
and restart the FE. 2. BE-Side: 
update_schema_worker_count
 While 
alter_tablet_worker_count
handles the data conversion, the initial "handshake" and task setup on the CN side are handled by a different pool called 
update_schema_worker_count
. * Default: 3 threads. * The Issue: This is also not mutable. If this is stuck at 3, the CN can only "start" 3 schema change tasks at a time, regardless of how many workers are available to do the work. * Action: Add 
update_schema_worker_count = 32
to your 
be.conf
and restart the CNs. 3. FE-Side: 
alter_max_worker_queue_size
 You mentioned you have 5,928 tablets. * Default Queue: 4,096. * The Issue: If your tablet count (5,928) exceeds the queue size (4,096), the FE may stop scheduling new tasks once the queue is full. This would leave nodes idle once they finish their current batch because the FE isn't "feeding" them fast enough. * Action: Set 
alter_max_worker_queue_size = 8192
in 
fe.conf
(requires FE restart). 4. Shared-Data "Task Throttling" In shared-data clusters, the FE limits the total number of concurrent tasks it dispatches to avoid overloading the metadata service. * Check this: 
ADMIN SHOW FRONTEND CONFIG LIKE 'lake_compaction_max_tasks';
. * The Issue: Although it says "compaction," this often acts as a global throttle for tablet tasks in shared-data mode. If it's set to 
-1
, it defaults to 
num_CNs * 16
(240 in your case). * Action: Set it to 600 or higher: 
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "600");
(This one is mutable). 5. Are you "Tail-Heavy"? If the 
ALTER
job is 80-90% done, you will naturally see idle nodes. * Reason: StarRocks schedules tablets across nodes. If 5,000 tablets are finished and only 900 remain, and those 900 are unevenly distributed in the FE's scheduling queue, the FE might only be sending work to 7 or 8 nodes while the others wait. * Diagnostic: Run 
SHOW ALTER TABLE COLUMN;
. Check the 
Progress
column. If it's near the end, you are just waiting for the "tail" of the job to finish. Recommended "Final Push" Config: If you are able to perform a rolling restart: In 
fe.conf
(Restart FE):
Copy code
properties
alter_max_worker_threads = 512
alter_max_worker_queue_size = 10000
In 
be.conf
(Restart CNs):
Copy code
properties
alter_tablet_worker_count = 32
update_schema_worker_count = 32
create_tablet_worker_count = 32
Rocky's Pro Tip: After restarting, verify all settings with 
ADMIN SHOW FRONTEND CONFIG
and 
SELECT * FROM information_schema.be_configs
. If they don't match your new numbers, the system is still running at the old throttled speed! References • administration/management/compaction.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md
c
@Rocky everything checks out except for 
update_schema_worker_count = 32
. I am hesitant to restart the CNs unless tis is in fact the bottleneck.
r
You don't need to restart the CNs to fix the "idle nodes" issue. While 
update_schema_worker_count
is a local worker pool, it is rarely the cause of half the cluster being idle. That symptom almost always points back to a global throttle at the Frontend (FE). Here is why your nodes are likely idle and how to fix it without a CN restart: 1. The FE Scheduling Gap (The "128" Problem) You mentioned you set 
alter_max_worker_threads = 128
. * The Problem: Your cluster capacity is 480 concurrent tablets (15 CNs × 32 workers). * The Math: If the FE is limited to 128 threads, it can only manage 128 "in-flight" tasks for the entire cluster. * The Result: 128 tasks / 15 nodes = ~8.5 tasks per node. Your nodes are idle because the FE is only giving each CN ~8 tablets to work on, even though you’ve told the CNs they can handle 32 (or 128). To saturate all 15 nodes at 32 workers each, you need at least 500+ threads on the FE. Crucial Check: Run 
ADMIN SHOW FRONTEND CONFIG LIKE 'alter_max_worker_threads';
. * If the value is still 4, your change never took effect (it is a static parameter and requires an FE restart). * If it is 128, it is still too low to keep 15 nodes busy at the concurrency you want. 2. The Shared-Data Throttles (No Restart Required) In shared-data (Lake) mode, schema changes are often gated by the Lake task framework. There are two "Mutable" (no restart needed) parameters that can bottleneck you: * 
lake_compaction_max_tasks
: This controls the total concurrent "Lake" tasks (including schema changes) the FE will allow. _ Default: 
nodes _ 16
(In your case, 240). _ _Fix:* 
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "1000");
* 
lake_compaction_max_parallel_default
: For very large tablets, this allows one tablet to be converted using multiple parallel sub-tasks. _ _Fix:* 
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_parallel_default" = "4");
3. Why 
update_schema_worker_count
is likely NOT the bottleneck This thread pool (default 3) handles the metadata "handshake" when a tablet transitions. * If this were the bottleneck, you would see the nodes busy (CPU usage) but the progress in 
SHOW ALTER TABLE
moving slowly. * Idle nodes mean the nodes are sitting there waiting for the FE to send them a task. This is a "Delivery" problem, not a "Metadata" problem. Summary Recommendation (No CN Restart): 1. FE Config (Dynamic): Increase the global Lake task limit:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "1000");
2. Verify FE Threads (Static): Run 
ADMIN SHOW FRONTEND CONFIG LIKE 'alter_max_worker_threads';
. If it is less than 500, you must restart the FE (not the CNs) to actually allow the cluster to work at full capacity. 3. Check Routine Load: If you haven't paused your 2 Routine Loads, do it now. They compete for the same FE locks required to "Commit" the finished tablets. Rocky's Pro-Tip: Run 
SHOW ALTER TABLE COLUMN;
and look at the 
Progress
column. If it's stuck at 
90%
, it’s waiting for the FE to "Publish" the final versions. If it's at 
20%
and nodes are idle, the FE isn't dispatching fast enough. References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • faq/shared_data_faq.md • administration/management/monitoring/alert.md References
c
@Rocky mysql> ADMIN SHOW FRONTEND CONFIG LIKE 'alter_max_worker%'; +-----------------------------+------------+-------+------+-----------+---------+ | Key | AliasNames | Value | Type | IsMutable | Comment | +-----------------------------+------------+-------+------+-----------+---------+ | alter_max_worker_queue_size | [] | 4096 | int | false | | | alter_max_worker_threads | [] | 512 | int | false | | +-----------------------------+------------+-------+------+-----------+---------+ 2 rows in set (0.106 sec) mysql> ADMIN SHOW FRONTEND CONFIG LIKE 'lake_compaction_max_tasks'; +---------------------------+------------+-------+------+-----------+-------------- | Key | AliasNames | Value | Type | IsMutable | Comment +---------------------------+------------+-------+------+-----------+-------------- | lake_compaction_max_tasks | [] | 512 | int | true | -1 means calc +---------------------------+------------+-------+------+-----------+--------------
r
Given that your FE configurations are active and set to 512, but you still see idle nodes, the bottleneck has shifted from "global thread capacity" to task queuing and sub-system limits. Here is what is likely preventing the rest of your CNs from picking up the work: 1. The Queue Bottleneck: 
alter_max_worker_queue_size
 You have 5,928 tablets, but your 
alter_max_worker_queue_size
is 4,096. * The Problem: This is an internal buffer for the Alter Subsystem. When the FE prepares the 5,928 tasks, it fills the first 4,096 into the queue and then rejects or halts the creation of the remaining 1,832 tasks. * The Result: The FE may enter a "wait and retry" state for the remaining tablets, leading to gaps where CNs finish their current small batch and sit idle because the next batch hasn't been queued yet. * Action: You need to increase this to at least 10,000 to accommodate all tablets. Since it is static, this requires an FE restart. 2. The CN Gateway: 
update_schema_worker_count
 Since you haven't restarted the CNs, this is still at the default of 3. * The Problem: This pool is the "reception desk" for schema change RPCs on the CN. Even though you have 32 
alter_tablet_worker_count
workers ready to do the heavy lifting, the CN can only "check in" 3 new tasks at a time. * The Result: If the tasks are small or finish quickly, the CN spends more time waiting for the "reception desk" to process the next batch than actually converting data. This manifests as nodes oscillating between busy and idle. * Action: If the 
ALTER
job is moving very slowly, you will eventually need to restart CNs to set this to 32. 3. The "Shared-Data Task" Limit: 
compact_threads
 In a Shared-Data (Lake) cluster, the schema change uses the Lake Task Framework (the same one used for compaction). * The Issue: On the CN side, the concurrency of these tasks is often governed by 
compact_threads
rather than 
alter_tablet_worker_count
. * Default: 4. * Calculation: 15 nodes × 4 threads = 60 concurrent tablets cluster-wide. * The Math: If your cluster is only doing 60 tablets at once, but your FE has 512 threads and your CNs have 32 workers each, your nodes will look 80-90% idle. * Action (Mutable - No Restart): Run this on your cluster:
Copy code
sql
    -- Set this to 50% of your CN CPU cores (e.g., if you have 16 cores, set to 8)
    UPDATE information_schema.be_configs SET value = "16" WHERE name = "compact_threads";
4. Partition Concurrency Shared-data schema changes are often scheduled partition-by-partition. * If your 5,928 tablets are spread across only a few partitions (e.g., 5 partitions with many buckets), the FE might only be processing those few partitions at a time. * Check the progress: 
SHOW ALTER TABLE COLUMN;
. * If the 
State
is 
RUNNING
, look at the 
Msg
column. If it shows only a small number of tablets are actually in the "CONVERTING" phase, the FE is likely throttling the number of active partitions. 5. Metadata Lock Contention If your Routine Loads are still running, they are likely the culprit. * Every few seconds, a Routine Load commits a transaction. This requires a Table Lock. * The 
ALTER
job also needs the Table Lock to dispatch tasks and commit finished tablets. * Rocky’s advice: Pause your Routine Loads. In a high-concurrency schema change, the "lock-wait" time in the FE can become the primary reason why tasks aren't being dispatched to CNs. Final Checklist: 1. CNs: Run 
UPDATE information_schema.be_configs SET value = "16" WHERE name = "compact_threads";
. This is the most likely reason for "idle nodes" in Lake mode. 2. Ingestion: Pause your Routine Loads (
PAUSE ROUTINE LOAD FOR table_name;
). 3. FE: If progress is still slow, you must restart the FE with 
alter_max_worker_queue_size = 10000
to allow it to buffer all 5,928 tablets.
Pro-Tip: Run 
SHOW PROC '/compactions';
to see the actual status of the background lake tasks. This will tell you if the tasks are stuck in "PENDING" (FE bottleneck) or "RUNNING" (CN bottleneck). References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • faq/shared_data_faq.md • best_practices/query_tuning/schema_tuning.md • administration/management/resource_management/query_queues.md • administration/management/monitor_manage_big_queries.md References • administration/management/BE_configuration.md • best_practices/query_tuning/schema_tuning.md • release_notes/release-2.5.md • administration/management/FE_configuration.md • release_notes/release-3.3.md
c
@Rocky based on what I am seeing in 
show alter table column;
it appears that tasks continue to run about 8 at a time. I thing compactions may be the bottleneck based on below
Copy code
---------------------------------------+---------+---------------------+---------------------+---------------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Partition                             | TxnID   | StartTime           | CommitTime          | FinishTime          | Error | Profile                                                                                                                                                                                                     |
---------------------------------------+---------+---------------------+---------------------+---------------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 _statistics_.task_run_history.5313041 | 1463035 | 2026-04-24 18:30:30 | 2026-04-24 18:30:31 | 2026-04-24 18:30:32 |       | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":1,"write_segment_mb":0,"write_remote_sec":0,"in_queue_sec":1} |
 _statistics_.task_run_history.5313041 | 1463172 | 2026-04-24 18:48:32 | 2026-04-24 18:48:33 | 2026-04-24 18:48:35 |       | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":15,"write_segment_count":2,"write_segment_mb":0,"write_remote_sec":0,"in_queue_sec":8} |
 _statistics_.task_run_history.5313041 | 1463309 | 2026-04-24 19:06:34 | 2026-04-24 19:06:35 | 2026-04-24 19:06:36 |       | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":1,"write_segment_mb":0,"write_remote_sec":0,"in_queue_sec":2} |
 _statistics_.task_run_history.5313041 | 1463446 | 2026-04-24 19:24:37 | 2026-04-24 19:24:38 | 2026-04-24 19:24:39 |       | {"sub_task_count":8,"read_local_sec":0,"read_local_mb":0,"read_remote_sec":0,"read_remote_mb":0,"read_segment_count":10,"write_segment_count":1,"write_segment_mb":0,"write_remote_sec":0,"in_queue_sec":0} |
---------------------------------------+---------+---------------------+---------------------+---------------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
r
The data you provided confirms a critical bottleneck in the Shared-Data (Lake) Task Framework. While you have increased the global thread counts, your cluster is still hitting a "per-partition" or "per-task" parallelism limit. The profile you shared shows 
sub_task_count: 8
. In StarRocks Shared-Data mode, a "Task" is generated for a Partition, and "Sub-tasks" are the Tablets within that partition. If you are seeing only ~8 tablets running at a time despite having 15 nodes, it means one of the following is happening: 1. The "Single Partition" Bottleneck If your table is not partitioned (or if the bulk of your 5,928 tablets are in a single partition), the FE will only generate one active Schema Change task for that partition. * The Issue: By default, StarRocks limits how many tablets (sub-tasks) it will process in parallel for a single partition to prevent overloading a single node or hitting transaction limits. * The Fix (Dynamic): Increase the sub-task parallelism limit on the FE:
Copy code
sql
    -- Default is 3 or 8 depending on version.
    -- Set this higher to allow more tablets in one partition to run at once.
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_parallel_default" = "64");
2. CN-Side Concurrency (
compact_threads
) In Lake mode, the Schema Change isn't just governed by `alter_tablet_worker_count`; it uses the Lake Compaction worker pool. * The Issue: Your 
compact_threads
on the CNs likely defaults to 4. With 15 nodes, that's only 60 total concurrent tablets cluster-wide. If other background tasks (like the statistics collection seen in your snippet) are running, your schema change gets throttled further. * The Fix (Dynamic): Run this to allow each CN to handle more tasks:
Copy code
sql
    UPDATE information_schema.be_configs SET value = "16" WHERE name = "compact_threads";
3. The Statistics Table Distraction Note: The snippet you pasted from 
_statistics_.task_run_history
is actually showing the run history for StarRocks' internal Statistics/Analyze tasks, not your actual 
ALTER TABLE
job. * The fact that 
sub_task_count
is 8 there is just because that system table has 8 tablets (buckets). * However, the logic remains the same: If your 
show alter table column
shows only 8 tablets running, the FE is likely only dispatching 8 because of the 
lake_compaction_max_parallel_default
limit mentioned above. 4. FE Queue Overflow You have 5,928 tablets, but your 
alter_max_worker_queue_size
is 4,096. * The Issue: Your FE has physically run out of room in its internal "to-do list." It will process the first 4,096 tablets, and then there will be a significant "stall" while it waits for the queue to clear before it can even schedule the remaining 1,832 tablets. * The Fix (Static): You must add 
alter_max_worker_queue_size = 10000
to your 
fe.conf
and restart the FE. This is likely why you see the nodes go idle; the FE is simply not "feeding" them tasks because they aren't in the queue yet. Recommended Action Plan: 1. Immediate (No Restart):
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "1000");
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_parallel_default" = "128");
    UPDATE information_schema.be_configs SET value = "24" WHERE name = "compact_threads";
2. Verify Partitions: Run 
SHOW PARTITIONS FROM your_table;
. If you only have 1 partition, the 
lake_compaction_max_parallel_default
is your only lever to increase speed. 3. Restart FE: If the above doesn't immediately saturate the nodes, you must restart the FE to apply the 
alter_max_worker_queue_size = 10000
setting so all 5,928 tablets can be queued at once. Rocky's Pro-Tip: Run 
SHOW PROC '/compactions';
right now. Look for the 
Type
"SCHEMA_CHANGE". If you only see 1 row, you are partition-bottlenecked. If you see many rows but they are all "PENDING," you are FE-queue-bottlenecked. References
Open in Slack
PreviousNext
Powered by