preventing ERROR: multixact "members" limit exceeded

From: Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com>
To: Forums postgresql <pgsql-general(at)postgresql(dot)org>
Subject: preventing ERROR: multixact "members" limit exceeded
Date: 2016-05-17 00:14:46
Message-ID: CA+bfosFU8pCP9nJJW9_krad-0URhFHfmz+GrZGCB5TkNjjzyRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is Postgres 9.4.4. Custom settings are [in this gist](
https://gist.github.com/skehlet/47c7f92daa0bd3d1a3aee2bb001da140)

This is a new one for me, one of our bigger (~2.2TB) databases started
having the following error:

> Caused by: org.postgresql.util.PSQLException: ERROR: multixact "members"
limit exceeded
> Detail: This command would create a multixact with 2 members, but the
remaining space is only enough for 0 members.
> Hint: Execute a database-wide VACUUM in database with OID 33554 with
reduced vacuum_multixact_freeze_min_age and
vacuum_multixact_freeze_table_age settings.

We followed those VACUUM instructions, and now everything's back to normal.

Now it's just about preventing this. Our best guess at this point is the
autovacuums aren't working fast enough. Sure enough this instance has our
old values for:
autovacuum_vacuum_cost_delay: 20ms
autovacuum_vacuum_cost_limit: 200

We've since started using:
autovacuum_vacuum_cost_delay: 10ms
autovacuum_vacuum_cost_limit: 2000

We'll be updating those settings as soon as possible.

Just looking for some expert eyes on this problem. Are we on the track
track? I.e. is making the autovacuumer run more aggressively our best bet
to avoid this issue?

Thank you,

Steve

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Elusai Soares 2016-05-17 00:56:44 How to convert firebird stored procedures into postgresql functions
Previous Message Scott Moynes 2016-05-16 22:54:35 Re: WAL files not being recycled