Re: Log file I/O throughput

Matt wrote:
> I'm looking for some clarification about a log file I/O bottleneck I
> have identified on one of my databases.
>
> By far the most significant waits occuring in the database are for
> 'log file sync' and 'log file parallel write'.
>
> These wait events normally go hand in hand because while the LGWR
> process is waiting for a write to the online log file to complete
> (i.e. log file par write), the users often wait for 'log file sync'.
>
> Since these 2 waits account for over half of total service time, I
> have come to the conclusion that I have a log file I/O bottleneck.
> The sensible thing to do to solve this would be to relocate the
> mirrored copies of my redo log files onto a dedicated disk
> (unfortunately both members are currently in the same filesystem). Or
> to drop the mirrored copy altogether since redundancy in built into
> the I/O subsystem (Hitachi S.A.N).

Other things you can consider:

1. Increase the striping of your mirrors. If you currently have 2 way striped mirrors, consider using 4 way striped mirrors. More striping would increase i/o throughput.
2. Use raw device for the redo log files. Raw device uses true kernalized async io which is faster than the async io mechanism for filesystems. You can also consider using a logical volume manager like VxVM to ease administration of the raw device, and to perform software raid if necessary.
3. If you have a large number of small transactions, batching them up into a larger transaction might save overhead and make things run faster.
4. If you regularly load large amounts of data to the database, see if you can safely do it in NOLOGGING mode.
5. If you're not using RMAN for backup, make sure your backup process is not holding the database in hot backup mode longer than necessary.

>
> The reason I am hesitating is that the Unix Sys Admin (HP-UX) has run
> some I/O diagnostics on the server (sar, glance, and iostat) and we
> can see that there
> is no bottleneck at the operating system level.

Be sure to take a look at the avque column in "sar -d" output to make sure the average number of requests outstanding is not too high.

Cheers,
Dave

> However I know for
> sure that Oracle is generating at least 100 x 80Mb redo log files
> every day.
>
> So I cant co-relate the statistics in Oracle's v$ tables with the
> feedback from the OS performance utilities.
>
> Any ideas....?
>
> Matt
Received on Fri Aug 08 2003 - 16:46:22 CDT