[Bug]: Server: the thread list read blocks the event loop and every other database call for seconds on large databases#14701

Triage

Thanks for the timings and the clear breakdown of the query, @saphid! This is a real bug. I confirmed it on t3code/codex-turn-mapping at de95adc336e, the branch tip you named. main doesn't do this read, since V1 already stores the shell fields on projection_threads and selects them directly.

ProjectionStore.getShellSnapshot wraps selectShellThreadRows in sql.withTransaction. That SELECT runs 13 correlated subqueries per thread, including the two COUNT(*)s over orchestration_v2_projection_turn_items (item_count and runless_item_count). The same transaction then runs selectShellRunItemCounts, a GROUP BY count over that table, and decodes every thread payload before it commits.

packages/shared/src/nodeSqliteClient.ts has a single node:sqlite DatabaseSync connection behind a semaphore that allows one statement at a time. statement.all runs synchronously on the calling thread, and the transaction keeps the semaphore until it commits. So for the whole snapshot the event loop is stuck, and every other statement, writes included, has to wait. That's why your main-thread gap is almost the same as the snapshot time.

Two callers run this on a live server:

• Every shell subscription. subscribeOrchestrationV2Shell loads getShellSnapshot({ location: "active" }) without unsettledOnly, inside another sql.withTransaction (apps/server/src/ws.ts). Every reconnect repeats the full read of active threads.
• The pull request discovery pass. ThreadPullRequestService queues a full pass every minute, and that pass calls getShellSnapshot({ location: "active", unsettledOnly }). If a backfill is still pending, it reads every active thread instead. The snapshot sync sweep isn't the culprit here: PullRequestSyncReactor also runs every minute, but it calls getThreadsWithPullRequests(), which doesn't use this query.

unsettledOnly is meant to skip settled threads before their item rows are counted, and the filter sits in the outer WHERE, so only the remaining threads get counted. A 1.4 to 2.6s trace once a minute means most of those threads are still unsettled, or a backfill is forcing the full read. A client connect always takes the full path.

On the disconnects: the client sends a Ping every 5 seconds and closes the socket if no Pong has arrived by the next tick. A stall only drops a client when it runs past that deadline, so a single snapshot doesn't always cause a drop. But each drop leads to a reconnect, the reconnect runs the full snapshot again, and that can snowball into the clusters you saw.

Suggested fix

• Stop counting turn items in the shell list. Either store those counts on the thread row or leave them out of this query.
• Have the once-a-minute discovery pass skip getShellSnapshot. It only needs the branch, settlement, and pull request fields.
• Don't hold the only connection for the whole read. Even once the SQL is cheaper, a long statement.all on this client will still block the event loop. The correlated counts are what stretch it to seconds.

Related

#14517 (open issue) is the config snapshot stall on connect, and #14516 (open issue) is the client dropping and reconnecting when that first snapshot is slow. Same failure mode, different read.

The same stall happens on a much smaller database, and the thread list isn't the only read that causes it.

Setup: v0.0.46-nightly.20261003.2632, web mode on Linux in a Kubernetes pod. statev2.sqlite is 2.2 GB, with 698 threads and 460k orchestration_events. The disk is ext4 on SSD. The pod had no CPU, memory or I/O pressure: PSI some was below 1.5% for all three.

Symptom: I counted 13 event loop stalled warnings over one day, averaging 4.7 s. The longest was 15.3 s. During stalls the relay logs Incoming request ended abruptly: context canceled on /api/auth/websocket-ticket, and clients disconnect and re-sync.

Slowest sql.execute spans from the server trace:

The thread search started at 12:53:06 and ran for 15.2 s. The 15.3 s stall was logged at 12:53:22. Thread search runs json_extract over every message payload, with no index or FTS behind it.

scheduled_tasks has 0 rows, so its 8.4 s is time spent waiting for the single connection, not query cost. One slow read, such as the search, delays every other database call queued behind it. That matches the one-connection-behind-a-semaphore diagnosis above. On top of the shell snapshot, thread search should be moved off the main thread or backed by FTS.

Another data point, plus a local mitigation in case it helps as a reference.

Preview 0.0.46-preview.20261002.2598 (V2) in a Linux container. statev2.sqlite is 10.4 GB, with about 1,200 threads and 166,000 turn items. The browser showed "reconnecting" most of the time, often for minutes before a message could be sent, along with "Some requests are slow" toasts for 20–30 requests at once. A local HTTP probe every 5 s over 24 hours had 15.0% of requests over 1 s and 10.0% with no answer within 2 s. A probe every 0.5 s showed 1.5–5 s stalls about every five seconds.

We rebuilt the server at the same revision with three changes. The patch is in https://gist.github.com/Dropje97/4a5002ee4cbe52292fdf37b7ed45fcbc (based on b37fcea, with focused tests):

1. nodeSqliteClient yields (Effect.yieldNow) after each completed native statement, so socket I/O can run between the statements of a long read or transaction.
2. The persistence layer sets PRAGMA cache_size = -65536 (64 MiB), so repeated projections reuse pages.
3. Storage cleanup coalesces queued sweeps, and on provider-session.updated only sweeps when the session has stopped.

With that build the UI responded immediately and stopped reconnecting, even with eight parallel CPU-heavy import jobs running on the host. The same probe dropped to 4.4% over 1 s and 1.9% without an answer. We did not measure the three changes separately.

This is a mitigation: it cannot help with a single long statement, and the worker-thread reads in #14703 look like the proper fix. We can test #14703 on this workload once it reaches the preview channel.

Another read that produces this stall, with a different cause: T3 never collects SQLite query-planner statistics, so on a large orchestration_events the command-ID lookup picks the wrong index.

Setup: 0.0.46 nightly (20261005 builds), desktop on Windows 11 and on macOS, SQLite 3.53.4 via node:sqlite. Nothing in the server runs ANALYZE or PRAGMA optimize (Sqlite.ts on main at c4481b9 sets only busy_timeout, foreign_keys, journal_mode and journal_size_limit), and sqlite_stat1 was absent on both machines.

Mechanism: the agent-event read in OrchestrationEventStore.readApplicationRows with onlyAgentEvents: true filters on the sequence range, application_event_version = 2 AND aggregate_kind = 'thread', and command_id = ?. Unlike the other branch it has no INDEXED BY. Without statistics the planner chooses:

which walks every v2 event, instead of idx_orch_events_command_id.

Cost:

On Windows a CPU profile put ~99% of samples in StatementSync.all for these repeated lookups. The backend stopped answering, the app took minutes to open and showed "reconnecting", and updating the nightly or reinstalling didn't help. ANALYZE orchestration_events; took ~65 ms on the Mac, changed no rows, and switched the plan. On Windows the plan has stayed on the command-ID index since, now at ~1.37M events (0.015 ms per lookup).

Fresh database: I built a copy of the same schema with 600k synthetic events and never analyzed it. The same lookup chose application_sequence and took 39 ms. Either of these switched it to idx_orch_events_command_id (0.02 ms):

• PRAGMA optimize=0x10002 on open. This took 0.3 s once on that copy; adding the 0x10 bit caps it with a temporary analysis_limit.
• Running the query, then a plain PRAGMA optimize before closing.

Suggested fix: follow SQLite's recommendation for long-lived connections (https://www.sqlite.org/pragma.html#pragma_optimize). Run PRAGMA optimize=0x10002 when the server opens the database, then PRAGMA optimize periodically (hourly or daily) and at shutdown, so statistics exist before the event table gets large. It's also worth running after migrations that add indexes. Pinning the agent-event command lookup with INDEXED BY idx_orch_events_command_id would protect that one query, but not other plans that depend on statistics.

We looked into this on a 21 GB statev2.sqlite and found three separate costs in the same read. Each has its own focused PR with a test.

1. The last_error lookup is quadratic (#17842)

One more cause of this stall, separate from the turn-item counts: the last_error lookup in selectShellThreadRows. It walks every provider session on the thread's provider instance, for every thread.

The subquery joins provider_sessions to provider_session_bindings without a fixed order. With no sqlite_stat1 (as @3mdistal found), SQLite picks:

So one snapshot costs threads × sessions-per-instance probes. Most threads usually share one provider instance, so the cost grows quadratically.

On 0.0.46-nightly.20261007.2787 (main has the same SQL): 21.6 GB statev2.sqlite, 4,445 threads, 1,903 sessions with 1,900 on one instance. Read-only, every 10th thread, warm:

The 445-row sample fell from 402.3 ms to 18.6 ms warm and returned identical rows. The new full statement returned 4,440 rows in 159.7 ms warm. We did not run the old full statement in this benchmark or compare the full old/new result sets; the roughly 4 s old-full estimate is extrapolated from the sample, consistent with earlier 3.0-3.6 s server traces. We have not run a patched server against this database.

Separately, earlier server traces on that install showed the shell SQL span at 3.0-3.3 s per MCP thread list, 3.3-3.6 s per client shell load, and 1.3-1.8 s for every minute's pull request pass. getLimitRecoveryCandidates has the same lookup (the "Settlement query" in @SpyrosPsarras's table).

2. t3_thread_list computes every project (#17843)

A third caller of this read, besides the client shell load and the pull request pass: the MCP t3_thread_list tool, and it reads the most.

ThreadManagementService.listProjectThreads calls orchestrator.getShellSnapshot() with no options. selectShellThreadRows then runs over every non-deleted thread in every project, archived ones included, and only afterwards does JS keep the requested project's active threads. Every page of a paged listing repeats the full read.

On 0.0.46-nightly.20261007.2787 (main has the same code): 21 GB statev2.sqlite, 4,409 threads in 14 projects. Server traces showed 3.2 to 6.5 s per t3_thread_list call. An agent that listed 6 projects every 4 minutes held the main thread about 20 s per cycle, 8.6% of wall time. Read-only, Node 24.18.1 node:sqlite:

Archiving threads does not lower this cost, since the list reads archived threads too.

3. The server runs on a 2 MiB page cache (#17844)

@Dropje97's patch above sets PRAGMA cache_size = -65536 alongside two other changes, measured together. I measured the cache change by itself, in #17844.

The server's connection runs at SQLite's 2 MiB default, so on a large database it reads the same pages from the OS again on every query. On a 21.8 GB statev2.sqlite (4,492 threads, Linux, a separate read-only connection in one read transaction), main's per-thread selectShellThreadRows(threadId), the statement getThreadShell runs on every thread event, did this for 50 recent threads:

On a synthetic 509 MiB database built by the repo's migrations (4,000 threads), getShellSnapshot went from 13,962 reads to 2, and the cache survived the connection's own writes. Wall time didn't change there, since each synthetic thread has only a few pages.

Limits:

• I did not measure the full shell list's working set. Samples of the list on the 21.8 GB database, run with the bindings-first SQL rewrite from #17842, read 37.7 MiB for 10% of the threads and 70.1 MiB for 25% on a fresh connection. Those are read-volume estimates, not distinct-page counts, and the larger sample already exceeds 64 MiB. The list still needs the query fixes from the triage above.
• SQLite empties a connection's cache whenever another connection commits. In the synthetic run, 200 getThreadShell calls, each after another connection's commit, cost 8,102 page reads at both sizes. The reader worker in #14703 is a second connection, so a bigger cache there would help only within one read transaction.
• Each process gets its own cache. The CLI uses the same setup, so a warmed server and CLI can together hold roughly 128 MiB of configured cache budget plus overhead, though a short CLI command may touch far fewer pages. In a synthetic benchmark, reading a 339.1 MiB database at the new bound added about 65 MiB of RSS over the default; that is one observation, not a ceiling.

Model and harness: Claude Opus 5.5 (Claude Code in T3 Code) produced the changes and measurements; GPT-6.1 Sol reviewed them.

🤖 Generated with Claude Code

Another occurrence, on a smaller database, where host CPU saturation turns fast reads into long event-loop stalls. This is an edge case: the machine was overloaded by other work, so it may fall outside the scope of this issue and not be a priority. I'm adding it for context.

Environment

• Desktop app 0.0.46-nightly.20261012.3035, local backend, macOS 27.0 arm64, 10 cores
• statev2.sqlite 2.6 GB: 973 threads, 77k turn items, 374k orchestration events. No sqlite_stat1.

What happened The desktop app kept showing " is reconnecting". Over 32 minutes, ws.rpc.subscribeServerConfig was interrupted 9 times, once per reconnect.

Evidence

• server.eventLoop.stall fired 28 times in 35 minutes: delayMaxMs 2,056–8,504, utilization 0.93–1.0.
• In the same 30 s windows the server used only 1.2–2.9 s of CPU (cpuUserMs + cpuSystemMs), with 12k–28k involuntary context switches. Load average was 53–75 on 10 cores, with 0% idle. The load came from other apps, Chrome and test runs. T3 itself was about 8%.
• In the server, sqlite spans for selectShellThreadRows took 3.3–9.8 s. On a copy of the database, on the same machine, the same query takes 0.2 s warm. Running ANALYZE on the copy didn't change that (0.21–0.36 s).
• Each reconnect resubscribes and reruns the shell snapshot, so stalls bunch up after a drop.

Why it matters here When the host is busy, the main thread is likely being descheduled while it holds the only SQLite connection. That turns a 0.2 s read into seconds of blocked loop and missed pings. Moving SQLite off the main thread, or limiting the shell snapshot, would keep the server responsive on a busy machine, not only on a large database.

Diagnosed with t3 triage by Claude Code (Claude Opus 5.5).

Another occurrence on 0.0.46-nightly.20261013.3053, observed on 2026-10-13. The local desktop showed the environment reconnecting and several chats stopped updating together. A native backend sample caught synchronous SQLite on the main thread while traces recorded long shell-snapshot and thread-history reads.

The same investigation found a separate duplicate-owner problem on an M1 remote host. Its process evidence and verified service-only workaround are in the report on #6097. The stalled client Mac had one desktop-owned database server. We have not established that the M1 conflict caused this SQLite stall.

Environment and workload

• Packaged desktop on Apple Silicon, macOS 27.2 build 26B5101f, 48 GiB RAM, 18 logical CPUs.
• T3 Code 0.0.46-nightly.20261013.3053, release target 6524a8d43795dc543f7cfb572388cdea3662ad48, embedded Node 24.21.0 and Electron 44.4.5.
• Roughly 22.8 GB statev2.sqlite, 2,057 threads, and 360,500 turn items. The largest observed thread had 33,132 items.
• Several Codex turns were running. Three headless browser processes each used roughly 293-324% CPU. Around 16 GB of swap was used, with active paging and compression. The initial one-minute load average was about 83.

This was a live incident under substantial host pressure, not an isolated query benchmark. The host-load amplification described in the preceding comment is relevant here too.

Redacted native stack and trace excerpts

A three-second /usr/bin/sample capture began at 12:12:14 UTC. All 1,920 sampled main-thread stacks included node::sqlite::StatementSync::All. Selected frames are below, with intervening frames and addresses omitted. The pread frame is one branch within the sample.

The captured completed spans include:

These are wall-clock span durations, which can include scheduling and contention. The native sample directly establishes main-thread synchronous SQLite execution during the incident. It does not measure how much of each span was CPU time.

Source correlation and recovery

In the installed release, nodeSqliteClient.ts opens DatabaseSync, calls statement.all, and uses a one-permit semaphore. ProjectionStore.ts contains the shell query and transactional snapshot read. The captured history SELECT is also relevant to t3_thread_read and t3_thread_wait, beyond the sidebar snapshot path.

At about 12:29 UTC, the desktop was quit normally and reopened on the same build. Nine active turns were cancelled during shutdown. All nine were subsequently resumed and verified through fresh assistant responses and command activity. Saved histories and drafts were retained.

Loopback HTTP observations were:

The probed /health path returned fallback application HTML. These values show HTTP responsiveness only, not a dedicated health check or successful RPC execution. Fresh thread responses and commands provided the separate continuation check.

Restart was a workaround. It also reduced the active workload and swap use, so this is not a controlled before/after performance comparison. Post-restart traces still included SQLite spans around 1.5-1.7 seconds.

No source patch or live-database tuning was applied, and no minimal reproduction has been built. #14703 is still open and unmerged at the time of this report. We have not tested it. A useful follow-up would hold workload constant on a private database copy and compare both shell snapshots and MCP history reads, recording event-loop delay, returned history size, CPU scheduling, and paging on the same timeline.

Follow-up analysis with Kimi K3

An independent Kimi K3 analysis identified two useful refinements. Codex checked both against the saved redacted capture.

First, the native sample contains three disjoint pread leaf branches with 967, 12, and 615 samples. Together, these account for 1,594 of 1,920 main-thread samples, about 83%, in the three-second capture. This quantifies the read-system-call path within synchronous SQLite. It does not distinguish physical disk waits, page-cache reads, or descheduling, and it is not a percentage of CPU time. Some intervening Electron frames resolve to ares_dns_rr_get_ttl with very large offsets. Those nearest-symbol labels are not evidence of DNS activity.

Second, long SQLite spans extended beyond the shell and history reads. Additional selected records from the same capture were:

This broadens the observed impact across reads and a write. These remain elapsed span times, not isolated query costs. The capture does not establish whether each span was dominated by SQL execution, serialization, I/O, or scheduling. The long outer t3_thread_wait MCP spans are also not query benchmarks, since they can include intentional waiting.

The most useful next comparison remains an idle-host run and a controlled-load run on a private database copy, measuring both shell snapshots and history reads. The additional sample counts do not establish a causal link to the separate M1 ownership problem.

Prepared with Codex / GPT-6. The excerpts omit credentials, private addresses, home paths, thread identifiers, prompt content, and SQL parameter values. Raw traces, provider transcripts, and databases remain private.

The turn-item counting is still on main after #15033.

• The shell query still groups every selected thread's turn items by run and separately counts runless items.
• The fork-source count query also counts item rows.

For scale, I timed just those two count expressions, unchanged, on a read-only copy of a heavily used database: 2,240 threads and about 350,000 turn items.

• The first pass over 2,097 active threads took 1.79 s.
• Warm passes took a median of 102 ms (97–112 ms over 10 runs).

That's standalone SQL timing (Python sqlite3 3.45, Linux), not a full snapshot or event-loop measurement.

Would you want a focused PR that keeps per-thread, per-run counts in a small table and has the shell queries read those instead?

• How the counts stay correct: they are backfilled in a migration and maintained in the same transaction by insert, delete and key-move triggers on the turn-item table. Payload-only updates, which is all a streaming message does, leave the counts untouched. Runless, rolled-back-run, missing-run and fork-source results stay as they are today.
• Relation to #14703: that PR moves these reads off the main connection, and this would make the reads cheaper, so the two are complementary.
• Scope: I'd keep it to the count table, the shell reads and focused tests.
• Tests: backfill, insert, delete and move; rollback; projection rebuild and replay; shell results compared before and after, including rolled-back runs and fork inheritance.
• Measurements: count-query and full-snapshot timings before and after, plus write overhead and migration time.

Drafted with Claude Code and Codex (GPT-6.1 Sol).