Learn › MySQL › Priya diagnoses the production crisis

MySQL Ch 10 / 18 Advanced

🚑

Priya diagnoses the production crisis

SHOW PROCESSLIST, KILL, EXPLAIN ANALYZE, InnoDB status — live performance debugging

⏱ 14 min 6 commands 5 takeaways

🚑

In this chapter

Priya

DBA, diagnosing a 2am production crisis

The story

Priya got a 2am alert: app is down. She SSHed in. MySQL was running but queries were hanging. Some were waiting over 60 seconds. The app was timing out.

She needed to diagnose and fix a live MySQL performance crisis.

STEP 1: SEE WHAT IS HAPPENING RIGHT NOW

SHOW PROCESSLIST;
-- or the full version:
SHOW FULL PROCESSLIST;

Output:

Id  | User    | Host      | db       | Command | Time | State               | Info
101 | appuser | localhost | myapp_db | Query   | 62   | Waiting for lock    | UPDATE orders SET status='paid' WHERE id=500
102 | appuser | localhost | myapp_db | Query   | 61   | Waiting for lock    | SELECT * FROM orders WHERE user_id=1 FOR UPDATE
103 | appuser | localhost | myapp_db | Query   | 0    | starting            | SHOW PROCESSLIST

Thread 101 has been waiting 62 seconds. Thread 102 is waiting too. Something is holding a lock.

-- Find locked threads:
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.INNODB_TRX;

She saw a transaction that had been open for 70 seconds -- a developer had started a transaction and forgot to COMMIT.

-- Kill the blocking thread:
KILL 101;

KILLING LONG-RUNNING QUERIES

-- Kill a specific thread:
KILL 101;

-- Kill all queries from a specific user running over 30 seconds:
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE user = 'appuser'
AND time > 30
AND command = 'Query';
-- Copy the output and run it

FINDING SLOW QUERIES IN REAL TIME

-- Queries running longer than 10 seconds right now:
SELECT id, user, time, state, LEFT(info, 100) AS query
FROM information_schema.PROCESSLIST
WHERE command = 'Query'
AND time > 10
ORDER BY time DESC;

ANALYZING QUERY PERFORMANCE

-- Use EXPLAIN ANALYZE (MySQL 8+) for real execution stats:
EXPLAIN ANALYZE SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Mumbai';

Output shows:
- actual time to execute
- actual rows examined vs estimated
- whether indexes were used
- where the bottleneck is

CHECKING INNODB STATUS

SHOW ENGINE INNODB STATUS\G

This shows:
- Current transaction list
- Deadlock history (last deadlock with full details)
- Lock wait details
- Buffer pool usage

\G formats output vertically -- much more readable for long outputs

DEADLOCKS -- WHEN TWO TRANSACTIONS BLOCK EACH OTHER

Transaction A locks row 1, wants row 2.
Transaction B locks row 2, wants row 1.
Neither can proceed. MySQL detects this and kills one automatically.

-- See last deadlock:
SHOW ENGINE INNODB STATUS\G
-- Look for the LATEST DETECTED DEADLOCK section

-- Prevent deadlocks:
-- Always lock rows in the same order in all transactions
-- Keep transactions short (COMMIT as soon as possible)
-- Use SELECT ... FOR UPDATE only when necessary

BUFFER POOL AND MEMORY

-- How full is the InnoDB buffer pool?
SHOW STATUS LIKE 'Innodb_buffer_pool%';

Innodb_buffer_pool_read_requests: how many reads were requested
Innodb_buffer_pool_reads:         how many had to go to disk (cache miss)

-- Hit ratio (should be > 99%):
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r, (
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

-- If hit ratio < 99%: increase innodb_buffer_pool_size in my.cnf

CHECKING REPLICATION LAG (IF YOU HAVE A REPLICA)

SHOW SLAVE STATUS\G
-- Look for Seconds_Behind_Master

Priya fixed the 2am crisis in 8 minutes. She killed the hung transaction, the backlog cleared, and the app recovered. She then added monitoring to alert on queries running longer than 10 seconds and transactions open longer than 30 seconds.

Key takeaways

SHOW FULL PROCESSLIST is the first command to run in any performance crisis — it shows every connection and what it is doing right now

KILL thread_id stops a hung query or stuck transaction immediately — use information_schema.PROCESSLIST to find the right ID

EXPLAIN ANALYZE (MySQL 8+) shows actual execution time and row counts alongside estimates — more useful than plain EXPLAIN

SHOW ENGINE INNODB STATUS shows deadlock history, lock waits, and transaction list — the most detailed diagnostic output

A transaction left open by a developer holds locks indefinitely — set innodb_lock_wait_timeout and monitor open transactions

Commands from this chapter

$ SHOW FULL PROCESSLIST;

See all active connections, their state, time, and current query

$ KILL 101;

Kill a specific connection or query by thread ID

$ SELECT id, time, state, LEFT(info,80) FROM information_schema.PROCESSLIST WHERE time > 10 ORDER BY time DESC;

Find queries running over 10 seconds

$ EXPLAIN ANALYZE SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'Mumbai';

Real execution stats with actual timing

$ SHOW ENGINE INNODB STATUS\G

Full InnoDB status — deadlocks, locks, transactions, buffer pool

$ SELECT * FROM information_schema.INNODB_TRX;

See all open transactions and how long they have been running