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(at)eisentraut(dot)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