Re: Long Transaction
From: Edward Rosenthal <edrosenthal@home.com>
>
>i first wonder why this
>
>LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
>LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit>why are these numbers so low? this will create a low efficiency write cache
>( look at onstat -p to see if this is true)
>( some one please correct me if i am wrong about this...)
You're right, but it's one of those "who cares?" situations. I'm generally
more worried about the duration of my checkpoints than the efficiency of my
write cache.
>which means every time there is a change to the database the physical log
>has to
>be
>retrieved into the buffer cache, in a vicious cycle: i.e.;
>user changes ( updates), the buffer is written to the physical log ( before
>image),
>the buffer is modified, , the low max dirty limit is reached, the lru
>cleaner
>starts,
>the physical log is written, the physical log is retrieved etc...
>you probably should increase the size of your physical log for the above
>reason,
>
>as well as since at 75% ( if you ever reach it, with the lru so low you may
>not.)
>the physical log has to be written.
Er....I have the distinctive aroma of cow pats in my nose...where does the
physical log get retrieved, exactly?
>lbu_preserve is set to 1, which is a good thing.
Is it? I wonder why they gave us the parameter to tune, then?
>and what about the sql causing this, if a huge update is taking place with
>no
>commits along the way, won't that cause the situation you are describing?
>i think i read that the size of the entire logical log needs to be large
>enough
>to accomodate this sort of update.
Ah, sense at last. :-)
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Er....I have the distinctive aroma of cow pats in my nose...where does the
physical log get retrieved, exactly?
i love the image. anyway
i'm from petaluma, calif. and we have plenty of cows.
i was thinking, and correct me if i'm wrong, (i'm sure you will!)
that each time the buffer is modified there has to be an io write to the
physical log
to keep the before image handy, for a rollback(?). this comes from the buffer
from
the read cache ...as each buffer modification takes place the physical log is
keeping before image rows by having the cache write to it.
when the 75% is reached,or a checkpoint, the physlog is written to the database,
and flushed.
and because the next buffer modification needs the next before image,
the physical log needs to have the before image written to from the database...
hence, the "retrieval"...
Obnoxio The Clown wrote:
> From: Edward Rosenthal <edrosenthal@home.com>
> >
> >i first wonder why this
> >
> >LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> >LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit> >why are these numbers so low? this will create a low efficiency write cache
> >( look at onstat -p to see if this is true)
> >( some one please correct me if i am wrong about this...)
>
> You're right, but it's one of those "who cares?" situations. I'm generally
> more worried about the duration of my checkpoints than the efficiency of my
> write cache.
>
> >which means every time there is a change to the database the physical log
> >has to
> >be
> >retrieved into the buffer cache, in a vicious cycle: i.e.;
> >user changes ( updates), the buffer is written to the physical log ( before
> >image),
> >the buffer is modified, , the low max dirty limit is reached, the lru
> >cleaner
> >starts,
> >the physical log is written, the physical log is retrieved etc...
> >you probably should increase the size of your physical log for the above
> >reason,
> >
> >as well as since at 75% ( if you ever reach it, with the lru so low you may
> >not.)
> >the physical log has to be written.
>
> Er....I have the distinctive aroma of cow pats in my nose...where does the
> physical log get retrieved, exactly?
>
> >lbu_preserve is set to 1, which is a good thing.
>
> Is it? I wonder why they gave us the parameter to tune, then?
>
> >and what about the sql causing this, if a huge update is taking place with
> >no
> >commits along the way, won't that cause the situation you are describing?
> >i think i read that the size of the entire logical log needs to be large
> >enough
> >to accomodate this sort of update.
>
> Ah, sense at last. :-)
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Edward Rosenthal wrote:
>
> Er....I have the distinctive aroma of cow pats in my nose...where does the
> physical log get retrieved, exactly?
> i love the image. anyway
> i'm from petaluma, calif. and we have plenty of cows.
> i was thinking, and correct me if i'm wrong, (i'm sure you will!)
> that each time the buffer is modified there has to be an io write to the
> physical log
Not exactly. The physical log is not needed for rollbacks only to provide
a known state to begin fast recovery in the event of a crash. Indeed in
9.2x (and undocumented and less so in 7.31) physical logging has been
eliminated for pages not containing special columns. Also even in 7.30
and earlier a page is only written to the physical log if it is not already
there, meaning that it was not updated since the last checkpoint. The
physical log holds the unchanged disk image before any changes NOT each
incremental version. The logical log contains enough information to commit
and rollback ANY transaction.
> to keep the before image handy, for a rollback(?). this comes from the buffer
> from
> the read cache ...as each buffer modification takes place the physical log is
> keeping before image rows by having the cache write to it.
> when the 75% is reached,or a checkpoint, the physlog is written to the database,
>
> and flushed.
> and because the next buffer modification needs the next before image,
No it does not! Not unless a checkpoint has happened in between the
updates.
> the physical log needs to have the before image written to from the database...
> hence, the "retrieval"...
No the unmodified page from the buffer cache is copied to the physical log
if the timestamp on the page is prior to the timestamp of the last
checkpoint. There is NO I/O involved, no retrieval.
Read the extensive description of physical logging in the 7.2x
Administrator's guide and of the new lite checkpoints and reduced physical
logging in the 9.2x Administrator's Guide.
Art S. Kagel
> Obnoxio The Clown wrote:
>
> > From: Edward Rosenthal <edrosenthal@home.com>
> > >
> > >i first wonder why this
> > >
> > >LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> > >LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit> > >why are these numbers so low? this will create a low efficiency write cache
> > >( look at onstat -p to see if this is true)
> > >( some one please correct me if i am wrong about this...)
> >
> > You're right, but it's one of those "who cares?" situations. I'm generally
> > more worried about the duration of my checkpoints than the efficiency of my
> > write cache.
> >
> > >which means every time there is a change to the database the physical log
> > >has to
> > >be
> > >retrieved into the buffer cache, in a vicious cycle: i.e.;
> > >user changes ( updates), the buffer is written to the physical log ( before
> > >image),
> > >the buffer is modified, , the low max dirty limit is reached, the lru
> > >cleaner
> > >starts,
> > >the physical log is written, the physical log is retrieved etc...
> > >you probably should increase the size of your physical log for the above
> > >reason,
> > >
> > >as well as since at 75% ( if you ever reach it, with the lru so low you may
> > >not.)
> > >the physical log has to be written.
> >
> > Er....I have the distinctive aroma of cow pats in my nose...where does the
> > physical log get retrieved, exactly?
> >
> > >lbu_preserve is set to 1, which is a good thing.
> >
> > Is it? I wonder why they gave us the parameter to tune, then?
> >
> > >and what about the sql causing this, if a huge update is taking place with
> > >no
> > >commits along the way, won't that cause the situation you are describing?
> > >i think i read that the size of the entire logical log needs to be large
> > >enough
> > >to accomodate this sort of update.
> >
> > Ah, sense at last. :-)
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com