The database is slow: a step-by-step performance triage runbook for MySQL and PostgreSQL

#Before you start

"The database is slow" can mean one bad query, a blocked table, a saturated server or a problem that isn't in the database at all. Guessing wastes the first hour. Work through the same steps every time, from the outside in, and stop when you find the cause:

1. Is it really the database?
2. Is the host saturated?
3. What is running right now?
4. What is it waiting for?
5. Which queries cost the most?
6. Why is the top query slow (its plan)?
7. Is the engine itself unhealthy?
8. Are connections the problem?

Write down the time you started and what you see at each step. It makes the incident review and the fix much easier.

#Step 1: is it really the database?

Check the application's own timing first: APM traces, or logs that show time spent in database calls.

• If database time is small and the app is still slow, look at the app, the network or a downstream API instead.
• If every query is slower, suspect the host, locks or connections (steps 2, 4 and 8).
• If one feature is slow, suspect a specific query (steps 5 and 6).

Measure a trivial query from the app server, so you know whether the network adds delay:

# MySQL
time mysql -h db01 -u app -p -e "SELECT 1;"
# PostgreSQL
time psql "host=db01 dbname=app user=app" -c "SELECT 1;"

#Step 2: is the host saturated?

On the database server (or the cloud metrics for managed services):

uptime                      # load average vs number of CPUs
vmstat 1 5                  # r = run queue, si/so = swapping, wa = I/O wait
iostat -x 1 5               # %util, await (ms) per disk
free -m
df -h                       # a full data or log disk stops writes

On managed services, check CPU, IOPS against the provisioned limit, burst credits and free storage in the provider's metrics.

#Step 3: what is running right now?

MySQL (the sys schema is installed by default in 8.0+):

SELECT conn_id, user, db, command, state, time, trx_state,
       LEFT(current_statement, 120) AS current_statement
FROM   sys.session
WHERE  command <> 'Sleep'
ORDER  BY time DESC
LIMIT  20;

PostgreSQL:

SELECT pid, usename, datname, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       now() - xact_start  AS in_transaction_for,
       LEFT(query, 120) AS query
FROM   pg_stat_activity
WHERE  state <> 'idle' AND backend_type = 'client backend'
ORDER  BY query_start
LIMIT  20;

Look for:

• The same query running many times at once: a hot query, or a retry storm from the app.
• One query running for minutes or hours: a report, a stuck batch, or a bad plan.
• Sessions idle in transaction (PostgreSQL state = 'idle in transaction', MySQL trx_state = ACTIVE with command = Sleep): they hold locks and block cleanup.

#Step 4: what are sessions waiting for?

#Lock waits

MySQL (row locks and metadata locks):

SELECT waiting_pid, LEFT(waiting_query, 80) AS waiting_query,
       blocking_pid, LEFT(blocking_query, 80) AS blocking_query, wait_age
FROM   sys.innodb_lock_waits;

SELECT object_schema, object_name, waiting_pid, blocking_pid
FROM   sys.schema_table_lock_waits;

PostgreSQL:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, wait_event,
       now() - query_start AS waiting_for, LEFT(query, 80) AS query
FROM   pg_stat_activity
WHERE  cardinality(pg_blocking_pids(pid)) > 0;

Follow blocked_by / blocking_pid to the head blocker: the session that blocks others but isn't blocked itself. It is often idle in transaction.

#Overall wait picture (PostgreSQL)

SELECT wait_event_type, wait_event, COUNT(*)
FROM   pg_stat_activity
WHERE  state = 'active'
GROUP  BY 1, 2
ORDER  BY 3 DESC;

#Emergency relief

-- MySQL: stop a statement, or the whole connection
KILL QUERY 1234;
KILL 1234;

-- PostgreSQL: cancel the statement, or end the session
SELECT pg_cancel_backend(1234);
SELECT pg_terminate_backend(1234);

Warning

Ending a session rolls back its open transaction. A large uncommitted change can take a long time to roll back and keeps its locks while it does. Check what the session is doing and how long its transaction has been open before you kill it, and tell its owner.

More detail: PostgreSQL lock waits, MySQL InnoDB deadlocks, MySQL metadata lock pile-ups.

#Step 5: which queries cost the most?

Rank queries by total time (calls × average). A 5 ms query run a million times usually matters more than one 30-second report.

MySQL (Performance Schema statement digests, on by default):

SELECT LEFT(query, 100) AS query, db, exec_count, total_latency, avg_latency,
       rows_examined_avg, rows_sent_avg, full_scan
FROM   sys.statement_analysis
LIMIT  10;

rows_examined_avg far above rows_sent_avg, or full_scan set, usually means a missing or unused index.

PostgreSQL (needs the pg_stat_statements extension in shared_preload_libraries):

SELECT LEFT(query, 100) AS query, calls,
       ROUND(total_exec_time::numeric / 1000, 1) AS total_s,
       ROUND(mean_exec_time::numeric, 1) AS mean_ms,
       rows, shared_blks_read, temp_blks_written
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

High shared_blks_read means reading from disk; temp_blks_written means sorts or hashes spilling to disk.

To compare "now" with "normal", reset the counters at a known time (CALL sys.ps_truncate_all_tables(FALSE); in MySQL, SELECT pg_stat_statements_reset(); in PostgreSQL) and look again after 15 minutes. Do this only if nobody else depends on the cumulative numbers.

Deeper guides: MySQL slow query log and Performance Schema, pg_stat_statements tuning.

#Step 6: why is the top query slow?

Get the real plan with run-time numbers. Both engines support EXPLAIN ANALYZE, which executes the query.

-- MySQL 8.0.18+
EXPLAIN ANALYZE
SELECT o.id, o.total FROM orders o
WHERE  o.customer_id = 42 AND o.status = 'OPEN'
ORDER  BY o.created_at DESC LIMIT 20;

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total FROM orders o
WHERE  o.customer_id = 42 AND o.status = 'OPEN'
ORDER  BY o.created_at DESC LIMIT 20;

Caution

EXPLAIN ANALYZE runs the statement. For UPDATE/DELETE/INSERT, wrap it in a transaction and roll back (BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;), or use plain EXPLAIN on production.

What to look for in either engine:

PostgreSQL detail: reading EXPLAIN ANALYZE. MySQL index review: sys schema index tuning.

#Step 7: is the engine itself unhealthy?

MySQL / InnoDB:

-- Buffer pool: reads from disk vs logical reads (want the first to be tiny)
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests',
   'Threads_running', 'Threads_connected', 'Created_tmp_disk_tables');

-- Undo history: long transactions stop purge
SELECT name, count FROM information_schema.innodb_metrics
WHERE  name = 'trx_rseg_history_len';

• Innodb_buffer_pool_reads growing fast: working set doesn't fit; consider a larger innodb_buffer_pool_size.
• History list length in the millions and rising: a long-running transaction is preventing purge, and queries slow down. Find and end it (step 3).
• Threads_running much higher than CPU count: too much concurrent work.

PostgreSQL:

-- Cache hit ratio per database (want > 99% for OLTP)
SELECT datname, ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp_size, deadlocks
FROM   pg_stat_database WHERE datname NOT LIKE 'template%';

-- Dead rows and vacuum status on the busiest tables
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM   pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Oldest transaction holding back cleanup
SELECT pid, now() - xact_start AS age, state, LEFT(query, 60)
FROM   pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;

Checkpoint activity is in pg_stat_bgwriter (PostgreSQL 16 and earlier) or pg_stat_checkpointer (17+). Many requested (not timed) checkpoints mean max_wal_size is too small for the write load.

Related: bloat and autovacuum, PostgreSQL memory settings.

#Step 8: are connections the problem?

-- MySQL
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
SELECT user, host, command, COUNT(*) FROM information_schema.processlist
GROUP  BY user, host, command ORDER BY 4 DESC LIMIT 10;

-- PostgreSQL
SELECT usename, application_name, state, COUNT(*)
FROM   pg_stat_activity GROUP BY 1, 2, 3 ORDER BY 4 DESC LIMIT 10;
SHOW max_connections;

Hundreds of connections, most of them idle, usually mean oversized application pools. Each connection costs memory, and a burst of active ones overloads the CPU. Use a pooler (ProxySQL for MySQL, PgBouncer for PostgreSQL) and right-size app pools.

See MySQL too many connections and PostgreSQL too many clients.

#Symptom to first action

#Prevent it next time

• Keep the evidence switched on all the time: Performance Schema and the slow query log (long_query_time of 1 second or less) in MySQL; pg_stat_statements, log_min_duration_statement, log_lock_waits and auto_explain in PostgreSQL.
• Record a baseline: normal CPU, I/O, active sessions and top 10 queries. You can't recognise "abnormal" without it.
• Alert on long transactions, lock waits, replication lag and free disk space, not just CPU.
• Review new queries' plans before they reach production, and keep statistics fresh after big data loads.