Re: Trying to tune postgres, how is this config?

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD