Re: foreign key locks, 2nd attempt

On Wed, Mar 14, 2012 at 9:17 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
>> > Agreed.  But speaking of that, why exactly do we fsync the multixact SLRU today?
>>
>> Good question.  So far, I can't think of a reason.  "nextMulti" is critical,
>> but we already fsync it with pg_control.  We could delete the other multixact
>> state data at every startup and set OldestVisibleMXactId accordingly.
>
> Hmm, yeah.

In a way, the fact that we don't do that is kind of fortuitous in this
situation. I had just assumed that we were not fsyncing it because
there seems to be no reason to do so. But since we are, we already
know that the fsyncs resulting from frequent mxid allocation aren't a
huge pain point. If they were, somebody would have presumably
complained about it and fixed it before now. So that means that what
we're really worrying about here is the overhead of fsyncing a little
more often, which is a lot less scary than starting to do it when we
weren't previously.

Now, we could look at this as an opportunity to optimize the existing
implementation by removing the fsyncs, rather than adding the new
infrastructure Alvaro is proposing. But that would only make sense if
we thought that getting rid of the fsyncs would be more valuable than
avoiding the blocking here, and I don't.

I still think that someone needs to do some benchmarking here, because
this is a big complicated performance patch, and we can't predict the
impact of it on real-world scenarios without testing. There is
clearly some additional overhead, and it makes sense to measure it and
hopefully discover that it isn't excessive. Still, I'm a bit
relieved.

> I have noticed that this code is not correct, because we don't know that
> we're holding an appropriate lock on the page, so we can't simply change
> the Xmax and reset those hint bits.  As things stand today, mxids
> persist longer.  (We could do some cleanup at HOT-style page prune, for
> example, though the lock we need is even weaker than that.)  Overall
> this means that coming up with a test case demonstrating this pressure
> probably isn't that hard.

What would such a test case look like?

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