Re: Redo Log problem

On Mon, 26 Oct 1998, Chuck Hamilton wrote:

> On Wed, 21 Oct 1998 20:24:19 -0700, Jeremiah Wilton <jeremiah_at_wolfenet.com> wrote:
>
> >Remember that with less frequent checkpointing, you may incur several
> >detrimental effects:
> >
> >- You will experience increased mean time to recovery (MTR) for crash
> > recovery, because crash recovery has to start application of redologs
> > from the point in time of the most recent checkpoint. The longer ago
> > that was, the more recovery you will have to do.
> >
> >- You will notice an increased incidence of "busy buffer" waits and "write
> > complete" waits for sessions, because more buffers in the block buffer
> > cache are dirty at any given time.
> >
> >- It will increase the number of dirty buffers that must be written out
> > for a checkpoint, making the checkpoint take longer.
>
> Which do you think is worse:
>
> 1) busy buffer waits, or
> 2) writing every dirty buffer to disk, *and* updating the
> header of every datafile in the database, and archiving
> a log file (if archivelog mode is employed).
>
> I'd gladly take #1 over #2.

You have to take #2 anyway (hmm, that sounds funny). It in unavoidable, since there is no way to avoid checkpointing. My point is that it is bad to fall behind on your checkpoints, because that will just result in bad performance overall, given the increased incidence of DBWR-related wait events, and the generally dirty state of a cache that hasn't been checkpointed in a while.

Your suggestion of increasing the size of the redologs increases the amount of time between checkpoints, allowing more buffers to get on the dirty list, and resulting in longer checkpoints anyway. I really think keeping the logs at a nominal size, and tuning checkpoint and write batch performance is a more sane approach.

> The point about MTR is well taken, but in all honesty, how often do
> you need to recover a database? I think that minute to minute
> performance is more important than the once or twice a year that you
> may need to perform recovery.

In my reasoning on this topic, the MTR issue pertains only to automatic *crash* recovery. On very active Oracle databases, you are bound to have the instance or the host crash from time to time, and despite the best of efforts, it will probably happen more than a couple times a year. If you are three logs behind on a checkpont, or if you have to recover a 1G log, that is 15 minutes wasted that you could have been up and doing business. In some environments, that could translate to large sums of money down the drain. If you don't have very high uptime requirements, I guess you can set it up to take as long as you want to recover, but why waste time when seconds count?

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Tue Oct 27 1998 - 09:50:54 CST