Toss and Create limits

Vinit Bot

New Member
What are the best settings for "Toss and Create" limits? The setting which I have are based on mean size, min size and max size - Should they be based on just one of the mentioned parameters or a combination of them?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Your record min, max, and mean values can change based on application logic and upgrades to it, use behaviour, and possibly other factors, e.g. purging of old, small records. I wouldn't go chasing these numbers and setting Toss and Create limits just because you can.

They are tuning options to help you address issues such as record fragmentation, long RM chains, and low block density. But you should be able to demonstrate that you actually have such problems, and that your database structure and configuration are already near optimal, before reaching this far down the OE DBA's list of possible performance optimizations. If you can justify adjusting these settings at all, it should only be for one to a handful of tables. For the rest it really shouldn't matter.

Here is a more in-depth discussion that touches on Create and Toss limits.
Storage Optimization Strategies
Tom Bascom, White Star Software
from Progress Exchange Online 2010
Video: http://download.psdn.com/media/exchange_online_2010/1004TomBascom.wmv
Slides: pugchallenge.org/2012PPT/sos.pptx
 

TomBascom

Curmudgeon
If you have ProTop you can run the control-r reports. (Make sure that fresh dbanalys data is available first.) This will create $PROTOP/rpt/dbname.rmchain.rpt that might look something like this:

Code:
RM Chain Summary for tpl /db/tpl/tpl

    Table Name                    Size (bytes)       Num Recs       RM Chain   Avg Row   RPB    xRPB     Frag%   Ideal Blks     RM:Blk   Create     Toss    xToss
    --------------------           -----------   ------------   ------------   -------   ---    ----   -------   ----------  ---------  -------   ------   ------
    ws-inbound-log                  1610612736        1521491         200890      1059    16       8     0.47%       196608       1.02      150      300     1164
    ar-trans-d                        50017075         146652           2247       341    32      32     0.00%         6106       0.37      150      300      375
    wb_funct-log                     219886387        1543729            606       142    16      64     0.00%        26842       0.02      150      300      157
    po-trans-d-prod                  225758413        2227660            575       101    64     128     0.00%        27559       0.02      150      300      111
    wm-location                            170              2            508        85    64     128     0.00%            1     508.00      150      300       94
    s_port                                  69              1            508        69    64     128     0.00%            1     508.00      150      300       76
    loc-group                               60              1            508        60    64     128     0.00%            1     508.00      150      300       66
    s_field-lang                           308              4            508        77    64     128     0.00%            1     508.00      150      300       85
    . . .

"RPB" is your actual rows per block. "xRPB" is what ProTop thinks you _should_ be using.

"Ideal Blocks" is the number of blocks that you could have packed this data into if everything were well organized.

RM:Blk is the ratio of RM Chain blocks to that ideal size mentioned above.

"Create" and "Toss" are the current values of the create and toss limits.

"xToss" is a suggested possible alternative Toss limit based on making it 110% the size of the average row.

In the example above ws-inbound-log has a very high number of blocks on the RM Chain. The current rows per block is too large so the first thing to do would be to change it to 8. To do that you will need to dump and load that table which is also the only supported way to zero out the RM Chain. The suggested value for the Toss limit is quite different from the default and the usage of this table has clearly been causing the RM chain to grow very large so I would change that.

The rest of the tables I probably wouldn't worry about from a Toss Limit perspective.
 

Vinit Bot

New Member
If you have ProTop you can run the control-r reports. (Make sure that fresh dbanalys data is available first.) This will create $PROTOP/rpt/dbname.rmchain.rpt that might look something like this:

Code:
RM Chain Summary for tpl /db/tpl/tpl

    Table Name                    Size (bytes)       Num Recs       RM Chain   Avg Row   RPB    xRPB     Frag%   Ideal Blks     RM:Blk   Create     Toss    xToss
    --------------------           -----------   ------------   ------------   -------   ---    ----   -------   ----------  ---------  -------   ------   ------
    ws-inbound-log                  1610612736        1521491         200890      1059    16       8     0.47%       196608       1.02      150      300     1164
    ar-trans-d                        50017075         146652           2247       341    32      32     0.00%         6106       0.37      150      300      375
    wb_funct-log                     219886387        1543729            606       142    16      64     0.00%        26842       0.02      150      300      157
    po-trans-d-prod                  225758413        2227660            575       101    64     128     0.00%        27559       0.02      150      300      111
    wm-location                            170              2            508        85    64     128     0.00%            1     508.00      150      300       94
    s_port                                  69              1            508        69    64     128     0.00%            1     508.00      150      300       76
    loc-group                               60              1            508        60    64     128     0.00%            1     508.00      150      300       66
    s_field-lang                           308              4            508        77    64     128     0.00%            1     508.00      150      300       85
    . . .

"RPB" is your actual rows per block. "xRPB" is what ProTop thinks you _should_ be using.

"Ideal Blocks" is the number of blocks that you could have packed this data into if everything were well organized.

RM:Blk is the ratio of RM Chain blocks to that ideal size mentioned above.

"Create" and "Toss" are the current values of the create and toss limits.

"xToss" is a suggested possible alternative Toss limit based on making it 110% the size of the average row.

In the example above ws-inbound-log has a very high number of blocks on the RM Chain. The current rows per block is too large so the first thing to do would be to change it to 8. To do that you will need to dump and load that table which is also the only supported way to zero out the RM Chain. The suggested value for the Toss limit is quite different from the default and the usage of this table has clearly been causing the RM chain to grow very large so I would change that.

The rest of the tables I probably wouldn't worry about from a Toss Limit perspective.


I don't have ProTop set up. Is there any other way to run the reports?
 

Vinit Bot

New Member
I managed to install ProTop and run the suggested report. I noticed that the Create Limit is always 150. Is there a reason as to why it's fixed at 150? Will it make any difference if I lower the value?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I managed to install ProTop and run the suggested report. I noticed that the Create Limit is always 150. Is there a reason as to why it's fixed at 150? Will it make any difference if I lower the value?
The create limit defaults to 150 bytes. It is the minimum amount of space reserved in an RM block for updates, i.e. it is not used for creates. It can be set at the area level or, in a Type II area, at the table level.

Lowering it can, in theory, increase record density slightly, subject to the size of the records in question. But lowering it also reduces the amount of space available for record updates that make records larger (e.g. field value "foo" changes to "foobar"). If you lower it, you risk increasing record fragmentation which would likely hurt your performance more than the higher record density would help it, so be sure you understand the application behaviour and the I/O profile of the table in question before making changes.
 

TomBascom

Curmudgeon
I have not found a case where I felt that I should lower the create limit.

I suppose that if I *knew* for certain exactly how much space every record needs and that variance or growth was very, very unlikely then I might. But the amount of space is quite small and, as Rob says, the cost of being wrong and increasing fragmentation is probably much higher than any hypothetical benefit.

Also -- the chances of my "knowing for certain" are pretty low. That sort of knowledge gets stale very fast. Especially when users and programmers get involved.
 
Top