On Thu, 19 Jul 2007, Pat Maddox wrote:
The machine in question is a 2.4 Ghz Xeon with 2 gigs of ram running freebsd 6.2 and postgres 8.2. There are 16 concurrent users. This machine is used only for the database. Usage is split out pretty evenly between reads and writes.
If you're running an earlier version of 8.2 than 8.2.4, you should consider upgrading; there were some quirks in earlier versions you'd be better off avoiding.
In the future, you might get a better response to questions of this type from the pgsql-performance list rather than this general one. Anyway, I'll get the easy ones out of the way:
shared_buffers = 256MB
You might improve performance by doubling this; 1/4 of the RAM is the general starting recommendation for this parameter in 8.2.
work_mem = 10MB # min 64kB
This is OK considering you have 16 users (they could use up to 160MB total), but note that if you have occasional large queries you run you can increase this value just for that session.
#maintenance_work_mem = 16MB # min 1MB
If your database is large, you should increase this significantly. 128-256MB would be more in the right area to start with on a dedicated server.
#checkpoint_segments = 3
This may be much too low if you're doing lots of writes; be sure to look in your log files for "checkpoints occuring too frequently" warnings and increase accordingly. 16-32 is a more normal range for this parameter for your class of system.
effective_cache_size = 650MB
This is in the right ballpark, but you might see improvements increasing to the 1GB range. See http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm for more on this and related topics.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
