On Thursday 15 January 2004 22:49, Anjan Dave wrote: > 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.
OK - settings don't look unreasonable so far. > 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. On linux you'll see three values: SIZE, RSS and SHARE. SIZE is what you're looking at, RSS is resident set size (it's in main memory) and SHARE is how much is shared with other processes. So - 3 processes each with RSS=15MB, SIZE=10MB take up 10+5+5+5 = 25MB. Don't worry about a tiny bit of swap - how is your buff/cache doing then? > Should i decrease the buffer value to about 50MB and monitor? That shared_buffer is between all backends. The sort_mem however, is *per sort*, not even per backend. So - if a complicated query uses four sorts you could use 4MB in one backend. > 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? You need to run VACUUM regularly to reclaim unused space. Since you're on 7.4, take a look at the pg_autovacuum utility, or start by running VACUUM ANALYZE from a cron job every evening. Perhaps a VACUUM FULL at weekends? > Further steps will be to add more memory, and possibly drop/recreate a > couple of indexes that are used in the UPDATE statements. A REINDEX might be worthwhile. Details on this and VACUUM in the manuals. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match