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

Re: Postgresql optimisation

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Denis BUCHER <dbucherml(at)hsolutions(dot)ch>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgresql optimisation
Date: 2009-10-28 16:20:11
Message-ID: alpine.GSO.2.01.0910281213260.14778@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 28 Oct 2009, Denis BUCHER wrote:

> For now, we only planned a VACUUM ANALYSE eacha night.

You really want to be on a later release than 8.1 for an app that is 
heavily deleting things every day.  The answer to most VACUUM problems is 
"VACUUM more often, preferrably with autovacuum", and using 8.1 puts you 
into a position where that's not really practical.  Also, 8.3 and 8.4 are 
much faster anyway.

8.4 in particular has a fix for a problem you're very likely to run into 
with this sort of workload (running out of max_fsm_pages when running 
VACUUM), so if you're going to upgrade I would highly recommend targeting 
8.4 instead of an earlier version.

> But the database complained about checkpoint_segments (currently = 3)
> What should be changed first to improve speed ?

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server covers this 
parameter and some of the others you should be considering.  If your goal 
is just to nail the major bottlenecks and get the configuration in the 
right neighborhood, you probably only need to consider the setting down to 
the work_mem section; the ones after that are more advanced than you 
probably need.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Dave DutcherDate: 2009-10-28 16:30:39
Subject: Re: Postgresql optimisation
Previous:From: Denis BUCHERDate: 2009-10-28 16:08:29
Subject: Re: Postgresql optimisation

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