Re: Why shared_buffers max is 8GB?

On Wed, Mar 26, 2014 at 1:21 PM, Alexey Vasiliev <leopard_ne(at)inbox(dot)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)?
>
> Thanks!
>
>
I've seen cases when going higher than 8GB memory lead to the improved
performance. Some of the server we are running has 128GB and 32GB
shared_buffers with a better performance than one it had with 8GB.

One should be aware of several drawbacks:
- OOM killer (Linux). If you allocate more memory than you have on the
system (+swap) and your vm.overcommit_memory setting is left to defaults
(0), the postmaster will be killed by the Linux OOM killer. Set it to 2 and
keep in mind other settings (work_mem, maintenance_work_mem, temp and wal
buffers) when determining the shared buffer size.
- Checkpoints. In the worst case most of your shared buffers will be
flushed to disk during checkpoint, affecting the overall system
performance. Make sure bgwriter is actively and aggressively evicting dirty
buffers and checkpoint is spread over the checkpoint_interval with the
checkpoint_completion_target.
- Monitoring. One can use pg_buffercache to peek inside the shared buffers
and see which relations are there and how big is the usage count.

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.

--
Regards,
Alexey Klyukin