Step-by-Step: How to Optimize MySQL/PostgreSQL Database on VPS

# ─────────────────────────────────────────────────────────────
#  Recommended Production Tuning — /etc/mysql/mysql.conf.d/mysqld.cnf
#  Tested: MySQL 8.0 / MariaDB 10.11 on 8GB RAM VPS with NVMe SSD
# ─────────────────────────────────────────────────────────────

[mysqld]
# InnoDB Buffer Pool: Set to 60-70% of total available RAM
innodb_buffer_pool_size        = 5G

# Multiple buffer pool instances improve parallel throughput
innodb_buffer_pool_instances   = 4

# InnoDB Log File Size: Larger = fewer disk flushes, better write speed
innodb_log_file_size           = 512M

# Flush method: O_DIRECT bypasses OS cache, prevents double buffering
innodb_flush_method            = O_DIRECT

# Set to 2 for maximum write performance (safe on NVMe with UPS/RAID)
# Set to 1 for full ACID compliance (mission-critical transactions)
innodb_flush_log_at_trx_commit = 2

# I/O capacity: Match to your NVMe IOPS (NVMe = 2000+, SATA SSD = 800)
innodb_io_capacity             = 2000
innodb_io_capacity_max         = 4000

# Connection Management: Avoid "Too many connections" errors
max_connections                = 300
max_connect_errors             = 1000000

# Thread cache reduces overhead of spawning new threads per connection
thread_cache_size              = 50

# Query Cache is deprecated in MySQL 8.0 — use ProxySQL/Redis instead
# query_cache_type = 0 (disabled by default in MySQL 8.0)

# Temp table size — prevents spilling temp tables to disk
tmp_table_size                 = 128M
max_heap_table_size            = 128M

# Slow Query Logging — identify problematic queries
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/slow-query.log
long_query_time                = 1

# Binary Logging for replication and point-in-time recovery
log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days               = 7
binlog_format                  = ROW

# Validate config file for syntax errors before restarting
mysqld --validate-config

# Restart MySQL service to apply new settings
systemctl restart mysql

# Verify InnoDB buffer pool size is correctly applied
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Check InnoDB buffer pool hit ratio (should be > 99%)
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"

# 1. Parse slow query log to find worst offenders
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 2. Run EXPLAIN on a slow query to see its execution plan
EXPLAIN SELECT * FROM orders
  WHERE customer_id = 1024
  ORDER BY created_at DESC
  LIMIT 20;

# 3. Run EXPLAIN ANALYZE for actual runtime stats (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1024;

# 4. Check for queries doing Full Table Scans (type = ALL is bad)
# Look for: "type: ALL" → missing index → add composite index

# 5. Add a composite index to fix the query above
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

# 6. Verify index is being used after creation
EXPLAIN SELECT * FROM orders
  WHERE customer_id = 1024
  ORDER BY created_at DESC LIMIT 20;

# ─────────────────────────────────────────────────────────────
#  PostgreSQL 16 Production Tuning — 8GB RAM VPS / NVMe SSD
# ─────────────────────────────────────────────────────────────

# MEMORY
# shared_buffers: Set to 25% of total RAM (PostgreSQL's own page cache)
shared_buffers            = 2GB

# effective_cache_size: Estimate of OS + Postgres cache (set to 75% of RAM)
effective_cache_size      = 6GB

# work_mem: Memory per sort/hash operation per query (be careful with connections!)
# Formula: (available_ram - shared_buffers) / (max_connections * 3)
work_mem                  = 32MB

# maintenance_work_mem: Memory for VACUUM, CREATE INDEX, ALTER TABLE
maintenance_work_mem      = 512MB

# PARALLELISM
# max_worker_processes: Match to your VPS vCPU count
max_worker_processes      = 8
max_parallel_workers_per_gather = 4
max_parallel_workers      = 8

# WAL / DISK PERFORMANCE
# wal_buffers: Set to ~3% of shared_buffers or 16MB minimum
wal_buffers               = 64MB

# synchronous_commit: Set to off for maximum write speed
# (safe for non-critical writes; always on for financial transactions)
synchronous_commit        = off

# checkpoint_completion_target: Spread checkpoint I/O over longer period
checkpoint_completion_target = 0.9

# PLANNER SETTINGS (Tell planner about your NVMe storage cost)
# random_page_cost = 1.1 for NVMe SSD (default 4.0 is for spinning HDD)
random_page_cost          = 1.1
effective_io_concurrency  = 200

# LOGGING
log_min_duration_statement = 1000   # Log queries taking > 1 second
log_checkpoints            = on
log_lock_waits             = on

# Reload PostgreSQL to apply changes (no full restart needed for most params)
systemctl reload postgresql@16-main

# Verify shared_buffers is applied correctly
psql -U postgres -c "SHOW shared_buffers;"

# Check cache hit ratio — should be > 99% for a well-tuned server
psql -U postgres -c "
SELECT
  sum(heap_blks_read)  AS heap_read,
  sum(heap_blks_hit)   AS heap_hit,
  ROUND(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;"

# 1. Run VACUUM ANALYZE on all tables to update planner statistics
VACUUM ANALYZE;

# 2. Run VACUUM FULL to reclaim bloated disk space (requires table lock)
# Run during maintenance window only
VACUUM FULL orders;

# 3. Check table bloat — find tables wasting the most disk space
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 10;

# 4. Find missing indexes — tables with high sequential scan counts
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

# 5. Create a partial index for high-read filtered columns
-- Example: Index only active users (not all 10M rows)
CREATE INDEX idx_users_active ON users (email) WHERE status = 'active';