Partitions Performance with MySQL 5.1 and 5.5
Thursday, February 3, 2011
Labels:
MySQL,
MySQL performance
Check out this SlideShare Presentation:
Subscribe to:
Post Comments (Atom)
Posts
Comments
1 comments:
On slide 45, four reasons are noted for partitions:
1. If you have large tables
2. If you know that you will always query for the partitioning column
3. If you have historical tables that you want to purge quickly
4. If your indexes are larger than available RAM
Before jumping to partitions for the above cases, which require planning, scripts, and maintenance, users should also evaluate whether they have the right storage engine support in their MySQL stack.
For #1, there is a perception that smaller tables are faster. However, a collection of small tables that you have to swap in and out is not necessarily faster than a single table, parts of which you have to swap in and out. We (Tokutek) have written about this in http://tokutek.com/2011/01/partitioning-free-lunches-and-indexing/. We’ve also had customers go up to billions of rows http://tokutek.com/customers/a-social-networking-case-study/ and turn to TokuDB to manage performance at this level.
For #2, Knowing you always query the partitioning column is not a reason for partitions, it is a requirement/limitation. Partitioned tables require table scans on queries that do not contain the partitioning column. This is not true for some storage engines, such as Tokutek’s TokuDB. With TokuDB and proper indexes, this requirement disappears. Queries that contain the "partitioning column" can perform well, as well as other queries.
For #3, I can see the value, at least for an InnoDB implementation. That said, the new change buffer in InnoDB will also likely reduce the importance of this reason for partitions in InnoDB implementations. TokuDB, however, can be orders of magnitude faster than InnoDB for deletions, and this may be fast enough in many cases to eliminate need for partitions.
For #4 (also noted in slide 74), indexes without RAM may be limiting for typical B-Tree structures found in default MySQL storage engines (MyISAM, InnoDB, etc..).
However this is not true for all storage engines. For Tokutek’s TokuDB, secondary indexes that don’t fit in memory are not a problem, as index creation does not exact write penalties. In fact, maintaining the indexes you really want on large tables is a good methodology once the limitations of typical B Trees are overcome. So it’s not really indexing that requires RAM, it’s B-tree indexing. This is something we’ve talked about in a number of places, including http://tokutek.com/2010/04/tokudb-indexes-are-not-in-memory-and-not-hash-tables-either/
Post a Comment