Monitoring MySQL Performance: Using SHOW STATUS and SHOW PROCESSLIST

When a MySQL-backed app slows down, SHOW STATUS and SHOW PROCESSLIST are the quickest built-in checks you can run before opening a dashboard. They will not explain every problem by themselves, but they answer two practical questions: what has the server been doing, and what is running right now?

Understanding Real-Time System Health with SHOW STATUS

The SHOW STATUS command, often used synonymously with SHOW GLOBAL STATUS or SHOW SESSION STATUS, provides a wealth of information regarding server activity since the last restart or since the current session began. These status variables act as counters, tracking everything from connection attempts to cache efficiency and lock waits.

Global vs. Session Status

When executing this command, it is crucial to understand the scope:

SHOW GLOBAL STATUS: Shows counters accumulated since the MySQL server instance started. This provides a bird's-eye view of overall server health and long-term trends.
SHOW SESSION STATUS: Shows counters specific only to the connection (session) you are currently using. This is useful for isolating the performance impact of specific transactions.

Key Performance Indicators (KPIs) from SHOW GLOBAL STATUS

While SHOW GLOBAL STATUS returns hundreds of variables, several are critical for initial performance triage. You typically want to pipe the output to grep or use a WHERE clause to filter for relevance.

1. Connection and Thread Monitoring

These variables help you understand connection load:

Variable Name	Description
`Threads_connected`	The number of currently open connections (clients).
`Threads_running`	The number of active threads currently executing queries (should generally be low).
`Max_used_connections`	The highest number of simultaneous connections since the server started. Useful for sizing `max_connections`.

Example: Checking active connections:

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. Query Caching and Efficiency

If you are using the legacy Query Cache (available in older MySQL versions, deprecated/removed in newer ones), these metrics are essential:

Qcache_hits: Number of times a query was served from the cache.
Qcache_lowmem_prunes: Number of queries that caused the cache to evict older entries due to low memory.

3. InnoDB Engine Metrics (Most Critical for Modern MySQL)

For modern deployments using the InnoDB storage engine, monitor buffer pool activity:

Innodb_buffer_pool_read_requests: Total read requests.
Innodb_buffer_pool_reads: Number of physical reads from disk (a high ratio of physical reads to requests indicates a need for a larger buffer pool).

Practical Tip: To quickly assess buffer pool efficiency, calculate the hit rate: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

4. Temporary Tables and Sorts

These indicate how much internal processing MySQL is doing:

Created_tmp_tables: Number of in-memory temporary tables created.
Created_tmp_disk_tables: Number of temporary tables that had to be written to disk (slow).

If Created_tmp_disk_tables is high, you may need to increase tmp_table_size or max_heap_table_size.

Diagnosing Active Workload with SHOW PROCESSLIST

While SHOW STATUS tells you what has happened, SHOW PROCESSLIST tells you what is happening right now. It displays information about the threads currently executing within the server, allowing you to identify long-running or blocked queries.

The Structure of the Process List

The command outputs several columns, each providing context about an active connection:

Column	Description
Id	The unique connection ID (used for killing the process).
User	The user account connected.
Host	The host the connection originated from.
db	The database currently being used by the thread.
Command	The type of command being executed (e.g., Query, Sleep, Connect).
Time	The number of seconds the thread has been in its current state.
State	The specific action the thread is performing (e.g., Sending data, Copying to tmp table).
Info	The actual SQL statement being executed (or truncated if long).

Filtering and Interpreting the Output

For large production systems, the full process list can be overwhelming. It is standard practice to use the FULL keyword to ensure you see the entire query text, and then filter by the Time or State columns.

1. Viewing the Full Command Text

Always use FULL if you suspect slow queries, as the standard output often truncates the Info field:

SHOW FULL PROCESSLIST;

2. Identifying Blocking or Slow Queries

Monitor the Time and Command columns:

High Time Value: Any query running for an extended duration (e.g., over 10 seconds, depending on your SLA) needs immediate investigation. Check the corresponding Info column to see the SQL.
Command = 'Sleep': These connections are idle but still consuming resources. If they accumulate excessively, consider adjusting the wait_timeout variable.
Command = 'Query': These are actively running statements. Pay close attention to their State.

3. Identifying Locking Issues

When queries are stuck waiting for resources, the State column often indicates this:

Waiting for table metadata lock
Waiting for table lock
Waiting for lock

If you see numerous threads in a waiting state, it signals contention, usually caused by a long-running transaction holding locks that others need.

Action: Terminating a Process

If you identify a runaway query that is severely degrading performance, you can terminate it using the KILL command followed by the process Id:

KILL 12345; -- Replace 12345 with the actual Id from the processlist

Warning: Use KILL with caution. Terminating an active transaction might leave the database in an inconsistent state if the transaction was halfway through a complex write operation. Always try to identify and optimize the query first if possible.

Combining Status and Process Information for Troubleshooting

Effective MySQL monitoring often involves correlation between these two commands:

Initial Check: Run SHOW FULL PROCESSLIST. Note any high-time queries or excessive connections.
Context Check: Review the connection count using SHOW GLOBAL STATUS LIKE 'Threads_connected'. Are you facing a flood or just one bad query?
Deep Dive: If a specific query is slow, analyze its impact on resource counters by reviewing Innodb_buffer_pool_reads or temporary table creation rates while the query is running (requires a baseline comparison).

By regularly checking these dynamic outputs, you move beyond guesswork and apply targeted solutions to improve MySQL stability and speed.

A Realistic Triage Routine

A good first pass takes less than a minute. Start with the process list:

SHOW FULL PROCESSLIST;

Scan for a pile of active queries, long Time values, lock waits, and many idle Sleep connections. A single slow report query is handled differently from hundreds of web connections waiting on the same table lock.

Then check thread counters:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected tells you how many clients are connected. Threads_running is usually more important during a slowdown because it shows how many threads are actively doing work. Many connected sleeping clients may be wasteful, but many running threads can mean the server is under real pressure.

Next, check whether the workload is creating disk-based temporary tables:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

These counters are cumulative since startup, so one snapshot can mislead you. Take two snapshots a few minutes apart during the incident. If disk temporary tables are increasing quickly, inspect queries with GROUP BY, ORDER BY, large joins, text columns, or missing indexes. Raising tmp_table_size might help in some cases, but a better query or index is often the cleaner fix.

Looking at InnoDB Pressure

Most modern MySQL deployments use InnoDB, so InnoDB counters deserve attention:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests counts logical read requests. Innodb_buffer_pool_reads counts reads that had to go to disk. If physical reads are rising quickly during normal traffic, the buffer pool may be too small for the working set, queries may be scanning too much data, or a batch job may be pushing useful pages out of cache.

Lock waits are another common source of pain:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

Rising row lock waits do not automatically mean InnoDB is broken. They usually mean transactions are holding locks longer than the application expects. Look for open transactions, slow updates, or code paths that start a transaction, call external services, and commit much later.

For deeper lock and transaction detail, SHOW ENGINE INNODB STATUS\\G can help, but its output is dense. Use it when the process list shows lock waits and you need to identify the transaction pattern behind them.

Safer Use of `KILL`

KILL is useful, but it is not a cleanup button. If you kill a connection running a large transaction, MySQL may need to roll back work, and rollback can take time. In some incidents that is still the right move, but make the decision deliberately.

Prefer killing the query first when your MySQL version and permissions support it:

KILL QUERY 12345;

That attempts to stop the current statement while keeping the connection alive. If the client is misbehaving or the connection must go away, use:

KILL CONNECTION 12345;

Before killing anything, capture the process list row, the user, host, database, and SQL text. After the incident, that detail helps you fix the source instead of waiting for the same query to return.

Common Process List States and What They Suggest

Sending data does not always mean MySQL is sending rows over the network. It often means the server is reading, filtering, sorting, or preparing rows. If a query spends a long time there, run EXPLAIN on the statement and look for table scans, poor join order, or missing indexes.

Copying to tmp table or Creating sort index often points to expensive sorting or grouping. Check whether an index can support the WHERE and ORDER BY pattern. Sometimes the query is doing exactly what the product asked for, but it belongs in an asynchronous report instead of a request path.

Waiting for table metadata lock often appears when DDL and normal queries collide. A seemingly simple ALTER TABLE can wait behind an open transaction, while later queries stack up behind the pending DDL. In that case, killing the oldest blocker may be safer than killing every waiting query.

Turning Counters Into Useful Evidence

Because SHOW STATUS values are mostly counters, rates are more useful than raw numbers. Capture the same variables twice:

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Wait sixty seconds, then run them again. The difference tells you the rate during that minute. This is the same idea dashboards use, but doing it manually is helpful when you only have terminal access.

Keep notes during incidents. "Threads_running jumped from 8 to 90, process list showed 70 queries waiting for metadata lock on orders, and Max_used_connections did not change" is a useful diagnosis. "MySQL was slow" is not.

When These Commands Are Not Enough

SHOW STATUS and SHOW PROCESSLIST are first-response tools. They do not replace the slow query log, Performance Schema, query plans, or host-level metrics. If the same problem returns, enable or review the slow query log and inspect the worst statements with EXPLAIN.

For recurring connection spikes, look at application pool settings and deployment behavior. Raising max_connections may buy time, but it can also let the server accept more work than it can actually run. For recurring lock waits, inspect transaction boundaries in the application. A transaction that stays open while code calls an external API can block unrelated requests and make MySQL look slower than it is.

Also check the host. If disk latency is high, CPU is saturated, memory is swapping, or a noisy neighbor is stealing resources, MySQL counters will show symptoms but not the whole cause. A good diagnosis combines database commands with system metrics.

SHOW STATUS gives you counters and context. SHOW FULL PROCESSLIST gives you the live workload. Used together, they help you tell the difference between connection pressure, one bad query, lock contention, disk-heavy temporary work, and InnoDB cache pressure.