Re: [GENERAL] Multixact members limit exceeded

2017-08-24 Thread Peter Hunčár
Hello it took 10 days to autovacuum the 32TB toast table and after restarting with previous (but slightly tuned autovacuum) parameters, the database works just fine. The data import is almost done, there are just several hundred thousands of rows to add :) The next headache will the the backup

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Thomas Munro
On Thu, Aug 10, 2017 at 10:26 AM, Thomas Munro wrote: > eaten a total of n! member space with an average size of n/2 per Erm, math fail, not n! but 1 + 2 + ... + n. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-general mailing list

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Alvaro Herrera
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

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Thomas Munro
On Wed, Aug 9, 2017 at 10:06 PM, Peter Hunčár 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

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Peter Hunčár
Hello, there are currently no transactions whatsoever, the app is paused. I can even restart the database if needed. I ran vacuum full, because as I mentioned above it seemed to me that manual vacuum did not change the relminmxid of a table. Unfortunately, an upgrade is not an option :(

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Andres Freund
Hi, On 2017-08-09 16:30:03 -0400, Alvaro Herrera wrote: > > One particular table before vacuum full: > > > >relname| relminmxid | table_size > > --++ > > delayed_jobs | 1554151198 | 21 GB > > > > And

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Alvaro Herrera
Peter Hunčár wrote: > Hi, > > 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. I bet you have a lot of subtransactions -- maybe a plpgsql block with an EXCEPTION clause that's doing something

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Peter Hunčár
Hi, 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

Re: [GENERAL] Multixact members limit exceeded

2017-08-09 Thread Andres Freund
Hi, On 2017-08-09 10:06:48 +, Peter Hunčár wrote: > We started feeding it several weeks ago and everything went smoothly until > we hit this issue: > > 2017-08-09 05:21:50.946 WIB >DETAIL: This command would create a multixact > with 2 members, but the remaining space is only enough for 0