Benchmarking vector indexes in MySQL with vector-dev-bench

AI

PostgreSQL has a rapidly evolving vector ecosystem. MySQL is earlier on that road, but the community is hard at work catching up. MariaDB ships its own HNSW index, Google engineers have shared a ScaNN-based vector index on MySQL's own vector index proposal, Percona is building an HNSW index inside InnoDB, and VillageSQL is building vector search as an extension with vsql-vector.

We want vector search on MySQL to be something that teams can choose with confidence (and not have to switch to PostgreSQL to get). Getting there takes more than one good implementation. The teams building these indexes need to measure them the same way, check each other's numbers, and fix what the numbers show.

Working with Percona, we published vector-dev-bench. It runs on a laptop, it drives VillageSQL, MariaDB, PostgreSQL with pgvector, and MySQL built from Google's ScaNN pull request, and it gives an answer in minutes.

Doing things in the open #

A benchmark number is only as good as the method behind it. A public method lets anyone rerun numbers, read the exact SQL each engine received, and tell us where the method may be unfair.

It also makes collaboration practical. When another team can run the same harness against their own build, we can compare results without a bunch of upfront coordination. With vector-dev-bench, an engine is one entry in a Python dictionary plus a start script, so adding a new one is a small change.

We've seen this work already. Percona's vector-bench is a full comparison framework, and we added VillageSQL to our copy of it. Along the way, we noticed that pgvector loaded its rows with COPY while the MySQL engines used INSERT, so the build times were measuring two different kinds of statements. We switched pgvector's incremental build to batched INSERTs so that every engine loads rows the same way. vector-bench adopted the change, and its code credits our commit. That is the kind of exchange we want more of.

Two tools for two jobs #

vector-bench and vector-dev-bench answer different questions, and we use both.

vector-bench is built to produce a comparison that you can publish. It runs each engine in Docker, built from pinned sources where it compiles them, runs real datasets such as SIFT, GloVe, and GIST, and measures recall against throughput, build cost, concurrency from 1 to 32 clients, filtered search, and recall after deletes and reinserts. It runs every engine twice, once with identical resources and once with each vendor's recommended settings, and it records the hardware and versions with every result. Its main profile takes about two days, which is the right cost for a number that other people will cite.

vector-dev-bench is built for the quick, iterative loop in which you change the index, rebuild it, and want to know whether recall moved. It runs against engines you've already built, does not use containers, and uses synthetic data by default. It builds a 20,000-vector index in about 7 seconds. Its README is plain about the trade-off: the numbers are for relative comparisons, and a published comparison belongs in a framework like vector-bench.

vector-dev-bench Measurement #

vector-dev-bench is three command-line tools over one shared module:

Recall is the measure that matters here, because an approximate nearest-neighbor index returns approximately the right answer by design. A test that compares exact output, the way MySQL's own test framework does, fails on a correct index. vector-dev-bench computes the true nearest neighbors itself with NumPy and reports what fraction of them each engine returned. You can also pass --dataset to use a standard ann-benchmarks dataset with its published ground truth.

The engines are profiles:

VillageSQL, MariaDB, and the Google build all speak the MySQL protocol, and the harness drives all three through the same PyMySQL connection code. The client cost is the same for each engine, but it isn't zero. A gap between two of these engines holds for the machine that measured it, and it can change on different hardware, so rerun it on the hardware you care about before you trust it.

pgvector takes a different path. The harness sends each batch of pgvector inserts or queries through the psql command-line client, so the timed window also includes starting psql and opening its connection. The parallel options (--readers and --build-threads) fall back to one serial client, and rw_bench.py does not run against pgvector. A VillageSQL-versus-pgvector number from vector-dev-bench measures the two clients as well as the two engines. Treat it as a hint about where to look, and don't quote it as a verdict on either engine.

What a run looks like #

We ran a recall sweep against VillageSQL 0.0.7 with the vsql-vector-pk build of the extension (whose HNSW index stores each row's primary key), on 20,000 random 32-dimension vectors. The tool builds the index once and then queries it at four ef_search values. The README and RUNBOOK cover building each engine and running the tools.

Here is the output from an Apple M5 laptop with 32 GB of memory:

build_time_s=7.03  (index built once; sweeping ef_search)
ef_search  recall@10    qps
50         0.7870       8829.3
100        0.9035       5903.0
200        0.9645       3526.0
400        0.9875       2044.4

This is the curve every HNSW index trades along. A wider search finds more of the true neighbors and answers fewer queries per second. The shape is also a check on the run itself: recall that stays at exactly 1.0 when ef_search drops usually means the optimizer chose a full table scan and the index wasn't used.

MariaDB and PostgreSQL run the same sweep through their own profiles, and run_three_way.sh runs all three engines one after another so that only one server is busy at a time.

Reads while the index is changing #

Benchmarks such as ann-benchmarks build the index once and then query a corpus that never changes. Production tables change all the time. rw_bench.py covers that case: it runs readers alone, adds writers for a window in the middle, and then runs readers alone again. This run used 4 readers and 4 writers against the same 20,000-vector index:

total reads=47700  writes=2000 rows  write_rate=432 rows/s over 4.6s
read-only mean=4564 qps  read+write mean=2738 qps  impact=40% drop
read latency (us)   p50      p95      p99      max      n
  read-only             797     1399     1805    87018    32038
  read+write           1440     1803     1908     8991    10988

The per-second table in the full output shows reads dropping when the writers start at second 4 and recovering once they stop at second 8. For an index that lives inside a transactional engine, this is one of the numbers we care about most, because it shows what a busy table costs the queries that read it.

When to use it, and when not to #

Use vector-dev-bench when you're changing an index and want to know within minutes whether recall, build time, or read throughput moved. Use it as a recall check in a script, since recall_bench.py exits with an error below its threshold. Use it to try an idea against MariaDB or pgvector on the same machine (for pgvector, recall and build time only) before you invest in a full run.

Use a full framework such as vector-bench when the number is going to leave the room. That includes anything published, anything that compares absolute latency, and anything that depends on filtered search or recall after deletes. Latency under concurrency counts as absolute latency. A comparison with pgvector needs the full framework most of all, because pgvector is the engine whose client differs. Every number from vector-dev-bench passes through a client, so absolute latency includes the client and the round trip. Synthetic vectors give a trustworthy relative signal on the machine that ran them, but they don't predict recall on real embeddings.

Add your engine #

The repository is Apache-2.0 licensed, and an AGENTS.md file walks a coding agent through running it. If you're building a vector index for MySQL, or for anything that speaks the MySQL or PostgreSQL protocol, add a profile and a start script and send a pull request. If you think our method is unfair to your engine, open an issue and tell us why. Getting vector search on MySQL to the point where teams can pick it with confidence is work for everyone in the MySQL community, and it starts with numbers we can all reproduce.

You can get VillageSQL from the website, read how custom indexes work in the custom indexes documentation, and see our Percona Live demo in Vector search in MySQL: an early look at HNSW and custom indexes in VillageSQL.