The list you scroll at 3 a.m.

SHOW FULL PROCESSLIST is the first thing most of us type after the page. Each row is a thread with Id, User, Host, db, Command, Time, State and Info. Command says what the thread does (Query, Sleep, Binlog Dump, Killed), Time counts the seconds in its current state, and Info holds the statement. Without FULL, Info stops at 100 characters.

On a server with 400 connections that list is a wall of text. It also hides the two answers you came for: which Sleep threads still hold an open transaction, and which thread the waiting ones wait for.

Three views and one privilege

SHOW PROCESSLIST and information_schema.PROCESSLIST return the same rows, and the table form takes a WHERE and an ORDER BY:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 80) AS query
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;

On MySQL, both walk the server’s thread list while they hold a global mutex, and that costs you on a busy server. MySQL 8.0.22 added performance_schema.processlist, which reads the same data from the Performance Schema without that mutex, and the performance_schema_show_processlist variable, which points SHOW PROCESSLIST at it:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM performance_schema.processlist
WHERE COMMAND <> 'Sleep';

SET GLOBAL performance_schema_show_processlist = ON;  -- MySQL 8.0.22+

Since MySQL 8.0.17, a running query shows the state executing where older versions said Sending data. MariaDB keeps information_schema.PROCESSLIST and adds columns such as TIME_MS, EXAMINED_ROWS, MEMORY_USED, QUERY_ID and PROGRESS, which shows how far an ALTER TABLE has got.

All of these views share one rule. Without the PROCESS privilege, MySQL and MariaDB show you only the threads of your own account. You get no error, and the list looks calm. Grant it once:

GRANT PROCESS ON *.* TO 'oncall'@'%';

PROCESS grants no writes. It opens the full processlist, INNODB_TRX, the lock views and SHOW ENGINE INNODB STATUS. It also shows other sessions’ SQL with the literal values in it, so guard that account’s password like any production secret.

Sleep threads that hold a transaction

A thread in Sleep has an empty Info, and its Time counts idle seconds. The processlist doesn’t say whether it left a transaction open, and that transaction can hold row locks for minutes and keep InnoDB from purging old row versions. Join INNODB_TRX to find out:

SELECT p.ID, p.USER, p.HOST, p.DB, p.TIME AS idle_s,
       t.trx_started, t.trx_rows_locked, t.trx_rows_modified
FROM information_schema.INNODB_TRX t
JOIN information_schema.PROCESSLIST p ON p.ID = t.trx_mysql_thread_id
WHERE p.COMMAND = 'Sleep'
ORDER BY t.trx_started;

On MySQL 8.0, sys.session adds the last statement the thread ran, which often points to the code path that forgot to commit:

SELECT conn_id, user, last_statement, last_statement_latency
FROM sys.session
WHERE conn_id = 24802;

Decide by what the transaction holds. If other threads wait on its locks, it is the root of a chain: kill the connection, since KILL QUERY has no statement to stop. If it holds no locks but has stayed open for an hour, it is holding back purge; see history list length. A Sleep thread with no transaction is a pooled connection, and it matters once you near max_connections (Too many connections).

Activity in RowDeck

Activity reads the processlist every 5 seconds while it is open and leaves RowDeck’s own connection out. Each row shows the thread ID, a state pill such as running, waiting, in trx or idle, user@program, the statement or the last: one for a sleeping transaction, and the time. Red lines say what a thread waits for, as in waiting: row lock · shop.orders or waiting: table metadata lock, and which thread holds up others: blocking 4 sessions.

Filter chips narrow the list to Running, In trx, > 10s or Waiting, and All brings the rest back. By client groups threads by program, user or host, so a runaway worker pool stands out. Tap a thread for its user, host, schema, command and time, its transaction (start, rows locked, rows modified, isolation level), the last statement with a Copy button and the locks it holds. Swipe a row for Kill query or Kill connection.

Without PROCESS, Activity says You can only see your own connections. Ask your DBA for the PROCESS privilege. and shows the GRANT for the account you signed in with.

Free and Pro

Activity, its filters, By client and the thread details are free for one saved connection. The swipe kills need RowDeck Pro, a one-time purchase that also unlocks unblocking a lock chain and unlimited connections. The built-in demo lets you try the kills on sample data first.

Keep the list readable

Give each service its own MySQL account, or have its driver send a program_name connection attribute, so you can tell services apart in the list.
wait_timeout closes idle connections after 28800 seconds by default. On MariaDB 10.3 and later, idle_transaction_timeout also ends connections that sit idle inside a transaction.
Keep max_connections above the sum of your pools’ maximum sizes, so a deploy that doubles the pods doesn’t lock you out.