Re: Memory Settings....

I recently tweaked some configs for performance, so I'll let you in on
what I changed.

For memory usage, you'll want to look at shared_buffers, work_mem, and
maintenance_work_mem. Postgres defaults to very low values of this,
and to get good performance and not a lot of disk paging, you'll want
to raise those values (you will need to restart the server and
possibly tweak some memory config for lots of shared_buffers, I had to
raise SHMMAX on Linux, but I don't know the Windows analogue). The
basic rule of thumb for shared_buffers is 25%-50% of main memory,
enough to use main memory but leaving some to allow work_mem to do its
thing and allow any other programs to run smoothly. Tweak this as
necessary.

The other big thing is the free space map, which tracks free space and
helps to prevent index bloat. A VACUUM VERBOSE in a database will tell
you what these values should be set to.

Go here for full details:
http://www.postgresql.org/docs/8.2/static/runtime-config.html, especially
http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html

Peter

On 10/22/07, Lee Keel <lee.keel@uai.com> wrote:
>
>
>
> I have a client server that is dedicated to being a Postgres 8.2.4 database
> server for many websites.  This server will contain approximately 15
> databases each containing between 40-100 tables.  Each database will have
> approximately 7 web applications pulling data from it, but there will
> probably be no more than 50 simultaneous requests.  The majority of the
> tables will be very small tables around 1K in total size.  However, most of
> the queries will be going to the other 10-15 tables that are in each
> database that will contain postgis shapes.  These tables will range in size
> from 50 to 730K rows and each row will range in size from a 2K to 3MB.  The
> data will be truncated and reinserted as part of a nightly process but other
> than that, there won't be many writes during the day.  I am trying to tune
> this server to its maximum capacity.  I would appreciate any advice on any
> of the settings that I should look at.  I have not changed any of the
> settings before because I have never really needed to.  And even now, I have
> not experienced any bad performance, I am simply trying to turn the track
> before the train gets here.
>
> Server Specification:
>
> Windows 2003 Enterprise R2
>
> Dual-Quad Core 2.33GHz
>
> 8GB RAM
>
> 263 GB HD (I am not 100% on drive speed, but I think it is 15K)
>
>
> Thanks in advance,
>
> Lee Keel
>
>  This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you have received this email in error please notify the sender. This
> message contains confidential information and is intended only for the
> individual named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail.