Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: >> Merlin Moncure <mmoncure@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
