Database open time is still linear in commits since GC; the 64 MiB checkpoint never fires for small commits
- Dominant language
- C
- Stars
- 268
- Forks
- 18
- Avg merge
- 2h 27m
- Merged PRs (30d)
- 447
Description
Found in a full-repo review at `0ba280f06f`.
#2266 reported database open time growing with incremental commits and was fixed for the shape it reported. The growth is still there for ordinary small commits, because the automatic WAL checkpoint only fires at 64 MiB and a stream of small commits never reaches it.
Measured, 5000-row table, one-row-update commits, median of 5 opens (`SELECT 1;`):
| commits since GC | 0 | 250 | 500 | 1000 | 2000 | 4000 |
|---|---|---|---|---|---|---|
| open | 4.6 ms | 10.5 | 15.3 | 23.6 | 41.2 | **78.5 ms** |
Linear, ~18.5 µs per commit, unbounded. `dolt_gc()` resets it to 4.8 ms **with the file still 24 MB** — the data is unchanged, so this is WAL replay, not data volume.
The same effect on an ordinary freshly built database: a 500k-row table with two indexes opens in **39.7 ms**; after `dolt_gc()` the same database opens in **4.4 ms**. Stock SQLite opens the equivalent data in 4.1 ms. So DoltLite is at open-parity with stock once checkpointed, and ~10x worse before.
Every reader pays this, and the deployment shape that suffers most — a connection per request against a database that commits often — is a common one.
## Cause
`csWalCheckpointThreshold()` returns 64 MiB (`src/chunk_wal.c:134`):
```c
static i64 csWalCheckpointThreshold(void){
i64 n = 64*1024*1024;
#if defined(SQLITE_TEST) || defined(DOLTLITE_MECH_REPRO)
const char *z = getenv("DOLTLITE_WAL_CHECKPOINT_THRESHOLD");
...
#endif
return n;
}
```
`csWalCheckpointDue` (`:145`) compares `iFileSize - iCheckpointReplay` against it; open replays from `iCheckpointReplay` (`csReplayWalFrom`, `src/chunk_wal.c:1290` / `:1376`). Checkpoints are written only when due, on commit (`src/chunk_store_commit.c:518`) or on close (`src/chunk_store.c:639`) — so even a clean close does not checkpoint below the threshold.
Traced with an instrumented build, 3000 small commits on a 5000-row table:
| threshold | checkpoints written | open time |
|---|---|---|
| 64 MiB (shipped) | **0** | 0.06 s |
| 8 MiB | 2 | 0.07 s |
| 4 MiB | 5 | 0.01 s |
At the shipped threshold the safety valve never engages. Write cost is unchanged from 4 to 64 MiB (0.6 s for 3000 commits either way); it only triples at 1-2 MiB. I could not explain why 8 and 16 MiB checkpoint but do not improve open time — that deserves its own look and I am not drawing a conclusion from it.
The threshold override is compiled out of release builds, so there is no supported way to bound this. Verified: setting `DOLTLITE_WAL_CHECKPOINT_THRESHOLD=1048576` has no effect on `build/doltlite` (44.9 ms vs 41.2 ms unset, i.e. unchanged).
## The regression test cannot see it
`test/doltlite_open_perf.sh` measures exactly the right quantity and is calibrated so it cannot fail. It commits `zeroblob(2097152)` — 2 MiB per commit — which is the payload from #2266's own repro script. At that size 64 commits is 128 MiB, so **every stage is already past a checkpoint** and the suite measures the post-checkpoint steady state:
```
as shipped (2 MiB payload):
64 commits: 969ms 128 commits: 880ms 256 commits: 717ms
growth: -252ms; maximum allowed: 750ms
PASS
```
Open time *falls* across stages. Change only the payload constant to 512 bytes and the same suite fails on master today:
```
same suite, payload 512 bytes:
1000 commits: 487ms 2000 commits: 873ms 4000 commits: 1669ms
growth: 1182ms; maximum allowed: 750ms
FAIL: open time grew by 1182ms
```
The regression test inherited the reporter's payload size, and that payload is the one value that hides the remaining problem.
## Fix
Make the checkpoint actually bound open cost for small-commit workloads, and make the threshold a supported setting available in release builds rather than a `SQLITE_TEST`-only env var. Then re-calibrate `doltlite_open_perf.sh` to a small payload and more stages so it proves the bound — it already detects the regression, it just is not pointed at it.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with csWalCheckpointThreshold and csWalCheckpointDue in src/chunk_wal.c, then trace checkpoint calls in src/chunk_store_commit.c and src/chunk_store.c. Run test/doltlite_open_perf.sh with its current and small-payload settings to establish the regression. Done means release builds support a threshold setting and the recalibrated test demonstrates bounded open-time growth for small commits.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, sqlite
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100