Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Flooding Log Files

Re: Flooding Log Files

From: Jeremiah Wilton <jeremiah_at_wolfenet.com>
Date: Tue, 16 Feb 1999 11:04:25 -0800
Message-ID: <Pine.OSF.4.02.9902160946170.15282-100000@gonzo.wolfenet.com>


On Tue, 16 Feb 1999, jack dectis wrote:
>
> So then you really believe that :
>
> 1) the only way to go through 24 meg of redo so fast that the archiver
> can not keep up is on an index rebuild - especially on a 50 Gig
> database?

Hmm, did I say that? Anyway, I was trying to say that the user didn't complain about the archiver not being able to keep up except during index rebuilds. No matter what your log size, the same amount of redo will be generated for an index rebuild uless you use the NOLOGGING clause (unrecoverable in v.7). It will just be in a few bigger files or lots of smaller files. The only difference is frequency of log switch and (presumably) checkpoint. Note that he wasn't complaning about "checkpoint not complete", rather "all online redologs need archiving." The issue was insufficient performance of the archiver given the load of an index build. Outside of that, it didn't sound like he was having any problems.

The size of the database is only anecdotally related to the amount of redo generated anyway. The rate of change is the greatest factor contributing to volume of redo.

> 2) Large redo logs by definition increase crash recovery time
> significantly?

Absolutely. If you are using the standard practice of checkpointing at log switch rather than at some specified time interval, yes. If your instance checkpoints at log switches, the instance will have to apply crash recovery from the most recent checkpoint forward. If that constitutes one 24Mb log file, it will be a fairly quick crash recovery. If the instance has to recover a 500Mb log file, the time to apply that log will be roughly twenty times greater. In high-availaility settings, this can be an important factor. After all, why waste time when seconds count? If you don't care about availability, you can probably ignore it.

> 3) There are no performance and recovery advantages to having all that
> redo information online?

Oops, did I say that too? I should have been clearer. I am not taking any issue with the number or total combined size of online redologs, only the size of the individual logfiles (what this thread has been addressing).

I think I understand that your approach is to increase the total amount of online redo such that the index build can complete before all online log groups need archival. I just don't see that the user who asked the question needs to sustain genuine spikes in redo volume of that size. The only spike he is experiencing is for index builds, which can be done unrecoverable.

BTW, what are you proposing are the recovery advantages to having lots of online redo? Won't Oracle recover archived and online redo equally quickly? I suppose there is some 'think time' on the part of the user to get media recovery going, but in terms of actual recovery performance, I don't see the difference. For crash recovery, you would never have to go to archived redologs anyway. Only recovery o fone or more of the datafiles from a backup would neccessitate such a thing. What is the recovery scenario you are envisioning here?

I still don't see the advantage to using huge logfiles. Use a size that fits your MTR requirements for crash recovery, and allows checkpoints to complete comfortably for every log switch at your peak volume. If you are uncomfortable with the size of log that requires on some poorly-performing I/O subsystems, then tune DB_BLOCK_CHECKPOINT_BATCH (Oracle7) or DB_BLOCK_MAX_DIRTY_TARGET (Oracle8).  

Some people like to use a rule of thumb of 10 to 20 minutes between log switches, and no more than a couple hundred Mb of redo to recover during crash recovery. That seems reasonable to me, but every 5 minutes wouldn't be a problem, as long as checkpoints complete and the archiver can keep up with any genuine spikes in redo volume.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Tue Feb 16 1999 - 13:04:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US