Repository navigation
synchronous=FULL + 64KB page_size under WAL causes severe write amplification during normal indexing (~733 writes/sec, ~1.2TB lifetime writes for a 27MB DB) #1419
Description
Environment
- codebase-memory-mcp 0.6.0, Linux (unprivileged LXC, ext4 rootfs)
- 3 concurrent project DBs in
~/.cache/codebase-memory-mcp/, sizes 2-27MB auto_index=false(confirmed viaconfig list) — no background watcher involved, this is plain on-demandindex_repositoryusage
What we observed
Host-level NVMe write throughput sustained ~100-135MB/s for ~3 minutes during a single index_repository run against the largest project (~27MB DB), measured independently via iostat and pidstat -d (cross-checked, both agree). The DB file itself grew only a few MB in that window.
Lifetime /proc/<pid>/io counters on the running server (alive 13.15h at time of sampling) confirm this isn't a one-off:
wchar: 1,186,885,136,130 (~1.11 TiB)
syscw: 34,671,469 (~733 writes/sec sustained for 13h+)
write_bytes: 1,186,595,905,536
Average write size = wchar/syscw ≈ 34,232 bytes — consistent with roughly half of the configured page size, i.e. lots of partial-page/WAL-frame writes each triggering their own fsync.
We also caught a live indexing burst via /proc/<pid>/fd + fdinfo position sampling: the process opens <project>.db + .db-wal + .db-shm during the burst and closes them again after — confirming the writes go to the app's own SQLite store, not logs or anything else.
PRAGMA state (checked read-only on all 3 project DBs, identical across all)
journal_mode = wal
synchronous = 2 (FULL)
page_size = 65536
wal_autocheckpoint = 1000
Analysis
Under WAL mode, synchronous=FULL is redundant per SQLite's own docs — NORMAL gives the same crash-consistency guarantee in WAL mode (durability risk is limited to the last few not-yet-checkpointed transactions on a power loss), which should be an acceptable tradeoff for a reconstructible code-index cache. Combined with a 64KB page size, small-row updates during incremental indexing likely dirty and fsync a full page each time, multiplying physical write volume well beyond the logical data change.
Ask
- Switch to
synchronous=NORMALfor these WAL-mode caches (or expose it as a config flag alongsideauto_index/auto_index_limit). - Reconsider
page_size(or use a smaller page size) for a workload dominated by small per-file/per-symbol row updates rather than bulk sequential writes.
Happy to provide iostat/pidstat/proc-io raw output if useful.
Activity
hsnvn commentedon Aug 3, 2026
---Confirming this on Windows 11 with a much larger blow-up: 261.77 GB WAL for a 37 MB DB, 3 days without a checkpoint.
Same symptom class as reported, different magnitude. codebase-memory-mcp 0.9.0, Windows 11 Pro 26200, 32-core / 64 GB RAM, 4 TB consumer NVMe (TEAM TM8FP4004T).
What we found
┌────────────────────────────┬───────────────────────────┐
│ File │ Size │
├────────────────────────────┼───────────────────────────┤
│ C-Prj-abc.db │ 37.6 MB │
├────────────────────────────┼───────────────────────────┤
│ C-Prj-abc-wal │ 261.77 GB (~7000× the DB) │
└────────────────────────────┴───────────────────────────┘
The WAL's last write timestamp was 3 days old — it had never been checkpointed since. Project scale is modest: 1564 tracked files (828 Python), 16380 nodes / 58864 edges.
Measured host impact
The machine became unusable — but neither CPU nor RAM was the cause:
Diagnostic gotcha worth documenting
The writer is nearly invisible to per-process sampling on Windows: Get-Process reports ReadTransferCount/WriteTransferCount as 0 for codebase-memory-mcp.exe, and \Process(*)\IO Data Bytes/sec showed only ~4 MB/s. Task Manager's I/O columns showed the real figure (100–300 MB/s sustained). This cost us a while — we first suspected SSD-internal garbage collection.
Recovery
Stopping the processes and deleting only *.db-wal + *.db-shm (keeping .db) reclaimed 261 GB and left the index fully usable — head_sha still matched git HEAD and detect_changes returned clean. Disk latency dropped from 18–142 ms to 0.4 ms and queue from 17–33 to 1–2 immediately.
Aggravating factor: auto_watch=true
auto_watch defaults to true, so re-indexing fires without an explicit index_repository call — in our case a worker started on its own and ran 12+ minutes. Combined with the write amplification in this issue, that turns the bug into continuous background disk saturation rather than a one-off cost. Setting auto_watch=false is an effective mitigation for anyone hitting this, since no SQLite knob (synchronous, page_size, wal_autocheckpoint) is exposed through config.
Supporting the proposed fix
+1 on synchronous=NORMAL for WAL caches. Additionally, wal_autocheckpoint=1000 with page_size=65536 means the first automatic checkpoint is deferred until ~65 MB of WAL — worth lowering, or setting an absolute byte ceiling, so a stuck checkpoint can't reach three-digit GB unnoticed.
DeusData commentedon Aug 3, 2026
Thank you for the careful write-volume measurements and the independent cross-platform evidence. Current main intends on-disk stores to use WAL with
synchronous=NORMAL, temporarily switches synchronization off during bulk writes, and sets a 256 MiB journal limit. The reported effectiveFULLand 64 KiB state therefore does not match the intended steady-state configuration. That discrepancy, together with the measured amplification, is a high-priority0.9.1-rcstability issue. The red case should record the effective pragmas and write volume so the fix binds to the observed behavior rather than an assumed cause.DeusData commentedon Oct 1, 2026
Thank you both, @pacewicz and @hsnvn, for measurements precise enough to point straight at the mechanism. The ~34 KB average write @pacewicz saw is exactly what 64 KiB WAL frames look like when they are written as header plus page.
Two notes on the PRAGMA readout:
synchronousandwal_autocheckpointare per-connection and never stored in the file, so a separate sqlite3 shell always reports its own defaults (FULL/1000).page_size=65536is real: our bulk writer creates every index with 64 KiB pages.That page size turned out to be the multiplier. Our write connections kept SQLite's default 2 MB page cache, which holds only about 31 such pages, so an incremental reindex kept spilling and rewriting pages in the WAL. On a 294 MB django index, a one-line edit wrote 5.2 GiB of WAL.
The fix in #2469 gives write connections a 64 MiB cache and stops forcing
synchronous=FULLon the private staging database. The durable FULL checkpoint before the atomic rename stays, so crash safety is unchanged. Measured: 5.2 GiB → 0.52 GiB of WAL for that edit, and WAL fsyncs 9 → 2 on a full index.Since v0.9.1, indexing no longer writes the live DB in place, and the WAL is capped (#1083). One cost remains: each reindex still copies the whole index into a staging file. We're keeping this issue open for that follow-up, and it is why
auto_watch=falseremains a useful lever on very large repos. Thank you again!Merge pull request #2469 from DeusData/fix/issue-1419-sqlite-write-am…
fix(store): cut WAL write amplification on 64 KiB-page indexes (DeusD…
DeusData commentedon Oct 19, 2026
Thank you again for the write-volume measurements. The last update separated the cache/WAL improvement in #2469 from the remaining full-index staging-copy cost. I have no additional result on that remaining cost to report in this follow-up; this is not a claim that the whole issue is resolved.
DeusData commentedon Oct 25, 2026
Thank you for the measurements. The earlier cache/WAL improvement does not settle the remaining full-index staging-copy cost; that follow-up remains open. Sorry for the delay; this is waiting on maintainer follow-up, and I’ll follow up soon.
Enferlain commentedon Oct 26, 2026
Another data point for the remaining staging-copy cost, from the background watcher on a large DB.
0.12.1-fork.1(my fork, 26 commits ahead of upstream main), Linux under WSL2, ext4 on a virtual disk on a consumer NVMe drive..gzexperiment dumps).watcher.changedevents, which led to 95 completeddaemon.index.resultruns of this project./proc/*/io. I haven't attributed bytes to each run, but 95 copies of a 5 GB file would be about 475 GB..cbmignoretook the DB down to 44 MB, and a full re-index now takes about 30 s.Suggestions: the watcher could avoid a full copy when the DB is large, or warn when the indexed tree is dominated by large data files (JSON/gz) that produce few code nodes.