Conference orarep::nomahs::rdb_60

    
    	; Version of Rdb is 6.1-02 (they've been waiting for delivery of 
    	; 6.1A for almost a month!) and Alpha VMS 6.2.
    
    You can have it on tape right now.
    
    Regards,
    rcs
    

>    Basic problem is locked free space - ugh!  Two questions came up that I
>    can't answer.  The thresholds say I need 135 free bytes or the area
>    will be marked as full.  However the pages with the locked free space
>    are all less than 135 bytes even when I add the free & and the locked.
>    Free = 68, locked = 60.  128 total should be set to threshold = 3.  So
>    at least Rdb will stop looking there.  Why isn't it?

    
>    2nd problem - area is ONLY used for inserts, no deletes; no updates. 
>    Eventually there will be an archive process, but it hasn't been written
>    yet!  (This is what the customer tells me!)  So how did the space get
>    locked in the first place??!!!  The documentation makes it sound as
>    though this is STRICTLY a function of deletes.

That is not exactly true. Free space can appear as a result of a record being
shrunk (updated so that the new record compresses more than the old one). But
then you say there are NO updates ... 

Can you ask for a dump of one of the pages ? Does it show anything like
"deleted by TID xxx" agains any line ? That would indicate that deletions do
occur.

Oh, another possibility. Does this table have a unique index ? Are there
situations when the inerting program would actually try to insert a row with a
key that already exists in this index ? If so, Rdb will store the row first,
then update the index, which will fail, causing a verb rollback and leaving 
locked free space behind.

Are you sure about the threshold setting ? Can you show the exact threshold
definition ? Is this a mixed or uniform area ?

/albert

Look at note 9.163, etc

This describes the locked free space algorithms for Rdb7 and prior versions. 
It has listed the reasons for locked free space...

Ian

    re: .1  Hi Richard!  What do you mean I can have it now on tape?  Is it
    "only" available on tape?  I don't really know what media the customer
    ordered, but I'll try to find out if they can use tape if that is what
    is available.
    
    re: .2  I'm not at the customer site anymore, but I worked on this for
    several hours and kept going back to the documentation to verify what I
    was seeing, so I think I have my facts right.  There are no thresholds
    set specifically, so the docs say it defaults to marking a page full
    when a full-size record (according to AIP) won't fit.  AIP thinks the
    row is 127 bytes (it's actually only 117 now) + 8 bytes overhead is
    135.  The pages marked full (showing up as threshold 3 when I dump the
    SPAM page) all seem to have 134 bytes or less.
    
    There is a unique index, so that could be the explanation.
    
    I looked at a lot of pages and don't recall seeing anything that said
    deleted by TID or anything else.
    
    re: .3  I read the doc set (several times!) last week - will look at
    note 9.163.  Thanks for the pointer; I did log in Thursday night (and
    again at 5AM Friday morning trying to find answers in here - but you
    were moving!  B-)

>    re: .2  I'm not at the customer site anymore, but I worked on this for
>    several hours and kept going back to the documentation to verify what I
>    was seeing, so I think I have my facts right.  There are no thresholds
>    set specifically, so the docs say it defaults to marking a page full
>    when a full-size record (according to AIP) won't fit.  AIP thinks the
>    row is 127 bytes (it's actually only 117 now) + 8 bytes overhead is
>    135.  The pages marked full (showing up as threshold 3 when I dump the
>    SPAM page) all seem to have 134 bytes or less.

The 127 bytes is the nominal record size i.e. the uncompressed data + Rdb and
Koda overhead (7 bytes + 1 byte per 8 columns). Any page having that amount of
free space (locked + not locked) will be considered "not full". In other words,
the threshold setting only consider the space needed to store the actual row,
not the line and tsn index (another 8 bytes as you indicate: 4 bytes for the
TSN, 4 bytes (2 for offset + 2 for length) for the line index.

Note also that the records are rounded up to the next 16-bit word. So a record 
of size 127 really needs 128 bytes.
    
You say the actual records are 117 bytes ? Are they compressed ?

I suspect that what happens in your case is that the page has sufficient space
to store one more row (128 bytes >= 127+1), but not enough to hold the tsn and
line index. So, even though the page is at threshold 0, no more rows will be
stored there.

/albert

    That doesn't match what I saw.  The reason I went back to the docs
    looking for the default behaviour (no thresholds specified) was that I
    found LOTS of pages marked threshold 3 with more free space (up to 134
    bytes free) than the pages which were marked threshold 0 and contained
    the locked free space.
    
    Would Rdb mark it as full once it discovered it couldn't store a row
    there?  That could explain it!
    
    Oh, and yes, compression is enabled since they don't ever update the
    rows.
    
    Mary Ann

Mary Ann,

>    Would Rdb mark it as full once it discovered it couldn't store a row
>    there?  That could explain it!

No, Rdb will not do this.

I can't recall you saying, but I presume this is page format uniform, and 
that the storage map does not have thresholds specified. In which case the 
theresholds should go from 0 to 3 when there is less free space (including 
locked) than the aip record length.

G'day,

Mark.

    Hi Mark, Ian, Richard, Albert . . . . did I miss anyone?
    
    Yes, it is uniform area; I think I said so, but it's been a week from
    hell so who knows?  Your answer seems contradictory to me.  First you
    say it won't Mark it as full, then you say it will.  
    
    What I saw is that it didn't!
    
    There are MANY pages marked as full (threshold=0) which have MORE free
    bytes than these pages which have the locked free space.  I know!  I
    read hundreds of them!!
    
    I was looking for a pages checked problem.  Trace shows every so often
    an insert does 754 reads then settles down to a reasonable rate.  Every
    first insert seems to be walking through the same footsteps so I know
    it is NOT recognizing that it can't fit something there.  Since other
    pages are marked as full, I don't understand what is happening.
    
    And of course this is one of those big important customers with 19
    levels of management checking the performance on an hourly basis!  No
    pressure here. . . .
    
    Thanks,
    Mary Ann

Mary Ann,

>    There are MANY pages marked as full (threshold=0) which have MORE free
>    bytes than these pages which have the locked free space.  I know!  I
>    read hundreds of them!!

Threshold 0 is empty. Threshold 3 is Full.

G'day,

Mark.

    All right.  I was tired last night; the pages I was looking at were
    marked FULL with more free space than the ones which show the locked
    free space.  And it sounds like you are telling me that it shouldn't be
    that way.
    
    Is it bug time?
    
    Mary Ann

Can you show us one of these pages?

I'd like to see the dump of the AIP (RMU/DUMP/LAREA=RDB$AIP)

Ian

    I can't this week.  I'm not on-site nor is muy contact there.  I'll try
    to get it next week, when he gets back!
    
    Mary Ann
    

Mary Ann,

>    All right.  I was tired last night; the pages I was looking at were
>    marked FULL with more free space than the ones which show the locked
>    free space.  And it sounds like you are telling me that it shouldn't be
>    that way.

Without seeing the page, I don't know. The threshold should be zero if the 
amount of free space (including locked free space) is greater than the AIP 
record length.

G'day,

Mark.

    Well I can't prove it to you tonight, but what caught my eye in the
    first place was that MANY of the pages marked full had more space than
    the 2 pages which supposedly had space, only half of what was there was
    locked.  And the thing that aggravated me, was that there wasn't enough
    space to store the row, even if the locked space weren't locked!
    
    But sadly, this may not even be the real problem .  . . . but that
    discussion will probably continue in the V7 conference.
    
    MAH

>    Well I can't prove it to you tonight, but what caught my eye in the
>    first place was that MANY of the pages marked full had more space than
>    the 2 pages which supposedly had space, only half of what was there was
>    locked.  And the thing that aggravated me, was that there wasn't enough
>    space to store the row, even if the locked space weren't locked!

Well rmu/verify should complain about PAGESPAMENT errors then.

Have you run a verify on this area?

G'day,

Mark.

    No, it's a 7X24 database doing 300K transactions per day trying to get
    to 2M transactions per day.  They've never run a full verify on this
    database (and yes, I told them that was not good practice!)
    
    I'm not there this week, but I believe they are rebuilding this area
    tonight!
    
    Mary Ann

>    I'm not there this week, but I believe they are rebuilding this area
>    tonight!

If it is just SPAM erroes why don't they use RMU/REPAIR, or RMU/MOVE since 
this would be much faster?

G'day,

Mark.

    I really don't know.  I'm leary of RMU/Move since they are on 6.1-02
    and I didn't about RMU/Repair.  
    
    Still learning after all these years!
    
    MAH