Re: Count and log pages set all-frozen by vacuum

On Mon, Dec 2, 2024 at 9:28 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Fri, Nov 29, 2024 at 2:46 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > Finally, in case where we have:
> >
> > visibility map: 10000 pages set all-visible, 10000 pages set all-frozen.
> >
> > We can understand that 10000 pages newly became all-frozen, but have
> > no idea how many pages became all-visible but not all-frozen. It could
> > be even 0. Users might want to know it to understand how (auto)vacuum
> > and freezing are working well.
>
> I agree that this is possible, but I am not clear why the user should
> care. In scenario #1, the same pages were set all-visible and also
> all-frozen. In scenario #2, one set of pages were set all-visible and
> a different set of pages were set all-frozen. Scenario #2 is a little
> worse, for a couple of reasons. First, in scenario #2, more pages were
> dirtied to achieve the same result. However, if the user is concerned
> about the amount of I/O that vacuum is doing, they will more likely
> look at the "buffer usage" and "WAL usage" lines in the VACUUM verbose
> output rather than at the "visibility map" line. Second, in scenario
> #2, we have deferred some work to the future and there is a risk that
> the pages will remain all-visible but not all-frozen until the next
> aggressive vacuum. I think it is possible someone could want to see
> more detailed information for this reason.

Hmm. So at the risk of producing two loaves that are worse than none,
I've decided I like the "everything" approach:

visibility map: 5 pages newly set all-visible, of which 2 set
all-frozen. 2 all-visible pages newly set all-frozen.

With this I can clearly get any of the information I might want: the
number of pages that changed state, the total number of pages set
all-visible or all-frozen, and the total number of vmbits set. It
makes the all-visible but not all-frozen debt clear. Without
specifying how many of the pages it set all-frozen were all-visible,
you don't really get that. I originally envisioned this log message as
a way to know which vmbits were set. But it is kind of nice to know
which pages changed state too.

With three counters, the code and comment repetition is a bit trying,
but I don't think it is quite enough to justify a "log_vmbits_set()"
function.

Here's an example to exercise the new log message:

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 > 500;
vacuum (verbose) foo;
-- visibility map: 5 pages newly set all-visible, of which 2 set
all-frozen. 0 all-visible pages newly set all-frozen.
insert into foo select generate_series(1,500), 1;
vacuum (verbose, freeze) foo;
--visibility map: 3 pages newly set all-visible, of which 3 set
all-frozen. 2 all-visible pages newly set all-frozen.

- Melanie