beer planet

• Phil Hilderbrand of thePlatform for Media, Inc presents
• classic partitioning
• old school – union in the archive tables
• auto partitioning and partition pruning
• great for data warehousing
• query performance improved
• maintenance is clearly improved
• design issues in applying partitioning to OLTP (On-Line Transaction Processing)
• often id driven access vs date driven access
• 1 big clients could be 80% of the whole database, so there's a difficulty selecting partitioning schemes
• partitioning is only supported starting from MySQL 5.1
• understanding the benefits
• reducing seek and scan set sizes
• improving inserts/updates durations
• making maintenance easier
• shows an EXPLAIN output for SELECTS on non-partitioned and partitioned tables. The results are significantly better for partitions
• OPTIMIZE TABLE on an unpartitioned table takes 1.14s
• ALTER TABLE REBUILD PARTITION p1; on a partitioned table takes 0.03s
• ALTER TABLE REBUILD PARTITION p1, p2, p3, p4, …, p10; takes 0.27s
• design consideration
• table sizes and predicted growth patterns – partition big tables and also partition in advance, if you predict quick growth
• access patterns – select what you want to partition by in a smart way, date, id, etc
• keys and indexes – there are a few restrictions, foreign keys are currently not supported
• availability requirements
• manageability considerations – choosing to partition by hash if there is a TON of data
• reuse / scope considerations – think ahead, think of the usage
• partitioning methods
• range partitioning
• data usually accessed by date
• limited number of primary partitions needed
• ordered intelligent keys
• support sub partitions
• list partitioning
• grouping data in partitions out of order (1,5,7 in partition x)
• limited number of primary partitions needed
• intelligent keys
• supports sub partitions
• hash partitioning
• low maintenance
• works with limited or large number of partitions
• non-intelligent keys (can work in some cases with intelligent keys)
• key partitioning
• non-integer based partitioned keys (MySQL converts to int for you)
• low maintenance
• hash partitioning example
• hash(mod%num_partitions)
• in this example, Phil has stores, employees, and inventory. He decided to partition by store.
• http://dev.mysql.com/doc/refman/5.1/en/partitioning-management-hash-key.html
• 50 stores
• ALTER TABLE my_store PARTITION BY HASH(id) PARTITIONS 50;
• ALTER TABLE my_employee PARTITION BY HASH(store_id) PARTITIONS 50;
• ALTER TABLE my_inventory PARTITION BY HASH(store_id) PARTITIONS 50;
• ALTER obviously takes a long time and blocks (grr)
• adding partitions
• ALTER TABLE my_store ADD PARTITION PARTITIONS 2;
• ALTER TABLE my_employee ADD PARTITION PARTITIONS 2;
• ALTER TABLE my_inventory ADD PARTITION PARTITIONS 2;
• ALTER takes some time again, though less (how come if the partitions are empty?)
• SELECT table_name, partition_name, table_rows FROM information_schema.partitions … shows info on partitions
• remove 4 partitions
• ALTER TABLE my_store COALESCE PARTITION 4;
• ALTER TABLE my_employee COALESCE PARTITION 4;
• ALTER TABLE my_inventory COALESCE PARTITION 4;

Artem Russakovskii is a San Francisco programmer, blogger, and future millionaire (that last part is in the works). Follow Artem on Twitter (@ArtemR) or subscribe to the RSS feed.

In the meantime, if you found this article useful, feel free to buy me a cup of coffee below.