Log File - excessive waiting

Clinton Smith Writes:

> We have a system that is currently under the microscope.
> Through careful analysis of the output of several ingres utilities
> we have determined that over a period of 2,705 seconds we had 5,235 
> LOG WAITS.
> 
> Additional to the above we found that there was very heavy usage and 
> contention for the raw log device.
> could the above figures be lessened by splitting the RAW LOG onto 2
> separate devices ? , and what are the pro's and con's?

Is it possible to split a raw device between 2 physical devices?  You might 
see how often are you hitting CPs?  If you have a small CP interval you may have frequent
log read i/o's from any journaling that is done when the archiver wakes up as
well as conceivably, flushing the dmf cache more often than you need to.

> 
> is there something else that we should look at for performance increases 
> in this area ? , should we be looking at tuning of log buffers ?

You might take a look at how full your log buffers are on average when they are
being flushed to the log file. You can use the formula:

     100 * ("Kbytes written") / ("log write i/o's" * "Block size in KB")

If the percentage is low, you might see if you can reduce the size of your log
buffers (8K is the smallest, however).  This would reduce the amount of empty
space being written into your log file and reduce volume to it which might help
reduce the LOG WAITS.  Conversely, if the % is very high, maybe you could reduce
I/Os by increasing the size of the log buffers to move more data with each 
buffer flush.

Perhaps more important would be to keep the LOG FREE WAITS down, if they are running
high.  If you have a lot of these, then perhaps you need more log buffers.  Depending
on how much memory you have available, there really isn't any reason this shouldn't
approach zero.
> 
> we are about to separate the data files from the index files to remove 
> some device contention issues in the hope of performance gains.

You should definitely see some improvement by doing this.

You probably should check out the Expert Note in advisor on logstat (US-13339,EN)
for a bunch of good into on the logging system and logstat, if you haven't already.

Hope this was helpful!

=========================================================================
Monte Malenke                            Phone               303/770-7200
Raymond James Consulting, Inc.           Fax                 303/770-5452
Stanford Building #3                     Email  mmalenke@raymondjames.com
4582 S. Ulster St. Parkway, Suite 100
Denver, CO 80237
=========================================================================
Ingres Q & A
To William's Home Page
Email William