Performance Tuning

Performance Tuning Guide

🚀 Value Proposition

Optimize your AI workloads for efficiency. mysql-mcp is an Enterprise-grade MySQL MCP Server designed for scalability.

Deploying in production requires tuning to unlock full potential. Tune connection pooling and schema caching. Leverage our revolutionary Code Mode. Reduce token costs by up to 90%. Provide fast responses across all 241 specialized tools. This guide covers configuration knobs to maximize your architecture.

1. Optimize Schema Caching (METADATA_CACHE_TTL_MS)

Schema introspection queries (retrieving table structures, indexes, foreign keys from INFORMATION_SCHEMA) are expensive, particularly on databases with many tables. mysql-mcp caches this metadata in memory.

Recommended Configurations

• Production (stable schema): 30000 (30 seconds) Eliminates introspection overhead. AI agents receive cached metadata instantly.
• Active development: 5000–10000 (5–10 seconds) Keeps the AI agent in sync with schema changes while still reducing query volume.
• Disabled: 0 Every introspection call queries INFORMATION_SCHEMA live. Use only during active migration scripts.

export METADATA_CACHE_TTL_MS=30000

You can manually bust the cache at any time using the mysql_clear_cache tool.

2. Accelerate Connection Pool Tuning

mysql-mcp uses mysql2 connection pooling. Pool configuration directly impacts how many concurrent tool calls can execute without queuing.

Key Parameters

Parameter	Default	CLI	Environment Variable	Effect
connectionLimit	10	--pool-size	MYSQL_POOL_SIZE	Max simultaneous connections
acquireTimeout	10000 ms	--pool-timeout		How long to wait for a free connection
queueLimit	0 (∞)	--pool-queue-limit		Max requests waiting in queue

Guidance

• stdio (single agent): Default 10 is more than sufficient. Most AI agents issue queries sequentially.
• Streamable HTTP (multiple agents): Increase to 20–50. Each concurrent tool call needs its own connection. Monitor for acquireTimeout errors in logs.
• Short-lived queries: If your workload is mostly reads, a pool of 10–15 handles high throughput because connections are returned quickly.
• Long-running analytics: Increase the pool if agents run complex aggregations that hold connections for seconds. Consider using Code Mode to batch work into a single connection.

3. Implement Tool Filtering Strategies

Exposing all 241 tools to an LLM simultaneously carries a severe performance penalty. It increases the system prompt payload, costing more tokens and slowing Time-To-First-Token (TTFT).

The Impact of Filters

The --tool-filter CLI flag (or TOOL_FILTER env var) selectively mounts only the tools your agent needs.

• No filter (all 241 tools): High latency, high token cost. Exceeds most IDE limits.
• starter (43 tools): Recommended default. Core CRUD, JSON, transactions, text, and Code Mode. Reduces tool payload by ~82%.
• dba-monitor (43 tools): For operations/DBA agents. Performance, monitoring, sys schema, optimization.
• codemode (1 tool): Ultimate minimalism. One tool with full database API access.

# Mount only the tools your agent needs
node dist/cli.js --tool-filter starter

See Tool-Filtering for the complete list of 28 groups and 16 shortcuts.

4. Maximize Efficiency: Code Mode vs Individual Tools

mysql-mcp offers two paradigms for database interaction: individual tool calls (mysql_read_query) and sandboxed JavaScript (mysql_execute_code).

When to Use Individual Tools

• Single-step retrieval: "Get the schema for the users table."
• Simple lookups: "Search for 'payment failed' in the logs."
• Low latency per step: Tool calls are fast, but multi-step reasoning requires multiple LLM roundtrips.

When to Use Code Mode (mysql_execute_code)

• Multi-step data pipelines: Query a table, process results in JavaScript, query another table based on those results, return a single YAML-encoded summary.
• Token efficiency: 70–90% token savings. The sandboxed script processes data server-side and returns only the final answer, instead of the LLM receiving large intermediate results.
• Performance: Eliminates network latency of multi-step LLM reasoning loops. The script runs in a native V8 isolate with fleet-standard restrictions, enforced heap limits, hard timeouts, and rate limiting.
• Unbeatable ROI: Instantaneous execution of complex data pipelines means you pay a fraction of the cost for exponentially faster answers.

Rule of Thumb: If a question requires more than two sequential queries to answer, prompt the agent to use Code Mode.

5. Analyze Query Performance

mysql-mcp includes dedicated tools for diagnosing slow queries and optimizing execution plans.

Key Tools

Tool	Purpose
mysql_explain	Analyze query execution plans (EXPLAIN / EXPLAIN ANALYZE)
mysql_slow_query_analysis	Identify and analyze slow queries from the performance schema
mysql_optimizer_trace	Trace optimizer decisions for a specific query
mysql_index_recommendation	Suggest missing indexes based on query patterns, now supporting full database-wide audits
mysql_buffer_pool_stats	Monitor InnoDB buffer pool hit rates and memory usage
mysql_sysschema_*	Expose MySQL sys schema diagnostics (redundant indexes, unused indexes, statement analysis)

Workflow

1. Identify slow queries via mysql_slow_query_analysis or mysql_sysschema_statements_with_runtimes_in_95th_percentile.
2. Analyze the plan with mysql_explain using analyze: true for actual execution metrics.
3. Check for missing indexes with mysql_index_recommendation.
4. Verify improvements by re-running mysql_explain after adding indexes.

6. Master InnoDB Tuning Quick Reference

Setting	Recommendation	Tool to Monitor
innodb_buffer_pool_size	70–80% of available RAM on dedicated servers	mysql_buffer_pool_stats
innodb_flush_log_at_trx_commit	1 for durability, 2 for throughput (risk: 1s data loss on crash)	mysql_show_variables
innodb_log_file_size	Large enough to hold 1–2 hours of writes	mysql_show_status
Buffer pool hit rate	Target ≥99%	mysql_buffer_pool_stats

Note: These are MySQL server-level settings, not mysql-mcp settings. Tune them in your MySQL configuration file (my.cnf/my.ini) or via SET GLOBAL.

7. Verify Performance Characteristics

mysql-mcp's internal benchmarks (Vitest bench) confirm sub-microsecond overhead on critical hot paths:

Area	Key Metric	Notes
Tool dispatch	~5M+ ops/sec (Map.get lookup)	O(1) hash-based tool resolution
Schema validation (Zod)	~200K–500K ops/sec	Depends on schema complexity
Token estimation	~1M+ ops/sec	Content-type-aware (/3 JSON, /3.5 SQL, /4 text)
Code Mode sandbox init	~2000 ops/sec	isolated-vm cold start
Logger (filtered debug)	~5M+ ops/sec	Zero work when below minLevel

Run pnpm run bench locally for full results on your hardware.

---

See also: Capacity-Planning · Tool-Filtering · Code-Mode · Configuration