The server that taught me about write amplification was a metrics ingest box. It took a steady firehose of inserts — host metrics, a few tens of thousands of rows a second at peak — into InnoDB, on respectable consumer NVMe. The workload looked modest: the data itself grew by maybe a hundred gigabytes a month. But the SMART wear indicators on those drives fell off a cliff, the kernel reported disk write rates several times higher than the data growth, and the drives were on track to burn through their rated endurance long before the hardware refresh date. The database was writing multiples of every byte we thought we were storing. Moving the hottest tables to MyRocks cut the physical write rate dramatically and shrank the dataset at the same time. It also cost us things, and this post is honest about both sides.
MyRocks is MariaDB's build of MyRocks/RocksDB — Facebook's LSM-tree storage engine — available as a pluggable engine since MariaDB 10.2. It exists for exactly one profile: write-heavy, space-sensitive workloads on flash. Here is how it differs from InnoDB, where it wins, what it costs, and how to run it without surprises.
Why InnoDB struggles on pure ingest
InnoDB stores rows in a B+tree of 16 KB pages. That structure is excellent for reads and mixed workloads, but a single-row insert to a random position in the tree dirties a whole page, and the durability machinery multiplies the cost: the change goes to the redo log, the page goes through the doublewrite buffer, and eventually the page itself is flushed. One logical row write becomes several physical writes — this is write amplification, and it is the tax you pay for crash safety on a B+tree. Random-key inserts make it worse by forcing page splits, and InnoDB's answer to space pressure, page compression via KEY_BLOCK_SIZE, is famously finicky: recompression failures, alignment requirements, and filesystem punch-hole behavior make it a knob I have seen cause more incidents than savings.
None of this makes InnoDB bad. It makes ingest its worst profile: relentless small writes, no reads worth caching, and a dataset that only ever grows.
What MyRocks does differently
RocksDB is a log-structured merge tree. Writes are appended to a write-ahead log and applied to an in-memory memtable; when the memtable fills, it is flushed to disk as a sorted, immutable file; and background compaction continuously merges files from level to level, discarding overwritten versions. Nearly everything the disk sees is large and sequential instead of small and random, and because data is rewritten in bulk during compaction anyway, compression is applied per level — something fast like LZ4 near the top, something heavy like ZSTD at the bottom where most of the bytes live. Point lookups are protected by bloom filters so a missing key usually costs no disk reads at all.
In MariaDB it is just another engine. Enable it and choose it per table:
INSTALL SONAME 'ha_rocksdb';
CREATE TABLE events (
id BIGINT NOT NULL AUTO_INCREMENT,
host VARCHAR(64) NOT NULL,
metric VARCHAR(64) NOT NULL,
value DOUBLE,
ts DATETIME NOT NULL,
PRIMARY KEY (id),
KEY (host, ts)
) ENGINE=ROCKSDB;
-- Confirm what you got:
SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = 'metrics';
-- The buffer pool analog and the firehose of engine counters:
SHOW GLOBAL VARIABLES LIKE 'rocksdb_block_cache_size';
SHOW GLOBAL STATUS LIKE 'Rocksdb%';The per-table choice is the whole game: you do not convert a database to MyRocks, you convert the three tables that are eating your SSDs, and you leave everything else on InnoDB.
Where MyRocks genuinely wins
- Ingest-heavy append streams. Metrics, logs, clickstream, IoT telemetry, audit trails — workloads that are almost all inserts, keyed so recent data is hot. LSM writes are sequential and batched, so sustained insert throughput holds up where a B+tree starts thrashing.
- Space-constrained datasets. Sorted runs compress very well, and there are no half-empty pages after splits. Against uncompressed InnoDB the space savings are usually substantial — the exact ratio depends on your data, so measure with a real sample, but the direction is reliable.
- SSD endurance. Sequential compaction writes plus fewer total bytes means meaningfully less NAND wear for the same logical write volume. If your capacity plan is bounded by drive TBW ratings — common with consumer-grade flash — this alone can justify the engine.
The tradeoffs, in plain terms
- Range scans pay the bill. An InnoDB primary-key range scan reads rows in order from one clustered structure. An LSM range read merges iterators across the memtable, level-zero files, and every deeper level. Short ranges are fine; large scans are slower, and bloom filters only help point lookups — they do nothing for a scan. Reporting queries over MyRocks tables need their own honest benchmarking.
- Transactions buffer in memory. A MyRocks transaction assembles its write batch in memory until commit. Giant transactions hit limits (rocksdb_max_row_locks and friends), and initial bulk loads should go through the session bulk-load path — SET rocksdb_bulk_load=1, load in primary-key order, turn it off — rather than a single monolithic transaction. Treat that mode as a trusted-pipe shortcut, not a general speedup: it skips unique-key checks and can commit in the middle of your transaction, so feed it only prevalidated, sorted data you would be happy to load twice.
- Reduced isolation semantics. MyRocks supports READ COMMITTED and REPEATABLE READ but has no gap locks, which means statement-based binary logging is unsafe with it. Use ROW binlog format — you should anyway, but here it is not a preference.
- A shorter feature list. No FULLTEXT or SPATIAL indexes, and the online DDL story is younger than InnoDB's. Test your actual ALTER statements on a copy before you trust them in a deploy pipeline.
- Younger tooling. Physical backup support for MyRocks arrived later than InnoDB's and has its own mechanics; logical dumps of large LSM tables are slow because of the range-scan cost above. Verify your exact backup path — with your exact MariaDB version — before the day you need it.
The handful of knobs that matter
MyRocks exposes an intimidating number of options, most of which you should leave alone. Start with two. rocksdb_block_cache_size is the buffer pool analog: it caches uncompressed blocks, and on a dedicated ingest box it deserves real memory. rocksdb_max_background_jobs controls compaction concurrency — on a many-core box under heavy ingest, too few background threads lets compaction fall behind, files pile up in the upper levels, and read amplification climbs until queries degrade. The write buffer size (rocksdb_write_buffer_size) and per-column-family compression options are the next tier. Beyond that, the Rocksdb_* status counters tell you the truth: compaction throughput, bytes read and written per level, bloom filter hits. Trend those before you touch anything esoteric.
The honest recommendation: mostly stay on InnoDB
Everything above is why MyRocks is a specialist, not a successor. InnoDB remains the right default for relational workloads: gap locks, mature DDL, every tool on earth understanding it, and range-scan performance you never have to think about. If you mix engines, keep the boundary clean anyway. A transaction that writes both InnoDB and MyRocks does stay atomic — both engines are XA-capable and the server coordinates the commit with its internal two-phase commit machinery — but you pay an extra prepare and fsync on every such commit and inherit XA's failure modes to debug, and any engine in the mix without XA support breaks the guarantee entirely. A write that constantly must be atomic with your InnoDB tables usually just belongs in InnoDB. The pattern that has worked for me is boring: InnoDB everywhere, MyRocks for the identified ingest tables, and a periodic check that the workload still matches the assumption.
And a warning about the failure mode I see most: someone enables MyRocks for the compression, on a read-heavy table, and files a ticket about slow queries a month later. Compression is a property of the workload as much as the engine. Profile first.
The signals that cross the database–hardware line
MyRocks lives on the seam between database counters and hardware: write amplification starts as a Rocksdb_* number and ends as a SMART wear percentage. Put those two in front of you before anything else — per-level bytes read and written, compaction lag, and the drive's media-wear indicator — and re-check them after every tuning change. I work on MonPG, which monitors PostgreSQL today; its MariaDB support is coming soon and in active development, with per-engine signals like compaction health on the list precisely because they drown in a wall of status variables. Running PostgreSQL alongside? The same workload-first approach is live there — start at the PostgreSQL overview, browse the blog, or check the engine comparison.