Archive Logs causing performance hits 1

[djbow]

OK so my current project is to move our Oracle Database off of Sun E10K and onto IBM Blade Server. All our testing went great and we just moved to production.

The only difference between UAT and production is that Archive Logs are now turned on. Our load times have quadrupled!!!! Does is make sense that load times could suffer that much because of archive logs?

 

[Thargy]

djbow,

yes it does. If you're doing lots of DML, then you'll naturally be generating lots of redo activity. A redo log eventually becomes an archive log, which takes time to write. The redo log can then be reused by the db and so on.

Thus it is possible for archive logging to delay activity on a database. If you've got a ton of data to load, why not turn archiving off, do your monster load, and then re-enable it.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Tharg,

I beg to differ...ARCHIVELOG mode does not delay activity on a database. Normal on-line redo logging takes the same amount of time as it always did. Then, when an on-line redo log file fills up, Oracle asynchronously copies the contents of the just-filled on-line redo log file to an identical archive redo log file while Oracle is writing to the next on-line redo log file. The only extra consumption of processing resources is the CPU time involved in doing the equivalent of a *nix "cp" command.

The high-tag item that we pay when in ARCHIVELOG mode is disk space: the volumes to which we write archived redo log files tend to fill up much quicker and we must have in place a scheme to spool those files off to less critical media.

So, I am still puzzled by what is causing djbow's unacceptable increase in "load times".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[djbow]

Thanks Dave I a though the same thing but the DBA's are telling me the same as thargtheslayer.

I think we have a parameter wrong I will let you know what I find.

 

[karluk]

We would have to know more details about your database architecture to give an informed answer, but clearly there are some factors that could cause running in archive log mode to affect load times.

1. Contention for physical resources. If your archive log files are on the same disk drive as your database files or perhaps share an i/o channel to the same SAN, then there is always going to be a maximum throughput that any physical device can handle. Once that capacity is reached, additional i/o to the same device will cause delays as different processes wait for the device to free up.

2. Redo logs outracing archiving. Typically dbas try to put redo log files on RAID 0+1 disk, since they tend to be small but i/o intensive. In contrast, the archive log directory is more commonly on RAID 5, which has slower performance but is less expensive. That's because on a busy system archive logs can accumulate quickly and require lots of storage. During periods of intense activity (such as data loads), this can cause archiving to fall behind. However, it can't fall too far behind. Once Oracle has cycled through all the redo log groups and wants to reuse the first, it will simply have to wait if that redo log hasn't been written to archive yet.

 

[SantaMufasa]

What that man (KarlUK) says is true!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,

obviously with finite processing power and I/O, it is possible to get into an 'end on' situation with writing redo to archive. My system for example, has one RAID 5 array to store data, redo and archive.

I imagine that some of you are using huge multi-core processor, optically connected systems, which render all of my issues moot. However, I do have one question to "toss into the pot". Does Oracle automatically generate an asynchronous process for redo and archive, regardless of operating system?

Regards

Tharg

Grinding away at things Oracular

 

[stefanhei]

@Tharg
A bad designed and configured system will cause problem no matter what hardware you use. Just try to use a different controler and different disks to part redo from archive - and on the same priciple different storage processors when using SAN.

The processes you are looking for are ora_lgwr_<sid> (logwriter) and ora_arch_<sid> or ora_arc<n>_<sid> (archiver).

@djbow
I think karluk has guessed the cause of your trouble.
Solving point 1 may solve the second point too.

In case it doesn't you'll have to add log groups to give oracle a "larger cycle". While redo outraces archiving your alert.log frequently tells you that lgwr had to wait for the archiver to finish.

If you use more than one archive log destination increasing the number of archiver processes may be a good idea too.

Stefan

 

[djbow]

So when I asked the DBA to create the database and import data so it matches what is in UAT I guess it wasnt clear.

We adjusted the following parameters and we are now running much better now.

Log Buffer = 3MB
Archive Processes = 6
DB Writer Processes = 3
Online Redo Log Size = 300MB
Parallel Servers = 5