Search guides

Fix a database that is slow

We'll find slow queries with the slow query log, run EXPLAIN to spot missing indexes, add indexes, and rule out disk or memory pressure—or tell you when to escalate.

Troubleshooting · Databases
20–45 min
What you'll need
  • Database admin access (or sudo)
  • Ability to restart or reload the database service
Step 1 of 6

Quick triage — pick your path

Get started

Choose the option that matches what you see. You can jump straight to that section.

Show full guide

Steps

Goal: Find slow queries, run EXPLAIN to spot missing indexes, add indexes, and rule out disk or memory pressure.

  • Run a simple query (SELECT 1) to confirm the database responds. Check application logs for which operations lag.
  • Good: Database responds but specific queries are slow. Proceed to Enable slow query log.
  • Bad: Database does not respond—different problem. See When to escalate.

Enable slow query log

Goal: Identify which queries are slow.

  • MySQL: SET GLOBAL slow_query_log=1; SET GLOBAL long_query_time=2; (or in my.cnf). PostgreSQL: ALTER SYSTEM SET log_min_duration_statement=2000; then SELECT pg_reload_conf();
  • Run typical workloads. Inspect the log (MySQL: SHOW VARIABLES LIKE 'slow_query_log_file'; then read that file; PostgreSQL: check log_destination).
  • Good: Slow queries listed. Proceed to Run EXPLAIN and add indexes.
  • Bad: No slow queries—check if threshold is too high or workload differs.

Run EXPLAIN and add indexes

Goal: See why queries are slow and add indexes.

  • Copy a slow query from the log. Run EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) on it.
  • Look for “full table scan”, “Seq Scan”, or “type: ALL”—these indicate missing indexes.
  • Add indexes on columns in WHERE, JOIN, and ORDER BY: CREATE INDEX idx_name ON table(column);
  • Retest. The query plan should show index scan instead of full scan.
  • Good: Queries use indexes and are fast.
  • Bad: Still slow—check disk and memory; escalate if needed.

Check disk and memory

Goal: Rule out disk or memory pressure.

  • Run df -h and df -i. Check iostat and top for I/O wait and memory usage.
  • If disk is full, see fix-linux-runs-out-of-disk. If memory is low, see fix-linux-server-runs-out-of-memory.
  • Good: Disk and memory are fine—focus on query optimization.
  • Bad: Resource pressure—fix disk or memory first.

When to escalate

Escalate if:

  • The database does not respond (connection or crash).
  • You have added indexes and tuned settings but the database is still slow.
  • You need schema redesign or hardware upgrades.

Provide EXPLAIN output, slow query samples, df -h, df -i, and memory stats.

Verification

  • Slow queries no longer appear in the slow query log (or appear less often).
  • EXPLAIN shows index scans instead of full table scans for the fixed queries.
  • Application response times improve.
  • df -h and df -i show adequate disk space; memory is not exhausted.

Escalation ladder

Work from the device outward. Stop when the problem is fixed.

  1. Slow query log Enable and inspect the slow query log to find long-running queries.
  2. EXPLAIN and indexes Run EXPLAIN on slow queries; add indexes on filtered and joined columns.
  3. Locks and contention Check for lock contention and long-running transactions.
  4. Disk and memory Check disk space, I/O wait, and memory; rule out resource pressure.
  5. Escalate Provide EXPLAIN output, slow query samples, df -h, df -i, memory stats.

What to capture if you need help

Before calling support or posting for help, have these ready. It speeds everything up.

  • Slow query log samples
  • EXPLAIN output for slow queries
  • df -h and df -i output
  • Memory and I/O stats
  • Steps already tried

Does the database respond but specific queries are slow?

Run a simple query (SELECT 1) to confirm the database is up. Check application logs for which operations lag.

Run a simple query and time it. Good: database responds quickly but application queries lag—proceed to slow query log. Bad: database does not respond—different problem (connection, crash); escalate.

Yes No

You can change your answer later.

Are slow queries logged?

Enable the slow query log. MySQL: slow_query_log=1, long_query_time=2. PostgreSQL: log_min_duration_statement=2000.

Enable the slow query log. Run typical workloads. Inspect the log. Good: slow queries listed—run EXPLAIN on them. Bad: no slow queries logged—check if threshold is too high or workload is different.

Yes No

You can change your answer later.

Does EXPLAIN show full table scans or missing indexes?

Run EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) on slow queries. Look for Seq Scan, type: ALL, or full table scan.

Run EXPLAIN on the slow queries. Look for full table scan, Seq Scan, or type: ALL. Yes (missing indexes): add indexes on WHERE, JOIN, ORDER BY columns. No (indexes used): check lock contention, disk, memory.

Yes No

You can change your answer later.

Add indexes and retest

Add indexes: CREATE INDEX idx_name ON table(column). Retest the queries. Good: queries use indexes and are fast. Bad: still slow—check disk, memory, locks; escalate if needed.

Done

Is disk full or memory low?

Run df -h, df -i. Check iostat, top for I/O wait and memory.

Run df -h and df -i. Check iostat and top. Disk full: see fix-linux-runs-out-of-disk. Memory low: see fix-linux-server-runs-out-of-memory. Both OK: check lock contention; escalate with EXPLAIN and slow query samples.
Question

Is disk or memory the bottleneck?

Continue

You can change your answer later.

Escalate

Provide EXPLAIN output, slow query samples, df -h, df -i, and memory stats. Escalate if database does not respond, indexes did not help, or schema/hardware changes are needed.

Done

Reviewed by Blackbox Atlas

Frequently asked questions

Why is my database slow?
Common causes: missing indexes (full table scans), slow queries, disk I/O saturation, or low memory. Use EXPLAIN and the slow query log to identify the cause.
How do I find slow queries?
Enable the slow query log (MySQL: slow_query_log; PostgreSQL: log_min_duration_statement). Run EXPLAIN on the logged queries to see execution plans.
When should I escalate a slow database?
If you have added indexes and optimized queries but the database is still slow, or if you need schema changes or hardware upgrades.

Rate this guide

Was this helpful?

Continue to