Re: Multixact members limit exceeded

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 :(

name | setting
-------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | 10
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
log_autovacuum_min_duration | -1
vacuum_cost_delay | 0
vacuum_cost_limit | 2000
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
vacuum_defer_cleanup_age | 0
vacuum_freeze_min_age | 50000000
vacuum_freeze_table_age | 150000000
vacuum_multixact_freeze_min_age | 3000000
vacuum_multixact_freeze_table_age | 140000000

The default vacuum_multixact_freeze_min_age is 5M according to the doc, but
according to the documentation, it's limited silently to half of
autovacuum_multixact_freeze_max_age.
So I guess I have to get those 400M to much lower number?

Thank you very much

Peter

On Wed, Aug 9, 2017 at 10:39 PM Andres Freund <andres(at)anarazel(dot)de> wrote:

> 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 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?
>
> FWIW, VACUUM FULL isn't a good tool to use here. It's commonly way more
> expensive than a normal vacuum (it rewrites the entire table, and
> rebuilds indexes).
>
>
> > But it did change ... the problem is that it didn't change enough (only
> > 4000 multixacts). Maybe your multixact freeze min age is too high?
> > Getting rid of 15 GB of bloat is a good side effect, though, I'm sure.
>
> I wonder if there's longrunning transactions preventing cleanup. I
> suggest checking pg_stat_activity, pg_prepared_xacts,
> pg_replication_slot for older stuff.
>
> Greetings,
>
> Andres Freund
>