Re: Increase in 'log file sync' waits while 'log file parallel write' remains unchanged
Date: Mon, 21 Sep 2009 17:02:24 -0700 (PDT)
Message-ID: <43173e4a-e0a1-4f35-b24a-1dd367260090_at_33g2000vbe.googlegroups.com>
On Sep 21, 7:52 pm, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
snip
> > Do you have available cpu time on this system when the log file waits
> > shoot up?
>
> This is LPAR on big AIX p595 frame, it has 32 CPU.
Does your LPAR have available cpu when the slow log file syncs occur?
>
> > Unless you are showing some signs of severe cpu starvation ( that
> > might have been missed possibly ) I think you gotta check out the IO
> > subsystem.
> > My databases get about .5 ms for log file sync ... 10 ms is ugly and
> > 100 ms just ouch.
>
> 'log file sync' is wait experienced by foreground user process while
> 'log file parallel write'
> is wait event experienced by Log Writer. I thought that slow I/O will
> lead to increase
> in 'log file parallel write'.
Log file sync usually means transactions are in a commit.
If you have bad log file sync ... it needs to be fixed.
> I checked db once agai, found that log_buffer is set to 1MB. If we
> unset it the
> default value will be 4 MB (128K * cpu_count), I don't think it will
> help.
Most of my systems are at 128 MB ... why would you have a system with a 1 MB log buffer?
> > Local storage or SAN? Can you get some iostat information during the
> > bad times?
>
> Yes, this is EMC DMX-something (2000).
Hard to believe you are getting 10 ms average on a system like that ( even when your db is "good"). That doesn't make much sense to me.
Do an iostat 10 10 when the log file syncs are spiking and post the results if you can. Received on Mon Sep 21 2009 - 19:02:24 CDT