Re: shared_buffer value - Mailing list pgsql-performance

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