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

From: Craig James <craig_james(at)emolecules(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Michal Vitecek <fuf(at)mageo(dot)cz>, pgsql-performance(at)postgresql(dot)org
Subject: Re: updating a row in a table with only one row
Date: 2009-10-06 15:57:51
Message-ID: 4ACB68FF.3020003@emolecules.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Merlin Moncure wrote:
> 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.

top(1) has a batch mode (-b) that's useful for sending results to a file.

Craig

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message david 2009-10-06 18:15:40 Re: Best suiting OS
Previous Message Josh Kupershmidt 2009-10-06 15:38:25 Re: What is the role of #fsync and #synchronous_commit in configuration file .