shared_buffer value

Gurus,

I have defined the following values on a db:

shared_buffers = 10240 # 10240 = 80MB
max_connections = 100
sort_mem = 1024 # 1024KB is 1MB per operation
effective_cache_size = 262144 # equals to 2GB for 8k pages

Rest of the values are unchanged from default.


The poweredge 2650 machine has 4GB RAM, and the size of the database
(size of 'data' folder) is about 5GB. PG is 7.4, RH9.

The machine has been getting quite busy (when, say, 50 students login at
the same time, when others have logged in already) and is maxing out at
100 connections (will increase this tonight probably to 200). We have
been getting "too many clients" message upon trying to connect. Once
connected, the pgmonitor, and the 'pg_stat_activity' show connections
reaching about 100.

There's a series of SELECT and UPDATE statements that get called for
when a group of users log in simultaneously...and for some reason, many
of them stay there for a while...

During that time, if i do a 'top', i can see multiple postmaster
processes, each about 87MB in size. The Memory utilization drops down to
about 30MB free, and i can see a little bit of swap utilization in
vmstat then.

Question is, does the 80MB buffer allocation correspond to ~87MB per
postmaster instance? (with about 100 instances of postmaster, that will
be about 100 x 80MB = 8GB??)

Should i decrease the buffer value to about 50MB and monitor?

Interestingly, at one point, we vacuumed the database, and the size
reported by 'df -k' on the pgsql slice dropped very
significantly...guess, it had been using a lot of temp files?

Further steps will be to add more memory, and possibly drop/recreate a
couple of indexes that are used in the UPDATE statements.


Thanks in advance for any inputs.
-Anjan

************************************************************************
**

This e-mail and any files transmitted with it are intended for the use
of the addressee(s) only and may be confidential and covered by the
attorney/client and other privileges. If you received this e-mail in
error, please notify the sender; do not disclose, copy, distribute, or
take any action in reliance on the contents of this information; and
delete it from your system. Any other use of this e-mail is prohibited.