Re: Fixed xloginsert_locks for 9.4

On Fri, Oct 3, 2014 at 2:49 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> I stand by my decision to make it a #define, at least until someone voices
> their objection in the form of a documentation patch.

I think that's exactly right. If we knew users should tune this, then
we might be able to make it self-tuning, which would be best of all.
At the least, we'd have useful information to provide to people who
want to change it. However, if we *can't* provide tuning advice, then
all making it a GUC does is give users a knob that says "turning this
might make things better! or worse!". Adding such knobs does little
harm individually, but in the aggregate it makes for a product that is
mysterious, hard to use, and hard to maintain and improve.

In practice, I suspect few people will get the amount of benefit that
Andres saw in his tests. You have to be xloginsert-bound, and a lot
of workloads aren't. And if they were before, they aren't now that we
have 8 xloginsert slots by default.

My suspicion, which I think is what Andres was getting at as well, is
that you'll want more slots if you have more CPUs. One way to measure
the effectiveness of a given value (and maybe even auto-tune) would be
to count how often you run out of slots. Of course, you'd need some
countervailing form of pressure based on the effort that you're
spending locking however many you have rather than some smaller
number. You can't just measure the upside without measuring the
downside.

The xlog slots use the kind of algorithm that I quite frankly hate
even when it works. It avoids one kind of loss but in a way that's
not scalable; as you crank up the number of slots you start to incur
cost elsewhere. Getting any more scalability beyond that point will
require doing something fundamentally different, or at least that's my
guess. So I feel like we've put our finger in the dike more than
anything, but that still beats letting the dike collapse.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company