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