Re: log writer tuning

Remigiusz,

What are the common log wait events which are encountering at the db level?
Is it log file synce or log file sequential write  or etc.

Having log_buffer size some time causes 'log file synce'.

Jaffar


On 2/21/07, Remigiusz Sokołowski <rems@xxxxxxxx> wrote:


Hi!

Problem:
in monitoring (OEM) we have got "needles" on activity figure (RDBMS ver.
10.2.0.1).
We believe slow log writer is responsible for them.

Question:
what can we do about it?

Considerations:
1. we have quite high commit rate and maybe this is our main problem,
then solution would be some new fast storage for redo logs - but this
solution is the last one to consider due to costs

2. log_buffer is very big (64M, so 1:1 to redo log files size) - we
could set it to lower value
may be that is why we have got those "needles" on diagram.
But in docs for 10gR2 they say:
"sizing the log buffer larger than 1M does not provide any performance
benefit. Increasing the log buffer size does not have any negative
implications on performance or recoverability."
Is it really no negative implications ?
At http://www.ixora.com.au/tips/tuning/log_buffer_size.htm there is
different opinion:
" However, if the log buffer is very big, then the default
/_log_io_size/ threshold will be big also, and so background writes may
seldom be triggered. This means that all the redo will have to be
flushed by sync writes, and so /log file sync/ waits will take longer
than otherwise. This impacts commit response time, and possibly DBWn
performance as well."

3. our redo logs are placed on the same mount point as datafiles - we
could move them to some other mount point
Does it change anything, if we move to separate mount point?
Storage system is a JBOD in architecture RAID10 (so actually as
indicated in SAME concept) composed as one large space. File system is
UFS with set flag forcedirectio - according to our sysadmin writes of
log writer doesn't block other processes on mount point.

4. Are there other parameters for log writing?

Thanks for any comments
Remigiusz




--
---------------------------------------
Remigiusz Sokolowski <rems@xxxxxxxx>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL  v.  4.x
Oracle v. 10.x
---------------------------------------

--
http://www.freelists.org/webpage/oracle-l






--
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA

http://jaffardba.blogspot.com/
http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."

Other related posts: