Re: Analyse without locking?

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
To:
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Analyse without locking?
Date: 2009-11-28 17:57:11
Message-ID: 4B116477.7070109@cam.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Greg Smith wrote:
> Richard Neill wrote:
>> Or am I barking up the wrong tree entirely?
> If you haven't already tuned checkpoint behavior, it's more likely
> that's causing a dropout than autovacuum. See the checkpoint_segments
> section of http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> for an intro.
>

Greg Smith wrote:
> Richard Neill wrote:
>> Or am I barking up the wrong tree entirely?
> If you haven't already tuned checkpoint behavior, it's more likely
> that's causing a dropout than autovacuum. See the checkpoint_segments
> section of http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> for an intro.
>

Thanks - I did that already - it's currently
checkpoint_segments = 64

Now, I understand that increasing checkpoint_segments is generally a
good thing (subject to some limit), but doesn't that just mean that
instead of say a 1 second outage every minute, it's a 10 second outage
every 10 minutes?

Also, correct me if I'm wrong, but mere selects shouldn't cause any
addition to the WAL. I'd expect that a simple row insert might require
perhaps 1kB of disk writes(*), in which case we're looking at only a few
kB/sec at most of writes in normal use.?

Is it possible (or even sensible) to do a manual vacuum analyze with
nice/ionice?

Richard

(*)A typical write should be about 80 Bytes of data, in terms of how
much is actually being stored. I'm using the engineers' "rule of 10"
approximation to call that 1kB, based on indexes, and incomplete pages.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2009-11-28 19:21:06 Re: Analyse without locking?
Previous Message Tom Lane 2009-11-28 16:42:56 Re: OpenMP in PostgreSQL-8.4.0