What is running right now: active sessions, latency, locks and transactions (sys.session)

Command

SELECT conn_id, user, db, command, state, time,
       LEFT(current_statement, 100) AS current_statement,
       statement_latency, progress, lock_latency,
       rows_examined, full_scan, tmp_disk_tables,
       trx_state, trx_latency, last_wait
FROM sys.session
WHERE command <> 'Sleep' OR trx_state = 'ACTIVE'
ORDER BY time DESC
LIMIT 25;

What it does

Enriches SHOW PROCESSLIST with performance_schema data: running statement latency, progress percentage, lock wait time, rows examined, full scan / disk temp table info and open transaction state in one row. It also lists sessions that are in Sleep but keep a transaction open.

When to use it

In the first 30 seconds of an incident: which query has been running for how long, who is waiting on locks, which idle session holds a transaction open.

Caution

sys.session performs several joins over performance_schema; with thousands of connections the query itself may take seconds, so use the x$ views and LIMIT for frequent monitoring. progress is populated only with stage instruments enabled and for operations that report progress, such as ALTER. A session in Sleep with trx_state=ACTIVE is a transaction forgotten by an application / connection pool using autocommit=0, the most common cause of metadata lock and purge problems.