Re: log writer tuning

Thanks for all suggestions and explaination of the whole process

Mark W. Farnham wrote:
> Switch related "log file sync" could be driven by competition for i/o by
> ARCH. That can be cured by alternating the online logs on areas of the disk
> farm that are not in competition with each other for i/o. (If you're not
> archiving, this is not an issue. If you have a similar test box you can
> switch to noarchivelog mode and run a load test, then you can see if this is
> the problem pretty easily.)
>

That is very good point - I will take it into consideration, thanks
> If shrinking the log_buffer size reduces "log file sync" waits then you just
> slowed your system down, but that is quite unlikely unless you have commits
> larger than one-third the buffer size after you shrink it. Since you have
> many, many small commits that is unlikely to be the case.
>
> Having the log buffer bigger than it needs to be just steals memory you
> could be devoting to something else, so if you're memory rich it doesn't
> hurt much. Having the log buffer too small will hurt, especially if you have
> some otherwise unmolested large commits, but the size that is "too small" is
> a bit difficult to measure and varies by circumstance in any case. So
> usually I make it big enough to remove it from consideration without wasting
> too much.
>

So we decrease log_buffer size to get back memory - I am now convinced, it would not help us much

Cary Millsap wrote:
> 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.
thanks for this insight - however I suppose CPU starvation is not the case here - usually we run at avg load of 6 with 12 procs (I believe Solaris load number is well bound with proc number and the good estimation here)

Concluding, we will have an occassion to reconfigure our storage in near future - I suppose it would be fine to separate log files from datafiles and as it would be possible to turn storage for redo into 2 mount points with odd logs on one and even logs on the other.

Thanks once again
Remigiusz

-- 
---------------------------------------
Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL  v.  4.x
Oracle v. 10.x
---------------------------------------

--
http://www.freelists.org/webpage/oracle-l