Amit Khandekar wrote:
> In a server where autovacuum is disabled and its databases reach
> autovacuum_freeze_max_age limit, an autovacuum is forced to prevent
> xid wraparound issues. At this stage, when the server is loaded with a
> lot of DML operations, an exceedingly high number of autovacuum
> workers keep on getting spawned, and these do not do anything, and
> then quit.
I think this is the same problem as reported in
https://www.postgresql.org/message-id/CAMkU=1yE4YyCC00W_GcNoOZ4X2qxF7x5DUAR_kMt-Ta=YPyFPQ@mail.gmail.com
> === Fix ===
[...]
> Instead, the attached patch (prevent_useless_vacuums.patch) prevents
> the repeated cycle by noting that there's no point in doing whatever
> vac_update_datfrozenxid() does, if we didn't find anything to vacuum
> and there's already another worker vacuuming the same database. Note
> that it uses wi_tableoid field to check concurrency. It does not use
> wi_dboid field to check for already-processing worker, because using
> this field might cause each of the workers to think that there is some
> other worker vacuuming, and eventually no one vacuums. We have to be
> certain that the other worker has already taken a table to vacuum.
Hmm, it seems reasonable to skip the end action if we didn't do any
cleanup after all. This would normally give enough time between vacuum
attempts for the first worker to make further progress and avoid causing
a storm. I'm not really sure that it fixes the problem completely, but
perhaps it's enough.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services