Re: Multixact members limit exceeded

Thomas Munro wrote:

> One thing I noticed is that there are ~4 billion members (that's how
> many you have when you run out of member space), but only ~128 million
> multixacts, so I think the average multixact has ~32 members.
> Considering the way that multixacts grow by copying and extending by
> one, by the time you've created a multixact with n members you've
> eaten a total of n! member space with an average size of n/2 per
> multixact... So one way to hit that average would be to repeatedly
> build ~64 member multixacts, or if mixed with smaller ones then you'd
> need to be intermittently building even larger ones. A thundering
> herd of worker processes repeatedly share-locking the same row or
> something like that?

Note that the behavior of 9.5 is quite different from that of 9.3/9.4;
in the older releases there is some nasty behavior involving multiple
subtransactions of the same transaction grabbing a lock on the same
tuple, whereby the multixact grows without bound. In 9.5 we fixed that.
There's a patch in the thread for bug #8470 that fixes it for 9.3 and
probably it can be applied to 9.4 too, not sure. You can see it here:
https://www.postgresql.org/message-id/20150410161704.GH4369@alvh.no-ip.org
Look for the "simple" formulation. I don't think I posted an updated
version later, but it was never applied.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services