http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> - I suspect that I have a bunch of ...
# questions-and-troubleshooting
a
@Rocky - I suspect that I have a bunch of stale connections pending to starrocks. Is there a way to see information about connections from starrocks and kill the ones that are over X minutes old?
r
To identify and terminate stale connections in StarRocks, you can use the following commands. 1. Identify Stale Connections You can view all current connections and their status using the 
SHOW PROCESSLIST
command:
Copy code
sql
SHOW FULL PROCESSLIST;
Key Columns to check: * 
Id
: The connection ID you will use to kill the session. * 
Command
: If this is 
Sleep
, the connection is currently idle. * 
Time
: The time (in seconds) that the connection has been in its current state. For idle connections (
Sleep
), this represents the idle time. * 
ConnectionStartTime
: The timestamp when the connection was first established. 2. Kill Specific Connections To terminate a specific connection, use its 
Id
from the processlist:
Copy code
sql
-- Terminate the connection and its current query
KILL <connection_id>;

-- Alternatively, kill only the query but keep the connection alive
KILL QUERY <connection_id>;
3. Automated Cleanup (Best Practice) Instead of manual killing, you can configure StarRocks to automatically close idle connections after a specific period using the 
wait_timeout
variable (value in seconds). * Check current timeout: 
SHOW VARIABLES LIKE 'wait_timeout';
* Set globally (requires admin):
Copy code
sql
    SET GLOBAL wait_timeout = 3600; -- Close idle connections after 1 hour
Summary of Differences * If you want to kill connections that have been idle for too long: Look for 
Command = 'Sleep'
and 
Time > (X * 60)
. * If you want to kill connections based on total age: Compare 
ConnectionStartTime
with the current time. Note: If you are using a load balancer (like HAProxy or F5) or a connection pool, ensure their timeout settings are aligned with StarRocks to prevent orphaned "half-closed" connections. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • using_starrocks/running_queries.md • administration/management/resource_management/query_queues.md
a
@Rocky - I need to list user connections
r
To list and monitor user connections in StarRocks, you can use the following methods: 1. List Current Connections The most common way to list connections is using the 
SHOW PROCESSLIST
command.
Copy code
sql
-- Shows a list of active connections
SHOW PROCESSLIST;

-- Shows the full SQL text for active queries
SHOW FULL PROCESSLIST;
Important Notes: * Permissions: If you are logged in as 
root
(or have the 
SYSTEM
level 
OPERATE
privilege), you will see connections for all users. Otherwise, you will only see your own connections. * Columns: * User: The username associated with the connection. * Host: The IP and port of the client. * Command: 
Sleep
indicates an idle connection; 
Query
indicates an active execution. * Time: How long (in seconds) the connection has been in its current state. * ConnectionStartTime: The exact time the connection was established. 2. List Cluster-Wide Running Queries If you want to see what users are currently executing queries across all FE (Frontend) nodes in a multi-FE cluster, use the 
PROC
commands:
Copy code
sql
-- List running queries on the current FE
SHOW PROC '/current_queries';

-- List running queries across the entire cluster (v3.4+)
SHOW PROC '/global_current_queries';
3. Aggregate Connections by User Since StarRocks is MySQL-compatible, you can query the 
information_schema.processlist
table to perform filtering or aggregation (e.g., to see which user has the most connections):
Copy code
sql
SELECT User, COUNT(*) 
FROM information_schema.processlist 
GROUP BY User;
4. Check Connection Limits To see the maximum allowed connections for a specific user, use:
Copy code
sql
SHOW PROPERTY FOR 'username' LIKE 'max_user_connections';
To check the global connection limit across the whole FE:
Copy code
sql
SHOW VARIABLES LIKE 'max_connections';
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • using_starrocks/running_queries.md • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • sql-reference/sql-statements/cluster-management/plan_profile/SHOW_PROFILELIST.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/information_schema/tables.md
a
@Rocky - Detail message: Unknown table 'information_schema.processlist'.
r
My apologies—you are correct. In StarRocks, 
information_schema.processlist
is not currently implemented as a queryable table. While StarRocks is MySQL-compatible, it does not yet support the full range of 
information_schema
metadata tables found in standard MySQL. To see and manage connections, you must use the 
SHOW
and 
KILL
commands. 1. View Connections Use the following command to see all active and idle sessions:
Copy code
sql
SHOW FULL PROCESSLIST;
* Time: This column shows how many seconds the connection has been in its current state. * Command: 
Sleep
means the connection is idle (stale); 
Query
means it is running a task. 2. Kill Stale Connections Because you cannot use a 
WHERE
clause on the 
SHOW
output, you must identify the 
Id
from the list and run:
Copy code
sql
KILL <connection_id>;
3. Automated Cleanup (The "Best" Way) To prevent connections from staying open too long in the future, you should configure the FE (Frontend) to automatically close idle sessions after a specific timeout. The variable 
wait_timeout
 (in seconds) controls how long the server waits for an idle connection to become active before closing it.
Copy code
sql
-- View current timeout (default is often 28800 seconds / 8 hours)
SHOW VARIABLES LIKE 'wait_timeout';

-- Set to 1 hour (3600 seconds) globally
SET GLOBAL wait_timeout = 3600;
Alternative: Checking Running Queries If you specifically want to see active queries across the whole cluster (not just idle sessions), you can use the internal diagnostic paths:
Copy code
sql
-- Shows running queries on the current FE
SHOW PROC '/current_queries';

-- Shows running queries on all FEs (v3.4+)
SHOW PROC '/global_current_queries';
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST.md • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • sql-reference/information_schema/tables.md • sql-reference/information_schema/information_schema.md • sql-reference/information_schema/schemata.md • sql-reference/information_schema/columns.md • sql-reference/information_schema/views.md
7 Views
Open in Slack
PreviousNext
Powered by