Capacity Planning

Capacity Planning Guide

When operating mysql-mcp in production, especially for large datasets, high-concurrency AI workloads, or vector search, it's essential to understand its scaling characteristics. This guide covers connection pool sizing, vector storage, schema caching, maintenance operations, InnoDB buffer pool monitoring, and token budgets.

1. Connection Pool Sizing

mysql-mcp uses mysql2 connection pooling. Each pool member holds a persistent TCP connection to the MySQL server.

Defaults

Parameter	Default	CLI Flag	Env Var
Pool size (max conns)	10	--pool-size	MYSQL_POOL_SIZE
Acquire timeout	10000	--pool-timeout	MYSQL_POOL_TIMEOUT
Queue limit	0 (∞)	--pool-queue-limit	—

Recommended Configurations

• Single-agent (stdio): Default pool of 10 is sufficient. Most agents issue queries sequentially.
• Multi-agent (HTTP): Increase to 20–50 when multiple AI clients connect simultaneously. Each concurrent tool call requires its own connection.
• Docker deployments: Ensure the MySQL server's max_connections exceeds the sum of all MCP server pools pointing at it, plus headroom for admin connections.
• 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. Vector Storage Capacity

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. Schema Cache Footprint

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_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. Maintenance Operations

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_table_maintenance tool with operation: "optimize", or execute 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 mysql_table_maintenance with operation: "analyze".
• 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. InnoDB Buffer Pool

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. Token Budget Planning

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. Instead of fetching 10,000 rows to find an anomaly, instruct the agent to write a script that processes the rows within the V8 sandbox and returns 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 JSON 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