A follow up --
<...>
>>> a) quickly relieve the immediate pain
>>
>> Set autovacuum_cost_limit to a smaller value. (Change the
>> postgresql.conf file and do a reload.) I would start by cutting the
>> current value in half.
>> >
>Thanks -- would not have known how to start. But for now, in
>observance of your idea on XID wraparound, I am letting
>everything run (this is actually a low point in regular traffic for this
>site and I am not seeing direct proof that autovac is in fact doing
>anything out of the ordinary).
>
While sleuthing logs for some data these 3 processes began to finish
so the immediate issue went away. We'll make these changes next
week I suspect. Lig searches showed that the vast majority of our
autovacs were on replication related tables in ordinary processing
so reducing the max number autovacs was not to be contemplated.
>>> and
>>>
>>> b) prevent such issues in the future (other than going to manual
>>> vacuuming on a schedule).
>>
>> If it is suddenly doing this on lots of big tables at once, it seems
>> somewhat likely that you've hit the transaction wraparound
>> protection threshold. Because the vacuum necessary for this can be
>> painful, and they tend to come at the worst possible time (the more
>> your workload looks like a really heavy OLTP workload at any given
>> moment, the higher the probability that this is about to happen), I
>> always follow a bulk load (like from restoring pg_dump output) with
>> a VACUUM FREEZE ANALYZE.
>>
>> You might also want to consider running off-hours vacuums to
>> supplement autovacuum. Upgrading to a more recent version of
>> PostgreSQL is likely to help some, too.
>>
>Duly noted.
The autovac processes were not the core problem, natch. Someone deleted
50000 rows from one table, generating larger deletes in the two other tables,
which in turn provoked replication issues, all of which got autovac's attention.
Given time everything calmed down and a lesson learned has been inscribed.
Don't do mass deletes -- throttle them back, one way or another. If you must
tread this path make some adjustments to relevant tables and values first.
Thanks, Kevin, for the quick response.
Greg W.
