From: Manu <manuelreyesbravo@gmail.com>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Zsolt Parragi <zsolt.parragi@percona.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Nikolay Samokhvalov <nik@postgres.ai>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: autovacuum: automatically propagate updated parameters
Date: Fri, 25 Sep 2026 16:12:20 -0300
Message-ID: <179036354055.1964197.12261940030767792554@gmail.com> (raw)
In-Reply-To: <8B81FC42-44FB-474C-A3B0-2593F44C4996@yesql.se>
References: <8B81FC42-44FB-474C-A3B0-2593F44C4996@yesql.se>
Hi,
Daniel Gustafsson <daniel@yesql.se> wrote:
> Given where we are in the cycle I am also in favor of a simpler solution unless
> it's shown to have (severe) performance regressions.
My earlier numbers counted the SetLatch() calls, not their cost, so I
measured the server's CPU with and without v3 (REL_19_STABLE
e60ee52841d, -O2, no cassert). The load is the same as before: small
tables moving in and out of the balance plus one parallel table, with
autovacuum saturated, 120 s per run, runs alternated.
3 workers, 8 runs each: median 25.75 s CPU both with and without v3.
One v3 run used 28.4 s, and it did not repeat in 7 more.
10 workers, 4 runs each: 125.8 s without v3 (sd 0.8), 126.1 s with
it (sd 0.3).
So I see no CPU cost from v3 at either size. One difference did show
up at 10 workers: v3 vacuumed 2.5% fewer tables in the same window, in
all four pairs. I have not checked why. It would fit the parallel
workers now following the rebalanced limit, but that is a guess.
The scripts and all runs are attached.
Regards,
Manu
Server CPU with and without v3 under a changing autovacuum balance
REL_19_STABLE e60ee52841d, with and without v3, both built with CFLAGS=-O2 and no cassert
24-core machine, otherwise idle (load average in each line)
===== perfcost.sh =====
#!/bin/bash
# What v3's SetLatch() in autovac_recalculate_workers_for_balance() costs,
# under the same load as latchcount.sh: NDB databases with 120 small tables
# each (every third one out of the balance, so the balance count keeps
# changing) plus one table vacuumed in parallel, and as many autovacuum
# workers as databases. v3 sets the latch of every balanced worker on each
# change, so the number of calls grows with the square of the workers.
#
# Reports, for the DURATION-second load window:
# server_cpu_s CPU of the whole server, read from its cgroup
# vacuums tables vacuumed
# (log_autovacuum's own CPU figures are rounded to 10 ms, and most of these
# vacuums take less, so they are not used.)
#
# perfcost.sh BUILD NDB DURATION (BUILD = pbase | pv3)
set -u
BUILD=$1 NDB=$2 DURATION=$3
I=$HOME/pgav/i-$BUILD/bin
D=$(mktemp -d /tmp/claude-1000/pc.XXXX); P=55451
UNIT=avperf-$BUILD-$$
"$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null
cat >> $D/postgresql.conf <<EOF
port = $P
unix_socket_directories = '/tmp'
autovacuum_max_workers = $NDB
autovacuum_max_parallel_workers = 2
max_worker_processes = 32
max_parallel_workers = 16
autovacuum_naptime = '1s'
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0
autovacuum_analyze_threshold = 1000000000
min_parallel_index_scan_size = 0
log_autovacuum_min_duration = 0
log_line_prefix = '%m [%p] '
EOF
# The server runs in its own scope, so its CPU can be read from the cgroup.
systemd-run --user --scope --quiet --unit=$UNIT \
"$I/pg_ctl" -D $D -l $D/log -w start >/dev/null
q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; }
cpu() { systemctl --user show -P CPUUsageNSec $UNIT.scope; }
{
for n in $(seq 1 120); do
if [ $((n % 3)) = 0 ]; then opt="WITH (autovacuum_vacuum_cost_limit = 500)"; else opt=""; fi
echo "CREATE TABLE s$n (id int PRIMARY KEY, v int) $opt;"
echo "INSERT INTO s$n SELECT g, g FROM generate_series(1, 3000) g;"
done
echo "CREATE TABLE big (id int PRIMARY KEY, a int, b int, c int) WITH (autovacuum_parallel_workers = 2);"
echo "INSERT INTO big SELECT g, g, g, g FROM generate_series(1, 400000) g;"
echo "CREATE INDEX big_a ON big (a); CREATE INDEX big_b ON big (b); CREATE INDEX big_c ON big (c);"
} > $D/setup.sql
DBS=postgres
for k in $(seq 2 $NDB); do q -c "CREATE DATABASE d$k"; DBS="$DBS d$k"; done
for db in $DBS; do q -d $db -f $D/setup.sql; done
# 60 rows: just over autovacuum_vacuum_threshold, so the server's CPU goes
# mostly to autovacuum rather than to the updates themselves.
for n in $(seq 1 120); do echo "UPDATE s$n SET v = v + 1 WHERE id <= 60;"; done > $D/round.sql
echo "UPDATE big SET a = a + 1 WHERE id % 4 = 0;" > $D/big.sql
sleep 20 # let autovacuum finish with the setup
off=$(stat -c %s $D/log); c0=$(cpu)
end=$(( $(date +%s) + DURATION )); r=0
while [ "$(date +%s)" -lt $end ]; do
for db in $DBS; do q -d $db -f $D/round.sql & done; wait
if [ $((r % 5)) = 0 ]; then for db in $DBS; do q -d $db -f $D/big.sql & done; wait; fi
r=$((r + 1)); sleep 2
done
c1=$(cpu)
L=$D/window.log; tail -c +$((off + 1)) $D/log > $L
"$I/pg_ctl" -D $D -m fast -w stop >/dev/null
vac=$(grep -c 'automatic vacuum of table' $L)
awk -v b=$BUILD -v n=$NDB -v r=$r -v c=$(( (c1 - c0) / 1000000 )) -v v=$vac 'BEGIN {
printf "build=%s ndb=%d rounds=%d server_cpu_s=%.2f vacuums=%d\n", b, n, r, c / 1000, v }'
rm -rf $D
===== perf_all.sh =====
#!/bin/bash
# perfcost.sh for pbase and pv3, alternating, REPS times each, with 3 and 10
# workers. Alternating spreads any drift in the machine's load over both.
# perf_all.sh [DURATION] [REPS]
set -u
DURATION=${1:-120}
REPS=${2:-4}
A=$(cd "$(dirname "$0")" && pwd)
OUT=$A/perfcost.runs.txt
: > $OUT
for ndb in 3 10; do
for rep in $(seq 1 $REPS); do
for b in pbase pv3; do
echo "load: $(cut -d' ' -f1-3 /proc/loadavg) | $(bash $A/perfcost.sh $b $ndb $DURATION)" | tee -a $OUT
done
done
done
echo
awk '{
for (i = 1; i <= NF; i++) { split($i, kv, "="); f[kv[1]] = kv[2] }
k = f["ndb"] " " f["build"]; n[k]++
c[k] += f["server_cpu_s"]; cc[k] += f["server_cpu_s"] ^ 2
v[k] += f["vacuums"]; r[k] += f["rounds"]
} END {
for (k in n) {
m = c[k] / n[k]; sd = (n[k] > 1) ? sqrt((cc[k] - n[k] * m * m) / (n[k] - 1)) : 0
printf "ndb=%s runs=%d server_cpu_s=%.2f sd=%.2f vacuums=%.0f rounds=%.1f cpu_ms_per_vacuum=%.3f\n",
k, n[k], m, sd, v[k] / n[k], r[k] / n[k], c[k] * 1000 / v[k]
}
}' $OUT | sort | tee $A/perfcost.summary.txt
===== runs: perf_all.sh 120 4 (pbase first in each pair) =====
load: 1.99 2.71 6.71 | build=pbase ndb=3 rounds=57 server_cpu_s=25.20 vacuums=15522
load: 0.95 2.00 5.88 | build=pv3 ndb=3 rounds=57 server_cpu_s=28.43 vacuums=15337
load: 1.94 2.03 5.32 | build=pbase ndb=3 rounds=57 server_cpu_s=25.22 vacuums=15766
load: 1.22 1.80 4.78 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.67 vacuums=15384
load: 1.19 1.62 4.28 | build=pbase ndb=3 rounds=57 server_cpu_s=25.80 vacuums=15633
load: 1.32 1.44 3.81 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.69 vacuums=15404
load: 1.02 1.20 3.38 | build=pbase ndb=3 rounds=57 server_cpu_s=25.70 vacuums=15039
load: 1.18 1.22 3.07 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.99 vacuums=15697
load: 1.02 1.11 2.77 | build=pbase ndb=10 rounds=53 server_cpu_s=125.34 vacuums=23643
load: 0.94 1.11 2.52 | build=pv3 ndb=10 rounds=53 server_cpu_s=126.33 vacuums=23213
load: 1.44 1.32 2.39 | build=pbase ndb=10 rounds=53 server_cpu_s=124.91 vacuums=24138
load: 1.99 1.64 2.35 | build=pv3 ndb=10 rounds=53 server_cpu_s=126.28 vacuums=23771
load: 1.66 1.62 2.24 | build=pbase ndb=10 rounds=53 server_cpu_s=126.60 vacuums=24658
load: 2.41 1.82 2.21 | build=pv3 ndb=10 rounds=53 server_cpu_s=125.69 vacuums=23555
load: 1.62 1.67 2.09 | build=pbase ndb=10 rounds=53 server_cpu_s=126.27 vacuums=24415
load: 1.82 1.83 2.09 | build=pv3 ndb=10 rounds=53 server_cpu_s=126.24 vacuums=23929
===== summary of those runs =====
ndb=10 pbase runs=4 server_cpu_s=125.78 sd=0.79 vacuums=24214 rounds=53.0 cpu_ms_per_vacuum=5.195
ndb=10 pv3 runs=4 server_cpu_s=126.14 sd=0.30 vacuums=23617 rounds=53.0 cpu_ms_per_vacuum=5.341
ndb=3 pbase runs=4 server_cpu_s=25.48 sd=0.31 vacuums=15490 rounds=57.0 cpu_ms_per_vacuum=1.645
ndb=3 pv3 runs=4 server_cpu_s=26.45 sd=1.33 vacuums=15456 rounds=57.0 cpu_ms_per_vacuum=1.711
===== 4 more pairs with 3 workers, pv3 first in each pair =====
load: 1.46 1.66 1.98 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.67 vacuums=15426
load: 0.71 1.31 1.80 | build=pbase ndb=3 rounds=57 server_cpu_s=25.33 vacuums=15222
load: 0.68 1.15 1.67 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.50 vacuums=15436
load: 1.05 1.07 1.56 | build=pbase ndb=3 rounds=57 server_cpu_s=25.90 vacuums=15642
load: 1.45 1.11 1.49 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.81 vacuums=15755
load: 0.83 0.95 1.37 | build=pbase ndb=3 rounds=57 server_cpu_s=26.26 vacuums=15395
load: 0.97 1.01 1.33 | build=pv3 ndb=3 rounds=57 server_cpu_s=26.39 vacuums=15514
load: 0.62 0.95 1.27 | build=pbase ndb=3 rounds=57 server_cpu_s=26.63 vacuums=15988
Attachments:
[text/plain] nocfbot-av-v3-cpu.txt (7.2K, ../179036354055.1964197.12261940030767792554@gmail.com/2-nocfbot-av-v3-cpu.txt)
download | inline:
Server CPU with and without v3 under a changing autovacuum balance
REL_19_STABLE e60ee52841d, with and without v3, both built with CFLAGS=-O2 and no cassert
24-core machine, otherwise idle (load average in each line)
===== perfcost.sh =====
#!/bin/bash
# What v3's SetLatch() in autovac_recalculate_workers_for_balance() costs,
# under the same load as latchcount.sh: NDB databases with 120 small tables
# each (every third one out of the balance, so the balance count keeps
# changing) plus one table vacuumed in parallel, and as many autovacuum
# workers as databases. v3 sets the latch of every balanced worker on each
# change, so the number of calls grows with the square of the workers.
#
# Reports, for the DURATION-second load window:
# server_cpu_s CPU of the whole server, read from its cgroup
# vacuums tables vacuumed
# (log_autovacuum's own CPU figures are rounded to 10 ms, and most of these
# vacuums take less, so they are not used.)
#
# perfcost.sh BUILD NDB DURATION (BUILD = pbase | pv3)
set -u
BUILD=$1 NDB=$2 DURATION=$3
I=$HOME/pgav/i-$BUILD/bin
D=$(mktemp -d /tmp/claude-1000/pc.XXXX); P=55451
UNIT=avperf-$BUILD-$$
"$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null
cat >> $D/postgresql.conf <<EOF
port = $P
unix_socket_directories = '/tmp'
autovacuum_max_workers = $NDB
autovacuum_max_parallel_workers = 2
max_worker_processes = 32
max_parallel_workers = 16
autovacuum_naptime = '1s'
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0
autovacuum_analyze_threshold = 1000000000
min_parallel_index_scan_size = 0
log_autovacuum_min_duration = 0
log_line_prefix = '%m [%p] '
EOF
# The server runs in its own scope, so its CPU can be read from the cgroup.
systemd-run --user --scope --quiet --unit=$UNIT \
"$I/pg_ctl" -D $D -l $D/log -w start >/dev/null
q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; }
cpu() { systemctl --user show -P CPUUsageNSec $UNIT.scope; }
{
for n in $(seq 1 120); do
if [ $((n % 3)) = 0 ]; then opt="WITH (autovacuum_vacuum_cost_limit = 500)"; else opt=""; fi
echo "CREATE TABLE s$n (id int PRIMARY KEY, v int) $opt;"
echo "INSERT INTO s$n SELECT g, g FROM generate_series(1, 3000) g;"
done
echo "CREATE TABLE big (id int PRIMARY KEY, a int, b int, c int) WITH (autovacuum_parallel_workers = 2);"
echo "INSERT INTO big SELECT g, g, g, g FROM generate_series(1, 400000) g;"
echo "CREATE INDEX big_a ON big (a); CREATE INDEX big_b ON big (b); CREATE INDEX big_c ON big (c);"
} > $D/setup.sql
DBS=postgres
for k in $(seq 2 $NDB); do q -c "CREATE DATABASE d$k"; DBS="$DBS d$k"; done
for db in $DBS; do q -d $db -f $D/setup.sql; done
# 60 rows: just over autovacuum_vacuum_threshold, so the server's CPU goes
# mostly to autovacuum rather than to the updates themselves.
for n in $(seq 1 120); do echo "UPDATE s$n SET v = v + 1 WHERE id <= 60;"; done > $D/round.sql
echo "UPDATE big SET a = a + 1 WHERE id % 4 = 0;" > $D/big.sql
sleep 20 # let autovacuum finish with the setup
off=$(stat -c %s $D/log); c0=$(cpu)
end=$(( $(date +%s) + DURATION )); r=0
while [ "$(date +%s)" -lt $end ]; do
for db in $DBS; do q -d $db -f $D/round.sql & done; wait
if [ $((r % 5)) = 0 ]; then for db in $DBS; do q -d $db -f $D/big.sql & done; wait; fi
r=$((r + 1)); sleep 2
done
c1=$(cpu)
L=$D/window.log; tail -c +$((off + 1)) $D/log > $L
"$I/pg_ctl" -D $D -m fast -w stop >/dev/null
vac=$(grep -c 'automatic vacuum of table' $L)
awk -v b=$BUILD -v n=$NDB -v r=$r -v c=$(( (c1 - c0) / 1000000 )) -v v=$vac 'BEGIN {
printf "build=%s ndb=%d rounds=%d server_cpu_s=%.2f vacuums=%d\n", b, n, r, c / 1000, v }'
rm -rf $D
===== perf_all.sh =====
#!/bin/bash
# perfcost.sh for pbase and pv3, alternating, REPS times each, with 3 and 10
# workers. Alternating spreads any drift in the machine's load over both.
# perf_all.sh [DURATION] [REPS]
set -u
DURATION=${1:-120}
REPS=${2:-4}
A=$(cd "$(dirname "$0")" && pwd)
OUT=$A/perfcost.runs.txt
: > $OUT
for ndb in 3 10; do
for rep in $(seq 1 $REPS); do
for b in pbase pv3; do
echo "load: $(cut -d' ' -f1-3 /proc/loadavg) | $(bash $A/perfcost.sh $b $ndb $DURATION)" | tee -a $OUT
done
done
done
echo
awk '{
for (i = 1; i <= NF; i++) { split($i, kv, "="); f[kv[1]] = kv[2] }
k = f["ndb"] " " f["build"]; n[k]++
c[k] += f["server_cpu_s"]; cc[k] += f["server_cpu_s"] ^ 2
v[k] += f["vacuums"]; r[k] += f["rounds"]
} END {
for (k in n) {
m = c[k] / n[k]; sd = (n[k] > 1) ? sqrt((cc[k] - n[k] * m * m) / (n[k] - 1)) : 0
printf "ndb=%s runs=%d server_cpu_s=%.2f sd=%.2f vacuums=%.0f rounds=%.1f cpu_ms_per_vacuum=%.3f\n",
k, n[k], m, sd, v[k] / n[k], r[k] / n[k], c[k] * 1000 / v[k]
}
}' $OUT | sort | tee $A/perfcost.summary.txt
===== runs: perf_all.sh 120 4 (pbase first in each pair) =====
load: 1.99 2.71 6.71 | build=pbase ndb=3 rounds=57 server_cpu_s=25.20 vacuums=15522
load: 0.95 2.00 5.88 | build=pv3 ndb=3 rounds=57 server_cpu_s=28.43 vacuums=15337
load: 1.94 2.03 5.32 | build=pbase ndb=3 rounds=57 server_cpu_s=25.22 vacuums=15766
load: 1.22 1.80 4.78 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.67 vacuums=15384
load: 1.19 1.62 4.28 | build=pbase ndb=3 rounds=57 server_cpu_s=25.80 vacuums=15633
load: 1.32 1.44 3.81 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.69 vacuums=15404
load: 1.02 1.20 3.38 | build=pbase ndb=3 rounds=57 server_cpu_s=25.70 vacuums=15039
load: 1.18 1.22 3.07 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.99 vacuums=15697
load: 1.02 1.11 2.77 | build=pbase ndb=10 rounds=53 server_cpu_s=125.34 vacuums=23643
load: 0.94 1.11 2.52 | build=pv3 ndb=10 rounds=53 server_cpu_s=126.33 vacuums=23213
load: 1.44 1.32 2.39 | build=pbase ndb=10 rounds=53 server_cpu_s=124.91 vacuums=24138
load: 1.99 1.64 2.35 | build=pv3 ndb=10 rounds=53 server_cpu_s=126.28 vacuums=23771
load: 1.66 1.62 2.24 | build=pbase ndb=10 rounds=53 server_cpu_s=126.60 vacuums=24658
load: 2.41 1.82 2.21 | build=pv3 ndb=10 rounds=53 server_cpu_s=125.69 vacuums=23555
load: 1.62 1.67 2.09 | build=pbase ndb=10 rounds=53 server_cpu_s=126.27 vacuums=24415
load: 1.82 1.83 2.09 | build=pv3 ndb=10 rounds=53 server_cpu_s=126.24 vacuums=23929
===== summary of those runs =====
ndb=10 pbase runs=4 server_cpu_s=125.78 sd=0.79 vacuums=24214 rounds=53.0 cpu_ms_per_vacuum=5.195
ndb=10 pv3 runs=4 server_cpu_s=126.14 sd=0.30 vacuums=23617 rounds=53.0 cpu_ms_per_vacuum=5.341
ndb=3 pbase runs=4 server_cpu_s=25.48 sd=0.31 vacuums=15490 rounds=57.0 cpu_ms_per_vacuum=1.645
ndb=3 pv3 runs=4 server_cpu_s=26.45 sd=1.33 vacuums=15456 rounds=57.0 cpu_ms_per_vacuum=1.711
===== 4 more pairs with 3 workers, pv3 first in each pair =====
load: 1.46 1.66 1.98 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.67 vacuums=15426
load: 0.71 1.31 1.80 | build=pbase ndb=3 rounds=57 server_cpu_s=25.33 vacuums=15222
load: 0.68 1.15 1.67 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.50 vacuums=15436
load: 1.05 1.07 1.56 | build=pbase ndb=3 rounds=57 server_cpu_s=25.90 vacuums=15642
load: 1.45 1.11 1.49 | build=pv3 ndb=3 rounds=57 server_cpu_s=25.81 vacuums=15755
load: 0.83 0.95 1.37 | build=pbase ndb=3 rounds=57 server_cpu_s=26.26 vacuums=15395
load: 0.97 1.01 1.33 | build=pv3 ndb=3 rounds=57 server_cpu_s=26.39 vacuums=15514
load: 0.62 0.95 1.27 | build=pbase ndb=3 rounds=57 server_cpu_s=26.63 vacuums=15988
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: manuelreyesbravo@gmail.com, daniel@yesql.se, sawada.mshk@gmail.com, zsolt.parragi@percona.com, bharath.rupireddyforpostgres@gmail.com, nik@postgres.ai, pgsql-bugs@lists.postgresql.org
Subject: Re: autovacuum: automatically propagate updated parameters
In-Reply-To: <179036354055.1964197.12261940030767792554@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox