Managing Database Performance
1. Monitoring Database Metrics
| Metric | Why |
|---|---|
| QPS / TPS | Workload trend |
| Latency p50/p95/p99 | User-visible perf |
| Cache hit ratio | Buffer pool effectiveness (target > 95%) |
| Active / idle connections | Pool saturation |
| Locks & waits | Contention |
| Replication lag | Replica freshness |
| CPU / IO / RAM | Resource saturation |
| Bloat / fragmentation | Storage health |
2. Analyzing Slow Queries
| Source | Detail |
|---|---|
| pg_stat_statements | Top queries by total/avg time |
| Slow query log | Per-query threshold logging |
| EXPLAIN ANALYZE | Detailed plan + actual costs |
| APM trace | Correlate with app flow |
3. Tuning Database Configuration Parameters
| Postgres Param | Guidance |
|---|---|
| shared_buffers | ~25% RAM |
| effective_cache_size | ~50–75% RAM (planner hint) |
| work_mem | Per-sort/hash; 8–64 MB; multiply by connections |
| maintenance_work_mem | Vacuum/CREATE INDEX; 256MB–2GB |
| max_connections | Low (100–200); use pooler for more |
| wal_compression | on — reduces WAL size |
| checkpoint_completion_target | 0.9 |
| random_page_cost | 1.1 on SSD |
4. Managing Connection Pooling
| Tool | Notes |
|---|---|
| PgBouncer | Transaction / session pooling for Postgres |
| Pgpool-II | Pooling + load balancing |
| ProxySQL | MySQL connection routing |
| HikariCP | Java app-side pool |
| pgx, sequelize, etc. | Built-in pools in drivers |
5. Implementing Query Result Caching
| Pattern | Detail |
|---|---|
| Cache-aside | App checks cache → DB on miss → store |
| Read-through | Cache fetches on miss transparently |
| Write-through / behind | Cache leads writes |
| Invalidation | TTL + explicit busts on writes |
| Materialized view | DB-managed cache with refresh |
6. Optimizing Buffer Pool and Memory Usage
| DB | Setting |
|---|---|
| Postgres | shared_buffers, effective_cache_size |
| MySQL InnoDB | innodb_buffer_pool_size (60–80% RAM) |
| SQL Server | max server memory |
| Oracle | SGA / PGA targets |
| Cache hit ratio | Aim > 0.95 on OLTP |
7. Managing Disk I/O Performance
| Action | Effect |
|---|---|
| NVMe SSD | Lower latency, higher IOPS |
| Separate WAL/data disks | Reduces seek contention |
| RAID 10 | Balanced read/write perf |
| Filesystem (XFS/ext4) | Tune mount options (noatime) |
| Page size alignment | Match DB block to FS block |
| Compression | Trade CPU for I/O |
8. Implementing Read Replicas for Load Distribution
| Concern | Detail |
|---|---|
| Routing | Writes → primary, reads → replicas |
| Lag-aware reads | Avoid replica if lag > SLA |
| Read-your-writes | Pin to primary briefly after write |
| Connection routing | ProxySQL, Aurora reader endpoint, Spring AbstractRoutingDataSource |
9. Using Database Profiling Tools
| Tool | DB |
|---|---|
| pg_stat_statements, pgBadger | Postgres |
| Performance Schema, sys | MySQL |
| Query Store, DMVs | SQL Server |
| AWR, ASH, SQLT | Oracle |
| Datadog, New Relic, pganalyze | Cross-DB SaaS |
10. Establishing Performance Baselines
| Step | Detail |
|---|---|
| Capture normal load | Hourly metrics over week |
| Define SLOs | e.g., p95 < 100 ms for primary queries |
| Alert on deviation | ±20% drift from baseline |
| Re-baseline | After schema/traffic shifts |