# MCP Database Performance Analyzer

> **Autonomous MCP server for SQL schema analysis, data generation, benchmarking and optimization.**

A production-ready [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server that acts as an intelligent SQL performance analysis and optimization assistant. Point it at a SQL dump, and it will parse the schema, populate a database with realistic relational data at scale, benchmark queries, identify bottlenecks, explain root causes, and generate optimized SQL and indexing recommendations.

---

## ✨ Features

| Category | Capabilities |
|:---|:---|
| **Schema Analysis** | Parse SQL dumps · Detect PK/FK/indexes/views/procedures/triggers · Relationship graph · Missing FK indexes |
| **Data Generation** | 6 business domains · 10k–10M rows · Referential integrity · Deterministic seed |
| **Query Execution** | SELECT/JOIN/CTE/Window functions · Execution metrics · EXPLAIN analysis |
| **Benchmarking** | P50/P95/P99 latencies · QPS · Concurrent workloads · Before/after comparison |
| **Optimization** | Anti-pattern detection · Query rewriting · Index recommendations · Health score (0–100) |
| **Reporting** | JSON · Markdown · HTML · CSV · PDF · ERD diagrams |

---

## 🚀 Quick Start

### Prerequisites

- Node.js 20+
- MySQL 8.0 (or use Docker)

### Option 1: Docker (Recommended)

```bash
# Start MySQL + MCP server
docker compose up -d

# Wait for MySQL to be healthy
docker compose logs -f mcp-server
```

### Option 2: Local

```bash
# Install dependencies
npm install

# Copy and configure environment
cp .env.example .env
# Edit .env with your MySQL credentials

# Build
npm run build

# Run
npm start
```

---

## 🔧 MCP Tools

| Tool | Description |
|:---|:---|
| `load_schema` | Parse a SQL dump file and extract the full schema model |
| `analyze_schema` | Analyze relationships, ERD, missing FK indexes, circular dependencies |
| `create_database` | Execute all DDL in dependency order and validate creation |
| `generate_test_data` | Preview generated data without inserting (dry run) |
| `populate_database` | Insert realistic relational data at specified scale |
| `execute_query` | Execute a SQL query with metrics capture |
| `benchmark_query` | Benchmark a query with P50/P95/P99 and QPS |
| `benchmark_database` | Run a mixed workload simulation |
| `analyze_execution_plan` | EXPLAIN + EXPLAIN ANALYZE with insights |
| `detect_slow_queries` | Classify queries and detect root causes |
| `recommend_indexes` | Detect missing, duplicate, unused, redundant indexes |
| `optimize_query` | Anti-pattern detection + query rewriting |
| `compare_queries` | Side-by-side benchmark with improvement % |
| `generate_report` | Full report in JSON/Markdown/HTML/CSV/PDF |
| `reset_database` | Truncate or recreate the database |

---

## 📋 Example Workflow

```
1. load_schema(sql: "<content of dump.sql>")
2. analyze_schema()
3. create_database()
4. populate_database(domain: "ecommerce", datasetSize: "100k")
5. benchmark_query(sql: "SELECT * FROM orders JOIN customers ON ...")
6. analyze_execution_plan(sql: "...")
7. detect_slow_queries(queries: [...])
8. recommend_indexes()
9. optimize_query(sql: "...")
10. generate_report(formats: ["html", "pdf"])
```

---

## 🗄 Database Support

| Database | Status |
|:---|:---|
| MySQL 8.0 | ✅ Full support (primary) |
| PostgreSQL | 🔶 Stub (core ops work, advanced metrics pending) |
| SQLite | ✅ Full support (used for tests) |
| SQL Server | 📋 Planned |

---

## 🏢 Business Domains

The data generator supports 6 business domain profiles:

- **ecommerce** — customers, products, orders, payments, reviews
- **erp** — suppliers, purchase orders, inventory, warehouses, accounts
- **crm** — contacts, leads, deals, activities, campaigns
- **hospital** — patients, doctors, appointments, prescriptions, diagnoses
- **banking** — accounts, transactions, loans, cards, branches
- **hr** — employees, departments, positions, payroll, performance reviews

---

## 🔍 Optimization Detectors

| Anti-Pattern | Detection | Auto-Fix |
|:---|:---:|:---:|
| SELECT * | ✅ | ✅ |
| Large OFFSET pagination | ✅ | ✅ |
| Non-sargable predicates | ✅ | ✅ |
| Leading wildcard LIKE | ✅ | — |
| Implicit type cast | ✅ | ✅ |
| Missing FK indexes | ✅ | ✅ |
| Duplicate indexes | ✅ | ✅ |
| Unused indexes | ✅ | ✅ |
| Full table scans | ✅ | — |
| Filesort | ✅ | — |
| Temporary tables | ✅ | — |

---

## 📊 Health Score

The database health score (0–100, Grade A–F) is computed from weighted categories:

| Category | Weight |
|:---|:---:|
| Index Strategy | 30% |
| Schema Design | 25% |
| Query Efficiency | 25% |
| Data Distribution | 10% |
| Security | 10% |

---

## 🧪 Testing

```bash
# Unit tests (no external DB required)
npm test

# Integration tests (requires MySQL or use SQLite path)
npm run test:integration

# Type check
npm run typecheck
```

---

## 📁 Project Structure

```
src/
├── index.ts              # Entry point
├── server.ts             # MCP server + adapter setup
├── config/               # Typed configuration
├── adapters/             # Database adapters (MySQL, PostgreSQL, SQLite)
├── modules/
│   ├── sql-parser/       # SQL DDL/DML parser
│   ├── schema-analyzer/  # Relationship graph, orphan/cycle detection
│   ├── erd-generator/    # Mermaid ERD generation
│   ├── database-creator/ # DDL execution in dependency order
│   ├── data-generator/   # Faker-based relational data generation
│   ├── bulk-loader/      # Batched INSERT with concurrency control
│   ├── query-executor/   # Query execution with metrics
│   ├── benchmark-engine/ # P50/P95/P99 latency benchmarking
│   ├── explain-analyzer/ # EXPLAIN plan parsing and insights
│   ├── slow-query-detector/ # Query classification
│   ├── index-analyzer/   # Missing/duplicate/unused index detection
│   ├── optimization-engine/ # Anti-patterns, query rewriting, health score
│   └── report-generator/ # JSON/MD/HTML/CSV/PDF rendering
├── tools/                # All 15 MCP tool handlers
├── types/                # Shared TypeScript interfaces
└── utils/                # Logger, progress reporter, retry
```

---

## 🌐 Claude Desktop Integration

Add to your `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "db-optimizer": {
      "command": "node",
      "args": ["/path/to/database-benchmark/dist/index.js"],
      "env": {
        "DB_ADAPTER": "mysql",
        "DB_HOST": "127.0.0.1",
        "DB_PORT": "3306",
        "DB_USER": "root",
        "DB_PASSWORD": "root",
        "DB_NAME": "mcp_benchmark"
      }
    }
  }
}
```

---

## 🤝 Contributing

Contributions are welcome! Please see our [Contributing Guide](CONTRIBUTING.md) for guidelines on code style, testing, and submitting pull requests.

---

## 📄 License

This project is licensed under the terms of the [MIT License](LICENSE).