Re: intermittent very high waits in LGWR on Linux?

From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Fri, 01 Jul 2005 12:14:19 +0100
Message-ID: <42c5258b$0$30841$ed2619ec@ptn-nntp-reader01.plus.net>

Noons wrote:

> Holger Baer wrote:
> 

>>>So
>>>"32 rows selected." indicates pain?
>>
>>Not really. All changes Nuno suggested can be made using
>>SQL*Plus (logged in as sysdba) using the following
>>syntax:
>>
>>SQL> alter system set <parametername here> = <value here> scope = spfile;
>>
>>After you're done changing the parameters, issue
>>SQL> create pfile from spfile;
>>
>>this gives you a nice and handy pfile in case you need it. And then you're
>>ready to
>>SQL> startup force
>>which will shutdown the database and start it again, using the new parameters.
>>

> 
> 
> and don't forget to plonk here the contents
> of your brand new "pfile" (edited for Usenet)
> so we can peek at it and see if there is anything else
> going "clunk".
> 

Many thanks to Noons and Holger.

This is looking like some kind of HORRIBLE Linux/Oracle interaction, phenomona, way outside the normal scope of tuning via buffer sizes etc.

I have made all the changes (except for data=writeback, since we have other precious data on the f/s)

The situation is COMPLETELY unchanged. My trace file on a run of 5000 inserts still shows
4922 "log file sync" waits, the shortest of which is 53 microseconds (zoom!) and the longest of which is 997069.

But the spread...
89% are under 29177 microseconds AKA 29 milliseconds with a "reasonable" spread.

But the remaining 11% are over 986468 microseconds, which is extraordinarily close to 1 second.

Indeed, there are only 3 times (out of 4922) above 29177 but below 986468.

It seems that I either get "correct" redo log write out, with times varying from 53 to 29177 microseconds, or I "fallback" to some kind of quantized timeout write behavior, driven by a 1 second clock.

This is gettin' weird.

    BugBear Received on Fri Jul 01 2005 - 06:14:19 CDT