RE: log writer tuning

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 21 Feb 2007 11:35:06 -0500
Message-Id: <200702211635.l1LGZQ7l017027@troll.tpk.net>

Switch related "log file sync" could be driven by competition for i/o by ARCH. That can be cured by alternating the online logs on areas of the disk farm that are not in competition with each other for i/o. (If you're not archiving, this is not an issue. If you have a similar test box you can switch to noarchivelog mode and run a load test, then you can see if this is the problem pretty easily.)

If shrinking the log_buffer size reduces "log file sync" waits then you just slowed your system down, but that is quite unlikely unless you have commits larger than one-third the buffer size after you shrink it. Since you have many, many small commits that is unlikely to be the case.

Having the log buffer bigger than it needs to be just steals memory you could be devoting to something else, so if you're memory rich it doesn't hurt much. Having the log buffer too small will hurt, especially if you have some otherwise unmolested large commits, but the size that is "too small" is a bit difficult to measure and varies by circumstance in any case. So usually I make it big enough to remove it from consideration without wasting too much.

Moving the log files around only helps if there is some possibility of avoiding resource contention all the way down to the spinning rust. Whether that is possible depends on how your disk farm is configured.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Remigiusz Sokolowski
Sent: Wednesday, February 21, 2007 10:23 AM To: Syed Jaffar Hussain
Cc: ORACLE-L
Subject: Re: log writer tuning

Syed Jaffar Hussain wrote:
> 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
>

usually it is "log file sync" (especially while log switch is performed), which is quite high, however as it is visible in excerpt below, log file sync is almost the same as log file parallel write. So I am not sure if log_buffer change will have any impact on log writer performance - may be it would if we set log_buffer size somewhere between 1,5M and 0,5M, when some commits would be written down during background initiated writes, but we have big number of small commits, so it is possible that most of log writes are forced by those small commits. "log file sync" is mainly seen while log switch is performed, so I would rather think our I/O subsystem for redo logs is just too slow and smaller log_buffer size won't improve our situation

EVENT                               TOTAL_WAITS TOTAL_TIMEOUTS

TIME_WAITED AVERAGE_WAIT WAIT_CLASS

----------------------------------- ----------- --------------

----------- ------------ --------------- here is excerpt from v$system_event

log file parallel write                73705627              0    
63145598          ,86 System I/O
log file sync                          61569023          82288    
73758397          1,2 Commit
db file parallel write                 68606925              0    
86106980         1,26 System I/O
direct path read                      144461515              0    
88194876          ,61 User I/O
db file sequential read               835890062              0   
444004616          ,53 User I/O

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

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




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

Received on Wed Feb 21 2007 - 10:35:06 CST