database-performance-tuning

name	database-performance-tuning
enabled	true
description	Use when performing database performance tuning — provides a systematic approach to diagnosing and resolving database performance issues. Covers query analysis, index optimization, schema review, configuration tuning, connection management, and capacity planning for relational and NoSQL databases.
required_connections	[{"prefix":"database","label":"Database Instance"},{"prefix":"monitoring","label":"Monitoring Platform"}]
config_fields	[{"key":"db_engine","label":"Database Engine","required":true,"placeholder":"e.g., PostgreSQL, MySQL, MongoDB, SQL Server"},{"key":"primary_symptom","label":"Primary Performance Symptom","required":true,"placeholder":"e.g., slow queries, high CPU, lock contention, connection exhaustion"},{"key":"db_size_gb","label":"Database Size (GB)","required":false,"placeholder":"e.g., 200"}]
features	["PERFORMANCE","DATABASE","OPTIMIZATION"]

Database Performance Tuning

Phase 1: Performance Baseline

1. 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)
2. Identify top resource-consuming queries
3. Review slow query log
4. 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

1. 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
2. Review execution plans for top queries
3. Identify missing indexes from query patterns
4. 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

1. 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
2. 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

1. 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
2. Check for schema-level performance issues

Phase 5: Configuration Tuning

1. 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
2. Apply changes incrementally and measure impact

Configuration Changes

Parameter	Current	Recommended	Impact	Risk
				Low/Med/High

Phase 6: Monitoring & Ongoing Optimization

1. Set up performance monitoring dashboards
2. Configure alerts for key metrics degradation
3. Implement query performance regression detection
4. Schedule regular index maintenance
5. 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