From: Tom Lane <tgl@sss.pgh.pa.us>
To: Melanie Plageman <melanieplageman@gmail.com>
cc: Peter Geoghegan <pg@bowt.ie>, Heikki Linnakangas <hlinnaka@iki.fi>,
        Pg Hackers <pgsql-hackers@postgresql.org>,
        Andres Freund <andres@anarazel.de>, Noah Misch <noah@leadboat.com>
Subject: Re: Vacuum ERRORs out considering freezing dead tuples from before
 OldestXmin
In-reply-to: 
 <CAAKRu_b+9i71Kk4MB3SxFMzox84En=v+X31sFvRzJ-ZsXVi0qw@mail.gmail.com>
References: 
 <CAAKRu_bDD7oq9ZwB2OJqub5BovMG6UjEYsoK2LVttadjEqyRGg@mail.gmail.com>
 <CAH2-WzkZUDr8TQDPuax1SmJg9B5yz-Qhr7NdoQJD5PpXLAUA7Q@mail.gmail.com>
 <acbce1b9-e88e-4a33-9fa7-4728f67454f2@iki.fi>
 <CAAKRu_apNU2MPBK96V+bXjTq0RiZ-=A4ZTaysakpx9jxbq1dbQ@mail.gmail.com>
 <CAAKRu_Z4PybtZ0i_NKOr-vbrFW5p1ZdfEfUqaeU8fLPhszpP_g@mail.gmail.com>
 <CAH2-WzmXqaX37VCVjTDBF5cauzUgXOkuXsSTLOq56EAB1vzkeQ@mail.gmail.com>
 <CAAKRu_b49z=TSUZG+i0NCEPW409wqsb3Y4MCaMRBVC6ZWKBeVw@mail.gmail.com>
 <CAAKRu_bQFifK3U=VGKpR5MXLaCWHZ-jf0N6xJvMnf_XYB2OR9A@mail.gmail.com>
 <CAH2-WznnDZgZAOUins9ye8_=Y=892N2mvjs9bUB8s37gXnOKQQ@mail.gmail.com>
 <CAAKRu_b+9i71Kk4MB3SxFMzox84En=v+X31sFvRzJ-ZsXVi0qw@mail.gmail.com>
Comments: In-reply-to Melanie Plageman <melanieplageman@gmail.com>
	message dated "Wed, 17 Jul 2024 12:49:35 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <614151.1721580711.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 21 Jul 2024 12:51:51 -0400
Message-ID: <614152.1721580711@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/614152.1721580711%40sss.pgh.pa.us>
Precedence: bulk

Melanie Plageman <melanieplageman@gmail.com> writes:
> When I run it on my machine with some added logging, the space taken
> by dead items is about 330 kB more than maintenance_work_mem (which is
> set to 1 MB). I could roughly double the excess by increasing the
> number of inserted tuples from 400000 to 600000. I'll do this.

So, after about two days in the buildfarm, we have failure reports
from this test on gull, mamba, mereswine, and copperhead.  mamba
is mine, and I was able to reproduce the failure in a manual run.
The problem seems to be that the test simply takes too long and
we hit the default 180-second timeout on one step or another.
I was able to make it pass by dint of

$ export PG_TEST_TIMEOUT_DEFAULT=3D1800

However, the test then took 908 seconds:

$ time make installcheck PROVE_TESTS=3Dt/043_vacuum_horizon_floor.pl
...
# +++ tap install-check in src/test/recovery +++
t/043_vacuum_horizon_floor.pl .. ok   =

All tests successful.
Files=3D1, Tests=3D3, 908 wallclock secs ( 0.17 usr  0.01 sys + 21.42 cusr=
 35.03 csys =3D 56.63 CPU)
Result: PASS
      909.26 real        22.10 user        35.21 sys

This is even slower than the 027_stream_regress.pl test, which
currently takes around 847 seconds on that machine.

mamba, gull, and mereswine are 32-bit machines, which aside from
being old and slow suffer an immediate 2x size-of-test penalty:

>> # The TIDStore vacuum uses to store dead items is optimized for its tar=
get
>> # system. On a 32-bit system, our example requires twice as many pages =
with
>> # the same number of dead items per page to fill the TIDStore and trigg=
er a
>> # second round of index vacuuming.
>> my $is_64bit =3D $node_primary->safe_psql($test_db,
>> 	qq[SELECT typbyval FROM pg_type WHERE typname =3D 'int8';]);
>> =

>> my $nrows =3D $is_64bit eq 't' ? 400000 : 800000;

copperhead is 64-bit but is nonetheless even slower than the
other three, so the fact that it's also timing out isn't
that surprising.

I do not think the answer to this is to nag the respective animal
owners to raise PG_TEST_TIMEOUT_DEFAULT.  IMV this test is simply
not worth the cycles it takes, at least not for these machines.
I'm not sure whether to propose reverting it entirely or just
disabling it on 32-bit hardware.  I don't think we'd lose anything
meaningful in test coverage if we did the latter; but that won't be
enough to make copperhead happy.  I am also suspicious that we'll
get bad news from other very slow animals such as dikkop.

I wonder if there is a less expensive way to trigger the test
situation than brute-forcing things with a large index.
Maybe the injection point infrastructure could help?

			regards, tom lane