Re: Postgresql optimisation

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