From: Murthy Nunna <mnunna@fnal.gov>
To: depesz@depesz.com <depesz@depesz.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: RE: Vacuumdb on a table
Date: Thu, 19 Oct 2023 17:08:52 +0000
Message-ID: <DM8PR09MB6677862AC7E153C7F650935DB8D4A@DM8PR09MB6677.namprd09.prod.outlook.com> (raw)
In-Reply-To: <ZTDoaYVjQHgRFgU5@depesz.com>
References: <DM8PR09MB66773DD56212C1EEF997ECDBB8D5A@DM8PR09MB6677.namprd09.prod.outlook.com>
<ZTDoaYVjQHgRFgU5@depesz.com>
Thanks.
I retried with -F. That actually worked and lowered the relfrozenxid of the table.
1) I am wondering if -F option interferes with application (table lock, row lock etc).
2) It says "aggressively vacuuming "<table>". Do you always see this with -F option? Is it harmless in terms of locking select/insert/update/delete statements from application?
vacuumdb -v -d <database> -t <table> -F
vacuumdb: vacuuming database "<database>"
INFO: aggressively vacuuming "<table>"
INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO: table "<table>": found 0 removable, 172218350 nonremovable row versions in 6332166 out of 6332166 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1465648214
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 134.15 s, system: 199.88 s, elapsed: 1856.67 s.
INFO: aggressively vacuuming "pg_toast.pg_toast_<toastref>"
INFO: table "pg_toast_<toastref>": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1465693324
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
Thanks.
-----Original Message-----
From: depesz@depesz.com <depesz@depesz.com>
Sent: Thursday, October 19, 2023 3:27 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuumdb on a table
On Wed, Oct 18, 2023 at 08:05:53PM +0000, Murthy Nunna wrote:
> Hi,
>
> The first table in the following query resulted in age(c.relfrozenxid)
> = 148795396. But when I manually run vacuumdb command (vacuumdb -d db1
> -t tab1) on that table it is not lowering the relfrozenxid. There is
> no indication in the pglog that vacuumdb failed.
Retry with vacuumdb -F
Best regards,
depesz
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-admin@postgresql.org
Cc: mnunna@fnal.gov, depesz@depesz.com
Subject: RE: Vacuumdb on a table
In-Reply-To: <DM8PR09MB6677862AC7E153C7F650935DB8D4A@DM8PR09MB6677.namprd09.prod.outlook.com>
* 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