Re: Count and log pages set all-frozen by vacuum - Mailing list pgsql-hackers

On Tue, Nov 26, 2024 at 12:37 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> On Tue, Nov 26, 2024 at 1:55 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > Just to be clear, do users want the number of updated VM bits or the
> > number of pages whose visibility information is updated? For example,
> >
> > > visibility map: 5 pages set all-visible, 4 pages set all-frozen.
> >
> > IIUC the above log can be interpreted in two ways in terms of the
> > number of pages:
> >
> > (a) 5 pages are marked as all-visible, and other 4
> > (already-marked-as-all-visible) pages are marked as all-frozen. That
> > is, 9 VM bits for 9 pages in total got updated.
> > (b) 1 page is marked as all-visible, and other 4 pages are marked as
> > all-frozen (and all-visible as well).  That is, 9 VM bits for 5 pages
> > in total got updated.
> >
> > If users want to know "how many VM bits were updated?", the above log
> > makes sense. But there is no clear answer to "How many pages were
> > updated in terms of VM?".
>
> Ah, good point. With a spin on the earlier example:
>
> 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 set all-visible, 2 pages set all-frozen.
>
> 5 pages were set all-visible and, of those, 2 were set all-frozen. So,
> 3 were set only all-visible. This is like (b) in your description.
>
> However, now if we do:
>
> vacuum (verbose, freeze) foo;
> visibility map: 0 pages set all-visible, 3 pages set all-frozen.
>
> Here, 3 already all-visible pages were set all-frozen.
> This does currently tell you the number of bits newly set, not the
> number of pages' whose VM status changed state.
>
> In fact, you could have a case where it is even more difficult to tell
> the total number of pages' whose VM status was updated. Let's say the
> first vacuum sets 5 pages newly all-visible, and of those, 2 are set
> all-frozen. Separately, 2 all-visible pages elsewhere in the relation
> are scanned (say due to SKIP_PAGES_THRESHOLD) and are old enough to
> require freezing. The message would be:
>
> visibility map: 5 pages set all-visible, 4 pages set all-frozen.
>
> But, we know 2 pages were set all-visible and all-frozen, 3 were set
> only all-visible, and 2 all-visible pages were set all-frozen. That's
> seven pages changing state. You would have no idea how many total
> pages changed state from the log message.

True.

> So, since the transitions that are possible here are:
> nothing -> all-visible
> nothing -> all-visible and all-frozen
> all-visible -> all-visible and all-frozen
>
> What if we changed the message to reflect these state changes:
>
> visibility map: 5 pages newly set all-visible, of which 2 set
> all-frozen. 2 all-visible pages newly set all-frozen.

While it's more precise, I'm not sure it is useful from the user
perspective to distinguish the last two cases (i.e. setting
all-visible & all-frozen bits and setting only all-frozen bit). I
think it makes sense to have both the number of pages newly marked as
all-visible and the number of pages newly marked as all-frozen. How
about showing these two pieces of information? That is, the log
message doesn't change but we don't double count the pages that are
marked as all-frozen and all-visible. For the original example,

> visibility map: 5 pages set all-visible, 4 pages set all-frozen.

Which means that 5 pages were marked as only all-visible and 4 pages
were marked as all-frozen (and possibly all-visible too). The total
number of pages' whose VM status changed is the sum of these two
numbers, 9 pages. We would have no idea how many total VM bits were
set, though.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com