Show MySQL Processes — Simple & Complete Guide

Use this page to view all active MySQL/MariaDB sessions on the server. You can identify long-running queries, locked sessions, and heavy users, then end specific threads if needed.

What You’ll See

The process list generally includes these columns:

• Id — Internal thread ID (used to kill a process).
• User — Database user running the session.
• Host — Client host/IP and port (source of the connection).
• DB — Selected database (may be empty if none selected yet).
• Command — Thread type (e.g., Query, Sleep, Connect, Binlog Dump).
• Time — Seconds the thread has been in the current state.
• State — What the server is doing (e.g., Sending data, Locked, Copying to tmp table).
• Info — The SQL statement text (may be truncated or empty for some thread types).
• Actions — Options such as Kill to end a thread.

Common Patterns to Watch

• Sleep with large Time — Idle connections held open by applications.
• Locked / Waiting for table metadata lock — Contention from DDL or long transactions.
• Sending data / Copying to tmp table — Likely missing indexes or big sorts.
• Long-running Query — High Time with Command = Query and heavy Info.

Filter, Sort, and Inspect

• Search by User, Host, or DB to isolate a tenant or app.
• Sort by Time to surface the oldest activity first.
• Expand or view Info to see the SQL text for tuning.

Kill a Problem Thread (UI)

1. Find the row with the problematic session (check User, Host, DB, and Info).
2. Click Kill next to the Id.
3. Confirm the action.

If the thread doesn’t end immediately, it may be inside an uninterruptible operation. Recheck after a few seconds.

Quick SQL (Optional, via Terminal)

These SQL commands help you validate what you see in the UI:

-- 1) Full process list (may truncate Info unless FULL is used)
SHOW FULL PROCESSLIST;

-- 2) Same via information_schema for better filtering
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
ORDER BY TIME DESC
LIMIT 100;

-- 3) Kill a specific thread (replace 12345 with the Id)
KILL 12345;

Tuning Hints

• Long SELECTs — Check indexes, avoid SELECT *, add WHERE and LIMIT, analyze EXPLAIN plans.
• Temp tables / filesort — Reduce result set size, add composite indexes, rewrite ORDER BY/GROUP BY.
• Lock waits — Keep transactions short; avoid DDL during peak; use appropriate isolation; split large updates.
• Too many sleeps — Lower connection pool caps; use proper timeouts; recycle idle connections.

Useful Server Variables (for context)

-- Connection and timeout behavior
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';
SHOW VARIABLES LIKE 'max_connections';

-- Slow query diagnostics (enable and review logs)
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Troubleshooting

• Frequent lock waits: Identify blocker thread via State/Time; consider killing the blocker rather than victims.
• High Time values: Check disk I/O, tmpdir space, and large sorts; add indexes and rewrite queries.
• Thread won’t die: It’s likely inside an uninterruptible operation; wait briefly and try again, or restart during a maintenance window.
• Repeat offenders: Enable the slow log, capture samples, and tune with EXPLAIN and indexes.

Example Workflow

1) Sort by Time and find the top long-running Query
2) Inspect Info to see the exact SQL and target tables
3) If critical impact now, Kill the thread (note User/Host for follow-up)
4) Reproduce on staging, run EXPLAIN, add indexes or rewrite SQL
5) Monitor after fix: long-running entries should drop

Summary

• Use the process list to spot heavy or stuck sessions quickly.
• Filter by user/host, sort by time, and check the SQL text.
• Kill threads only when necessary; fix the underlying query or index.
• Leverage slow logs and EXPLAIN to prevent future issues.