Re: [GENERAL] Multixact members limit exceeded - Mailing list pgsql-general
| From | Peter Hunčár |
|---|---|
| Subject | Re: [GENERAL] Multixact members limit exceeded |
| Date | |
| Msg-id | CADOjABkHbFvbEoLcXfnA7HqWPR4t0MQ=fwLM3BCFmRGNLTnDSw@mail.gmail.com Whole thread Raw |
| In response to | Re: [GENERAL] Multixact members limit exceeded (Andres Freund <andres@anarazel.de>) |
| List | pgsql-general |
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@anarazel.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
pgsql-general by date: