Horton

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

Melanie Plageman <melanieplageman@gmail.com>

View thread
From: Melanie Plageman <melanieplageman@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, Pg Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>, Noah Misch <noah@leadboat.com>
Date: 2024-07-17T16:49:35Z
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 Wed, Jul 17, 2024 at 12:11 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Wed, Jul 17, 2024 at 12:07 PM Melanie Plageman
> <melanieplageman@gmail.com> wrote:
> > We didn't end up doing two index vacuum passes. Because it doesn't
> > repro locally for me, I can only assume that the conditions for
> > forcing two index vacuuming passes in master just weren't met in this
> > case. I'm unsurprised, as it is much harder since 17 to force two
> > passes of index vacuuming. It seems like this might be as unstable as
> > I feared. I could add more dead data. Or, I could just commit the test
> > to the back branches before 17. What do you think?
>
> How much margin of error do you have, in terms of total number of
> dead_items? That is, have you whittled it down to the minimum possible
> threshold for 2 passes?

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.

> Some logging with VACUUM VERBOSE (run on the ci instance) might be illuminating.

Vacuum verbose only will tell us the number of dead tuples and dead
item identifiers but not how much space they take up -- which is how
we decide whether or not to do index vacuuming.

- Melanie