1 / 38

DB-03: A Tour of the OpenEdge™ RDBMS Storage Architecture

DB-03: A Tour of the OpenEdge™ RDBMS Storage Architecture. Richard Banville Technical Fellow. What’s New: Type II Storage Areas. Block clustering Alleviates object fragmentation Improves I/O efficiency Concurrent Space Allocation Area space Allocation Object space allocation

sal
Download Presentation

DB-03: A Tour of the OpenEdge™ RDBMS Storage Architecture

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. DB-03:A Tour of the OpenEdge™ RDBMS Storage Architecture Richard Banville Technical Fellow

  2. What’s New:Type II Storage Areas • Block clustering • Alleviates object fragmentation • Improves I/O efficiency • Concurrent Space Allocation • Area space Allocation • Object space allocation • Improved object manipulation • Table scan without index • Fast object deletion DB-03: The OpenEdge™ RDBMS Storage Architecture

  3. Agenda • Physical Layout • Advantages • A Usage Example • Summary DB-03: The OpenEdge™ RDBMS Storage Architecture

  4. Type II Area Block Clusters • Block Cluster: • 8, 64, or 512 adjacent blocks • Configured in .st file • Applied via prostrct • Fixed size for area • Unit of space allocation for objects • Blocks within cluster are “non-social” DB-03: The OpenEdge™ RDBMS Storage Architecture

  5. Type II Area Objects Object are made up of one or more block clusters (Objects: tables, indexes, lobs, area control) DB-03: The OpenEdge™ RDBMS Storage Architecture

  6. Type II Area Objects Clusters chained together for fast access Allows table scan without an index Allows fast table delete (Objects: tables, indexes, LOBS, area control) DB-03: The OpenEdge™ RDBMS Storage Architecture

  7. What Else Is Different w/Type II • Area HWM increased a cluster at a time • More efficient block formatting • Concurrent Space Allocation • Database extend • MAX(64, cluster size) • Other • Reduced fragmentation and scatter • Allows more advanced tools to be designed DB-03: The OpenEdge™ RDBMS Storage Architecture

  8. Object Block (Type I Area):Space Allocation Chains Free Block Free Block Free Block Free Block Unique indexes Only Idxdel Block Idxdel Block Idxdel Block Mixed Object Block Mixed Rec Block Mixed Rec Block Mixed Rec Block Mixed Rec Block Record Free Chain Total blocks, HWM DB-03: The OpenEdge™ RDBMS Storage Architecture

  9. Object Block (Type II Area):Area Free Space Allocation Chains Area Object Block Free Block Free Block Free Block Free Block Free Cluster List (for Area) Area Control Object Total blocks, Cluster HWM DB-03: The OpenEdge™ RDBMS Storage Architecture

  10. Object Block (Type II Area):Space Allocation Chains Index Object Block Idxdel Block Idxdel Block Unique indexes Only Idxdel Block Total blocks, Cluster HWM Free Block Free Block Free Block Free Block Why should I care? Rec Free Block Rec Free Block Rec Free Block Table 1 Object Block Rec Free Block Total blocks, Cluster HWM Rec Free Block Rec Free Block Rec Free Block Table 2 Object Block Rec Free Block Total blocks, Cluster HWM DB-03: The OpenEdge™ RDBMS Storage Architecture

  11. This Is Important Stuff • Indexes • Index delete chain maintenance • Index blocks are clustered together • Mixed Areas • Vast improvements multi table or multi index areas • Won’t have index and record blocks intertwined • Reduced fragmentation and scatter • Fewer Dump and Loads! • More efficient I/O DB-03: The OpenEdge™ RDBMS Storage Architecture

  12. Record Space Allocation • Record Packing Factor • Records stored variable length • Space allocated from record block free chains • RPF regulates record block free chain content • Definitions • Records per Block: (Blocksize / mean rec size) • Create limit: rec block free space required for a created record’s expansion. Also the minimum rec fragment size. (75/150) • Toss limit: free space required for a rec block to remain on the record block free chain (150/300) DB-03: The OpenEdge™ RDBMS Storage Architecture

  13. Changing RPF • Records Per Block • Changed via .st file • Granularity • Value per area • Create/Toss limits • _proutil <db> C setTableTossLimit <table> value • Granularity • Values per area in Type I storage area • Values per object in Type II storage area DB-03: The OpenEdge™ RDBMS Storage Architecture

  14. RPF Suggestions • Change Create Limit if: • Fragmentation occurs due to record updates of newly created records • You expect to see 1 fragment but get 2 • Change Toss Limit if fragmentation occurs due to record updates of existing records • You expect 1 or 2 fragments but get 3 or 4 • Coordinate with RPB • (Blocksize / mean rec size) • Don’t change if you have no reason to DB-03: The OpenEdge™ RDBMS Storage Architecture

  15. Overall Type II Layout Area Data (Free & in use) Area Control Object Cluster Free List Object #1 Object #2 Object #3 DB-03: The OpenEdge™ RDBMS Storage Architecture

  16. Agenda • Physical Layout • Advantages • A Usage Example • Summary DB-03: The OpenEdge™ RDBMS Storage Architecture

  17. Other Advantages of Type II Storage Areas • Block level Check sum • Identifies corrupt blocks prior to data change • Allows for larger I/O in future • Storage • Rowids stored as 64 bits • Variable length block header size • Object information stored in block header • Allows for improved maintenance • Improves database repair operations DB-03: The OpenEdge™ RDBMS Storage Architecture

  18. Storage Management Advantages • Improvements through organization • Efficient block formatting • Cluster at a time • Fewer bi/ai notes written • Bottlenecks Resolved • Concurrent space allocation • Optimistic buffer and index locking protocols • Migration Path • Can use both Type I & Type II in same database DB-03: The OpenEdge™ RDBMS Storage Architecture

  19. Table Scan via B-tree Level 3 (Leaf) Root Level 1 Level 2 Records - Leaf entries contain pointer to record - Cursor maintains info or last key accessed DB-03: The OpenEdge™ RDBMS Storage Architecture

  20. Select * from Customer; 3rd Cluster 2nd Cluster 1st Cluster 4th Cluster - Cursor maintains info of last record accessed - I/O Sequential through cluster DB-03: The OpenEdge™ RDBMS Storage Architecture

  21. Fast Object Delete Area Data (Free & in use) Area Control Object Cluster Free List Object #1 Object #2 Object #3 DB-03: The OpenEdge™ RDBMS Storage Architecture

  22. Fast Object Delete Area Data (Free & in use) Area Control Object Cluster Free List Object #2 Object #3 DB-03: The OpenEdge™ RDBMS Storage Architecture

  23. Fast Object Delete Area Data (Free & in use) Area Control Object Cluster Free List Why should I care? Object #2 Object #3 DB-03: The OpenEdge™ RDBMS Storage Architecture

  24. OpenEdge 10 Temp tables • Released in 10.0b • Fast delete • Fast delete/create on empty • Enhanced in 10.0b02 • Avoid delete/create • Avoid I/O when formatting • Hybrid Type I & II Storage Area • Index Objects Type I • Other Objects Type II • 8 Block Clusters • Suggestions • “empty temp-table <name>” • -tmpbsize 1, -tmpbsize 8 • Better performance with increased –Bt DB-03: The OpenEdge™ RDBMS Storage Architecture

  25. Agenda • Physical Layout • Advantages • A Usage Example • Summary DB-03: The OpenEdge™ RDBMS Storage Architecture

  26. Best Practices for Use • Physical • Include Striping (RAID or do it yourself) • File extent Location • Schema • Separate index and table data • Multi table area for small, medium & large records • Records per block properly set for each area • Growth • Always have a variable length extent • Enable large files DB-03: The OpenEdge™ RDBMS Storage Architecture

  27. Location, Location, Location b /bi/exampleDB.b1 f 1024000 b /bi/exampleDB.b2 f 1024000 b /bi/exampleDB.b3 # d "Schema Area":6,64 /db/exampleDB.d1 # d “Customer Indexes":7,1;8 /db/exampleDB_7.d1 f 512000 d “Customer Indexes":7,1;8 /db/exampleDB_7.d2 # d “Customer Data":8,128;64 /db/exampleDB_8.d1 f 1024000 d “Customer Data":8,128;64 /db/exampleDB_8.d2 DB-03: The OpenEdge™ RDBMS Storage Architecture

  28. Cluster Size b /bi/exampleDB.b1 f 1024000 b /bi/exampleDB.b2 f 1024000 b /bi/exampleDB.b3 # d "Schema Area":6,64 /db/exampleDB.d1 # d “Customer Indexes":7,1;8 /db/exampleDB_7.d1 f 512000 d “Customer Indexes":7,1;8 /db/exampleDB_7.d2 # d “Customer Data":8,128;64 /db/exampleDB_8.d1 f 1024000 d “Customer Data":8,128;64 /db/exampleDB_8.d2 DB-03: The OpenEdge™ RDBMS Storage Architecture

  29. Records Per Block b /bi/exampleDB.b1 f 1024000 b /bi/exampleDB.b2 f 1024000 b /bi/exampleDB.b3 # d "Schema Area":6,64 /db/exampleDB.d1 # d “Customer Indexes":7,1;8 /db/exampleDB_7.d1 f 512000 d “Customer Indexes":7,1;8 /db/exampleDB_7.d2 # d “Customer Data":8,128;64 /db/exampleDB_8.d1 f 1024000 d “Customer Data":8,128;64 /db/exampleDB_8.d2 DB-03: The OpenEdge™ RDBMS Storage Architecture

  30. Multi Object Areas d “Large Record Indexes":9,1;8 /db/exampleDB_9.d1 f 512000 d "Large Record Indexes":9,1;8 /db/exampleDB_9.d2 # d “Large Record Tables":10,16;64 /db/exampleDB_10.d1 f 1024000 d “Large Record Tables":10,16;64 /db/exampleDB_10.d2 # d “Small Record Indexes":11,1;8 /db/exampleDB_11.d1 f 512000 d “Small Record Indexes":11,1;8 /db/exampleDB_11.d2 # d “Small Record Tables":12,256;64 /db/exampleDB_12.d1 f 1024000 d “Small Record Tables":12,256;64 /db/exampleDB_12.d2 DB-03: The OpenEdge™ RDBMS Storage Architecture

  31. Cluster Size:Fast Growing Tables d “Misc Indexes":13,1;64 /db/exampleDB_13.d1 f 512000 d “Misc Indexes":13,1;64 /db/exampleDB_13.d2 # d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d1 f 1024000 d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d2 f 1024000 d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d3 # a /ai/exampleDB.a1 f 51200 a /ai/exampleDB.a2 f 51200 a /ai/exampleDB.a3 f 51200 … DB-03: The OpenEdge™ RDBMS Storage Architecture

  32. Records Per Block:Fast Growing Tables d “Misc Indexes":13,1;64 /db/exampleDB_13.d1 f 512000 d “Misc Indexes":13,1;64 /db/exampleDB_13.d2 # d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d1 f 1024000 d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d2 f 1024000 d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d3 # a /ai/exampleDB.a1 f 51200 a /ai/exampleDB.a2 f 51200 a /ai/exampleDB.a3 f 51200 … DB-03: The OpenEdge™ RDBMS Storage Architecture

  33. AI File Location d “Misc Indexes":13,1;64 /db/exampleDB_13.d1 f 512000 d “Misc Indexes":13,1;64 /db/exampleDB_13.d2 # d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d1 f 1024000 d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d2 f 1024000 d “Fast Growing Tables":14,64;512 /db/exampleDB_14.d3 # a /ai/exampleDB.a1 f 51200 a /ai/exampleDB.a2 f 51200 a /ai/exampleDB.a3 f 51200 … DB-03: The OpenEdge™ RDBMS Storage Architecture

  34. In Summary • More efficient layout • Better Performance • Migration path • Foundation for the future DB-03: The OpenEdge™ RDBMS Storage Architecture

  35. Questions? DB-03: The OpenEdge™ RDBMS Storage Architecture

  36. Thank you for your time! DB-03: The OpenEdge™ RDBMS Storage Architecture

  37. DB-03: The OpenEdge™ RDBMS Storage Architecture

  38. OpenEdge 10 RDBMS Advanced Storage Architecture The following Progress courses cover related subject matter. Please visit: www.progress.com/education for course descriptions and relevant curriculum maps. • Database Administration DB-03: The OpenEdge™ RDBMS Storage Architecture

More Related