[Bug]: All read-only queries share one SQLite reader with one permit, so one slow read blocks every other read; ensureTranscript also re-queries native V2 threads on every call #24660
Description
Summary
Reads marked readOnly go to a single reader worker guarded by Semaphore.make(1). One slow read (a large shell snapshot, a long-thread snapshot) holds it, and every other read, including trivial lookups, waits behind it. On our hosts most cancelled snapshot requests were waiting on a 0 ms lookup in ensureLegacyTranscript, and that lookup never caches its answer for threads that were born in V2.
Version / setup
0.1.3-nightly.20261023.3566, Linux,t3 servewith remote clients over T3 Connect.statev2.sqlite: 25.4 GB on host A, 16.2 GB on host B; about 4,800 and 4,300 unarchived threads before cleanup.- Same code on
mainatedb2b2f365.
Where in the code
- One reader worker and one permit: nodeSqliteClient.ts:1106-1108. Every
readOnlyeffect routes to it (:1109-1119). Enabled for the server DB at Sqlite.ts:63. ensureLegacyTranscriptruns on most thread reads and on command dispatch (ThreadManagementService.ts:554-590; callers at :598, :603, :608, :641, :705, :1136).ensureTranscriptcaches only when a legacy-import row exists (LegacyV1ThreadImporter.ts:686-700). For a thread created in V2 there is no row (imported === undefined), so it returns without adding the id toconfirmedTranscriptThreadIds. The next call takes the per-thread lock and queries again, through the same single reader.
Numbers
- One shell snapshot read: 8.9 MB, 1.6 s. While it runs, all other reads wait.
- Shell read median before vs. after archiving ~4,000 finished threads per host (n=5 each): host A 1,358 → 311 ms (13.1 → 2.5 MB), host B 1,410 → 293 ms (11.7 → 2.2 MB).
- Of ~120 thread-snapshot requests that clients cancelled (6 s budget), ~96 were queued in
ensureLegacyTranscript, whose query takes ~0 ms once it runs. The time was spent waiting for the reader permit.
Ask
- Cache the negative answer in
ensureTranscript: no legacy-import row means nothing to import. (Or checkconfirmedTranscriptThreadIds/ a "no legacy rows at all" flag before taking the lock.) - Allow more than one concurrent reader (a small pool of reader workers, WAL already allows it), or at least keep cheap point lookups from queuing behind long snapshot reads.
Related
- [Bug]: Thread snapshot HTTP handlers have no server-side timeout; cold-open herd times out at 6s and floods relay logs with context-canceled warnings #17765: snapshot herd times out at 6 s (filed when reads still used the writer lane).
- [Bug]: the other ProjectionStore reads take the write lock instead of the reader connection #23687 and fix(server): run windowed snapshots on the SQLite reader #21869: move more reads to the reader. With one reader permit, that moves the queue rather than removing it.
- [Bug]: Thread opens take seconds with ~5,000 unarchived threads; client work scales with every settled thread in the shell #22372: shell cost grows with unarchived threads.
Activity
Dropje97 commentedon Oct 25, 2026
Same symptom on a 22 GB database with ~10 concurrent agent runs. We traced which reads hold the single reader permit: the PR-sync shell sweep, JSON-based archive filters, run controls loading the full recovery projection, and the recovery/canonical projection statements that scan whole thread histories. Measurements and tested query fixes are in #24895. A reader pool (#24762) would spread that load but not remove it.
kkolodziej7 commentedon Oct 25, 2026
Adding measurements from a mid-size desktop install (T3 Code Beta 0.1.2, macOS arm64; 8 projects, 1,460 non-archived threads, ~185k visible items).
Besides the single-permit queueing described here, the shell query's own cost is multi-second at this size. In a 30-minute window the main shell SELECT alone took 4.14 s and 3.60 s with a reader-permit wait of ~0.01 ms (decode ~29 ms). An earlier window (1,434 threads, ~181k items) had one at 10.25 s with 0.012 ms wait. Handler median ~240 ms, p90 ~840 ms.
Load disclosure: an external MCP server polling GET /api/orchestration/shell about every 6 s (~10 reads/min) accounted for ~91% of reader busy time but only ~7% of wall time; natural permit wait stayed ≤0.05 ms in the window. A modest poller is enough to dominate the single reader, which supports both the reader pool (#24762) and a cheaper filtered or incremental shell read (cf. #24895).
Method: server.trace.ndjson spans joined shell handler → reader transaction → main SELECT; reader busy = transaction elapsed minus lock_wait_ms; window verified free of host sleep in the pmset log; times are elapsed, not CPU.
WhiteWarrior625 commentedon Oct 26, 2026
Additional Linux desktop confirmation on
0.1.3-nightly.20261026.3717.Immediately before a slow-request warning, a successful
orchestration.launchThreadRPC took 30,218 ms. Its command-receipt lookup took 18,118.265 ms, including 18,113.959 ms indb.lock_wait_mson the reader. A separate read took 21,222.041 ms, including 21,221.794 ms waiting for that connection.In a 40-second incident capture, 32 annotated reader statement or transaction spans waited at least 15 seconds. The maximum annotated writer wait was 0.032 ms. Concurrent work included sidebar shell projection, automatic settlement, and HTTP shell and thread snapshots. These observations support the shared-reader contention described here. They do not identify one query as the sole source of the accumulated queue.
The mounted installation and the published source for this exact release both have one reader worker guarded by
Semaphore.make(1). Background Activity was Balanced. The database was approximately 49.9 GB. The installation has local WAL-checkpoint and OpenCode SSE patches; neither replaces the single-reader implementation. This is therefore not a claim of a completely stock installation.The request completed successfully. The client's exact expanded toast request list was not captured, so the toast-to-RPC match is inferred from timing. No live database or application configuration changes were made for this diagnosis. This is production observation, not a controlled reproduction of a proposed reader-pool fix.
This adds another affected environment to #24660 and overlaps the workload described in #24895. A local rollout of #24762 would still need validation against this workload, including cancellation, transaction isolation, and memory use. No private database, transcripts, credentials, or raw logs are attached.
luworks commentedon Oct 28, 2026
+1 for the reader-queue symptom on official v0.1.3.
In a short trace sample, slow SQLite spans on
db.connection = readerspent almost their entire duration indb.lock_wait_ms; the longest observed wait was approximately 18 seconds. Lightweight HTTP requests remained responsive. This is a useful distinction: responsive basic endpoints do not establish that thread/database operations are responsive.This supports investigating the shared-reader queue separately from the pinned-WAL writer-admission issue in #23482. I have not identified the specific long reader transaction responsible for our sample, independently verified the negative-cache aspect of this issue, or established that the WAL gate caused these reader waits.
Suggestions:
Only rounded aggregate timing and public API/span names are shared here. No production logs, databases, conversation content, infrastructure identifiers, or attachments are included.
t3dotgg commentedon Oct 31, 2026
Note
🤖 Claude Opus 5.5 responding on behalf of Theo
Closing: this is covered by #27097 and #24707, which landed. Reopen if an important part is still missing.