I've done some reading on the percona website, most notably MySQL Partitioning: A Flow Chart where they recommend TokuDB as a solution to faster(er) deletes over innoDB rather than partitioning.

I've taken a sample table, and have added the same 20M rows to both tables. I'm then running tests by deleting ~ 200k rows on an otherwise quiet machine (my extra laptop).

I'm not seeing any differences in speed between deleting 200k rows on InnoDB and TokuDB - they're within a few % time-wise (ie. 45 seconds vs 43 seconds).

I installed Percona 5.6 along with the TokuDB engine on Ubuntu but did not otherwise do any configuration.

Can anyone suggest what I might be missing?

The table looks a bit like this:

CREATE TABLE `testTable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `filename` varchar(200) DEFAULT NULL,
    -- 20-ish columns
    PRIMARY KEY (`id`),
    KEY `filename` (`filename`),
    CLUSTERING KEY `clstr_key` (`filename`),
    -- 3 or 4 other indexes

I've tried doing deletes in several ways to gauge results, including:

DELETE FROM testTable WHERE id BETWEEN :start AND :end;
DELETE FROM testTable WHERE filename IN ('FilenameA', 'FilenameB', 'FilenameC');
-- Where dateCol is indexed
DELETE FROM testTable where dateCol BETWEEN :start AND :end;

Results were all pretty similar for both TokuDB and InnoDB across all methods.

  • Are those 3 DELETEs what you intend to do in real life? I have trouble imagining a database application where big deletes are the focus!
    – Rick James
    Commented Mar 3, 2016 at 20:12
  • @RickJames The problem itself is more complex and deals with tables that contain billions of rows that need to have information removed daily, as things stand currently.
    – Erik
    Commented Mar 4, 2016 at 20:39

2 Answers 2


If your table has 1 or more secondary indexes than each delete requires a read on the primary key index to get the fields necessary for the deletes in each secondary, which eliminates much of the TokuDB advantage over InnoDB. Also, if your delete pattern is "left-most" or you are performing a large number of deletes in a specific key range you will likely end up with quite a bit of garbage in your Fractal Tree indexes which will need to be cleaned up by optimizing the table/indexes.

  • I updated the original question with some example deletes
    – Erik
    Commented Feb 28, 2016 at 0:04
  • All 3 deletes you've listed will need to perform IO on the particular to get the keys required for deletion from other indexes. Since you've created a clustering index on column filename that one lookup gives you all the other keys you need for the other index deletions. Same goes for column id. The delete using column dateCol needs to do a lookup in that index to get id values, then another lookup on the primary key index prior to deleting. Commented Feb 28, 2016 at 12:53
  • Couple of thoughts: (1) you have two indexes on column filename, drop the one you don't want and (2) unless your dataset is huge or your storage is slow you won't see that much difference between TokuDB and InnoDB for your particular use-case. Commented Feb 28, 2016 at 12:54
  • The dataset is huge (100M+ rows) and the deletes are in the millions of rows at at a time. I can add/remove keys, if necessary
    – Erik
    Commented Feb 28, 2016 at 23:57
  • My last recommendation is to look at the IO of your server during the delete operations. If there isn't much IO (i.e., most of your data fits in the cache) or your IO is extremely fast then the TokuDB IO advantages might not be offering much. Commented Feb 29, 2016 at 13:23

(This answer relates to InnoDB, not TokuDB.)

If :start and :end are really "the oldest data", then we can discuss a sliding set of partitions. In such, you can delete, via DROP PARTITION, millions of rows 'instantly'. Further discussion.

Having more than about 50 partitions is harmful to performance. So, if you have hundreds of filenames, putting each in a separate partition is not a good idea.

If you don't need to delete 200K rows 'instantly', then let's talk about another approach: Walk through the table, deleting a thousand rows at a time. It won't interfere noticeably with other activity. It will probably take longer than your 43 seconds. It won't clog the undo/redo log like the sudden big delete. And it will take some code; here is a discussion of how to do it efficiently.

  • I really appreciate the answer, but I was evaluating Toku in order to get away from partitions - they can cause some serious query performance issues when you are often querying across partitions. One of the pages on Percona's site indicates deletes are much faster with Toku than InnoDB
    – Erik
    Commented Apr 11, 2016 at 21:15
  • What is a sample query that has 'serious problems across partitions'?
    – Rick James
    Commented Aug 11, 2016 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.