Re: autovacuum: automatically propagate updated parameters

Hi,

Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> While it's a good idea to wake the leader up instead of polling, I'm a
> bit concerned that we call SetLatch() on all autovacuum workers
> participating in cost balancing, whereas we need it only in a narrow
> situation: when the worker is participating in cost balancing, using
> parallel vacuum, and waiting for its parallel workers to finish.

I wanted to know how narrow, so I logged every SetLatch() that v3 adds,
with the target's wait event and whether its latch was already set
(the log line is in the attachment, on top of v3, REL_19_STABLE
e60ee52841d).

Load: autovacuum_max_workers = 3, three databases with the same 120
small tables each (every third table with its own
autovacuum_vacuum_cost_limit, so it is not in the balance) plus one
table with three indexes and autovacuum_parallel_workers = 2, updated
in rounds for 120 s. That gave 14506 table vacuums, 28 of them
parallel.

SetLatch() calls: 10066
on the calling worker itself: 4871
on another worker: 5195
waiting in ParallelFinish: 235
in VacuumDelay: 4635
anything else: 325
other-worker latch already set: 4724
ParallelFinish latch already set: 1

Workers made 10001 of the calls and the launcher 65. The count moves
every time a worker starts a table that is in the balance or not, often
2 -> 3 -> 2 within a few milliseconds, and v3 turns each move into a
SetLatch() on every balanced worker.

So you are right that almost none of them are needed: 235 out of 5195
reach a leader that is waiting. The cost is smaller than the count
suggests, though. A worker that is vacuuming sleeps with pg_usleep()
and does not reset its latch, so 4724 of those calls found it already
set and changed nothing. That leaves 237 new spurious sets in
120 s across all workers, each costing at most one early return from
the worker's next wait. Workers already get the same from a config
reload, since SignalHandlerForConfigReload() does SetLatch(MyLatch).

With a single database, only one worker ran at a time and every call
was on the caller itself. Those 4871 calls are never needed, because
the caller runs VacuumUpdateCosts() right after the recalculation. If
the SetLatch() stays, skipping wi_proc == MyProc removes about half of
them. With the condition variable you describe, none of this applies,
and only the 235 waiting leaders would be woken.

The script, the log line and the full results are in the attachment.

Regards,
Manu