MySQL Conference Liveblogging: Applied Partitioning And Scaling your (OLTP) Database System (Wednesday 11:55AM)
Posted by Artem Russakovskii on April 16th, 2008 in Databases
| Share |
- 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.
beer planet is a blog about technology, programming, computers, and geek life. It is run by Artem Russakovskii - a local San Francisco geek who currently works at 
Artem loves commenting on other people's blogs and reading comments on his own, so go ahead and leave one.
[...] Recent public urls tagged "partitioning" → MySQL Conference Liveblogging: Applied Partitioning And Scaling … [...]