Re: Analyse without locking? - Mailing list pgsql-performance

From Richard Neill
Subject Re: Analyse without locking?
Date
Msg-id 4B116477.7070109@cam.ac.uk
Whole thread Raw
In response to Re: Analyse without locking?  (Greg Smith <greg@2ndquadrant.com>)
Responses Re: Analyse without locking?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Analyse without locking?  (Greg Smith <greg@2ndquadrant.com>)
Re: Analyse without locking?  (Laurent Laborde <kerdezixe@gmail.com>)
List 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.


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: OpenMP in PostgreSQL-8.4.0
Next
From: Tom Lane
Date:
Subject: Re: Analyse without locking?