Prevent process-wide CPU/GC/tsnet starvation during sync all

[darron]

Problem

A running sync all can still starve the entire dbrain process. In the investigation for #159, a zero-work /favicon.ico request measured approximately 17.2 seconds while sync was active. The delay is not explained by the request's database work and can affect HTTP and MCP responsiveness.

This is the remaining Channel B — process starvation problem. It is distinct from the deferred link-capture, SQLite-pool, and synchronous-feed-fetch mitigations delivered in #162.

Goal

Keep HTTP and MCP request latency bounded and responsive while sync all is running by identifying and removing or bounding the process-wide CPU, GC, scheduler, or tsnet starvation mechanism.

Investigation

• Reproduce the stall with a production-shaped sync all and concurrent no-work HTTP, API, and MCP requests.
• Capture request latency distributions and correlate them with CPU saturation, Go scheduler/runtime metrics, GC pauses, goroutine counts, memory pressure, network activity, and tsnet behavior.
• Attribute the delay to the actual sync stage and mechanism before choosing a mitigation; do not assume SQLite contention or the semantic lease is the cause.
• Test the fix under sustained sync load, including the stages and local model/network configuration that reproduce the original stall.

Acceptance criteria

• The root cause is documented with reproducible evidence and a clear distinction between database/lease wait and process-wide starvation.
• HTTP and MCP no-work/read-only probes remain responsive throughout sync all, with the prior multi-second starvation outlier eliminated or materially bounded and any remaining limit explicitly documented.
• The verification includes before/after latency evidence plus runtime/GC/CPU/tsnet observations from the same workload.
• The mitigation does not bypass authoritative write coordination or silently weaken sync correctness.
• Add regression or diagnostic coverage that prevents the starvation mechanism from returning unnoticed.

Related work

• #159 — Sometimes when dbrain is importing on a sync all
• #162 — Deferred capture and contention safeguards

[darron]

Revised plan: verify Channel B on current main before changing code

Amp and OpenCode independently audited the production evidence and agree that the original issue premise is not established. The cited 17.234-second authenticated /favicon.ico request did not overlap sync all: the sync ran from about 10:13:14 through 10:26:54 local, while the request began at 10:50:26.421. The request also was not DB-free: authenticated requests perform the GitHub-user approval lookup in SQLite. The same interval contains /api/bootstrap, which shared the installed v0.8.16 web pool's single connection.

The aggregate log audit supports that explanation rather than process starvation:

• 145 genuinely unauthenticated favicon responses had a maximum latency of 93 ms.
• All 41 authenticated favicon responses taking at least one second overlapped /api/bootstrap.
• Cheap MCP initialize/tools-list/notification pairs had a maximum end-to-end latency of 162 ms; cheap inner methods topped out at 108 ms.

Current main is 2f393115940c9237e9ef8aec79fbf4931ced9bba, which includes the four-connection interactive web/MCP pool from #160/#162. Production was still running d6ec75b/v0.8.16 when the old sample was captured.

Phase 1: current-main reproduction

Run a production-shaped, controlled instance built from current main, with an actually overlapping sync all. During the same active sync interval, issue and independently time:

1. unauthenticated /favicon.ico (the DB-free probe);
2. authenticated /favicon.ico (the auth-DB/pool comparison);
3. authenticated /api/bootstrap; and
4. cheap MCP initialize, tools/list, and notifications/initialized operations.

Correlate every request-duration log with the exact sync start/finish interval. The run must identify the binary commit and demonstrate that the four-connection web/MCP pool is active. Do not infer process starvation from an authenticated route or from a request that did not overlap sync.

Decision gate

• If the DB-free favicon and cheap MCP probes remain responsive, and only authenticated favicon/bootstrap pairs show contention, document the before/after evidence and close this issue as already addressed by Decouple deferred link capture from sync contention #160/Harden deferred link capture follow-ups #162. There is no speculative CPU, GC, scheduler, tsnet, throttle, or process-isolation PR.
• If a genuinely DB-free probe or cheap MCP operation still has a multi-second outlier during an active sync, capture evidence for that same interval before choosing a fix: Go execution trace, CPU profile, runtime/GC samples, process CPU/RSS, tsnet observations, and per-stage sync metrics. Trace the outlier to a concrete stage/mechanism, then amend this plan with the narrow mitigation and build the regression test and PR.

Acceptance

The work is complete when the issue records either:

1. a current-main reproduction showing the remaining contention is the already-fixed interactive-pool channel, with no new code required; or
2. a same-interval DB-free/MCP reproduction, source-backed root cause, focused regression test, and mitigation PR whose verification covers the affected request path.

Until the second outcome is observed, do not create a draft PR merely to carry a speculative process-throttling change.

[darron]

Current-main verification boundary

The read-only checkout used for the plan is exactly:

2f393115940c9237e9ef8aec79fbf4931ced9bba (main, merged #162)

The relevant current-main regression packages pass:

go test -count=1 ./internal/store ./internal/remote ./web
ok  github.com/darron/dbrain/internal/store  9.104s
ok  github.com/darron/dbrain/internal/remote 0.925s
ok  github.com/darron/dbrain/web            2.478s

The exact checkout also builds successfully. This is source/test/build verification, not a claim that the live production process has exercised the new pool: production remains the installed v0.8.16 / d6ec75b process. A genuinely same-process reproduction still requires a controlled serve remote instance built from current main (or an authorized deployment), with the probe matrix and overlap correlation specified above.

Therefore there is still no evidence-backed code change to put in a draft PR. Opening one now would be a speculative process-throttling change, which the two independent audits reject.

[darron]

Review correction: measure bootstrap separately

OpenCode’s final independent review adds one required distinction to the gate:
/api/bootstrap must be measured as its own probe. It is the slow holder in the historical pairs and runs backlog, activity, and source-activity queries sequentially. A current-main run can therefore eliminate pool head-of-line blocking while bootstrap remains slow because of query cost on the large database. That result is a separate bootstrap/query-performance issue, not Channel B, and must not be treated as a passing “no-work” probe.

The current production database is already WAL, and current main reasserts WAL at writable open; no migration or journal-mode change is part of this plan.

If a deterministic CI gate is needed, use a temp WAL database, seeded records, fake feeds with injected delays, and concurrent clients for unauthenticated favicon, authenticated favicon, bootstrap, and cheap MCP operations. Assert counter-based pool behavior and generous latency bounds rather than wall-clock thresholds. A future diagnostic change may expose web/MCP sql.DBStats wait counters, but it is not a mitigation and should be added only if the controlled run needs that evidence. Do not claim that a fake-feed test covers local-model/OCR CPU saturation; that remains a production observation boundary.