Re: updating a row in a table with only one row

On Tue, Oct 6, 2009 at 10:59 AM, Michal Vitecek <fuf(at)mageo(dot)cz> wrote:
> Merlin Moncure wrote:
>>On Mon, Oct 5, 2009 at 5:17 AM, Michal Vitecek <fuf(at)mageo(dot)cz> wrote:
>>
>>>  Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB
>>>  with write-back enabled. Could it be that its internal cache becomes
>>>  full and all disk I/O operations are delayed until it writes all
>>>  changes to hard drives?
>>
>>that's possible...the red flag is going to be iowait. if your server
>>can't keep up with the sync demands for example, you will eventually
>>outrun the write cache and you can start to see slow queries.  With
>>your server though it would take in the hundreds of (write)
>>transactions per second to do that minimum.
>
>  The problem is that the server is not loaded in any way. The iowait is
>  0.62%, there's only 72 sectors written/s, but the maximum await that I
>  saw was 28ms (!). Any attempts to reduce the time (I/O schedulers,
>  disabling bgwriter, increasing number of checkpoints, decreasing shared
>  buffers, disabling read cache on the card etc.) didn't help. After some
>  3-5m there occurs a COMMIT which takes 100-10000x longer time than
>  usual. Setting fsynch to off Temporarily improved the COMMIT times
>  considerably but I fear to have this option off all the time.
>
>  Is anybody else using the same RAID card? I suspect the problem lies
>  somewhere between the aacraid module and the card. The aacraid module
>  ignores setting of the 'cache' parameter to 3 -- this should completely
>  disable the SYNCHRONIZE_CACHE command.

I think you're right. One thing you can do is leave fsync on but
disable synchronous_commit. This is compromise between fsync on/off
(data consistent following crash, but you may lose some transactions).

We need to know what iowait is at the precise moment you get the long
commit time. Throw a top, give it short update interval (like .25
seconds), and watch.

merlin