Performance

AxiomDB is designed to outperform MySQL on specific workloads by eliminating several layers of redundant work: double-buffering, the double-write buffer, row-by-row query evaluation, and thread-per-connection overhead. This page presents current benchmark numbers and guidance on how to write queries and schemas that stay fast.

Benchmark Results

All benchmarks run on Apple M2 Pro (12 cores), 32 GB RAM, NVMe SSD, single-threaded, warm data (all pages in OS page cache unless noted).

SQL Parser Throughput

Compared to sqlparser-rs (the common Rust SQL parser library):

The speed advantage comes from two decisions:

1. logos DFA lexer — compiles the token patterns to a Deterministic Finite Automaton at compile time. Token scanning is O(n) with a very small constant.
2. Zero-copy tokens — Ident and QuotedIdent tokens are &'src str slices into the original input. No heap allocation occurs during lexing.

Storage Engine Throughput

Wire Protocol Throughput (Phase 5.14)

End-to-end throughput measured via the MySQL wire protocol (pymysql client, autocommit mode, 1 connection, localhost). Includes: network round-trip, protocol encode/decode, parse, analyze, execute, WAL, MmapStorage.

The 185 q/s SELECT result reflects a 3.3× improvement in Phase 5.14 over the prior 56 q/s baseline. Read-only transactions (SELECT, SHOW, etc.) no longer fsync the WAL — see Benchmarks → Phase 5.14 for the technical explanation.

Remaining bottlenecks:

• INSERT (single connection): one fdatasync per autocommit statement; enable Group Commit for concurrent workloads (see below)

Primary-Key Lookups After 6.16

Phase 6.16 removes the planner blind spot that still treated WHERE id = ... as a scan on PK-only tables. The PRIMARY KEY B+Tree is now used for single-table equality and range lookups.

Measured with python3 benches/comparison/local_bench.py --scenario select_pk --rows 5000 --table on the same machine:

The old debt was “planner never reaches the PK B+Tree”. That is now closed. The remaining gap is smaller and sits after planning: row materialization and MySQL packet serialization still cost more than MariaDB/MySQL on this path.

DELETE WHERE / UPDATE After 5.20

Phase 5.19 removed the old-key delete bottleneck for DELETE ... WHERE and the old-key half of UPDATE. Phase 5.20 finishes the real UPDATE fix for the benchmark schema by preserving the heap RecordId when the new row fits in the same slot, which makes selective index skipping correct.

Measured with python3 benches/comparison/local_bench.py --scenario all --rows 50000 --table on the same machine:

Compared to the 4.6K rows/s pre-5.19 DELETE-WHERE baseline that originally triggered this work, AxiomDB now stays in the same order of magnitude as MySQL and MariaDB on the same local benchmark. More importantly, compared to the 52.9K rows/s post-5.19 / pre-5.20 UPDATE baseline, the stable-RID path raises AxiomDB UPDATE throughput to 648K rows/s on the same 50K-row benchmark.

The main remaining write-path bottleneck is now INSERT, not UPDATE.

Indexed UPDATE ... WHERE After 6.20

Phase 6.17 removed the old full-scan candidate discovery path for indexed UPDATE predicates. Phase 6.20 then removed the dominant apply-side costs on the default PK-range benchmark: candidate heap reads are batched by page, no-op rows skip physical mutation, stable-RID rewrites batch their WAL append, and index maintenance only runs when a key, predicate membership, or RID really changes.

Measured with python3 benches/comparison/local_bench.py --scenario update_range --rows 5000 --table on the same machine:

Compared to the 6.17 result (85.2K rows/s), the 6.20 apply fast path is a 4.3x improvement on the same benchmark and now exceeds the documented local MySQL result. The remaining gap is specifically MariaDB’s tighter clustered-row update path, not AxiomDB’s old discovery-side O(n) scan.

INSERT in Explicit Transactions After 5.21

Phase 5.21 adds transactional INSERT staging for consecutive INSERT ... VALUES statements inside one explicit transaction. Instead of writing heap + WAL + index roots per statement, AxiomDB now buffers eligible rows and flushes them together on COMMIT or the next barrier statement.

Measured with python3 benches/comparison/local_bench.py --scenario insert --rows 50000 --table on the same machine:

This path targets one specific workload: many separate INSERT statements inside BEGIN ... COMMIT. Autocommit throughput remains a different problem and depends on the server-side fsync path.

Multi-row INSERT on Indexed Tables After 6.18

Phase 6.18 fixes the immediate multi-row VALUES path for indexed tables. A statement such as:

INSERT INTO bench_users VALUES
  (1, 'u1', 18, TRUE, 100.0, 'u1@b.local'),
  (2, 'u2', 19, FALSE, 100.1, 'u2@b.local'),
  (3, 'u3', 20, TRUE, 100.2, 'u3@b.local');

now uses grouped heap/index apply even when the target table has a PRIMARY KEY or secondary indexes. Before 6.18, that path still fell back to per-row maintenance on indexed tables.

Measured with python3 benches/comparison/local_bench.py --scenario insert_multi_values --rows 5000 --table on the benchmark schema with PRIMARY KEY (id):

Prepared Statement Plan Cache (Phase 5.13)

COM_STMT_PREPARE compiles the SQL once (parse + analyze). Every subsequent COM_STMT_EXECUTE reuses the compiled plan — no re-parsing, no catalog scan:

Schema invalidation (correctness guarantee): after ALTER TABLE, DROP TABLE, CREATE INDEX, etc., the cached plan is re-analyzed automatically on the next execute. The schema_version counter in Database increments on every successful DDL; each connection polls it lock-free (Arc<AtomicU64>) before each execute.

LRU eviction: each connection caches up to max_prepared_stmts_per_connection (default 1024) compiled plans. The least-recently-used plan is evicted silently when the limit is reached. Configurable in axiomdb.toml.

WAL Fsync Pipeline (6.19, closed with a documented gap)

Phase 6.19 replaced the old timer-based CommitCoordinator with an always-on leader-based WAL fsync pipeline. The runtime behavior changed, but the key single-connection autocommit benchmark remains a documented gap.

Measured with:

python3 benches/comparison/local_bench.py --scenario insert_autocommit --rows 1000 --table --engines axiomdb

Current result:

End-to-End INSERT Throughput

Full pipeline: parse → analyze → execute → WAL → MmapStorage. Measured with executor_e2e benchmark (MmapStorage + real WAL, release build, Apple M2 Pro NVMe).

How to achieve this throughput in your application:

-- Fast: one SQL string with N value rows (211K rows/s)
INSERT INTO orders (user_id, amount) VALUES
  (1, 49.99), (2, 12.50), (3, 99.00), -- ... up to thousands of rows
  (1000, 7.99);

-- Slower: N separate INSERT strings (35K rows/s — parse+analyze per row)
INSERT INTO orders VALUES (1, 49.99);
INSERT INTO orders VALUES (2, 12.50);
-- ...

The difference between the two approaches is 6× in throughput. The bottleneck in the per-string case is parse + analyze overhead per SQL string (~20 µs/string), not the storage write.

Four-Engine Native Benchmark (2026-03-24)

All four engines measured locally on Apple M2 Pro, same machine, no Docker overhead, 10,000-row table (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), value INT). Each engine was given equivalent hardware resources.

Engines tested:

• MariaDB 12.1 — port 3306
• MySQL 8.0 — port 3310
• PostgreSQL 16 — port 5433
• AxiomDB — port 3309

INSERT batch — 2× faster than MariaDB

AxiomDB reaches 36K r/s vs MariaDB’s 18K r/s (2× faster) and MySQL’s 16K r/s (2.25× faster). The gap comes from the same three optimizations described above: HeapChain::insert_batch() (O(P) page writes), record_insert_batch() (O(1) WAL write), and a single parse+analyze pass for all N rows.

SELECT * — on par with MySQL, 11× behind PostgreSQL

AxiomDB SELECT (212K r/s) is marginally faster than MySQL 8.0 (189K r/s) and on par with the full-pipeline expectation. PostgreSQL’s 2.3M r/s reflects its shared buffer pool: after the first scan, all 10K rows fit in PostgreSQL’s hot in-memory buffer and subsequent queries never touch disk. AxiomDB’s mmap approach relies on the OS page cache for the same effect — the gap closes when pages are hot, but PostgreSQL’s buffer pool gives it an edge on repeated same-connection scans because it bypasses the OS cache layer entirely.

DELETE (no WHERE) — 3× faster than MariaDB, 40× faster than MySQL

AxiomDB deletes 10,000 rows in 9.6 ms (1M r/s). MariaDB takes 31 ms; MySQL 8.0 takes 407 ms. The AxiomDB advantage comes from two optimizations working together:

1. WalEntry::Truncate — a single 51-byte WAL entry replaces 10,000 per-row Delete entries. MySQL InnoDB writes one undo log record per row before marking it deleted — for 10K rows this is 10K undo writes plus 10K page modifications.
2. HeapChain::delete_batch() — groups deletions by page, reads each page once, marks all slots dead, writes back once. 10K rows across 50 pages = 100 page operations instead of 30,000.

Row Codec Throughput

Row encoding is fast because:

• The codec iterates values once with a fixed dispatch per type.
• The null bitmap is written as bytes with bit shifts — no per-column branch on NULL.
• Variable-length types (Text, Bytes) use a 3-byte length prefix that avoids the 4-byte overhead of a full u32.

Why AxiomDB Is Fast — Architecture Reasons

1. No Double-Buffering

MySQL InnoDB maintains its own Buffer Pool in addition to the OS page cache. The same data lives in RAM twice.

MySQL:   Disk → OS page cache → InnoDB Buffer Pool → Query
                (copy 1)            (copy 2)

AxiomDB: Disk → OS page cache → Query
                (mmap — single copy)

AxiomDB uses mmap to map the .db file directly. The OS page cache IS the buffer. When a page is hot, it is served from L2/L3 cache with zero copies.

2. No Double-Write Buffer

MySQL writes each 16 KB page to a special “doublewrite buffer” area on disk before writing it to its actual location. This prevents torn-page corruption but costs two disk writes per page.

AxiomDB uses a WAL + per-page CRC32c checksum. The WAL record is small (tens of bytes for the changed key-value pair). On recovery, AxiomDB replays the WAL to reconstruct any page that has a checksum mismatch. No doublewrite buffer needed.

3. Lock-Free Concurrent Reads

The Copy-on-Write B+ Tree uses an AtomicU64 to store the root page ID. Readers load the root pointer with Acquire semantics and traverse the tree without acquiring any lock. Writers swap the root pointer with Release semantics after finishing the copy chain.

A running SELECT does not stall any INSERT or UPDATE. Both proceed in parallel.

4. Async I/O with Tokio

The server mode uses Tokio async I/O. 1,000 concurrent connections run on approximately 8 OS threads. MySQL’s thread-per-connection model requires 1,000 OS threads for 1,000 connections, consuming ~8 GB in stack space alone.

Performance Budget

The following table defines the minimum acceptable performance for each critical operation. Benchmarks that fall below the “acceptable maximum” column are treated as blockers before any phase is closed.

Index Usage Guide

Rules of Thumb

1. Every foreign key column needs an index — AxiomDB does not auto-index FK columns. Without an index, every FK check during DELETE/UPDATE scans the child table linearly.

2. Put the most selective column first in composite indexes — A query filtering WHERE user_id = 42 AND status = 'paid' benefits most from (user_id, status) if user_id is more selective (fewer distinct values match).

3. Covering indexes eliminate heap lookups — If all columns in a SELECT are in the index, AxiomDB returns results directly from the index without touching heap pages.

4. Partial indexes reduce size — CREATE INDEX ... WHERE deleted_at IS NULL indexes only active rows. If 90% of rows are soft-deleted, the partial index is 10× smaller than a full index.

5. BIGINT AUTO_INCREMENT beats UUID v4 for PK — UUID v4 inserts at random positions in the B+ Tree, causing ~40% more page splits than sequential integers. Use UUID v7 if you need UUIDs (time-sortable prefix).

Query Patterns to Avoid

Unindexed range scans on large tables

-- Slow: scans every row in orders (no index on placed_at)
SELECT * FROM orders WHERE placed_at > '2026-01-01';

-- Fix: create the index
CREATE INDEX idx_orders_date ON orders (placed_at);

Leading wildcard LIKE

-- Slow: cannot use index on 'name' (leading %)
SELECT * FROM users WHERE name LIKE '%smith%';

-- Better: full-text search index (planned Phase 8)
-- Acceptable workaround for small tables: use LOWER() + LIKE on indexed column

SELECT * with wide rows

-- Fetches all columns including large TEXT blobs for every row
SELECT * FROM documents WHERE category_id = 5;

-- Better: select only what the UI needs
SELECT id, title, created_at FROM documents WHERE category_id = 5;

NOT IN with nullable subquery

-- Returns 0 rows if the subquery contains a single NULL
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM banned_users);

-- Fix: filter NULLs explicitly
SELECT * FROM orders WHERE user_id NOT IN (
    SELECT id FROM banned_users WHERE id IS NOT NULL
);

Measuring Performance

EXPLAIN (planned)

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY placed_at DESC;

Running the Built-in Benchmarks

# B+ Tree benchmarks
cargo bench --bench btree -p axiomdb-index

# Storage engine benchmarks
cargo bench --bench storage -p axiomdb-storage

# Compare before/after an optimization
cargo bench -- --save-baseline before
# ... make change ...
cargo bench -- --baseline before

Benchmarks use Criterion.rs and report mean, standard deviation, and throughput in a format compatible with critcmp for historical comparison.

Optimization Results — All-Visible Flag + Prefetch (2026-03-24)

Two storage-level optimizations implemented on branch research/pg-internals-comparison, inspired by PostgreSQL internals analysis:

All-Visible Page Flag (optim-A)

After the first sequential scan on a stable table (all rows committed, none deleted), AxiomDB sets bit 0 of PageHeader.flags. Subsequent scans skip per-slot MVCC visibility tracking for those pages — 1 flag check per page instead of N per-slot comparisons.

Impact on DELETE: scan_rids_visible() (used before batch delete) goes faster because most pages are all-visible after INSERT → COMMIT. Measured improvement on 10K-row DELETE: 10ms → 7ms (+30%).

Sequential Scan Prefetch Hint (optim-C)

MmapStorage now calls madvise(MADV_SEQUENTIAL) before every sequential heap scan. The OS kernel begins async read-ahead for following pages, overlapping I/O with processing of the current page.

Impact: Measurable on cold-cache workloads (pages not in OS page cache). No regression on warm cache.

Benchmark after both optimizations (wire protocol, Apple M2 Pro)