Labsco
Dmitriusan logo

MCP DB Analyzer

★ 1

from Dmitriusan

Multi-database analysis MCP server (PostgreSQL, MySQL, SQLite). Inspects schemas, detects index problems, analyzes table bloat, and explains query plans for actionable database optimization.

🔥🔥🔥🔥✓ VerifiedFreeAdvanced setup
#email#payments#database#documents#devops#security

MCP DB Analyzer

A Model Context Protocol (MCP) server that gives AI assistants deep visibility into your databases. It inspects schemas, detects index problems, analyzes table bloat/fragmentation, and explains query plans — so your AI can give you actionable database optimization advice instead of generic suggestions.

Supports PostgreSQL, MySQL, and SQLite.

Why This Tool?

There are dozens of database MCP servers — most are CRUD gateways (run queries, list tables). This tool analyzes your database: schema problems, missing indexes, bloated tables, slow queries, vacuum health.

Other analytical MCP servers (CrystalDBA, pg-dash, MCP-PostgreSQL-Ops) cover PostgreSQL only. MCP DB Analyzer is the only analytical MCP server that supports PostgreSQL, MySQL, and SQLite in a single npx install — no Python, no Go, no Docker.

Features

  • 9 MCP tools for comprehensive database analysis
  • PostgreSQL + MySQL + SQLite support via --driver flag
  • Read-only by design — all queries wrapped in READ ONLY transactions
  • Markdown output optimized for LLM consumption
  • Zero configuration — just set DATABASE_URL

Pro Tier

Generate exportable diagnostic reports (HTML + PDF) with a Pro license key.

  • Full JVM thread dump analysis report with actionable recommendations
  • PDF export for sharing with your team
  • Priority support

$9.00/month — Get Pro License

Pro license key activates the generate_report MCP tool in mcp-jvm-diagnostics.

Quick Demo

Once configured, try these prompts in Claude:

  1. "Show me the schema and how tables are related" — Returns table structures, foreign keys, and identifies orphan tables
  2. "Are there any slow queries or missing indexes?" — Ranks slow queries by execution time and suggests indexes to add
  3. "How many connections are active? Are any queries blocked?" — Shows connection pool utilization, idle-in-transaction sessions, and blocked queries

Tools

inspect_schema

List all tables with row counts and sizes, or drill into a specific table's columns, types, constraints, and foreign keys.

Parameters:

  • table (optional) — Table name to inspect. Omit to list all tables.
  • schema (default: "public") — Database schema.
> inspect_schema

## Tables in schema 'public'

| Table       | Rows (est.) | Total Size |
|-------------|-------------|------------|
| users       | 12,450      | 3.2 MB     |
| orders      | 89,100      | 18.4 MB    |
| order_items | 245,000     | 12.1 MB    |
> inspect_schema table="users"

## Table: public.users

- **Rows (est.)**: 12,450
- **Total size**: 3.2 MB

### Columns
| # | Column | Type          | Nullable | Default |
|---|--------|---------------|----------|---------|
| 1 | id     | integer       | NO       | nextval |
| 2 | email  | varchar(255)  | NO       | -       |
| 3 | name   | varchar(100)  | YES      | -       |

analyze_indexes

Find unused indexes wasting disk space and missing indexes causing slow sequential scans. Also detects unindexed foreign keys.

Parameters:

  • schema (default: "public") — Database schema.
  • mode ("usage" | "missing" | "all", default: "all") — Analysis mode.
> analyze_indexes

### Unused Indexes (2 found)
| Table | Index              | Size   | Definition                    |
|-------|--------------------|--------|-------------------------------|
| users | idx_users_legacy   | 1.2 MB | CREATE INDEX ... (old_col)    |

### Unindexed Foreign Keys (1 found)
| Table       | Column  | FK →   | Constraint        |
|-------------|---------|--------|-------------------|
| order_items | user_id | users  | fk_items_user_id  |

explain_query

Run EXPLAIN on a SQL query and get a formatted execution plan with cost estimates, node types, and optimization warnings. Optionally run EXPLAIN ANALYZE for actual timing (SELECT queries only).

Parameters:

  • sql — The SQL query to explain.
  • analyze (default: false) — Run EXPLAIN ANALYZE (executes the query; SELECT only).
> explain_query sql="SELECT * FROM orders WHERE status = 'pending'"

## Query Plan Analysis

- **Estimated Total Cost**: 1234.56
- **Estimated Rows**: 500

### Plan Tree
→ Seq Scan on orders (cost=0..1234.56 rows=500)
  Filter: (status = 'pending')

### Potential Issues
- **Sequential Scan** on `orders` (~500 rows). Consider adding an index.

analyze_table_bloat

Analyze table bloat by checking dead tuple ratios, vacuum history, and table sizes. Recommends VACUUM ANALYZE for tables with >10% dead tuples.

Parameters:

  • schema (default: "public") — Database schema.
> analyze_table_bloat

### Tables Needing VACUUM (1 found)
| Table     | Live Tuples | Dead Tuples | Bloat % | Size  | Last Vacuum |
|-----------|-------------|-------------|---------|-------|-------------|
| audit_log | 8,000       | 2,000       | 20.0%   | 10 MB | Never       |

### Recommended Actions
VACUUM ANALYZE public.audit_log;

suggest_missing_indexes

Find tables with high sequential scan counts and zero index usage, cross-referenced with unused indexes wasting space. Provides actionable CREATE INDEX and DROP INDEX recommendations.

Parameters:

  • schema (default: "public") — Database schema.
> suggest_missing_indexes

### Tables Missing Indexes (1 found)
| Table  | Seq Scans | Index Scans | Rows   | Size  |
|--------|-----------|-------------|--------|-------|
| events | 5,000     | 0           | 50,000 | 25 MB |

### Unused Indexes (1 found)
| Table | Index            | Size | Definition                       |
|-------|------------------|------|----------------------------------|
| users | idx_users_legacy | 8 kB | CREATE INDEX ... (legacy_col)    |

DROP INDEX public.idx_users_legacy;

analyze_slow_queries

Find the slowest queries using pg_stat_statements (PostgreSQL) or performance_schema (MySQL). Shows execution times, call counts, and identifies optimization candidates.

Parameters:

  • schema (default: "public") — Database schema.
  • limit (default: 10) — Number of slow queries to return.
> analyze_slow_queries

## Slow Query Analysis (by avg execution time)

| # | Avg Time | Total Time | Calls | Avg Rows | Query |
|---|----------|------------|-------|----------|-------|
| 1 | 150.0ms  | 750000ms   | 5000  | 5        | `SELECT * FROM orders WHERE status = $1` |
| 2 | 200.0ms  | 40000ms    | 200   | 2        | `SELECT u.* FROM users u JOIN orders o...` |

### Recommendations
- **2 high-impact queries** — called >100 times with >100ms avg
- **2 queries returning few rows but slow** — likely missing indexes

analyze_connections

Analyze active database connections. Detects idle-in-transaction sessions, long-running queries, lock contention, and connection pool utilization. PostgreSQL and MySQL only.

> analyze_connections

## Connection Analysis (PostgreSQL)

### Connection States
| State | Count |
|-------|-------|
| active | 3 |
| idle | 12 |
| idle in transaction | 2 |
| **Total** | **17** |

**Max connections**: 100
**Utilization**: 17.0%

### Idle-in-Transaction Connections
| PID  | User | Duration | Query |
|------|------|----------|-------|
| 1234 | app  | 00:05:30 | UPDATE orders SET status = $1 |

analyze_table_relationships

Analyze foreign key relationships between tables. Builds a dependency graph showing entity connectivity, orphan tables (no FKs), cascading delete chains, and hub entities.

Parameters:

  • schema (default: "public") — Database schema.
> analyze_table_relationships

## Table Relationships

**Tables**: 5
**Foreign Keys**: 4

### Entity Connectivity
| Table | Incoming FKs | Outgoing FKs | Total |
|-------|-------------|-------------|-------|
| users **hub** | 5 | 0 | 5 |
| orders | 1 | 2 | 3 |

### Orphan Tables (no FK relationships)
- `audit_log`

### Cascading Delete Chains
- **users** → cascades to: orders, addresses
  - **orders** → further cascades to: order_items

analyze_vacuum

Analyze PostgreSQL VACUUM maintenance status. Checks dead tuple ratios, vacuum staleness, autovacuum configuration, and identifies tables needing manual VACUUM. PostgreSQL only.

> analyze_vacuum

Detects:

  • Tables with high dead tuple ratios (>10% warning, >20% critical)
  • Tables never vacuumed or analyzed
  • Autovacuum disabled globally
  • Autovacuum configuration issues

Output includes:

  • Findings grouped by severity (CRITICAL / WARNING / INFO)
  • Tables needing VACUUM with dead tuple percentages
  • Full vacuum history per table
  • Autovacuum configuration settings

Security

  • All queries are wrapped in READ ONLY transactions by default
  • EXPLAIN ANALYZE is restricted to SELECT queries only
  • DDL/DML statements are rejected in ANALYZE mode
  • No data modification queries are allowed

Part of the MCP Java Backend Suite