1 / 28

A first look at table partitioning

A first look at table partitioning. PUG Challenge Americas. Richard Banville & Havard Danielsen OpenEdge Development June 9, 2014. The Steps To Table Partitioning. Why are you partitioning? Identify tables Partitioning Strategy See white paper Preparation Type II Storage Areas

malaya
Download Presentation

A first look at table partitioning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A first look at table partitioning PUG Challenge Americas Richard Banville & Havard Danielsen OpenEdge Development June 9, 2014

  2. The Steps To Table Partitioning • Why are you partitioning? • Identify tables • Partitioning Strategy • See white paper • Preparation • Type II Storage Areas • Recid/rowid usage • Aligned field assignments • Partition aligned (local) indexes • Migration / adoption strategy • Dump / Load • “In place” migration • Current backup • Enable partitioning • Database • Tables / indexes / lobs • Split out existing data • Truncate / de-allocate “initial” partition

  3. Disclaimer • This presentation is for informational purposes only, and the reader is hereby cautioned that actual product development may vary significantly from it • This presentation may not be interpreted as any commitment on behalf of Progress, and future development, timing and release of any features or functionality described in this presentation remains at our sole discretion

  4. Why are you partitioning? Advantages Disadvantages Partition alignment & lookup (insert / delete) Update of partition aligned key values Missing aligned columns in where clause or record creation Getting it right the first time Knowledge of application table definition & physical layout Repartitioning costs More complex deployment • Performance impact • Partition elimination for queries (“pruning”) • Improved concurrency • For random activity • Availability • Maintenance advantages • Purge, Archive • Repair, rebuild, recover • Partition level tuning

  5. Identify tables & Partitioning strategy • “Stable” data logically grouped by • Chronological events (range partitions) • Discrete list of values (list partitions) • Sub-partition partitioning • Partitioning the same table according to the values of more than one column • Table / index maintenance too high • Operational time • Data availability • See white paper: //community.progress.com/technicalusers/f/18/t/9294.aspx

  6. Identify tables & Partitioning strategy Sub-partitioning Order Table • Strategy first! • 9 partition example • Sub-partition by region &order-date w/in region • {List, Range} 12/31/2013 12/31/2012 12/31/2014 12/31/2012 Western Region 12/31/2014 12/31/2013 Northern Region 12/31/2016 12/31/2012 12/31/2014 Southern Region • Only last partitioned column may be a range partition • Range partition can be any “indexable” field type • List / ranges must be inclusive, not necessarily symmetrical • Storage considerations for new partitions

  7. Preparation • Type II Storage Areas • All associated data (Table, indexes, lobs) • Storage considerations • Aligned field assignment • Columns in partition definition cannot be UNKNOWN • Avoid multiple assign statements • Otherwise, use a partition defined default • # Storage areas • Extent location • Cluster size • Records per block • Toss limits • Buffer pool assignment

  8. Preparation • Recid / Rowid usage • Recids will NOT work • Rowids can change at runtime • Browser’s result sets updated internally • Rowid generation • USING OpenEdge.DataAdmin.Util.*. • RowidGenerator: TableStart(table, min, max, pid) • myROWID = RowidGenerator: GetNextRowid() • Where’s my row? • BUFFER-PARTITION-ID:getByHdl(handle) • BUFFER-PARTITION-ID:getByRowid(rowid) • BUFFER-PARTITION-ID(buffer)

  9. Where’s my row? Find first order NO-LOCK. Find _File where _File-name = “Order” NO-LOCK. Find _StorageObject where _Object-Number = _File-num AND _PartitionId = BUFFER-PARTITION-ID(Order) AND _Object-Type = 1 NO-LOCK. Find _Area of _StorageObject NO-LOCK. Find _Partition-policy-detail where _Partition-policy-detail._Partition-id = BUFFER-PARTITION-ID(Order) AND _Partition-policy-detail._Object-Number = _File-num NO-LOCK. Display _Area-name _File-name _Partition-name.

  10. Preparation - Index Support 9 B-trees supporting 3 index definitions Composite Partitioning Idx #1 Idx #2 Idx #1 Idx #2 Idx #3 Order Table 12/31/2014 12/31/2013 Western Region • 4 partition example • Sub-partition by region &order-date w/in region 12/31/2014 Northern Region 12/31/2013 Local indexes on order-date, name Local indexes on order-date, S-rep Local indexes on order-date, name Local indexes on order-date, S-rep Global indexes span partitionsIndexed field need not be partition aligned 12/31/2016 12/31/2014 Southern Region

  11. Migration/adoption strategy – New tables (or Dump / Load) Sub-partitioning Order Table 12/31/2013 12/31/2012 12/31/2014 12/31/2012 • 9 partition example • Sub-partition by region &order-date w/in region Western Region 12/31/2014 12/31/2013 Northern Region 12/31/2016 12/31/2012 12/31/2014 Southern Region Dump data* Delete table* Add table Define / Add partitions Load data Have a nice day

  12. Migration/adoption strategy – In place migration Composite Partitioning Order Table 06/30/2014 Western Region • Single composite partition example • Sub-partition by region &order-date w/in region Northern Region 06/30/2014 06/30/2014 Southern Region • Implemented for migration only • Tool creates list of unique entries • Can modify range of given list Partition 0 Data logically segregated

  13. Migration/adoption strategy – In place migration Composite Partitioning Order Table 06/30/2014 Western Region • Single composite partition example • Sub-partition by region &order-date w/in region Northern Region 06/30/2014 06/30/2014 Southern Region • Implemented for migration only • Tool creates list of unique entries • Can modify range of given list Partition 0 Data logically segregated

  14. Migration/adoption strategy – In place migration Composite Partitioning Order Table 12/31/2014 06/30/2014 Western Region • 4 partition example • Sub-partition by region &order-date w/in region 12/31/2014 Northern Region 06/30/2014 12/31/2014 06/30/2014 Southern Region • Implemented for migration only • Tool creates list of unique entries • Can modify range of given list Partition 0 Data logically segregated Partitions 1-3 Data physically segregated

  15. Migration/adoption strategy – In place migration Composite Partitioning Order Table 12/31/2014 06/30/2014 Western Region • 4 partition example • Sub-partition by region &order-date w/in region 12/31/2014 Northern Region 06/30/2014 Composite Index #10Partition #0 Local Index #10Partition #3 Local Index #10Partition #1 Local Index #10Partition #2 10 10 10 10 12/31/2014 06/30/2014 Southern Region • Implemented for migration only • Tool creates list of unique entries • Can modify range of given list

  16. Enable partitioning – Database level 5) Have a good backup! proutil<db> -C enabletablepartitioning • _Partition-Policy-Detail (-353) • Defines each individual partition • _Partition-Policy (-352) • Describes partition at the “table” level

  17. Enable partitioning – Table and indexes Migration • Single composite partition • Composite partition aligned “local” index • Sub-partition by region &order-date w/in region Order Table Western Region 06/30/2014 06/30/2014 Northern Region Composite Index #10Partition #0 06/30/2014 10 Southern Region • Storage considerations for new partitions

  18. Enable partitioning – Table and indexes Migration • Composite partition • 3 newly added partitions • Partition aligned indexed • Sub-partition by region &order-date w/in region Order Table Western Region 12/31/2014 06/30/2014 06/30/2014 Northern Region 12/31/2014 Composite Index #10Partition #0 Local Index #10Partition #1 Local Index #10Partition #2 Local Index #10Partition #3 06/30/2014 10 10 10 10 12/31/2014 Southern Region • Storage considerations for new partitions

  19. Split out existing data (optional) • Identify created partition as a “split target” • Move data into target partitions • Transactional scoping by groups of record/index operations • Data for same partition definition spans physical partitions • Only ever one copy of the data • Recovery of operation restarts where it left off • Online operation with full access to non-split data • New split transitional state for partitions • Multiple concurrent operations allowed • OpenEdge Replication fully supported proutil<db> -C partitionmanage split table <name>partition <name> | composite "initial“useindex<name> recs<number>

  20. Split out existing data Migration • Create new partitions • Mark as split target” via OpenEdge Management • Composite partition • 3 newly added partitions • Partition aligned indexed • Sub-partition by region &order-date w/in region Order Table Western Region 12/31/2014 06/30/2014 06/30/2014 Northern Region 12/31/2014 Composite Index #10Partition #0 Local Index #10Partition #1 Local Index #10Partition #2 Local Index #10Partition #3 06/30/2014 10 10 10 10 12/31/2014 Southern Region

  21. Split out existing data Migration • Create new partitions • Mark as split target” via OpenEdge Management • 3 newly added “split target partitions • New partition aligned indexes Order Table Western Region 12/31/2014 12/31/2013 06/30/2014 06/30/2014 Northern Region 12/31/2014 12/31/2013 Composite Index #10Partition #0 Local Index #10Partition #4 Local Index #10Partition #6 Local Index #10Partition #3 Local Index #10Partition #5 Local Index #10Partition #1 Local Index #10Partition #2 06/30/2014 10 10 10 10 10 10 10 12/31/2014 12/31/2013 Southern Region • Data in this range now has restricted access

  22. Split out existing data Migration proutil<db> -C partitionmanage split table ordercomposite "initial“ recs1000 Order Table Western Region 12/31/2014 12/31/2013 06/30/2014 06/30/2014 Northern Region 12/31/2014 12/31/2013 Composite Index #10Partition #0 Local Index #10Partition #6 Local Index #10Partition #2 Local Index #10Partition #1 Local Index #10Partition #4 Local Index #10Partition #5 Local Index #10Partition #3 06/30/2014 10 10 10 10 10 10 10 12/31/2014 12/31/2013 Southern Region

  23. Split out existing data Migration proutil<db> -C partitionmanage split table ordercomposite "initial“ recs1000 Order Table 06/30/2014 12/31/2013 12/31/2014 06/30/2014 Western Region 12/31/2013 06/30/2014 12/31/2014 06/30/2014 Northern Region 06/30/2014 12/31/2013 12/31/2014 06/30/2014 Southern Region • Split target

  24. Truncate / de-allocate “initial” partition Reclaiming Free Space proutil<db> -C partitionmanage truncate partition <pname> table <tname>recs<#recs per txn> [ deallocate ] 06/30/2014 12/31/2013 12/31/2014 Order Table 06/30/2014 12/31/2013 12/31/2014 Western Region 06/30/2014 12/31/2013 12/31/2014 Northern Region Southern Region

  25. Truncate / de-allocate “initial” partition proutil<db> -C partitionmanage truncate partition <pname> table <tname>recs<#recs per txn> [ deallocate ] 06/30/2014 12/31/2013 12/31/2014 Order Table 06/30/2014 12/31/2013 12/31/2014 Western Region 06/30/2014 12/31/2013 12/31/2014 Northern Region Southern Region

  26. Truncate / de-allocate “initial” partition Reclaiming Free Space 06/30/2014 12/31/2013 12/31/2014 Order Table 06/30/2014 12/31/2013 12/31/2014 Western Region 06/30/2014 12/31/2013 12/31/2014 Northern Region Southern Region

  27. It’s Demo Time

More Related