c-relay-pg/plans/wal_checkpoint_fix_plan.md

WAL Checkpoint Fix — db-write Thread CPU Plan

Problem

Fresh profiling of the production server shows the db-write thread consuming 77% average CPU (peaking at 100%) while lws-main sits at 0.3%.

The perf callgraph confirms 100% of the hot symbols are SQLite B-tree read operations running on the db-write thread:

% CPU	Symbol	Role
13.88	sqlite3BtreeTableMoveto	B-tree seek
10.05	sqlite3VdbeExec	VM execution
7.77	rep_movs_alternative	kernel memcpy in pread
7.76	sqlite3GetVarint	varint decode
6.66	pcache1Fetch	page cache lookup
6.02	memcpy	data copy

These are read-path functions, not write-path. The event-worker thread accumulated only 3 CPU ticks over 5 minutes, meaning very few events are actually being stored.

Root Cause

SQLite WAL auto-checkpoint. By default SQLite triggers a checkpoint every 1000 WAL pages. The checkpoint is executed by the next writer — which is always the db-write thread since all writes are funnelled through it.

A WAL checkpoint reads every dirty page from the WAL file and writes it back to the main database file. This involves B-tree traversals to locate pages, which explains the BtreeTableMoveto dominance.

The current db_open_worker_connection in src/db_ops.c sets PRAGMA journal_mode=WAL and busy_timeout=5000 but does not configure wal_autocheckpoint. SQLite's default of 1000 pages applies.

In earlier profiling runs the same CPU burn appeared on lws-main at 55-67% — because that thread was doing the writes directly before the thread-pool refactoring. The work simply moved threads; the total cost is unchanged.

Implementation Plan

Step 1 — Disable auto-checkpoint on the write connection

In db_open_worker_connection add:

sqlite3_exec(db, "PRAGMA wal_autocheckpoint=0;", NULL, NULL, NULL);

This prevents the write thread from ever running a checkpoint inline with normal writes.

Step 2 — Add a periodic checkpoint job type to the thread pool

Add a new job type THREAD_POOL_JOB_WAL_CHECKPOINT to thread_pool.h.

The handler in thread_pool.c calls:

sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, NULL, NULL);

PASSIVE mode checkpoints only pages that are not currently being read, so it never blocks readers.

Step 3 — Submit the checkpoint job on a timer

In the existing 60-second periodic timer in websockets.c, add a call to submit a WAL checkpoint job to the write queue:

thread_pool_submit_wal_checkpoint();

This runs the checkpoint once per minute on the write thread, but as a bounded, predictable operation rather than being triggered unpredictably by every INSERT.

Step 4 — Also disable auto-checkpoint on reader connections

In db_open_worker_connection, the same wal_autocheckpoint=0 applies to reader connections too. Readers can also trigger checkpoints in SQLite; disabling it on all connections ensures only the explicit periodic job does checkpointing.

Step 5 — Run a TRUNCATE checkpoint at shutdown

In thread_pool_shutdown, before closing the write connection, run:

sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_TRUNCATE, NULL, NULL);

This ensures the WAL is fully flushed and truncated on clean shutdown, keeping the database file compact.

Files Changed

File	Change
src/db_ops.c	Add PRAGMA wal_autocheckpoint=0 in db_open_worker_connection
src/thread_pool.h	Add THREAD_POOL_JOB_WAL_CHECKPOINT enum value
src/thread_pool.c	Add execute_wal_checkpoint_job handler; add checkpoint-at-shutdown in thread_pool_shutdown; add thread_pool_submit_wal_checkpoint helper
src/websockets.c	Call thread_pool_submit_wal_checkpoint() in the 60-second periodic timer

Expected Impact

• db-write CPU should drop from ~77% to near 0% when idle (no events to store)
• Periodic checkpoint bursts of a few seconds every 60s instead of continuous burn
• No change to data durability — WAL still protects against crashes; checkpoint just moves data from WAL to main DB file
• Readers are unaffected — PASSIVE checkpoint never blocks them

Risk

• If the relay crashes between checkpoints, the WAL file may be larger than before (up to 60s of accumulated writes). This is safe — SQLite replays the WAL on next open. The WAL file size is bounded by the write rate, which is low.
• PASSIVE checkpoint may not checkpoint all pages if readers hold them. This is fine — the next checkpoint will catch up.

Verification

After deploying, re-run the profiler:

./deploy_lt_debug.sh && DURATION=300 INTERVAL=5 ./tests/thread_cpu_profile.sh

Expected: db-write avg CPU drops below 5%.