Re: pgtune + configurations with 9.3

On 15/11/14 06:06, Shaun Thomas wrote:
> Alexey,
>
> The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is
thatsetting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system
with512GB of RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm
prettymuch any disk controller and end up  completely ruining DB performance. And that's just *one* of the drawbacks. 
>

It is probably time to revisit this 8GB limit with some benchmarking. We
don't really have a hard and fast rule that is known to be correct, and
that makes Alexey's job really difficult. Informally folk (including
myself at times) have suggested:

min(ram/4, 8GB)

as the 'rule of thumb' for setting shared_buffers. However I was
recently benchmarking a machine with a lot of ram (1TB) and entirely SSD
storage [1], and that seemed quite happy with 50GB of shared buffers
(better performance than with 8GB). Now shared_buffers was not the
variable we were concentrating on so I didn't get too carried away and
try much bigger than about 100GB - but this seems like a good thing to
come out with some numbers for i.e pgbench read write and read only tps
vs shared_buffers 1 -> 100 GB in size.

Cheers

Mark

[1] I may be in a position to benchmark the machines these replaced at
some not to distant time. These are the previous generation (0.5TB ram,
32 cores and all SSD storage) but probably still good for this test.