Performance Tuning

Performance Tuning Guide

The db-mcp server is designed to be highly performant, but deploying it in production environments (especially when exposed via HTTP to AI agents) requires careful tuning. This guide covers the key configuration knobs and architectural choices that impact speed, memory usage, and token efficiency.

1. Schema Caching (METADATA_CACHE_TTL_MS)

Database introspection queries (e.g., retrieving table structures, indexes, and foreign keys) are expensive. db-mcp caches schema metadata in memory to accelerate tools like sqlite_list_tables and sqlite_describe_table.

Recommended Configurations

• Production (Stable Schema): 3600000 (1 Hour) If your application's database schema rarely changes, set a high TTL. This completely eliminates introspection overhead for AI reasoning steps.
• Active Development: 60000 (1 Minute) or 0 When running migrations or actively developing the schema, lower the TTL so the AI agent doesn't receive stale structural data. Setting 0 disables the cache entirely.

export METADATA_CACHE_TTL_MS=3600000

Note: You can manually bust the cache at any time using the sqlite_clear_cache tool.

2. Native vs. WASM Backends

db-mcp ships with two backend implementations: Native (better-sqlite3) and WASM (sql.js). Choosing the correct backend is critical for performance.

Native (better-sqlite3)

When to use: Production workloads, large databases on disk, high concurrency.

• Speed: Compiles down to native C/C++ bindings. Consistently 5-10x faster than WASM for complex joins and aggregations.
• Features: Supports SQLCipher for encryption at rest, WAL mode, and native extension loading (e.g., mod_spatialite).
• Memory: Zero-copy string extraction from SQLite directly into V8 memory.

WASM (sql.js)

When to use: Highly constrained environments, strict sandboxing requirements, or platforms where native compilation is impossible (e.g., Cloudflare Workers).

• Speed: Slower, especially for heavy read operations, due to WebAssembly serialization boundaries.
• Memory: Operates entirely in memory. It must load the entire database into RAM. Do not use WASM for databases larger than a few hundred megabytes.
• Persistence: In stateful HTTP mode, the WASM database is lost if the server restarts unless explicitly backed up using the export tools.

3. Tool Filtering Strategies

Exposing all 139+ tools to an LLM simultaneously carries a severe performance penalty. It increases the system prompt payload size, costing you more tokens and slowing down the LLM's Time-To-First-Token (TTFT).

The Impact of Filters

Using the TOOL_GROUPS environment variable or the --tool-groups CLI flag allows you to selectively mount only the tools your agent needs.

• Full Mount (No Filter): Exposes all tools. High latency, high token cost.
• core,introspection: Exposes ~30 tools. Ideal for general-purpose reasoning agents. Reduces the tool description payload by ~75%.
• search,readonly: Ideal for customer-facing RAG bots. Prevents the LLM from hallucinating administrative or write tools.

# Mount only the necessary tool groups
db-mcp --tool-groups core,introspection,stats

4. Code Mode vs. Individual Tools

The db-mcp server offers two distinct paradigms for database interaction: issuing individual tool calls (e.g., sqlite_read_query) versus executing sandboxed JavaScript (sqlite_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 audit logs."
• Low latency per step: Tool calls are fast, but multi-step reasoning requires multiple roundtrips to the LLM.

When to Use Code Mode (sqlite_execute_code)

• Multi-step data pipelines: Code Mode allows the agent to execute a script that queries the database, processes the data in JavaScript, queries another table based on those results, and returns a single, summarized JSON payload.
• Token Efficiency: Code Mode drastically reduces token usage. Instead of the LLM receiving large intermediate SQL results in the context window, the sandboxed script processes the data server-side and only returns the final answer.
• Performance: Eliminates the network latency of multi-step LLM reasoning loops. The script runs entirely within a native V8 isolate on the server.

Rule of Thumb: If a question requires more than two sequential queries to answer, prompt the agent to use Code Mode.