TickYouOff
Sign in
Back
🗄️

SQL Database Performance Tuning Checklist

Hard 18 items · 2 hours
testuser's avatar
testuser Published 2 weeks ago
Technical

This checklist guides engineers and developers through practical steps to diagnose and improve SQL database performance. It covers slow query analysis, indexing, query fixes, configuration tuning, caching, replicas, and post-change monitoring.

Progress
0 / 18
  1. Take baseline of current performance metrics — Record latency, throughput, error rates and slow query counts before changes.
  2. Enable slow query log and set a sensible threshold — Set threshold (e.g., 100–500ms) and enable logging for 24–72 hours.
  3. Aggregate and prioritize slow queries by total time — Sort by cumulative time to find queries that cost the most overall.
  4. EXPLAIN slow queries and review execution plans — Check for table scans, missing index usage, temp files, and row estimates.
  5. Identify missing or low-selectivity indexes to add — Target WHERE, JOIN, ORDER BY and GROUP BY columns with high selectivity.
  6. Create and test indexes in a staging environment — Validate improvements with EXPLAIN and regression tests before production.
  7. Schedule maintenance window and backup production before changes — Take full backup or snapshot and plan rollback steps prior to changes.
  8. Apply tested schema and index changes to production during maintenance — Run changes in maintenance window, monitor impact and be ready to rollback.
  9. Remove unused or duplicate indexes to reduce write overhead — Audit index usage and drop those with no reads but high write cost.
  10. Rewrite N+1 queries and batch operations to reduce roundtrips — Use joins, IN queries, or batched selects to cut repetitive queries.
  11. Parameterize queries and use prepared statements — Reduce parsing overhead and improve plan reuse with parameterization.
  12. Configure and tune connection pooling (size, timeouts) — Match pool size to DB capacity and app concurrency to avoid thrashing.
  13. Tune database configuration for memory and temp settings — Adjust buffer pool, work_mem, temp settings and max_connections carefully.
  14. Evaluate and implement a caching layer for frequent reads — Consider app-level cache or Redis/Memcached to offload hot read traffic.
  15. Set up read replicas for read-scaling and test replication lag — Use replicas for reporting; monitor lag and failover behavior.
  16. Consider table partitioning for very large tables — Partition by date or key to improve pruning and maintenance.
  17. Monitor post-change metrics and compare to baseline — Track latency, throughput, errors, and slow query counts after changes.
  18. Automate regular maintenance: update stats, vacuum, and rebuild indexes — Schedule jobs for stats and occasional index maintenance to keep plans optimal.
Sign in to save
📝 My Notes