Re: SELECT Query on DB table preventing inserts

On 31 Srpen 2011, 1:07, Dan Scott wrote:
> On Tue, Aug 30, 2011 at 13:52, Daniel Verite <daniel(at)manitou-mail(dot)org>
> wrote:
>>        Dan Scott wrote:
>>
>>> the insert process is unable to insert new rows into the database
>>
>> You should probably provide the error message on insert or otherwise
>> describe
>> how it's not working. Normally reading does not unintentionally prevent
>> writing in a concurrent session.
>
> I've investigated a little further and it seems that they don't really
> 'fail'. Just start taking significantly longer to insert, and the
> messages start backing up and eventually stop being sent because

What messages are you talking about?

> they're not being acknowledged. I can see a few "WARNING: pgstat wait
> timeout" messages around the time that this is happening in the
> syslog.

The pgstat messages are a typical symptom of I/O bottleneck - it just
means you'ro doing a lot of writes, more than the drives can take. Enable
checkpoint logging (log_checkpoints=on) and watch the system stats (e.g.
using 'iostat -x' or vmstat), my bet is this is a checkpoint or pdflush
issue.

Anyway we need more info about your system - Pg version, amount of RAM,
shared buffers, checkpoint settings (segments, completion) and page cache
config (/proc/sys/vm/). A few lines of vmstat/iostat output would help
too.

Tomas