Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: postgresql(at)taljaren(dot)se, pgsql-bugs(at)lists(dot)postgresql(dot)org, Michael Paquier <michael(at)paquier(dot)xyz>
Subject: Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)
Date: 2022-12-15 21:57:16
Message-ID: 3544447.1671141436@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Peter Geoghegan <pg(at)bowt(dot)ie> writes:
> On Thu, Dec 15, 2022 at 10:56 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> * A better idea, though sadly not very back-patchable, could
>> be to expose a VACUUM option to control whether it runs
>> vac_update_datfrozenxid, so that vacuumdb can do that just
>> once at the end. Considering that vac_update_datfrozenxid
>> requires an exclusive lock, the current behavior is poison for
>> parallel vacuuming quite aside from the O(N^2) issue. This
>> might tie into some work Peter G. has been pursuing, too.

> That sounds like a good idea to me. But do we actually need a VACUUM
> option for this? I wonder if we could get away with having the VACUUM
> command never call vac_update_datfrozenxid(), except when run in
> single-user mode. It would be nice to make pg_xact/clog truncation
> autovacuum's responsibility.

I could get behind manual VACUUM not invoking vac_update_datfrozenxid
by default, perhaps. But if it can never call it, then that is a
fairly important bit of housekeeping that is unreachable except by
autovacuum. No doubt the people who turn off autovacuum are benighted,
but they're still out there.

Could we get somewhere by saying that manual VACUUM calls
vac_update_datfrozenxid only if it's a full-DB vacuum (ie, no table
was specified)? That would fix the problem at hand. However, it'd
mean (since v12) that a vacuumdb run never calls vac_update_datfrozenxid
at all, which would result in horrible problems for any poor sods
who think that a cronjob running "vacuumdb -a" is an adequate substitute
for autovacuum.

Or maybe we could modify things so that "autovacuum = off" doesn't prevent
occasional cycles of vac_update_datfrozenxid-and-nothing-else?

In the end I feel like a manual way to call vac_update_datfrozenxid
would be the least magical way of running this.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kyotaro Horiguchi 2022-12-16 02:11:28 Re: pg_rewind succeed but postgresql showing error when trying to make standby with common ancestor
Previous Message Jean-Francois Levesque 2022-12-15 20:41:28 Re: BUG #17710: Slow queries (100% CPU) after auto-vacuum

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-12-15 22:18:03 Re: Error-safe user functions
Previous Message David Rowley 2022-12-15 21:33:13 Re: Speedup generation of command completion tags