From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: postgresql@taljaren.se, pgsql-bugs@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>
Subject: Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)
Date: Thu, 15 Dec 2022 16:57:16 -0500
Message-ID: <3544447.1671141436@sss.pgh.pa.us> (raw)
In-Reply-To: <CAH2-Wzne4Wgaaruq-VSY2qWNhxcPRqWUbdwVQPkM81TpaHY_kQ@mail.gmail.com>
References: <17717-6c50eb1c7d23a886@postgresql.org>
<3439037.1671130590@sss.pgh.pa.us>
<CAH2-Wzne4Wgaaruq-VSY2qWNhxcPRqWUbdwVQPkM81TpaHY_kQ@mail.gmail.com>
Peter Geoghegan <pg@bowt.ie> writes:
> On Thu, Dec 15, 2022 at 10:56 AM Tom Lane <tgl@sss.pgh.pa.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, pg@bowt.ie, michael@paquier.xyz
Subject: Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)
In-Reply-To: <3544447.1671141436@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox