Different Progress Database Structures

[LBaliao]

Hello.

I am hoping to learn different ways I can structure PROGRESS databases, its advantages and disadvantages. If anyone knows a good resource that I can look up, I would appreciate if you could share it with me.

Thank you in advance.
Liza

 

[Rob Fitzpatrick]

Oldie but a goodie:
Tom Bascom's presentation called Storage Optimization Strategies
video: http://download.psdn.com/media/exchange_online_2010/1004TomBascom.wmv
slides: http://dbappraise.com/ppt/sos.pptx

 

[Rob Fitzpatrick]

Also relevant:
Tom's presentation on the Alternate Buffer Pool (OE 10.2B+):
http://dbappraise.com/ppt/B2Buzz.pptx

The Alternate Buffer Pool may be particularly helpful to you if you have objects (tables or indexes) that are small but very frequently read. To determine this you need runtime statistics for your application(s). If you take this approach you may want to structure your database so that these frequently-read storage objects reside in their own storage areas.

In addition to runtime data you should also get a static analysis report of your database:
proutil dbname -C dbanalys -Bp 10 > dbname.dba
With some massaging you can take the text file produced by this command and bring it into Excel for analysis.

Topics to research (in Database Administration manual and/or KB):
Database startup parameters:
-basetable
-baseindex
-tablerangesize
-indexrangesize

VSTs:
_TableStat
_IndexStat

You can get information on which tables and indexes are accessed most frequently by writing your own code to interrogate the VSTs above, or by running Tom's free open-source utility ProTop (http://dbappraise.com/protop.html).

 

[LBaliao]

Hi Rob. This is great information. Thank you for sharing. I will read them up and study. - Liza

 

[Rob Fitzpatrick]

You're welcome. Some other general points, in no particular order (I assume you are on OE 10 or later and you are using the Enterprise database license):

• Always use Type II storage areas (areas with 8, 64, or 512 blocks per cluster are Type II areas).
• Never put any objects (tables, indexes, or LOB columns) in the Schema Area.
• Never put objects of different types (e.g. tables and indexes) in a single storage area.
• Create a storage area for each very large table.
• For each of those, create a storage area for the indexes of that table.
• Create separate storage areas for LOBs, if any.
  
• Create a storage area for tables with word indexes, if any (this will yield benefits for future index rebuilds; ensures that data scans can be multi-threaded).
• Ensure that you understand your choices for storage area settings for records per block and blocks per cluster before defining your structure. Choose values appropriate for your data. RPB doesn't matter for index areas (provided they only contain indexes). For those I use RPB 128 just in case someone accidentally adds a table to an index area in the future.
• Split tables into different areas based on the physical characteristics of the data (e.g. large tables, small tables, empty tables, tables with small mean record size, tables with large mean record size) rather than the functional characteristics of the data (e.g. accounting tables, sales tables, tax tables, etc.).
• Re fixed versus variable extents: unless you have very high transaction volume or your storage areas are very large (hundreds of GB), you should be fine using all variable extents in your structure. That, combined with enabling large file support, will reduce the maintenance and monitoring required. Variable extents can grow to a maximum of 1 TB. Without large file support the maximum extent size is 2 GB.
  
• When creating a new DB, choose the 8 KB database block size. Don't ever use a file system block size that is larger than your database block size, as performance will suffer.
  
• In production, always use after imaging (and monitor its status!). Using variable-length AI extents will make your life easier.
  
• If possible, locate the after image extents on separate physical storage from the rest of the database. That way if your database becomes corrupted or is lost due to media failure, you can recreate it with your last backup and your AI files since that backup. Obviously that won't be an option if your AI files were also stored on the failed media.

Related stuff:

• Get to know your storage subsystem. What storage is available to you? Is it on a SAN? NAS? Direct-attached storage? HDD or SDD? RAID level? Are there different tiers of storage available to you?
• Don't put database files on parity-based RAID storage. Examples: RAID 5/6/50/60/DP, etc. RAID 10 is a good choice.
  
• Use quality enterprise storage. Don't put database files on a NAS or on consumer-grade disks.