Re: VERY slow queries at random

On 07.06.2007, at 22:42, Greg Smith wrote:

> On Thu, 7 Jun 2007, Gunther Mayer wrote:
>
>> wal checkpoint config is on pg defaults everywhere, all relevant
>> config options are commented out. I'm no expert in wal stuff but I
>> don't see how that could cause the problem?
>
> Checkpoints are very resource intensive and can cause other
> processes (including your selects) to hang for a considerable
> period of time while they are processing.  With the default
> parameters, they can happen very frequently.  Normally
> checkpoint_segments and checkpoint_timeout are increased in order
> to keep this from happening.
>
> This would normally be an issue only if you're writing a
> substantial amount of data to your tables.  If there are a lot of
> writes going on, you might get some improvement by adjusting those
> parameters upward; the defaults are pretty low.  Make sure you read
> http://www.postgresql.org/docs/8.2/static/wal-configuration.html
> first so you know what you're playing with, there are some recovery
> implications invoved.

I remember us having problems with 8.0 background writer, you might
want to try turning it off. Not sure if it behaves as badly in 8.2.
increasing wal buffers might be a good idea also.

Kristo