Re: Reducing power consumption on idle servers

On 2/21/22 10:11 AM, Simon Riggs wrote:

* autovac launcher - autovacuum_naptime

On production systems autovacuum_naptime often can't be a large value,
otherwise it's easy to not keep up on small busy tables. That's fine for
actually busy servers, but with the increase in hosted PG offerings, the
defaults in those offerings needs to cater to a broader audience.

Autovac varies its wakeup cycle according to how much work is done. It
is OK to set autovacuum_naptime without affecting power consumption
when idle.

I'm wondering how many people understand that. I've seen a number of servers running very low values of autovacuum_naptime in order to make things more responsive.

Idle for autovac is defined slightly differently, since if all user
work completes then there may still be a lot of vacuuming to do before
it goes fully idle. But my observation is that there are many servers
that go idle for more than 50% of each week, when operating 8-12 hours
per day, 5 days per week, so we can still save a lot of power.

This patch doesn't change how autovac works, it just uses a common
setting for the hibernation that eventually occurs.

I'm wondering if it'd be worth linking autovac wakeup from a truly idle state to the stats collector. If there's no stats messages coming in clearly there's nothing new for autovac.