Re: foreign key locks, 2nd attempt

On Tue, Mar 13, 2012 at 01:46:24PM -0400, Robert Haas wrote:
> On Mon, Mar 12, 2012 at 3:28 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > I agree with you that some worst case performance tests should be
> > done. Could you please say what you think the worst cases would be, so
> > those can be tested? That would avoid wasting time or getting anything
> > backwards.
>
> I've thought about this some and here's what I've come up with so far:
>
> 1. SELECT FOR SHARE on a large table on a system with no write cache.

Easy enough that we may as well check it. Share-locking an entire large table
is impractical in a real application, so I would not worry if this shows a
substantial regression.

> 2. A small parent table (say 30 rows or so) and a larger child table
> with a many-to-one FK relationship to the parent (say 100 child rows
> per parent row), with heavy update activity on the child table, on a
> system where fsyncs are very slow. This should generate lots of mxid
> consumption, and every 1600 or so mxids (I think) we've got to fsync;
> does that generate a noticeable performance hit?

More often than that; each 2-member mxid takes 4 bytes in an offsets file and
10 bytes in a members file. So, more like one fsync per ~580 mxids. Note
that we already fsync the multixact SLRUs today, so any increase will arise
from the widening of member entries from 4 bytes to 5. The realism of this
test is attractive. Nearly-static parent tables are plenty common, and this
test will illustrate the impact on those designs.

> 3. It would be nice to test the impact of increased mxid lookups in
> the parent, but I've realized that the visibility map will probably
> mask a good chunk of that effect, which is a good thing. Still, maybe
> something like this: a fairly large parent table, say a million rows,
> but narrow rows, so that many of them fit on a page, with frequent
> reads and occasional updates (if there are only reads, autovacuum
> might end with all the visibility map bits set); plus a child table
> with one or a few rows per parent which is heavily updated. In theory
> this ought to be good for the patch, since the the more fine-grained
> locking will avoid blocking, but in this case the parent table is
> large enough that you shouldn't get much blocking anyway, yet you'll
> still pay the cost of mxid lookups because the occasional updates on
> the parent will clear VM bits. This might not be the exactly right
> workload to measure this effect, but if it's not maybe someone can
> devote a little time to thinking about what would be.

You still have HEAP_XMAX_{INVALID,COMMITTED} to reduce the pressure on mxid
lookups, so I think something more sophisticated is needed to exercise that
cost. Not sure what.

> 4. A plain old pgbench run or two, to see whether there's any
> regression when none of this matters at all...

Might as well.

> This isn't exactly a test case, but from Noah's previous comments I
> gather that there is a theoretical risk of mxid consumption running
> ahead of xid consumption. We should try to think about whether there
> are any realistic workloads where that might actually happen. I'm
> willing to believe that there aren't, but not just because somebody
> asserts it. The reason I'm concerned about this is because, if it
> should happen, the result will be more frequent anti-wraparound
> vacuums on every table in the cluster. Those are already quite
> painful for some users.

Yes. Pre-release, what can we really do here other than have more people
thinking about ways it might happen in practice? Post-release, we could
suggest monitoring methods or perhaps have VACUUM emit a WARNING when a table
is using more mxid space than xid space.

Also consider a benchmark that does plenty of non-key updates on a parent
table with no activity on the child table. We'll pay the overhead of
determining that the key column(s) have not changed, but it will never pay off
by preventing a lock wait. Granted, this is barely representative of
application behavior. Perhaps, too, we already have a good sense of this cost
from the HOT benchmarking efforts and have no cause to revisit it.

Thanks,
nm