Re: Fixed xloginsert_locks for 9.4

On 2014-10-03 12:40:21 -0400, Bruce Momjian wrote:
> On Fri, Oct  3, 2014 at 04:11:30PM +0200, Andres Freund wrote:
> > On 2014-10-03 10:07:39 -0400, Gregory Smith wrote:
> > > On 10/3/14, 8:26 AM, Andres Freund wrote:
> > > >#define NUM_XLOGINSERT_LOCKS  1
> > > >tps = 52.711939 (including connections establishing)
> > > >#define NUM_XLOGINSERT_LOCKS  8
> > > >tps = 286.496054 (including connections establishing)
> > > >#define NUM_XLOGINSERT_LOCKS  16
> > > >tps = 346.113313 (including connections establishing)
> > > >#define NUM_XLOGINSERT_LOCKS  24
> > > >tps = 363.242111 (including connections establishing)
> > > 
> > > Just to clarify:  that 10% number I threw out was meant as a rough estimate
> > > for a system with the default configuration, which is all that I tested.  It
> > > seemed like people would likely need to tune all the usual things like
> > > checkpoint_segments, shared_buffers, etc. as well before seeing much better.
> > > You did all that, and sure enough the gain went up; thanks for confirming my
> > > guess.
> > > 
> > > I still don't think that means this needs a GUC for 9.4.  Look at that jump
> > > from 1 to 8.  The low-hanging fruit here hasn't just been knocked off.  It's
> > > been blended into a frozen drink, poured into a glass, and had a little
> > > paper umbrella put on top.  I think that's enough for 9.4.  But, yes, let's
> > > see if we can add delivery to the side of the pool in the next version too.
> > 
> > So 25% performance on a relatively small machine improvements aren't
> > worth a GUC? That are likely to be larger on a bigger machine?
> > 
> > I utterly fail to see why that's a service to our users.
> 
> Well, I think the issue is that having a GUC that can't reasonably be
> tuned by 95% of our users is nearly useless.  Few users are going to run
> benchmarks to see what the optimal value is.

Sure. And the default loooks to be a good one. So it's not bad that they
don't tune it further.
But. How are we ever going to be able to tune it further without
feedback from actual production usage?

It's possible to convince customers to play with a performance
influencing parameter and see how the results are. Even in
production. It's near impossible to do so if that requires to download
source packages, change a define in some .c file, rebuild the packages,
distribute them to the respective servers. And then continue to do so
for every release thereafter.

Not only is that a *significant* amount of work. It often involves a
different set of people (sysadmin, not dba-ish people). And often
complicated procedures to allow patching the source code at all.

> I remember Informix had a setting that had no description except "try
> different values to see if it helps performance" --- we don't want to do
> that.

I'm pretty sure we can come up with a better description than that.

> What if we emit a server message if the setting is too low?  That's how
> we handle checkpoint_segments.

I don't think it's realistically possible in this case. The
instrumentation we'd need to add would be too expensive for something
running as frequently as this.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services