Search Postgresql Archives

Re: Multixact members limit exceeded

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Aug 9, 2017 at 10:06 PM, Peter Hunčár <hunci@xxxxxxxx> 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@xxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux