Re: log writer tuning

  • From: Remigiusz Sokołowski <rems@xxxxxxxx>
  • To: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
  • Date: Wed, 21 Feb 2007 16:23:28 +0100

Syed Jaffar Hussain wrote:
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@xxxxxxxx>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL  v.  4.x
Oracle v. 10.x


Other related posts: