MyRocks as an InnoDB Alternative: When the LSM-Tree Approach Pays Off

1. Why InnoDB's B-tree architecture hits limits under write load

InnoDB stores every index, including the primary key, as a B+tree. Every row change is applied directly to the matching page of the tree, which is very efficient for sequential access. Under random write access to large tables, this in-place update model regularly causes page splits, because a page fills up and must be split, which generates additional I/O operations and fragmentation.

Combined with the redo log and the doublewrite buffer, both of which provide crash safety, this creates noticeable write amplification: a single logical row change results in several physical writes to disk. Under workloads with very high insert rates, such as event logs, audit trails, or time series data with millions of rows per day, this overhead adds up to a real bottleneck, even when individual queries would be unproblematic on their own.

2. The basic principle of the Log-Structured-Merge-Tree compared to a B-tree

An LSM-tree flips the storage principle: instead of updating a row immediately at its final position, new writes are first appended to a sorted in-memory structure, the memtable. Once that memtable reaches a certain size, it is written unchanged to disk as a sorted, immutable file called an SST file. Changes to already-written rows therefore never trigger in-place updates, only additional entries that supersede earlier versions.

In the background, a compaction process merges several SST files into larger, freshly sorted files while removing outdated versions and deleted rows. Both writing the memtable and compaction happen sequentially, turning the application's random write pattern into predominantly sequential disk access, whereas a B-tree still needs random page access for the same random write pattern.

3. How MyRocks integrates as a storage engine in MySQL

MyRocks originated at Facebook, today's Meta, as a storage engine layer on top of RocksDB, an embedded key-value library that itself descends from Google's LevelDB. RocksDB handles the memtable, SST files, and compaction, while MyRocks acts as the bridge implementing the MySQL storage engine API and mapping relational tables and secondary indexes onto RocksDB's key-value model.

MyRocks is available as ENGINE=ROCKSDB, shipped by default as a plugin in Percona Server for MySQL, available as an optional loadable plugin in MariaDB, and can be built from source for MySQL Community Server as well. For transactional safety spanning the binlog and RocksDB's own write-ahead log, MyRocks uses a two-phase commit protocol, and operational data is exposed through the INFORMATION_SCHEMA.ROCKSDB_* tables.

4. Write load advantages: lower write amplification and sequential I/O

Because writes are first only appended to the memtable and compacted later in the background, write amplification with MyRocks is noticeably lower than with InnoDB, especially under workloads with many small, randomly distributed inserts or updates. Benchmarks from Facebook and Percona regularly show noticeably lower disk I/O throughput under write-heavy workloads at comparable application-level throughput.

For Magento contexts, this mostly concerns tables with very high insert volume and rare point lookups, such as indexer changelog tables, admin action logs, or customer activity logs, where rows are predominantly written and rarely retrieved individually afterward.

-- Create a changelog table on MyRocks
CREATE TABLE catalog_index_changelog_rocks (
    version_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_id  INT UNSIGNED NOT NULL,
    changed_at DATETIME NOT NULL,
    KEY idx_entity (entity_id),
    KEY idx_changed (changed_at)
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4;

-- Compare current compression ratios per storage engine
SELECT engine, SUM(data_length) AS data_bytes, SUM(index_length) AS index_bytes
FROM information_schema.tables
WHERE table_schema = DATABASE()
GROUP BY engine;

5. Storage compression: why MyRocks tables are often noticeably smaller

RocksDB compresses data blocks per level individually, typically using LZ4 for lower, frequently read levels and the stronger but slower Zstandard for higher levels holding rarely read, older data. Since compaction already rewrites data regularly, this compression integrates into the existing background process at almost no extra cost, unlike InnoDB, where compression forces additional page reorganizations.

In practice, operators frequently report tables two to four times smaller than uncompressed InnoDB, depending on data type and value redundancy. For Magento installations with limited disk space on the database server, for instance with extensive historical order or log data, that can be the difference between a tight and a comfortable storage budget.

-- Configure the compression algorithm per RocksDB level (my.cnf)
-- rocksdb_default_cf_options=compression=kLZ4Compression;
--     bottommost_compression=kZSTD

-- Check current RocksDB compaction statistics per column family
SELECT * FROM information_schema.rocksdb_compaction_stats
WHERE cf_name = 'default';

6. The downside: read amplification and when it becomes noticeable

The price for cheap writes is higher read amplification: a single point lookup may, in the worst case, need to search the memtable and several SST levels before finding a row's current value, while an InnoDB B-tree lookup reaches its target directly in nearly constant depth. Bloom filters mitigate this considerably by ruling out, with high probability, whether a sought key is contained in a given SST file at all, so many levels never need to be read.

Even so, read behavior under load stays less predictable than with InnoDB, particularly for workloads with many random point reads on cold data not yet in the block cache. For tables with a high share of random reads, such as product catalogs with very many individual lookups per second, this can noticeably increase latency compared to InnoDB, even when overall batch throughput is higher.

7. Practical limits: maturity, missing foreign keys, locking differences

MyRocks does not support referential integrity via foreign keys, which is a direct disqualifier for tables with explicit foreign key constraints, as commonly found in classic Magento core schemas, unless those constraints are already enforced in application logic. Locking behavior also differs: MyRocks has no next-key locking like InnoDB, which changes phantom-read behavior under certain isolation levels compared to what teams are used to.

Overall, MyRocks is considerably younger in widespread production use than InnoDB, operational tooling for backup, monitoring, and troubleshooting is narrower, and much third-party tooling and community experience is primarily built around InnoDB. Anyone adopting MyRocks should plan for less established operational knowledge and budget correspondingly generous testing phases.

8. When MyRocks pays off and when InnoDB remains the better choice

MyRocks is particularly suited to tables with very high, predominantly sequential or append-style write load and rare, more batch-style read access: logging, audit trails, time series data, indexer changelogs, or archive tables used mainly for later analysis rather than individual lookups. When disk space on the database server is a scarce and expensive factor, compression clearly favors MyRocks.

For classic OLTP core tables in a Magento shop, such as cart, order, and product catalog tables with a high share of random point reads, mixed read and write load, and foreign key requirements, InnoDB remains the more robust and battle-tested choice. Switching an entire database wholesale to MyRocks therefore rarely makes sense.

9. Migration and coexistence: converting individual tables deliberately

MyRocks can be adopted per table while the rest of the database keeps running on InnoDB. That allows a low-risk, incremental approach: identify concrete candidates with high write volume and low point-lookup volume first, convert them on staging environments as a trial, and measure storage savings as well as response times under realistic load before switching production.

The actual conversion is a simple ALTER TABLE ... ENGINE=ROCKSDB, though for very large tables the online DDL process needs corresponding time and I/O capacity and is ideally run outside peak load periods. Monitoring through information_schema.rocksdb_compaction_stats and RocksDB's own log files then helps track compaction behavior and actual compression ratios in production.

-- Trial-convert an existing InnoDB log table to MyRocks
ALTER TABLE admin_action_log ENGINE=ROCKSDB;

-- Compare size before and after
SELECT table_name, engine,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'admin_action_log';

10. Summary

11. FAQ: MyRocks in MySQL: The Essentials at a Glance