TideSQL 5 and MariaDB: the Tides Are Moving Fast

Written by Written by Frédéric Descamps Leave a comment on TideSQL 5 and MariaDB: the Tides Are Moving Fast

A few months ago, I wrote about my first experiments with TideSQL, the MariaDB storage engine powered by TidesDB.

At that time, getting started meant building the TidesDB library, adding the TideSQL sources to the MariaDB source tree, compiling everything, installing the plugin and finally playing with:

CREATE TABLE my_table (...) ENGINE=TIDESDB;

It worked, and I liked what I saw.

But things are moving fast.

TideSQL 5.0.0 is now available, based on TidesDB 10, and I think it is time to have another look at the project.

And this time there is quite a lot to look at.

TidesDB is a fast, open-source, embeddable transactional key-value storage engine library written in C.
TideSQL is a pluggable storage engine for MariaDB and MySQL-compatible database servers that uses TidesDB as its underlying storage layer.

A Quick Reminder: What Is TideSQL?

TidesDB is a storage engine based on an LSM-tree, a Log-Structured Merge-tree.

Instead of organizing data around pages like a traditional B-tree storage engine, writes are first absorbed into memory and the write-ahead log. Data is later flushed into sorted files and reorganized by background compaction.

This makes LSM trees particularly interesting when there are many writes.

TideSQL is the layer that brings TidesDB into MariaDB as a normal pluggable storage engine.

From the SQL side, there is nothing exotic:

CREATE TABLE events (
    id BIGINT NOT NULL AUTO_INCREMENT,
    ts DATETIME NOT NULL,
    device_id INT NOT NULL,
    payload JSON,
    PRIMARY KEY (id),
    KEY idx_ts (ts)
) ENGINE=TIDESDB;

And that is something I really like about the MariaDB storage engine architecture.

The application still talks SQL to MariaDB. We can choose how individual tables are stored.

A Lot Has Changed Since My First Test

When I first tested TideSQL, I was mostly interested in the LSM implementation, TTL, partitioning and its potential for event and time-series workloads.

TideSQL 5 goes much further.

It supports the features we expect from a serious transactional storage engine: MVCC transactions, primary and secondary indexes, foreign keys, auto-increment, generated columns, savepoints, XA transactions, partitioning and online DDL.

There is also compression, TTL, online backup and data-at-rest encryption.

But there are some less usual things too.

TideSQL supports full-text indexes using BM25, vector indexes for approximate nearest-neighbor searches, and spatial indexes.

So we can have an LSM storage engine inside MariaDB and still use features such as:

FULLTEXT(...)

or MariaDB vector indexes.

That starts to become very interesting.

Especially because these features are available through the same MariaDB SQL interface and can coexist with tables using InnoDB or another storage engine.

Installing TideSQL Is Also Getting Easier

One of the less exciting parts of my first experiment was the build process.

I had to compile tidesdb-lib, then compile TideSQL as part of the MariaDB source tree.

That is fine for somebody like me who spends far too much time compiling MariaDB anyway 🙂

But it is obviously not how most users want to install a storage engine.

This is changing too.

TideSQL can now be built and packaged using MariaDB Foundry.

Foundry builds plugins outside of the MariaDB Server source tree against an installed MariaDB version. It can produce the packages we normally expect on Linux: RPM, DEB and tar.gz.

This is important.

A storage engine becomes much easier to test when installing it doesn’t mean rebuilding the complete database server.

Install MariaDB, install the TideSQL package, load the plugin and start experimenting.

That is much closer to what the MariaDB plugin ecosystem should look like… and more to come soon on this particular topic!

Transactions and Durability

TideSQL is transactional and uses the MVCC implementation provided by TidesDB.

MariaDB transaction isolation levels are mapped to the engine, and TideSQL also exposes different durability choices.

This matters when looking at performance numbers.

An LSM engine can accept writes into its memtable and WAL very quickly, but comparing that with another engine only makes sense when we understand what each engine is asked to guarantee.

TidesDB can operate with different synchronization modes, from avoiding an fsync for every commit to fully synchronizing commits to storage.

As always with database benchmarks, durability settings matter. A lot.

And What About Performance?

The TidesDB team recently published a sysbench comparison between TideSQL 5.0.0 and InnoDB using MariaDB 13.1.

The test used 16 tables with one million rows each, around 3GB of data, and ran workloads with 1, 8 and 16 threads.

The results are impressive, particularly for writes.

With the initial configuration and 16 threads, TideSQL was ahead in nine of the ten tested workloads. The largest difference appeared with secondary-index updates.

But don’t stop reading here and conclude:

TideSQL is 60x faster than InnoDB!

That would be the wrong conclusion.

There are some important details in the test.

The initial InnoDB buffer pool was only 128MB while the complete dataset was around 3GB. TideSQL used a 256MB block cache and a 256MB memtable.

Both engines therefore operated with datasets much larger than their caches.

The test was also performed on one machine, with runs lasting 20 seconds after a short warm-up.

This is useful data, but it is not a universal database benchmark.

Things Get More Interesting With More Memory

The author also repeated the tests with 4GB caches. And this is where the results become much more useful.

With the dataset fitting into memory, InnoDB became faster for some read workloads.

For point selects, for example, InnoDB was about 2.9 times faster. That makes sense.

A clustered B-tree with the pages already sitting in the buffer pool is extremely efficient for point lookups. But the write results did not disappear.

In the published test, TideSQL remained significantly ahead for write-heavy operations such as updates, deletes and inserts. And that also makes sense.

This is exactly the type of workload where an LSM architecture is supposed to be interesting.

Instead of continuously modifying pages, TideSQL can append writes to its memtable and WAL and let compaction organize the data later.

Different architecture, different trade-offs.

Full Durability

There was another test I found important.

Both engines were tested with stronger durability settings: innodb_flush_log_at_trx_commit=1 for InnoDB and FULL synchronization for TideSQL.

The huge differences became smaller. Again, no surprise: making sure commits reach durable storage has a cost.

But TideSQL still performed very well on the write-heavy workloads in that particular test.

This is why I always prefer looking at several configurations instead of one big benchmark number.

Databases don’t run benchmark charts. They run workloads.

One Interesting Detail About Contention

There is another behavior worth knowing.

TideSQL performs optimistic conflict validation for some transactional workloads. Under contention, transactions can therefore be retried at commit time.

In the published sysbench test, retries increased significantly as concurrency increased.

There is also a practical difference for applications: the conflict can currently reach the client as MariaDB error 1180 rather than the 1213 deadlock error applications often expect.

MariaDB error code 1180 (ER_ERROR_DURING_COMMIT): Got error %iE during COMMIT
Learn more: https://err.mariadb.com/1180

MariaDB error code 1213 (ER_LOCK_DEADLOCK): Deadlock found when trying to get lock; try restarting transaction
Learn more: https://err.mariadb.com/1213

If your application only retries transactions after error 1213, this is something to keep in mind when testing TideSQL.

These small operational details are exactly the things I like discovering when playing with a new storage engine.

Benchmarks are nice, behavior in real applications is more interesting.

Replication and Galera

TideSQL is not limited to standalone MariaDB instances.

The current engine supports MariaDB replication and also integrates with Galera through the wsrep interface.

The Galera part is particularly interesting because TideSQL participates in write-set certification and conflict handling at the engine level.

I definitely want to spend some time testing this.

Running an LSM-based engine in a MariaDB Galera Cluster sounds like a good playground for another article.

One Server, Different Storage Engines

For me, this remains the most interesting part; this isn’t really about replacing InnoDB. I don’t think that is the useful question.

The useful question is:

Which storage engine makes sense for this table and this workload?

  • A transactional application might continue using InnoDB for its main tables.
  • A large ingestion table could use TideSQL.
  • An analytical workload could use DuckDB.

And all of them can comfortably live behind MariaDB SQL.

For example:

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(200)
) ENGINE=InnoDB;

CREATE TABLE events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME NOT NULL,
    customer_id BIGINT,
    payload JSON
) ENGINE=TidesDB;

Same server, same SQL interface, different storage architectures.

This is one reason I keep talking about MariaDB’s pluggable storage engine architecture.

The best implementation is to have multiple first-class storage engines coexisting behind one SQL server!

What’s Next?

My first TideSQL article was really about discovering the engine. Now I want to test it differently. There are several things on my list.

I want to compare TideSQL and InnoDB on some workloads of my own, especially ingestion with secondary indexes and, of course, I also want to test replication and Galera.

TTL deserves more testing too, because automatic retention inside a write-heavy table is a very attractive feature for logs, metrics and event data.

But I think the biggest change since my first article is simply this:

But I think the biggest change since my first article is simply this:

TideSQL seems now ready to take the world of MariaDB by storm!

The project is moving quickly, the MariaDB integration is becoming easier to consume, and the feature set is already surprisingly large.

Does that mean you should convert all your InnoDB tables to TidesDB tomorrow? Of course not.

But should you install it, create a test table, throw a real workload at it and see what happens? Absolutely!

That’s what MariaDB’s storage engine architecture is there for.

Test it. Measure it. Find the workloads where it makes sense. And when something breaks, report it. That’s how new database technology gets better.

Enjoy MariaDB and TideSQL!