A Tour of the OpenEdge RDBMS Storage Architecture

1
A Tour of the OpenEdge RDBMS Storage Architecture

• Laureano Leyva
• Servicios Profesionales
• lleyva_at_progress.com

2
Whats NewType 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

3
Agenda

• Physical Layout
• Advantages
• A Usage Example

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

5
Type II Area Objects
Object are made up of one or more block clusters
(Objects tables, indexes, lobs, area control)
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)
7
What else is different w/Type II

• Area HWM
• 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

8
Object Block (Type I Area)Space Allocation
Chains
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
9
Object Block (Type II Area)Area Free Space
Allocation Chains
Area Control Object
Total blocks, Cluster HWM
10
This is important stuff

• Indexes
• Index delete chain maintenance
• Index blocks are clustered together
• Mixed Areas
• Vast improvements multi table or multi index
  areas
• Wont have index and record blocks intertwined
• Reduced fragmentation and scatter
• Fewer Dump and Loads!
• More efficient I/O

11
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 records 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)

12
Changing RPF

• Records Per Block
• Changed via .st file
• Granularity
• Value per area
• Create/Toss limits
• _proutil ltdbgt C setTableTossLimit lttablegt value
• Granularity
• Values per area in Type I storage area
• Values per object in Type II storage area

13
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)
• Dont change if you have no reason to

14
Overall Type II layout
Area Data (Free in use)
Area Control Object
Cluster Free List
Object 1
Object 2
Object 3
15
Agenda

• Physical Layout
• Advantages
• A Usage Example

16
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

17
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

18
Table scan via B-tree
Root
Level 1
Level 2
Records
- Leaf entries contain pointer to record
- Cursor maintains info or last key accessed
19
Select from Customer
- Cursor maintains info of last record accessed
- I/O Sequential through cluster
20
Fast Object Delete
Area Data (Free in use)
Area Control Object
Cluster Free List
Object 1
Object 2
Object 3
21
Fast Object Delete
Area Data (Free in use)
Area Control Object
Cluster Free List
Object 2
Object 3
22
Fast Object Delete
Area Data (Free in use)
Area Control Object
Cluster Free List
Object 2
Object 3
23
OpenEdge 10 Temp tables

• Released in 10.0b
• Fast delete
• Fast delete/create on empty
• Hybrid Type I II Storage Area
• Index Objects Type I
• Other Objects Type II
• 8 Block Clusters
• Suggestions
• empty temp-table ltnamegt
• -tmpbsize 1, -tmpbsize 8
• Better performance with increased Bt

24
Agenda

• Physical Layout
• Advantages
• A Usage Exampl

25
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

26
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,18
/db/exampleDB_7.d1 f 512000 d Customer
Indexes"7,18 /db/exampleDB_7.d2 d Customer
Data"8,12864 /db/exampleDB_8.d1 f 1024000 d
Customer Data"8,12864 /db/exampleDB_8.d2
27
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,18
/db/exampleDB_7.d1 f 512000 d Customer
Indexes"7,18 /db/exampleDB_7.d2 d Customer
Data"8,12864 /db/exampleDB_8.d1 f 1024000 d
Customer Data"8,12864 /db/exampleDB_8.d2
28
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,18
/db/exampleDB_7.d1 f 512000 d Customer
Indexes"7,18 /db/exampleDB_7.d2 d Customer
Data"8,12864 /db/exampleDB_8.d1 f 1024000 d
Customer Data"8,12864 /db/exampleDB_8.d2
29
Multi Object Areas
d Large Record Indexes"9,18
/db/exampleDB_9.d1 f 512000 d "Large Record
Indexes"9,18 /db/exampleDB_9.d2 d Large
Record Tables"10,1664 /db/exampleDB_10.d1 f
1024000 d Large Record Tables"10,1664
/db/exampleDB_10.d2 d Small Record
Indexes"11,18 /db/exampleDB_11.d1 f
512000 d Small Record Indexes"11,18
/db/exampleDB_11.d2 d Small Record
Tables"12,25664 /db/exampleDB_12.d1 f
1024000 d Small Record Tables"12,25664
/db/exampleDB_12.d2
30
Cluster SizeFast Growing Tables
d Misc Indexes"13,164 /db/exampleDB_13.d1 f
512000 d Misc Indexes"13,164
/db/exampleDB_13.d2 d Fast Growing
Tables"14,64512 /db/exampleDB_14.d1 f
1024000 d Fast Growing Tables"14,64512
/db/exampleDB_14.d2 f 1024000 d Fast Growing
Tables"14,64512 /db/exampleDB_14.d3 a
/ai/exampleDB.a1 f 51200 a /ai/exampleDB.a2 f
51200 a /ai/exampleDB.a3 f 51200
31
Records Per BlockFast Growing Tables
d Misc Indexes"13,164 /db/exampleDB_13.d1 f
512000 d Misc Indexes"13,164
/db/exampleDB_13.d2 d Fast Growing
Tables"14,64512 /db/exampleDB_14.d1 f
1024000 d Fast Growing Tables"14,64512
/db/exampleDB_14.d2 f 1024000 d Fast Growing
Tables"14,64512 /db/exampleDB_14.d3 a
/ai/exampleDB.a1 f 51200 a /ai/exampleDB.a2 f
51200 a /ai/exampleDB.a3 f 51200
32
AI File Location
d Misc Indexes"13,164 /db/exampleDB_13.d1 f
512000 d Misc Indexes"13,164
/db/exampleDB_13.d2 d Fast Growing
Tables"14,64512 /db/exampleDB_14.d1 f
1024000 d Fast Growing Tables"14,64512
/db/exampleDB_14.d2 f 1024000 d Fast Growing
Tables"14,64512 /db/exampleDB_14.d3 a
/ai/exampleDB.a1 f 51200 a /ai/exampleDB.a2 f
51200 a /ai/exampleDB.a3 f 51200
33
In Summary

• More efficient layout
• Better Performance
• Migration path
• Foundation for the future

34
Preguntas?
35
Gracias por su tiempo!
36
(No Transcript)
37
OpenEdge 10 RDBMS Advanced Storage Architecture

• The following Progress courses cover related
  subject matter. Please visit www.progress.com/ed
  ucation for course descriptions and relevant
  curriculum maps.
• Database Administration