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.