Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-10-03 Thread Hannu Krosing
On Mon, 2009-09-28 at 10:36 -0700, Josh Berkus wrote: On 9/26/09 8:19 AM, Greg Smith wrote: This means that the question you want an answer to is if the OS cache isn't really available, where does giving memory to shared_buffers becomes less efficient than not caching things at all? My

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-28 Thread Josh Berkus
On 9/26/09 8:19 AM, Greg Smith wrote: This means that the question you want an answer to is if the OS cache isn't really available, where does giving memory to shared_buffers becomes less efficient than not caching things at all? My guess is that this number is much larger than 10GB, but I

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-26 Thread Greg Smith
On Thu, 24 Sep 2009, Dan Sugalski wrote: Is there any practical limit to the number of shared buffers PG 8.3.7 can handle before more becomes counter-productive? There are actually two distinct questions here you should consider, because the popular wisdom here and what makes sense for your

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-26 Thread Pierre Frédéric Caillau d
Is there any practical limit to the number of shared buffers PG 8.3.7 can handle before more becomes counter-productive? It is more efficient to have the page in shared buffers, rather than doing a context switch to the OS, copying the entire page from the OS's cache into shared buffers,

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-26 Thread Greg Smith
On Fri, 25 Sep 2009, Jeff Janes wrote: Does it do this even if the block was already in shared_buffers? Usually not. The buffer ring algorithm is used to manage pages that are read in specifically to satisfy a sequential scan (there's a slightly different ring method used for VACUUM too).

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-26 Thread Jeff Janes
On Sat, Sep 26, 2009 at 9:57 AM, Gerhard Wiesinger li...@wiesinger.com wrote: On Sat, 26 Sep 2009, Greg Smith wrote: On Fri, 25 Sep 2009, Jeff Janes wrote: Does it do this even if the block was already in shared_buffers? Usually not. The buffer ring algorithm is used to manage pages that

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-26 Thread Jeff Janes
On Sat, Sep 26, 2009 at 8:19 AM, Greg Smith gsm...@gregsmith.com wrote: Another problem spot are checkpoints. If you dirty a very large buffer cache, that whole thing will have to get dumped to disk eventually, and on some workloads people have found they have to reduce shared_buffers

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-26 Thread Greg Smith
On Sat, 26 Sep 2009, Jeff Janes wrote: On Sat, Sep 26, 2009 at 8:19 AM, Greg Smith gsm...@gregsmith.com wrote: Another problem spot are checkpoints. If you dirty a very large buffer cache, that whole thing will have to get dumped to disk eventually, and on some workloads people have found

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-25 Thread Dan Sugalski
At 12:36 AM -0400 9/25/09, Tom Lane wrote: Dan Sugalski d...@sidhe.org writes: Is there any practical limit to the number of shared buffers PG 8.3.7 can handle before more becomes counter-productive? Probably, but I've not heard any definitive measurements showing an upper limit. The

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-25 Thread Aidan Van Dyk
* Dan Sugalski d...@sidhe.org [090925 06:06]: I'll have to go check, but I think it does. This box hasn't actually hit swap since it started -- a good chunk of that RAM is used as semi-permanent disk cache but unfortunately the regular day-to-day use of this box (they won't let me have

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-25 Thread Scott Carey
[ai...@highrise.ca] Sent: Friday, September 25, 2009 6:33 AM To: Dan Sugalski Cc: Tom Lane; pgsql-performance@postgresql.org Subject: Re: [PERFORM] PG 8.3 and large shared buffer settings * Dan Sugalski d...@sidhe.org [090925 06:06]: I'll have to go check, but I think it does. This box hasn't

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-25 Thread Aidan Van Dyk
* Scott Carey sc...@richrelevance.com [090925 11:57]: That won't work well anyway because the postgres shared_buffers dos not cache things that are sequentially scanned (it uses a ring buffer for each scan). So, for any data that is only accessed by sequential scan, you're relying on the

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-25 Thread Jeff Janes
On Fri, Sep 25, 2009 at 8:53 AM, Scott Carey sc...@richrelevance.com wrote: That won't work well anyway because the postgres shared_buffers dos not cache things that are sequentially scanned (it uses a ring buffer for each scan).   So, for any data that is only accessed by sequential scan,

[PERFORM] PG 8.3 and large shared buffer settings

2009-09-24 Thread Dan Sugalski
Is there any practical limit to the number of shared buffers PG 8.3.7 can handle before more becomes counter-productive? I remember the buffer management algorithm used to get unhappy with too many buffers and past a certain point performance dropped with extra memory pitched at Postgres. My

Re: [PERFORM] PG 8.3 and large shared buffer settings

2009-09-24 Thread Tom Lane
Dan Sugalski d...@sidhe.org writes: Is there any practical limit to the number of shared buffers PG 8.3.7 can handle before more becomes counter-productive? Probably, but I've not heard any definitive measurements showing an upper limit. The traditional wisdom of limiting it to 1G or so dates