Re: pgtune + configurations with 9.3

From: Mark Kirkwood
Subject: Re: pgtune + configurations with 9.3
Date: ,
Msg-id: 5466C896.4040509@catalyst.net.nz
(view: Whole thread, Raw)
In response to: Re: pgtune + configurations with 9.3  (Jim Nasby)
List: pgsql-performance

Tree view

pgtune + configurations with 9.3  (Tory M Blue, )
 Re: pgtune + configurations with 9.3  (Albe Laurenz, )
 Re: pgtune + configurations with 9.3  (Josh Berkus, )
  Re: pgtune + configurations with 9.3  (Shaun Thomas, )
   Re[2]: [PERFORM] pgtune + configurations with 9.3  (Alexey Vasiliev, )
    Re: pgtune + configurations with 9.3  (Shaun Thomas, )
     [PERFORM] pgtune + configurations with 9.3  (Alexey Vasiliev, )
      Re: pgtune + configurations with 9.3  (Shaun Thomas, )
       Re[2]: [PERFORM] pgtune + configurations with 9.3  (Alexey Vasiliev, )
        Re: Re[2]: [PERFORM] pgtune + configurations with 9.3  (Stuart Bishop, )
       Re: pgtune + configurations with 9.3  (Mark Kirkwood, )
        Re: pgtune + configurations with 9.3  (Jim Nasby, )
         Re: pgtune + configurations with 9.3  (Mark Kirkwood, )
        Re: pgtune + configurations with 9.3  (Stuart Bishop, )
         Re: pgtune + configurations with 9.3  (Johann Spies, )
 Re: pgtune + configurations with 9.3  (Johann Spies, )
  Re: pgtune + configurations with 9.3  (Johann Spies, )

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




pgsql-performance by date:

From: Mark Kirkwood
Date:
Subject: Re: pgtune + configurations with 9.3
From: Yuri Kunde Schlesner
Date:
Subject: Plan uses wrong index, preferring to scan pkey index instead