RE: log_buffer
- From: "James Howerton" <jhowerton@xxxxxxxxx>
- To: "oracle list" <oracle-l@xxxxxxxxxxxxx>,<mwf@xxxxxxxx>
- Date: Wed, 21 May 2008 07:09:53 -0500
Thanks for the info Mark. We do have peaks of commits, its a vendor
applicationn so its difficult but not impossible to get them to change code.
I'm working on 15,000 rpm disks for redo log files but it will take a while to
convince management.
...JIM...
>>> "Mark W. Farnham" <mwf@xxxxxxxx> 5/20/08 9:45 AM >>>
Increasing your log buffer size may tend to reduce log buffer space
requests. A recent post by JL explained some of the system total counting
complications when many sessions are simultaneously spewing commits, so I
won't repeat it.
The fact that you're generating a significant fraction of space request
waits (log buffer space) tends to indicate that you are actually overdriving
your i/o, so to the extent your commit rate is peaked, increasing the space
may tend to reduce the space waits to the extent it allows caching
transaction redo in flux and not yet committed. That's only about 10% of the
reported wait count and 20% of the wait time compared to the "log file sync"
amounts, but since "log file sync" may tend to be over-reported, there is a
good chance you will increase throughput in your case by increasing the
buffer. It may exacerbate the reported "log file sync" waits, so you'll have
to evaluate whether it improves overall throughput some other way. (Like
maybe the elapsed time of your most important individual session action(s);
so if the individual important session space waits tend to go down without
the individual session log file syncs going up, then you did the right
thing. Unless memory is very tight on your machine it won't cost much to
give it a bump, like maybe double it as the previous poster suggests.)
If your real problem is commit rate and your load is not peaked, then
increasing space probably won't help much and you'll need to figure out how
to drain commits faster or accomplish your workload with less of them.
Unfortunately you can't make a certain decision from system level aggregates
(though a quick look at disk service times on the devices fielding the log
writes is probably also worthwhile. If the service times are peaked then log
writer is probably periodically fighting with archiver or something else. If
they are chronically slow you might want to get the log destinations on some
faster media by themselves [if you can't execute your work load with fewer
commits]).
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ujang Jaenudin
Sent: Monday, May 19, 2008 10:21 PM
To: jhowerton@xxxxxxxxx
Cc: oracle list
Subject: Re: log_buffer
jim,
I never set log_buffer > 10mb
on high oltp system, I set it to 3mb.
mostly log file sync is due to frequent commit and "may be" slow io.
--
thanks and regards
ujang | oracle dba
jakarta - indonesia
On Tue, May 20, 2008 at 5:04 AM, James Howerton <jhowerton@xxxxxxxxx> wrote:
> DBA's,
>
> What is you're log_buffer set to??? I have a 2 Tb oltp two node RAC
database on 10.1.0.5 that is showing lots of log file sync waits. My current
setting is 1.5 Mb. Redo log files are 2Gb each.
>
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~ % Total
> Event Waits Time (s) DB Time Wait
Class
> ------------------------------ ------------ ----------- ---------
--------------
> log file sync 356,123 113,472 64.41
Commit
> log buffer space 35,114 26,771 15.20
Configuration
> buffer busy waits 21,139 13,489 7.66
Concurrency
> CPU time 7,996 4.54
> gcs drm freeze in enter server 564,623 5,753 3.27
Other
>
> TIA
> ...JIM...
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
