Обсуждение: Vacuumdb on a table
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.
SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC ;
I am wondering why vacuumdb is unable to lower relfrozenxid on this table? It seems to work on other tables though.
Thank you!
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.
SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC ;
I am wondering why vacuumdb is unable to lower relfrozenxid on this table? It seems to work on other tables though.
Thank you!
> On Oct 18, 2023, at 10:31 PM, Keith <keith@keithf4.com> wrote: > > Check pg_stat_activity or pg_locks to see if there are any open transactions or locks on the table in question. pg_stat_statements is good too write a couple of queries to find: - queries run most often - queries that are slowest - queries that are most executions x time the first one is good for finding badly-behaved applications or scripts; the others can point out need for indexes
Please make this vacuum "verbose".
Possible reasons VACUUM FREEZE do not work:
But these all should be reported in log.
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.
SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC ;
I am wondering why vacuumdb is unable to lower relfrozenxid on this table? It seems to work on other tables though.
Thank you!
I was thinking about hot-standby.
There is a possibility that on long lasting sql executed on hot-standby cause locks on blocks of table disallowing vacuum freeze.
Hi,There can be many reasons why vacuum do not work but definitely the reason should be recorded in the output log.
Please make this vacuum "verbose".
Possible reasons VACUUM FREEZE do not work:- table locked by some SQL - even on the standby replica- deadlock with some other vacuum process- table is corrupted - the worst case scenario- someone killed this process because was using to much resources (may be there is some other admin or someone set statement_timeout or something similar ? )
But these all should be reported in log.Regards Tomekśr., 18 paź 2023 o 22:12 Murthy Nunna <mnunna@fnal.gov> napisał(a):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.
SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC ;
I am wondering why vacuumdb is unable to lower relfrozenxid on this table? It seems to work on other tables though.
Thank you!
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/deletestatements 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
On Thu, 2023-10-19 at 17:08 +0000, Murthy Nunna wrote: > 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). No, it only does more work and wil use more resources. > 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? "Aggressive" is not as nasty as it sounds. It just means that it won't skip pages that are all-visible or pinned by other backends. My guess is that vacuumdb --disable-page-skipping --no-index-cleanup -d <database> -t <table> would have worked as well, and it would have been cheaper. Yours, Laurenz Albe