How to make TokuDB engine on Percona delete faster

Question

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.

tmcallaghan · Answer 1 · 2016-02-26T20:48:45.107

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.

score 1 · Answer 2 · answered Mar 03 '16 at 20: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.

How to make TokuDB engine on Percona delete faster

2 Answers2