Message-ID: <5466898C.9070809@catalyst.net.nz>
Date: Sat, 15 Nov 2014 12:00:28 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: Shaun Thomas <sthomas@optionshouse.com>,
 'Alexey Vasiliev' <leopard_ne@inbox.ru>
CC: 'Josh Berkus' <josh@agliodbs.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: pgtune + configurations with 9.3
References: 
 <CAEaSS0bLy8=1yVVbAH6us3m5d1tQ9obCxeHHeTU6vrqkggcN+g@mail.gmail.com>
 <1415975036.62330340@f358.i.mail.ru>
 <0683F5F5A5C7FE419A752A034B4A0B9797DCD287@sswchi5pmbx2.peak6.net>
 <1415983219.686386092@f34.i.mail.ru>
 <0683F5F5A5C7FE419A752A034B4A0B9797DCD4DA@sswchi5pmbx2.peak6.net>
In-Reply-To: <0683F5F5A5C7FE419A752A034B4A0B9797DCD4DA@sswchi5pmbx2.peak6.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 that setting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system with 512GB of RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm pretty much 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.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance