TokuDB engine for mysql as historian DB

Archived Topics
1

Does anybody tried TokuDB engine for mysql or mariaDB as an historian DB for ignition ?
http://www.tokutek.com/products/tokudb-for-mysql

With up to 20x or more improvement in insertions and indexing…
reduction in storage size …

1 Like
2

[quote=“mazeyrat”]Does anybody tried TokuDB engine for mysql or mariaDB as an historian DB for ignition ?
http://www.tokutek.com/products/tokudb-for-mysql

With up to 20x or more improvement in insertions and indexing…
reduction in storage size …[/quote]
This has been on my timeline for testing. Thanks for kicking me in the ass. I’m testing now. Post back later.

3

Alright, I spun up a vm, and loaded a set of data into the database with their default tables. The information is as follows

Source:
Number of Tables: 233
Size on disk as reported by “du -hs”: 175 GB
Size reported by “SHOW TABLE STATUS”: 160.56 GB
Number of Rows: 1.927 billion

4

Ok the results of converting the source database to tokudb using compression=‘tokudb_lzma’. You might want to sit down.
.
.
.
.
.
.
.
TokuDB after ALTER TABLE table ENGINE=TokuDB Compression='tokudb_lzma:
Number of Tables: 233
Size on disk as reported by “du -hs”: 18.791 GB
Size reported by “SHOW TABLE STATUS”: 133.56 GB (data size AND index size)
Number of Rows: 1.927 billion
Query time for the following query : 0.006s

SELECT t_stamp,floatvalue FROM `sqlt_data_1_20130829` WHERE tagid = 359 AND t_stamp BETWEEN 1377811527493 AND 1377966645327

I’m really impressed. 9.3x compression ratio. Teaming this with an SSD, this can really extend the amount of data Ignition should be able to store before hitting a performance wall. This will allow us to us larger partition sizes.

A few caveats.
TokuDB doesn’t support foreign keys, so if you are used to relying on foreign key restrictions and cascading, you shouldn’t use TokuDB for that use case.

The compression setting of tokudb_lzma is not recommended for systems with less than 4 cpus, as the indexes are compressed as well.

Ill post back more as I test further.

1 Like