OPS-14: Effective OpenEdge

1
OPS-14 Effective OpenEdge Database Configuration
Rob Marshall
Principal Solutions Consultant
2
Agenda

• Performance
• The Physical Layout
• Other Considerations

3
Performance

• What performance gains could I expect
•   By just moving to OpenEdge
•   By going to Type I Storage Areas
•   By going to Type II Storage Areas
• NOTE YMMV (Your Mileage May Vary)

4
Performance

• Real Customer Experience
• Manufacturing Industry
• Challenge was long running major processes
• Customer Statement process was taking over 25 min
  each (interactive process)
• Nightly MRP was taking over 4 hours

5
Real World Results
6
Real World Results
7
Real World Results

• Why so big of a difference?
• Is this typical of what I can expect?
• How fast can I get there?

8
Agenda

• Performance
• The Physical Layout
• Other Considerations

9
Storage Areas?

• No adverse application effects
• Physical reorg does NOT change the application
• Object location is abstracted from the language
  by an internal mapping layer
• Different physical deployments can run with the
  same compiled r-code

10
Determining the Layout

• What process is used to determine what the new
  layout should be?

11
Determining the Layout

• Every layout could be different
• Not every company uses the same application the
  same way or with the same options.

12
Determining the Layout

• Things to consider
• Is your application from a Partner or do you
  maintain it?
• If it is from a Partner
• Have you asked for their recommendations?
• Would they support you if you changed the layout?
• Do you have the capacity to re-org your database
• (you have the capability)

13
Determining the Layout

• To do a layout.
• Step 1 Run a dbanalys report of the full
  database (or on a copy of it if you have one)

14
Determining the Layout

• Step 1 The beginning.
• Run a dbanalys report of the full database (or on
  a copy of it if you have one)
• What we look for
• Large tables by record count
• Large tables by raw storage size
• Unused tables (no records)
• What is considered large?

15
Determining the Layout

• Step 2 Initial Separation.
• For each of the large tables, make a separate
  data area for the table and a separate index area
  for its indices. This will add 2 new storage
  areas FOR EACH large table.
• For the tables with no records, make a small
  storage area for the tables and a separate
  storage area for the indexes. This will add 2 new
  storage areas total.

16
Determining the Layout

• Step 3 Special tables.
• Every application has some of these
• Control Tables (e.g. country codes)
• High Create/Destroy activity (e.g. batch report
  queue)
• Make a separate storage area for all control
  tables and a separate area for their indexes
• Make a separate storage area for all high create
  / destroy tables and a separate area for their
  indexes

17
Determining the Layout

• Step 4 The rest.
• Group the remaining tables by mean record size
  into 32, 64, 128 and 256 record per block groups.
• Make a separate storage area for each grouping
  and a separate area for their indexes

18
Determining the Layout

• How to select the record per block and cluster
  setting?

19
Determining the Layout

• How to select the record per block, and why do I
  care?
• Incorrect settings waste recid pointers and can
  cause internal block fragmentation
• You have approx. 8000 bytes in a 8k db block and
  3900 bytes in a 4k db block

20
Determining the Layout

• RECORD BLOCK SUMMARY FOR AREA "Employee" 7
• -----------------------------------------------Rec
  ord Size (B)----Fragments------Scatter
• Table Records Size Min
  Max Mean Count Factor Factor
• PUB.Benefits 21 848.0B 39
  41 40 21 1.0 1.0
• PUB.Department 7 211.0B 26 35
  30 7 1.0 2.7
• PUB.Employee 55 6.2K 99 135
  115 55 1.0 0.8
• PUB.Family 72 3.1K 38
  51 44 72 1.0 1.1
• So, in a 4k db block we could put 100 Benefits
  records, in a 8k db block we could put 200.
  However, neither of these are allowed values
  WHAT DO WE DO?

21
Determining the Layout

• Do we choose based on Performance or Storage
  requirements?
• Choose the higher rpb setting for better
  performance
• Choose the lower rpb setting for better Storage
  needs

22
Determining the Layout

• How to select the cluster setting?
• Tables 64 or 512 block clusters
• Indexes 8 or 64 block clusters

23
Agenda

• General Migration Strategy
• The Fast and Easy Upgrade
• Other Considerations

24
Other Considerations

• The physical layout
• RAID considerations
• Separating files
• Fixed or Fixed/Floating Extents
•  
• Combining Databases ?

25
Other Considerations

• The Database rules of normalization and the
  impact on performance
•  
• Index considerations
• Many single component keys or fewer
  multi-component keys

26
Other Considerations

• Impact to startup parameters
• -B is number of db blocks
•  
• Impact to other environments
• Not just Production is impacted
• SQL
• Verify your SQL Width values using dbtool
• Dont forget to UPDATE STATISTICS

27
In Summary

• Huge Performance Gains Possible
• Can be done in Phases
• You can do this !

28
Relevant Exchange Sessions

• OPS-1 DBA 101 - How Healthy is Your Database
  Today?
• OPS-8 Alerts, Alarms, Pages and Harbingers of
  Trouble
• OPS-10 Moving V8/V9 RDBMS to OpenEdge 10
• OPS-18 Data Management and Platforms Roadmap
• OPS-19 What is IPv6 and Why Should I Care
• OPS-20 Data Management and Platforms Info
  Exchange
• OPS-23 OpenEdge Performance Basics
• OPS-28 A New Spin on Some Old Latches

29
?
Questions
30
Thank You
31
(No Transcript)