dolthub / dolthub/doltlite

Performance fixtures cannot reach the engine's hot spots: no secondary index, nothing past the cache, no stock arm

Closed
#2,920 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
268
Forks
18
Avg merge
2h 27m
Merged PRs (30d)
447

Description

Found in a full-repo review at `0ba280f06f`.

The performance suites run in CI and their thresholds are shaped sensibly, but their **fixtures** are built so that the engine's actual hot spots cannot appear in them. Several multi-x regressions found in this review sit entirely outside what these benchmarks exercise.

This is about fixture shape, not wiring — `doltlite_perf.sh`, `doltlite_count_perf.sh` and `doltlite_checkpoint_perf.sh` *are* reachable from CI via `doltlite_timing_suites()` (`test/lib/doltlite_suite_manifest.sh:145`) → `platform-test.yml:215`.

## What the fixtures miss

**No secondary index, and a change set shaped to be free.** `make_merge_data_db` (`test/vc_perf_ceiling.sh:164`) builds:

```sql
CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT);
```

with `MERGE_ROWS=100000` and `MERGE_CHANGE_ROWS=2000` (`:33-34`), changed in contiguous PK ranges (`WHERE id BETWEEN 1 AND 2000`). So the merge benchmark changes 2% of rows, in ascending key order, on a table with no secondary index — which is precisely the case where the merge index path costs nothing. The superlinear index-edit cost is structurally invisible to it.

**Every fixture fits in the node cache.** The largest is 100,000 rows; the sysbench suites use `BENCH_ROWS=100000` (`test/sysbench_compare_*.sh:10`). The scan-reuse cliff sits at roughly 60 MB of chunks, well above all of them, so no benchmark ever measures the cold-cache regime that a real database of any size lives in.

**No benchmark compares against stock SQLite.** None of `vc_perf_ceiling.sh`, `doltlite_perf.sh`, `doltlite_count_perf.sh`, `doltlite_open_perf.sh` or the `sysbench_compare_*.sh` suites reference a stock reference binary or `assert_stock_reference.sh`. The sysbench suites take `SQLITE3=${SQLITE3:-./sqlite3}` (`:5`) but run as "candidate vs baseline" — DoltLite PR against DoltLite base. A gap against stock that has always been there therefore never surfaces; only a change since the last commit does.

**`doltlite_perf.sh`'s ratios are diluted by process startup.** Each sample is a whole CLI invocation, and `assert_ratio` floors the small side of the comparison at 50 ms (`test/doltlite_perf.sh:43`), so the query work has to regress by more than an order of magnitude before a 10x ratio limit can trip.

**`doltlite_checkpoint_perf.sh` enforces nothing.** Its final line is literally `PASS: measured checkpoint and post-checkpoint append ratios; no bound is enforced yet`; the only non-zero exits are structural.

## Suggested changes

- Add a secondary index to the merge fixture, and a case that changes a large fraction of rows with index keys uncorrelated with the primary key.
- Add one fixture large enough to exceed the node cache (a few hundred MB), so the cold-cache regime is measured at all.
- Give at least one scan benchmark a stock arm, or an absolute ceiling, so a standing gap is visible and not just a delta.
- Measure the query inside one process (or subtract a measured startup constant) before taking a ratio in `doltlite_perf.sh`.
- Give `doltlite_checkpoint_perf.sh` a real bound from a measured baseline, the way `vc_perf_ceiling.sh` does.

One caveat on the tight ceilings that do exist: `VC_PERF_IO_SCALE_MAX=5` (`test/vc_perf_ceiling.sh:360`) lets a loaded runner multiply every ceiling by up to 5x. That is a deliberate anti-flake measure, but it means the ceilings are loosest exactly when the runner is slowest.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with test/vc_perf_ceiling.sh and the fixture and threshold sections it names, then inspect test/doltlite_perf.sh, test/doltlite_checkpoint_perf.sh, and the sysbench_compare_*.sh suites. Trace doltlite_timing_suites() in test/lib/doltlite_suite_manifest.sh and its platform-test.yml entry before running the relevant benchmarks. Done means the fixtures cover indexed and cold-cache cases, at least one scan has a stock or absolute comparison, query ratios account for startup, and checkpoint performance has an enforced bound.

Written by the indexing model from the issue text.

Assessment

Tech stack
shell, sqlite
Domain
databases, performance, testing
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.