10,000×
a cloud-SSD page miss versus a hit
RAM answers in about 100 ns, a gp3 cloud SSD in 1 ms or more. So engines cache hot pages and turn random writes into appends.
99% buffer-pool hits. Good enough?
Bad: 99% is plenty
With 1 ms misses, that averages 10 µs per page.
Good: The last 1% sets the speed
99.9% averages 1 µs. Keep the working set in memory.
An LSM write: one log append, one memory insert
Full memtables flush to sorted, immutable SSTables, and compaction merges them in the background. Nothing on disk changes in place.
Past the memtables, a read checks four L0 files and four levels: eight files.
The trick
In-memory Bloom filters rule out most files, so you read about one block. Range scans get no such help.
Change one 100-byte row. How many bytes hit disk?
A B-tree’s cost depends on locality: a cold page is written whole. An LSM sits near 34×, all sequential.
Speed up commits for regenerable click events?
Bad: fsync = off
Faster, but a crash can corrupt data.
Good: synchronous_commit = off
Per transaction: a crash loses up to 600 ms, never corrupts. Payments keep the default.
Where engines bite in production
- Checkpoints every minute: flushes hit 76 MB/s
- Compaction falls behind: RocksDB slows writes, then stops them
- Queue-like Cassandra tables: reads wade through tombstones
- RAM too small: average page cost rises tenfold
Remember
Pick the engine by the read/write mix. B-trees log, then update pages in place: fast, predictable reads. LSMs append and compact: cheap sequential writes, reads lean on Bloom filters.