Re: shared_buffers formula - Mailing list pgsql-general

​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@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.