High create / Delete tables in own extent (Type 1) - Forum - OpenEdge RDBMS - Progress Community

High create / Delete tables in own extent (Type 1)

 Forum

High create / Delete tables in own extent (Type 1)

This question has suggested answer(s)

75%+ of all CREATE activity occurs in just 3 tables on our 9.1 DB (i.e. Type 1 extents) .  These tables constitute about 38% of the total DB size.

Would it be worth having these in their own extent to reduce Scatter?  My thinking here is that all this delete activity is peppering the DB with gaps like Swiss cheese leading to high scatter over time.

All Replies
  • In my opinion you'd be better off spending the effort upgrading, and moving to Type II storage areas really. Some people do say that busy tables (and their indexes) should be in separate areas, even in Type II, but it's the sort of question that tends to divide the crowd.

  • Correction, first sentence should have started "75%+ of all DELETE activity...".

  • Hi James, totally agree on the need to upgrade but for the immediate future this isn't an option the company I am contracting at are prepared to undertake at present.  Maybe next year...

  • Yes.  Putting those tables into *dedicated* storage areas with appropriately sized rows per block will improve overall performance.

    One storage area per table for the data.  Set RPB to dbBlockSize / (avgRowSize + 20 ) (get that from dbanalys) and round up to the next power of 2.  Put the indexes into dedicated storage areas corresponding to the tables.  It is ok to have multiple indexes per area.

    It is not quite as good as upgrading to OE11 and converting to Type 2 areas -- but it is close.

    I don't think splitting data and indexes into their own areas is at all controversial.  There is occasionally some "spirited discussion" about the appropriate rows per block for index areas.  Some argue for "1" since there is no data.  Others say that if you mess up and accidentally put data in an index area then it is better to have a reasonable default value such as 128.  

    --
    Tom Bascom
    tom@wss.com

  • setting the rows per block of an index are to 1 gives such a small advantage it is nearly immeasureable.

    the consequences of making a mistake and accidentally putting a table into such an area by accident is very bad. Usually it involves much unscheduled downtime. I have paid dearly for this error and will never make it again.

  • Small simple 4GL program can warn us if a table resided in area with RPB 1. The sooner it happens the easier to fix the problem. RPB 1 means: "For indexes only. Even LOB objects are not allowed here!". ;-)

  • so can looking at the current .st file, which i was doing, but not carefully enough.

  • > On May 3, 2016, at 10:32 AM, George Potemkin wrote:

    >

    > The sooner it happens the easier to fix the problem

    true. in my case, i had a fixed downtime window i could not exceed so i could not complete the other tasks i had planned. i noticed during a binary load that the area was growing too fast. still had to pay the piper.

  • And dbanalys could report an actual value of records per block individually for each table. So we could easy compare it with an area's RPB. Does anybody notice how wrong is the choice of the area's RPBs in sports database? ;-)

    Dbanalys "invisibly" reports the number of blocks used by each table. We can "extract" this information and calculate the actual RPBs ourselves.

  • > On May 3, 2016, at 10:49 AM, George Potemkin wrote:

    >

    > And dbanalys could report an actual value of records per block individually for each table. So we could easy compare it with an area's RPB. Does anybody notice how wrong is the choice of the area's RPBs in sports database? ;-)

    this is a waste of time.

  • Setting RPBs for sports database? Well, the sports database is maily used in the readprobe tests and the rather small values of RPBs improve the results of such tests. Sometimes little things mean a lot. ;-)

  • Thanks for all your replies.

    Hello Tom - I've run proutil tabanalys to get the avg row size and then used this to split the tables into 256,128,64,32,8,4,2,1 RPB.  I'm interested in how you said to add 20 to the avg size - is this to allow for potential row expansion?  Is it '20' for a reason - e.g. a good rule of thumb or something more scientific?  There is a KB which also says to subtract 100 from the DB block size to accommodate the block header information - is this something you would do?

  • > 75%+ of all CREATE activity occurs in just 3 tables on our 9.1 DB (i.e. Type 1 extents)

    Do you have the statistics for these tables?:

    _TableStat-update vs _TableStat-create vs _TableStat-delete

    > Would it be worth having these in their own extent to reduce Scatter?  My thinking here is that all this delete activity is peppering the DB with gaps like Swiss cheese leading to high scatter over time.

    How long are RM chains of these tables? It's more important than their scatter factors.

    > There is a KB which also says to subtract 100 from the DB block size to accommodate the block header information

    100 bytes is close to the size of RM block headers in storage area type 2. If one cares about such small pieces of block space then the toss limit (300 bytes for 8K) should be taken into account as well. But I wonder why we need to calculate the best number of records per block with a lot of the decimals and then round the value up to a power of two (not even to a nearest integer)?

  • Hello George,

    > Do you have the statistics for these tables?:

    Yes I do.  These tables cumulatively account for ~75% of the total DB Create, Delete and Update activity,  but they have relatively low Read activity compared to other tables.

    > How long are RM chains of these tables?

    proutil dbanlys reports:

    2,067,264 total blocks found in the database.

    1,123,270 RM block(s) found in the database.

    42.47% of the RM block space is used.

    How do I interpret this?

  • Hello Grant,

    I meant the ratio between creates/deletes/updates for those three tables individually.

    > 2,067,264 total blocks found in the database.

    > 1,123,270 RM block(s) found in the database.

    I meant the message 3905:

    <n> block(s) found in the RM chain.