MySQL Partitioning: A Flow Chart

In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.

Here goes:

1. Spindle contention? In other words, are you partitioning in order to spread your query work load across many disks? I’ve yet to see a compelling technical case that RAIDing your disks doesn’t do this as well, with much less setup and maintenance.
2. Are deletions a problem? Are you partitioning because of issues arising from deletions (i.e., poor performance or index fragmentation)?
3. Is fragmentation a problem? In InnoDB, deletions cause fragmentation, whereas dropping a partition doesn’t. However, TokuDB doesn’t fragment, and it’s a lot easier to switch the storage engine once than it is to maintain partitions forever.
4. Do you really need instant block deletes? Dropping a partition is really fast. And InnoDB deletes can be really slow. But TokuDB deletions are very fast (not as fast as dropping a partition, but fast). Is the ability to drop a partition what matters, or is it good enough to do deletions super fast (though not instantaneously)? I’m pretty sure very few people rely on instant partition drop, but this type of use case (if it exists) would be a credible reason to use partitions.
5. Large-table performance problems? When tables (and especially indexes) get larger than memory, InnoDB fails in a variety of ways (e.g., in the way its insertion rate drops precipitously). But that doesn’t mean all storage engines do. TokuDB, for example, is designed specifically to perform superbly with out-of-core databases.
6. Slow queries? One failure mode for large tables is for queries to become slow. This can be addressed by adding covering indexes (or in the case of TokuDB either covering or clustering indexes).
7. Slow indexing? Adding more indexes can cause indexing performance for InnoDB to deteriorate badly. But TokuDB is fast at indexing.
8. You probably don’t need partitioning. I don’t know of any other use cases that argue for partitioning, but I’d love to hear about them if they exist.