Capacity Planning

Scale Enterprise Workloads

Value Proposition Achieve robust scale and reliable multi-agent concurrency. We provide high-performance connection pooling. Efficient schema caching and optimized payloads maximize throughput. Read the full value proposition.

1. 🛜 Optimize High-Throughput Connection Pooling

• Connection lifecycle: mysql2 keeps idle connections alive. There is no idle timeout in the pool. Connections persist until the server process exits or MySQL closes them via wait_timeout.

Tip

Set --pool-size to 2× the expected concurrent AI tool calls. For a single-agent setup, the default of 10 provides ample headroom.

2. ⚡ Cache Schemas Real-Time

The server caches schema metadata in memory. This reduces redundant INFORMATION_SCHEMA queries.

• Default TTL: 30000 ms (30 seconds), controlled via METADATA_CACHE_TTL_MS.
• Footprint: Moderate schema metadata consumes minimal memory (e.g., a few megabytes).
• Invalidation: DDL tools automatically invalidate the cache upon execution.

Recommended TTLs

Environment	TTL	Rationale
Production (stable schema)	300000 (5 min) or higher	Eliminates introspection overhead during AI reasoning
Active development	5000–30000 (5–30 s)	Keeps AI in sync with frequent schema changes
Migration runs	0 (disabled)	Guarantees fresh metadata after every DDL statement

export METADATA_CACHE_TTL_MS=300000

3. 🏥 Automate Database Healing for Peak Performance

AI agents continuously modify data at scale. InnoDB tables accumulate fragmentation and stale statistics. Automate database healing workflows to maintain peak throughput.

OPTIMIZE TABLE

InnoDB does not automatically reclaim disk space from deleted rows. OPTIMIZE TABLE rebuilds tables and indexes. This defragments the data file.

• When to use: After large bulk deletes, archival operations, or significant churn.
• How: Use the mysql_write_query tool to execute OPTIMIZE TABLE.
• Note: OPTIMIZE TABLE locks the table. It remains I/O-intensive. Schedule this during low-traffic windows.

ANALYZE TABLE

• When to use: After bulk loads that change data distribution significantly. Stale statistics cause the query optimizer to choose suboptimal indexes.
• How: Use the mysql_write_query tool to execute ANALYZE TABLE.
• Note: For MySQL, consider using histogram statistics (ANALYZE TABLE ... UPDATE HISTOGRAM ON ...) for columns with skewed distributions.

Partitioned Tables

Consider range partitioning for exceptionally large tables or time-series segments. Use the mysql_write_query tool to manage them. Benefits:

• Partition pruning reduces scan scope for time-bounded queries.
• ALTER TABLE ... DROP PARTITION is instant compared to DELETE FROM ... WHERE date < X.

4. 🚀 Maximize Buffer Pool for High-Throughput AI Operations

The InnoDB buffer pool is MySQL's primary memory cache. Its size directly impacts query performance and supports enterprise-scale AI data operations.

• Monitoring: Use mysql_read_query to query performance metrics. Inspect hit rates, dirty page ratios, and free buffers.
• Sizing rule of thumb: Set innodb_buffer_pool_size to 70–80% of available RAM on a dedicated MySQL server.
• Hit rate target: A hit rate below 99% indicates a pool too small for the working set. Use mysql_read_query to review this metric.

Note

Administrators should use the pre-configured Grafana dashboard for ongoing capacity planning (visualizing buffer pool stats) to reduce the need for constant manual polling.

5. 🪙 Drive Down Operational Costs & Maximize Token Efficiency

• Cost Optimization: Transmitting raw query results to LLMs incurs scaling limits and cost bottlenecks. Code Mode mitigates this as a high-performance processing layer. It aggregates data server-side to optimize context bloat.
• Default limit: mysql-mcp enforces default limits via tool arguments (e.g., enforcing sensible default row limits on read queries) rather than a configurable global server default.
• Cursor pagination: Use the cursor parameter for scanning large tables instead of OFFSET. OFFSET 100000 forces MySQL to scan and discard 100,000 rows. Cursor pagination uses keyset ordering. This executes in O(1) time on indexed columns.
• Token-saving flags: Many tools support compact, summary, and limit flags. These provide a significant reduction in token overhead. Truncating tools return limited and totalAvailable flags. This informs agents about capped results.

---

See also: Performance-Tuning · Configuration · Tool Filtering