Wasted Vacuum cycles when OldestXmin is not moving

From: sirisha chamarthi <sirichamarthi22(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Wasted Vacuum cycles when OldestXmin is not moving
Date: 2023-01-10 21:46:19
Message-ID: CAKrAKeW4CpPooWSnT9s3RbKznEh8YqYZNdDwEb_6BLr64pQXnA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Hackers,

vacuum is not able to clean up dead tuples when OldestXmin is not moving
(because of a long running transaction or when hot_standby_feedback is
behind). Even though OldestXmin is not moved from the last time it checked,
it keeps retrying every autovacuum_naptime and wastes CPU cycles and IOs
when pages are not in memory. Can we not bypass the dead tuple collection
and cleanup step until OldestXmin is advanced? Below log shows the vacuum
running every 1 minute.

2023-01-09 08:13:01.364 UTC [727219] LOG: automatic vacuum of table
"postgres.public.t1": index scans: 0
pages: 0 removed, 6960 remain, 6960 scanned (100.00% of total)
tuples: 0 removed, 1572864 remain, 786432 are dead but not yet
removable
removable cutoff: 852, which was 2 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0
dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13939 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.15 s, system: 0.00 s, elapsed: 0.29 s
2023-01-09 08:14:01.363 UTC [727289] LOG: automatic vacuum of table
"postgres.public.t1": index scans: 0
pages: 0 removed, 6960 remain, 6960 scanned (100.00% of total)
tuples: 0 removed, 1572864 remain, 786432 are dead but not yet
removable
removable cutoff: 852, which was 2 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0
dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13939 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.14 s, system: 0.00 s, elapsed: 0.29 s

Thanks,
Sirisha

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2023-01-10 22:13:06 Re: ATTACH PARTITION seems to ignore column generation status
Previous Message David Rowley 2023-01-10 21:29:06 Re: Allow DISTINCT to use Incremental Sort