RE: log writer tuning
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <kevinc@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Feb 2007 14:23:03 -0600
Another comment worth making: when <log file sync> is a dominant
response time contributor, it can be because of CPU starvation as well.
(By "CPU starvation", I mean too much CPU workload for the CPU capacity
you have. ...Such as what happens when several CPU-intensive queries run
concurrently.)
A <log file sync> is basically the time between the committing process's
posting a message to LGWR and then getting the response back. In between
those two events, these things have to happen:
A. LGWR has to be scheduled and begin executing its code path,
B. ...which of course includes the flush of the buffer.
C. Then the committing process has to be scheduled and returned to its
code path, where it can issue an OS timer call to see how long the <log
file sync> event took.
Most people who see <log file sync> automatically jump to the conclusion
that the problem is B. ...It's always disk for some people, even when
it's really not. But on a system that's CPU starved, A and C can be the
dominant time consumers. Step A is also a dominant time consumer on
systems where people (DO NOT DO THIS!!!) renice their LGWR process to
have a diminished slot in the OS scheduler's pecking order.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Hotsos Symposium 2007 / March 4-8 / Dallas
Visit www.hotsos.com for curriculum and schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kevin Closson
Sent: Wednesday, February 21, 2007 1:05 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: log writer tuning
usually it is "log file sync" (especially while log switch is
performed), which is quite high, however as it is visible in excerpt
below, log file sync is almost the same as log file parallel write.
So I am not sure if log_buffer change will have any impact on log writer
performance - may be it would if we set log_buffer size somewhere
between 1,5M and 0,5M, when some commits would be written down during
background initiated writes, but we have big number of small commits, so
it is possible that most of log writes are forced by those small
...Jaffar, you are right. Having a large redo buffer only accomodates
more piggy-back commit action, it will not somehow make lgwr wait to do
more "efficient" flushes. Quotes on purpose.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
