Re: Impact of multixact "members" limit exceeded

Hello Álvaro,

I'm running at 9.4.3, I know its an older version but upgrading it outside
the scope right now for various reasons.
Based on the settings I posted what would be your recommended settings
right now to resolve this situation?

Maintenance is limited to weekends as this is a database which needs to be
online 24/5 and consumes a lot of data and responds to many queries.

I very much appreciate the recommendation but I would also like to now the
impact of this warning, data right now does not seem to be corrupted, get
overwritten, for that matter not insert new data or cause issues when read.
It just seems that is has stopped creating "members" and I can't read into
what problem that would cause, table was around 1.6 TB, it was cleaned up
and the re-packed using pg-repack to go down to around 400 GB in size,
however a freeze vacuum or autovacuum did not complete.

Then when it comes to vacuum, do you think its worth increasing work_mem to
say 200 GB, for the maintenance period only, and let autovacuum go to town
on the tables with the highest age and lowest minmixid? Or should one run
for example flexible freeze instead with zero pauses as nothing is
happening on the weekends, which means no inbound transactions. Also worth
mentioning is thatI restart the server on the weekend after I have backed
up the data.

I have tried running autovacuum for 40 hours before with 5 GB work_mem, on
the weekend, across 6 tables at the same time and it never completed on the
bigger tables which are around 2 TB.

Best regards

Daniel

On Wed, Jun 13, 2018 at 11:29 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> On 2018-Jun-13, Daniel Lagerman wrote:
>
> > Hello!
> >
> > I have a question where I'm trying to determine the seriousness of the
> > problem.
>
> What's your server version?
>
> The way to reclaim members space is to move forwards the
> pg_database.datminmxid value from the database where it is oldest; and
> the way to move *that* forward is to advance the pg_class.relminmxid
> value from the tables in which it is oldest. So you need to set a low
> value vacuum_multixact_freeze_min_age and _table_age and vacuum those
> tables.
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>