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

Re: Postgresql optimisation

From: Denis BUCHER <dbucherml(at)hsolutions(dot)ch>
To: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgresql optimisation
Date: 2009-10-28 13:48:56
Message-ID: 4AE84BC8.6020004@hsolutions.ch (view raw or flat)
Thread:
Lists: pgsql-performance
Grzegorz Jaśkiewicz a écrit :
> 
> 
> On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER <dbucherml(at)hsolutions(dot)ch
> <mailto:dbucherml(at)hsolutions(dot)ch>> wrote:
> 
>     Dear all,
> 
>     I need to optimize a database used by approx 10 people, I don't need to
>     have the perfect config, simply to avoid stupid bottle necks and follow
>     the best practices...
> 
>     The database is used from a web interface the whole work day with
>     "normal" requests (nothing very special).
> 
>     And each morning huge tables are DELETED and all data is INSERTed new
>     from a script. (Well, "huge" is very relative, it's only 400'000
>     records)
> 
> use truncate, to clear the tables.

Oh yes, instead of DELETE FROM table; ? Ok thanks for the tip

>     For now, we only planned a VACUUM ANALYSE eacha night.
> 
> if it is 8.3+, don't , as autovacuum takes care of that.

8.1.17

>     But the database complained about checkpoint_segments (currently = 3)
> 
> depending on traffic, that's pretty low. You should increment it, beyond
> 12 if possible.

Ok no problem in increasing this value, to, let's say... 50 ?

>     What should be changed first to improve speed ?
>     * memory ?
>      *???
>     Thanks a lot for any advice (I know there are plenty of archived
>     discussions on this subject but it's always difficult to know what very
>     important, and what's general as opposed to specific solutions)
> 
> 
> again, if it is 8.3+ (and everyone here would advice you to run at least
> that version), try using pg_tune script to get best performance settings.

Ok, we will soon move it to a new server, it will be 8.3 then :-)
And I will use pg_tune...

Thanks a lot for your advices !

Denis

In response to

Responses

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2009-10-28 14:17:16
Subject: Re: Postgresql optimisation
Previous:From: Grzegorz JaśkiewiczDate: 2009-10-28 12:26:29
Subject: Re: Postgresql optimisation

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