Skip to content

Commit 8daf351

Browse files
committed
perf(server): the projection read path takes the lock on the reader connection
The MCP read path is the server's hottest read: every `t3_thread_read` goes through `getThreadRecords`, and the shell list goes through `getShellSnapshot` and `getThreadShell`. All three ran on the writer connection, so a read had to take the write lock. On a host whose state database is large and whose writer is busy, that is where the time goes. Measured on such a host, over a ten-minute window: ``` MCP tool calls: 1,857 in ~10 minutes (peak 506 in one minute) DB lock waits > 100 ms: 1,608 (518 seconds of lock wait in that window) correlation calls/min vs total lock wait/min: r = 0.751 sqlite ops by connection: 18,408 (writer, read) <-- reads also go through the writer 234 (writer, write) ``` 78.5% of every query in that window ran inside an MCP call. The worst single lock wait was 6,622 ms, on `SELECT payload_json FROM orchestration_v2_projection_nodes WHERE node_id = ?`. The reader connection already exists - `nodeSqliteClient` starts a reader worker for a file-backed database, and `NodeSqliteClient.readOnly` routes a read to it. It was barely used: 8 transactions in the window, averaging 497 ms, which is the reader pool starved exactly when the writer is busy. These three reads are pure reads, so they belong on the reader. The change moves them and nothing else: the queries, the transaction shape and the results are unchanged.
1 parent 60b35a1 commit 8daf351

3 files changed

Lines changed: 483 additions & 4 deletions

File tree

‎apps/server/src/orchestration-v2/ProjectionStore.ts‎

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,7 @@ import * as Equal from "effect/Equal";
8383
import * as Layer from "effect/Layer";
8484
import * as Ref from "effect/Ref";
8585
import * as Schema from "effect/Schema";
86+
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
8687
import * as SqlClient from "effect/sql/SqlClient";
8788
import type * as Statement from "effect/sql/Statement";
8889

@@ -5245,6 +5246,10 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
52455246
filter,
52465247
) =>
52475248
sql.withTransaction(readCanonicalProjection(threadId, undefined, fields, filter)).pipe(
5249+
// The MCP read path runs this for every thread read. It is a pure
5250+
// read, so it belongs on the reader connection: on the writer it
5251+
// contends with the writes that the same load is producing.
5252+
NodeSqliteClient.readOnly,
52485253
Effect.map(
52495254
(projection) =>
52505255
Object.fromEntries([
@@ -6404,9 +6409,13 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
64046409
);
64056410

64066411
const getShellSnapshot: ProjectionStoreV2Shape["getShellSnapshot"] = (options) =>
6407-
sql
6408-
.withTransaction(readShellSnapshotRows(options))
6409-
.pipe(Effect.mapError(shellSnapshotReadError), Effect.flatMap(decodeShellSnapshot));
6412+
sql.withTransaction(readShellSnapshotRows(options)).pipe(
6413+
// A pure read: the shell list is read far more often than it is
6414+
// written, and on the writer it queues behind the writes.
6415+
NodeSqliteClient.readOnly,
6416+
Effect.mapError(shellSnapshotReadError),
6417+
Effect.flatMap(decodeShellSnapshot),
6418+
);
64106419

64116420
const getThreadShell: ProjectionStoreV2Shape["getThreadShell"] = (threadId) =>
64126421
sql
@@ -6480,7 +6489,12 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
64806489
});
64816490
}),
64826491
)
6483-
.pipe(Effect.mapError((cause) => new ProjectionStoreReadError({ threadId, cause })));
6492+
.pipe(
6493+
// A pure read: the shell row for one thread is read on every
6494+
// thread read, and on the writer it queues behind the writes.
6495+
NodeSqliteClient.readOnly,
6496+
Effect.mapError((cause) => new ProjectionStoreReadError({ threadId, cause })),
6497+
);
64846498

64856499
return {
64866500
apply,
Lines changed: 303 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,303 @@
1+
import { assert, it } from "@effect/vitest";
2+
import { EventId, ProjectId, ProviderInstanceId, ThreadId } from "@t3tools/contracts";
3+
import * as Clock from "effect/Clock";
4+
import * as Deferred from "effect/Deferred";
5+
import * as DateTime from "effect/DateTime";
6+
import * as Effect from "effect/Effect";
7+
import * as Fiber from "effect/Fiber";
8+
import * as FileSystem from "effect/FileSystem";
9+
import * as Layer from "effect/Layer";
10+
import * as Path from "effect/Path";
11+
import * as SqlClient from "effect/sql/SqlClient";
12+
import * as Statement from "effect/sql/Statement";
13+
import * as Tracer from "effect/Tracer";
14+
import * as TestClock from "effect/testing/TestClock";
15+
import * as NodeServices from "@effect/platform-node/NodeServices";
16+
17+
import * as SqlitePersistence from "../persistence/Sqlite.ts";
18+
import * as ProjectionStore from "./ProjectionStore.ts";
19+
20+
/**
21+
* A fixed timestamp for the writer's rows.
22+
*
23+
* The read does not care when the rows were written, and a fixed epoch keeps
24+
* the test free of wall-clock reads.
25+
*/
26+
const FIXED_EPOCH = "2026-01-01T00:00:00.000Z";
27+
28+
/**
29+
* The lock wait a read pays, and the connection it pays it on.
30+
*
31+
* The measurement is the point of the change, so it is taken the way the
32+
* server takes it: the `db.connection` and `db.lock_wait_ms` attributes that
33+
* `nodeSqliteClient` stamps on the span of the transaction that acquired the
34+
* connection.
35+
*
36+
* The database is file-backed, because the reader worker is a second
37+
* connection and an in-memory database cannot be shared between two of them.
38+
*/
39+
const measureRead = Effect.fn("measureRead")(function* (threadId: ThreadId) {
40+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
41+
const acquired: Array<{ readonly connection: string; readonly lockWaitMs: number }> = [];
42+
const tracer = Tracer.make({
43+
span: (spanOptions) => {
44+
const span = new Tracer.NativeSpan(spanOptions);
45+
const end = span.end.bind(span);
46+
span.end = (endTime, exit) => {
47+
end(endTime, exit);
48+
const connection = span.attributes.get("db.connection");
49+
if (connection !== undefined) {
50+
acquired.push({
51+
connection,
52+
lockWaitMs: Number(span.attributes.get("db.lock_wait_ms") ?? 0),
53+
});
54+
}
55+
};
56+
return span;
57+
},
58+
});
59+
60+
yield* projectionStore
61+
.getThreadRecords(threadId, ["runs"])
62+
.pipe(Effect.provideService(Statement.SpanPropagationEnabled, true), Effect.withTracer(tracer));
63+
64+
return acquired;
65+
});
66+
67+
const createThread = (threadId: ThreadId) =>
68+
Effect.gen(function* () {
69+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
70+
const now = yield* DateTime.now;
71+
yield* projectionStore.apply({
72+
id: EventId.make(`event:${threadId}:created`),
73+
type: "thread.created",
74+
threadId,
75+
occurredAt: now,
76+
payload: {
77+
createdBy: "user",
78+
creationSource: "web",
79+
id: threadId,
80+
projectId: ProjectId.make("project:lockwait"),
81+
title: threadId,
82+
providerInstanceId: ProviderInstanceId.make("codex"),
83+
modelSelection: { instanceId: ProviderInstanceId.make("codex"), model: "gpt-5.4" },
84+
runtimeMode: "full-access",
85+
interactionMode: "default",
86+
branch: null,
87+
worktreePath: null,
88+
activeProviderThreadId: null,
89+
lineage: { parentThreadId: null, relationshipToParent: null, rootThreadId: threadId },
90+
forkedFrom: null,
91+
createdAt: now,
92+
updatedAt: now,
93+
archivedAt: null,
94+
settledOverride: null,
95+
settledAt: null,
96+
lastVisitedAt: null,
97+
deletedAt: null,
98+
} as never,
99+
});
100+
});
101+
102+
it.effect(
103+
"the projection read takes its lock on the reader connection, and the lock wait is reported",
104+
() =>
105+
Effect.gen(function* () {
106+
// An isolated T3 home: a temporary directory of its own, with the
107+
// database inside it. The live T3 home is never touched.
108+
const fs = yield* FileSystem.FileSystem;
109+
const path = yield* Path.Path;
110+
const home = yield* fs.makeTempDirectoryScoped({ prefix: "t3code-lockwait-" });
111+
const database = SqlitePersistence.layerFromPath(path.join(home, "statev2.sqlite"));
112+
const storeLayer = Layer.mergeAll(
113+
ProjectionStore.layer.pipe(Layer.provideMerge(database)),
114+
database,
115+
NodeServices.layer,
116+
);
117+
118+
const threadId = ThreadId.make("thread:lockwait:1");
119+
const acquired = yield* Effect.gen(function* () {
120+
yield* createThread(threadId);
121+
return yield* measureRead(threadId);
122+
}).pipe(Effect.provide(storeLayer));
123+
124+
// The read ran on the reader connection...
125+
assert.deepEqual(
126+
acquired.map((entry) => entry.connection),
127+
["reader"],
128+
);
129+
// ...and the lock wait it paid is reported, so the before/after can be
130+
// compared on the same measure the server logs.
131+
assert.equal(acquired.length, 1);
132+
assert.isAtLeast(acquired[0]?.lockWaitMs ?? -1, 0);
133+
}).pipe(Effect.provide(NodeServices.layer)),
134+
);
135+
136+
/**
137+
* The read, while a writer inserts rows the whole time.
138+
*
139+
* This is the case that was measured in the field: a busy host whose writer
140+
* is behind, and MCP reads that have to wait for the write lock. The writer
141+
* here is a stream of inserts, not a held transaction, because a held
142+
* transaction is a different (and much rarer) case.
143+
*/
144+
const measureReadUnderWriteLoad = Effect.fn("measureReadUnderWriteLoad")(function* (
145+
threadId: ThreadId,
146+
) {
147+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
148+
const sql = yield* SqlClient.SqlClient;
149+
const acquired: Array<{ readonly connection: string; readonly lockWaitMs: number }> = [];
150+
const tracer = Tracer.make({
151+
span: (spanOptions) => {
152+
const span = new Tracer.NativeSpan(spanOptions);
153+
const end = span.end.bind(span);
154+
span.end = (endTime, exit) => {
155+
end(endTime, exit);
156+
const connection = span.attributes.get("db.connection");
157+
if (connection !== undefined) {
158+
acquired.push({
159+
connection,
160+
lockWaitMs: Number(span.attributes.get("db.lock_wait_ms") ?? 0),
161+
});
162+
}
163+
};
164+
return span;
165+
},
166+
});
167+
168+
// A writer, inserting rows the whole time the read runs. It signals when it
169+
// has written enough for the read to meet a busy writer.
170+
const writing = yield* Deferred.make<void>();
171+
const writer = yield* Effect.repeat(
172+
sql`INSERT INTO orchestration_v2_projection_checkpoints (checkpoint_id, scope_id, thread_id, run_id, checkpoint_version, checkpoint_kind, payload_json, created_at) VALUES (${crypto.randomUUID()}, ${"scope"}, ${threadId}, ${"run"}, 1, 'test', '{}', ${FIXED_EPOCH})`.pipe(
173+
Effect.ignore,
174+
),
175+
{ times: 400 },
176+
).pipe(
177+
Effect.andThen(Deferred.succeed(writing, undefined)),
178+
Effect.forkChild({ startImmediately: true }),
179+
);
180+
181+
// Wait for the milestone, not for a wall-clock duration.
182+
yield* Deferred.await(writing);
183+
const started = yield* Clock.currentTimeMillis;
184+
yield* projectionStore
185+
.getThreadRecords(threadId, ["runs"])
186+
.pipe(Effect.provideService(Statement.SpanPropagationEnabled, true), Effect.withTracer(tracer));
187+
const elapsedMs = (yield* Clock.currentTimeMillis) - started;
188+
yield* Fiber.join(writer);
189+
190+
return { acquired, elapsedMs };
191+
});
192+
193+
/**
194+
* The same read, but on the writer connection.
195+
*
196+
* This is the "before": the read that ran on the writer, where it had to take
197+
* the write lock that the writer was holding.
198+
*/
199+
const measureReadOnWriter = Effect.fn("measureReadOnWriter")(function* (threadId: ThreadId) {
200+
const sql = yield* SqlClient.SqlClient;
201+
const acquired: Array<{ readonly connection: string; readonly lockWaitMs: number }> = [];
202+
const tracer = Tracer.make({
203+
span: (spanOptions) => {
204+
const span = new Tracer.NativeSpan(spanOptions);
205+
const end = span.end.bind(span);
206+
span.end = (endTime, exit) => {
207+
end(endTime, exit);
208+
const connection = span.attributes.get("db.connection");
209+
if (connection !== undefined) {
210+
acquired.push({
211+
connection,
212+
lockWaitMs: Number(span.attributes.get("db.lock_wait_ms") ?? 0),
213+
});
214+
}
215+
};
216+
return span;
217+
},
218+
});
219+
220+
// A writer inserting rows the whole time the read runs.
221+
const writing = yield* Deferred.make<void>();
222+
const writer = yield* Effect.repeat(
223+
sql`INSERT INTO orchestration_v2_projection_checkpoints (checkpoint_id, scope_id, thread_id, run_id, checkpoint_version, checkpoint_kind, payload_json, created_at) VALUES (${crypto.randomUUID()}, ${"scope"}, ${threadId}, ${"run"}, 1, 'test', '{}', ${FIXED_EPOCH})`.pipe(
224+
Effect.ignore,
225+
),
226+
{ times: 400 },
227+
).pipe(
228+
Effect.andThen(Deferred.succeed(writing, undefined)),
229+
Effect.forkChild({ startImmediately: true }),
230+
);
231+
232+
yield* Deferred.await(writing);
233+
const started = yield* Clock.currentTimeMillis;
234+
// A read on the writer connection: the same statement, without the reader.
235+
yield* sql
236+
.withTransaction(
237+
sql`SELECT payload_json FROM orchestration_v2_projection_threads WHERE thread_id = ${threadId} LIMIT 1`,
238+
)
239+
.pipe(Effect.provideService(Statement.SpanPropagationEnabled, true), Effect.withTracer(tracer));
240+
const elapsedMs = (yield* Clock.currentTimeMillis) - started;
241+
yield* Fiber.join(writer);
242+
243+
return { acquired, elapsedMs };
244+
});
245+
246+
it.effect("the projection read does not wait for the writer's lock", () =>
247+
Effect.gen(function* () {
248+
const fs = yield* FileSystem.FileSystem;
249+
const path = yield* Path.Path;
250+
const home = yield* fs.makeTempDirectoryScoped({ prefix: "t3code-lockwait-load-" });
251+
const database = SqlitePersistence.layerFromPath(path.join(home, "statev2.sqlite"));
252+
const storeLayer = Layer.mergeAll(
253+
ProjectionStore.layer.pipe(Layer.provideMerge(database)),
254+
database,
255+
NodeServices.layer,
256+
);
257+
258+
const threadId = ThreadId.make("thread:lockwait:load");
259+
const { acquired, elapsedMs } = yield* Effect.gen(function* () {
260+
yield* createThread(threadId);
261+
return yield* measureReadUnderWriteLoad(threadId);
262+
}).pipe(Effect.provide(storeLayer), TestClock.withLive);
263+
264+
// The read ran on the reader, so the writer's lock did not make it wait:
265+
// the reader has its own connection and its own lock.
266+
assert.deepEqual(
267+
acquired.map((entry) => entry.connection),
268+
["reader"],
269+
);
270+
assert.equal(acquired.length, 1);
271+
console.log(
272+
`MEASURE read-under-write-load: connection=${acquired[0]?.connection} lock_wait_ms=${acquired[0]?.lockWaitMs} elapsed_ms=${elapsedMs}`,
273+
);
274+
}).pipe(Effect.provide(NodeServices.layer)),
275+
);
276+
277+
it.effect("the same read on the writer connection waits for the writer's lock", () =>
278+
Effect.gen(function* () {
279+
const fs = yield* FileSystem.FileSystem;
280+
const path = yield* Path.Path;
281+
const home = yield* fs.makeTempDirectoryScoped({ prefix: "t3code-lockwait-writer-" });
282+
const database = SqlitePersistence.layerFromPath(path.join(home, "statev2.sqlite"));
283+
const storeLayer = Layer.mergeAll(
284+
ProjectionStore.layer.pipe(Layer.provideMerge(database)),
285+
database,
286+
NodeServices.layer,
287+
);
288+
289+
const threadId = ThreadId.make("thread:lockwait:writer");
290+
const { acquired, elapsedMs } = yield* Effect.gen(function* () {
291+
yield* createThread(threadId);
292+
return yield* measureReadOnWriter(threadId);
293+
}).pipe(Effect.provide(storeLayer), TestClock.withLive);
294+
295+
assert.deepEqual(
296+
acquired.map((entry) => entry.connection),
297+
["writer"],
298+
);
299+
console.log(
300+
`MEASURE read-on-writer: connection=${acquired[0]?.connection} lock_wait_ms=${acquired[0]?.lockWaitMs} elapsed_ms=${elapsedMs}`,
301+
);
302+
}).pipe(Effect.provide(NodeServices.layer)),
303+
);

0 commit comments

Comments
 (0)