Database Performance Tuning

Database Performance Tuning

Database performance problems almost always come down to two root causes: missing indexes causing full table scans, or queries that are logically correct but execute inefficiently. Before tuning server parameters, find and fix the slow queries — a query that scans 10 million rows without an index will be slow regardless of how much RAM you throw at the server. The right order: identify slow queries, explain them, add indexes, then tune server parameters.

Identifying slow queries

# MySQL: enable slow query log:
sudo mysql -e "SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 1;
  SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"

# Or in /etc/mysql/mysql.conf.d/mysqld.cnf:
# slow_query_log = 1
# long_query_time = 1    ← log queries taking more than 1 second
# log_queries_not_using_indexes = 1

# Analyze the slow query log:
mysqldumpslow -s t /var/log/mysql/slow.log | head -20    # Sort by total time

mysqldumpslow output

Count: 234  Time=5.23s (1223s)  Lock=0.00s (0s)  Rows=10234.5 (2394813)
  SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC

# PostgreSQL: enable slow query logging in postgresql.conf:
# log_min_duration_statement = 1000    ← log queries taking more than 1000ms
# log_statement = 'all'               ← log all statements (high volume, use carefully)
sudo systemctl reload postgresql

Using EXPLAIN

# MySQL: analyze query execution plan:
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

EXPLAIN output — shows full table scan (bad)

+----+-------------+--------+------+---------------+------+----------+------+----------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len  | ref  | rows     | Extra       |
+----+-------------+--------+------+---------------+------+----------+------+----------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL     | NULL | 10234567 | Using where |
+----+-------------+--------+------+---------------+------+----------+------+----------+-------------+
-- type=ALL means full table scan on 10 million rows — very bad

# EXPLAIN ANALYZE actually runs the query with timing:
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

# PostgreSQL EXPLAIN ANALYZE output (more detail):
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

Indexing strategies

# Add a missing index (for the query above):
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);   # covering index

# Verify index is now being used:
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

EXPLAIN after adding index — much better

| type | key                          | rows | Extra       |
| ref  | idx_orders_status_created    | 245  | Using index |
-- type=ref with rows=245 instead of 10 million — 40,000x improvement

# Check existing indexes:
SHOW INDEXES FROM orders;    -- MySQL
# PostgreSQL:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

# Find unused indexes (PostgreSQL):
SELECT indexname FROM pg_stat_user_indexes WHERE idx_scan = 0;

MySQL InnoDB buffer pool

# The InnoDB buffer pool is MySQL's main cache for data and indexes
# Default: 128MB — too small for production databases

# Set to 50-70% of available RAM (for dedicated DB server):
# In /etc/mysql/mysql.conf.d/mysqld.cnf:
# innodb_buffer_pool_size = 2G    ← for 4GB server

# Check buffer pool hit rate (should be >99%):
sudo mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"

Buffer pool statistics

Innodb_buffer_pool_reads       | 1234     ← disk reads (should be low)
Innodb_buffer_pool_read_requests | 9876543 ← cache hits
-- Hit rate: (9876543 - 1234) / 9876543 = 99.98% (good)

Conclusion

The performance tuning workflow: enable slow query logging, identify the worst offenders with mysqldumpslow or PostgreSQL's slow query log, run EXPLAIN on those queries, and add indexes where you see full table scans (type=ALL in MySQL, Seq Scan in PostgreSQL) on large tables. Indexes solve 80% of database performance problems. Once queries are indexed, tune innodb_buffer_pool_size (MySQL) or shared_buffers (PostgreSQL) to match available RAM. These two steps alone typically reduce response time by 10-100x.