Skip site navigation (1) Skip section navigation (2)

Re: shared_buffer value

From: Richard Huxton <dev(at)archonet(dot)com>
To: "Anjan Dave" <adave(at)vantage(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: shared_buffer value
Date: 2004-01-16 00:17:42
Message-ID: 200401160017.42790.dev@archonet.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-01-16 00:51:55
Subject: Re: shared_buffer value
Previous:From: Anjan DaveDate: 2004-01-15 22:49:28
Subject: shared_buffer value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group