Horton

Re: Should vacuum process config file reload more often

Melanie Plageman <melanieplageman@gmail.com>

View thread View on postgr.es
From: Melanie Plageman <melanieplageman@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>, Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>, Masahiko Sawada <sawada.mshk@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Amit Kapila <amit.kapila16@gmail.com>
Date: 2023-04-05T19:43:59Z
Lists: pgsql-hackers 
On Wed, Apr 5, 2023 at 2:56 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> + /*
> + * Balance and update limit values for autovacuum workers. We must
> + * always do this in case the autovacuum launcher or another
> + * autovacuum worker has recalculated the number of workers across
> + * which we must balance the limit. This is done by the launcher when
> + * launching a new worker and by workers before vacuuming each table.
> + */
>
> I don't quite understand what's going on here. A big reason that I'm
> worried about this whole issue in the first place is that sometimes
> there's a vacuum going on a giant table and you can't get it to go
> fast. You want it to absorb new settings, and to do so quickly. I
> realize that this is about the number of workers, not the actual cost
> limit, so that makes what I'm about to say less important. But ... is
> this often enough? Like, the time before we move onto the next table
> could be super long. The time before a new worker is launched should
> be ~autovacuum_naptime/autovacuum_max_workers or ~20s with default
> settings, so that's not horrible, but I'm kind of struggling to
> understand the rationale for this particular choice. Maybe it's fine.

VacuumUpdateCosts() also calls AutoVacuumUpdateCostLimit(), so this will
happen if a config reload is pending the next time vacuum_delay_point()
is called (which is pretty often -- roughly once per block vacuumed but
definitely more than once per table).

Relevant code is at the top of vacuum_delay_point():

    if (ConfigReloadPending && IsAutoVacuumWorkerProcess())
    {
        ConfigReloadPending = false;
        ProcessConfigFile(PGC_SIGHUP);
        VacuumUpdateCosts();
    }

- Melanie

Commits

  1. Fix assertion failure in heap_vacuum_rel

  2. Fix vacuum_cost_delay check for balance calculation.

  3. Fix autovacuum cost debug logging

  4. Refresh cost-based delay params more frequently in autovacuum

  5. Separate vacuum cost variables from GUCs

  6. Make vacuum failsafe_active globally visible

  7. Don't balance vacuum cost delay when per-table settings are in effect