Re: VERY slow queries at random

From: Kristo Kaiv <kristo(dot)kaiv(at)skype(dot)net>
To: Greg Smith <gsmith(at)gregsmith(dot)com>
Cc: Gunther Mayer <gunther(dot)mayer(at)googlemail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: VERY slow queries at random
Date: 2007-06-08 08:27:18
Message-ID: 5DCBB698-A6F0-41E9-BF8A-4BB9A66E64B9@skype.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Andrew Sullivan 2007-06-08 14:03:54 Re: reclaiming disk space after major updates
Previous Message choksi 2007-06-08 08:22:14 Database size