0

MySQL Conference Liveblogging: Applied Partitioning And Scaling your (OLTP) Database System (Wednesday 11:55AM)


Posted by Artem Russakovskii on April 16th, 2008 in Databases

  • 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 and blogger. 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.

Tags: , , , , ,