Redo log size and redo log switching !

[Chris Fung]

Hi all,

I am just wondering the relationship between the redo log size and redo log switching.

Perviously I have a redo log with 20MB and I found that there was bunch of log switching activities logged in the alert.log file....then I sized the redo log to 200MB

Since then...I got performance degradation because I migrate the original single copy of redo log from a 3 disk striping group filesystem to ....

a multiplexed configuration with the 1st redo group in a 2 disk striping filesystem and the 2nd group in a 1 disk filesystem.

Right now I am thinking to fallback my change....and some told me that the size of the redo log may cause some effect on the I/O write process.....Is it true....do I need to fallback the 200MB size to 20MB ??

P.S, no DB archiving is required on our system.

Many thanks,

Chris

[Alexander M. Ermes]

Hi there.
You should have two members in each redo log group in case one is blown.
Go to a size of 50 MB and test this.
Another way is to put the online logs on raw devices.
Rgds
Alexander M. Ermes

[Yogeeraj_1]

hi,

Normally when you have to do sizing of redologs, you figure out your redo generation rate and use that to help you size it. I generally just add logs until it goes away. Not very scientific but brutally effecient.

The general procedure is:
Start with 3 groups of 2 members of 25MB a piece and work up from there. Either adding more groups or making the members larger until I have enough online redo log to get me through at least 15-20 minutes of work without switching. As with all rules of thumb -- there will be times when 15-20 minutes is too long and too short. You need to pick your time and go from there.

hope this helps!

regards
Yogeeraj

[Brian Crabtree]

Chris,

Working with the v$loghist view will probably be your biggest help though. Play around with this command to extract data:

select to_char(first_time,'YYYY-MM-DD'),
count(*) from v$loghist
group by to_char(first_time,'YYYY-MM-DD')
order by to_char(first_time,'YYYY-MM-DD');

This will give you a list of days and the number of log switches (not times, although you can break it down to there too), and you can see how many log switches you do a day. I have found the 15-20 minute rule too restrictive personally (not that it isn't a good rule of thumb). I try to keep log switches in the 4-5 minute period for heavily loaded databases if possible, however for some of our large SAP systems, 250m log switches occur every minute during load periods.

Hope this helps,

Brian

[Indira Aramandla]

Hi Chris,

The relationship between the redo log size and log switch is the bigger the redo log size the longer is the interval for the switch.
A general rule is that redo logs should switch every 20 to 30 minutes.
The general recommendation is to size the logs so that they don't switch more often than approximately 10-15 minutes or longer during normal activity.

You should check the database alert log and review the frequency of your log switches. You may need to resize your online logs so that the switching/archiving frequency is reduced. The log file switch (archiving needed) would indicate that you are waiting for a log switch because the log we will be switching into has not been archived yet.

I hope this helps

Indira

[Hein van den Heuvel · Accepted solution]

Hello Chris,

You received some good advice already. Notably about sizing the redo to allow for sufficiently infrequent log switches which imply checkpoints.
You did not mention your Oracle version... more recent Oracle version will perform a SOFT checkpoint for a logswitch as it estimates the time and IOs needed and starts early. If your version does not do that, then a 200 MB logfile will have allowed you to build up more dirty buffers and the larger checpoint effort might be seen as a slowdown.

200 MB _ought_ to be better than 20 MB for any application. My experience is in the benchmark world where we often run with extreme redo write rates. We might write at 25 MB/sec. So to avoid a logswitch checkpoint every 5 minutes we readily use 10GB redo logs.

With the latest (9.2) Oracle version one should also look at using the 'Mean Time To Recover' mechanisme as a way to remove checkpoint write spikes.

Dual member groups is advisable, or at least some flavor of mirrored disk as target.

RAW is extra good for REDO. Redo _should_ be all write (as you do not archive). If you see reads on the device, then the file system may be doing read-modify-write cycles, which can be avoided on raw, remove up to half of the IOs. If you need a backup, and don't want to deal with say dd, then just switch to a file based redolog over the backup. Trivial.

a SINGLE physical disk for all redofiles / volumes is all you need for typical applications (up to 10MB/sec) as oracle will only write to one at a time. They _should_ be disks with minimal other activity to avoid disturbing the nice 'linear writes' Oracle does.

Good luck,
Hein.