Re: log_file_sync io wait question
What about Steve Adam's comments at: http://www.ixora.com.au/tips/tuning/log_buffer_size.htm What results have been achived by tweeking _log_io_size settings? I was about to poke around with this. Of course, it is not necessary to carefully balance log buffer space waits against log file sync waits. You can have both a large log_buffer setting to avoid log buffer space waits, and a relatively small _log_io_size setting to minimize log file sync waits and reduce their duration. On Wed, Sep 18, 2002 at 09:08:32AM -0800, Pat Howe wrote: We have a canned package that we use to insert approx 100 records/second into one table (oltp environment). Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted. Each and every record is committed. Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit). Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ; log_file_sync = 180 waits/sec log_file_parallel_write = 180 waits/sec We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements. Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes. Does anyone know how we might be able to minimize the IO waits? Thanks in advance. ENVIRONMENT oracle : Oracle 8.1.7.4 os : Sun Sparc Solaris 8 box: 8x8 E10K IO : Hitachi SANS unit through fiber and Brocade switch _ Patrick J. Howe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Howe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log_file_sync io wait question
Title: RE: log_file_sync io wait question well if you have mirrored members that exist on the same disk thats an issue, if this same disk houses the currently used datafiles that would add to the situation. I placed my redo on a seperate raid-1 and dropped the member mirrors and see better stats. I have a buffer size 1mb but I am using 11i applications. Also consider your log file sizes, the larger the more it has to flush at a given time, but the smaller the more often it has to flush all theses consideration above need to be configured according to what you have available hardware-wise. HTH -Original Message- From: Pat Howe [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Subject: log_file_sync io wait question We have a canned package that we use to insert approx 100 records/second into one table (oltp environment). Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted. Each and every record is committed. Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit). Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ; log_file_sync = 180 waits/sec log_file_parallel_write = 180 waits/sec We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements. Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes. Does anyone know how we might be able to minimize the IO waits? Thanks in advance. ENVIRONMENT oracle : Oracle 8.1.7.4 os : Sun Sparc Solaris 8 box : 8x8 E10K IO : Hitachi SANS unit through fiber and Brocade switch _ Patrick J. Howe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Howe INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log_file_sync io wait question
Have you played around with the size and number of your redo logs? Larger redo logs would mean fewer checkpoints. Dropping redo log member mirrors is tempting, but RAID alone isn't enough protection. I experienced a corrupted file system one time, and I was glad that my redo logs were multiplexed on another file system. [EMAIL PROTECTED] 09/18/02 03:39PM well if you have mirrored members that exist on the same disk thats an issue, if this same disk houses the currently used datafiles that would add to the situation. I placed my redo on a seperate raid-1 and dropped the member mirrors and see better stats. I have a buffer size 1mb but I am using 11i applications. Also consider your log file sizes, the larger the more it has to flush at a given time, but the smaller the more often it has to flush all theses consideration above need to be configured according to what you have available hardware-wise. HTH -Original Message- Sent: Wednesday, September 18, 2002 1:09 PM To: Multiple recipients of list ORACLE-L We have a canned package that we use to insert approx 100 records/second into one table (oltp environment). Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted. Each and every record is committed. Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit). Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ; log_file_sync = 180 waits/sec log_file_parallel_write = 180 waits/sec We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements. Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes. Does anyone know how we might be able to minimize the IO waits? Thanks in advance. ENVIRONMENT oracle : Oracle 8.1.7.4 os : Sun Sparc Solaris 8 box: 8x8 E10K IO : Hitachi SANS unit through fiber and Brocade switch _ Patrick J. Howe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Howe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log_file_sync io wait question
There are all sorts of things you can do to speed up the process of writing log files: a) direct io file systems b) isolate logs onto disks c) raw d) faster disks e) ultra fine grain striping f) transaction_auditing = false etc etc etc...but the big key here is: Each and every record is committed... bad bad news there...With that policy, you'll almost always be constricted on lgwr performance. Cheers Connor --- Pat Howe [EMAIL PROTECTED] wrote: We have a canned package that we use to insert approx 100 records/second into one table (oltp environment). Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted. Each and every record is committed. Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit). Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ; log_file_sync = 180 waits/sec log_file_parallel_write = 180 waits/sec We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements. Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes. Does anyone know how we might be able to minimize the IO waits? Thanks in advance. ENVIRONMENT oracle : Oracle 8.1.7.4 os : Sun Sparc Solaris 8 box: 8x8 E10K IO : Hitachi SANS unit through fiber and Brocade switch _ Patrick J. Howe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pat Howe INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: log_file_sync io wait question
Hi Pat There is not a lot you can do since every commit will force a write to the disk ! You are hitting that limit of the disk that normally is app. 100 - 120 I/O per sek. The only real fix is change the application to do less commit's. It will not help to change the size of the redo log buffer or the redo log files if you have multiple redo log members on the same disk it will help to separate the members to different disk's but it will not fix the design problem of committing for every insert. Somebody might suggest you to put a chach on the redo log file system but don't this very dangerous since Oracle assumes that writes to disk's are true writes and if you ever had a loss of the chach there is a big chance that your database would loose transactions :-( Pat Howe wrote: We have a canned package that we use to insert approx 100 records/second into one table (oltp environment). Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted. Each and every record is committed. Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit). Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ; log_file_sync = 180 waits/sec log_file_parallel_write = 180 waits/sec We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements. Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes. Does anyone know how we might be able to minimize the IO waits? Thanks in advance. ENVIRONMENT oracle : Oracle 8.1.7.4 os : Sun Sparc Solaris 8 box: 8x8 E10K IO : Hitachi SANS unit through fiber and Brocade switch _ Patrick J. Howe -- /regards Peter Gram Mobil : +45 2527 7107 Fax : +45 4466 8856 Miracle A/S Kratvej 2 2760 Måløv http://miracleas.dk smime.p7s Description: application/pkcs7-signature
