Обсуждение: 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

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

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

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.