Re: The shared buffers challenge

On Thu, May 26, 2011 at 6:10 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Merlin Moncure wrote:
>>
>> So, the challenge is this: I'd like to see repeatable test cases that
>> demonstrate regular performance gains > 20%.  Double bonus points for
>> cases that show gains > 50%.
>
> Do I run around challenging your suggestions and giving you homework?  You
> have no idea how much eye rolling this whole message provoked from me.

That's just plain unfair: I didn't challenge your suggestion nor give
you homework. In particular, I'm not suggesting the 25%-ish default is
wrong -- but trying to help people understand why it's there and what
it's doing. I bet 19 people out of 20 could not explain what the
primary effects of shared_buffers with any degree of accuracy. That
group of people in fact would have included me until recently, when I
started studying bufmgr.c for mostly unrelated reasons. Understand my
basic points:

*) the documentation should really explain this better (in particular,
it should debunk the myth 'more buffers = more caching')
*) the 'what' is not nearly so important as the 'why'
*) the popular understanding of what buffers do is totally, completely, wrong
*) I'd like to gather cases to benchmark changes that interact with
these settings
*) I think you are fighting the 'good fight'. I'm trying to help

> OK, so the key thing to do is create a table such that shared_buffers is
> smaller than the primary key index on a table, then UPDATE that table
> furiously.  This will page constantly out of the buffer cache to the OS one,
> doing work that could be avoided.  Increase shared_buffers to where it fits
> instead, and all the index writes are buffered to write only once per
> checkpoint.  Server settings to exaggerate the effect:

This is exactly what I'm looking for...that's really quite striking.
I knew that buffer 'hit' before it goes out the door is what to gun
for.

> As for figuring out how this impacts more complicated cases, I hear somebody
> wrote a book or something that went into pages and pages of detail about all
> this.  You might want to check it out.

so i've heard: http://imgur.com/lGOqx (and yes: I 100% endorse the
book: everyone who is serious about postgres should own a copy).
Anyways, double points to you ;-).

merlin