What should be the Redo Log Members size?

[Eric Antunes]

I have my Redo Log Members sizes set to 100Mb but I'm affraid this may be too high: is there any rule for this?

Best Regards,

Eric Antunes

[Eric Antunes]

And what about log_checkpoint_interval? It should be twice or half the Redo Log Members size??

[Jean-Luc Oudart]

Eric,

Not really, I suppose the associated question is how often you switch log file ?

Regards
Jean-Luc

[Patti Johnson]

Eric,

Depending on the number of datafiles in your database a checkpoint can become expensive. At minimum you must checkpoint when you perform a log switch. There is no correct size, but a good suggestion is to size the redo log such that it can hold about 30 min of activity during normal operations.

Check pointing more often reduces recovery time in the event of a failure, but the trade off is a performance hit during normal operations.

Check your alert log to determine how often you are performing a logswitch and use that to decide if the redolog files are too large or small.

Patti

[Eric Antunes]

Hi Jean-Luc,

How often you switch log file depends on the redo log members size and the init.ora related parameters, right?

I have read documents advising that checkpoint should trigger at half the redo log members size and others advising a log_checkpoint_interval set to twice the redo log members size (in Kb)...

Eric

[Eric Antunes]

Hi Patti,

Here's the situation:

Thread 1 advanced to log sequence 23993
Current log# 3 seq# 23993 mem# 0: /disc1/.../log3a.dbf
Current log# 3 seq# 23993 mem# 1: /disc2/.../log3b.dbf
Fri Dec 2 13:58:59 2005
Thread 1 advanced to log sequence 23994
Current log# 1 seq# 23994 mem# 0: /disc1/.../log1a.dbf
Current log# 1 seq# 23994 mem# 1: /disc2/.../log1b.dbf

And that last checkpoint was forced by "alter system checkpoint" command. So I suppose I'm not having enough checkpoints...

Eric

[Jean-Luc Oudart · Accepted solution]

Check attachment

I probably ignore tha fact that say the redo must be sized so redo log switch happens very 30mms (or is it every hour ?).
It depends on activity.
get the right checkpoint frequency so yuo have enough but not too many.
In my view the size of thr redo depends mostly on how long does it takes to archive one redo log file when IO activity is fairly high. If it takes between 30sec and 1mn, you may not allowed the redo log switch frequency too be below say 3mns.

Regards
Jean-Luc

[Jean-Luc Oudart]

see also this link
http://www.ixora.com.au/tips/tuning/log_switch.htm

Regards
Jean-Luc

[Patti Johnson]

Eric,

From your post I can't tell how often you switch logs as I only see one timestamp.

If you want to know how ofter you checkpoint you can add
log_checkpoints_to_alert = true
to your init.ora, then you will log checkpoints in additions to log switches to the alert log.

If you have enable archivelog mode (and I assume you would in a production db) then looking at the timestamps on the archive log files will also give an indication on how often you switch log files. The time needed to archive the online log file should be much shorter than the time to fill an online log file.
These init.ora parameters control the frequency of checkpoint so set them accordingly if you wich to checkpoint more frequently than at log switch.

log_checkpoint_interval
log_checkpoint_timeout

You need to know how much redo information you generate in a given period of time to determine the best size for your logs.

If you force a log switch after 30 min, then note the size of your archive log file, that will give you an idea of how much redo is generated in 30min. There are other was to calculate this info, but I think thats the easiest.

Patti

[Yogeeraj_1]

hi Eric,

You should work it out yourself.

This is how we started:

3 groups of 2 members of 25meg a piece

Then, working up from there, you may end in either adding more groups or making the members larger until you have enough online redo log to get you through at least 15-20 minutes of work without switching.

Note that there will be times when 15-20 minutes is too long and too short. You need to pick your time and go from there.

kind regards
yogeeraj

[Indira Aramandla]

Hi Eric,

Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint are one of the major tuning areas in database performance tuning. Generally, larger redo log files provide better performance, however it must be balanced out with the expected recovery time. .Undersized log files increase checkpoint activity and increase CPU usage.

Prior to 10g, it was not possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes were considered reasonable. As rule of thumb switching logs at most once every fifteen minutes.
A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint. This necessitates well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches. The alert log is a valuable tool for monitoring the rate that log switches occur, and subsequently, checkpoints occur. Oracle recommends sizing the online redo logs such that switches occur no more than once per hour. The following is an example of quick log switches from the alert log.
If redo logs switch every 3 minutes, you will see performance degradation. This indicates the redo logs are not sized large enough to efficiently handle the transaction load.
Checkpoint frequency impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that more time will be required during database recovery.
Redo Log Tuning Advisory is a new feature introduced in Oracle 10G. The redo logfile sizing advisory is indeed a good feature in 10g wherein the redo logfile size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery. This feature require setting the parameter "fast_start_mttr_target" for the advisory to take effect and populate the column optimal_logfile_size. Attached is a note from metalink explaining the new feature "Redo Log Tuning Advisory"


Indira A

[Hein van den Heuvel]

imho the rule would be NOT to have the redo log size dictate a checkpoint. Checkpoints can be better controlled through the init params for their size and/or time limits.

But really it is not as so much the redo size that is critical as the amount of work which needs to be recovered if/when the redo is needed. Oracle has recognized this as per 9i and you should check out the (web/doc) reference s for 'Mean Time To Recover'. You'll find that a better business related value than just redo size.

The only time where size might play role is when aggresive archive log shipping is being deployed.. but then you wouldn't be asking this question here.

In the fwiw departement. When I was running Oracle benchmarks, which may not be the most realistic reference point, we always benefitted from (and consequently deployed) really large redo logs. 1GB seemed a minimum, we played with 10Gb when/where needed (when writing more than 10mb/sec redo data). So 100 MB does not sound like too much.

And fwiw 2, when deploying large (1GB + ) redo logs we would either re-create a fresh set of redo logs just before a backup (such that they would compress down to nothing), or temporarely create 10MB redo log during the backup to help speed the backup. Specifically when using redo on Raw we'd temporarely switch to small file system basde redo's for a full backup. (ditto for TEMP and UNDO).

Cheers,
Hein.

[Steve Lewis]

Bear in mind that there is a difference between database recovery time and business recovery time.

For example, our system does a lot of batch updates overnight, churning through the logs at a high rate. During the day, the users are in, making the occasional update (sometimes while on the phone to a customer) but nothing much happens in database terms.

Log size and checkpoint tuning is a fine balance that is affected by your business processing profile.

[Eric Antunes]

Hi,

Sorry for the delay...

Jean-Luc,

The link doesn't work now but thanks any way.

Patti,

I have log_checkpoint_timeout set to 7200 (2 hours) but RDBMS 8.0.5 seems to have a bug 714994 (corrected in 8.0.6) which forces the database checkpointing every 3 seconds when this option is set. I've just unset it and set log_checkpoints_to_alert and tomorow I will see the behaviour...

Here is another part of alert_.log (this shows only the log sequence advance, not real checkpointing since log_checkpoints_to_alert is not set):

Thread 1 opened at log sequence 24004
Current log# 2 seq# 24004 mem# 0: /disc1/.../log2a.dbf
Current log# 2 seq# 24004 mem# 1: /disc2/.../log2b.dbf
Successful open of redo thread 1.
Fri Dec 9 03:47:35 2005
SMON: enabling cache recovery
SMON: enabling tx recovery
Fri Dec 9 03:47:40 2005
Completed: alter database open
Fri Dec 9 10:56:37 2005
Thread 1 advanced to log sequence 24005
Current log# 3 seq# 24005 mem# 0: /disc1/.../log3a.dbf
Current log# 3 seq# 24005 mem# 1: /disc2/.../log3b.dbf

Best Regards,

Eric Antunes

[Patti Johnson]

Eric,

From your last post it looks like checkpoints happen very infrequently, so yes your 100Mb log files may be to large.
Since the bug prevents you from using log_checkpoint_timeout, how about setting log_checkpoint_interval to a value 1/10 the size of your redo log size. Then monitor the checkpoints in the alert log and see what the frequency is.

I prefer to checkpoint only at log switch time, and I like to see the log switches occur every 30min to 1 hour. To accomplish this I have one database with a 40mb log file and another with only 4mb. The smaller sizer are appropraite for smaller db's.

Patti

[Eric Antunes]

Hi,

Since I have this bug issue, I've choosed to decrease redo size to 6Mb to checkpoint just at log switch.

Thanks,

Eric Antunes

[Eric Antunes]

Decreased to 10Mb actually.

[Eric Antunes]

I've also increased db_block_checkpoint_batch from 8 to 64 and checkpoints are TWICE faster now! Any comments?

Best Regards,

Eric Antunes

[Yogeeraj_1]

hi,

just one comment that this parameter has been obsoleted as from Oracle 8i.

this is what oracle says about Obsoleted parameters:

Sometimes development determines that the default value for a parameter provides the predominantly desired behavior and there is no longer a need for an external parameter, therefore the parameter is obsoleted. This can mean that the parameter is either totally eliminated or still exists but is a 'hidden' or 'underscore' parameter. These sort of parameters can be eliminated at any time. Additionally, it is not recommended to alter the value of these parameters unless directed to do so by Oracle Support/Development with regards to a particular situation.


kind regards
yogeeraj

[Eric Antunes]

Today, with more checkpoints to do the statistic analysis, it seems that no great improvements comes with db_block_checkpoint_batch at 64...

Best Regards,

Eric Antunes