Anti-wraparound autovacuum stops for good after "no slot available for new autovacuum worker process"

Started by Colin McDonnell3 days ago3 messagesbugs
Jump to latest
#1Colin McDonnell
colinmcd94@gmail.com

With autovacuum = off and autovacuum_worker_slots = 1, the autovacuum that
the postmaster starts to prevent XID wraparound can stop for good after
vacuuming one database. The postmaster logs this once:

LOG: no slot available for new autovacuum worker process

After that no autovacuum worker starts again, however far the other
databases go past autovacuum_freeze_max_age, until the server is restarted
or autovacuum is turned on. The documentation for autovacuum says that even
when it is disabled, the system will launch autovacuum processes if
necessary to prevent transaction ID wraparound.

Versions and platform:

- 18.7, Ubuntu package 18.7-1.pgdg24.04+2
- 19.0, built from the REL_19_0 tag
- master at 4280abf884
- Ubuntu 24.04 in Docker, Linux 6.8.0 aarch64, 8 CPUs, 24 GB RAM, glibc 2.39

17.12 (with autovacuum_max_workers = 1 in place of autovacuum_worker_slots)
does not have it.

Reproduction, with initdb, pg_ctl, pgbench and psql on PATH:

#!/bin/sh
set -e
D=/tmp/avdata
initdb -D $D >/dev/null
cat >> $D/postgresql.conf <<'EOF'
autovacuum = off
autovacuum_worker_slots = 1
autovacuum_freeze_max_age = 100000
EOF
pg_ctl -D $D -l $D/log -w start >/dev/null

# Consume XIDs past 131072, the first multiple of 65536 above xidVacLimit,
# where GetNewTransactionId() asks the postmaster for an emergency launcher.
echo 'SELECT txid_current();' > /tmp/xid.sql
pgbench -n -f /tmp/xid.sql -c 4 -j 4 -t 34000 postgres >/dev/null

sleep 60
psql -X -c "SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 1"
postgres
grep -E 'no slot|could not fork' $D/log || true

Expected: workers vacuum postgres, template1 and template0 in turn, and
every age in the query is below 100000. On 17.12 the three workers finish
within 50 ms of the first launcher starting.

Actual, on the 18.7 package:

datname | age
-----------+--------
postgres | 5522
template0 | 136008
template1 | 136008
(3 rows)

2026-11-19 07:47:41.583 UTC [2926] LOG: no slot available for new
autovacuum worker process

With log_min_messages = debug1 and the backend type in log_line_prefix,
another run ended like this:

07:18:33.384 [3793] autovacuum launcher DEBUG: autovacuum launcher started
07:18:33.387 [3794] autovacuum worker DEBUG: autovacuum: processing
database "postgres"
07:18:33.404 [3794] autovacuum worker DEBUG: transaction ID wrap limit is
2147484391, limited by database with OID 1
07:18:33.404 [3795] autovacuum launcher DEBUG: autovacuum launcher started
07:18:33.405 [3767] postmaster LOG: no slot available for new autovacuum
worker process

Consuming another 70,000 XIDs crosses the next multiple of 65536, so
GetNewTransactionId() starts another emergency launcher. It exits without
starting a worker, and the ages keep growing. Setting autovacuum = on and
reloading recovers, and the new launcher logs:

WARNING: autovacuum worker took too long to start; canceled

It depends on timing. The script above left databases past the limit, with
that LOG line, in 7 of 8 runs on the 18.7 package. A longer version, which
also consumes the 70,000 extra XIDs before turning autovacuum on, gave
these counts (the extra databases were made with CREATE DATABASE before
consuming XIDs):

build databases fsync stalled for good
18.7 package 3 off 6 of 6
18.7 package 303 off 2 of 2
18.7 package 303 on 2 of 2
18.7 package 1003 on 1 of 1
18.7 built from source 3 off 0 of 3
19.0 3 off 0 of 5
19.0 303 off 1 of 2
master 3 off 1 of 6
master 303 off 1 of 2

Adding pg_usleep(100000L) at the end of FreeWorkerInfo(), which only widens
the window described below, makes master and a source build of 18.7 stall
in every run.

Cause:

1. In emergency mode (autovacuum or track_counts off),
AutoVacLauncherMain() calls do_start_worker() and exits. do_start_worker()
takes a WorkerInfo off av_freeWorkers, points av_startingWorker at it, and
sends PMSIGNAL_START_AUTOVAC_WORKER. Only the launcher's main loop retries
a failed start (AutoVacForkFailed) or gives back the WorkerInfo of a worker
that never started ("took too long to start; canceled"), and an emergency
launcher never gets there.

2. When the postmaster can't start the worker, StartAutovacuumWorker()
calls AutoVacWorkerFailed() only while a launcher is running. The emergency
launcher has already exited, so the WorkerInfo stays in av_startingWorker
and nothing gives it back. Every later emergency launcher finds
av_worker_available() false and exits.

3. No fork() has to fail. The worker that just finished calls
SetTransactionIdLimit() from vac_update_datfrozenxid(), which signals for
the next launcher while the worker is still running. The worker then
returns its WorkerInfo in FreeWorkerInfo() and exits. The new launcher can
take that WorkerInfo and signal the postmaster before the postmaster has
reaped the old worker. Since a78af04270 (in 18), autovacuum workers have
their own pool of exactly autovacuum_worker_slots postmaster child slots,
so AssignPostmasterChildSlot() returns NULL and StartChildProcess() logs
"no slot available". In 17 the workers took slots from a shared pool of 2 *
(MaxConnections + autovacuum_max_workers + 1 + max_wal_senders +
max_worker_processes), so an unreaped worker didn't matter.

A real fork() failure in emergency mode leaks the WorkerInfo the same way
in older releases, one slot per failure.

This is close to the race reported in 2024 for the xid_wraparound tests
[1]: /messages/by-id/5811175c-1a31-4869-032f-7af5e3e4506a@gmail.com
previous worker hasn't exited yet. That launcher exits without leaking
anything, so autovacuum resumes at the next multiple of 65536 XIDs, and
800cd3e923 [2]/messages/by-id/3e2cbd24-f45e-4b2b-ba83-8149214f0a4d@dunslane.net changed the tests to run with autovacuum on. I see that
variant too, on 17.12 and on 18.7 with the default 16 slots and
autovacuum_max_workers = 1, and it recovers both times.

Suggested fix: the emergency launcher waits for the worker it asked for to
start before exiting, retrying a failed start and timing out a stuck one as
the main loop does. The attached patch for master does that, and moves the
main loop's give-back code into a function both use. Results with the
pg_usleep() above and fsync = off:

build databases stalled for good failed starts, all retried
master 3 3 of 3 -
master + patch 3 0 of 3 2 per run
master + patch 53 0 of 1 52
18.7 built from source 3 2 of 2 -
18.7 built from source + patch 3 0 of 2 0
18.7 built from source + patch 53 0 of 1 1

On master each retry runs as soon as the postmaster reaps the old worker,
since the postmaster wakes the launcher then, and the 53 databases took
about 6 seconds. The patch applies to REL_18_STABLE too, but there the
emergency launcher sets av_launcherpid only after the emergency branch, so
the starting worker can't wake it and the wait relies on its 1 s timeout.
The 53 databases took 54 seconds. A backpatch could set av_launcherpid
before the branch and clear it before the emergency launcher exits.

[1]: /messages/by-id/5811175c-1a31-4869-032f-7af5e3e4506a@gmail.com
/messages/by-id/5811175c-1a31-4869-032f-7af5e3e4506a@gmail.com
[2]: /messages/by-id/3e2cbd24-f45e-4b2b-ba83-8149214f0a4d@dunslane.net

Attachments:

t259554_1
v1-0001-Retry-a-failed-worker-start-in-the-emergency-autova.patchapplication/octet-stream; name=v1-0001-Retry-a-failed-worker-start-in-the-emergency-autova.patchDownload+95-17
#2Sami Imseih
samimseih.pg@gmail.com
In reply to: Colin McDonnell (#1)
Re: Anti-wraparound autovacuum stops for good after "no slot available for new autovacuum worker process"

Hi,

Thanks for the report.

With autovacuum = off and autovacuum_worker_slots = 1, the autovacuum that
the postmaster starts to prevent XID wraparound can stop for good after
vacuuming one database.

I can reproduce it on HEAD. As I understand it, the old worker still holds
the only child slot until the postmaster cleans up after it, so the new one
can't start. When that happens the emergency launcher has already exited,
so nobody hands its WorkerInfo back and we're stuck.

A real fork() failure in emergency mode leaks the WorkerInfo the same way
in older releases, one slot per failure.

Since a78af04270 a configuration setting is enough to hit it, while
before 18 a start has to really fail, and we haven't seen that
reported. So I'd fix this in HEAD and backpatch to 18.

... there the emergency launcher sets av_launcherpid only after the
emergency branch ...

HEAD already handles it since 842169b6c11, so I'd leave 18 and 19 as
is. It's separate from this fix. WDYT?

Some comments on the patch.

+        if (!ShutdownRequestPending && OidIsValid(do_start_worker()))
+            wait_for_emergency_worker();

If the wait loop errors out, the launcher comes back here, takes a
second WorkerInfo and loses the first. Unlikely, but easy to avoid by
waiting on a WorkerInfo that's already reserved instead. When none is
reserved, any leftover "failed to start" flag is stale, so clear it
first.

Also, this could also be one function, say do_start_emergency_worker(),
placed next to do_start_worker(). It would start or reuse the worker
and wait for it, and be called where do_start_worker() is now.
WDYT?

+            SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER);
+            continue;
+        }

.....
.........

+        ResetLatch(MyLatch);
+
+        if (ShutdownRequestPending)
+            return;
+        if (ProcSignalBarrierPending)
+            ProcessProcSignalBarrier();

I'd only ask again if the worker hasn't shown up during the pause. And
the continue skips the interrupt handling, so I'd move it to the top of
the loop.

+ if (worker == NULL)

Nit, this is the WorkerInfo, not the worker. Maybe "reserved"?

+cancel_starting_worker(void)

The helper looks good. Maybe Assert that the lock is held?

Sami Imseih
Amazon Web Services (AWS)

#3Colin McDonnell
colinmcd94@gmail.com
In reply to: Sami Imseih (#2)
Re: Anti-wraparound autovacuum stops for good after no slot available for new autovacuum worker process

Sami Imseih <samimseih.pg@gmail.com> wrote:

Since a78af04270 a configuration setting is enough to hit it, while
before 18 a start has to really fail, and we haven't seen that
reported. So I'd fix this in HEAD and backpatch to 18.

Totally agree.

HEAD already handles it since 842169b6c11, so I'd leave 18 and 19 as
is. It's separate from this fix. WDYT?

I think it stops being separate once the launcher waits, though. On 18 and
19 nothing wakes the emergency launcher when its worker starts, so with the
patch each database waits for the 1s latch timeout, whatever
autovacuum_worker_slots is. On REL_18_STABLE (813a74bc5a) with the default
16 slots, 53 databases past the limit took 2s without the patch and 52s
with it. Setting av_launcherpid before the emergency branch, and back to 0
before that launcher exits, brought it to 1s. So I'd add that to the 18 and
19 backpatch.

If the wait loop errors out, the launcher comes back here, takes a
second WorkerInfo and loses the first.

Yep, the error recovery block falls through to the emergency branch again.
v2 waits on a WorkerInfo that's already reserved instead of taking another,
and clears the failed-start flag when none is.

Also, this could also be one function, say do_start_emergency_worker(),
placed next to do_start_worker().

For sure, done 👍 The emergency branch calls it where it called
do_start_worker().

I'd only ask again if the worker hasn't shown up during the pause. And
the continue skips the interrupt handling, so I'd move it to the top of
the loop.

Yep, both done.

Nit, this is the WorkerInfo, not the worker. Maybe "reserved"?

✅

The helper looks good. Maybe Assert that the lock is held?

✅

v2 attached, against d27418375d. It applies to REL_18_STABLE and
REL_19_STABLE too. On an assert-enabled build of HEAD with
pg_usleep(100000L) at the end of FreeWorkerInfo(), HEAD stalled in 2 of 2
runs, and v2 vacuumed every database in 4 of 4, retrying all 58 failed
starts. make check passes and pgindent leaves it unchanged.

Colin

Attachments:

t259554_3
v2-0001-Retry-a-failed-worker-start-in-the-emergency-auto.patchapplication/octet-stream; name=v2-0001-Retry-a-failed-worker-start-in-the-emergency-auto.patchDownload+121-16