Re: Postgresql optimisation

Hello Greg,

Greg Smith a écrit :
> 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.

Ok as the new server will be Debian and the latest stbale is 8.3 we'll
be on 8.3 soon :-)

> 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.

I got this problem already on 8.1, I just increased max_fsm_pages, is
that OK ?

>> 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.

Ok I tried to change some parameters, we'll see what happens ;-)

Thanks a lot for all your tips :-)

Have a nice evening !

Denis