cloudthinker-ai Database Performance Tuning
Phase 1: Performance Baseline
- Collect current performance metrics
- CPU utilization (average and peak)
- Memory utilization and buffer cache hit ratio
- Disk I/O (IOPS, throughput, latency)
- Active connections and connection wait times
- Transactions per second
- Replication lag (if applicable)
- Identify top resource-consuming queries
- Review slow query log
- Document current database configuration parameters
Performance Baseline
| Metric | Current Value | Healthy Range | Status |
|---|---|---|---|
| CPU utilization | % | < 70% | OK/Warning/Critical |
| Buffer cache hit ratio | % | > 99% | |
| Disk IOPS | < max provisioned | ||
| Active connections | < max_connections * 80% | ||
| Avg query time | ms | < target ms | |
| Deadlocks/hour | 0 |
Phase 2: Query Analysis
- Identify problematic queries
- Queries with full table scans
- Queries with high execution time
- Queries with high execution frequency
- Queries causing lock contention
- N+1 query patterns
- Review execution plans for top queries
- Identify missing indexes from query patterns
- Check for parameter sniffing issues
Top Queries by Impact
| Query | Avg Time | Calls/min | Total Time % | Full Scans | Action |
|---|---|---|---|---|---|
| ms | % | Yes/No | Index/Rewrite/Cache |
Phase 3: Index Optimization
- Analyze current indexes
- Identify unused indexes (consuming write overhead)
- Identify duplicate or overlapping indexes
- Find missing indexes for frequent query patterns
- Review index bloat and fragmentation
- Check composite index column ordering
- Design index changes
Index Recommendations
| Table | Recommendation | Type | Affected Queries | Write Impact | Priority |
|---|---|---|---|---|---|
| Add/Remove/Modify | B-tree/Hash/GIN | Low/Med/High | 1-5 |
Phase 4: Schema & Data Model Review
- Review schema design
- Identify tables with excessive columns
- Check for denormalization opportunities
- Review data types (oversized columns)
- Assess partitioning candidates (large tables)
- Evaluate archival strategy for historical data
- Check for schema-level performance issues
Phase 5: Configuration Tuning
- Review and optimize database configuration
- Memory allocation (shared buffers, work memory)
- Connection limits and pooling
- WAL / transaction log settings
- Checkpoint frequency and timing
- Autovacuum settings (PostgreSQL) or table maintenance
- Query cache settings
- Parallel query configuration
- Apply changes incrementally and measure impact
Configuration Changes
| Parameter | Current | Recommended | Impact | Risk |
|---|---|---|---|---|
| Low/Med/High |
Phase 6: Monitoring & Ongoing Optimization
- Set up performance monitoring dashboards
- Configure alerts for key metrics degradation
- Implement query performance regression detection
- Schedule regular index maintenance
- Plan capacity based on growth trends
Counter-Rationalizations
| Shortcut | Counter | Why |
|---|---|---|
| "We can skip some steps for this case" | Adapt the workflow steps, don't skip them | Skipped steps are where incidents and oversights originate |
| "The user seems to already know what to do" | Complete all workflow phases with the user | The workflow catches blind spots that experience alone misses |
| "This is a minor case, full process is overkill" | Scale the process down, don't turn it off | Minor cases become major when unstructured; the process scales, not disappears |
| "I'll fill in the details later" | Complete each section before moving on | Deferred details are forgotten; real-time capture is more accurate |
| "The template output isn't necessary" | Always produce the structured output format | Structured output enables comparison, audit trails, and handoff to other teams |
Output Format
- Baseline Report: Current performance metrics snapshot
- Query Analysis: Top problematic queries with solutions
- Index Recommendations: Changes with expected impact
- Configuration Changes: Parameter adjustments with rationale
- Monitoring Setup: Dashboards and alerts configuration
Action Items
- Collect performance baseline metrics
- Analyze and optimize top problematic queries
- Implement index changes (test in staging first)
- Apply configuration tuning incrementally
- Set up ongoing performance monitoring
- Schedule monthly query performance review