Re: foreign key locks, 2nd attempt

On Thu, Mar 15, 2012 at 2:26 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

Good

> 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.

This is not an exercise in tuning mxact code. There is a serious
algorithmic problem that is causing real world problems.

Removing the fsync will *not* provide a solution to the problem, so
there is no "opportunity" here.

> 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.

You're right that the existing code could use some optimisation.

I'm a little tired, but I can't see a reason to fsync this except at checkpoint.

Also seeing that we issue 2 WAL records for each RI check. We issue
one during MultiXactIdCreate/MultiXactIdExpand and then immediately
afterwards issue a XLOG_HEAP_LOCK record. The comments on both show
that each thinks it is doing it for the same reason and is the only
place its being done. Alvaro, any ideas why that is.

> 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.

Very much agreed.

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