Horton

Re: Vacuum ERRORs out considering freezing dead tuples from before OldestXmin

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>, 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>
Date: 2024-07-23T02:53:24Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Test that vacuum removes tuples older than OldestXmin

  2. Lower minimum maintenance_work_mem to 64kB

  3. Add accidentally omitted test to meson build file

  4. Use DELETE instead of UPDATE to speed up vacuum test

  5. Revert "Test that vacuum removes tuples older than OldestXmin"

  6. Ensure vacuum removes all visibly dead tuples older than OldestXmin

On Mon, Jul 22, 2024 at 6:26 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> On Mon, Jul 22, 2024 at 6:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Melanie Plageman <melanieplageman@gmail.com> writes:
> > > We've only run tests with this commit on some of the back branches for
> > > some of these animals. Of those, I don't see any failures so far. So,
> > > it seems the test instability is just related to trying to get
> > > multiple passes of index vacuuming reliably with TIDStore.
> >
> > > AFAICT, all the 32bit machine failures are timeouts waiting for the
> > > standby to catch up (mamba, gull, merswine). Unfortunately, the
> > > failures on copperhead (a 64 bit machine) are because we don't
> > > actually succeed in triggering a second vacuum pass. This would not be
> > > fixed by a longer timeout.
> >
> > Ouch.  This seems to me to raise the importance of getting a better
> > way to test multiple-index-vacuum-passes.  Peter argued upthread
> > that we don't need a better way, but I don't see how that argument
> > holds water if copperhead was not reaching it despite being 64-bit.
> > (Did you figure out exactly why it doesn't reach the code?)
>
> I wasn't able to reproduce the failure (failing to do > 1 index vacuum
> pass) on my local machine (which is 64 bit) without decreasing the
> number of tuples inserted. The copperhead failure confuses me because
> the speed of the machine should *not* affect how much space the dead
> item TIDStore takes up. I would have bet money that the same number
> and offsets of dead tuples per page in a relation would take up the
> same amount of space in a TIDStore on any 64-bit system -- regardless
> of how slowly it runs vacuum.

Looking at copperhead's failure logs, I could not find that "VACUUM
(VERBOSE, FREEZE) vac_horizon_floor_table;" wrote the number of index
scans in logs. Is there any clue that made you think the test failed
to do multiple index vacuum passes?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com