RE: Lots and lots of redo logs

From: Bill Buchan <wbuchan_at_uk.intasys.com>
Date: Tue, 18 Sep 2001 13:24:31 -0700
Message-ID: <F001.00391700.20010918074616@fatcity.com>

Out of curiosity and as a test I scrubbed my 16Mb RAID1 array and set it up again with 5 x 50Mb redo groups. I ran the following script to generate lots of redo:

begin

         for i in 1..15000000 loop
                 insert into test_table values (mod(i,10));
                 delete from test_table where anumber = (mod(i+1,10));
                 commit;
         end loop;

end;

ARCHIVELOG mode was on and the archive destination (on another disk) was initially empty.
There were 239 log switches and the test completed in 2 hours 56 minutes.

I then added a further 250 50Mb redo groups, cleared the archive destination and ran the test again. Again it completed in exactly 2 hours 56 minutes, this time 240 log switches (difference of 1, I guess due to whereabouts in the first log it was when the test started).

v$session_wait shows permanent "log file parallel write" for LGWR while the test runs.

Any suggestions why I failed to see any slow down when I (almost) filled the disk with redo groups? (This server wasn't doing anything else and the time of the test). The RAID1 array had a Linux ext2 filesystem on it and I had disabled the write cache.

Thanks again,
- Bill.

At 08:40 14/09/01 -0800, you wrote:
>!! Please do not post Off Topic to this List !!
>
>Generally IDE shows this problem much more than scsi, but on IDE you can see
>as much as 50% performance degrading, I haven't really tested the
>difference.
>
>If you put the logs on, then fill the rests of the disks with a empty file
>of that size, you can make sure that the end of the disk is filled with junk
>and the logs will never pull blocks from that area.
>
>"Do not criticize someone until you walked a mile in their shoes, that way
>when you criticize them, you are a mile a way and have their shoes."
>
>Christopher R. Spence
>Oracle DBA
>Phone: (978) 322-5744
>Fax: (707) 885-2275
>
>Fuelspot
>73 Princeton Street
>North, Chelmsford 01863
>
>
>
>-----Original Message-----
>Sent: Friday, September 14, 2001 12:15 PM
>To: Multiple recipients of list ORACLE-L
>
>!! Please do not post Off Topic to this List !!
>
>
>How big a performance issue is the location of the log file on disk? Even
>if I create the minimum of 2 archive log groups, how can I be sure the
>controller hasn't put these on the outer platters anyway? Since these are
>log files then the writes will be sequential anyway (does that make a
>difference?) - showing my hardware ignorance here!
>
>As you said:
>
>"But in my opinion here is my priorities.
>1. Recoverability
>2. Performance"
>
>I would have thought that having lots of archive log groups (normally) not
>overwritten for a few days in addition to properly archived logs would
>boost my recoverability with negligible impact on
>performance. (Incidentally the MAXLOGFILES maximum value is 255, on Linux
>anyway).
>
>Thanks
>- Bill.
>
>At 07:10 14/09/01 -0800, you wrote:
> >!! Please do not post Off Topic to this List !!
> >
> >It is very common for people to see all that free space and want to fill
>it.
> >Avoid the desire, fill it with a blank file. Disks are cheap and if you
> >store things on the outer platters performance will suffer.
> >
> >Redo logs and other things in oracle waste disk space as drives get bigger,
> >it is unavoidable. But using the space for something totally defeats the
> >purpose of good configurations.
> >
> >"Do not criticize someone until you walked a mile in their shoes, that way
> >when you criticize them, you are a mile a way and have their shoes."
> >
> >Christopher R. Spence
> >Oracle DBA
> >Phone: (978) 322-5744
> >Fax: (707) 885-2275
> >
> >Fuelspot
> >73 Princeton Street
> >North, Chelmsford 01863
> >
> >
> >
> >-----Original Message-----
> >Sent: Friday, September 14, 2001 10:35 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >!! Please do not post Off Topic to this List !!
> >
> >Hi
> >
> >Thanks. That's a very good point. I agree that ARCHIVELOG mode will be
> >needed.
> >
> >However, I still have this big disk just for redo logs, so I'm tempted to
> >fill it anyway. This will be a "sort of" supplementary backup in case the
> >archive disk (+ database disks) crash before the backup. Of course it is
> >only "sort of" because, as you say, a runaway process will cycle the logs
> >if it generates lots of redo.
> >
> >However, this seems better use of the disk space than just having a few log
> >groups and leaving the rest of the array empty and unused.
> >Unless there are any other implications?
> >
> >Thanks
> >- Bill.
> >
> >
> >
> > >Hi
> > >
> > >
> > >Yes you can untill the day that you have this runaway process that
>creates
> > >20Gb of redo and than crashes your database 5 minutes before the daily
> > >offline backup should kick in.
> > >
> > >But you are only mirroring, why not put your database in archivelogmode.
> > >You do not have so much redo per day that your disks/archiver can't
>handle
> > >it. Now if your archive directory is full oracle won't crash, but just
>stop
> > >untill you free up some space (I believe this is the behaviour anyway).
> > >
> > >
> > >
> > >Jack

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).

Received on Tue Sep 18 2001 - 15:24:31 CDT