[PERFORMANCE]: Reduce SQLite busy_timeout from 30s to 5s (configurable)

[crivetimihai]

Summary

The SQLite busy_timeout pragma is set to 30 seconds (30000ms), which can cause API requests to hang for up to 30 seconds waiting for database locks. This should be reduced to 5 seconds (configurable) for better user experience.

Impact

• User Experience: Requests can hang for 30 seconds before failing
• Resource Waste: Worker threads blocked waiting on locks aren't serving other requests
• Cascading Failures: Long timeouts can cause request queue buildup under contention
• Health Checks: Load balancers may mark instances unhealthy during long waits

Affected Code

File: mcpgateway/db.py (lines 202-226)

cursor.execute("PRAGMA busy_timeout=30000")  # Line 221

Proposed Solution

1. Add configurable setting to config.py (after line 1067)

db_sqlite_busy_timeout: int = Field(
    default=5000,
    ge=1000,
    le=60000,
    description="SQLite busy timeout in milliseconds (default: 5000ms)"
)

2. Update db.py line 221

cursor.execute(f"PRAGMA busy_timeout={settings.db_sqlite_busy_timeout}")

3. Add to .env.example (after line 64)

# SQLite busy timeout in milliseconds (default: 5000)
# DB_SQLITE_BUSY_TIMEOUT=5000

Timeout Recommendations

Scenario	Timeout	Rationale
Development	5000ms	Fast failure for debugging
Production API	5000-10000ms	Balance between retry and fast failure
Batch Operations	30000ms	Bulk imports may need longer waits
CI/CD Testing	2000ms	Fast test execution

Acceptance Criteria

• Default timeout reduced to 5000ms (5 seconds)
• Timeout configurable via DB_SQLITE_BUSY_TIMEOUT environment variable
• .env.example documents the setting
• Setting follows db_* naming convention
• No regression in existing tests
• Passes make verify

References

• SQLite PRAGMA busy_timeout
• SQLite WAL Mode
• Full analysis: todo/performance/reduce-sqlite-busy-timeout.md