Thread: Why shared_buffers max is 8GB?

Hi Alexey,

On Wed, Mar 26, 2014 at 1:21 PM, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:
> I read from several sources, what maximum shared_buffers is 8GB.

I believe that was an issue on some older versions, and thats why was
mentioned in several talks. Today it is a sort of apocrypha.

> Does this true? If yes, why exactly this number is maximum number of
> shared_buffers for good performance (on Linux 64-bits)?

25% of available RAM is a good idea to start. Sometimes, if you have
heavy workload _and_ it is possible to reside whole database in
memory, better to use something larger, about  ~75% of RAM.

Best regards,
Ilya
--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com

Il 26/mar/2014 13:36 "Ilya Kosmodemiansky" <ilya.kosmodemiansky@postgresql-consulting.com> ha scritto:
>
> Hi Alexey,
>
> On Wed, Mar 26, 2014 at 1:21 PM, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:
> > I read from several sources, what maximum shared_buffers is 8GB.
>
> I believe that was an issue on some older versions, and thats why was
> mentioned in several talks. Today it is a sort of apocrypha.
>
> > Does this true? If yes, why exactly this number is maximum number of
> > shared_buffers for good performance (on Linux 64-bits)?
>
> 25% of available RAM is a good idea to start. Sometimes, if you have
> heavy workload _and_ it is possible to reside whole database in
> memory, better to use something larger, about  ~75% of RAM.
>
> Best regards,
> Ilya
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

max is 1024mb.

you have to test your workload if it's too low you will get too much i/o ( the filesystem cache could help.. not always /*nfs*/),  if too high your cpu will be eated by lru/ latch/ and so on.

Mat Dba

desmodemone wrote:

> max is 1024mb.

That must be a typo.
It can surely be much higher.

Yours,
Laurenz Albe

On Wed, Mar 26, 2014 at 6:21 AM, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:
> I read from several sources, what maximum shared_buffers is 8GB.
>
> Does this true? If yes, why exactly this number is maximum number of
> shared_buffers for good performance (on Linux 64-bits)?

On most machines the limit is higher than you'd ever want to set it. I
have a set of servers with 1TB RAM and shared buffers on them is set
to 10G and even that is probably higher than it needs to be. The old
1/4 of memory advice comes from the days when db server memory was in
the 1 to 16GB range and even then it was more of a starting place. It
has been found through experience and experiment that few setups can
use more shared buffers than a few gigabytes and get better
performance.


On Wed, Mar 26, 2014 at 7:24 AM, Markella Skempri
<markella_skembri@hotmail.com> wrote:
> I wanted to follow up from this question. I'm running on 9.3.4
> My DB server has 32GB ram so I have assigned 8GB shared_buffer_memory. It is
> quite a big db but with not much traffic. When there is traffic, it's
> usually big.
>
> Lately, the kernel has been killing the postmaster for having assigned too
> much shared memory. Latest crash was when loading a 500MB file.
>
> Should I reduce the shared buffers in order for this to be more robust?

It's not JUST your shared_buffers here. What are your changed settings
in postgresql.conf? Specifically work_mem, max_connections,
temp_buffers and to a lesser extent maintenance_work_mem.

Here's the thing. If you set shared_buffers, work_mem, and
max_connections too low you get a minor problem. Some apps can't
connect, pg is a little slow. If you set them too high you start
killing your DB with the OOM killer which is a major problem.

> On most machines the limit is higher than you'd ever want to set it. I
> have a set of servers with 1TB RAM and shared buffers on them is set
> to 10G and even that is probably higher than it needs to be. The old
> 1/4 of memory advice comes from the days when db server memory
> was in the 1 to 16GB range and even then it was more of a starting place. It
> has been found through experience and experiment that few setups
> can use more shared buffers than a few gigabytes and get better
> performance.

This is really the core of the issue. You can set shared_buffers to almost any level, into multiple TBs if you really
wantedto. Whether or not this is prudent however, is entirely different. There are many considerations at play with
sharedbuffers: 

* Shared buffers must (currently) compete with OS inode caches. If this is shared buffers are too high, much of the
cacheddata is already cached by the operating system, and you end up with wasted RAM. 
* Checkpoints must commit dirty shared buffers to disk. The larger this is, the more risk you have when checkpoints
come,up to and including an unresponsive database. Writing to disks isn't free, and sadly this is still on the slower
sideunless all of your storage is SSD-based. You don't want to set this too much higher than your disk write cache. 
* Performance gains taper off quickly. Most DBAs don't see gains after 4GB, and fewer still see any gains above 8GB. We
haveours set at 4GB after a lot of TPS and risk analysis. 
* Since shared_buffers is the amount of memory that could potentially remain uncommitted to data files, the larger this
is,the longer crash recovery can take. Having this too high could mean the difference between a five-minute outage, and
afive-second outage. The checkpoint_* settings control how this is distributed and maintained, but the risk starts
here.

With that said, we really need to update the WIKI page to reflect all of this. It's still claiming the 25% memory rule:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On Wed, Mar 26, 2014 at 5:14 PM, Shaun Thomas <sthomas@optionshouse.com> wrote:
> * Checkpoints must commit dirty shared buffers to disk. The larger this is, the more risk you have when checkpoints
come,up to and including an unresponsive database. Writing to disks isn't free, and sadly this is still on the slower
sideunless all of your storage is SSD-based. You don't want to set this too much higher than your disk write cache. 

We use on some heavy working machines 48GB of shared buffers (and
sometimes more - depends on amount of RAM). Of course that works only
with good enough hardware raid with large bbu, well tuned linux (dirty
bytes appropriate to raid cache size etc) and aggressively tuned both
checkpoints and background writer:

 bgwriter_delay          | 10
 bgwriter_lru_maxpages   | 1000
 bgwriter_lru_multiplier | 10
checkpoint_completion_target | 0.9
 checkpoint_segments          | 300
 checkpoint_timeout           | 3600

and it really makes sense


--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com

On Wed, Apr  2, 2014 at 11:38:57AM +0200, Alexey Klyukin wrote:
> In most cases 8GB should be enough even for the servers with hundreds of GB of
> data, since the FS uses the rest of the memory as a cache (make sure you give a
> hint to the planner on how much memory is left for this with the
> effective_cache_size), but the exact answer is a matter of performance testing.
>
> Now, the last question would be what was the initial justification for the 8GB
> barrier, I've heard that there were a lock congestion when dealing with huge
> pool of buffers, but I think that was fixed even in the pre-9.0 era.

The issue in earlier releases was the overhead of managing more then 1
million 8k buffers.  I have not seen any recent tests to confirm that
overhead is still significant.

A larger issue is that going over 8GB doesn't help unless you are
accessing more than 8GB of data in a short period of time.  Add to that
the problem if potentially dirtying all the buffers and flushing it to a
now-smaller kernel buffer cache, and you can see why the 8GB limit is
recommended.

I do think this merits more testing against the current Postgres source
code.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +