Database areas design

Posted by jmls on 24-May-2011 11:09

I know that this has been discussed before, but I am curious to know if there are any "new" features to people running on 10.2B (preferably on SP04)

Have an opportunity to redesign the database layout and was considering the following:

1) all large or heavy I/O tables to have their own area

2) All indices relating to #1 to have their own area

3) all other tables to be in a "data" area

4) All indices relating to #3 to have their own "index" area

for #1, is 64 rows per block still the "best" rough guide ?

for #2, is there any advantage to putting each index into it's own area, rather than putting all indices for a table into 1 area

for #2 and #4, what is the optimal or "best" rough guide to rpb ?

most of the #1 tables are write-once, read many or have a very high proportion of reads vs writes

are there any more "new" startup parameters relating to tuning ?

Thanks

OpenEdge RDBMS - Forum

All Replies

Posted by Ruanne Cluer on 12-Nov-2013 02:41

-B2 secondary buffer pool ?

Specific create and toss limits for individual Tables in the same Type II Storage Area as opposed to the default values in order to achieve better compaction for example on fields that grow over time as they are continually updated (eg a Comment, where you'd want that Toss limit to be higher and the create limit lower based on the minimum and maximum record size of the entry).

Posted by Rob Fitzpatrick on 12-Nov-2013 23:14

For a good primer on database storage design considerations, have a look at Tom Bascom's presentation Storage Optimization Strategies.

Slide deck: dbappraise.com/.../sos.pptx

Video (Tom at Progress Exchange Online 2010):

http://download.psdn.com/media/exchange_online_2010/1004TomBascom.wmv

To address your questions:

  1. Optimal RPB for a storage area containing tables is dependent on your database block size and the characteristics of your data.  If you're looking for a simple "set it and forget it" approach you could use an 8 KB DB block size and 128 RPB.  You can get more complicated than this, and others will offer different opinions on this.      
  2. You can get very complicated with your storage design, or keep it simple.  The right choice for you depends a lot on your situtation, including how good your hardware is, how big your database is, and how hands-on you are or plan to be with your database.  Some people like to tweak, and some want to set things up once and leave it as is.  So you could put all the indexes of a large table in their own area, or you could subdivide further, e.g. if a dbanalys shows that there is a significant size difference between indexes, or if your run-time stats show there are significant usage differences between indexes.  You may have large indexes on a table that are very infrequently used. So you might not want to rebuild them at the same time as the most often-accessed index.  You might put them in a separate area to avoid doing unnecessary work in an idxbuild.
  3. You should always segregate tables and indexes into separate storage areas.  RPB doesn't matter for index-only areas (unless you goof and put a table into an index-only area by mistake).
  4. There are new database startup parameters in SP06 and later that could help you, if you have LRU latch contention.  These are -lruskips and -lru2skips; they are documented in the service pack release notes and in this KB article: http://knowledgebase.progress.com/articles/Article/000031150/p

Posted by ChUIMonster on 13-Nov-2013 11:42

If 128 is good then 256 is better...

This thread is closed