TokuDB vs InnoDB in timeseries INSERT benchmark

TokuDBThis post is a continuation of my research of TokuDB’s  storage engine to understand if it is suitable for timeseries workloads.

While inserting LOAD DATA INFILE into an empty table shows great results for TokuDB, what’s more interesting is seeing some realistic workloads.

So this time let’s take a look at the INSERT benchmark.

What I am going to do is to insert data in 16 parallel threads into the table from the previous post:

The INSERTS are bulk inserts with sequentially increasing ts and with sensor_id from 1 to 1000.

While the inserts are not fully sequential, because the primary key is (sensor_id, ts), it is enough to have in memory workload, so I do not expect performance degradation when data exceeds memory. This will play in favor for InnoDB, as it is known that TokuDB performs worse in CPU-bound benchmarks.

The benchmark executes 1mln events, each event inserts 1000 records in bulk. That is when finished we should about about 1 bln records in the table.

So let’s see how InnoDB (compressed 8K vs not compressed) performs.
Throughput (more is better):

Response time (log 10 scale on the axe Y) (less is better):

So InnoDB comes with following numbers:

  • InnoDB no compression. Averages at 350 inserts/sec with response time 80-100ms per transaction. The final table size is 82GB
  • InnoDB 8K compression. Throughput is 130 inserts/sec, response time 250ms. Table size is 60GB

Now, we have a quite bad compression rate, because I used uniform distribution for values of data1-data5 columns, and uniform may not be good for compression. And actually in the real case I expect much more repeating values, so I am going to re-test with pareto (zipfian) distribution.

For TokuDB (tested tokudb_fast and tokudb_small formats)

Throughput (more is better):

Response time (log 10 scale on the axe Y) (less is better):

TokuDB observations:

  • After an initial warmup TokuDB shows quite inconsistent performance with both tokudb_fast and tokudb_small formats
  • For tokudb_fast, the throughput is topping at ~150 inserts/sec, and 95% response time ~160 ms. However there are periodical stalls when throughput drops almost to 0 and response time jump to 10 sec !!! per transaction.
  • For tokudb_small, the throughput even less stable jumping around 100 inserts/sec and response time starts from 300ms per transactions with stalls up to 30 sec per transaction

File sizes for TokuDB: tokudb_fast: 50GB, tokudb_small: 45GB. Again I correspond a bad compression rate to uniform distribution. If we switch to pareto, the file size for tokudb_fast is 21GB, and for tokudb_small is 13GB

If we zoom in to 900 sec timeframe we can see periodic behavior of TokuDB:

Now I consider these stalls in TokuDB as severe and I do not think I can recommend to use it in production under such workload conditions until the problem is fixed.

The scripts for the timeseries benchmark for sysbench v0.5 you can find there

Software versions, for InnoDB: Percona Server 5.6-RC3 , for TokuDB: mariadb-5.5.30-tokudb-7.0.4

UPDATE (5-Sep-2013):
By many requests I update the post with following information:
TokuDB throughput (tokudb_small row format) with Pareto distribution, for two cases:

  • 1. PRIMARY KEY (sensor_id,ts) (on graph: tokudb_small)
  • 2. PRIMARY KEY (ts,sensor_id), KEY (sensor_id,ts) (on graph: tokudb_small_key)

Throughput in this case:

We can see that top throughput for tokudb_small_key is less then for tokudb_small, but there is also less variance in throughput.

The my.cnf files.
For InnoDB

for TokuDB (pretty much defaults)

Share this post

Comments (19)

  • Peter Zaitsev Reply


    Wow… this looks like the Innodb fuzzy checkpoint nightmare graphs from few years back.
    I wonder when you share some select speeds with us as it is very important too 🙂

    September 5, 2013 at 3:11 pm
  • Vadim Tkachenko Reply

    So TokuDB engineers gave me some advise, that in this case instability in results is caused
    that inserts into table with PRIMARY KEY (sensor_id,ts) are not sequential.

    So recommendation is to have
    PRIMARY KEY (ts,sensor_id)
    KEY (sensor_id,ts)

    So inserts into PRIMARY KEY in this case are fully sequential.

    It actually help, with this schema I have much more stable results almost without drop in throughput.

    The drawback is obvious – we increase space on disk. in this case, with tokudb_small format,
    the KEY (sensor_id,ts) takes additional 2GB of space.

    I am working on SELECT queries, stay tuned.

    September 5, 2013 at 3:18 pm
  • Peter Zaitsev Reply

    Thanks Vadim,

    So we should treat it as limitation of TokuDB should not be used with non sequential primary key inserts when ?

    If “random” inserts cause such dips I would be very curious how updates behave. It is possible to use auto-increment primary keys to get PK sequential essentially for any tables but for some workloads you always need semi random updates.

    September 5, 2013 at 3:26 pm
  • Vadim Tkachenko Reply


    Yes, I think at this point it is a limitation of TokuDB that it requires sequential PK inserts.
    TokuDB is aware about this
    and hope they will find a solution.

    September 5, 2013 at 3:37 pm
  • Mark Callaghan Reply

    What happens if you add more columns to the secondary index to make it covering and avoid random disk reads on queries?

    September 5, 2013 at 3:53 pm
  • Tim Callaghan Reply


    I assume your hardware configuration is as it was in the prior blog. Can you provide the my.cnf files you used for the tests?

    September 5, 2013 at 7:42 pm
  • Andy Reply


    1) is this using SSD? if so the insertion rate of ~100 inserts/s seems extremely low. Why is that?

    2) At the beginning of the benchmark when all data fits in memory why isn’t the performance much higher? InnoDB should be able to manage much more than 400 inserts/s on an in-memory data set, no?

    3) After you changed TokuDB’s PK to (ts,sensor_id) what insert performance did you get?

    September 5, 2013 at 8:18 pm
  • Vadim Tkachenko Reply


    If we include dataN fields into covered index, obviously the size of covered index will grow.
    And as we need to access all data1-data5 and if we include all of them – the size of the index will equal to size of the primary key,
    that is space consumption will pretty much double.

    September 5, 2013 at 9:06 pm
  • Vadim Tkachenko Reply


    1) This is very fast PCI-e SSD card. Please note that each insert is insert of 1000 rows. so 100 inserts/sec is actually 100.000 rows/sec inserted.

    2) Again, this is 400.000 rows/sec

    3) I updated the post with the results for PRIMARY KEY (ts,sensor_id),
    KEY (sensor_id,ts)

    September 5, 2013 at 9:09 pm
  • Bradley C Kuszmaul Reply

    I don’t understand your concern about doubling space. You wrote that TokuDB is 20x smaller than uncompressed InnoDB, or 10x smaller than InnoDB8K. If giving up a factor of two of the space would speed things up, it might be worth it: the space would still be 10x (or 5x respectively) smaller than InnoDB.

    September 5, 2013 at 9:18 pm
  • Vadim Tkachenko Reply


    In this case, if we perform INSERTS, the compression ratio is not 20x anymore.

    InnoDB uncompressed: 82GB
    TokuDB_small: 13GB
    So compression ratio is 6.3x , with doubled space it will be only 3.15x.
    Which still kind of good, but not so much anymore.
    And all this comes as just plan overhead, as I don’t really need (ts,sensor_id) index.

    September 5, 2013 at 9:24 pm
  • René Cannaò Reply

    I am curious to see how NDB Cluster (1 node) performs with this workload. Although it needs much more RAM (even if with Disk Data) and disk space.

    September 6, 2013 at 7:22 am
  • Jonathan Levin Reply


    It seems to me as tokudb is much smaller, there is the option of comparing tokudb on SSD/PCI-E vs innodb(uncompressed) on SAS.
    So you might be able to say: “Yes, tokudb is slower and has more flushing issues, but because it compresses so well, better hardware may solve those issues”.
    In your opinion, is it worth testing that scenario?

    September 6, 2013 at 8:13 pm
  • Mark Callaghan Reply

    Some things that matter for flash:
    * how much must you buy (compression matters here)
    * how long will it last (doing less random writes or larger writes matters here)

    So on these issues TokuDB does better than InnoDB. Sometimes response time is the priority, other times TCO matters more.

    September 6, 2013 at 8:20 pm
  • Raghavendra Reply

    Regarding compression, TokuDB has pluggable compression engine
    feature (with lz4 added recently to it), whereas InnoDB has been
    at zlib all this while (even with 5.6 with all its compression
    improvements). So the compression ratio and speed can be impacted
    by this too.

    September 7, 2013 at 1:13 pm
  • Will Edwards Reply

    I’m following your posts carefully, Vadim, because I too am evaluating tokudb for not-quite-sequential time-series inserts.

    I curious, what happens if you PARTITION by sensor_id?

    September 9, 2013 at 8:35 am
  • John Zobolas Reply

    Vadim have you done any tests comparing query performance for InnoDB and TokuDB?
    I had some problem with the queries, i posted it here:

    September 9, 2013 at 11:12 am
  • Alexey Zilber Reply

    @Vadim, do you think you could rerun these tests using TokuDB 7.1.6 (or later). There was a major key related bug that was fixed. Although that one deals with deleted keysm

    May 5, 2014 at 7:16 pm
  • Vlad Reply

    This is a great post, but it doesn’t have the solution to this issue.

    TokuDB does NOT require sequential PK inserts for good performance!

    You can have good performance for random PK inserts, but you have to use the right type of INSERT command that takes advantage of TokuDB’s tombstone messaging to avoid unnecessary overhead.

    For Vadim’s test that uses the Primary KEY (sensor_id,ts), he needs to use the REPLACE INTO command or INSERT IGNORE command.

    See these articles for a further explanation:

    December 27, 2014 at 12:53 am

Leave a Reply