Re: foreign key locks, 2nd attempt - Mailing list pgsql-hackers

On Tue, Mar 13, 2012 at 11:42 PM, Noah Misch <noah@leadboat.com> wrote:
> 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.

Agreed.  But speaking of that, why exactly do we fsync the multixact SLRU today?

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

I don't think HEAP_XMAX_COMMITTED is much help, because committed !=
all-visible.  HEAP_XMAX_INVALID will obviously help, when it happens.

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

Well, post-release, the cat is out of the bag: we'll be stuck with
this whether the performance characteristics are acceptable or not.
That's why we'd better be as sure as possible before committing to
this implementation that there's nothing we can't live with.  It's not
like there's any reasonable way to turn this off if you don't like it.

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

Good idea.

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