SHOW PROCESSLIST is the first command anyone runs when MySQL misbehaves, and it is also the most over-interpreted output in the whole system. Thread states were named by and for people debugging the server source, not for operators triaging an incident. Some states carry real signal. Some are so broad they mean almost nothing. Knowing which is which is the difference between a five-minute triage and a wild goose chase.
This is a working guide to reading the processlist on MySQL 8.x: where to read it from, which columns to trust, what the famous states actually mean, and the grouped-snapshot habit that turns a wall of rows into a diagnosis.
Read it from performance_schema, not the legacy path
There are several ways to see the same threads, and they are not equivalent. The classic SHOW PROCESSLIST (and INFORMATION_SCHEMA.PROCESSLIST) traditionally gathers data from the thread manager under a mutex - on a server that is already struggling with thousands of connections, the diagnostic itself can add contention. That is a bad property for an incident tool.
MySQL 8.0.22 added an alternative implementation backed by performance_schema (enabled via performance_schema_show_processlist), and the old INFORMATION_SCHEMA.PROCESSLIST path is deprecated as of 8.0.35. But the cleanest habit is to skip the compatibility layers and query performance_schema.threads directly: it is designed to be read without blocking the server, it includes background threads, and it joins cleanly to every other performance_schema table. The one prerequisite is having instrumentation on, which the defaults give you - the low-overhead performance_schema setup covers keeping that cheap.
SELECT processlist_id AS id,
processlist_user AS user,
processlist_command AS command,
processlist_time AS time_s,
processlist_state AS state,
LEFT(processlist_info, 120) AS query
FROM performance_schema.threads
WHERE processlist_id IS NOT NULL
AND processlist_command <> 'Sleep'
ORDER BY processlist_time DESC;One more legacy trap: plain SHOW PROCESSLIST truncates query text to 100 characters. If you use it at all, use SHOW FULL PROCESSLIST, or you will triage the wrong query because the interesting WHERE clause was cut off.
The columns people misread
Command tells you whether a thread is doing anything at all. Sleep means the connection is idle between statements - a hundred Sleep rows is a pool doing its job, not a problem, though a Sleep row whose session holds an open transaction is a very different story that the processlist alone will not show you.
Time is the number of seconds the thread has spent in its current command - for an executing query, effectively how long the statement has been running. Sort by it descending; the top of that list is where incidents live. But note that for Sleep threads, Time is just idle duration, so "Time = 3600" means abandoned connection, not hour-long query.
State is the column everyone quotes and the one that needs the most skepticism. It reports which internal phase the thread is passing through, and some phases absorb nearly all execution time while others flash by. A state is evidence only when it persists across repeated samples or when many threads pile into it at once.
The death of "Sending data"
For two decades, "Sending data" was the most misleading state in MySQL. The name suggests network transfer; in reality it covered the bulk of query execution - reading rows, evaluating conditions, doing the actual work. Generations of engineers concluded the network was slow when the query was simply reading ten million rows. As of MySQL 8.0.17 that state is gone, shown as the equally generic but at least honest "executing".
The operational lesson survived the rename: a broad execution state tells you the query is working, not why it is slow. When threads sit in "executing", the follow-up questions are about the query itself - its plan, the rows it examines, what it waits on - answered from performance_schema statement and wait instrumentation, not from staring at the state column harder.
Pileups that do mean something
Some states earn their keep precisely because they are specific.
A pileup in "statistics" means many threads are stuck in optimizer plan preparation, where InnoDB may perform index dives to estimate cardinality. Seeing one thread pass through statistics is normal; seeing dozens parked there suggests contention or an optimization-phase problem - frequently correlated with very high concurrency, complex multi-table joins, or I/O pressure making the sampling itself slow. It is one of the few states where the diagnosis genuinely starts from the state name.
"Waiting for table metadata lock" is the most actionable state in the list. Someone is running (or ran) DDL, or a transaction touched a table and never committed, and now everything queuing behind the metadata lock stalls - the classic shape is one ALTER TABLE waiting on a forgotten transaction while every subsequent query on that table queues behind the ALTER. The state names the problem; finding the culprit takes one more query, below.
"Copying to tmp table on disk" and "Creating sort index" mean the query is materializing intermediate results - GROUP BY, ORDER BY, DISTINCT work that outgrew memory and spilled. Persistent residents of these states are index-and-query-shape problems; MySQL internal temp tables on disk covers why the on-disk variant is so much worse and how to catch it from status counters.
Finding the metadata lock blocker
When "Waiting for table metadata lock" appears, do not start killing waiters - they are victims. The blocker is often a session showing an innocent Sleep, because an uncommitted transaction holds its MDL until commit. The sys schema resolves the chain directly:
SELECT object_schema,
object_name,
waiting_pid,
waiting_query,
blocking_pid,
sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE waiting_pid <> blocking_pid;This requires the metadata lock instrument (wait/lock/metadata/sql/mdl), which is enabled by default in 8.0. The blocking_pid is the session to inspect - and, after confirming what it is, usually the one to kill. Killing the ALTER instead just reschedules the same pileup for tomorrow.
Build the triage habit
Individual rows lie; distributions tell the truth. My first incident query is never the full listing - it is the grouped summary: SELECT processlist_state, COUNT(*) FROM performance_schema.threads WHERE processlist_id IS NOT NULL AND processlist_command <> 'Sleep' GROUP BY processlist_state ORDER BY COUNT(*) DESC. Forty threads in one state is a diagnosis; forty threads in forty states is load.
Three more habits compound. Sample twice, thirty seconds apart - a state that persists for the same thread across samples is real; one that changed is noise. Save the snapshot during every incident, because the processlist at 14:03 is unrecoverable at 14:30 when the postmortem starts. And record Threads_running alongside, since the grouped processlist explains a spike that the counter merely detects.
A word on the intervention the processlist usually leads to: killing things. KILL QUERY terminates the statement but keeps the connection; KILL (or KILL CONNECTION) drops the session entirely - prefer the former when the client is a pooled application, because pools react badly to vanishing connections. And before killing a long-running write, understand what you are buying: InnoDB must roll back everything the transaction changed, and rolling back can take as long as - sometimes longer than - the work already done, during which the locks stay held. Killing a nine-hour UPDATE at hour eight is frequently worse than letting it finish. The processlist tells you the thread's age; performance_schema's transaction instrumentation tells you how much it has modified, and that second number is the one that should drive the kill decision.
Where MonPG fits
Continuous processlist sampling with history - so you can see the state distribution from five minutes before the page - is exactly what a monitoring platform should do for you. MonPG does this today for PostgreSQL (pg_stat_activity, wait events, lock chains), and MySQL support is in active development with performance_schema.threads sampling as a first-class part of the design. It is not available yet: see MySQL monitoring (coming soon) for status. If you also run PostgreSQL, start there - the triage habit transfers almost verbatim.