CI slowdown due to PG_TEST_INITDB_EXTRA_OPTS
Hi,
I was once more wondering why CI is so slow. While thinking about what
measurements to take, I think I figured out a major source:
Many of the jobs use PG_TEST_INITDB_EXTRA_OPTS. Unfortunately, using that
turns out to disable the initdb template mechanism:
sub init
{
...
if (defined $initdb_extra_opts_env)
{
push @{ $params{extra} }, shellwords($initdb_extra_opts_env);
}
...
# If available, if there aren't any parameters and if force_initdb is
# disabled, use a previously initdb'd cluster as a template by copying it.
# For a lot of tests, that's substantially cheaper. It does not seem
# worth figuring out whether extra parameters affect compatibility, so
# initdb is forced if any are defined.
#
# There's very similar code in pg_regress.c, but we can't easily
# deduplicate it until we require perl at build time.
if ( $params{force_initdb}
or defined $params{extra}
or !defined $ENV{INITDB_TEMPLATE})
{
note("initializing database system by running initdb");
To see the impact of that, I just hacked up a commit that comments out all the
use of PG_TEST_INITDB_EXTRA_OPTS. That's of course not a fair comparison,
several of the options passed to PG_TEST_INITDB_EXTRA_OPTS do increase
overhead, but it's an approximation.
master: https://github.com/anarazel/postgres/actions/runs/29593721971
hack: https://github.com/anarazel/postgres/actions/runs/29597834725
(note that for the hack the compiles are cached, so you can't look at the
overall runtimes)
master hack
autoconf 14m52s 8m45s
meson-32 8m13s 6m52s
meson-64 19m19s 13m27s
macos 19m24s 9m07s
mingw 15m41s 19m05s
vs-slice-1 15m59s 14m19s
vs-slice-2 10m39s 13m55s
Note that mingw and vs did not use PG_TEST_INITDB_EXTRA_OPTS, so their test
timing is unrelated. Which also shows that the rest of the numbers are not
entirely to be trusted.
But I can also reproduce decent improvements locally, so I'm pretty confident
not using PG_TEST_INITDB_EXTRA_OPTS will yield significant improvements:
To make the comparison fairer, I'm comparing not using
PG_TEST_INITDB_EXTRA_OPTS
with using
PG_TEST_INITDB_EXTRA_OPTS='-c debug_copy_parse_plan_trees=off'
which shouldn't change any runtime behaviour.
PG_TEST_INITDB_EXTRA_OPTS='-c debug_copy_parse_plan_trees=off':
1794.54user 366.10system 2:11.37elapsed 1644%CPU (0avgtext+0avgdata 153580maxresident)k
52160inputs+69971904outputs (612major+26216385minor)pagefaults 0swaps
without:
435.36user 293.50system 1:48.81elapsed 669%CPU (0avgtext+0avgdata 153508maxresident)k
429472inputs+34543792outputs (780major+20764791minor)pagefaults 0swaps
So that's a ~3x increase in CPU time, no wonder that slows down the tests.
And that's without even using sanitizers, which make initdb a good bit slower!
Now, the slightly harder question is how to do this without just ripping out
the PG_TEST_INITDB_EXTRA_OPTS. Looks like we started using that for CI with:
commit a292c98d62ddc0ad681f772ab91bf68ee399cb4b
Author: Peter Eisentraut <peter@eisentraut.org>
Date: 2024-08-01 09:37:44 +0200
Convert node test compile-time settings into run-time parameters
I guess the reason Peter didn't use TEMP_CONFIG is that it's kinda annoying to
do properly, due TEMP_CONFIG already being set to src/tools/ci/pg_ci_base.conf.
Without adding new pg_regress.c and Cluster.pm code, it seems we would have to
create a conf file that includes src/tools/ci/pg_ci_base.conf and also adds
the other options. But that's somewhat annoying to do :(
We could add an include_if_exists to src/tools/ci/pg_ci_base.conf, but I'm not
sure what absolute path would be portable enough, and I don't think we allow
environment variables or such.
Any better ideas?
On macos we probably also could get a decent further boost by using cp -c when
copying the template data directory (which uses copy on write). On linux the
default is --reflink=auto, so it'd get used automatically, but CI doesn't run
on a CoW/reflink capable filesystem (locally I see pretty decent gains from
it) . On an m4 mac mini, I see about a 10% gain, but it's quite plausible
this would be larger in CI. But that's a separate thread, I guess.
Greetings,
Andres Freund
Andres Freund <andres@anarazel.de> writes:
I was once more wondering why CI is so slow. While thinking about what
measurements to take, I think I figured out a major source:
Many of the jobs use PG_TEST_INITDB_EXTRA_OPTS. Unfortunately, using that
turns out to disable the initdb template mechanism:
...
Any better ideas?
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?
regards, tom lane
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?
Yeah, that seems to work pretty well. See attached.
I'm getting similar test timings in CI with this as Andres got for the
Linux runs that he did when removing PG_TEST_INITDB_EXTRA_OPTS.
For MacOS I see basically no improvement though, I guess that's probably
because the debug_xxx options actually incur significant overhead. Maybe
it's worth trying to move them to one of the faster runs. But that seems
like a separate discussion.
Attachments:
v1-0001-Keep-using-the-initdb-template-when-PG_TEST_INITD.patchtext/x-patch; charset=utf-8; name=v1-0001-Keep-using-the-initdb-template-when-PG_TEST_INITD.patchDownload+108-17
Hi,
On 2026-07-20 23:47:16 +0200, Jelte Fennema-Nio wrote:
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?
That seems like a somewhat complicated mechanism. But mainly I feel like it's
a workaround around the config-customization-via-env-var having to go through
exactly one file. None of the uses of PG_TEST_INITDB_EXTRA_OPTS in CI actually
needs it to be an initdb option, it's just a way to set configuration options
for the server via an environment variable.
I wonder if the best thing would be to add PG_TEST_SERVER_POSTGRES_OPTS or
such, which Cluster.pm and regress.c would add to postgresql.conf.
For MacOS I see basically no improvement though, I guess that's probably
because the debug_xxx options actually incur significant overhead. Maybe
it's worth trying to move them to one of the faster runs. But that seems
like a separate discussion.
Hm. I see very consistent improvements locally on an m4 mac mini. On CI the
runtime of the macos job is crazily variable, e.g. the last few runs on master
had these runtimes for macos' "Test world":
14m59s, 17m24s, 12m13s, 18m38s, 13m15s, 12m22s
No idea what causes that degree of variability. I guess the effect of
neighboring tasks is the most likely explanation...
In contrast, meson-64 has these "Test world" times for the same commits:
19m46s, 19m40s, 18m38s, 18m14s, 19m15s, 18m23s
(meson-64 is slow due to asan)
With the configuration for meson-64 instead done via:
- name: 'configure extra opts'
run: |
echo "io_method=io_uring" >> src/tools/ci/pg_ci_base.conf
cat src/tools/ci/pg_ci_base.conf
I see ~13m30s for meson-64.
Greetings,
Andres Freund
Hi,
On Tue, 21 Jul 2026 at 03:56, Andres Freund <andres@anarazel.de> wrote:
On 2026-07-20 23:47:16 +0200, Jelte Fennema-Nio wrote:
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?I wonder if the best thing would be to add PG_TEST_SERVER_POSTGRES_OPTS or
such, which Cluster.pm and regress.c would add to postgresql.conf.
Attached is an attempt to implement 'PG_TEST_SERVER_POSTGRES_OPTS'.
Like Jelte, I didn't see any improvement for MacOS.
--
Regards,
Nazir Bilal Yavuz
Microsoft