Re: Memory Settings....

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.

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(dot)keel(at)uai(dot)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.

You may find this informative:

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

On Mon, 22 Oct 2007, Lee Keel 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.
>

Peter Koczan wrote:
> 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.

Another rule of thumb is that on Windows you want only very little
shared_buffers, because of some performance issues with shared memory on
Windows.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

>>> On Mon, Oct 22, 2007 at 11:10 AM, in message
<76758090F8686C47A44B6FF52514A1D30904E0B5(at)hermes(dot)uai(dot)int>, Lee Keel
<lee(dot)keel(at)uai(dot)com> wrote:

> there will probably be no more than 50 simultaneous requests.

> Dual-Quad Core 2.33GHz

My benchmarks have indicated that you want to keep the number of
active queries at or below four times the number of CPUs. You might
want to consider some form of connection pooling which can queue the
requests to achieve that. It can boost both the throughput and the
response time.

-Kevin