Re: Multixact members limit exceeded

On Wed, Aug 9, 2017 at 10:06 PM, Peter Hunčár <hunci(at)hunci(dot)sk> wrote:
> SELECT relname, age(relminmxid) as mxid_age,

I'm pretty sure you can't use age(xid) to compute the age of a
multixact ID. Although they have type xid in pg_class and the
age(xid) function will happily subtract your multixact ID from the
next xid, the answer is meaningless. mxid_age is the function you
want, though I can't remember if it's present in 9.4.

On Thu, Aug 10, 2017 at 7:31 AM, Peter Hunčár <hunci(at)hunci(dot)sk> wrote:
> Thank you, yes those are the 'urgent' tables, I'd talk to the developers
> regarding the locks.I too think, there's something 'fishy' going on.
>
> Anyway, could it be that autovacuum blocks manual vacuum? Because I ran
> vacuum (full, verbose) and some tables finished quite fast, with huge amount
> of io recorded in the monitoring, but some of them are kind of stuck?
> Which brings me to the second question, how can I cancel autovacuum?
>
> And the last thing I don't understand:
>
> One particular table before vacuum full:
>
> relname | relminmxid | table_size
> ------------------------------+------------+------------
> delayed_jobs | 1554151198 | 21 GB
>
> And after vacuum full:
>
> relname | relminmxid | table_size
> --------------+------------+------------
> delayed_jobs | 1554155465 | 6899 MB
>
> Shouldn't be the relminmxid changed after vacuum full, or am I not
> understanding something?

How can delayed_jobs have relminmxid = 1,554,155,465? According to
your pg_controldata output, the active range of multixact IDs in your
cluster is 11,604 -> 129,346,573, so there is some confusion here.
Perhaps this is the output of an expression involving
age(multixactid)?

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?

--
Thomas Munro
http://www.enterprisedb.com