Partitioning in MySQL 5.1, Part 2

Read Partitioning In MySQL, Part 1 before reading this post.

Partition pruning is the most critical performance component about partitioning. Partitioning can ease management of your data (for example the ability to just backup or restore a single partition or the ability to drop a no longer needed partition) but the main reason people add partitions is for performance.

Pruning helps performance by only reading the partitions that need to be read for a query. For example, if you have a table range partitioned by year that has partitions for the years 2000 through 2010. If you write a query selecting data between 2002 and 2005, you don't want to have to scan all 10 partitions.

The optimizer needs to be smart enough to select and scan just those partitions that hold the data. This is called partition pruning. All of the databases that I have used that support partitioning (Oracle, Postgres, EnterpriseDB and DB2) support partition pruning. The better the optimizer, the less often the optimizer will erroneously scan partitions that aren't really required.

As I said above, partition management is important and this is one area that postgres (and enterprisedb) lacks. The management and support functions in MySQL are pretty robust. The way it will dynamically move data when a partition is dropped or added is pretty sweet. This is very advanced logic. I will need to play with it to see how robust and performant it is but in most databases, moving data like this requires manual intervention.

I also like the ability to convert a table to a partitioned table online. That is another feature that requires manual intervention in most databases.

The last slide I will cover from the presentation are the limitations. Right now there are several severe limitations.

Most of the limitations listed here are no big deal. All partitions must use the same storage engine (no biggie) and increased response time for partitioned tables with many partitions (expected). The time you save by partition pruning should outweigh the overhead by a large margin.

The things that bother me are the integer result requirement. I'm just not sure how I would implement range partitioning using a numeric. That really sounds like a list partition to me.

The lack of foreign keys in a partition is also a limitation of postgres and is a show stopper in many cases. If you can't ensure the integrity of your database, do you really need that data? I'm glad the fix for that is on the road map.

What do you think of partitioning in MySQL?

Partitioning in MySQL 5.1, Part 1

I was browsing around the MySQL web site tonight and ran across some free webinars (recordings). Seeing as how I once did a podcast on Oracle partitioning, one webinar that jumped out at me was Partitioning in MySQL 5.1 and onwards.

I didn't even know MySQL did partitioning. Here is a description of the webinar:

In this webinar we will provide further insight into MySQL partitioning, including:

• Introduction to MySQL Partitioning
• Linear Key Partitioning
• Partition Function
• Partition Options
• Information Schema for Partitions
• EXPLAIN PARTITIONS
• Partition Management
• Partition Pruning
• Partitioning and NULL Values
• Partitioning Implementation
• Partitioning for MySQL Cluster
• Future Roadmap for Partitioning
• Partitioning Limitations in 5.1

Sounds good. It's a good presentation but kind of slow. If you're like me and would prefer to move at your own speed, you can download the presentation itself.

For those of you even more impatient than me, I will point out a few highlights. The beginning of the presentation was about what partitioning is and why use it. I'll skip over those parts. If you are interested in those details, post me a note and I will do a blog entry on that topic.

One of the more important pieces of information is the types of partitioning supported.

Oracle supports all of these types of partitioning but some other very good databases do not. For example, PostgreSQL only supports range partitioning (and list becuase of that) but you would have to manually implement a hash partition and any composites are out of scope for now.

I am fairly impressed with this support. You can also mix and match engines (in future versions). That's pretty exciting.

A quick description of the types for those not familiar:

• Range Partition - Things like by year, by month, etc. Used very frequently in data warehousing. You might have a partition for each year. You can drop partitions (old years) and add new parititions (for future years). Range partitions tend to be dynamic (you'll be adding and dropping partitions over time).

• List Partition - A known list of partitioning values. You may partition by state, country, region etc. Each value gets its own partition. You can add partitions but a list tends to be fairly static.

• Hash Partition - A hashing algorithm creates a numeric value. You'll use this on large data sets that don't have a logical partitioning key. The idea is to give you the ability to partition on numeric data like a primary key.

• Composite Partition - A combination of the three partitioning types above.

The partition function is the function that determines which partition a particular row will reside in. The current implementation has some serious limitations but those can be worked around. Basically you write a function, say by comparing dates, and that determines which partitions will hold your record. I'm sure that I could live with two of the current limitations: always an integer value (how do you get that to work with dates?) and the fact that you can only write the function on your primary key.

I'll finish up my coverage of this presentation in part 2. All in all, I am quite impressed with the partitioning function in MySQL 5.1.

LewisC