After testing the latest Storage Engine in the MariaDB family, DuckDB, I wanted to test another storage engine about which I had only heard good things: TideSQL.
TideSQL is the name of the pluggable storage engine for MariaDB Server powered by TidesDB.
TidesDB is an LSM-tree-based storage engine, and TideSQL makes it available directly inside MariaDB using the usual pluggable storage engine architecture.
An LSM-tree-based storage engine, or Log-Structured Merge-tree engine, is designed to absorb writes quickly by first writing data sequentially in memory and logging it to files, then flushing sorted, immutable files to disk. Those files are later merged and cleaned up in the background during compaction.
This architecture is especially well-suited to write-heavy workloads such as logs, metrics, events, and time-series data, where fast ingestion and efficient compression are more important than constantly updating random pages on disk.
In other words, we can create a table using:
ENGINE=TIDESDB
and then use it with standard SQL.
This is exactly the kind of feature I like to test. It is new, it is moving fast, and it gives us another good reason to play with MariaDB’s pluggable architecture.
This is not (yet) a production recommendation. It is a first look, a test, and some notes from my playground.
Let’s have a look.
The Goal
In this post, I want to test:
- how to build and enable the TidesDB storage engine
- how to create a TidesDB table
- how to load some time-series-like data
- how to query it
- how to check the internal status of the engine
- how TTL and partitioning can help with time-series data
- how backup works
- some tips, hacks, and limitations
Getting Ready
For this test, I used the main branch of MariaDB Server on GitHub and TideSQL’s repository on GitHub.
But before we can compile the TideSQL Storage Engine, we also need to build and install tidesdb-lib on our system.
$ git clone --depth 1 https://github.com/tidesdb/tidesdb.git tidesdb-lib
$ cd tidesdb-lib/
$ mkdir build && cd build
$ cmake ..
$ make -j 4
$ sudo make install
$ sudo ldconfigThis will provide the library we need:
/usr/local/lib64/libtidesdb.so.9.3.7Now that the library is present, we can compile MariaDB Server, and first, we need to copy or link tidesdb folder from the tidesql repository into the MariaDB Server source storage folder.
To compile, just refer to this blog post.
TidesDB Storage Engine
Installation
After being compiled successfully, we can see the storage engine plugin:
-rwxr-xr-x 1 fred fred 3.7M Jun 15 14:39 storage/tidesdb/ha_tidesdb.soAnd we can install it from MariaDB’s client:
MariaDB [(none)]> INSTALL SONAME 'ha_tidesdb.so';
MariaDB [(none)]> SHOW plugins;
+-------------------------------+----------+--------------------+---------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------------+---------+
...
| TidesDB | ACTIVE | STORAGE ENGINE | ha_tidesdb.so | GPL |
+-------------------------------+----------+--------------------+---------------+---------+
78 rows in set (0.003 sec)
MariaDB [(none)]> SELECT * FROM mysql.plugin;
+---------+---------------+
| name | dl |
+---------+---------------+
| TidesDB | ha_tidesdb.so |
+---------+---------------+
1 row in set (0.001 sec)
MariaDB [information_Schema]> SELECT * FROM PLUGINS WHERE
PLUGIN_NAME='TidesDB'\G
*************************** 1. row ***************************
PLUGIN_NAME: TidesDB
PLUGIN_VERSION: 1029.6
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 130100.0
PLUGIN_LIBRARY: ha_tidesdb.so
PLUGIN_LIBRARY_VERSION: 1.15
PLUGIN_AUTHOR: TidesDB
PLUGIN_DESCRIPTION: LSM-tree engine with ACID transactions, MVCC concurrency, secondary/spatial/full-text/vector indexes, and encryption
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
PLUGIN_MATURITY: Gamma
PLUGIN_AUTH_VERSION: 4.5.6
1 row in set (0.002 sec)After the plugin is installed, new variables and status variables are available. A large number of them!
System Variables
MariaDB [tides_test]> show global variables like 'tidesdb%';
+------------------------------------------------+-----------------+
| Variable_name | Value |
+------------------------------------------------+-----------------+
| tidesdb_backpressure_wait_timeout_ms | 60000 |
| tidesdb_backup_dir | |
| tidesdb_block_cache_size | 268435456 |
| tidesdb_checkpoint_dir | |
| tidesdb_compact_after_range_delete_min_rows | 0 |
| tidesdb_compaction_threads | 4 |
| tidesdb_data_home_dir | |
| tidesdb_default_block_index_prefix_len | 16 |
| tidesdb_default_block_indexes | ON |
| tidesdb_default_bloom_filter | ON |
| tidesdb_default_bloom_fpr | 100 |
| tidesdb_default_compression | LZ4 |
| tidesdb_default_dividing_level_offset | 1 |
| tidesdb_default_index_sample_ratio | 1 |
| tidesdb_default_isolation_level | REPEATABLE_READ |
| tidesdb_default_klog_value_threshold | 512 |
| tidesdb_default_l0_queue_stall_threshold | 10 |
| tidesdb_default_l1_file_count_trigger | 4 |
| tidesdb_default_level_size_ratio | 10 |
| tidesdb_default_min_disk_space | 104857600 |
| tidesdb_default_min_levels | 1 |
| tidesdb_default_object_lazy_compaction | OFF |
| tidesdb_default_object_prefetch_compaction | ON |
| tidesdb_default_skip_list_max_level | 12 |
| tidesdb_default_skip_list_probability | 25 |
| tidesdb_default_sync_interval_us | 128000 |
| tidesdb_default_sync_mode | FULL |
| tidesdb_default_tombstone_density_min_entries | 1024 |
| tidesdb_default_tombstone_density_trigger | 0 |
| tidesdb_default_use_btree | OFF |
| tidesdb_default_write_buffer_size | 67108864 |
| tidesdb_finish_compactions_on_close | OFF |
| tidesdb_flush_threads | 4 |
| tidesdb_ft_stopword_table | |
| tidesdb_fts_blend_chars | |
| tidesdb_fts_bm25_b | 0.750000 |
| tidesdb_fts_bm25_k1 | 1.200000 |
| tidesdb_fts_max_word_len | 84 |
| tidesdb_fts_min_word_len | 3 |
| tidesdb_lock_wait_timeout_ms | 50000 |
| tidesdb_log_level | DEBUG |
| tidesdb_log_to_file | ON |
| tidesdb_log_truncation_at | 25165824 |
| tidesdb_max_concurrent_flushes | 0 |
| tidesdb_max_memory_usage | 0 |
| tidesdb_max_open_sstables | 256 |
| tidesdb_object_store_backend | LOCAL |
| tidesdb_objstore_cache_on_read | ON |
| tidesdb_objstore_cache_on_write | ON |
| tidesdb_objstore_local_cache_max | 0 |
| tidesdb_objstore_max_concurrent_downloads | 0 |
| tidesdb_objstore_max_concurrent_uploads | 0 |
| tidesdb_objstore_multipart_part_size | 0 |
| tidesdb_objstore_multipart_threshold | 0 |
| tidesdb_objstore_replica_replay_wal | ON |
| tidesdb_objstore_replicate_wal | ON |
| tidesdb_objstore_sync_manifest_to_object | ON |
| tidesdb_objstore_wal_sync_on_commit | OFF |
| tidesdb_objstore_wal_sync_threshold | 1048576 |
| tidesdb_objstore_wal_upload_sync | OFF |
| tidesdb_pessimistic_locking | ON |
| tidesdb_print_all_conflicts | OFF |
| tidesdb_promote_primary | OFF |
| tidesdb_replica_mode | OFF |
| tidesdb_replica_sync_interval | 5000000 |
| tidesdb_s3_access_key | |
| tidesdb_s3_bucket | |
| tidesdb_s3_endpoint | |
| tidesdb_s3_multipart_part_size | 0 |
| tidesdb_s3_multipart_threshold | 0 |
| tidesdb_s3_path_style | OFF |
| tidesdb_s3_prefix | |
| tidesdb_s3_region | |
| tidesdb_s3_secret_key | |
| tidesdb_s3_tls_ca_path | |
| tidesdb_s3_tls_insecure_skip_verify | OFF |
| tidesdb_s3_use_ssl | ON |
| tidesdb_single_delete_primary | OFF |
| tidesdb_skip_unique_check | OFF |
| tidesdb_ttl | 0 |
| tidesdb_unified_memtable | ON |
| tidesdb_unified_memtable_skip_list_max_level | 0 |
| tidesdb_unified_memtable_skip_list_probability | 0.000000 |
| tidesdb_unified_memtable_sync_interval | 128000 |
| tidesdb_unified_memtable_sync_mode | FULL |
| tidesdb_unified_memtable_write_buffer_size | 268435456 |
+------------------------------------------------+-----------------+
86 rows in set (0.001 sec)The session variables, the global ones (static or dynamic), and the session ones are documented here.
Status Variables
MariaDB [tides_test]> show global status like 'tides%';
+-----------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------+-------------+
| Tidesdb_backpressure_wait_us | 0 |
| Tidesdb_backpressure_waits | 0 |
| Tidesdb_cache_bytes | 0 |
| Tidesdb_cache_entries | 0 |
| Tidesdb_cache_hit_rate | 0.000000 |
| Tidesdb_cache_hits | 0 |
| Tidesdb_cache_misses | 0 |
| Tidesdb_cache_partitions | 64 |
| Tidesdb_column_families | 3 |
| Tidesdb_compaction_bytes_read | 0 |
| Tidesdb_compaction_bytes_written | 0 |
| Tidesdb_compaction_count | 0 |
| Tidesdb_compaction_queue | 0 |
| Tidesdb_data_size_bytes | 0 |
| Tidesdb_flush_bytes_written | 0 |
| Tidesdb_flush_count | 0 |
| Tidesdb_flush_pending | 0 |
| Tidesdb_flush_queue | 0 |
| Tidesdb_global_sequence | 100001 |
| Tidesdb_immutable_memtables | 0 |
| Tidesdb_last_uploaded_generation | 0 |
| Tidesdb_library_version | 9.3.7 |
| Tidesdb_local_cache_bytes | 0 |
| Tidesdb_local_cache_files | 0 |
| Tidesdb_lock_chain_max | 19 |
| Tidesdb_lock_deadlocks | 0 |
| Tidesdb_lock_entries | 1482 |
| Tidesdb_lock_entry_recycles | 100000 |
| Tidesdb_lock_held | 0 |
| Tidesdb_lock_timeouts | 0 |
| Tidesdb_lock_wait_us | 0 |
| Tidesdb_lock_waits | 0 |
| Tidesdb_max_sst_tombstone_density | 0.000000 |
| Tidesdb_max_sst_tombstone_density_level | 0 |
| Tidesdb_memory_limit | 24982772736 |
| Tidesdb_memory_pressure | 0 |
| Tidesdb_memtable_bytes | 13738666 |
| Tidesdb_object_store_enabled | 0 |
| Tidesdb_open_sstables | 0 |
| Tidesdb_replica_mode_active | 0 |
| Tidesdb_tombstone_ratio | 0.000000 |
| Tidesdb_total_sstables | 0 |
| Tidesdb_total_tombstones | 0 |
| Tidesdb_total_uploads | 0 |
| Tidesdb_txn_memory_bytes | 0 |
| Tidesdb_unified_immutable_count | 0 |
| Tidesdb_unified_is_flushing | 0 |
| Tidesdb_unified_memtable_bytes | 13738666 |
| Tidesdb_unified_memtable_enabled | 1 |
| Tidesdb_unified_wal_generation | 0 |
| Tidesdb_upload_failures | 0 |
| Tidesdb_upload_queue_depth | 0 |
| Tidesdb_user_bytes_written | 12538666 |
| Tidesdb_uwal_bytes_written | 19689136 |
| Tidesdb_version | 4.5.6 |
| Tidesdb_version_hex | 263430 |
| Tidesdb_wal_bytes_written | 0 |
+-----------------------------------------+-------------+
57 rows in set (0.002 sec)As you can see, when I mean a large number, it’s really a large number. We won’t go through all these. All the status variables are referenced in the manual.
Engine Status
The engine, like InnoDB, provides more information with the command SHOW ENGINE TIDESDB STATUS:
MariaDB [tides_test]> SHOW ENGINE TIDESDB STATUS\G
*************************** 1. row ***************************
Type: TIDESDB
Name:
Status: ================== TidesDB Engine Status ==================
Data directory: /run/media/fred/DATA/workspace/build-mariadb-relwithdebinfo/mysql-test/var/mysqld.1/tidesdb_data
Unified memtable: ON
Column families: 7
Global sequence: 100004
--- Memory ---
Total system memory: 31767 MB
Resolved memory limit: 23825 MB
Memory pressure level: 0
Total memtable bytes: 13738798
Transaction memory bytes: 0
--- Storage ---
Total SSTables: 0
Open SSTable handles: 0
Total data size: 0 bytes
Immutable memtables: 0
--- Background ---
Flush pending: 0
Flush queue size: 0
Compaction queue size: 0
--- Unified Memtable ---
Active bytes: 13738798
Immutable count: 0
Currently flushing: NO
WAL generation: 0
Next CF index: 7
--- Write Amplification ---
User bytes written: 12538786
Unified WAL bytes: 19689364
Per-CF WAL bytes: 0
Flush bytes written: 0 (0 sstables)
Compaction bytes written: 0 (0 sstables)
Compaction bytes read: 0
Total WA ratio: 1.57x
--- Block Cache ---
Enabled: YES
Entries: 0
Size: 0 bytes
Hits: 0
Misses: 0
Hit rate: 0.0%
Partitions: 64
--- Tombstones ---
Total tombstones: 0
Tombstone ratio: 0.00%
Worst SSTable density: 0.00% at level 0
1 row in set (0.001 sec)This command provides database-level statistics and block cache metrics. It also shows the path where the data is stored.
Let’s Go
Let’s create a small table with our newly installed engine:
MariaDB [(none)]> CREATE DATABASE tides_test;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> USE tides_test;
Database changed
MariaDB [tides_test]> CREATE TABLE events (
id BIGINT NOT NULL AUTO_INCREMENT,
ts DATETIME NOT NULL,
device_id INT NOT NULL,
metric VARCHAR(64) NOT NULL,
value DOUBLE NOT NULL,
payload JSON,
PRIMARY KEY (id),
KEY idx_ts (ts),
KEY idx_device_ts (device_id, ts)
) ENGINE=TIDESDB;
Query OK, 0 rows affected (0.040 sec)No special syntax is needed to start. The interesting part is that TideSQL maps the MariaDB table and its indexes to internal TidesDB structures. The table data has its own storage area, and each secondary index can have its own internal storage area, too.
As MariaDB users, we don’t need to manage those internal structures directly. We still create a normal SQL table, define indexes with KEY, and query it with SQL. TideSQL translates that into the internal layout used by TidesDB.
On disk, we can see the following structure:
tidesdb_data/
├── LOCK
├── LOG
├── tides_test__events
│ └── config.ini
├── tides_test__events__idx_idx_device_ts
│ └── config.ini
├── tides_test__events__idx_idx_ts
│ └── config.ini
├── UNIMAP
└── uwal_0.logJust by curiosity, we can see what one of those ini files contains:
[tides_test__events]
write_buffer_size = 67108864
level_size_ratio = 10
min_levels = 1
dividing_level_offset = 1
value_threshold = 512
compression_algorithm = LZ4
enable_bloom_filter = 1
bloom_fpr = 0.010000
enable_block_indexes = 1
index_sample_ratio = 1
block_index_prefix_len = 16
sync_mode = 1
sync_interval_us = 128000
skip_list_max_level = 12
skip_list_probability = 0.250000
default_isolation_level = 2
l1_file_count_trigger = 4
l0_queue_stall_threshold = 10
tombstone_density_trigger = 0.000000
tombstone_density_min_entries = 1024
min_disk_space = 104857600
use_btree = 0
object_lazy_compaction = 0
object_prefetch_compaction = 1
comparator_name =Now let’s add some data to our new table. I use a store procedure that you can find here.
MariaDB [tides_test]> CALL load_events(100000);
Query OK, 100000 rows affected (2 min 7.868 sec)Of course, this is not a benchmark; this is just to have some data to test TideSQL.
Querying the Data
Let’s check that all the rows are present:
MariaDB [tides_test]> SELECT COUNT(*) FROM events;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.043 sec)Now let’s try to execute a query using a time range:
MariaDB [tides_test]> SELECT device_id, metric, AVG(value), MIN(value), MAX(value)
FROM events WHERE ts >= NOW() - INTERVAL 1 HOUR
GROUP BY device_id, metric
ORDER BY device_id, metric LIMIT 10;
+-----------+-------------+--------------------+--------------------+--------------------+
| device_id | metric | AVG(value) | MIN(value) | MAX(value) |
+-----------+-------------+--------------------+--------------------+--------------------+
| 1 | voltage | 67.50772555126596 | 61.115345043237646 | 73.90010605929429 |
| 2 | voltage | 79.2549595043575 | 79.2549595043575 | 79.2549595043575 |
| 3 | voltage | 90.9454873678698 | 88.01454872639341 | 93.87642600934619 |
| 5 | voltage | 68.8251511834796 | 61.310632490842266 | 76.9280325406492 |
| 6 | voltage | 86.5267843813885 | 86.5267843813885 | 86.5267843813885 |
| 7 | voltage | 83.59980423338693 | 75.45915010893638 | 97.59113462417493 |
| 8 | temperature | 4.2814280877648185 | 4.2814280877648185 | 4.2814280877648185 |
| 8 | voltage | 79.24511831183463 | 63.28302841939314 | 95.20720820427611 |
| 9 | voltage | 72.2512163894728 | 64.83939864192102 | 79.66303413702458 |
| 10 | temperature | 15.221254681443101 | 10.448306063607621 | 19.99420329927858 |
+-----------+-------------+--------------------+--------------------+--------------------+
10 rows in set (0.007 sec)This is fast, let’s check if an index is used:
MariaDB [tides_test]> EXPLAIN SELECT device_id, metric, AVG(value), MIN(value), MAX(value)
FROM events WHERE ts >= NOW() - INTERVAL 1 HOUR
GROUP BY device_id, metric
ORDER BY device_id, metric LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: events
type: range
possible_keys: idx_ts
key: idx_ts
key_len: 5
ref: NULL
rows: 1
Extra: Using index condition; Using temporary; Using filesort
1 row in set (0.000 sec)We can see that we don’t perform a full table scan, and the idx_ts index is used.
More Info
We already saw that TidesDB provides useful information through SHOW ENGINE TIDESDB STATUS and through the status variables. But there is another option that provides engine-specific details for a table: ANALYZE TABLE:
MariaDB [tides_test]> ANALYZE TABLE events\G
*************************** 1. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: status
Msg_text: Engine-independent statistics collected
*************************** 2. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] CF 'tides_test__events' total_keys=0 data_size=0 bytes memtable=0 bytes levels=1 read_amp=1.00 cache_hit=0.0%
*************************** 3. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] avg_key=0.0 bytes avg_value=0.0 bytes
*************************** 4. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] level 1 sstables=0 size=0 bytes keys=0
*************************** 5. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] WA user=9339389 wal=0 flush=0 (0 ssts) compact_write=0 (0 ssts) compact_read=0 ratio=0.00x
*************************** 6. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] idx CF 'tides_test__events__idx_idx_ts' keys=0 data_size=0 bytes levels=1
*************************** 7. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] idx 'idx_ts' sampled=100000 distinct=59350 rec_per_key=1
*************************** 8. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] idx CF 'tides_test__events__idx_idx_device_ts' keys=0 data_size=0 bytes levels=1
*************************** 9. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Note
Msg_text: [TIDESDB] idx 'idx_device_ts' sampled=100000 distinct=99946 rec_per_key=1
*************************** 10. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: Warning
Msg_text: Engine-independent statistics are not collected for column 'payload'
*************************** 11. row ***************************
Table: tides_test.events
Op: analyze
Msg_type: status
Msg_text: OK
11 rows in set (9.727 sec)This is useful to understand:
- number of SSTables
- data size
- memtable usage
- read amplification
- cache hit rate
- write amplification
- compaction activity
For a write-optimized engine, watching compaction and write amplification is important.
An SSTable, or Sorted String Table, is one of the immutable files used by an LSM-tree storage engine.
When data is inserted, it is first written in memory, usually in a structure called a memtable. Later, that memory structure is flushed to disk as an SSTable.
The important characteristics are:
SSTable = Sorted String Table
sorted = keys are stored in order
immutable = once written, the file is not modified in place
So instead of constantly updating pages on disk as a traditional B-tree engine does, an LSM-tree engine writes new sorted files and later merges them during compaction.
As I point this out, this is important, let’s try to understand our output:
[TIDESDB] level 1 sstables=0 size=0 bytes keys=0So for the main column family, the SSTables are 0. Having a large number here would have meant that more files may need to be checked during reads. Compaction reduces that by merging SSTables and removing obsolete row versions.
[TIDESDB] WA user=9339389 wal=0 flush=0 (0 ssts) compact_write=0 (0 ssts) compact_read=0 ratio=0.00xMeaning:
user=9339389 bytes written by SQL/user workload
wal=0 bytes written to WAL
flush=0 (0 ssts) bytes flushed from memtable to SSTables
compact_write=0 bytes written by compaction
compact_read=0 bytes read by compaction
ratio=0.00x current write amplification ratioThe important part is this: our data is still in the memtable and hasn’t been flushed to SSTables yet.
That explains why we see:
total_keys=0
data_size=0 bytes
memtable=0 bytes
sstables=0
flush=0
compact_write=0But at the same time, the engine-independent index statistics show that MariaDB sampled rows:
idx 'idx_ts' sampled=100000 distinct=59350 rec_per_key=1
idx 'idx_device_ts' sampled=100000 distinct=99946 rec_per_key=1So MariaDB can see the table data, but TidesDB’s LSM-level stats do not yet show persisted SSTable data.
Forcing a Flush
But we can force the flush to disk using OPTIMIZE TABLE:
MariaDB [tides_test]> OPTIMIZE TABLE events;
+-------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| tides_test.events | optimize | status | OK |
+-------------------+----------+----------+----------+
1 row in set (0.232 sec)After that, the global status variables showed flush activity:
MariaDB [tides_test]> SHOW GLOBAL STATUS LIKE 'Tide%flu%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Tidesdb_flush_bytes_written | 0 |
| Tidesdb_flush_count | 0 |
| Tidesdb_flush_pending | 0 |
| Tidesdb_flush_queue | 0 |
| Tidesdb_unified_is_flushing | 0 |
+-----------------------------+-------+
5 rows in set (0.004 sec)Then ANALYZE TABLE showed the persisted TidesDB structures:
[TIDESDB] CF 'tides_test__events' total_keys=100000 data_size=3306137 bytes
[TIDESDB] level 1 sstables=1 size=3306137 bytes keys=100000So the main table data was flushed into a single SSTable.
The two secondary indexes were also stored in their own TidesDB column families:
[TIDESDB] idx CF 'tides_test__events__idx_idx_ts' keys=100000 data_size=1459968 bytes
[TIDESDB] idx CF 'tides_test__events__idx_idx_device_ts' keys=100000 data_size=1733112 bytesThis also explains the global flush count:
main table data 3306137 bytes
idx_ts 1459968 bytes
idx_device_ts 1733112 bytes
----------------------------------
total 6499217 bytesThat total matches:
Tidesdb_flush_bytes_written = 6499217So in this test, OPTIMIZE TABLE flushed three TidesDB structures: the table data and the two secondary indexes.
Write Amplification
ANALYZE TABLE also reports write amplification information:
[TIDESDB] WA user=9339389 wal=0 flush=3306137 (1 ssts) compact_write=0 (0 ssts) compact_read=0 ratio=0.35xIn this output:
user bytes written by the SQL workload
flush bytes written when memory data was flushed to SSTables
compact_write bytes written by compaction
compact_read bytes read by compaction
ratio write amplification ratioIn this test, there was no compaction yet:
compact_write=0
compact_read=0The ratio was:
3306137 / 9339389 = 0.35xThis means that, for the main column family, TidesDB wrote about 0.35 bytes to SSTables for every byte written by the user workload.
This number can vary widely depending on compression, the number of indexes, memtable size, compaction activity, and workload.
Time-series
TideSQL is also a strong candidate for time-series. With a time-series workload, the important part is usually to avoid random access patterns as much as possible.
That means the primary key and secondary indexes matter a lot.
Let’s create a new table that would be good for such a workload:
MariaDB [tides_test]> CREATE TABLE metrics (
device_id INT NOT NULL,
ts DATETIME(6) NOT NULL,
metric VARCHAR(32) NOT NULL,
value DOUBLE NOT NULL,
payload JSON,
PRIMARY KEY (device_id, ts, metric),
KEY idx_ts (ts)
) ENGINE=TIDESDB;
Query OK, 0 rows affected (0.036 sec)This table is a better fit for a time-series because most queries are usually:
- give me data for this device
- give me data between two timestamps
- aggregate data by time range
- remove old data
That’s why we design the primary key around the access pattern.
Table Options
TidesDB exposes several table options, such as compression:
MariaDB [tides_test]> ALTER TABLE metrics
ENGINE=TIDESDB COMPRESSION='ZSTD';
Query OK, 0 rows affected (0.201 sec)
Records: 0 Duplicates: 0 Warnings: 0The available compression options include:
- NONE
- SNAPPY
- LZ4
- ZSTD
- LZ4_FAST
For time-series data, compression can be very interesting because the data is often repetitive.
Another useful option is the sync mode.
The sync mode controls how aggressively the engine flushes writes to durable storage:
MariaDB [tides_test]> ALTER TABLE metrics ENGINE=TIDESDB
SYNC_MODE='INTERVAL' SYNC_INTERVAL_US=500000;
Query OK, 0 rows affected, 1 warning (0.123 sec)
Records: 0 Duplicates: 0 Warnings: 1Of course, this is a trade-off.
FULL is safer. NONE is faster but not something I would use for important data. INTERVAL can be a good middle ground for some test workloads.
SYNC_MODE needs to be interpreted carefully. When tidesdb_unified_memtable=ON, the table-level SYNC_MODE governs SSTable file sync behavior only. WAL durability is controlled by the global tidesdb_unified_memtable_sync_mode setting.
TTL: Very interesting for Time-Series
One feature that immediately caught my attention is TTL.
TTL means Time To Live.
In a database/storage-engine context, it means: how long a row or record should remain valid before it automatically expires.
For time-series data, retention is always part of the design.
We can, for example, modify our table for the rows to expire after a week:
MariaDB [tides_test]> ALTER TABLE metrics ENGINE=TIDESDB TTL=604800;
Query OK, 0 rows affected, 1 warning (0.134 sec)
Records: 0 Duplicates: 0 Warnings: 1I think TidesDB is very interesting for time-series workloads if we want to keep them in MariaDB.
Why?
Because time-series workloads are often:
- write-heavy
- append-heavy
- retention-based
- compression-friendly
- partition-friendly
- range-query oriented
And TidesDB gives us:
- LSM-tree storage
- compression
- TTL
- partitioning
- online backup
- useful internal status
- object-store mode
But TidesDB is not a specialized time-series database like Prometheus, InfluxDB, TimescaleDB, or QuestDB, it’s much more than that!
TidesDB does not magically provide downsampling, continuous aggregates, retention policies with automatic partition management, or time-series-specific query syntax.
But if you want to keep SQL, stay inside MariaDB, and use a write-optimized engine for event or metric ingestion, it is definitely worth testing.
My First Impression
I really like it.
Not because I would immediately replace InnoDB everywhere. I would not.
But because MariaDB’s pluggable storage engine architecture allows this kind of innovation.
TideSQL provides a write-optimized LSM-tree engine directly within MariaDB. It supports normal SQL, transactions, secondary indexes, TTL, partitioning, online backup, and even object-store mode (which I plan to cover in a future post).
That is already a lot.
For time-series-like workloads, event ingestion, logs, metrics, and retention-based tables, this is a very interesting area to explore.
And of course, the best part is that we can test it table by table:
CREATE TABLE my_events (...) ENGINE=TIDESDB;No need to move the whole database.
As usual: test, measure, break things, report bugs, and help the project improve.
Happy testing, and once again, enjoy MariaDB Server and TideSQL!