Re: redo log sync time

"Spencer" <spencerp_at_swbell.net> schrieb im Newsbeitrag news:%Pj76.2958$yG3.15915_at_nnrp1.sbc.net...
>
> "Andreas Goldammer" <agoldammer_at_viega.de> wrote in message
> news:3a5d4d91$0$8777$73bec562_at_personalnews.de.uu.net...
> >
> > "Connor McDonald" <connor_mcdonald_at_yahoo.com> schrieb im Newsbeitrag
> > news:3A5C72C2.2D2C_at_yahoo.com...
> > > Andreas Goldammer wrote:
> > > >
> > > > A few weeks ago I found a high average redo log sync time (40 ms)
> > > > for my AIX database. I looked at the documentation and
> > > > everything I found was tuning the I/O subsystem (SSA) or
> > > > reduce the number of commits (impossible, we are using SAP R/3).
> > > >
> > > > Two weeks ago I moved the database to new hardware
> > > > (new fast box, new fast adapters, new fast disks) and
> > > > removed the AIX mirror from the online redo log filesystems
> > > > to avoid the MWC impact (now I use ORACLE to mirror the log)
> > > >
> > > > The average redo log sync time remains steady, whereas
> > > > other file access counters have decreased
> > > >
> > > > Any other ideas beside I/O and commits ?
> > > >
> > > > Thanks in advance
> > > > Andreas
> > > > mailto: agoldammer_at_viega.de
> > >
> > > As well as Brian's info - you may get some benefit by lowering your
 log
> > > buffer size to get LGWR to wake up "earlier" in the transaction cycle.
> > >
> > > HTH
> > > --
> > > ===========================================
> > > Connor McDonald
> > > http://www.oracledba.co.uk (mirrored at
> > > http://www.oradba.freeserve.co.uk)
> > >
> > > "Some days you're the pigeon, some days you're the statue"
> >
> > Woudn't a lower log buffer size leed to problems in the
> > ORACLE server processes (waiting for logspace) ?
> >
>
> this is not necessarily a problem. remember that the contents
> of the log buffer are not written (by LGWR) to the redo log file
> until (1) the buffer is 1/3 full, or (2) a commit statement is issued.
>
> as Connor points out, with an overly large log buffer, LGWR may
> be waiting for the log buffer to become 1/3 full, and then may have
> a lot of i/o (sequential writes) to do to catch up.
>
> aside from reducing the frequency of commits, the cure for "high"
> redo log sync times is indeed tuning I/O, which means (1) reducing
> contention on the disk drives, and (2) avoiding any unnecessary
> overhead, like that imposed by a file system.
>
>
>

I have tuned I/O and removed the contention on the disk drives and I am still wondering why the logsync time is the same as on the old box.

My tuning actions:

• a faster box (about double speed)
• split the I/O's to the SSA (use two adapters on separate bus)
• install faster disk drives
• increase the SSA speed from 20 to 40
• remove the AIX mirror from the log filesystems (avoid MWC)
• allocate the logs on separate disks (with no other FS on)

These actions were taken and I can see a performance improvment; e.g. The database response times have decreased significant,

       The creation of a 2GB datafile on the old box took 15 min
       whereas the new one finishes in 4 min.

Can anybody tell me about logfile sync and hardware in other OLTP's ? Received on Fri Jan 12 2001 - 01:14:10 CST