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.
