Capacity Planning

🚀 Scale Enterprise Workloads

🚀 Value Proposition

Our Capacity Planning framework supports resilience during multi-agent orchestration. You can seamlessly scale for high-throughput workloads. Enforce optimized vector indexing. Optimize token budgets using precision Code Mode scaling.

Mastering scaling characteristics unlocks peak performance. This secures massive datasets and high-concurrency AI orchestrations. This guide covers connection pool sizing and vector storage. It also details schema caching and maintenance. Maximize your token budget. Monitor your InnoDB buffer pool to guarantee flawless execution.

1. 🏊 Scale 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.

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

mysql-mcp's vector tools use MySQL 9.0+ native VECTOR columns. They compute distances server-side via DISTANCE(). InnoDB stores vectors on disk, 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 searches on large datasets. Otherwise, mysql_vector_search performs full table scans. This computes distances for every row.
• Distance metrics: MySQL computes COSINE (default), EUCLIDEAN, and DOT server-side. This eliminates V8 memory pressure.
• Pre-filter with WHERE: Use the filter parameter on search tools. This narrows candidates before computing distance. It helps especially on unindexed tables.

3. 🧠 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: A database with 200 tables and 2,000 columns consumes 5–15 MB of memory.
• Invalidation: DDL tools automatically invalidate the cache upon execution. You can manually clear it using mysql_admin_clear_cache.

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. 🛠️ Automate Database Healing

AI agents rapidly modify data. This causes InnoDB tables to accumulate fragmentation. It also creates stale optimizer statistics.

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_admin_optimize_table tool, or execute mysql.admin.optimizeTable() via Code Mode.
• 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_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

Consider range partitioning for tables exceeding 50 GB or time-series segments. Use mysql_partition_* tools 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.

5. 🗜️ Maximize Buffer Pool

The InnoDB buffer pool is MySQL's primary memory cache. 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 hit rate below 99% indicates a pool 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

Passing raw results to an LLM context window causes scaling bottlenecks in MCP servers.

• Code Mode is your primary shield: Use mysql_execute_code for data aggregation. Do not fetch 10,000 rows to find anomalies. Instruct your agent to write a script. Process rows inside the V8 sandbox. We enforce strict isolated-vm restrictions, payload caps, and rate limits. Return only summaries.
• Default limit: mysql-mcp enforces a default LIMIT 50 on mysql_read_query.
• 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 reduce YAML encoding overhead. Truncating tools return limited and totalAvailable flags. This informs agents about capped results.

---

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