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.