Re: Log file I/O throughput

mccmx_at_hotmail.com (Matt) wrote in
news:cfee5bcf.0308080303.1ee2492c_at_posting.google.com:

> I'm looking for some clarification about a log file I/O bottleneck I
> have identified on one of my databases.
>
> By far the most significant waits occuring in the database are for
> 'log file sync' and 'log file parallel write'.
>
> These wait events normally go hand in hand because while the LGWR
> process is waiting for a write to the online log file to complete
> (i.e. log file par write), the users often wait for 'log file sync'.
>
> Since these 2 waits account for over half of total service time, I
> have come to the conclusion that I have a log file I/O bottleneck.
> The sensible thing to do to solve this would be to relocate the
> mirrored copies of my redo log files onto a dedicated disk
> (unfortunately both members are currently in the same filesystem). Or
> to drop the mirrored copy altogether since redundancy in built into
> the I/O subsystem (Hitachi S.A.N).
>
> The reason I am hesitating is that the Unix Sys Admin (HP-UX) has run
> some I/O diagnostics on the server (sar, glance, and iostat) and we
> can see that there
> is no bottleneck at the operating system level. However I know for
> sure that Oracle is generating at least 100 x 80Mb redo log files
> every day.
>
> So I cant co-relate the statistics in Oracle's v$ tables with the
> feedback from the OS performance utilities.
>
> Any ideas....?
>
> Matt
>

Log File Sync means a session is waiting for a commit to complete. With the volume of log's you are generating it sounds like you have a fairly busy OLTP system.

Here are a few suggestions to speed up log file i/o

• Make sure your online logs are on otherwise quiet disks.
• Make sure logs are not on a choked controller. You sysadmin may have looked at % disk busy, but not the % throughput that the controller can handle.
• Never (!!!) use raid-5 for log disks. Raid 0, 1, or 0+1 are okay. Raid- 5 can be very slow, even with caching enabled.
• SAN's introduce problems of their own. Forget what the SAN vendor tells you about not having to worry about the layout of the storage inside their box. It's hogwash. Make sure the logs are not on a busy physical disk that may be busy because of activity not even coming from your database server.