MySQL-Performance-Tuner-Mcp

Retrieve slow queries from MySQL performance_schema.

Returns the top N slowest queries with detailed statistics:

• Total execution time

• Number of calls

• Average execution time

• Rows examined vs rows sent

• Full table scans

• Temporary tables usage

Requires performance_schema to be enabled (default in MySQL 5.6+). For older versions, use the slow query log instead.

Note: This tool excludes queries against MySQL system schemas (mysql, information_schema, performance_schema, sys) to focus on user/application query performance.

Analyze a MySQL query's execution plan using EXPLAIN.

Provides detailed analysis of:

• Query execution plan with access types

• Index usage and potential missing indexes

• Join types and optimization opportunities

• Rows examined estimates

• Key usage and key length

Supports EXPLAIN FORMAT=JSON for MySQL 5.6+ for detailed cost analysis. Use EXPLAIN ANALYZE (MySQL 8.0.18+) for actual execution statistics.

WARNING: With analyze=true, the query is actually executed!

Get detailed statistics for MySQL user tables.

Returns information about:

• Table size (data, indexes, total)

• Row counts and average row length

• Index information

• Auto-increment values

• Table fragmentation

• Engine type and collation

Helps identify tables that may need:

• Optimization (OPTIMIZE TABLE)

• Index improvements

• Partitioning consideration

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys).

Get AI-powered index recommendations for MySQL user tables.

Analyzes query patterns from performance_schema to recommend indexes:

• Identifies queries with full table scans

• Finds queries not using indexes efficiently

• Suggests composite indexes for multi-column filters

• Prioritizes recommendations by potential impact

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys).

Based on MySQL performance_schema statistics and query patterns. Similar to MySQLTuner's index analysis but with more detailed recommendations.

Find unused and duplicate indexes in MySQL user tables.

Identifies:

• Indexes with zero or very few reads since server start

• Duplicate indexes (same columns in same order)

• Redundant indexes (one index is a prefix of another)

Removing unused indexes can:

• Reduce storage space

• Speed up INSERT/UPDATE/DELETE operations

• Reduce memory usage for index buffers

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys).

Based on information_schema and performance_schema statistics.

Get detailed index statistics for MySQL user tables.

Returns:

• Index cardinality and selectivity

• Index size and memory usage

• Read/write operation counts

• Index efficiency metrics

Helps identify:

• Low cardinality indexes

• Oversized indexes

• Infrequently used indexes

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys).

Perform a comprehensive MySQL database health check.

Analyzes multiple aspects of MySQL health:

• Connection statistics and pool usage

• Buffer pool hit ratio

• Query cache efficiency (if enabled)

• InnoDB metrics (buffer pool, log, transactions)

• Replication status (if configured)

• Thread and connection usage

• Uptime and general status

Returns a health score with detailed breakdown and recommendations. Based on MySQLTuner analysis concepts.

Get currently active queries in MySQL.

Shows:

• Running queries with execution time

• Long-running queries

• Blocked queries waiting on locks

• Idle transactions that may be holding locks

Useful for:

• Identifying queries causing performance issues

• Finding blocking transactions

• Monitoring query execution in real-time

Review MySQL configuration settings and get recommendations.

Analyzes key performance-related settings:

• Memory settings (buffer pool, sort buffer, join buffer)

• InnoDB settings

• Connection settings

• Query cache settings (if applicable)

• Logging settings

Compares against best practices and system resources.

Analyze MySQL wait events to identify bottlenecks.

Wait events indicate what processes are waiting for:

• Lock waits (row locks, table locks)

• I/O waits (disk operations)

• Buffer pool waits

• Log waits

• Mutex and semaphore waits

This helps identify:

• I/O bottlenecks

• Lock contention patterns

• Resource saturation

Analyze InnoDB engine status from SHOW ENGINE INNODB STATUS.

Parses and analyzes:

• Buffer pool statistics and hit ratios

• InnoDB log information and checkpoints

• Row operations (reads, inserts, updates, deletes)

• Transaction information and history list

• Semaphore waits and mutex contention

• Deadlock information (if any)

• I/O statistics and pending operations

• Redo log performance

Based on MySQLTuner's InnoDB analysis patterns. Provides actionable recommendations for InnoDB optimization.

Detailed analysis of InnoDB buffer pool usage.

Analyzes:

• Buffer pool allocation by schema and table

• Page types and distribution

• Hit ratios and efficiency metrics

• Memory allocation patterns

• Recommendations for buffer pool sizing

Note: When analyzing by schema/table, this tool only shows user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys).

Uses sys schema views for detailed breakdown when available.

Analyze InnoDB transactions and locking.

Identifies:

• Long-running transactions

• Lock waits and blocking transactions

• Deadlock history

• History list length (purge lag)

• Transaction isolation levels

Helps identify transaction-related performance issues.

Analyze SQL statements from performance_schema/sys schema.

Provides comprehensive analysis of:

• Statement digest summaries

• Total and average execution times

• Rows examined vs rows sent ratios

• Statement error rates

• Most expensive queries

Based on MySQLTuner's performance schema analysis. Requires performance_schema enabled.

Note: This tool excludes queries against MySQL system schemas (mysql, information_schema, performance_schema, sys) to focus on user/application query analysis.

Get statements that create temporary tables.

Temporary tables can cause performance issues when:

• They're created on disk instead of memory

• They're created too frequently

• They grow too large

Identifies queries that should be optimized.

Note: This tool excludes queries against MySQL system schemas (mysql, information_schema, performance_schema, sys) to focus on user/application query analysis.

Get statements that perform sorting operations.

Identifies queries with:

• File sorts (on disk)

• Memory sorts

• Sort merge passes

High file sort ratios indicate need for index optimization or sort_buffer_size increase.

Note: This tool excludes queries against MySQL system schemas (mysql, information_schema, performance_schema, sys) to focus on user/application query analysis.

Get statements that perform full table scans.

Full table scans can severely impact performance on large tables. Identifies queries that:

• Don't use any index

• Use a non-optimal index

These queries are prime candidates for index optimization.

Note: This tool excludes queries against MySQL system schemas (mysql, information_schema, performance_schema, sys) to focus on user/application query analysis.

Get statements that produce errors or warnings.

Identifies queries with:

• Error counts

• Warning counts

• Error rates

Helps identify problematic application queries.

Note: This tool excludes queries against MySQL system schemas (mysql, information_schema, performance_schema, sys) to focus on user/application query analysis.

Calculate MySQL memory usage and provide recommendations.

Analyzes:

• Per-thread memory buffers (read_buffer, sort_buffer, join_buffer, etc.)

• Global server buffers (key_buffer, innodb_buffer_pool, etc.)

• Maximum potential memory usage

• Current memory utilization

Based on MySQLTuner's memory calculation methodology. Helps identify memory-related configuration issues.

Get memory usage breakdown by host or user.

Uses sys schema or performance_schema to show:

• Memory allocated per host

• Memory allocated per user

• Memory by event/operation type

Requires performance_schema memory instrumentation enabled.

Analyze memory usage for user tables and caches.

Shows:

• Table cache usage and hit rates

• Table definition cache efficiency

• Open tables vs table_open_cache

• InnoDB buffer pool by table

Note: Buffer pool breakdown by table only shows user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys).

Helps optimize table caching parameters.

Analyze storage engine usage and statistics for user tables.

Provides:

• List of available engines and their status

• Table count and size by engine

• Engine-specific metrics (InnoDB, MyISAM, MEMORY, etc.)

• Recommendations for engine optimization

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys) by default.

Based on MySQLTuner's engine analysis patterns.

Find user tables with significant fragmentation.

Fragmentation occurs when:

• Data is deleted from tables

• Tables are frequently updated

• VARCHAR/TEXT columns are modified

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys) by default.

High fragmentation wastes disk space and can slow queries.

Analyze auto-increment columns for potential overflow.

Checks:

• Current value vs maximum value for column type

• Usage percentage

• Tables approaching overflow

Note: This tool only analyzes user/custom tables and excludes MySQL system tables (mysql, information_schema, performance_schema, sys) by default.

Based on MySQLTuner's auto-increment analysis.

Get MySQL replication status and health.

Analyzes:

• Master/Source status and binary log position

• Slave/Replica status and lag

• Replication errors and warnings

• Binary log configuration

• Semi-sync replication status

Works with both MySQL and MariaDB terminology.

Get Galera cluster status for MariaDB/Percona XtraDB Cluster.

Analyzes:

• Cluster membership and state

• Node status (Primary, Donor, Joiner)

• Flow control status

• Replication health metrics

• Certification and write-set conflicts

Only applicable to Galera-enabled MySQL variants.

Get MySQL Group Replication status.

Analyzes:

• Group membership and state

• Member roles (PRIMARY/SECONDARY)

• Replication channels

• Transaction certification

• Flow control

Only applicable to MySQL with Group Replication enabled.

Perform comprehensive MySQL security analysis.

Checks:

• Anonymous user accounts

• Users without passwords

• Users with weak password policies

• Root account security

• Password validation plugin status

• SSL/TLS configuration

• Host-based access patterns

• Dangerous privileges (SUPER, FILE, GRANT)

Based on MySQLTuner's security_recommendations() function.

Analyze privileges for a specific user or all users.

Shows:

• Global privileges

• Database-level privileges

• Table-level privileges

• Column-level privileges

• Routine privileges

Helps identify excessive or missing privileges.

Check MySQL audit log configuration and status.

Analyzes:

• Audit plugin status

• Audit log configuration

• Recent audit events (if accessible)

• Compliance recommendations

Supports MySQL Enterprise Audit, MariaDB Audit Plugin, and Percona Audit Log Plugin.