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