DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Monitoring and Safely Killing Sessions in GBase 8a

#gbase #database #数据库 #performance

Managing user sessions is a core DBA skill in any gbase database. When performance dips or resources are tight, knowing how to list connections and terminate problematic ones safely keeps your cluster running smoothly.

1. Viewing All Connected Sessions

Option A: SHOW PROCESSLIST

A quick overview of threads on the current coordinator.

SHOW PROCESSLIST;
Enter fullscreen mode Exit fullscreen mode

Key columns:

  • Id – the unique session identifier, used later with KILL.
  • User, Host – who is connected and from where.
  • Time – seconds the session or current query has been running.
  • State – e.g., Sleep (idle), Query (executing).
  • Info – the running SQL; empty means the session is idle.

Option B: information_schema.processlist (Recommended)

This view supports filtering and sorting, making it the go‑to for analysis.

SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
Enter fullscreen mode Exit fullscreen mode

Use it to quickly spot long‑running, resource‑heavy sessions.

2. Safely Terminating a Problem Session

Before running any kill command, identify the right session and choose the least disruptive method.

Termination Options

Command Effect When to Use Impact
KILL CONNECTION <id> (or KILL <id>) Disconnects the session, rolls back uncommitted work Frozen sessions, unresponsive connections High
KILL QUERY <id> Cancels only the current SQL statement, keeps the connection alive A runaway query that doesn't need a full disconnect Lower
PAUSE <id> / CONTINUE <id> Temporarily pauses / resumes execution Quick resource relief for diagnostics Minimal

Examples:

-- Terminate the entire session
KILL 5593;

-- Cancel only the running query
KILL QUERY 5593;
Enter fullscreen mode Exit fullscreen mode

Safe Procedure

  1. Identify: find queries running longer than 5 minutes. 
   SELECT id, user, time, state, info
   FROM information_schema.processlist
   WHERE command = 'Query' AND time > 300
   ORDER BY time DESC;
Enter fullscreen mode Exit fullscreen mode
  1. Evaluate: check State (Sending data, Sorting result) and review the SQL in Info for anomalies.
  2. Kill: prefer KILL QUERY to preserve the connection; escalate to KILL CONNECTION only if needed.
  3. Verify: run SHOW PROCESSLIST again to confirm the session is gone or its state has changed.

3. Important Notes

  • Privileges: PROCESS is required to view all sessions; SUPER (or equivalent) is needed for KILL.
  • Multi‑VC clusters: switch to the target VC first: 
  USE VC vc_name;
  SHOW PROCESSLIST;
Enter fullscreen mode Exit fullscreen mode
  • Prevention: use resource pools to cap per‑query resources, set task_running_timeout to auto‑cancel long queries, and leverage GDOM for visual monitoring.

The golden rule for terminating sessions: diagnose first, then act; use KILL QUERY whenever possible. This keeps your gbase database healthy while minimizing business disruption.

Top comments (0)