When troubleshooting a slow or unresponsive MySQL instance, the first step is always the same: find out what is happening right now. These quick diagnostic queries use MySQL’s built-in SHOW STATUS and SHOW PROCESSLIST commands to surface the most useful information in seconds — no external tools required.

1. Active Processes and Running Queries

SHOW PROCESSLIST lists all current connections and their state. By default it truncates long query texts — use the FULL modifier to see complete statements. The G modifier formats each row vertically, which is much more readable for long output in a terminal.

-- Standard output (query text truncated at 100 chars)
SHOW PROCESSLIST;

-- Full query text, vertical output (easier to read in terminal)
SHOW FULL PROCESSLISTG

2. Instance Uptime

MySQL stores operational metadata in status variables queryable with SHOW STATUS. Uptime is expressed in seconds.

SHOW STATUS LIKE '%uptime%';
-- Example output:
-- +---------------------------+--------+
-- | Variable_name             | Value  |
-- +---------------------------+--------+
-- | Uptime                    | 880329 |  -- ~10 days
-- | Uptime_since_flush_status | 8347   |
-- +---------------------------+--------+

3. Connection Statistics

This view reveals the current number of active threads, total historical connections, and any aborted connections — useful to detect connection pool exhaustion or authentication problems.

SHOW STATUS LIKE '%conn%';

Key variables to watch: Threads_connected (current active connections), Max_used_connections (peak since last restart), Aborted_connects (failed connection attempts — non-zero values can indicate authentication or network issues).

4. Query Cache Status

MySQL’s query cache (available up to MySQL 5.7; removed in MySQL 8.0) stores the result of SELECT queries in memory to avoid repeated disk reads for identical queries. First, verify whether it is enabled:

SHOW VARIABLES LIKE '%query_cache%';

If query_cache_type is ON, check cache effectiveness with:

SHOW STATUS LIKE 'QCache%';

Key metrics: Qcache_hits vs Qcache_inserts (hit ratio), Qcache_lowmem_prunes (cache evictions due to insufficient memory — a high value means the cache is too small), Qcache_not_cached (queries that bypass the cache).