/db-optimize

allowed-tools: Bash(psql:*), Bash(mysql:*), Bash(mongosh:*), Bash(sqlite3:*), Read, Write, Task
name: "Db Optimize"
description: "Comprehensive database performance optimization with intelligent analysis and automated improvements"
author: "wcygan"
tags: ["analyze","db"]
version: "1.0.0"
created_at: "2025-07-14T00:00:00Z"
updated_at: "2025-07-14T00:00:00Z"

Context

• Session ID: !`gdate +%s%N`
• Current directory: !`pwd`
• Database systems detected: !`(command -v psql >/dev/null && echo "PostgreSQL") || true; (command -v mysql >/dev/null && echo "MySQL") || true; (command -v mongosh >/dev/null && echo "MongoDB") || true; fd "\.(sqlite|db)$" . -t f | head -1 >/dev/null && echo "SQLite" || true`
• Docker services: !`docker-compose ps 2>/dev/null | rg "(postgres|mysql|mongo)" | head -5 || echo "No database containers running"`
• Database configs: !`fd "(\.env|docker-compose\.yml|database\.yml)" . -t f | head -5 || echo "No database config files found"`
• Environment variables: !`env | rg "DATABASE_URL|DB_" | cut -d'=' -f1 || echo "No database env vars"`
• Project type: !`fd "(package\.json|Cargo\.toml|go\.mod|pom\.xml)" . -t f -d 1 | head -1 | xargs basename 2>/dev/null || echo "Unknown"`

Your Task

**CRITICAL: Deploy 9-12 parallel sub-agents IMMEDIATELY for ultra-fast database optimization. Sequential analysis is OBSOLETE.**

Perform comprehensive database performance optimization for $ARGUMENTS using 9-12x faster parallel analysis.

**Expected speedup: 9-12x faster than traditional sequential optimization.**

STEP 1: Instant Parallel Database Discovery and Analysis

**IMMEDIATELY LAUNCH 10 PARALLEL AGENTS** for comprehensive database system discovery:

[Deploy all agents in single response - NO sequential execution]

• **Agent 1: Connection Discovery**: Find all database connections, test connectivity, gather credentials
• **Agent 2: Schema Analyzer**: Map all tables, views, materialized views, indexes, constraints
• **Agent 3: Query Performance Scanner**: Identify slow queries, missing indexes, N+1 problems
• **Agent 4: Configuration Auditor**: Analyze database settings, memory allocation, connection pools
• **Agent 5: Storage Optimizer**: Check table sizes, bloat, fragmentation, partition opportunities
• **Agent 6: Statistics Analyzer**: Review table statistics, histogram accuracy, cardinality estimates
• **Agent 7: Lock & Concurrency Detector**: Find blocking queries, deadlocks, lock contention
• **Agent 8: Replication & Backup Auditor**: Check lag, backup schedules, recovery readiness
• **Agent 9: Security Scanner**: Analyze permissions, exposed data, encryption status
• **Agent 10: Monitoring Gap Finder**: Identify missing metrics, alerts, performance baselines

**CRITICAL: All agents execute simultaneously. Zero waiting between agent launches.**

STEP 2: Database-Specific Deep Optimization (Parallel)

IF PostgreSQL detected: **SPAWN 10 SPECIALIZED POSTGRES AGENTS NOW**:

• **PG-Agent-1**: Analyze pg_stat_statements for query patterns
• **PG-Agent-2**: Optimize autovacuum settings and bloat
• **PG-Agent-3**: Index usage analysis with pg_stat_user_indexes
• **PG-Agent-4**: Connection pool and pgbouncer optimization
• **PG-Agent-5**: Partition strategy recommendations
• **PG-Agent-6**: WAL and checkpoint tuning
• **PG-Agent-7**: Extension usage optimization (pg_stat_statements, etc.)
• **PG-Agent-8**: Query plan analysis for top queries
• **PG-Agent-9**: Memory settings optimization (shared_buffers, work_mem)
• **PG-Agent-10**: Parallel query execution tuning

IF MySQL/MariaDB detected: **LAUNCH 10 MYSQL OPTIMIZATION AGENTS**:

• **MySQL-Agent-1**: InnoDB buffer pool optimization
• **MySQL-Agent-2**: Query cache and performance schema analysis
• **MySQL-Agent-3**: Index cardinality and selectivity analysis
• **MySQL-Agent-4**: Binary log and replication optimization
• **MySQL-Agent-5**: Thread pool and connection handling
• **MySQL-Agent-6**: Temporary table and sort buffer tuning
• **MySQL-Agent-7**: Partition pruning opportunities
• **MySQL-Agent-8**: Storage engine optimization
• **MySQL-Agent-9**: Join buffer and query optimizer hints
• **MySQL-Agent-10**: Slow query log deep analysis

IF MongoDB detected: **DEPLOY 10 MONGODB SPECIALISTS**:

• **Mongo-Agent-1**: Collection scan elimination
• **Mongo-Agent-2**: Index intersection opportunities
• **Mongo-Agent-3**: Aggregation pipeline optimization
• **Mongo-Agent-4**: Working set size analysis
• **Mongo-Agent-5**: Shard key effectiveness
• **Mongo-Agent-6**: WiredTiger cache optimization
• **Mongo-Agent-7**: Read preference and write concern tuning
• **Mongo-Agent-8**: Connection pool sizing
• **Mongo-Agent-9**: Profiler data deep analysis
• **Mongo-Agent-10**: Replica set configuration optimization

**CRITICAL: Database-specific agents run in parallel with discovery agents. Total 20+ agents working simultaneously.**

STEP 3: Parallel Risk Assessment and Optimization Strategy

**DEPLOY 12 RISK ASSESSMENT AGENTS IMMEDIATELY**:

[All agents launch simultaneously for instant comprehensive analysis]

• **Risk-Agent-1**: Production impact analyzer - downtime requirements, business hours
• **Risk-Agent-2**: Data integrity validator - constraint violations, data loss risks
• **Risk-Agent-3**: Performance regression detector - identify potential slowdowns
• **Risk-Agent-4**: Rollback strategy planner - create instant recovery procedures
• **Risk-Agent-5**: Index impact calculator - write performance vs read gains
• **Risk-Agent-6**: Memory pressure analyzer - resource competition risks
• **Risk-Agent-7**: Replication lag predictor - impact on read replicas
• **Risk-Agent-8**: Lock escalation analyzer - concurrency impact assessment
• **Risk-Agent-9**: Cost-benefit analyzer - implementation effort vs gains
• **Risk-Agent-10**: Dependency mapper - application code impact analysis
• **Risk-Agent-11**: Maintenance window optimizer - minimal disruption scheduling
• **Risk-Agent-12**: Compliance checker - regulatory and audit requ