Re: reducing the overhead of frequent table locks - now, with WIP patch

On Mon, Jun 6, 2011 at 8:50 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Jun 6, 2011 at 8:40 PM, Stefan Kaltenbrunner
> <stefan(at)kaltenbrunner(dot)cc> wrote:
>> On 06/06/2011 09:24 PM, Dave Page wrote:
>>> On Mon, Jun 6, 2011 at 8:12 PM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
>>>> So, to the question “do we want hard deadlines?” I think the answer is
>>>> “no”, to “do we need hard deadlines?”, my answer is still “no”, and to
>>>> the question “does this very change should be considered this late?” my
>>>> answer is yes.
>>>>
>>>> Because it really changes the game for PostgreSQL users.
>>>
>>> Much as I hate to say it (I too want to keep our schedule as
>>> predictable and organised as possible), I have to agree. Assuming the
>>> patch is good, I think this is something we should push into 9.1. It
>>> really could be a game changer.
>>
>> I disagree - the proposed patch maybe provides a very significant
>> improvment for a certain workload type(nothing less but nothing more),
>> but it was posted way after -BETA and I'm not sure we yet understand all
>> implications of the changes.
>
> We certainly need to be happy with the implications if we were to make
> such a decision.
>
>> We also have to consider that the underlying issues are known problems
>> for multiple years^releases so I don't think there is a particular rush
>> to force them into a particular release (as in 9.1).
>
> No, there's no *technical* reason we need to do this, as there would
> be if it were a bug fix for example. I would just like to see us
> narrow the gap with our competitors sooner rather than later, *if*
> we're a) happy with the change, and b) we're talking about a minimal
> delay (which we may be - Robert says he thinks the patch is good, so
> with another review and beta testing....).

Stefan/Robert's observation that we perform a
VirtualXactLockTableInsert() to no real benefit is a good one.

It leads to the following simple patch to remove one lock table hit
per transaction. It's a lot smaller impact on the LockMgr locks, but
it will still be substantial. Performance tests please?

This patch is much less invasive and has impact only on CREATE INDEX
CONCURRENTLY and Hot Standby. It's taken me about 2 hours to write and
test and there's no way it will cause any delay at all to the release
schedule. (Though I'm sure Robert can improve it).

If we combine this patch with Koichi-san's recommended changes to the
number of lock partitions, we will have considerable impact for 9.1.
Robert will still get his day in the sun, just with 9.2.

This way we get something now *and* something later, while the risk
minimisers will have succeeded in protecting the code. A compromise
for everyone.

Please consider this as a serious proposal for tuning in 9.1.

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