Count and log pages set all-frozen by vacuum

From: Melanie Plageman <melanieplageman(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Count and log pages set all-frozen by vacuum
Date: 2024-10-30 21:42:21
Message-ID: CAAKRu_aJM+0Gwoq_+-sozMX8QEax4QeDhMvySxRt2ayteXJNCg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Vacuum currently counts and logs the number of pages of a relation
with newly frozen tuples. It does not count the number of pages newly
set all-frozen in the visibility map.

The number of pages set all-frozen in the visibility map by a given
vacuum can be useful when analyzing which normal vacuums reduce the
number of pages future aggressive vacuum need to scan.

Also, empty pages that are set all-frozen in the VM don't show up in
the count of pages with newly frozen tuples. When making sense of the
result of visibilitymap_summary() for a relation, it's helpful to know
how many pages were set all-frozen in the VM by each vacuum.

The attached patch set makes visibilitymap_set() return the old value
of the block's VM bits and then uses that to increment a new counter
in the LVRelState.

Here is example logging output with the patch:

create table foo (a int, b int) with (autovacuum_enabled = false);
insert into foo select generate_series(1,1000), 1;
delete from foo where a > 50;
vacuum (freeze, verbose) foo;

finished vacuuming "melanieplageman.public.foo": index scans: 0
pages: 4 removed, 1 remain, 5 scanned (100.00% of total)
tuples: 950 removed, 50 remain, 0 are dead but not yet removable
frozen: 1 pages from table (20.00% of total) had 50 tuples frozen. 5
pages set all-frozen in the VM

- Melanie

Attachment Content-Type Size
v1-0003-Count-pages-set-all-frozen-in-VM-during-vacuum.patch application/octet-stream 4.9 KB
v1-0002-Make-visibilitymap_set-return-previous-state-of-v.patch application/octet-stream 3.2 KB
v1-0001-Rename-LVRelState-frozen_pages.patch application/octet-stream 3.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2024-10-30 22:29:46 Re: New "raw" COPY format
Previous Message Devulapalli, Raghuveer 2024-10-30 21:36:00 RE: Popcount optimization using AVX512