On Thu, May 26, 2011 at 6:10 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
