Hello,
I'm trying to tune the parameters in postgres.conf to improve the
performance of my database (where performance = 'make queries run
faster'). I would be very grateful if you could give me some comments
about my choice of configuration settings below (did I do anything
very silly? Am I missing something relevant?).
This my situation:
1. I installed postgresql (8.4) on my own machine to manage my own
data. So I'm its only user. Queries are not complicated but should
handle tables with (tens of) millions of rows.
2. Hardware: Laptop (DELL Latitude E6500) with 3.48 GB of RAM; Intel
Core 2 Duo Mobile Processor P8600 2.40 GHz; running Windows XP. 160 GB
hard disk (+ an external one of 640 GB).
3. Size of database: The /data directory is c.ca 37 GB, 88 tables in
the main schema.
4. Raw data is backed-up elsewhere so I'm not terribly worried about
risks of losing data.
And these are the entries in postgres.conf that I changed from default
(after reading some literature/docs):
max_connections = 20 # Default was 100
shared_buffers = 512MB # min 128kB # Default was 32MB
work_mem = 256MB # min 64kB default was 1MB
wal_buffers = 1MB # min 32kB default was 64kb
checkpoint_segments = 30 # in logfile segments, min 1, 16MB each;
default was 3
checkpoint_timeout = 30min # range 30s-1h; default was 3
effective_cache_size = 1GB # Default 128MB
I understand that tuning is a very database specific issue, but even
some general pointers would help me...
Many thanks!
Dario
--
Dr. Dario Beraldi
Institute of Evolutionary Biology
University of Edinburgh
West Mains Road
Edinburgh EH9 3JT
Scotland, UK
--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.