# TokuDB engine for mysql as historian DB

**URL:** <https://forum.inductiveautomation.com/t/tokudb-engine-for-mysql-as-historian-db/7872>\
**Category:** Archived Topics\
**Created:** [January 18, 2014, 7:47pm UTC](https://forum.inductiveautomation.com/t/tokudb-engine-for-mysql-as-historian-db/7872 "2014-01-18T19:47:51Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [January 18, 2014, 7:47pm UTC](https://forum.inductiveautomation.com/t/tokudb-engine-for-mysql-as-historian-db/7872/1 "2014-01-18T19:47:51Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [January 19, 2014, 7:42am UTC](https://forum.inductiveautomation.com/t/tokudb-engine-for-mysql-as-historian-db/7872/2 "2014-01-19T07:42:55Z")

</div>

[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](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.

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [January 22, 2014, 3:46pm UTC](https://forum.inductiveautomation.com/t/tokudb-engine-for-mysql-as-historian-db/7872/3 "2014-01-22T15:46:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [January 23, 2014, 5:41am UTC](https://forum.inductiveautomation.com/t/tokudb-engine-for-mysql-as-historian-db/7872/4 "2014-01-23T05:41:59Z")

</div>

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

```auto
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.