tidesdb-mysql is an experimental build that was developed to verify how TidesDB, the LSM-tree key/value engine, can work with MySQL 9.7 as a storage engine. The current build is v0.2.4, and it’s an experiment, not a finished product. So you can use it in your tests if you also want to try TidesDB with MySQL and compare with MariaDB
There was already a way to use TidesDB from SQL. It’s TideSQL, which loads the engine into MariaDB as ha_tidesdb, and it works fine. But it doesn’t work with MySQL. So we wanted TidesDB to work with MySQL 9.7.
MariaDB and MySQL share a lot of history, but they are not the same. We couldn’t just recompile the MariaDB plugin against MySQL headers and call it done. The one thing that stayed put through all of it was TidesDB itself, doing exactly what it does anywhere else. Only the server wrapped around was changed. In result we got our implementation, so if you’re on MySQL, you no longer have to switch to MariaDB to give TidesDB a try.
tidesdb-mysql is a loadable plugin, ha_tidesdb.so. The engine gets built on its own and loaded into the server at runtime, the same shape as the MariaDB version. It speaks the MySQL handler API and wires MySQL tables and indexes onto TidesDB column families. After it loads, TidesDB sits right next to InnoDB in SHOW ENGINES and you choose it per table.
All you need is Docker. Pull the image and start it:
|
1 2 3 4 5 6 |
docker pull perconalab/tidesdb-mysql:0.2.4 docker run -d --name tidesdb \ -e MYSQL_ROOT_PASSWORD=secret \ -p 3306:3306 \ perconalab/tidesdb-mysql:0.2.4 |
The plugin is baked into this image and loaded on boot, so there’s no INSTALL PLUGIN step to remember. Confirm the engine is live:
|
1 2 3 |
docker exec tidesdb mysql -uroot -psecret \ -e "SELECT engine, support FROM information_schema.engines WHERE engine='TidesDB';" # TidesDB | YES |
Now make a table and treat it like any other:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE shop; USE shop; CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64) NOT NULL, price DECIMAL(10,2) NOT NULL, KEY idx_price (price) ) ENGINE=TIDESDB; INSERT INTO products (name, price) VALUES ('Widget', 9.99), ('Gadget', 24.50); SELECT * FROM products WHERE price < 20; |
Transactions, secondary indexes, the usual SQL, it all behaves:
|
1 2 3 |
START TRANSACTION; UPDATE products SET price = price + 1 WHERE name = 'Widget'; COMMIT; |
Per-table TidesDB options ride along in MySQL’s ENGINE_ATTRIBUTE JSON field. MySQL doesn’t have MariaDB’s COMPRESSION=… grammar, so the options are identical but you write them differently:
|
1 2 3 4 5 |
CREATE TABLE events ( id BIGINT PRIMARY KEY AUTO_INCREMENT, msg TEXT ) ENGINE=TIDESDB ENGINE_ATTRIBUTE='{"compression":"ZSTD","bloom_filter":true}'; |
Compression accepts NONE, SNAPPY, LZ4, ZSTD, or LZ4_FAST. Server-wide knobs live in system variables such as tidesdb_default_compression, tidesdb_block_cache_size, tidesdb_compaction_threads, and tidesdb_flush_threads. The full list is in docs/build-and-load.md.
Write a handful of rows, kill the server with no clean shutdown, bring it back, and count what’s left:
# 1. Write rows inside a transaction and COMMIT.
|
1 2 3 4 5 6 7 8 |
docker exec -i tidesdb mysql -uroot -psecret <<'SQL' CREATE DATABASE IF NOT EXISTS t; CREATE TABLE IF NOT EXISTS t.kv (k INT PRIMARY KEY, v VARCHAR(32)) ENGINE=TIDESDB; BEGIN; INSERT INTO t.kv VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'); COMMIT; SELECT COUNT(*) AS before_crash FROM t.kv; -- 5 SQL |
# 2. Hard-kill the server (no graceful shutdown) and restart it.
|
1 2 3 |
docker kill -s KILL tidesdb docker start tidesdb until docker exec tidesdb mysql -uroot -psecret -e 'SELECT 1' >/dev/null 2>&1; do sleep 2; done |
# 3. The committed rows are still there.
|
1 2 |
docker exec tidesdb mysql -uroot -psecret \ -e "SELECT COUNT(*) AS after_crash FROM t.kv;" -- 5 |
after_crash should come back equal to before_crash.
Compression is the one people ask about first, so here’s a table that leans on it. We generate a couple thousand rows of repetitive text, which is exactly the shape ZSTD likes:
|
1 2 3 4 5 6 7 |
CREATE TABLE logs ( id BIGINT PRIMARY KEY AUTO_INCREMENT, level VARCHAR(8) NOT NULL, body TEXT, KEY idx_level (level) ) ENGINE=TIDESDB ENGINE_ATTRIBUTE='{"compression":"ZSTD","bloom_filter":true}'; |
|
1 2 3 4 5 |
INSERT INTO logs (level, body) SELECT IF(RAND() < 0.2, 'warn', 'info'), REPEAT('the quick brown fox jumps over the lazy dog ', 40) FROM information_schema.columns LIMIT 2000; |
|
1 2 |
SELECT level, COUNT(*) AS rows FROM logs GROUP BY level; SELECT id, LEFT(body, 30) AS preview FROM logs WHERE id = 1000; |
The rows go in compressed and come back out as the original text, so queries don’t change at all. If you want to confirm the option actually landed on the table rather than being silently dropped, ask the server what it stored:
|
1 2 |
SHOW CREATE TABLE logs\G -- ENGINE=TIDESDB ... ENGINE_ATTRIBUTE='{"compression":"ZSTD","bloom_filter":true}' |
The bloom filter from that same attribute is what keeps point lookups cheap once the data has compacted down into several on-disk files:
|
1 |
SELECT id, level FROM logs WHERE id = 1500; |
A JSON column behaves the way you’d expect, including the ->> extraction operator:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE kv (k VARCHAR(64) PRIMARY KEY, v JSON) ENGINE=TIDESDB; INSERT INTO kv VALUES ('en', JSON_OBJECT('lang','English', 'msg','hello')), ('es', JSON_OBJECT('lang','Spanish', 'msg','hola')), ('fr', JSON_OBJECT('lang','French', 'msg','bonjour')); SELECT k, v->>'$.lang' AS language, v->>'$.msg' AS greeting FROM kv ORDER BY k; |
And the secondary index on products from earlier is a real index, not decoration. A range query uses it, and EXPLAIN will show idx_price in the key column:
|
1 2 3 |
SELECT name, price FROM products WHERE price BETWEEN 5 AND 20 ORDER BY price; EXPLAIN SELECT name, price FROM products WHERE price BETWEEN 5 AND 20; |
Quite a bit works. The common column types are all there, primary keys single and composite, AUTO_INCREMENT, secondary indexes with index-condition pushdown, COMMIT/ROLLBACK, REPLACE and INSERT … ON DUPLICATE KEY UPDATE, online add/drop index, instant add column, full-text search, spatial indexes, per-row TTL, per-table compression and bloom filters, at-rest encryption, and mixed-engine transactions where a TidesDB table and an InnoDB table share one BEGIN … COMMIT. The functional test suite, which we lifted from TideSQL and then extended, passes 58 of 58 executed tests.
A few things you should know about before you lean on it:
Treat v0.2.5 as a serious experiment. It’s solid enough that committed data rides through a crash, and it’s not something we’d point production traffic at yet.
|
1 |
docker pull perconalab/tidesdb-mysql:0.2.5 |
That’s the whole setup. Spin up a table with ENGINE=TIDESDB, run the crash demo, and point your own SQL at it. The source, the build scripts, and the engine patches all live in the tidesdb-mysql repository, and the durability fixes are written up in KNOWN-ISSUES.md. This is a tool made by users for users, so if you give it a spin, we’d genuinely like to hear what held up and what fell over.
Resources
RELATED POSTS
