Re: Help me tuning this wait event:log file sync

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 17 Jul 2002 09:37:04 +1000
Message-ID: <ah2alp$o5v$1@lust.ihug.co.nz>

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0207161516.43766774_at_posting.google.com... > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<agvicv$s65$1_at_lust.ihug.co.nz>...
> > "Yong Huang" <yong321_at_yahoo.com> wrote in message
> > news:b3cb12d6.0207151402.18ca5b57_at_posting.google.com...
> > > Never use a log_buffer larger than 1M. It's useless over that limit.
> > >
> >
> > Not quite true. On very heavy-transactional-load systems, a buffer uo to
5
> > or 6Mb *may* (or may not!) be appropriate. Had Oracle ever decided that
> > anything bigger than 1Mb was utterly "useless", they would never have
> > introduced the rule about LGWR flushing every 1Mb of uncommitted redo
> > (because that rule doesn't get invoked until the buffer is bigger than
3Mb,
> > because of the 'flush when 1/3rd full' rule).

>
> OK. Maybe setting log_buffer to up to 3 MB is appropriate. Above that,
> LGWR has to write when the uncommitted redo reaches 1MB anyway. It'll
> be very unlikely for foreground sessions to generate redo to fill more
> than 2 MB log buffer when LGWR is writing to disk. (Otherwise reaching
> to the 1MB mark would also be much more frequent). Not sure if I
> express myself clearly.

Well, I think you're wrong on this one too. Entries into the log buffer are being made entirely in memory by server processes. Yet LGWR only clears the buffer by writing its contents down to slow spinning chunks of metal called hard disks. It is therefore perfectly possible for the log buffer to fill much more quickly than LGWR can clear it. Additional space in the buffer gives LGWR a chance to clear the first 1Mb before you come back wanting to over-write its contents.

Incidentally, reaching the 1Mb mark is meaningless if LGWR is still busy flushing the first 1Mb. It doesn't mean LGWR just stops flushing that first 1Mb and move on to the second. That's the point: on a busy system, you can easily get a backlog of flushing building up, and fill up your second (and third and fourth etc) 1Mb sections whilst the first 1Mb is still being flushed.

Therefore, 3Mb is a complete red herring, too. The buffer needs to be as big as it needs to be to prevent redo allocation retries. That is *usually* about 1Mb or so, but on heavily-loaded systems, that could well be 6Mb, 24Mb or any other megabytage you care to mention.

>
> In fact, this discussion is not relevant to the original poster's
> problem.

I didn't say it necessarily was relevant, but you made the sweeping statement about anything over 1Mb being useless, and that comment really doesn't deserve to be left in the air without challenge. Likewise, any blanket statement that anything over 3Mb is a waste of time is equally questionable.

HJR

>His commit rate is so high adjusting log_buffer shouldn't
> have much effect, because LGWR writing is triggered by the
> application, not by the (1) 3 second, (2) 1/3 buffer full, or (3) 1MB
> buffer full rule. Steve Adams once had a newsletter "condemning" most
> developers' bad habit of too frequent commits, not using PL/SQL stored
> code. I still propose that his application be overhauled to solve the
> problem.
>

> >
> > > Do you have multiple log members per group? Use 1 member.
> >
> > I definitely can't agree with this last suggestion. 1 member groups? A
> > recipe for data loss.
> >
> > You're right that it might help eliminate some performance woes, of
course.
> > Oracle has to do less work, so yeah, of course things work faster. But
in
> > the eternal trade-off between security and performance, you've just
plumped
> > squarely on the performance side of things. Which may or may not be
> > acceptable to the original poster, but it's certainly not what I would
want
> > to recommend.

>
> The original poster says he's using Veritas Quick I/O. We should
> assume data redundancy is already done at the lower level. I always
> propose 1 log member at the company I'm working at, because we either
> have Veritas or (on non-production servers) Solaris Disk Suite. Using
> 2 or more log members used to be prevalent.
>
> Yong Huang

Received on Tue Jul 16 2002 - 18:37:04 CDT