1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Sanity Checks On Db Layout And B2 For Upgrade

Discussion in 'Database Admin' started by LarryD, Jul 11, 2017.

  1. LarryD

    LarryD Member

    I know this has been covered before (perhaps ad nauseum), but it never hurts to get ask.

    I am in the process of preparing for a upgrade for a 400+GB db from 10.2B08 to 11.7.1 (or .2 or whatever is available when the upgrade happens by year end).

    Many moons ago we set them up with type II storage areas, where tables with lots of record adds going into their own data and index areas, and pretty much the rest went into a single areas for data and indexes.

    I am planning on doing a dump/load for the upgrade, and would like some thoughts on recommendations for the table areas layout.

    I have analyzed the existing mean record sizes to come up with RPB's as well as utilizing record counts + utilization (yes I know all of our code usage for the tables) to come up with tentative cluster sizes.

    The large tables that have lots of reads/updates/additions (sales, etc) will have their own data/index areas as I currently have them laid out.

    What I am wondering for the relatively static tables:

    - Should relatively static but lots of reads/updates tables be segregated into their own areas or does it matter?
    - Should like sized RPB's be in their own data areas? Or does it really matter if I make them into all say 128 RPB areas vs a 64 and 128 (or even 256)?
    - Does it make sense to have the index areas match the data areas, or should indexes be put together in one index area?

    I am also going to utilize B2 (finally). We have a number of smallish but highly read tables that I am planning on moving into B2 (which I should have already done, but ... ).

    - Is there a practical limit as to efficiency when allocating B2 when taking memory from -B ?
    - If a smallish table gets updated frequently is it still a good candidate for B2?

    Any and all comments, suggestions, criticisms welcome!
     
  2.  
  3. cj_brandt

    cj_brandt Member

    For tables that weren't large enough to justify their own SA, we grouped them by RPB, we have 32, 64 and 128. We also created an area for tables with word indexes - done for performance during index rebuild.

    When we started using the lruskips, the B2 wasn't as big of a performance boost. It is a common recommendation to put the schema area in B2, and highly read small tables, both table and indexes.

    We start the storage areas at 100, so the AI extents aren't scattered through the area numbers when you do a prostrct list.
    We keep all data areas even and all index areas odd. Helpful when you see an error in the db log, you'll know if it is a data area or index.
     
    LarryD and Rob Fitzpatrick like this.
  4. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    I agree with CJ's points.

    Tables that are relatively static (few creates) tend to be relatively small. I group my small tables in a common "data" area, and their indexes in a common "index" area.

    I don't often go to the trouble of doing this but I tend to deal with DBs much smaller than yours.

    I prefer to have matching index areas for my table areas. So, bigtable1_data, bigtable1_index, bigtable2_data, bigtable2_index, etc. The exception, as CJ noted, is small tables that have word indexes. I put them in their own area (wordidx_data) but the indexes just go in the common index area.

    I look at CRUD stats and look for tables with very high reads and zero or low creates. I tend not to have too many. For a client where I implemented ABP a while back my -B2 came to about 40,000 as I recall, which is pretty small (about 310 MB). DB was 50 GB, -B was about 2.25M. But that was enough to move almost half of my logical I/O from -B to -B2. I guess it depends on your application. YMMV.

    I would say yes.

    When you do periodic checks, look at the free blocks in -B2. I believe _BuffStatus (and promon) has been enhanced in 11.7.0 or 11.7.1 to have separate fields for empty buffers in -B and -B2. Also check that the LRU2 replacement policy remains disabled, which is what you want (promon R&D 2|3). I would also recommend setting -lru2skips 100, just in case your assigned objects grow larger than -B2 before you get a chance to increase it.

    Good ideas.
     
    LarryD likes this.
  5. LarryD

    LarryD Member

    Thank you both for the replies and suggestions and recommendations.

    I had already re-read all the prior posts on this, but just wanted to make sure there hasn't been any significant changes to the computations in 11.7.x.

    I also really like CJ's idea of setting the storage areas starting at 100 -- it will indeed help with those issues in the .lg (although we are already using the odd / even strategy).

    Rob, I am using both the CRUD stats as well as knowledge of the app to decide which tables are appropriate for -B2. Sizing of B2 will be part of the testing of the upgrade.

    While we have no complaints about speed of the app (thank you SSD's), since we are going to do a d/l I just wanted to revisit the extents layout to see what might be improved.
     
  6. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    One nice recent change (I think 11.6.3 to be specific) is that proutil db -C viewb2 now shows table sizes in blocks (in addition to index sizes). This makes your -B2 sizing exercise much more straightforward.
     
    LarryD likes this.

Share This Page