shared_buffers formula

On 2015-03-03 15:06:54 +0400, Alexander Shutyaev wrote:

Recently we've been having problems with swap on our postgresql server. It
has 125GB of RAM. We've decided to calculate it's memory consumption. To do
this we've used the formulas from the official docs [1].

Note that I think those formulas have been removed from the docs for a
while now (9.2?).

However there is
one parameter that seems strange - Shared disk buffers. According to the
formula it occupies the following space:

(block_size + 208) * shared_buffers

Our values are

block_size=8192
shared_buffers=30GB

The block_size has the default value and shared_buffers was calculated by
pgtune. According to the formula the product will be around 252 000 GB
which doesn't make any sense.

The problem with your calculation is that the shared_buffers referenced
in the formula is the number of buffers - whereas when you specify it
using a size unit (like MB, GB,...) that amount of memory is divided by
the size of a page. So you're off by a factor of 8192.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

Thanks in advance,
Alexander Shutyaev

2015-03-03 14:26 GMT+03:00 Andres Freund <andres@2ndquadrant.com>:

Hi,

On 2015-03-04 14:05:09 +0400, Alexander Shutyaev 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?

Presumably it's used by the OS to cache IO? Are you investigating an
actual problem with memory consumption or just wondering?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, 4 Mar 2015 14:05:09 +0400
Alexander Shutyaev <shutyaev@gmail.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?

Memory usage is much more dependent on the OS than Postgres than you
might realize. I don't see where you state the OS, but I'll assume
it's Linux for now.

Linux default NUMA policy seems to be tuned toward applications
that don't use a lot of RAM. If your 128G server has 8 CPUs, then
Linux will allow a single process to use 16G of RAM before deciding
that it has to use swap for that process. This is one of the
advantantages I find with FreeBSD.

Read up on how NUMA works a bit, and do some research into how to
tune the NUMA policies ... assuming, of course, that you _are_ using
Linux. Or switch to FreeBSD where the default NUMA policy is more
friendly to programs that use a lot of RAM.

2015-03-03 14:26 GMT+03:00 Andres Freund <andres@2ndquadrant.com>:

On 2015-03-03 15:06:54 +0400, Alexander Shutyaev wrote:

Recently we've been having problems with swap on our postgresql server.

It

has 125GB of RAM. We've decided to calculate it's memory consumption. To

do

this we've used the formulas from the official docs [1].

Note that I think those formulas have been removed from the docs for a
while now (9.2?).

However there is
one parameter that seems strange - Shared disk buffers. According to the
formula it occupies the following space:

(block_size + 208) * shared_buffers

Our values are

block_size=8192
shared_buffers=30GB

The block_size has the default value and shared_buffers was calculated by
pgtune. According to the formula the product will be around 252 000 GB
which doesn't make any sense.

The problem with your calculation is that the shared_buffers referenced
in the formula is the number of buffers - whereas when you specify it
using a size unit (like MB, GB,...) that amount of memory is divided by
the size of a page. So you're off by a factor of 8192.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Bill Moran

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Alexander:

On Wed, Mar 4, 2015 at 11:05 AM, Alexander Shutyaev <shutyaev@gmail.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@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.

On Wed, Mar 4, 2015 at 4:50 AM, Bill Moran <wmoran@potentialtech.com> wrote:

On Wed, 4 Mar 2015 14:05:09 +0400
Alexander Shutyaev <shutyaev@gmail.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?

Memory usage is much more dependent on the OS than Postgres than you
might realize. I don't see where you state the OS, but I'll assume
it's Linux for now.

Linux default NUMA policy seems to be tuned toward applications
that don't use a lot of RAM. If your 128G server has 8 CPUs, then
Linux will allow a single process to use 16G of RAM before deciding
that it has to use swap for that process. This is one of the
advantantages I find with FreeBSD.

Read up on how NUMA works a bit, and do some research into how to
tune the NUMA policies ... assuming, of course, that you _are_ using
Linux. Or switch to FreeBSD where the default NUMA policy is more
friendly to programs that use a lot of RAM.

The single most important step on a NUMA pg machine is to make sure
that zone_reclaim_mode = 0. If the kernel detects a largish internode
communication cost, it will turn it on at boot time and after you've
been running a while it will suddenly slow to a crawl as it tries to
move things around in memory. Other important steps are to lower the
background dirty bytes so you don't get massive background writes.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general