Mysql – How to make TokuDB engine on Percona delete faster

MySQLperconatokudb

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.

Best Answer

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.

Related Question