Skip to content

Commit 8a6dd21

Browse files
only21milclaude
andcommitted
perf(server): keep up to 64 MiB of SQLite pages in the server's cache
The server's connection ran with SQLite's default 2 MiB page cache, so on a large statev2.sqlite every query re-read its b-tree pages from the OS with a pread per 4 KiB page. Bound the cache at 64 MiB instead. SQLite fills it only as pages are read, so a small database uses no more memory than before. Co-Authored-By: Claude Opus 5.5 (1M context) <[email protected]>
1 parent 8c777fb commit 8a6dd21

2 files changed

Lines changed: 16 additions & 1 deletion

File tree

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

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ import * as Effect from "effect/Effect";
1010
import * as Layer from "effect/Layer";
1111
import * as SqlClient from "effect/sql/SqlClient";
1212

13-
import { WAL_SIZE_LIMIT_BYTES } from "./Sqlite.ts";
13+
import { PAGE_CACHE_SIZE_KIB, WAL_SIZE_LIMIT_BYTES } from "./Sqlite.ts";
1414
import * as SqlitePersistence from "./Sqlite.ts";
1515

1616
const lockHolderSource = `
@@ -91,3 +91,11 @@ it.effect("applies busy_timeout in the shared persistence setup", () =>
9191
assert.equal(rows[0]?.timeout, 5000);
9292
}).pipe(Effect.provide(SqlitePersistence.layerMemory)),
9393
);
94+
95+
it.effect("applies the page cache bound in the shared persistence setup", () =>
96+
Effect.gen(function* () {
97+
const sql = yield* SqlClient.SqlClient;
98+
const rows = yield* sql<{ readonly cache_size: number }>`PRAGMA cache_size`;
99+
assert.equal(rows[0]?.cache_size, -PAGE_CACHE_SIZE_KIB);
100+
}).pipe(Effect.provide(SqlitePersistence.layerMemory)),
101+
);

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,11 @@ import * as ServerConfig from "../config.ts";
1212
// Size the -wal file is cut back to on the first commit after a WAL reset.
1313
export const WAL_SIZE_LIMIT_BYTES = 32 * 1024 * 1024;
1414

15+
// Upper bound on the connection's page cache. At SQLite's 2 MiB default, every
16+
// query on a large database re-reads its b-tree pages from the OS. The cache
17+
// only grows as pages are read, so a small database never reaches the bound.
18+
export const PAGE_CACHE_SIZE_KIB = 64 * 1024;
19+
1520
const layerSetup = Layer.effectDiscard(
1621
Effect.gen(function* () {
1722
const sql = yield* SqlClient.SqlClient;
@@ -22,6 +27,8 @@ const layerSetup = Layer.effectDiscard(
2227
// PASSIVE checkpoints never shrink the -wal file, so it otherwise keeps its
2328
// largest size until the last connection closes.
2429
yield* sql.unsafe(`PRAGMA journal_size_limit = ${WAL_SIZE_LIMIT_BYTES};`);
30+
// A negative cache_size is in KiB rather than pages.
31+
yield* sql.unsafe(`PRAGMA cache_size = -${PAGE_CACHE_SIZE_KIB};`);
2532
yield* runMigrations();
2633
}),
2734
);

0 commit comments

Comments
 (0)