MySQL Conf 2008 – Applied Partitioning (Day 3)

The Applied Partitioning and Scaling Your Database System session by Phil Hildebrand appears to be a very popular session. The line outside the door was more of a crowd than a line. The talk was about the new partitioning abilities of MySQL 5.1.

The new table partitioning system has several benefits for your large datasets where you are having performance issues due to the volume of data. It can reduce the seek & scan set sizes and reduce the INSERT/UPDATE transaction durations. If you are already performing UNION queries or secondary queries to find data in an archive table, this functionality may be for you.

There are four (point 5) partitioning styles available for your use:

  1. Range
    • Very useful for situations where the data is accessed by date
    • Usually used when a limited number of primary partitions are needed
    • It supports sub-partitions (the .5 partition type)
  2. List
    • Good for when you are grouping data in partitions out of order (e.g., SELECT 1,5,7 in partition X)
    • As the name implies, you list which data goes into which partition
    • Higher maintenance costs in many situations
    • Supports sub-partitions
  3. Hash
    • Usually has a lower maintenance cost
    • Partitions the data based on the hash that you tell it
    • Good for when you have non-intelligent keys
    • Works with both a limited and a large number of Partitions
  4. Key
    • Similar to Hash partitioning, but based on one or more of the fields in the primary key instead of a user-defined hash
    • If there is no primary key, then the first unique index is used
    • Does not require the key to be integer based
  5. Subpartitions
    • As the name implies, a partition within a partition
    • You can only create subpartitions for Range & List
    • If needed, subpartitions can be spread across volumes

The alter table statements are fairly simplistic; “ALTER TABLE my_table PARTITION BY KEY() PARTITIONS 50” will create 50 partitions for that table.  If you need to add more partitions, you can just run “ALTER TABLE my_table ADD PARTITION PARTITIONS 25” and your table will now have 75 partitions.  Of course you can shrink the size of the partitions as well.  One of the nice features of the partition modification is that the data is re-allocated as appropriate.

All in all, this looks like a much better solution than the standard 10/90 tables.

Posted under Events, mysql

This post was written by Michael Tougeron on April 16, 2008

Tags: , , ,