MCP server startup blocked by synchronous orphan session scan — causes connection timeout with large EventStore

[isac322]

Summary

When the EventStore database grows large, the MCP server's synchronous cancel_orphaned_sessions() call during startup takes too long to complete, causing MCP clients (e.g., Claude Code) to time out before the JSON-RPC initialize handshake finishes.

Reproduction

• Environment: ouroboros-ai 0.27.0, Claude Code CLI, Linux
• DB: ~/.ouroboros/ouroboros.db — ~1.4 GB, 165K events, 109 sessions
• Symptom: claude mcp list → plugin:ouroboros:ouroboros — ✗ Failed to connect
• Root cause: Server takes ~38s to produce first JSON-RPC response, exceeding the client's connection timeout.

Measured startup times (time to first JSON-RPC response)

DB size	Events	Sessions	Startup time
1.4 GB	165,187	109	~38 s
547 MB	57,184	22	~17 s
121 MB	12,129	5	~5 s

Bottleneck analysis

The hot path in _run_mcp_server() (cli/commands/mcp.py):

await event_store.initialize()
repo = SessionRepository(event_store)
cancelled = await repo.cancel_orphaned_sessions()   # ← blocks serving
await server.serve()                                  # ← clients can connect only after this

find_orphaned_sessions() (orchestrator/session.py:866) performs:

1. get_all_sessions() — loads all orchestrator.session.* events
2. For each session → replay("session", session_id) — loads every event for that session into memory
3. Iterates all events to determine status via _status_from_event()

This is O(S × E) where S = number of sessions and E = average events per session. The vast majority of sessions are already terminal (COMPLETED/CANCELLED/FAILED), yet they are still fully replayed every time.

Proposed optimizations

1. Defer orphan scan to after server starts serving (highest impact, simplest change)

Move cancel_orphaned_sessions() to a background task so the MCP handshake completes immediately:

async def _deferred_cleanup(repo):
    await asyncio.sleep(2)
    await repo.cancel_orphaned_sessions()

asyncio.create_task(_deferred_cleanup(repo))
await server.serve()  # clients can connect immediately

2. SQL-level filtering to skip terminal sessions (avoids unnecessary replay)

Before calling replay() per session, exclude sessions that already have a terminal event with a single query:

SELECT DISTINCT aggregate_id FROM events
WHERE event_type = 'orchestrator.session.started'
  AND aggregate_id NOT IN (
    SELECT aggregate_id FROM events
    WHERE event_type IN (
      'orchestrator.session.completed',
      'orchestrator.session.failed',
      'orchestrator.session.cancelled'
    )
  )

Only truly RUNNING/PAUSED sessions (typically 0–1) would need full replay.

3. (Longer term) Snapshot table for session status

Maintain a lightweight read-model table updated on each status-changing event, eliminating the need for full event replay during orphan detection entirely.

Current workaround

Manually trim old events and vacuum the database:

import sqlite3
conn = sqlite3.connect(os.path.expanduser("~/.ouroboros/ouroboros.db"))
conn.execute("DELETE FROM events WHERE timestamp < '2025-01-01'")
conn.commit()
conn.execute("VACUUM")
conn.close()

[ouroboros-agent]

Triage result: this is a reproducible bug report with enough detail to proceed.

Why it passes triage

• Clear symptom and measured impact during MCP startup
• Reproduction context is included (version, environment, DB size, timings)
• The report points to the likely hot path in src/ouroboros/cli/commands/mcp.py and the orphan-scan logic in src/ouroboros/orchestrator/session.py
• Current blast radius appears limited to MCP startup and orphan-session cleanup, so this looks like a single-PR change rather than a multi-PR design effort

Initial scope assessment

• src/ouroboros/cli/commands/mcp.py
• src/ouroboros/orchestrator/session.py
• tests covering MCP startup / orphan cleanup behavior

Routing: proceeding to implementation workflow.

ouroboros-agent[bot]

[shaun0927]

The orphan-scan hot path was reworked in #326 and released as v0.30.0 (2243836). The new path:

• EventStore.get_session_activity_snapshots (event_store.py:519+) pre-aggregates per-session start / last-activity / latest-status in one SQL query using window functions — no per-session replay.
• SessionRepository.find_orphaned_sessions (session.py:945-959) bypasses the old O(S × E) replay loop entirely when the snapshot method is present.

Could you retest on v0.30.0 (or newer) against your 1.4 GB / 165K-event DB and report the new time-to-first JSON-RPC response?

• If it's back under your MCP client's connection timeout → we can close this issue.
• If it's still over budget → please share the new measurement and we'll re-scope with concrete v0.30.0 numbers.

Cross-link: PR #311 (your other PR on the same hot path) is also being closed as superseded by #326 — context there.

[shaun0927]

Triage cleanup: closing this as fixed/superseded by the large-EventStore startup work already merged.

Evidence:

• The same startup-timeout failure mode was tracked in [Bug] cancel_orphaned_sessions() timing out on large SQLite db #304 and fixed by fix(#304): prevent MCP startup timeout on large SQLite databases #326 (fix(#304): prevent MCP startup timeout on large SQLite databases), released in v0.30.0.
• Current upstream/main no longer uses the old per-session full replay hot path for orphan detection when snapshots are available: SessionRepository.find_orphaned_sessions calls EventStore.get_session_activity_snapshots, and the snapshot query pre-aggregates per-session activity/status in SQL.
• The remaining low-severity edge case in that snapshot implementation (session aggregate has lifecycle/progress events but no explicit orchestrator.session.started) is now tracked separately as Orphan detection ignores sessions without an explicit orchestrator.session.started event #508, so keeping this broader startup-timeout report open would duplicate the already-shipped fix plus the narrower follow-up.

Closing as completed. If v0.30.0 or newer still exceeds the MCP client handshake timeout on the original large DB, please reopen or file a new issue with the new time-to-first JSON-RPC measurement and version number.