Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!postmaster.news.prodigy.com!newssvr21.news.prodigy.com.POSTED!10a1c2a2!not-for-mail Message-ID: <3F341C8D.5030208@nospam_netscape.net> From: Dave Hau User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.2) Gecko/20030208 Netscape/7.02 X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: Log file I/O throughput References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 70 NNTP-Posting-Host: 66.47.68.26 X-Complaints-To: abuse@prodigy.net X-Trace: newssvr21.news.prodigy.com 1060379790 ST000 66.47.68.26 (Fri, 08 Aug 2003 17:56:30 EDT) NNTP-Posting-Date: Fri, 08 Aug 2003 17:56:30 EDT Organization: SBC http://yahoo.sbc.com X-UserInfo1: Q[O[R^_GPBTQR\H]]RKB_UDAZZ\DPCPDLXUNNHLIWIWTEPIB_NVUAH_[BL[\IRKIANGGJBFNJF_DOLSCENSY^U@FRFUEXR@KFXYDBPWBCDQJA@X_DCBHXR[C@\EOKCJLED_SZ@RMWYXYWE_P@\\GOIW^@SYFFSWHFIXMADO@^[ADPRPETLBJ]RDGENSKQQZN Date: Fri, 08 Aug 2003 21:56:30 GMT Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240138 Chuck wrote: > mccmx@hotmail.com (Matt) wrote in > news:cfee5bcf.0308080303.1ee2492c@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