@Rocky How can I check if my queries got queued up and in pending state .? and how can I check if how long does the query is taking time .?

To check if your queries are queued (pending) and to monitor how long they are taking, StarRocks provides several built-in tools and commands. 1. How to check if queries are queued (Pending) Queries enter a

PENDING

state only if you have Query Queues enabled. If enabled, you can use these methods: *

SHOW PROCESSLIST

: Look at the

IsPending

column. If it is

true

, the query is currently waiting in a queue.

sql
    SHOW [FULL] PROCESSLIST;

*

SHOW RUNNING QUERIES

(v3.1.4+): This command specifically shows the queue status of queries. Look at the

State

column; it will show

PENDING

if queued or

RUNNING

if executing.

sql
    SHOW RUNNING QUERIES;

*

SHOW PROC '/current_queries'

: Look at the

ExecState

column. It will display

PENDING

if the query is in the queue.

Note: Query queues are off by default. You can enable them for select queries using

SET GLOBAL enable_query_queue_select = true;

.

────────── 2. How to check query duration (Time Taken) You can track both live queries and completed queries: For currently running queries: *

SHOW PROC '/current_queries'

: Provides a real-time view of running queries on the current FE. *

ExecTime

: Total wall-clock time spent in execution. *

CPUTime

: Total CPU time consumed. *

StartTime

: When the query started.

sql
    SHOW PROC '/current_queries';

_(Use

SHOW PROC '/global_current_queries'

in v3.4+ to see queries across all FEs.)_ *

SHOW PROCESSLIST

: The

Time

column shows the number of seconds the query has been in its current state. For completed queries (Historical): * FE Audit Log (

fe.audit.log

): The most reliable source for historical data. Every query logs its performance here. Relevant fields include: * `QueryTime`: Total time (ms). * `PendingTimeMs`: Time spent waiting in the queue. * `ScanTime`: Time spent scanning data. * Query Profile: For deep analysis, you can view the detailed execution profile which breaks down time by every operator (Scan, Join, Aggregate).

sql
    -- To enable profile collection for your session
    SET enable_profile = true;
    -- After running your query, get the ID and view it
    -- (Usually viewed via the StarRocks Manager UI or using get_query_profile() in later versions)

Summary Table of Commands Command | Best For | Key Columns to Watch

SHOW PROCESSLIST

| Quick live status |

IsPending

,

Time

SHOW RUNNING QUERIES

| Queue management |

State

(PENDING/RUNNING),

StartTime

SHOW PROC '/current_queries'

| Real-time resource usage |

ExecTime

,

CPUTime

,

ExecState

fe.audit.log

| Historical analysis |

QueryTime

,

PendingTimeMs

References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_RUNNING_QUERIES.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/resource_management/query_queues.md

@Rocky my queries are getting queued up in query queue and in pending state, how can I check if any of the query is taking longer time and hence all other queries are stuck in pending state. ?

To identify "blocker" queries that are consuming resources and causing others to wait in the queue, you should follow these steps: 1. Identify which queries are "Running" vs "Pending" Since your queries are queued, you first need to see which ones are actually executing (RUNNING) and occupying the available "slots" or resources.

sql
SHOW RUNNING QUERIES;

* Look for

State = RUNNING

: These are the queries currently active. * Check

StartTime

: Any query with a

RUNNING

state and a

StartTime

from several minutes/hours ago is likely the "blocker." * Check

ResourceGroupId

: If you use Resource Groups, it will show you which specific group is full. 2. Analyze the Resource Usage of Running Queries Once you have the

QueryId

of the running queries, use this command to see why they are taking so long (e.g., scanning too much data or using too much CPU/Memory):

sql
-- For v3.4+:
SHOW PROC '/global_current_queries';

-- For versions before v3.4 (shows only queries on the current FE):
SHOW PROC '/current_queries';

Key columns to investigate: *

ExecTime

: How long the query has been running. *

ScanBytes

/

ScanRows

: If these numbers are massive (GBs or Billions of rows), the query is likely a heavy ETL or a poorly filtered scan. *

CPUTime

: High CPU time relative to

ExecTime

indicates a computation-heavy query (large joins/aggregations). *

MemoryUsage

: If this is near your system limits, it might be triggering the queue based on the

query_queue_mem_used_pct_limit

setting. 3. Check Backend (BE) Health Sometimes queries queue because the entire system is under load (CPU/Memory thresholds met), not just because of a single query. Check the overall load on your worker nodes:

sql
SHOW PROC '/backends';

Look at

CpuUsedPct

and

MemUsedPct

. If these exceed the thresholds set in your configuration (default is often 90% memory or 80% CPU), StarRocks will queue all new queries until the load drops. 4. How to "Unstick" the Queue If you find a specific long-running query that is blocking everyone else, you can manually kill it: 1. Find the

ConnectionId

from

SHOW PROCESSLIST

or

SHOW PROC '/current_queries'

. 2. Run the kill command:

sql
    KILL CONNECTION <ConnectionId>;

Summary of Blocker Detection If you see... | It means... High

ScanBytes

in

current_queries

| Query is scanning too much data (missing indexes or partitions). Many

RUNNING

queries at once | You have hit your

query_queue_concurrency_limit

. High

CpuUsedPct

in

SHOW PROC '/backends'

| The system is CPU saturated; queries are queued to prevent a crash. One query with very high

MemoryUsage

| This query is likely triggering the

query_queue_mem_used_pct_limit

. limit`. | References • administration/management/resource_management/query_queues.md • using_starrocks/running_queries.md • administration/management/monitor_manage_big_queries.md

@Rocky is there any issue with query queue implementation in 4.1 version .? I see lot of my queries are queing up and eventually my sql command is timing out.

In StarRocks v4.1.0, there was a major fix related to query queue logic, but there are also known behaviors and environment-specific issues that could cause the queuing and timeouts you are experiencing. 1. Known Fix in v4.1.0 If you upgraded from an older version, you should know that v4.1.0 specifically addressed Issue #65802, which involved bugs in "query queue allocation time and pending timeout logic." While this was intended to make the queue more accurate, it may have changed how your existing limits are enforced. 2. Critical Warning: Container Environments If you are running StarRocks v4.1.0 in a container (Docker/Kubernetes), there is a known critical issue: * Unstable BE Start: An unstable load order issue in the v4.1.0 image can cause BE processes to fail to start or report health incorrectly. * Impact: If BEs are unstable or failing, the FE will see fewer available resources, causing it to queue almost all incoming queries. * Recommendation: The community recommends skipping v4.1.0 and waiting for v4.1.1 (or checking if it has been released) for containerized deployments. 3. Default Behavior Change (ETL Mode) In version 4.1, ETL execution mode optimizations are now enabled by default for

INSERT INTO SELECT

and

CTAS

queries. * These queries are now more aggressively managed by the query queue. If you have many background loading tasks, they may be consuming the concurrency "slots" that were previously unmanaged, causing your interactive SQL commands to queue and eventually time out. 4. Why your SQL is Timing Out There are two distinct timeouts that cause the "timeout" error in a queued state: *

query_queue_pending_timeout_second

(Default: 300s): This is the maximum time a query can stay Pending in the queue. If it doesn't start within this window, StarRocks rejects it. *

query_timeout

(Default: 300s): This is the total execution time. If a query spends 200s in the queue and then runs for 101s, it will time out. Troubleshooting Steps 1. Verify BE Health: Run

SHOW PROC '/backends';

and ensure all nodes are

Alive

and

CpuUsedPct

isn't stuck at a high value. 2. Check Queue Limits: See if your limits are too restrictive for the new version's resource tracking:

sql
    SHOW VARIABLES LIKE '%query_queue%';

If

query_queue_concurrency_limit

is set, try increasing it or increasing

query_queue_pending_timeout_second

to see if the queries eventually clear. 3. Identify the "Slot Hog": Use the command from the previous answer to see what is currently `RUNNING`:

sql
    SHOW RUNNING QUERIES;

If you see many

INSERT

tasks, they are likely the reason your

SELECT

queries are stuck. If you are on v4.1.0 in a container, the most likely cause is the BE instability mentioned in the release notes. You may need to check your BE logs for "Thrift" or "Service" start failures.

@Rocky is the qurey limits based on fe resources or be resources .? or both .?

Query limits and the query queue in StarRocks are managed by the FE, but the thresholds are based on BE resources. Here is the breakdown of how the resources are measured: 1. Limits are based on BE Resources The thresholds that trigger queuing are almost entirely defined by the health and capacity of your Backends (BEs). StarRocks queues queries when the BEs reach specific resource limits: * Concurrency:

query_queue_concurrency_limit

tracks the number of concurrent queries running on a BE. * Memory:

query_queue_mem_used_pct_limit

triggers queuing if the BE memory usage exceeds a certain percentage (e.g., if a BE is at 90% memory, the FE will queue new queries). * CPU:

query_queue_cpu_used_permille_limit

triggers queuing based on the BE CPU load. * Resource Groups: If you use Resource Groups, settings like

concurrency_limit

and

max_cpu_cores

are enforced per BE node. 2. Management is done by the FE While the resources being monitored are on the BEs, the "brain" of the queue lives on the Front-end (FE): * Reporting: BEs report their current CPU and Memory usage to the FE (default is every 1 second). * Decision Making: When you submit a query, the FE checks the latest reports from the BEs. If the BEs are over the limit, the FE marks the query as

PENDING

and holds it in a queue. * Global Coordination (v3.1.4+): In newer versions, the Leader FE acts as a central coordinator. Follower FEs notify the Leader FE when they start/finish queries so the "concurrency" count is accurate across the whole cluster. 3. Are there any FE-specific limits? There are very few query limits based on FE-only resources. The FE can become a bottleneck if it runs out of memory (due to too many connections or massive metadata), but this is handled by general JVM settings rather than the "Query Queue" feature. * The only common FE-side limit is the

max_connections

(default 1024), which limits the number of active MySQL protocol connections the FE will accept. Summary * The metrics (CPU, Mem, Concurrency) come from the BEs. * The logic (Is it full? Should I queue this?) happens on the FE. If your queries are queuing, it usually means your BEs are reporting that they are too busy (high CPU/Mem) or are already running the maximum allowed number of concurrent tasks. References • administration/management/resource_management/query_queues.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md -PROC) References • administration/management/resource_management/query_queues.md • administration/management/FE_configuration.md

@Rocky my BE pods are fully healthy and is having very low usage, however my queries queue are still in pending state. Is there anything wrong with query limits in v4.1 ?

Yes, there are a few specific factors in StarRocks v4.1 that could cause queries to stay in a

PENDING

state even when your Backends (BEs) appear idle. 1. Known Issue: v4.1.0 Container Image Bug If you are running StarRocks v4.1.0 on Kubernetes (Pods), there is a confirmed critical bug in the container image regarding "unstable load order." * The Symptom: BE processes may start but fail to initialize their internal services (like the Thrift server) correctly. * The Impact: Even if the Pod is "Running" and "Healthy" in Kubernetes, the FE may perceive the BEs as having zero capacity or being in a state where they cannot accept new tasks, leading to immediate queuing. * Fix: Upgrade to v4.1.1 (which contains fix #71825) or use a non-containerized deployment if possible. 2. Behavioral Change: ETL Mode Enabled by Default In v4.1, ETL execution mode optimizations are now enabled by default for

INSERT INTO SELECT

and

CTAS

queries. * The Change: These tasks are now much more strictly governed by the query queue. * The Impact: If you have many background loading tasks or materialized view refreshes, they might be "consuming" the available concurrency slots. Even if they aren't using much CPU/Memory yet, they occupy a "Slot" in the FE's tracking, which blocks your interactive

SELECT

queries. 3. Check for "Zombies" or Desync (Leader FE) Since v3.1.4, StarRocks uses a Collective Concurrency Management where Follower FEs notify the Leader FE when queries start and stop. If there is a communication glitch: * The Leader FE might believe there are many "Running" queries that have actually already finished. * How to verify: Run the following on the Leader FE:

sql
    SHOW RUNNING QUERIES;

If you see many queries listed as

RUNNING

that you know should be finished, you may have a metadata desync. Restarting the Leader FE usually clears this "ghost" concurrency. 4. Logic Fix in v4.1 (#65802) StarRocks v4.1 introduced a fix for "query queue allocation time and pending timeout logic". While this fixed a bug, it made the queue enforcement more rigid. If your

query_queue_concurrency_limit

was set very low (or left at a default that is now being strictly enforced), queries will queue more aggressively than in previous versions. Recommended Troubleshooting Steps To confirm exactly why the FE is choosing to queue the queries, run these three commands: 1. Check FE's view of BEs:

sql
    SHOW PROC '/backends';

_Look at

NumRunningQueries

. If this number is equal to your

query_queue_concurrency_limit

, the FE will not send any more work._ 2. Verify Global Limits:

sql
    SHOW VARIABLES LIKE '%query_queue%';

_If

query_queue_concurrency_limit

is set to a small number (e.g., 5 or 10), try increasing it to see if queries start running:_

sql
    SET GLOBAL query_queue_concurrency_limit = 100;

3. Check for specific blockers:

sql
    SHOW RUNNING QUERIES;

Check the

StartTime

. If you see queries that have been "Running" for days but the BE usage is low, those are likely leaked/stuck queries that are hogging the queue slots. Summary: If you are on v4.1.0 in a container, the image bug is the most likely culprit. If you are not in a container, it is likely the new default ETL slot management or a concurrency count desync on the FE. References • administration/management/resource_management/query_queues.md • sql-reference/System_variable.md • administration/management/monitor_manage_big_queries.md

@Rocky my

query_queue_concurrency_limit

is set to high number but I still see see query queues are stuck and in pending. and eventually timing out.

If your

query_queue_concurrency_limit

is set to a high value but queries are still stuck in

PENDING

, the system is likely being triggered by non-concurrency thresholds or a metadata desync. In StarRocks v4.1, there are four common reasons why this happens despite a high concurrency limit: 1. Hidden CPU or Memory Thresholds The query queue is triggered if any of the following thresholds are met, not just concurrency. Check if these are set: * `query_queue_mem_used_pct_limit`: If this is set (e.g., to

0.8

), and a BE node reports it is at 80% memory, the FE will queue all new queries, even if only 1 query is running. * `query_queue_cpu_used_permille_limit`: If this is set (e.g., to

800

), and BE CPU usage spikes, queries will queue. How to check:

sql
SHOW VARIABLES LIKE '%query_queue%';

If these are non-zero, they might be the culprit. Try setting them to

0

(disabled) to see if the queue clears. 2. Resource Group Overrides If your queries are hitting a specific Resource Group, that group's internal limits will override the global variables. * The smaller limit wins: If global concurrency is 1000 but the resource group

concurrency_limit

is 5, your queries will queue at the 6th concurrent query. * Check if the queries in

PENDING

belong to a specific group:

sql
    SHOW RUNNING QUERIES;

Look at the

ResourceGroupId

column. If it's not

-

, check that group's configuration:

sql
    SHOW RESOURCE GROUPS;

3. The "v4.1.0 Container Image" Bug If you are using the v4.1.0 Docker/K8s image, there is a known critical issue (#71825). * The Bug: BE processes in this specific image version can fail to initialize their internal RPC services (Thrift) correctly. * The Result: The FE thinks the BE is "Alive" but cannot send any tasks to it because the communication port is technically unresponsive or has "zero" available slots. This forces every query into a

PENDING

loop until it times out. * Fix: Upgrade to v4.1.1 immediately. This version was released specifically to fix this container startup instability. 4. Leader FE Metadata Desync Since v3.1.4, StarRocks uses Collective Concurrency Management. The Leader FE keeps a central count of all running queries across the whole cluster. * If a Follower FE crashed or failed to send a "Finish" signal to the Leader, the Leader FE might think there are 1000+ queries running (occupying all slots) when the BEs are actually idle. * How to verify: Run

SHOW RUNNING QUERIES;

on the Leader FE. If you see hundreds of queries that you know are old or finished, you have "ghost" queries. * Fix: Restarting the Leader FE will clear this internal memory-based counter. Immediate Troubleshooting Commands Run these to see exactly why the FE is holding the queries: 1. See if the FE thinks the cluster is full:

sql
    SHOW PROC '/backends';

Check the

NumRunningQueries

column. If this number is high despite low CPU/Mem on the BE pods, you have the "Ghost Query" desync issue. 2. Check for specific queue blockers:

sql
    -- Check if it's pending due to a specific Resource Group
    SELECT * FROM information_schema.warehouse_queries WHERE state = 'PENDING';

Summary Recommendation: If you are on v4.1.0 and using containers, the image bug is the 90% likely cause. If not, check for

query_queue_mem_used_pct_limit

or a Resource Group limit. References • sql-reference/System_variable.md • administration/management/resource_management/query_queues.md • faq/resource_isolation_faq.md