Обсуждение: show "aggressive" or not in autovacuum logs
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,
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
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
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