Operating System - HP-UX
1748265 Members
3924 Online
108760 Solutions
New Discussion юеВ

Re: What should be the Redo Log Members size?

 
SOLVED
Go to solution
Indira Aramandla
Honored Contributor

Re: What should be the Redo Log Members size?

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
Never give up, Keep Trying
Hein van den Heuvel
Honored Contributor

Re: What should be the Redo Log Members size?

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
Honored Contributor

Re: What should be the Redo Log Members size?

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
Honored Contributor

Re: What should be the Redo Log Members size?

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
Each and every day is a good day to learn.
Patti Johnson
Respected Contributor

Re: What should be the Redo Log Members size?

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
Honored Contributor

Re: What should be the Redo Log Members size?

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
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: What should be the Redo Log Members size?

Decreased to 10Mb actually.
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: What should be the Redo Log Members size?

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

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: What should be the Redo Log Members size?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: What should be the Redo Log Members size?

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
Each and every day is a good day to learn.