3

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
) ENGINE=TokuDB AUTO_INCREMENT=20000000 DEFAULT CHARSET=uft8

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 Mar 3 '16 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 Mar 4 '16 at 20:39
3

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 Feb 28 '16 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. – tmcallaghan Feb 28 '16 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. – tmcallaghan Feb 28 '16 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 Feb 28 '16 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. – tmcallaghan Feb 29 '16 at 13:23
1

(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 Apr 11 '16 at 21:15
  • What is a sample query that has 'serious problems across partitions'? – Rick James Aug 11 '16 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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