Message-ID: <5466C896.4040509@catalyst.net.nz>
Date: Sat, 15 Nov 2014 16:29:26 +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: Jim Nasby <Jim.Nasby@BlueTreble.com>,
 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>
 <5466898C.9070809@catalyst.net.nz> <5466B5BB.8000208@BlueTreble.com>
In-Reply-To: <5466B5BB.8000208@BlueTreble.com>
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 15:08, Jim Nasby wrote:
> On 11/14/14, 5:00 PM, Mark Kirkwood wrote:
>>
>> 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.
>
> What PG version?
>
> One of the huge issues with large shared_buffers is the immense overhead
> you end up with for running the clock sweep, and on most systems that
> overhead is born by every backend individually. You will only see that
> overhead if your database is larger than shared bufers, because you only
> pay it when you need to evict a buffer. I suspect you'd actually need a
> database at least 2x > shared_buffers for it to really start showing up.
>

That was 9.4 beta1 and2.

A variety of db sizes were tried, some just fitting inside 
shared_buffers and some a bit over 2x larger, and one variant where we 
sized the db to 600GB, and used 4,8 and 50GB shared_buffers (50 was the 
best by a small margin...and certainly no worse).

Now we were mainly looking at 60 core performance issues (see thread "60 
core performance with 9.3"), and possibly some detrimental effects of 
larger shared_buffers may have been masked by this - but performance was 
certainly not hurt with larger shared_buffers.

regards

Mark


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