Capacity Planning

Scale for Enterprise Workloads

🚀 Value Proposition

Unleash infinite scale. Our Enterprise Capacity Planning framework guarantees rock-solid resilience during multi-agent orchestration. Dynamically adapt to extreme connection throughput, enforce optimized vector indexing, and optimize your token budget with precision Code Mode scaling.

Mastering scaling characteristics unlocks peak performance. This is paramount for massive datasets and high-concurrency AI orchestrations. This guide covers connection pool sizing and vector storage. We also cover schema caching and maintenance operations. Maximize your token budget and effortlessly monitor your InnoDB buffer pool to guarantee flawless execution.

1. 🏊 Hyper-Scale Connection Pooling for Limitless Concurrency

• Connection lifecycle: Idle connections are kept alive by mysql2. There is no idle timeout in the pool — connections persist until the server process exits or MySQL closes them via wait_timeout.

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

2. 🗄️ Optimize Vector Storage: Enterprise AI Embeddings

mysql-mcp's vector tools use MySQL 9.0+ native VECTOR columns with server-side distance computation via DISTANCE(). Vectors are stored in InnoDB, not in memory.

Storage Overhead

MySQL VECTOR columns store embeddings as compact binary arrays (4 bytes per float32 dimension):

Embedding Model	Dimensions	Storage/Row	100K Rows	1M Rows
OpenAI text-embedding-3-small	1536	~6 KB	~600 MB	~6 GB
OpenAI text-embedding-3-large	3072	~12 KB	~1.2 GB	~12 GB
Sentence Transformers all-MiniLM-L6	384	~1.5 KB	~150 MB	~1.5 GB

Performance Considerations

• VECTOR INDEX (MySQL 9.1+): Use mysql_vector_create_index for approximate nearest-neighbor (ANN) search on large datasets. Without it, mysql_vector_search performs a full table scan computing distances for every row.
• Distance metrics: COSINE (default), EUCLIDEAN, and DOT are computed server-side by MySQL — no V8 memory pressure.
• Pre-filter with WHERE: Use the filter parameter on search tools to narrow the candidate set before distance computation, especially on unindexed tables.

3. 🧠 Zero-Latency Schema Caching for Real-Time Agent Context

mysql-mcp caches schema metadata (table structures, columns, indexes, foreign keys) in memory to reduce redundant INFORMATION_SCHEMA queries.

• Default TTL: 30000 ms (30 seconds), controlled via METADATA_CACHE_TTL_MS.
• Footprint: A database with 200 tables and 2,000 columns will consume approximately 5–15 MB of memory for the cached metadata.
• Invalidation: The cache is automatically invalidated when DDL tools (mysql_create_table, mysql_schema_apply_migration, etc.) execute. You can also manually clear it with the mysql_admin_clear_cache tool.

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

4. 🛠️ Perform Routine Maintenance: Autonomous Database Healing

As AI agents rapidly insert, update, and delete data, InnoDB tables can accumulate fragmentation and stale optimizer statistics.

OPTIMIZE TABLE

InnoDB does not automatically reclaim disk space from deleted rows. OPTIMIZE TABLE rebuilds the table and its indexes, defragmenting the data file.

• When to use: After large bulk deletes, archival operations, or significant churn.
• How: Use the mysql_admin_optimize_table tool, or execute mysql.admin.optimizeTable() via Code Mode.
• Note: OPTIMIZE TABLE locks the table (online DDL in MySQL 8.0+, but still I/O-intensive). Schedule 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_admin_analyze_table tool, or execute mysql.admin.analyzeTable() via Code Mode.
• Note: For MySQL 8.0+, consider using histogram statistics (ANALYZE TABLE ... UPDATE HISTOGRAM ON ...) for columns with skewed distributions.

Partitioned Tables

If a table exceeds 50 GB or has distinct time-series segments, consider range partitioning. Use the mysql_partition_* tools to manage partitions. Benefits:

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

5. 🗜️ Maximize InnoDB Buffer Pool: Unleash Peak Memory Performance

The InnoDB buffer pool is MySQL's primary memory cache for data and indexes. Its size directly impacts query performance.

• Monitoring: Use mysql_buffer_pool_stats to inspect hit rates, dirty page ratios, and free buffer counts.
• Sizing rule of thumb: Set innodb_buffer_pool_size to 70–80% of available RAM on a dedicated MySQL server.
• Hit rate target: A buffer pool hit rate below 99% usually indicates the pool is too small for the working set. Check via mysql_show_status → Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads.

6. 📉 Optimize Token Budgets: Reduce AI Costs

Passing raw database results to an LLM context window is the most common scaling bottleneck in MCP servers.

• Code Mode is your primary shield: Use mysql_execute_code for data aggregation. Do not fetch 10,000 rows to find an anomaly. Instruct the agent to write a script. Process the rows inside the V8 sandbox. We enforce strict isolated-vm restrictions, a configurable payload cap (default 100KB, up to 50MB), and rate limiting. Return only the summary.
• Default limit: mysql-mcp enforces a default LIMIT 50 on mysql_read_query.
• Cursor pagination: For scanning large tables, use the cursor parameter (returned as nextCursor) rather than OFFSET. OFFSET 100000 requires MySQL to scan and discard 100,000 rows. Cursor pagination uses keyset ordering (WHERE id > X) which is O(1) in indexed columns.
• Token-saving flags: Many tools support compact: true, summary: true, and limit: N to reduce YAML encoding structural overhead. Tools that truncate results return limited: true and totalAvailable so the agent knows results were capped.

---

See also: Performance-Tuning · Configuration · Tool-Filtering