Re: Fw: Help me put 2 Gigs of RAM to use

Mark Stosberg wrote:
> I find the file a bit hard to read because of the lack of units in
> the examples, but perhaps that's already been addressed in future
> versions.
>
> max_connections = 400 # Seems to be enough us
> shared_buffers = 8192
> effective_cache_size = 1000
> work_mem = 4096
> maintenance_work_mem = 160MB
>
It's already addressed in 8.2, as you can note by the fact that
"maintenance_work_mem" is in there with an easy to read format.
Guessing that someone either pulled in settings from an older version,
or used some outdated web guide to get starter settings.

To convert the rest of them, you need to know what the units for each
parameter is. You can find that out like this:

gsmith=# select name,setting,unit from pg_settings where name in
('shared_buffers','effective_cache_size','work_mem');

name | setting | unit
----------------------+---------+------
effective_cache_size | 16384 | 8kB
shared_buffers | 4096 | 8kB
work_mem | 1024 | kB

So your shared buffers setting is 8192 * 8K = 64MB
effective_cache_size is 8MB
work_mem is 4MB.

The first and last of those are reasonable but on the small side, the
last is...not. Increasing it won't actually use more memory on your
server though, it will just change query plans--so you want to be
careful about increasing it too much in one shot.

The next set of stuff you need to know about general guidelines for
server sizing is at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

You'd probably want to put shared_buffers at a higher level based on the
amount of RAM on your server, but I'd suggest you tune the checkpoint
parameters along with that--just increasing the buffer space along can
cause problems rather than solve them if you're having checkpoints all
the time.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com