Re: log buffer size and log file syncs
Date: Sat, 28 Apr 2012 01:14:15 +0300
Message-ID: <CAMHX9J+AcijX4Q4uGHk5_gcEQJ7Okxn-5+c9-gkb2KChtA4tPA_at_mail.gmail.com>
One way to work around this problem would be to identify the fastest redo-generating sessions/workloads/jobs and throttling them. Like reducing parallelism of DML batch jobs or not scheduling them during the busiest OLTP times. Or drop an index on your massive DML batch job, so it would run slower - thus also generate redo slower ;-) Tanel.
On Sat, Apr 28, 2012 at 1:09 AM, Tanel Poder <tanel_at_tanelpoder.com> wrote:
> Yep, it's possible in theory and practice too - if you have some large
> (parallel) transactions generating redo on multiple CPUs - which flood the
> log buffer. This can drive the *log file sync* times up, as there's
> sometimes tens or 100+ MB of redo waiting to be flushed before the commit
> can return.
>
> With lower log buffer size, your large transaction would run slower as it
> would wait for *log buffer space* wait much more. Your smaller
> transactions wait for log buffer space as well, but as there's less redo to
> be flushed in the "queue" then they'll get their redo in there faster and
> also get the write done sooner too.
>
> *However* - I would first look into what the LGWR itself is doing (how
> much CPU it's consuming vs waiting for IO, what's the average IO size (and
> redo size per message received by LGWR etc). A common cause for such
> symptoms is that LGWR just doesn't get enough CPU.
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 27 2012 - 17:14:15 CDT