Re: The shared buffers challenge

Merlin Moncure <mmoncure@gmail.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> Merlin Moncure <mmoncure@gmail.com> 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%.
>>
>> Are you talking throughput, maximum latency, or some other
>> metric?
>
> I am talking about *any* metric..you've got something, let's see
> it.  But it's got to be verifiable, so no points scored.

Oh, that wasn't to score points; just advocating for more than a
one-dimensional view of performance.  I'm adding to your demands,
not attempting to satisfy them.  :-)

> See my note above about symptoms -- if your symptom of note
> happens to be unpredictable spikes in fast query times under load,
> then I'd like to scribble that advice directly into the docs along
> with (hopefully) some reasoning of exactly why more database
> managed buffers are helping.

In our case it was *fewer* shared_buffers which helped.

> As noted, I'm particularly interested in things we can test
> outside of production environments, since I'm pretty skeptical the
> Wisconsin Court System is going to allow the internet to log in
> and repeat and verify test methodologies.

Right, while it was a fairly scientific and methodical test, it was
against a live production environment.  We adjusted parameters
incrementally, a little each day, from where they had been toward
values which were calculated in advance to be better based on our
theory of the problem (aided in no small part by analysis and advice
from Greg Smith), and saw a small improvement each day with the
problem disappearing entirely right at the target values we had
calculated in advance.  :-)

> Point being: cranking buffers may have been the bee's knees with,
> say, the 8.2 buffer manager, but present and future improvements
> may have render that change moot or even counter productive.

We did find that in 8.3 and later we can support a larger
shared_buffer setting without the problem than in 8.2 and earlier.
We still need to stay on the low side of what is often advocated to
keep the failure rate from this issue at zero.

> all else being equal, the lowest shared_buffers setting possible
> without sacrificing performance is best because it releases more
> memory to the o/s to be used for other things

I absolutely agree with this.

I think the problem is that it is very tedious and time-consuming to
construct artificial tests for these things.  Greg Smith has spent a
lot of time and done a lot of research investigating the dynamics of
these issues, and recommends a process of incremental adjustments
for tuning the relevant settings which, in my opinion, is going to
be better than any generalized advice on settings.

Don't get me wrong, I would love to see numbers which "earned
points" under the criteria you outline.  I would especially love it
if they could be part of the suite of tests in our performance farm.
I just think that the wealth of anecdotal evidence and the dearth of
repeatable benchmarks in this area is due to the relatively low-cost
techniques available to tune production systems to solve pressing
needs versus the relatively high cost of creating repeatable test
cases (without, by the way, solving an immediate need).

-Kevin