Re: shared_buffers formula

Hi Alexander:

On Wed, Mar 4, 2015 at 11:05 AM, Alexander Shutyaev <shutyaev(at)gmail(dot)com>
wrote:

> Thanks for the answer. Now, given this info I've calculated that our
> postgresql should occupy approx. 30,53 GB while the server has 125 GB of
> RAM. However we often see in top that there is very little free memory and
> even swap is used. What could be the reason of postgres using so much
> memory?
>

​First, do your homework. What is 'so much memory'? because you have told
us that postgres should occupy 30Gb ( more on this below ) and the serveer
has 125 GB ​( strange number, but we'll assume it correct ) and then state
there is little free memory and swap is used. But, what you hadn't told yet
is HOW MUCH MEMORY IS POSTGRES USING ? Take the top output, use ipcs or any
other tool if needed, add memory usage for postgres and tell us how much it
is. Maybe it is using only 16Gb and you have another processes eating out
the RAM.

​Now, on the top output. Even if much smaller than yours my machine is near
idle, but TOP shows very few free RAM and some swap usage.​ The swap is
because I did use a lot of ram some hours ago and it swapped a couple of
totally inactive programs. As they are still inactive, the OS has not
bothered to swap them in an free the swap ( why would it do it, maybe I
launch another memory hog and it needs to swap them out again, it will swap
them in when needed ). The little free is because I did some big
filesystems operations, so nearly all the ram is used by disk buffers.

Bear in mind permanently free ram is not good, it means you have too much.
After a time working with a system the only free ram is due to processes
which have terminated and the system hasn't got time to put it to use. If I
now start a program which uses a lot of memory and ends, I get free RAM,
but I will loose the disk cache. Let's sacrify the cache for the good of
science:

folarte(at)xx:~$ free; perl -e '@x[100_000_000]=0;';free
total used free shared buffers cached
Mem: 4047276 3588708 458568 25408 700236 864552
-/+ buffers/cache: 2023920 2023356
Swap: 5858300 3260 5855040
total used free shared buffers cached
Mem: 4047276 3118732 928544 25400 575276 657588
-/+ buffers/cache: 1885868 2161408
Swap: 5858300 8920 5849380

I did use @ instead of $ but it demonstrates my point. I previously had
458568 Kb free. I made a perl one liner unnecesarily request about
4*100_000_000 bytes and after it I had 928544 Kb. Yay! RAM growth. The
problem is I lost a a part of my disk cache ( The OS cleans the disk cache
by writting dirty blocsk, but does not free them until needed ).

After that, if you measure PG usage, your database DISK SIZE is greater
than 30Gb and the DB is being moderately used, it should be greater than
that. 30.5 is what it is going to use for shared buffers, but every working
backend needs extra memory, first of all just to run, and also, if they
perform any signinficant query, to sort results, combine them and do its
magic. Those things are controled by settings like work_mem and others,
they are in the manual, and can add up to quite a bit.

Regards.
Francisco Olarte.