From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com> |
Cc: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: preventing ERROR: multixact "members" limit exceeded |
Date: | 2016-05-17 01:18:45 |
Message-ID: | 20160517011845.GA125149@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Steve Kehlet wrote:
> 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?
Not really. Your best bet is to reduce the
autovacuum_multixact_freeze_min_age limit, so that vacuums are able to
get rid of multixacts sooner (and/or reduce
autovacuum_multixact_freeze_table_age, so that whole-table vacuuming
takes place earlier). You may need to decrease the cost_delay too
(and/or increase the cost_limit) in order for autovac to be able to keep
up, but really that's more a side effect because of a possible need for
autovac to do more in the same period of time.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2016-05-17 01:31:30 | Re: WAL files not being recycled |
Previous Message | Elusai Soares | 2016-05-17 00:56:44 | How to convert firebird stored procedures into postgresql functions |