MySQL Performance Tuning MCP Server

A comprehensive Model Context Protocol (MCP) server for MySQL performance tuning and optimization. This server provides AI assistants with powerful tools to diagnose and optimize MySQL database performance through Performance Schema, Information Schema, and system tables.

🎯 Features

Performance Analysis Tools

• Slow Query Detection - Identify CPU and I/O intensive queries using Performance Schema
• Unused Index Detection - Identify indexes consuming resources without benefit
• Index Usage Statistics - Detailed index utilization metrics from Performance Schema
• Table Size Analysis - Storage consumption by table
• Wait Statistics - Analyze what MySQL is waiting on
• Blocking Detection - Identify blocking queries and lock information
• Statistics Health - Identify outdated table statistics
• Performance Health Check - Comprehensive database health assessment
• Execution Plan Analysis - Retrieve and analyze query execution plans (EXPLAIN)
• Slow Log Queries - Get queries from slow query log or Performance Schema

📋 Prerequisites

• Node.js 18+ with npm
• TypeScript 5.3+
• MySQL 5.7+ or MySQL 8.0+ (recommended)
• MySQL credentials with appropriate permissions:
  • SELECT permission on performance_schema (for query analysis)
  • SELECT permission on information_schema (for metadata)
  • PROCESS permission (for blocking queries)
  • SHOW DATABASES permission

Note: For best results, enable Performance Schema:

SET GLOBAL performance_schema = ON;

🚀 Installation

1. Install Globally

npm install -g mysql-performance-mcp

2. Configure Claude Desktop or Cursor

Add to your MCP configuration file:

Claude Desktop (macOS): ~/Library/Application Support/Claude/claude_desktop_config.json
Claude Desktop (Windows): %APPDATA%\Claude\claude_desktop_config.json
Cursor: ~/.cursor/mcp.json

{
  "mcpServers": {
    "mysql-performance-mcp": {
      "command": "npx",
      "args": [
        "-y",
        "mysql-performance-mcp"
      ]
    }
  }
}

3. Restart Claude Desktop or Cursor

That's it! The MCP server will be available immediately.

🛠️ Available Tools

1. get_slow_queries

Identify slow-running queries using performance_schema.events_statements_summary_by_digest.

Parameters:

• host (required): MySQL hostname or IP
• user (required): MySQL username
• password (required): MySQL password
• database (optional): Specific database name
• port (optional): Port number (default: 3306)
• top_n (optional): Number of queries to return (default: 20)
• order_by (optional): Sort metric - cpu, duration, rows, executions (default: cpu)

Example:

Get the top 10 slowest queries by CPU time from MySQL server 'mysql-prod-01'

Returns:

• Query digest text
• Execution statistics (count, CPU time, duration)
• Rows examined/sent
• First and last seen timestamps

2. get_unused_indexes

Find unused or rarely-used indexes using sys.schema_unused_indexes (if available) or information_schema fallback.

Parameters:

• host (required): MySQL hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 3306)
• min_size_mb (optional): Minimum size to consider (default: 10 MB)

Example:

Find unused indexes larger than 50 MB

Returns:

• DROP INDEX statements
• Schema, table, and index names
• Size information

Note: For best results, ensure the sys schema is available (MySQL 5.7+).

3. get_index_usage_stats

Get detailed index usage statistics using performance_schema.table_io_waits_summary_by_index_usage.

Parameters:

• host (required): MySQL hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 3306)
• schema (optional): Filter by schema name
• table (optional): Filter by table name

Example:

Get index usage statistics for schema 'mydb'

Returns:

• Fetch, insert, update, delete counts
• Total operation time
• Schema, table, and index names

4. get_table_sizes

Get table sizes, row counts, and storage statistics using information_schema.TABLES.

Parameters:

• host (required): MySQL hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 3306)
• top_n (optional): Number of tables to return (default: 50)

Example:

Get the top 20 largest tables in database 'mydb'

Returns:

• Table sizes (data, index, total)
• Row counts
• Storage engine
• Free space

5. get_wait_statistics

Analyze wait statistics using performance_schema.events_waits_summary_global_by_event_name.

Parameters:

• host (required): MySQL hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 3306)
• top_n (optional): Number of wait types to return (default: 20)

Example:

Show me the top 10 wait events on MySQL server

Returns:

• Wait type names
• Wait counts and times
• Bytes read/written

6. get_blocking_queries

Identify currently blocking queries using Performance Schema (MySQL 8.0+) or Information Schema (MySQL 5.7).

Parameters:

• host (required): MySQL hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 3306)

Example:

Find all blocking queries on the MySQL server

Returns:

• Blocked and blocking thread IDs
• Query texts
• Wait times
• Database and user information

7. get_database_statistics

Check table statistics health and last update times using information_schema.TABLES.

Parameters:

• host (required): MySQL hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 3306)
• days_old (optional): Show stats older than N days (default: 7)

Example:

Find tables with statistics older than 30 days in database 'mydb'

Returns:

• Last update times
• Days since update
• ANALYZE TABLE statements
• Priority levels

8. get_performance_health_check

Comprehensive database health check covering multiple performance aspects.

Parameters:

• host (required): MySQL hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 3306)

Example:

Run a comprehensive health check on MySQL server

Returns:

• MySQL version information
• Performance Schema status
• Top wait events
• Database sizes
• Connection status

9. get_execution_plan

Get execution plan (EXPLAIN) for a specific query.

Parameters:

• host (required): MySQL hostname
• database (required): Database name
• user (required): Username
• password (required): Password
• port (optional): Port (default: 3306)
• query_text (required): SQL query to analyze
• format (optional): EXPLAIN format - traditional, json, tree (default: traditional)

Example:

Get execution plan for query "SELECT * FROM users WHERE id = 1"

Returns:

• Execution plan in specified format
• Plan analysis with recommendations
• Warnings and optimization suggestions

10. get_slow_log_queries

Get queries from slow query log using Performance Schema or sys schema.

Parameters:

• host (required): MySQL hostname
• user (required): Username
• password (required): Password
• database (optional): Database name
• port (optional): Port (default: 3306)
• top_n (optional): Number of queries to return (default: 20)
• min_duration_ms (optional): Minimum query duration in milliseconds (default: 1000)

Example:

Get slow queries taking more than 5 seconds

Returns:

• Query texts
• Execution counts
• Duration statistics
• Rows examined/sent

🔧 MySQL Configuration

Enable Performance Schema

For best results, ensure Performance Schema is enabled:

-- Check if enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Enable if needed (requires restart)
SET GLOBAL performance_schema = ON;

Enable Slow Query Log (Optional)

-- Set slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries > 1 second

Install sys Schema (Recommended)

The sys schema provides helpful views for performance analysis:

-- MySQL 5.7+
-- Download and install from: https://github.com/mysql/mysql-sys
-- Or use mysql_upgrade (includes sys schema)

📊 Differences from SQL Server Version

| Feature | SQL Server | MySQL | |---------|-----------|-------| | Query Stats | sys.dm_exec_query_stats | performance_schema.events_statements_summary_by_digest | | Missing Indexes | sys.dm_db_missing_index_details | Not directly available (use EXPLAIN analysis) | | Index Fragmentation | sys.dm_db_index_physical_stats | Not applicable (InnoDB handles automatically) | | Wait Stats | sys.dm_os_wait_stats | performance_schema.events_waits_summary_global_by_event_name | | Query Store | Built-in feature | Not available (use Performance Schema) | | Execution Plans | XML format | EXPLAIN (traditional/JSON/tree) |

🚨 Common Issues

Performance Schema Not Available

Error: performance_schema.events_statements_summary_by_digest is not available

Solution:

SET GLOBAL performance_schema = ON;
-- Restart MySQL server

Insufficient Permissions

Error: Access denied for user

Solution: Grant necessary permissions:

GRANT SELECT ON performance_schema.* TO 'username'@'hostname';
GRANT SELECT ON information_schema.* TO 'username'@'hostname';
GRANT PROCESS ON *.* TO 'username'@'hostname';

sys Schema Not Available

Some tools will fall back to information_schema if sys schema is not available. For best results, install the sys schema.

📝 License

MIT

🤝 Contributing

Contributions welcome! Please open an issue or submit a pull request.

📚 References

• MySQL Performance Schema Documentation
• MySQL sys Schema
• Model Context Protocol