Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group