Re: shared_buffer value

On Fri, 16 Jan 2004, Anjan Dave wrote:

> 68 processes: 67 sleeping, 1 running, 0 zombie, 0 stopped
> CPU0 states: 3.1% user 4.4% system 0.0% nice 0.0% iowait 92.0%
> idle
> CPU1 states: 0.0% user 3.2% system 0.0% nice 0.0% iowait 96.3%
> idle
> CPU2 states: 0.4% user 0.3% system 0.0% nice 0.0% iowait 98.3%
> idle
> CPU3 states: 0.3% user 1.0% system 0.0% nice 0.0% iowait 98.2%
> idle
> Mem: 3874188k av, 3622296k used, 251892k free, 0k shrd, 322372k
> buff
> 2369836k actv, 454984k in_d, 44568k in_c
> Swap: 4096532k av, 24552k used, 4071980k free 2993384k
> cached

Note that that machine has 2993384k of kernel cache. This means that
after all that it's doing, there's about 3 gigs of free memory, and the
kernel is just using it to cache files. Should a process need that
memory, the kernel would free it right up.

So, you don't have to worry about setting the buffers too high in
postgresql and running out of memory, you're not even close.

I'd crank up sort mem to 4 or 8 meg or so, and the shared buffers to
something a little higher, say 5000 to 10000 or so. Note that there is a
point of diminishing returns in postgresql where if you allocate too much
buffer memory, it gets slower than just letting the kernel do it.

> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU
> COMMAND
> 4258 postgres 16 0 88180 86M 85796 S 2.1 2.2 14:55 0

this says that this process is using 88 meg or so of ram, and of that 88
mef or so, 84 meg is shared between it and the other postgres processes.

> 5260 postgres 15 0 85844 83M 84704 S 0.0 2.2 2:51 1

Same here. That means that this single process represents a delta of 1
meg or so.

> 3157 postgres 15 0 50364 49M 48484 S 0.0 1.2 0:02 3

Delta is about 2 meg.
and so forth. I.e. you're not using 50 to 80 megs per process, only 2
megs or so, plus the 80 meg of shared memory.

> I am not sure how do I calculate whether 80MB shared_buffer (in
> postgresql.conf)should be increased or decreased from the above values,
> because during higher loads, the number of postmaster instances go up to
> 100 (limited by max connections), each at an RSS of about 87MB...

Generally, increase it until it doesn't make things go faster any more.
80 meg is pretty small, especially for a machine with 4 gigs of ram. The
upper limit is generally found to be around 256 Meg or so, and that's what
we use on our machine at work. Note this may make smaller queries slower,
since the overhead of maintaining a large buffer costs a bit, but it makes
larger queries faster, so it's a trade off.