Skip to content

Commit facaa08

Browse files
perf(shared): SQLite reads stop queueing behind each other, up to 5x faster under load (pingdotgg#27097)
Co-authored-by: Claude Opus 5.5 <[email protected]>
1 parent 52f4ec6 commit facaa08

2 files changed

Lines changed: 183 additions & 89 deletions

File tree

‎packages/shared/src/nodeSqliteClient.test.ts‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -238,6 +238,27 @@ it.effect("lets the writer commit while a read-only transaction is open", () =>
238238
),
239239
);
240240

241+
it.effect("serves another read while a read-only transaction is open", () =>
242+
withReaderClient((sql) =>
243+
Effect.gen(function* () {
244+
const opened = yield* Deferred.make<void>();
245+
const other = yield* Effect.forkChild(
246+
Deferred.await(opened).pipe(Effect.andThen(countEntries(sql).pipe(SqliteClient.readOnly))),
247+
);
248+
yield* sql
249+
.withTransaction(
250+
Effect.gen(function* () {
251+
yield* countEntries(sql);
252+
yield* Deferred.succeed(opened, undefined);
253+
// One shared reader would make this join wait on the transaction forever.
254+
assert.equal(yield* Fiber.join(other), 1);
255+
}),
256+
)
257+
.pipe(SqliteClient.readOnly);
258+
}),
259+
),
260+
);
261+
241262
// regression: #19226 read-only transactions run on the separate reader connection
242263
it.effect("rejects a write in a read-only transaction", () =>
243264
withReaderClient((sql) =>
@@ -333,6 +354,25 @@ it.effect("uses the writer when the reader cannot open the database", () =>
333354

334355
assert.equal(yield* sql.withTransaction(countEntries(sql)).pipe(SqliteClient.readOnly), 1);
335356
assert.equal(yield* sql.withTransaction(countEntries(sql)).pipe(SqliteClient.readOnly), 1);
357+
358+
// A read beside an open read transaction must not start a worker on the next slot.
359+
const held = yield* Deferred.make<void>();
360+
const release = yield* Deferred.make<void>();
361+
const holder = yield* sql
362+
.withTransaction(
363+
countEntries(sql).pipe(
364+
Effect.andThen(Deferred.succeed(held, undefined)),
365+
Effect.andThen(Deferred.await(release)),
366+
),
367+
)
368+
.pipe(SqliteClient.readOnly, Effect.forkChild);
369+
yield* Deferred.await(held);
370+
const other = yield* countEntries(sql).pipe(SqliteClient.readOnly, (effect) =>
371+
Effect.forkChild(effect, { startImmediately: true }),
372+
);
373+
yield* Deferred.succeed(release, undefined);
374+
yield* Fiber.join(holder);
375+
assert.equal(yield* Fiber.join(other), 1);
336376
assert.equal(startedWorkers.length, startedBefore + 1);
337377
}),
338378
),

‎packages/shared/src/nodeSqliteClient.ts‎

Lines changed: 143 additions & 89 deletions
Original file line numberDiff line numberDiff line change
@@ -293,16 +293,21 @@ const failed = (cause: unknown, message: string, operation: string) =>
293293
// Read-transaction cleanup succeeds after worker exit because it can no longer change anything.
294294
const endsReadTransaction = (sql: string) => /^\s*ROLLBACK\b|^\s*COMMIT\b/i.test(sql);
295295

296+
// WAL readers run concurrently; one worker serialized every read behind the slowest transaction.
297+
const READER_POOL_SIZE = 4;
298+
296299
// Bind transactions to one worker; fail after its exit instead of resuming outside the transaction on a replacement.
297-
const makeReader = Effect.fnUntraced(function* (options: SqliteClientConfig) {
300+
const makeReader = Effect.fnUntraced(function* (
301+
options: SqliteClientConfig,
302+
pool: { unavailable: boolean },
303+
) {
298304
let current:
299305
| {
300306
readonly worker: NodeWorkerThreads.Worker;
301307
readonly connection: Connection;
302308
readonly started: Promise<ReaderStartup>;
303309
}
304310
| undefined;
305-
let unavailable = false;
306311
let nextRequestId = 0;
307312

308313
const start = () => {
@@ -439,16 +444,16 @@ const makeReader = Effect.fnUntraced(function* (options: SqliteClientConfig) {
439444
void reader.worker.terminate();
440445
}
441446
// @effect-diagnostics-next-line effectSucceedWithVoid:off - The connection result distinguishes undefined from void.
442-
if (unavailable) return Effect.succeed(undefined);
443-
unavailable = true;
447+
if (pool.unavailable) return Effect.succeed(undefined);
448+
pool.unavailable = true;
444449
return Effect.logWarning("SQLite reader worker unavailable; reads use the main connection", {
445450
reason,
446451
}).pipe(Effect.as(undefined));
447452
});
448453

449454
return Effect.suspend((): Effect.Effect<Connection | undefined> => {
450455
// @effect-diagnostics-next-line effectSucceedWithVoid:off - The connection result distinguishes undefined from void.
451-
if (unavailable) return Effect.succeed(undefined);
456+
if (pool.unavailable) return Effect.succeed(undefined);
452457
let reader: NonNullable<typeof current>;
453458
try {
454459
reader = current ?? start();
@@ -467,7 +472,7 @@ const makeReader = Effect.fnUntraced(function* (options: SqliteClientConfig) {
467472
}),
468473
(startup): Effect.Effect<Connection | undefined> =>
469474
// A late ready event must not hand out a worker retired by another acquire timeout.
470-
unavailable || current !== reader
475+
pool.unavailable || current !== reader
471476
? // @effect-diagnostics-next-line effectSucceedWithVoid:off - The connection result distinguishes undefined from void.
472477
Effect.succeed(undefined)
473478
: Option.isSome(startup) && startup.value.type === "ready"
@@ -1073,21 +1078,48 @@ const make = Effect.fn("makeWithDatabase")(function* (
10731078

10741079
const semaphore = yield* Semaphore.make(1);
10751080
const connection = yield* makeConnection;
1076-
const acquireReader =
1077-
options.readerWorker && isFileDatabase ? yield* makeReader(options) : undefined;
1078-
const readerSemaphore = yield* Semaphore.make(1);
1081+
// A reader that cannot start turns off the whole pool, so routing never picks a dead slot.
1082+
const readerPool = { unavailable: false };
1083+
const readers =
1084+
options.readerWorker && isFileDatabase
1085+
? yield* Effect.forEach(Array.from({ length: READER_POOL_SIZE }), () =>
1086+
Effect.map(
1087+
Effect.all([makeReader(options, readerPool), Semaphore.make(1)]),
1088+
([acquire, lock]) => ({
1089+
acquire,
1090+
lock,
1091+
claims: 0,
1092+
}),
1093+
),
1094+
)
1095+
: [];
10791096

10801097
const writer = { name: "writer" as const, lock: semaphore, connection };
10811098
let activeWriter: ConnectionOwner | undefined;
1082-
const route = Effect.withFiber((fiber) =>
1083-
acquireReader !== undefined && Context.get(fiber.context, ReadOnly)
1084-
? Effect.map(acquireReader, (readerConnection) =>
1099+
// Callers must run route uninterruptibly and pass `claimed` a finalizer that runs `release` once.
1100+
const route = (claimed: (release: Effect.Effect<void>) => Effect.Effect<void>) =>
1101+
Effect.withFiber((fiber) => {
1102+
if (readers.length === 0 || readerPool.unavailable || !Context.get(fiber.context, ReadOnly)) {
1103+
return Effect.succeed(writer);
1104+
}
1105+
// Least claimed wins and ties go to the lowest slot, so idle slots never start a worker.
1106+
let slot = readers[0]!;
1107+
for (const candidate of readers) if (candidate.claims < slot.claims) slot = candidate;
1108+
slot.claims++;
1109+
const reader = slot;
1110+
return Effect.andThen(
1111+
claimed(
1112+
Effect.sync(() => {
1113+
reader.claims--;
1114+
}),
1115+
),
1116+
Effect.map(reader.acquire, (readerConnection) =>
10851117
readerConnection === undefined
10861118
? writer
1087-
: { name: "reader" as const, lock: readerSemaphore, connection: readerConnection },
1088-
)
1089-
: Effect.succeed(writer),
1090-
);
1119+
: { name: "reader" as const, lock: reader.lock, connection: readerConnection },
1120+
),
1121+
);
1122+
});
10911123

10921124
const acquired = (
10931125
target: { readonly name: Acquired["connection"] },
@@ -1101,88 +1133,110 @@ const make = Effect.fn("makeWithDatabase")(function* (
11011133
const writerBorrowBatchSize = 16;
11021134
let writerBorrowsSinceYield = 0;
11031135
const borrower: Borrower = (use) =>
1104-
Effect.flatMap(route, (target) =>
1105-
Effect.withFiber((fiber) => {
1106-
const clock = fiber.getRef(Clock.Clock);
1107-
const start = clock.monotonicTimeNanosUnsafe();
1108-
const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
1109-
const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1110-
const useConnection = Effect.suspend(() => {
1111-
if (target.name === "writer") activeWriter = owner;
1112-
if (
1113-
fiber.getRef(References.TracerEnabled) &&
1114-
fiber.getRef(Statement.SpanPropagationEnabled)
1115-
) {
1116-
annotateAcquired(fiber, acquired(target, clock, start), ownerAtWaitStart);
1117-
}
1118-
return use(target.connection);
1119-
});
1120-
const useOwnedConnection =
1121-
target.name === "writer"
1122-
? Effect.onExit(useConnection, () =>
1123-
Effect.sync(() => {
1124-
if (activeWriter === owner) activeWriter = undefined;
1125-
}),
1126-
)
1127-
: useConnection;
1128-
if (!options.checkpointWorker || target.name !== "writer") {
1129-
return target.lock.withPermits(1)(useOwnedConnection);
1130-
}
1131-
const writerUse = Effect.onExit(useOwnedConnection, (exit) =>
1132-
Effect.sync(() => {
1133-
writerBorrowsSinceYield = Exit.hasInterrupts(exit)
1134-
? 0
1135-
: Math.min(writerBorrowsSinceYield + 1, writerBorrowBatchSize);
1136-
}),
1137-
);
1138-
return Effect.flatMap(target.lock.withPermitsIfAvailable(1)(writerUse), (result) =>
1139-
Option.isSome(result)
1140-
? Effect.succeed(result.value)
1141-
: target.lock.withPermits(1)(
1142-
Effect.andThen(
1143-
// Failed SQL also consumes the batch; cancellation hands off immediately.
1144-
Effect.suspend(() => {
1145-
if (writerBorrowsSinceYield < writerBorrowBatchSize) return Effect.void;
1146-
writerBorrowsSinceYield = 0;
1147-
return Effect.callback<void>((resume) => {
1148-
const immediate = setImmediate(() => resume(Effect.void));
1149-
return Effect.sync(() => clearImmediate(immediate));
1150-
});
1151-
}),
1152-
writerUse,
1153-
),
1154-
),
1155-
);
1156-
}),
1157-
);
1158-
const transactionAcquirer = Effect.flatMap(route, (target) =>
11591136
Effect.uninterruptibleMask((restore) => {
1160-
const fiber = Fiber.getCurrent()!;
1161-
const scope = Context.getUnsafe(fiber.context, Scope.Scope);
1137+
let release: Effect.Effect<void> = Effect.void;
1138+
const claimed = (next: Effect.Effect<void>) =>
1139+
Effect.sync(() => {
1140+
release = next;
1141+
});
1142+
return Effect.flatMap(route(claimed), (target) => restore(borrowTarget(target, use))).pipe(
1143+
Effect.ensuring(Effect.suspend(() => release)),
1144+
);
1145+
});
1146+
const borrowTarget = <A, E, R>(
1147+
target: {
1148+
readonly name: Acquired["connection"];
1149+
readonly lock: Semaphore.Semaphore;
1150+
readonly connection: Connection;
1151+
},
1152+
use: (connection: Connection) => Effect.Effect<A, E, R>,
1153+
): Effect.Effect<A, E, R> =>
1154+
Effect.withFiber((fiber) => {
11621155
const clock = fiber.getRef(Clock.Clock);
11631156
const start = clock.monotonicTimeNanosUnsafe();
1164-
const pending = fiber.getRef(TransactionAcquire);
11651157
const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
11661158
const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1167-
const acquire = Effect.tap((pending?.restore ?? restore)(target.lock.take(1)), () => {
1159+
const useConnection = Effect.suspend(() => {
11681160
if (target.name === "writer") activeWriter = owner;
1169-
if (pending !== undefined) {
1170-
pending.acquired = acquired(target, clock, start);
1171-
pending.ownerAtWaitStart = ownerAtWaitStart;
1161+
if (
1162+
fiber.getRef(References.TracerEnabled) &&
1163+
fiber.getRef(Statement.SpanPropagationEnabled)
1164+
) {
1165+
annotateAcquired(fiber, acquired(target, clock, start), ownerAtWaitStart);
11721166
}
1173-
return Scope.addFinalizer(
1174-
scope,
1175-
Effect.sync(() => {
1176-
if (target.name === "writer" && activeWriter === owner) activeWriter = undefined;
1177-
}).pipe(Effect.andThen(target.lock.release(1))),
1178-
);
1167+
return use(target.connection);
11791168
});
1180-
return Effect.as(
1181-
// A scheduler yield after taking the permit must not precede its release finalizer.
1182-
Effect.provideService(acquire, Scheduler.PreventSchedulerYield, true),
1183-
target.connection,
1169+
const useOwnedConnection =
1170+
target.name === "writer"
1171+
? Effect.onExit(useConnection, () =>
1172+
Effect.sync(() => {
1173+
if (activeWriter === owner) activeWriter = undefined;
1174+
}),
1175+
)
1176+
: useConnection;
1177+
if (!options.checkpointWorker || target.name !== "writer") {
1178+
return target.lock.withPermits(1)(useOwnedConnection);
1179+
}
1180+
const writerUse = Effect.onExit(useOwnedConnection, (exit) =>
1181+
Effect.sync(() => {
1182+
writerBorrowsSinceYield = Exit.hasInterrupts(exit)
1183+
? 0
1184+
: Math.min(writerBorrowsSinceYield + 1, writerBorrowBatchSize);
1185+
}),
11841186
);
1185-
}),
1187+
return Effect.flatMap(target.lock.withPermitsIfAvailable(1)(writerUse), (result) =>
1188+
Option.isSome(result)
1189+
? Effect.succeed(result.value)
1190+
: target.lock.withPermits(1)(
1191+
Effect.andThen(
1192+
// Failed SQL also consumes the batch; cancellation hands off immediately.
1193+
Effect.suspend(() => {
1194+
if (writerBorrowsSinceYield < writerBorrowBatchSize) return Effect.void;
1195+
writerBorrowsSinceYield = 0;
1196+
return Effect.callback<void>((resume) => {
1197+
const immediate = setImmediate(() => resume(Effect.void));
1198+
return Effect.sync(() => clearImmediate(immediate));
1199+
});
1200+
}),
1201+
writerUse,
1202+
),
1203+
),
1204+
);
1205+
});
1206+
const claimedUntilScopeCloses = (release: Effect.Effect<void>) =>
1207+
Effect.withFiber((fiber) =>
1208+
Scope.addFinalizer(Context.getUnsafe(fiber.context, Scope.Scope), release),
1209+
);
1210+
const transactionAcquirer = Effect.uninterruptible(route(claimedUntilScopeCloses)).pipe(
1211+
Effect.flatMap((target) =>
1212+
Effect.uninterruptibleMask((restore) => {
1213+
const fiber = Fiber.getCurrent()!;
1214+
const scope = Context.getUnsafe(fiber.context, Scope.Scope);
1215+
const clock = fiber.getRef(Clock.Clock);
1216+
const start = clock.monotonicTimeNanosUnsafe();
1217+
const pending = fiber.getRef(TransactionAcquire);
1218+
const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
1219+
const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1220+
const acquire = Effect.tap((pending?.restore ?? restore)(target.lock.take(1)), () => {
1221+
if (target.name === "writer") activeWriter = owner;
1222+
if (pending !== undefined) {
1223+
pending.acquired = acquired(target, clock, start);
1224+
pending.ownerAtWaitStart = ownerAtWaitStart;
1225+
}
1226+
return Scope.addFinalizer(
1227+
scope,
1228+
Effect.sync(() => {
1229+
if (target.name === "writer" && activeWriter === owner) activeWriter = undefined;
1230+
}).pipe(Effect.andThen(target.lock.release(1))),
1231+
);
1232+
});
1233+
return Effect.as(
1234+
// A scheduler yield after taking the permit must not precede its release finalizer.
1235+
Effect.provideService(acquire, Scheduler.PreventSchedulerYield, true),
1236+
target.connection,
1237+
);
1238+
}),
1239+
),
11861240
);
11871241

11881242
const client = yield* Client.make({

0 commit comments

Comments
 (0)