Log problem with deletes

Gday All,

    I cant explain why a particular delete takes so much space to log and
    would appreciate advice.

    The log has 519800 blocks of 4k. ie just under 2G

    The table in question is an ISAM with unique keys. It has 5 secondary
    indexes (3 btrees and 2 isams - dont ask me why! it just does).
    The tables is journalled.

    If I delete a single row from the table whilst running log_trace I get
    the following output:

delete from payments_processed where pay_period_number='98013'

********************************************************************
    LOG: BT             Size written/reserved:   76/  52  Flags: JNL
    LOG: DEL            Size written/reserved:  491/ 532  Flags: JNL
    LOG: BTREE_DEL      Size written/reserved:  104/ 140  Flags: JNL
    LOG: DEL            Size written/reserved:   93/ 135  Flags: JNL
    LOG: BTREE_DEL      Size written/reserved:  104/ 139  Flags: JNL
    LOG: BTREE_DEL      Size written/reserved:  106/ 144  Flags: JNL
    LOG: DEL            Size written/reserved:   87/ 134  Flags: JNL

********************************************************************
(1 row)

    Which as far as I can see means that a single delete will take 1061 bytes
    to log and require a reservation of 1276 bytes.

    Hence if I were to go out and delete oh say 169727 records from this
    table I would expect to use approx 176M in the log file and reserve 212M
    as well. ie approx 20% of my log file.

    However, when I run logstat (installation was only executing this delete,
    no other processes were running).

    
----Current log file header-----------------------------------------------------
    Block size: 4096    Block count: 519800    Buffer count: 64
    CP interval: 5198   Logfull interval: 499008   Abort interval: 421038
    Last Transaction Id:    0000364A3719C3B3
    Begin: <910856709:205896:92>    CP: <910856709:308451:252>    End: <910856709:313174:264>
    Percentage of log file in use or reserved: 62
    Log file blocks reserved by recovery system: 218271
    Archive Window: <910856709,308451,252>..<910856709,313174,264>
    Previous CP:    <910856709,305119,344>
    Status:         ONLINE,ECPDONE
    Active Log(s):         II_LOG_FILE
    
    Tx_id: 004C6DA2    Tran_id: 0000364A3719C24C    Database: 00050B39
        Process: 00230001    Dis_tran_id: <0,0> Session: 00000001406BFA80
        First: <910856709,205904,280>    Last: <910856709,313174,264>    Cp: <910856709,205896,92>
        Write: 951724  Split: 0  Force: 0  Wait: 17  Reserved: 218275
        Status: ACTIVE,PROTECT,JOURNAL
        Wait Reason: (not waiting)
        User: 

    The 'Write' figure is interesting. 951724 > Block count(519800). How can
    you write more log buffers than the log actually has?

    If we use the 'First' and 'Last' log file addresses we get blocks used to 
    write the delete were: 313174-205904=107270
    Add the reserved blocks to get a total required of 325545 blocks.
    ie 62% of my log file - which at least tallies with whats observed.

    Not only am I using 3 times as much log as I expect, the ratio of
    reserved space to logged data is 2:1 whereas The log_trace output would
    indicate an (approx) 5:6 ratio is to be expected.

    What gives? Please keep answers brief, I've been hit in the head a lot.

    I'm running OI1.2 Patch 5542 on a DEC Alpha. OS is DUNIX 4.0D.

    Martin Bowes




At 12:00 PM +1100 1/14/99, Martin Bowes wrote:
>Gday All,
>
>    I cant explain why a particular delete takes so much space to log and
>    would appreciate advice.
> [snip]
>
>    Which as far as I can see means that a single delete will take 1061 bytes
>    to log and require a reservation of 1276 bytes.

Well, actually you only need one BT so each delete uses 985 bytes and
reserves 1224.

>    Hence if I were to go out and delete oh say 169727 records from this
>    table I would expect to use approx 176M in the log file and reserve 212M
>    as well. ie approx 20% of my log file.
>
>    However, when I run logstat (installation was only executing this delete,
>    no other processes were running).
>...
>    Percentage of log file in use or reserved: 62
>    Log file blocks reserved by recovery system: 218271
> ....
>    The 'Write' figure is interesting. 951724 > Block count(519800). How can
>    you write more log buffers than the log actually has?

The 'Write' figure counts log records written, not log I/O's.  At 6 log records
per deleted record it would seem that you had deleted about 158620 rows
when you took your logstat snapshot.  Does that sound about right?

> ....
>    Not only am I using 3 times as much log as I expect, the ratio of
>    reserved space to logged data is 2:1 whereas The log_trace output would
>    indicate an (approx) 5:6 ratio is to be expected.

About all that I can figure is that you are running afoul of some sort of
blocking factor.  I have always had the impression that log records don't
split buffers if it can be avoided, and it's likely that they have to start
on some suitable alignment boundary too.  That will use up a few percent
more but hardly a factor of 2.

Perhaps the best way to answer this (lacking an "insider" answer) is to
try the delete and run logdump on the log file part way through.

Karl R. Schendel, Jr.
K/B Computer Associates   schendel@kbcomputer.com
Ingres and Unix Expertise

President, North American Ingres Users Association
president@naiua.org
Join the NAIUA and make your voice heard!
Ingres Q & A
Back to William's Home Page
Email William