On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER
<dbucherml@hsolutions.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.
For now, we only planned a VACUUM ANALYSE eacha night.
if it is 8.3+, don't , as autovacuum takes care of that.
But the database complained about checkpoint_segments (currently = 3)
depending on traffic, that's pretty low. You should increment it, beyond 12 if possible.
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.
--
GJ