Performance Tuning

Performance Tuning Guide

1. 🚀 Value Proposition

• Achieve sub-microsecond latency on critical paths.
• Ensure your database orchestrations run at maximum velocity.
• Keep agents fast and costs minimal with ultra-low overhead.

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