Skip to content

Commit ce26864

Browse files
fix(server): a slow database write no longer freezes the server (#35728)
Co-authored-by: Julius Marminge <[email protected]>
1 parent 36254ab commit ce26864

7 files changed

Lines changed: 418 additions & 841 deletions

File tree

‎apps/server/src/orchestration-v2/FoundationPersistence.test.ts‎

Lines changed: 31 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
import * as NodeSqlite from "node:sqlite";
1+
import type * as NodeWorkerThreads from "node:worker_threads";
22
import * as NodeServices from "@effect/platform-node/NodeServices";
33
import { assert, it } from "@effect/vitest";
44
import {
@@ -73,6 +73,29 @@ import * as ProjectStore from "./ProjectStore.ts";
7373
import * as ProviderRuntimeRecovery from "./ProviderRuntimeRecoveryService.ts";
7474
import * as TurnItemPositionStore from "./TurnItemPositionStore.ts";
7575

76+
const workerStatements = vi.hoisted(
77+
(): Array<{ readonly readOnly: boolean; readonly sql: string }> => [],
78+
);
79+
80+
vi.mock("node:worker_threads", async (importOriginal) => {
81+
const actual = await importOriginal<typeof NodeWorkerThreads>();
82+
class RecordedWorker extends actual.Worker {
83+
readonly #readOnly: boolean;
84+
constructor(...args: ConstructorParameters<typeof actual.Worker>) {
85+
super(...args);
86+
this.#readOnly = args[1]?.workerData?.readOnly === true;
87+
}
88+
override postMessage(...args: Parameters<NodeWorkerThreads.Worker["postMessage"]>) {
89+
const [message] = args;
90+
if (typeof message === "object" && message !== null && typeof message.sql === "string") {
91+
workerStatements.push({ readOnly: this.#readOnly, sql: message.sql });
92+
}
93+
return super.postMessage(...args);
94+
}
95+
}
96+
return { ...actual, Worker: RecordedWorker };
97+
});
98+
7699
const isLiveStreamBufferError = Schema.is(LiveStreamBufferError);
77100
const encodeJson = Schema.encodeEffect(Schema.fromJsonString(Schema.Unknown));
78101

@@ -193,45 +216,22 @@ it.effect("discovers on the reader worker and deletes on the writer", () =>
193216
yield* Effect.gen(function* () {
194217
const sql = yield* SqlClient.SqlClient;
195218
yield* seedCompaction();
196-
// Establish the reader before recording main-thread statements.
219+
// Establish the reader before recording statements.
197220
yield* sql`SELECT 1`.pipe(NodeSqliteClient.readOnly);
198-
const reads: string[] = [];
199-
const writes: string[] = [];
200-
const all = NodeSqlite.StatementSync.prototype.all;
201-
const run = NodeSqlite.StatementSync.prototype.run;
202-
yield* Effect.acquireRelease(
203-
Effect.sync(() => {
204-
const readSpy = vi
205-
.spyOn(NodeSqlite.StatementSync.prototype, "all")
206-
.mockImplementation(function (this: NodeSqlite.StatementSync, ...params) {
207-
reads.push(this.sourceSQL);
208-
return all.apply(this, params);
209-
});
210-
const writeSpy = vi
211-
.spyOn(NodeSqlite.StatementSync.prototype, "run")
212-
.mockImplementation(function (this: NodeSqlite.StatementSync, ...params) {
213-
writes.push(this.sourceSQL);
214-
return run.apply(this, params);
215-
});
216-
return { readSpy, writeSpy };
217-
}),
218-
({ readSpy, writeSpy }) =>
219-
Effect.sync(() => {
220-
readSpy.mockRestore();
221-
writeSpy.mockRestore();
222-
}),
223-
);
221+
workerStatements.length = 0;
224222
const maintenance = yield* ProjectionMaintenance.ProjectionMaintenanceV2;
225223
const result = yield* maintenance.pruneEventStore();
226224
assert.equal(result.deletedEventCount, 150);
227225
assert.equal(result.deletedReceiptCount, 100);
226+
const writer = workerStatements.filter((statement) => !statement.readOnly);
228227
// Only the in-transaction check for rows appended during the pass reads on the writer.
229228
assert.deepEqual(
230-
reads.filter((query) => /\bSELECT\b/.test(query) && !query.includes("AS prompt_sequence")),
229+
writer.filter(({ sql }) => /\bSELECT\b/.test(sql) && !sql.includes("AS prompt_sequence")),
231230
[],
232231
);
233-
assert(writes.some((query) => query.includes("DELETE FROM orchestration_events")));
234-
assert(writes.some((query) => query.includes("DELETE FROM orchestration_command_receipts")));
232+
assert(workerStatements.some(({ readOnly, sql }) => readOnly && /\bSELECT\b/.test(sql)));
233+
assert(writer.some(({ sql }) => sql.includes("DELETE FROM orchestration_events")));
234+
assert(writer.some(({ sql }) => sql.includes("DELETE FROM orchestration_command_receipts")));
235235
}).pipe(
236236
Effect.provide(
237237
ProjectionMaintenance.layer.pipe(

‎apps/server/src/persistence/Sqlite.test.ts‎

Lines changed: 1 addition & 74 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,6 @@
22
import * as NodeFS from "node:fs";
33
import * as NodeOS from "node:os";
44
import * as NodePath from "node:path";
5-
import * as NodeSqlite from "node:sqlite";
65
import type * as NodeWorkerThreads from "node:worker_threads";
76

87
import * as NodeCrypto from "@effect/platform-node/NodeCrypto";
@@ -23,92 +22,19 @@ import * as ServerOwnership from "../serverOwnership.ts";
2322
import { migrationManifest, runMigrations } from "./Migrations.ts";
2423
import * as SqlitePersistence from "./Sqlite.ts";
2524

26-
const workers = vi.hoisted((): Array<NodeWorkerThreads.Worker> => []);
2725
const workerData = vi.hoisted((): Array<unknown> => []);
2826

2927
vi.mock("node:worker_threads", async (importOriginal) => {
3028
const actual = await importOriginal<typeof NodeWorkerThreads>();
3129
class RecordedWorker extends actual.Worker {
3230
constructor(...args: ConstructorParameters<typeof actual.Worker>) {
3331
super(...args);
34-
workers.push(this);
3532
workerData.push(args[1]?.workerData);
3633
}
3734
}
3835
return { ...actual, Worker: RecordedWorker };
3936
});
4037

41-
it.effect("keeps writes moving past a 48 MiB WAL while a reader retains its snapshot", () => {
42-
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-burst-"));
43-
const dbPath = NodePath.join(tempDir, "state.sqlite");
44-
const persistence = SqlitePersistence.layerFromPath(dbPath).pipe(
45-
Layer.provide(NodeServices.layer),
46-
);
47-
48-
return Effect.gen(function* () {
49-
const sql = yield* SqlClient.SqlClient;
50-
const checkpointWorker = workers.at(-1)!;
51-
yield* sql`CREATE TABLE burst_probe(id INTEGER PRIMARY KEY, payload BLOB)`;
52-
yield* sql`INSERT INTO burst_probe VALUES (1, NULL)`;
53-
yield* sql`PRAGMA wal_checkpoint(TRUNCATE)`;
54-
const reader = yield* Effect.acquireRelease(
55-
Effect.sync(() => new NodeSqlite.DatabaseSync(dbPath, { readOnly: true })),
56-
(database) => Effect.sync(() => database.close()),
57-
);
58-
reader.exec("BEGIN");
59-
assert.equal(reader.prepare("SELECT COUNT(*) AS count FROM burst_probe").get()?.count, 1);
60-
61-
yield* sql.withTransaction(
62-
sql`INSERT INTO burst_probe VALUES (2, zeroblob(${48 * 1024 * 1024}))`,
63-
);
64-
const state = yield* Effect.callback<{
65-
readonly log: number;
66-
readonly checkpointed: number;
67-
readonly blocked: boolean;
68-
}>((resume) => {
69-
const onMessage = (message: {
70-
readonly type?: string;
71-
readonly log?: number;
72-
readonly checkpointed?: number;
73-
readonly blocked?: boolean;
74-
}) => {
75-
if (
76-
message.type === "checkpoint" &&
77-
typeof message.log === "number" &&
78-
message.log > 10_000 &&
79-
typeof message.checkpointed === "number" &&
80-
typeof message.blocked === "boolean"
81-
) {
82-
checkpointWorker.off("message", onMessage);
83-
resume(
84-
Effect.succeed({
85-
log: message.log,
86-
checkpointed: message.checkpointed,
87-
blocked: message.blocked,
88-
}),
89-
);
90-
}
91-
};
92-
checkpointWorker.on("message", onMessage);
93-
checkpointWorker.ref();
94-
// oxlint-disable-next-line unicorn/require-post-message-target-origin -- Node workers do not accept a target origin.
95-
checkpointWorker.postMessage("barrier");
96-
return Effect.sync(() => checkpointWorker.off("message", onMessage));
97-
});
98-
assert.isBelow(state.checkpointed, state.log);
99-
assert.isFalse(state.blocked);
100-
101-
yield* sql.withTransaction(sql`INSERT INTO burst_probe VALUES (3, NULL)`);
102-
assert.equal(reader.prepare("SELECT COUNT(*) AS count FROM burst_probe").get()?.count, 1);
103-
reader.exec("COMMIT");
104-
assert.equal(reader.prepare("SELECT COUNT(*) AS count FROM burst_probe").get()?.count, 3);
105-
}).pipe(
106-
Effect.scoped,
107-
Effect.provide(persistence),
108-
Effect.ensuring(Effect.sync(() => NodeFS.rmSync(tempDir, { recursive: true, force: true }))),
109-
);
110-
});
111-
11238
// Linux names an open, already-deleted file "<path> (deleted)" in /proc/self/fd.
11339
// SQLite names its temp files "etilqs_*" and, without TMPDIR, puts them in
11440
// /var/tmp while os.tmpdir() is /tmp, so match the name, not the directory.
@@ -223,6 +149,7 @@ it.effect("upgrades an existing database on a read-write worker connection", ()
223149
assert.deepInclude(workerData.slice(startedBefore), {
224150
filename: dbPath,
225151
readOnly: false,
152+
requireWal: false,
226153
allowExtension: false,
227154
prepareCacheSize: 200,
228155
});

‎apps/server/src/persistence/Sqlite.ts‎

Lines changed: 5 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -64,26 +64,6 @@ const runMigrationsAsOwner = Effect.fn("SqlitePersistence.runMigrationsAsOwner")
6464
yield* runMigrations();
6565
}, Effect.scoped);
6666

67-
// Index builds over large tables take minutes; on the main connection they block the event loop,
68-
// so requests hang instead of getting the listener's 503. A fresh database stays on the main
69-
// connection, which must create the first table for its auto_vacuum setting to apply.
70-
const runMigrationsOffMainThread = Effect.fn("SqlitePersistence.runMigrationsOffMainThread")(
71-
function* (dbPath: string) {
72-
const sql = yield* SqlClient.SqlClient;
73-
const tables = yield* sql<{
74-
readonly count: number;
75-
}>`SELECT COUNT(*) AS count FROM sqlite_schema WHERE type = 'table'`;
76-
if (!tables[0]?.count || !(yield* hasPendingMigrations())) return yield* runMigrations();
77-
return yield* Effect.gen(function* () {
78-
const worker = yield* SqlClient.SqlClient;
79-
yield* worker`PRAGMA foreign_keys = ON;`;
80-
yield* worker`PRAGMA synchronous = NORMAL;`;
81-
yield* worker`PRAGMA temp_store = MEMORY;`;
82-
return yield* runMigrations();
83-
}).pipe(Effect.provide(NodeSqliteClient.layerWorker(dbPath)));
84-
},
85-
);
86-
8767
const makeLayerSetup = <E, R>(migrate: Effect.Effect<unknown, E, R>) =>
8868
Layer.effectDiscard(
8969
Effect.gen(function* () {
@@ -97,12 +77,12 @@ const makeLayerSetup = <E, R>(migrate: Effect.Effect<unknown, E, R>) =>
9777
}>`SELECT COUNT(*) AS count FROM sqlite_schema WHERE type = 'table'`;
9878
if (tables[0]?.count === 0) yield* sql`PRAGMA auto_vacuum = INCREMENTAL;`;
9979
yield* sql`PRAGMA journal_mode = WAL;`;
100-
// FULL fsyncs every event commit on the main thread; NORMAL syncs only at WAL checkpoints.
80+
// FULL fsyncs every event commit; NORMAL syncs only at WAL checkpoints.
10181
// App crashes lose nothing; OS crashes or power loss can roll back post-checkpoint commits without corruption.
10282
yield* sql`PRAGMA synchronous = NORMAL;`;
10383
// macOS fsync misses drive caches; F_FULLFSYNC flushes them at checkpoints and is ignored elsewhere.
10484
yield* sql`PRAGMA checkpoint_fullfsync = ON;`;
105-
// Keep statement journals and sort spills off the main thread's disk path.
85+
// Keep statement journals and sort spills off the disk.
10686
// Large statements and open savepoints can increase peak memory use.
10787
yield* sql`PRAGMA temp_store = MEMORY;`;
10888
// PASSIVE checkpoints retain peak WAL size until the last connection closes.
@@ -131,8 +111,8 @@ const makeLayerFromPath = <E, R>(makeSetup: (dbPath: string) => Layer.Layer<neve
131111
NodeSqliteClient.layer({
132112
filename: dbPath,
133113
readerWorker: true,
134-
// Run checkpoint fsyncs off the event loop so slow disk flushes cannot stall clients.
135-
checkpointWorker: true,
114+
// A slow disk then delays writes instead of freezing every client.
115+
writerWorker: true,
136116
spanAttributes: {
137117
"db.name": path.basename(dbPath),
138118
"service.name": "t3code-server",
@@ -152,9 +132,7 @@ const makeLayerFromPath = <E, R>(makeSetup: (dbPath: string) => Layer.Layer<neve
152132
);
153133
}, Layer.unwrap);
154134

155-
export const layerFromPath = makeLayerFromPath((dbPath) =>
156-
makeLayerSetup(runMigrationsOffMainThread(dbPath)),
157-
);
135+
export const layerFromPath = makeLayerFromPath(() => layerSetup);
158136

159137
export const layerMemory = Layer.provideMerge(
160138
layerSetup,

‎apps/server/src/project/ProjectService.test.ts‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -552,6 +552,12 @@ it.layer(layerTest)("ProjectService", (it) => {
552552
title: "Delete retry",
553553
workspaceRoot: "/work/delete-retry",
554554
});
555+
// Enrichment landing between the two deletes would change the project the retry reads.
556+
yield* waitForProject(
557+
service,
558+
projectId,
559+
(project) => project.faviconPath !== null && project.repositoryIdentity !== null,
560+
);
555561
const input = { commandId: CommandId.make("command:delete-retry:delete"), projectId };
556562
const deleted = yield* service.delete(input);
557563
assert.deepEqual(yield* service.delete(input), deleted);

‎docs/operations/observability.md‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -57,8 +57,9 @@ The `TraceRecord`, `EffectTraceRecord`, and `OtlpTraceRecord` schemas live in
5757
`packages/shared/src/observability.ts`.
5858

5959
SQL statement spans (named `sqlite`) and `sql.transaction` spans carry `db.connection` and
60-
`db.lock_wait_ms`. `db.connection` is `writer` for the main connection, which runs on the event loop,
61-
or `reader` for the read-only connection in a worker thread. `db.lock_wait_ms` is the part of the
60+
`db.lock_wait_ms`. `db.connection` is `writer` for the read-write connection or `reader` for a
61+
read-only one. For a database file both run in worker threads; an in-memory database has only a
62+
writer, on the event loop. `db.lock_wait_ms` is the part of the
6263
span spent waiting for that connection, behind other SQL. That wait does not block the event loop,
6364
so subtract it before you blame a slow span on the query. Statements inside a transaction use its
6465
connection and do not carry these attributes.

0 commit comments

Comments
 (0)