Skip to content

Commit cb27f62

Browse files
donjor-agent[bot]donjor
authored andcommitted
fix(server): stop syncing the SQLite WAL on every commit (#1722)
## Problem SQLite runs at its default `synchronous = FULL`, so every commit waits for an fsync. The one connection commits on the main thread, so a slow disk freezes the whole server. On `ugrindtime`, disk writes waited about 224 ms each, and clients dropped for 2 minutes. Upstream tracks the same stall as `pingdotgg#15707`, with no fix pull request yet. ## Change - **Setting:** `PRAGMA synchronous = NORMAL` - **New file:** `SqliteSynchronous.fork.ts` - **Hook:** one line in `Sqlite.ts` - **WAL sync:** at checkpoints, not per commit ### Durability - **Power loss:** can drop the last commits - **Consistency:** the database stays intact - **App crash:** loses nothing ### Live measurements - **Commits:** about 7.7 per second - **Syncs per commit:** 1 before, 0 after - **Checkpoints:** about 1 every 6 s ### Not changed A larger `wal_autocheckpoint` saved only 11% of bytes in a 3-minute replay of live WAL pages. It was left out. ## Scope and approval Refs #1720. The maintainer chose this fix after the stall analysis. ## Verification - **New test:** WAL mode with `synchronous` 1 - **Mutation check:** fails with 2 without the hook - **Persistence tests:** 4 of 4 pass - **Typecheck:** `apps/server`, exit 0 - **Fork CI:** `vp run fork:ci`, exit 0 - **Not checked:** a live server on this build Claude Code, claude-opus-5-5. ## Fork trailers Fork-Domain: upstream-fixes Fork-Tier: bugfix Fork-Upstreamable: yes Co-authored-by: donjor <[email protected]>
1 parent ba8335a commit cb27f62

3 files changed

Lines changed: 44 additions & 0 deletions

File tree

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ import * as SqlClient from "effect/sql/SqlClient";
66
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
77

88
import { repairStaleForkMigrationRow, ensureForkSchema } from "./ForkSchema.ts"; // fork-hook: zmux-estate/fork-schema
9+
import { relaxWalSyncFork } from "./SqliteSynchronous.fork.ts"; // fork-hook: upstream-fixes/sqlite-synchronous
910
import { runMigrations } from "./Migrations.ts";
1011
import { initializeV2Database } from "./initializeV2Database.ts";
1112
import * as ServerConfig from "../config.ts";
@@ -31,6 +32,7 @@ const layerSetup = Layer.effectDiscard(
3132
yield* sql`PRAGMA checkpoint_fullfsync = ON;`;
3233
// PASSIVE checkpoints retain peak WAL size until the last connection closes.
3334
yield* sql.unsafe(`PRAGMA journal_size_limit = ${WAL_SIZE_LIMIT_BYTES};`);
35+
yield* relaxWalSyncFork; // fork-hook: upstream-fixes/sqlite-synchronous
3436
yield* repairStaleForkMigrationRow(); // fork-hook: zmux-estate/fork-schema
3537
yield* runMigrations();
3638
yield* ensureForkSchema(); // fork-hook: zmux-estate/fork-schema
Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
// @effect-diagnostics nodeBuiltinImport:off
2+
import * as NodeFS from "node:fs";
3+
import * as NodeOS from "node:os";
4+
import * as NodePath from "node:path";
5+
6+
import * as NodeServices from "@effect/platform-node/NodeServices";
7+
import { assert, it } from "@effect/vitest";
8+
import * as Effect from "effect/Effect";
9+
import * as Layer from "effect/Layer";
10+
import * as SqlClient from "effect/sql/SqlClient";
11+
12+
import * as SqlitePersistence from "./Sqlite.ts";
13+
14+
it.effect("commits in WAL mode without a sync per commit", () => {
15+
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-sync-"));
16+
const dbPath = NodePath.join(tempDir, "state.sqlite");
17+
18+
return Effect.gen(function* () {
19+
const sql = yield* SqlClient.SqlClient;
20+
const [mode] = yield* sql<{ readonly journal_mode: string }>`PRAGMA journal_mode`;
21+
const [sync] = yield* sql<{ readonly synchronous: number }>`PRAGMA synchronous`;
22+
assert.equal(mode?.journal_mode, "wal");
23+
// 1 is NORMAL; SQLite's default of 2 (FULL) syncs the WAL on every commit.
24+
assert.equal(sync?.synchronous, 1);
25+
}).pipe(
26+
Effect.provide(SqlitePersistence.layerFromPath(dbPath).pipe(Layer.provide(NodeServices.layer))),
27+
Effect.ensuring(Effect.sync(() => NodeFS.rmSync(tempDir, { recursive: true, force: true }))),
28+
);
29+
});
Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/sql/SqlClient";
3+
4+
/**
5+
* In WAL mode, `NORMAL` syncs at checkpoints instead of on every commit.
6+
* Commits run on the main thread, so a per-commit fsync on a slow disk stalls
7+
* the whole server. A power loss can drop the last commits; the database stays
8+
* consistent.
9+
*/
10+
export const relaxWalSyncFork = Effect.gen(function* () {
11+
const sql = yield* SqlClient.SqlClient;
12+
yield* sql`PRAGMA synchronous = NORMAL;`;
13+
});

0 commit comments

Comments
 (0)