Thread: show "aggressive" or not in autovacuum logs

show "aggressive" or not in autovacuum logs

From
Kyotaro HORIGUCHI
Date:
Hello, it would be too late but I'd like to propose this because
this cannot be back-patched.


In autovacuum logs, "%u skipped frozen" shows the number of pages
skipped by ALL_FROZEN only in aggressive vacuum.

So users cannot tell whether '0 skipped-frozen' means a
non-agressive vacuum or no frozen-pages in an agressive vacuum.

I think it is nice to have an indication whether the scan was
"agressive" or not in log output. Like this,

> LOG:  automatic aggressive vacuum of table "template1.pg_catalog.pg_statistic": index scans: 0

"0 skipped frozen" is uesless in non-aggressive vacuum but
removing it would be too-much.  Inserting "aggressive" reduces
machine-readability so it might be better in another place. The
attached patch does the following.

>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: normal, index scans: 0
>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: aggressive, index scans: 0

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 5b43a66..644c93c 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -374,10 +374,11 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,             * emitting
individualparts of the message when not applicable.             */            initStringInfo(&buf);
 
-            appendStringInfo(&buf, _("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n"),
+            appendStringInfo(&buf, _("automatic vacuum of table \"%s.%s.%s\": mode: %s, index scans: %d\n"),
                 get_database_name(MyDatabaseId),
get_namespace_name(RelationGetNamespace(onerel)),                            RelationGetRelationName(onerel),
 
+                             aggressive ? "aggressive" : "normal",
vacrelstats->num_index_scans);           appendStringInfo(&buf, _("pages: %u removed, %u remain, %u skipped due to
pins,%u skipped frozen\n"),                             vacrelstats->pages_removed, 

Re: show "aggressive" or not in autovacuum logs

From
Masahiko Sawada
Date:
On Wed, Mar 29, 2017 at 12:46 PM, Kyotaro HORIGUCHI
<horiguchi.kyotaro@lab.ntt.co.jp> wrote:
> Hello, it would be too late but I'd like to propose this because
> this cannot be back-patched.
>
>
> In autovacuum logs, "%u skipped frozen" shows the number of pages
> skipped by ALL_FROZEN only in aggressive vacuum.
>
> So users cannot tell whether '0 skipped-frozen' means a
> non-agressive vacuum or no frozen-pages in an agressive vacuum.
>
> I think it is nice to have an indication whether the scan was
> "agressive" or not in log output.

Good idea. I also was thinking about this.

> Like this,
>
>> LOG:  automatic aggressive vacuum of table "template1.pg_catalog.pg_statistic": index scans: 0
>
> "0 skipped frozen" is uesless in non-aggressive vacuum but
> removing it would be too-much.  Inserting "aggressive" reduces
> machine-readability so it might be better in another place. The
> attached patch does the following.
>
>>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: normal, index scans: 0
>>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: aggressive, index scans: 0
>

Should we add this even to the manual vacuum verbose message?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center



Re: show "aggressive" or not in autovacuum logs

From
Kyotaro HORIGUCHI
Date:
Hello,

At Fri, 31 Mar 2017 18:20:23 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in
<CAD21AoBN-Hsih7HEP8ey6NUwQgwYj0O7eMuaRtas-6+CyafOgA@mail.gmail.com>
> On Wed, Mar 29, 2017 at 12:46 PM, Kyotaro HORIGUCHI
> <horiguchi.kyotaro@lab.ntt.co.jp> wrote:
> > Hello, it would be too late but I'd like to propose this because
> > this cannot be back-patched.
> >
> >
> > In autovacuum logs, "%u skipped frozen" shows the number of pages
> > skipped by ALL_FROZEN only in aggressive vacuum.
> >
> > So users cannot tell whether '0 skipped-frozen' means a
> > non-agressive vacuum or no frozen-pages in an agressive vacuum.
> >
> > I think it is nice to have an indication whether the scan was
> > "agressive" or not in log output.
> 
> Good idea. I also was thinking about this.

Thanks. Currently we cannot use "skipped-frozen" to see the
effect of ALL_FROZEN.

> > Like this,
> >
> >> LOG:  automatic aggressive vacuum of table "template1.pg_catalog.pg_statistic": index scans: 0
> >
> > "0 skipped frozen" is uesless in non-aggressive vacuum but
> > removing it would be too-much.  Inserting "aggressive" reduces
> > machine-readability so it might be better in another place. The
> > attached patch does the following.
> >
> >>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: normal, index scans: 0
> >>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: aggressive, index scans: 0
> >
> 
> Should we add this even to the manual vacuum verbose message?

I forgot that. The patch adds the mode indication in the first
message of VACUUM VERBOSE.

| =# vacuum freeze verbose it;
| INFO:  vacuuming "public.it" in aggressive mode
| INFO:  "it": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
...
| Skipped 0 pages due to buffer pins, 0 frozen pages.

I still feel a bit uneasy about the word "aggressive" here. Is it
better to be "freezing" or something?

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: show "aggressive" or not in autovacuum logs

From
Masahiko Sawada
Date:
On Tue, Apr 4, 2017 at 10:09 AM, Kyotaro HORIGUCHI
<horiguchi.kyotaro@lab.ntt.co.jp> wrote:
> Hello,
>
> At Fri, 31 Mar 2017 18:20:23 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in
<CAD21AoBN-Hsih7HEP8ey6NUwQgwYj0O7eMuaRtas-6+CyafOgA@mail.gmail.com>
>> On Wed, Mar 29, 2017 at 12:46 PM, Kyotaro HORIGUCHI
>> <horiguchi.kyotaro@lab.ntt.co.jp> wrote:
>> > Hello, it would be too late but I'd like to propose this because
>> > this cannot be back-patched.
>> >
>> >
>> > In autovacuum logs, "%u skipped frozen" shows the number of pages
>> > skipped by ALL_FROZEN only in aggressive vacuum.
>> >
>> > So users cannot tell whether '0 skipped-frozen' means a
>> > non-agressive vacuum or no frozen-pages in an agressive vacuum.
>> >
>> > I think it is nice to have an indication whether the scan was
>> > "agressive" or not in log output.
>>
>> Good idea. I also was thinking about this.
>
> Thanks. Currently we cannot use "skipped-frozen" to see the
> effect of ALL_FROZEN.
>
>> > Like this,
>> >
>> >> LOG:  automatic aggressive vacuum of table "template1.pg_catalog.pg_statistic": index scans: 0
>> >
>> > "0 skipped frozen" is uesless in non-aggressive vacuum but
>> > removing it would be too-much.  Inserting "aggressive" reduces
>> > machine-readability so it might be better in another place. The
>> > attached patch does the following.
>> >
>> >>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: normal, index scans: 0
>> >>  LOG:  automatic vacuum of table "postgres.public.pgbench_branches": mode: aggressive, index scans: 0
>> >
>>
>> Should we add this even to the manual vacuum verbose message?
>
> I forgot that. The patch adds the mode indication in the first
> message of VACUUM VERBOSE.
>
> | =# vacuum freeze verbose it;
> | INFO:  vacuuming "public.it" in aggressive mode
> | INFO:  "it": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
> ...
> | Skipped 0 pages due to buffer pins, 0 frozen pages.
>
> I still feel a bit uneasy about the word "aggressive" here.

I think we can use the word "aggressive" here since we already use the
word "aggressive vacuum" in docs[1], but it might be easily
misunderstood.

[1] https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html

>Is it better to be "freezing" or something?

An another idea can be something like "prevent wraparound". The
autovaucum process doing aggressive vacuum appears in pg_stat_activity
with the word ".... (to prevent wraparound)". This word might be more
user friendly IMO.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center