http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> What is the best way to throttle a ...
# questions-and-troubleshooting
m
@Rocky What is the best way to throttle a resource group that is running concurrent heavy queries against my cluster and starving ingestion of available CPU? I dont want to fail queries, just make it slower. Average concurrent is ~6. conccurency_limit shouldnt with query queueing is not the issue. 6 concurrent queries should be fine.
r
To throttle a resource group and "slow down" queries without failing them or relying on queuing, you should focus on CPU Weighting (soft limit) or CPU Hard Limits (available in v3.3.5+). 1. Adjust CPU Weighting (Soft Limit) This is the best way to ensure queries continue to run but take a "backseat" to ingestion when the cluster is busy. You do this by setting a low 
cpu_weight
(called 
cpu_core_limit
in versions before 3.3.5) for the query group relative to the group handling ingestion. * If Ingestion is in `default_wg`: Lower the weight of the query group. * If Ingestion is categorized: Create/Alter an ingestion group with a much higher weight.
Copy code
sql
-- Example: Throttling a group named 'heavy_query_group'
ALTER RESOURCE GROUP heavy_query_group WITH (
    'cpu_weight' = '1' -- Set to a low relative value
);

-- Ensure the default or ingestion group has a higher weight
ALTER RESOURCE GROUP default_wg WITH (
    'cpu_weight' = '10'
);
Note: In this scenario, if the cluster is idle, the heavy queries can still use 100% CPU. Throttling only kicks in when there is competition (e.g., during ingestion). 2. Use CPU Hard Limits (v3.3.5+) If you want to ensure queries never exceed a certain number of cores, even if the cluster is otherwise idle, use 
exclusive_cpu_cores
. This is a hard cap that effectively slows down heavy queries by limiting their total compute footprint.
Copy code
sql
-- Example: Cap the group to exactly 4 cores across the BEs
ALTER RESOURCE GROUP heavy_query_group WITH (
    'exclusive_cpu_cores' = '4'
);
_Requirement: 
exclusive_cpu_cores
requires StarRocks v3.3.5 or later._ 3. Reduce Per-Query Parallelism (
pipeline_dop
) If 6 concurrent queries are "too heavy," it is likely because each query is trying to use all available cores via the Pipeline Engine. You can "slow down" individual queries by reducing their 
pipeline_dop
(Degree of Parallelism). This can be set at the resource group level or via a classifier.
Copy code
sql
-- This ensures no single query in this group uses more than 4 threads per BE
ALTER RESOURCE GROUP heavy_query_group WITH (
    'concurrency_limit' = '10',
    'pipeline_dop' = '4' 
);
4. Prioritize Ingestion To ensure ingestion tasks are not starved, make sure they are either in the 
default_wg
with a high weight or their own dedicated group. * INSERT / Broker Load: Can be mapped to resource groups using 
query_type in ('insert')
. * Routine Load / Stream Load: These tasks typically run with high priority. By throttling the query resource groups using the methods above, you naturally leave CPU headroom for these background loading processes. Summary Recommendation If you are on v3.3.5+, use 
exclusive_cpu_cores
for a guaranteed cap. If you are on an older version, use a low 
cpu_core_limit
(weight) and reduce the 
pipeline_dop
for the resource group to prevent any single query from being too aggressively grabbing CPU cycles. References • administration/management/resource_management/resource_group.md • administration/management/resource_management/query_queues.md • faq/resource_isolation_faq.md
m
@Rocky
Copy code
name,id,cpu_weight,exclusive_cpu_cores,mem_limit,big_query_cpu_second_limit,big_query_scan_rows_limit,big_query_mem_limit,concurrency_limit,spill_mem_limit_threshold,classifiers
audit_loader,9142922,1,0,100.0%,0,0,0,0,100%,"(id=9142923, weight=1.0, user=audit_loader)"
audit_loader,9142922,1,0,100.0%,0,0,0,0,100%,"(id=9142924, weight=1.0, user=audit_loader)"
audit_loader,9142922,1,0,100.0%,0,0,0,0,100%,"(id=10433017, weight=1.0, user=audit_loader)"
audit_loader,9142922,1,0,100.0%,0,0,0,0,100%,"(id=10448540, weight=1.0, user=audit_loader)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=7772943, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=7772944, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=7774470, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=7774947, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=7776609, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=8433344, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=8452065, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=8453252, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=8545531, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=8550184, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=8621790, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=9047046, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=9127897, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=9142942, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=10433027, weight=1.0, user=bi_airflow)"
bi_airflow,7772942,1,0,100.0%,0,0,0,0,100%,"(id=10448544, weight=1.0, user=bi_airflow)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=7772934, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=7772935, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=7774466, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=7774943, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=7776613, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=8433337, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=8452059, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=8453256, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=8545527, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=8550188, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=8621796, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=9047040, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=9127895, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=9142939, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=10433029, weight=1.0, user=bi_jupyterhub)"
bi_jupyterhub,7772933,1,0,100.0%,0,0,0,0,100%,"(id=10448550, weight=1.0, user=bi_jupyterhub)"
daisy-coworker,10433008,1,0,5.0%,100,0,2147483648,10,100%,"(id=10433009, weight=1.0, user=daisy-coworker)"
daisy-coworker,10433008,1,0,5.0%,100,0,2147483648,10,100%,"(id=10433010, weight=1.0, user=daisy-coworker)"
db_backup_restore,10448531,1,0,100.0%,0,0,0,0,100%,"(id=10448532, weight=1.0, user=db_backup_restore)"
db_backup_restore,10448531,1,0,100.0%,0,0,0,0,100%,"(id=10448533, weight=1.0, user=db_backup_restore)"
default_mv_wg,3,1,0,80.0%,0,0,0,null,80%,"(id=0, weight=0.0)"
default_wg,2,16,0,100.0%,0,0,0,null,100%,"(id=0, weight=0.0)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7560380, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7560381, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7772951, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7774468, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7774945, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7775454, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=7776607, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=8433342, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=8452063, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=8453250, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=8545529, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=8550182, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=8621798, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=9047044, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=9127893, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=9142944, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=10433019, weight=1.0, user=elastic_consumer)"
elastic_consumer,7560379,1,0,100.0%,0,0,0,0,100%,"(id=10448552, weight=1.0, user=elastic_consumer)"
explorer_backend,9047027,1,0,10.0%,100,0,4294967296,50,100%,"(id=9047028, weight=1.0, user=explorer_backend)"
explorer_backend,9047027,1,0,10.0%,100,0,4294967296,50,100%,"(id=9047029, weight=1.0, user=explorer_backend)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8433327, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8433328, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8452067, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8453254, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8545525, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8550186, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=8621794, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=9047042, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=9127891, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=9142935, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=10433023, weight=1.0, user=high_ctr_domain_blacklist)"
high_ctr_domain_blacklist,8433326,1,0,100.0%,0,0,0,0,100%,"(id=10448548, weight=1.0, user=high_ctr_domain_blacklist)"
ml_zeppelin,9127878,1,0,100.0%,0,0,0,0,100%,"(id=9127879, weight=1.0, user=ml_zeppelin)"
ml_zeppelin,9127878,1,0,100.0%,0,0,0,0,100%,"(id=9127880, weight=1.0, user=ml_zeppelin)"
ml_zeppelin,9127878,1,0,100.0%,0,0,0,0,100%,"(id=9142931, weight=1.0, user=ml_zeppelin)"
ml_zeppelin,9127878,1,0,100.0%,0,0,0,0,100%,"(id=10433021, weight=1.0, user=ml_zeppelin)"
ml_zeppelin,9127878,1,0,100.0%,0,0,0,0,100%,"(id=10448546, weight=1.0, user=ml_zeppelin)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=8545517, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=8545518, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=8550190, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=8621792, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=9047038, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=9127887, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=9142933, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=10433031, weight=1.0, user=sa_web)"
sa_web,8545516,1,0,100.0%,0,0,0,0,100%,"(id=10448554, weight=1.0, user=sa_web)"
sdm_maintain,6401200,3,0,50.0%,0,0,0,50,100%,"(id=6401201, weight=1.0, user=sdm_maintain)"
sdm_maintain,6401200,3,0,50.0%,0,0,0,50,100%,"(id=7560389, weight=1.0, user=sdm_maintain)"
sdm_maintain,6401200,3,0,50.0%,0,0,0,50,100%,"(id=7776611, weight=1.0, user=sdm_maintain)"
sdm_maintain,6401200,3,0,50.0%,0,0,0,50,100%,"(id=8433335, weight=1.0, user=sdm_maintain)"
sdm_maintain,6401200,3,0,50.0%,0,0,0,50,100%,"(id=8452057, weight=1.0, user=sdm_maintain)"
sdm_reader,6401177,1,0,5.0%,100,0,2147483648,50,100%,"(id=6401178, weight=1.0, user=sdm_reader)"
sdm_reader,6401177,1,0,5.0%,100,0,2147483648,50,100%,"(id=7560388, weight=1.0, user=sdm_reader)"
sdm_reader,6401177,1,0,5.0%,100,0,2147483648,50,100%,"(id=7776615, weight=1.0, user=sdm_reader)"
sdm_reader,6401177,1,0,5.0%,100,0,2147483648,50,100%,"(id=8433339, weight=1.0, user=sdm_reader)"
sdm_reader,6401177,1,0,5.0%,100,0,2147483648,50,100%,"(id=8452061, weight=1.0, user=sdm_reader)"
sdm_requests_reader,7776598,1,0,5.0%,100,0,2147483648,50,100%,"(id=7776599, weight=1.0, user=sdm_requests_reader)"
sdm_requests_reader,7776598,1,0,5.0%,100,0,2147483648,50,100%,"(id=7776600, weight=1.0, user=sdm_requests_reader)"
sdm_requests_reader,7776598,1,0,5.0%,100,0,2147483648,50,100%,"(id=8433346, weight=1.0, user=sdm_requests_reader)"
sdm_requests_reader,7776598,1,0,5.0%,100,0,2147483648,50,100%,"(id=8452069, weight=1.0, user=sdm_requests_reader)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=8621782, weight=1.0, user=services_tester)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=8621783, weight=1.0, user=services_tester)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=9047036, weight=1.0, user=services_tester)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=9127889, weight=1.0, user=services_tester)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=9142937, weight=1.0, user=services_tester)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=10433025, weight=1.0, user=services_tester)"
services_tester,8621781,1,0,100.0%,0,0,0,0,100%,"(id=10448542, weight=1.0, user=services_tester)"
These are my resource group configs. The issue is that the transaction publish latency and stream load response (even with merge commit async enabled) is takinng longer than the configured timeout from non us east regions. elastic consumer is the stream loading application. WIll increasing its cpu_weight help ingestion? Or stream load from client all the way to BE commit data is controlled by default_wg, or not resource group controlled at all
r
To troubleshoot the CPU starvation of your ingestion (Stream Load) by concurrent heavy queries, it is important to understand how StarRocks prioritizes these different workloads. 1. Resource Groups do NOT control Stream Load According to the StarRocks documentation, Stream Load, Routine Load, and Schema Change are not managed by resource groups. The 
cpu_weight
and 
mem_limit
in your 
elastic_consumer
resource group only apply to queries run by that user (e.g., 
SELECT
or 
INSERT INTO...SELECT
). They do not apply to the 
STREAM LOAD
HTTP requests. Because Stream Load is "unmanaged," it relies on the OS and the internal StarRocks thread pools. When your query resource groups are under heavy load, they can saturate the BE's CPU, leaving no "slack" for the background threads responsible for parsing data, writing to disk, and performing the Transaction Publish. 2. The 
default_wg
Weight Issue In your configuration, your 
default_wg
has a 
cpu_weight
of 16, while most other groups (including 
elastic_consumer
) have a weight of 1. * If your heavy queries are falling into the 
default_wg
(because they don't match a specific user classifier), they are receiving 16x the CPU priority of other workloads. * Even if they are in their own groups, a combined weight of many groups can still saturate the CPU. 3. Recommended Solution: "Throttle the Queries to Save Ingestion" Since you cannot "speed up" ingestion via a resource group, you must cap or slow down the queries to leave headroom for ingestion. A. Lower the 
default_wg
weight Reduce the priority of any queries that fall into the default group so they don't starve the system.
Copy code
sql
ALTER RESOURCE GROUP default_wg WITH ('cpu_weight' = '1');
B. Use CPU Hard Limits (v3.3.5+) If you are on version 3.3.5 or later, the most effective way to protect ingestion is to use 
exclusive_cpu_cores
on your query groups. This ensures that even if 100 queries are running, they can never use more than the specified number of cores, leaving the remaining cores for Stream Load. Example: If your BE has 64 cores, cap your heavy query groups so they only use 40, leaving 24 for system tasks and ingestion.
Copy code
sql
ALTER RESOURCE GROUP bi_airflow WITH ('exclusive_cpu_cores' = '10');
ALTER RESOURCE GROUP bi_jupyterhub WITH ('exclusive_cpu_cores' = '10');
C. Reduce 
pipeline_dop
for Query Groups To "slow down" queries without failing them, you can limit the number of execution threads each query is allowed to use. This prevents 6 concurrent queries from grabbing every available thread on the BE.
Copy code
sql
-- This limits every query in this group to 4 threads per BE
ALTER RESOURCE GROUP bi_airflow WITH ('pipeline_dop' = '4');
D. Verify Transaction Publish Latency Transaction publish latency is often high when the BE is too busy to handle the metadata RPCs from the FE. By implementing the throttles above, the BE CPU utilization should drop slightly (or at least be less "spiky"), allowing the publish/commit threads to respond faster. Summary of Action Items: 1. Reduce 
default_wg
weight to 1 (currently 16). 2. Lower 
pipeline_dop
 to 4 or 8 for all BI/heavy query resource groups. 3. If on v3.3.5+, use 
exclusive_cpu_cores
 to hard-cap the total CPU query groups' CPU usage to ~70% of your total cores. References • best_practices/audit_log_resource_group.md • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md
m
@Rocky But doesn't exclusive CPU cores mean only that resource group can use those cores, and during downtime, unused cores is wasted? What about giving default_wg exclusive cpu cores. No one uses default_wg, everyone has a resource group. So those are reserved for stream load
r
You are correct that 
exclusive_cpu_cores
 creates a hard partition: cores assigned to an exclusive group are reserved and cannot be used by other resource groups, even if they are idle. However, your strategy of giving 
default_wg
exclusive cores to protect ingestion is based on a slight misunderstanding of how Stream Load works. 1. Stream Load is "Unmanaged" In StarRocks (v3.3.5+), Stream Load, Routine Load, and Schema Change are not managed by resource groups. * If you give 
default_wg
exclusive cores, those cores are reserved strictly for queries that don't match any classifier. * Because Stream Load is unmanaged, it will not automatically run on the cores reserved for 
default_wg
. 2. The "Shared" Pool vs. "Exclusive" Pool When you use 
exclusive_cpu_cores
, the BE's total CPU cores are split into two categories: 1. Exclusive Pools: One for each resource group configured with 
exclusive_cpu_cores
. 2. Shared Pool: The remaining cores that were not assigned to any exclusive group. Unmanaged tasks (like Stream Load) and Shared Resource Groups run in the Shared Pool. 3. The Best Way to Protect Ingestion To protect ingestion (Stream Load) from being starved by queries, you should leave enough cores in the Shared Pool by capping your query groups. The Strategy: 1. Don't give 
default_wg
exclusive cores (unless you have many unclassified queries). 2. Assign 
exclusive_cpu_cores
to your heavy query groups (e.g., 
bi_airflow
, 
bi_jupyterhub
, etc.). 3. Ensure the sum of all 
exclusive_cpu_cores
is less than your total BE cores. Example: If your BE has 64 cores: * Cap your combined query groups to 48 cores using 
exclusive_cpu_cores
. * This leaves 16 cores unassigned. * These 16 cores form the Shared Pool. * Your Stream Load (unmanaged) will now have a guaranteed 16 cores to perform transaction publish, data parsing, and RPCs, without competing with the 48 cores used by your heavy queries. Summary * Do cores go to waste? If a query group is idle, its 
exclusive_cpu_cores
are indeed "wasted" for other resource groups. However, this is the trade-off for "Hard Isolation." * Why not 
cpu_weight
? 
cpu_weight
is "Soft Isolation." If the query groups have a combined weight that is very high, they can still momentarily spike to 100% CPU and cause the network/RPC timeouts you are seeing in Stream Load before the scheduler can react. * Recommendation: Use 
exclusive_cpu_cores
on the groups you listed (like 
bi_airflow
) to "corral" them into a specific CPU limit, ensuring the "Shared" cores are always available for your 
elastic_consumer
Stream Loads. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • best_practices/audit_log_resource_group.md References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • loading/StreamLoad.md
m
@Rocky I have 10 BE ndoes 32 vCPU each, so if i give one resource group 4 exlucsive cores, we cap it. Is that the right strategy
r
Yes, that is a sound strategy for your goal. When you set 
exclusive_cpu_cores
for a resource group, StarRocks enforces that limit on each BE node individually. In your specific setup (10 BE nodes, 32 vCPU each): 1. How the math works * The Group Cap: If you set 
exclusive_cpu_cores = 4
for your heavy query group, that group is strictly limited to using 4 cores per BE (a total of 40 cores across the cluster). * The Shared Pool: The remaining 28 cores on each BE (32 total - 4 exclusive) become the "Shared Pool." * Ingestion Protection: Since Stream Load is unmanaged (it does not belong to any resource group), it automatically runs in the Shared Pool. By capping the queries at 4 cores, you are effectively guaranteeing that ingestion always has up to 28 cores available on every BE. 2. Why this is the "Right" Strategy * Hard Throttling: Unlike 
cpu_weight
(which is a soft limit that only works when the system is 100% busy), 
exclusive_cpu_cores
is a hard limit. Your 6 concurrent queries will be forced to compete for those 4 cores, naturally slowing them down without failing them. * No Starvation: Your 
elastic_consumer
user's Stream Loads will no longer have to fight those heavy queries for the same CPU threads. 3. Important Considerations * Resource Borrowing: By default, StarRocks allows "Shared" groups (like ingestion) to borrow idle cores from "Exclusive" groups, but Exclusive groups cannot borrow from the Shared Pool. This is perfect for you: Ingestion can use 32 cores if the cluster is idle, but heavy queries can never use more than 4, even if ingestion is idle. * Sum of Cores: The sum of all 
exclusive_cpu_cores
across all resource groups must be less than 
min_be_cpu_cores - 1
. In your case, the sum must be $\leq 31$. * Strictness: If you want to ensure the heavy queries never ever burst above 4 cores (even if the cluster is empty), ensure the BE configuration 
enable_resource_group_cpu_borrowing
is set to 
true
(which is default). If you set it to 
false
, the 28 cores are strictly walled off from the queries. Recommended Command To throttle the group (let's assume it's 
bi_airflow
):
Copy code
sql
ALTER RESOURCE GROUP bi_airflow WITH (
    'exclusive_cpu_cores' = '4',
    'cpu_weight' = '0' -- You must set weight to 0 to use exclusive cores
);
Verification After applying, you can verify how the threads are bound on your BEs:
Copy code
sql
SELECT BE_ID, NAME, BOUND_CPUS 
FROM information_schema.be_threads 
WHERE name LIKE '%pip_exec%';
You will see a specific thread pool for that Resource Group ID bound to exactly 4 CPUs. References • administration/management/resource_management/resource_group.md • best_practices/audit_log_resource_group.md • faq/resource_isolation_faq.md • administration/management/BE_configuration.md References • administration/management/BE_configuration.md • administration/management/resource_management/resource_group.md • faq/Others.md • faq/resource_isolation_faq.md • best_practices/audit_log_resource_group.md • deployment/plan_cluster.md
m
@Rocky Can you point me specifically where in documentation says shared pool using cpu weight can borrow from exclusive cores but exclusive cores are capped at their own granted cores?
r
The specific behavior you are describing is documented in the Administration > Management > Resource Group section of the StarRocks documentation. Documentation Location You can find the detailed explanation under the CPU resource parameters section for 
exclusive_cpu_cores
. Key Rules from the Docs: 1. Exclusive Groups (The Cap): Resource groups with 
exclusive_cpu_cores > 0
are strictly limited. The documentation defines this as Quota: > "Limits the resource group to only using these reserved CPU cores, preventing it from using available CPU resources from other groups." 2. Shared Groups (The Borrowers): Groups using 
cpu_weight
are called Shared resource groups and run on the Shared Cores. 3. The Borrowing Mechanism: The documentation explicitly states that Shared groups can borrow from Exclusive groups if the Exclusive groups are idle: > _"You can configure whether Shared resource groups can borrow Exclusive Cores from Exclusive resource groups using the BE configuration 
enable_resource_group_cpu_borrowing
. When set to 
true
(default), Shared groups can borrow CPU resources when Exclusive groups are idle."_ Why this works for you: * Queries (Exclusive): If you give a BI group 4 
exclusive_cpu_cores
, they are capped at 4. They cannot borrow from the rest of the system (the 28 other cores). * Ingestion (Shared/Unmanaged): Stream Load is unmanaged and operates as a "Shared" workload. Because 
enable_resource_group_cpu_borrowing
is 
true
by default, Stream Load can use its own shared cores PLUS any of the 4 cores from the BI group if those BI queries aren't currently using them. How to verify/change borrowing: If you want to ensure this behavior is active, you can check or set the BE configuration:
Copy code
sql
-- This allows Shared/Ingestion to borrow idle cores from Exclusive groups
UPDATE information_schema.be_configs 
SET VALUE = "true" 
WHERE NAME = "enable_resource_group_cpu_borrowing";
Doc Link: StarRocks Resource Group Documentation References • administration/management/resource_management/resource_group.md • administration/management/monitoring/metrics.md • faq/resource_isolation_faq.md • best_practices/audit_log_resource_group.md
m
@Rocky I have an array varchar column called cleaned_genre in my primary key table. How can I get percentage distribtion of cleaned genres efficiently? Since we can't group by, we have to unnest the array and then group it?
r
To get the percentage distribution of array elements efficiently in StarRocks, you are correct: you must unnest the array into individual rows first, then group by the resulting values. For a Primary Key table, the most efficient approach is to use a Lateral Join with the 
unnest()
function combined with a Window Function to calculate the percentage in a single pass. The Efficient SQL Pattern
Copy code
sql
SELECT 
    unnested_genre,
    COUNT(*) AS genre_count,
    -- Calculate percentage: (Individual Count / Total Genre occurrences) * 100
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM your_table_name, 
UNNEST(cleaned_genre) AS t(unnested_genre) -- Lateral join to flatten array
GROUP BY unnested_genre
ORDER BY percentage DESC;
Why this is efficient: 1. Pipeline Execution: StarRocks executes the 
unnest
and 
group by
in a streaming fashion. By using 
SUM(COUNT(*)) OVER()
, you avoid a second scan of the table or a separate subquery for the total count. 2. Vectorized Unnest: The 
unnest()
function in StarRocks is highly optimized and vectorized, meaning it handles the expansion of arrays to rows very quickly in memory. 3. Primary Key Tables: Since your table is a Primary Key table, StarRocks is already optimized for reading these columns efficiently from the latest version of the data (ignoring deleted/overwritten rows). Key Details to Consider: * Denominator Logic: The query above calculates the percentage based on the total number of genre tags. If a single record has 
['Action', 'Sci-Fi']
, it contributes 2 to the total. * If you want % of Records: If you want to know "What % of movies have the Action genre?", use 
(SELECT COUNT(*) FROM your_table_name)
as the denominator instead of the window function. * Filtering: If you only care about specific genres, apply the 
WHERE
clause after the unnest but before the grouping for best performance. * Handling NULLs/Empty: By default, 
UNNEST
skips empty arrays or NULL values. If you want to include "No Genre" in your distribution, use 
LEFT JOIN UNNEST(...) ON TRUE
. References • sql-reference/sql-functions/array-functions/unnest.md • using_starrocks/Lateral_join.md • sql-reference/sql-functions/window-functions/window_functions.md References • sql-reference/sql-functions/array-functions/unnest.md • using_starrocks/Lateral_join.md • sql-reference/data-types/semi_structured/Array.md
m
@Rocky [42000][1064] Getting syntax error at line 1, column 44. Detail message: No viable statement for input 'desc'
Copy code
SELECT
    unnested_genre,
    COUNT(*) AS genre_count,
    SUM(COUNT(*)) OVER() as total_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM requests.requests,
UNNEST(cleaned_genre) AS t(unnested_genre)
WHERE is_audio = 1
GROUP BY unnested_genre
ORDER BY genre_count desc;
r
The error 
[42000][1064]
and the message 
No viable statement for input 'desc'
usually indicate that the StarRocks parser encountered a syntax it didn't expect earlier in the query, causing it to fail and then misinterpret the trailing 
DESC
keyword as a new command (like the 
DESCRIBE
statement). There are two likely causes for this in your specific query: 1. The Window Function over an Aggregate: While some SQL engines allow 
SUM(COUNT(*)) OVER()
, StarRocks versions (especially older ones) can be strict about nesting window functions and aggregates in the same 
SELECT
block without a subquery. 2. The UNNEST Alias Syntax: The syntax 
AS t(unnested_genre)
is supported in recent versions, but in many deployments, the "column-list" alias for table functions is a common point of parser failure. The Recommended Fix (Using a CTE) The most robust and efficient way to write this in StarRocks—which avoids parser ambiguity—is to separate the flattening from the aggregation using a Common Table Expression (CTE).
Copy code
sql
WITH flattened AS (
    -- Step 1: Unnest the array first
    SELECT 
        t.unnest AS genre
    FROM requests.requests, 
    UNNEST(cleaned_genre) AS t
    WHERE is_audio = 1
)
SELECT 
    genre,
    COUNT(*) AS genre_count,
    -- Step 2: Use window function on the result of the aggregation
    SUM(COUNT(*)) OVER() AS total_genre_hits,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM flattened
GROUP BY genre
ORDER BY genre_count DESC;
Why this works better: * Alias Clarity: By using 
UNNEST(cleaned_genre) AS t
, the resulting column is automatically named 
unnest
. We rename it to 
genre
in the CTE to make the outer query cleaner. * Execution Order: StarRocks prefers calculating the aggregation (
GROUP BY
) and then applying the window function (
OVER()
) on the resulting rows. Using a CTE ensures the parser understands this sequence clearly. * Primary Key Performance: This still leverages the speed of your Primary Key table because the 
WHERE
filter is applied during the initial scan in the CTE. A Note on the Syntax Error Position: The "column 44" error you saw likely points to the 
COUNT(*)
or the alias immediately following it. In StarRocks, if the 
UNNEST
logic is not parsed correctly, the 
SELECT
list columns that depend on it can trigger errors at their specific character positions. Tip: If you are on StarRocks v3.1+, you can also use 
CROSS JOIN LATERAL UNNEST(cleaned_genre) AS t(genre)
which is the most modern and explicit syntax for this operation. References • sql-reference/sql-functions/array-functions/unnest.md • using_starrocks/Lateral_join.md • sql-reference/sql-functions/Window_function.md • sql-reference/sql-statements/table_bucket_part_index/DESCRIBE.md • sql-reference/data-types/numeric/DECIMAL.md References • sql-reference/sql-statements/table_bucket_part_index/DESCRIBE.md • sql-reference/data-types/numeric/DECIMAL.md • sql-reference/sql-statements/cluster-management/storage_volume/DESC_STORAGE_VOLUME.md
Open in Slack
PreviousNext
Powered by