Re: Log file I/O throughput

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Fri, 08 Aug 2003 21:56:30 GMT
Message-ID: <3F341C8D.5030208@nospam_netscape.net>

Chuck wrote:

> 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.

What the SAN vendor said is correct for cache-centric devices, i.e. high-end devices typically with cache larger than 64GB, like the HDS 9900 series, EMC Symmetrix, IBM Shark.

For storage-centric devices, or mid-range devices like EMC Clariion or HDS 9500 series, what you said is correct, that you have to look at back-end controller info in allocating volumes.

Cheers,
Dave Received on Fri Aug 08 2003 - 16:56:30 CDT