Re: High CPU load caused by the autovacuum launcher process

Owayss Kabtoul <owayssk(at)gmail(dot)com> writes:
> I ran into an issue where, on Postgres instances that have a very large
> number of databases per cluster (~15K), the autovacuum process seems to
> have a very high impact on CPU usage.

That's, um, not a very reasonable configuration.

> So auto-vacuum never really sleeps. Even changing the autovacuum_naptime
> and setting it to a much higher value (from 1min to 50min) did not have any
> effect at all.

Simple arithmetic says that even at 50min, the launcher will have to
launch one worker every 0.2 seconds in order to visit every database
once per naptime.

You could raise the naptime setting by another factor of 10 and then
the launcher would only have to wake up every other second ... but
these DBs had better all have extremely light workloads, or you're
risking terrible bloat due to tables only getting vacuumed a couple
times per day.

On the whole, I'd revisit why you need so many DBs per cluster.
Quite aside from autovacuum performance issues, you're expending
~100GB just on redundant copies of the system catalogs; that can't
be helping disk cache performance, for instance. (Or, if you've
got a box that's so beefy you just don't care about that, why are
you worried about the launcher?)

regards, tom lane