@Rocky time to time queries just stuck and I saw that it is in running state (Query, OK) in SHOW FULL PROCESSLIST but dont see that query in SHOW RUNNING QUERIES or in SHOW PROC '/global_current_queries' what it can mean?

The behavior you're describing—where a query appears in

SHOW FULL PROCESSLIST

but is missing from

SHOW RUNNING QUERIES

or `SHOW PROC '/global_current_queries'`—typically indicates that the query is stuck in the Frontend (FE) layer before it has been dispatched to the execution engine (BEs), or it has already finished execution and is simply waiting for the client to fetch results. Here are the most common reasons for this state: 1. Metadata Locking (Most Likely) The FE requires metadata locks to analyze and plan a query. If another process (like an

ALTER TABLE

,

TRUNCATE

, or a long-running load) is holding an exclusive lock on a table, your query will wait at the FE level. * How to verify: Check the system view for FE locks:

sql
    SELECT * FROM sys.fe_locks WHERE granted = false;

If you see your query's thread ID in the

waiter_list

or with

granted = false

, it is waiting for a metadata lock. 2. Slow Planning / Metadata Fetching (Data Lake Queries) If you are querying external tables (Hive, Iceberg, Hudi), the FE must fetch metadata (like file lists or partition info) from the remote metastore or storage (S3/HDFS). If the storage or Metastore is slow, the FE thread will appear as "Running" in the processlist but won't show up in "Running Queries" because the execution plan hasn't been generated or sent to the BEs yet. * Symptom: This often happens with queries involving thousands of partitions or slow remote metadata services. 3. Connection Still Fetching Results A query is considered "Running" in the execution engine as long as the BEs are processing it. Once the BEs finish their work and send the results back to the FE, the query may disappear from

SHOW RUNNING QUERIES

. However, the connection remains in the

PROCESSLIST

in a

Query

or

OK

state until the client has fully fetched all rows. * Symptom: If the client is slow to consume data or the result set is massive, the thread stays active in the FE. 4. FE Resource Exhaustion In rare cases, the FE might be experiencing high GC (Garbage Collection) pressure or thread pool exhaustion, causing it to hang during the analysis phase. * Check: FE logs (

fe.log

or

fe.warn.log

) for any

Long GC

or

OutOfMemory

errors. Summary of Commands to Troubleshoot: Command | What it shows

SHOW FULL PROCESSLIST

| Connection-level status.

IsPending: true

means it's in the [Query Queue](https://docs.starrocks.io/docs/administration/management/resource_management/query_queues/).

SHOW RUNNING QUERIES

| Queries actively in the execution engine or waiting in a queue.

SELECT * FROM sys.fe_locks

| Real-time metadata lock status.

SHOW PROC '/global_current_queries'

| All queries the cluster-wide FEs believe are executing. What should you do? If the query is stuck for a long time, check

sys.fe_locks

first. If no locks are found and it's an external table query, check your connectivity to the metadata source (HMS/Glue). You can terminate the stuck connection using

KILL <connection_id>

(the

Id

from

SHOW PROCESSLIST

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

@Rocky what if I see it now in SHOW PROC '/global_current_queries'; but all the values of ScanBytes,ScanRows,MemoryUsage,DiskSpillSize,CPUTime does not change may be eveyrthing is calculated and something just consumes provided results?

When you see a query in

SHOW PROC '/global_current_queries'

with static metrics (ScanBytes, ScanRows, and CPUTime not increasing), it almost always means the BE-side execution is complete, and the query is currently in the Result Fetching phase. Here is what is happening: 1. The BEs are done, the FE is "Serving" In StarRocks, the BEs (Backends) do the heavy lifting: scanning data, filtering, and aggregating. Once they finish, they send the final result set to the FE (Frontend) or wait for the FE to pull it. * ScanBytes / ScanRows: These track data being read from disks or remote storage. If they aren't moving, the BEs have finished reading all the data they need. * CPUTime: This tracks active computation. If it's static, the BEs are no longer actively processing rows. 2. The Client is the Bottleneck (Slow Consumer) The query stays in the

RUNNING

state because the FE is still holding the connection open and transmitting data to your client (e.g., your Python script, BI tool, or MySQL CLI). * If your result set is large (e.g., millions of rows) and your client or network is slow, the FE will sit there waiting for the client to acknowledge receipt of the packets. * The FE cannot mark the query as "Finished" until the last byte has been sent to the client. 3. Final Result Processing on FE If the query involves a

LIMIT

with a very large

OFFSET

, or a final

ORDER BY

that cannot be fully parallelized, the FE might be doing some final single-threaded work. however, if CPUTime is also not increasing, this is unlikely. ────────── How to verify this: 1. Check `fe.audit.log`: After you eventually kill the query or it finishes, look at the

fe.audit.log

. Check the

ReturnRows

field. If it's a high number, the delay was simply the time it took to transfer those rows to the client. 2. Monitor Network Outbound: Look at the network traffic on your FE node. If you see a steady stream of outbound traffic to the client IP, the query is actively sending results. 3. Client-side Check: Check if the application receiving the data is "stuck" or processing each row slowly (e.g., doing a slow

INSERT

or complex logic for every row it fetches). What should you do? * If the result set is unintentionally large: Refine your

WHERE

clause or add a

LIMIT

. * If you need all the data: Consider using

SELECT INTO OUTFILE

to dump the data directly to S3/HDFS. This bypasses the FE-to-Client bottleneck and is much faster for large exports. * Kill the session: If the client has clearly hung, you can run

KILL <connection_id>

to free up the FE resources. References • using_starrocks/running_queries.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/monitor_manage_big_queries.md References • introduction/Architecture.md • using_starrocks/caching/query_cache.md • best_practices/query_tuning/query_planning.md • knowledge/trouble_shooting/fe_memory_problems.md

@Rocky is there a known bug when backend cant move results to frontend so client could fetch it?

The behavior where a query is "stuck" in a running state with static metrics (ScanBytes, ScanRows, and CPUTime not increasing) usually points to an asynchronous result delivery bottleneck rather than a specific "bug." In StarRocks architecture, once the BE (Backend) finishes processing data, it enters the Result Sink phase. Here is what is happening internally: 1. The "Result Sink" Block When a query's execution metrics stop changing, it means the BE has finished its work (Scan, Join, Aggregate) and is now trying to push the final results to the FE. * Backpressure: StarRocks uses a buffer for query results. If the client (your application or BI tool) is slow to fetch the results, or if the FE is struggling to handle the data volume, the BE's

ResultSinkOperator

will block. * Static Metrics: Because the "execution" part of the query is technically finished, the counters for

ScanRows

and

CPUTime

stop incrementing. The query remains in the

global_current_queries

list because the session is still active and the result transmission is incomplete. 2. Potential FE-side Issues (The "Wait") If the query is visible in

SHOW PROCESSLIST

but not in

SHOW RUNNING QUERIES

, it often means the FE has already "reaped" the query from the execution engine but the MySQL protocol layer is still busy sending data. * FE JVM GC: If your FE is experiencing a Long GC, it might be unresponsive to the BEs' attempts to deliver results, causing the query to hang at the very end of its lifecycle. * Thread Exhaustion: Check if your FE's

thrift_server_max_worker_threads

(default 4096) is exhausted. If the FE cannot allocate a thread to handle the incoming results from a BE, the query will stall. 3. How to Distinguish a Hang from a Bug While there aren't many widely known "bugs" for this in recent versions (3.1+), there are specific edge cases: * Variable/Metadata Mismatch: In rare cases, if a query returns a very specific complex type (like large nested Map/Structs), there have been historical bugs where the serialization between BE and FE could hang. * Large Result Sets: If you are running

SELECT *

on a table with millions of rows without a

LIMIT

, you are likely hitting the network/client throughput limit, which looks like a "hang." Troubleshooting Steps: 1. Check FE Locks: Run

SELECT * FROM sys.fe_locks;

. If you see the query ID waiting for a lock, it's a metadata conflict. 2. Verify Client Status: Check if the application that sent the query is actually reading the data. If the application has a small fetch buffer or is doing heavy processing per row, it will backpressure StarRocks. 3. Audit Log Analysis: Once the query finally finishes (or you kill it), check

fe.audit.log

. Look at the

ResultRows

vs.

QueryTime

. If

ResultRows

is huge, the "hang" was just data transfer. 4. Check FE Health: Use

jstat -gcutil <FE_PID> 1000 10

to see if the FE is stuck in a Full GC. 5. Kill the connection: If it's truly stuck and metrics haven't moved for minutes, use

KILL <connection_id>;

from the

SHOW PROCESSLIST

output. Summary: This is rarely a bug in the code itself, but rather a protocol/transport bottleneck where the BE is waiting for the FE (or the FE is waiting for the client) to "take" the results. References • using_starrocks/running_queries.md • administration/management/monitor_manage_big_queries.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md