Обсуждение: Re: [HACKERS] show "aggressive" or not in autovacuum logs
Hello, Currently the message shows the '%d skipped-frozen' message but it is insufficient to verify the true effect. This is a patch to show mode as 'aggressive' or 'normal' in the closing message of vacuum. %d frozen-skipped when 'aggressive mode' shows the true effect of ALL_FROZEN. I will add this patch to CF2017-09. At Tue, 4 Apr 2017 20:29:38 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in <CAD21AoBiw96efy+tynvMLFQWERfPnhO53B=XFW9yyzEJN-fNaA@mail.gmail.com> > On Tue, Apr 4, 2017 at 10:09 AM, Kyotaro HORIGUCHI > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > > | =# 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. Hmm. This appears to be in several form. https://www.postgresql.org/docs/devel/static/sql-vacuum.html > aggressive “freezing” of tuples. ... Aggressive freezing https://www.postgresql.org/docs/devel/static/routine-vacuuming.html > VACUUM will perform an aggressive vacuum, > an anti-wraparound autovacuum https://www.postgresql.org/docs/devel/static/runtime-config-client.html > ACUUM performs an aggressive scan ps title >.... (to prevent wraparound) The nearest common wording seems to be just aggressive (vacuum) so I left it alone in the attached patch. regards, -- Kyotaro Horiguchi NTT Open Source Software Center
On Mon, Aug 28, 2017 at 5:26 AM, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > Currently the message shows the '%d skipped-frozen' message but > it is insufficient to verify the true effect. This is a patch to > show mode as 'aggressive' or 'normal' in the closing message of > vacuum. %d frozen-skipped when 'aggressive mode' shows the true > effect of ALL_FROZEN. > > I will add this patch to CF2017-09. I would be a bit inclined to somehow show aggressive if it's aggressive and not insert anything at all otherwise. That'd probably require two separate translatable strings in each case, but maybe that's OK. What do other people think? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
https://www.postgresql.org/docs/devel/static/runtime- config-client.html
>VACUUM performs an aggressive scan
Maybe this should gets its own thread/patch but I'll tack this on here since it all seems related.
That paragraph you linked has a couple of typos:
"Although users can set this value anywhere from zero to two billions, VACUUM" ...
should be 'two billion' (i.e., drop the "s")
"...so that a periodical manual VACUUM has..."
'periodic' - though the description in the linked 24.1.5 is somewhat clearer (and longer) - the gap exists for the benefit of routine vacuum invocations to detect the need for an aggressive vacuum as part of a normal operating cycle rather than the last routine vacuum being non-aggressive and shortly thereafter an auto-vacuum anti-wraparound run is performed.
Current:
VACUUM will silently limit the effective value to 95% of autovacuum_freeze_max_age, so that a periodical manual VACUUM has a chance to run before an anti-wraparound autovacuum is launched for the table.
My interpretation:
VACUUM will silently limit the effective value to 95% of autovacuum_freeze_max_age so that a normal scan has a window within which to detect the need to convert itself to an aggressive scan and preempt the need for an untimely autovacuum initiated anti-wraparound scan.
As noted in the 24.1.5 that "normal scan" can be time scheduled or an update driven auto-vacuum one. It could be manual (though not really periodic) if one is monitoring the aging and is notified to run on manually when the age falls within the gap.
"Aggressive" sounds right throughout all of this.
Up-thread:
INFO: vacuuming "public.it" in aggressive mode
Maybe:
INFO: aggressively vacuuming "public.it"
INFO: vacuuming "public.it"
Likewise:
LOG: automatic vacuum of table "postgres.public.pgbench_
could be:
LOG: automatic aggressive vacuum of table "postgres.public.pgbench_branches", index scans: 0
Having read the docs and come to understand what "aggressive" means two wordings work for me (i.e., leaving non-aggressive unadorned).
David J.
On Tue, Aug 29, 2017 at 10:16 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Mon, Aug 28, 2017 at 5:26 AM, Kyotaro HORIGUCHI > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: >> Currently the message shows the '%d skipped-frozen' message but >> it is insufficient to verify the true effect. This is a patch to >> show mode as 'aggressive' or 'normal' in the closing message of >> vacuum. %d frozen-skipped when 'aggressive mode' shows the true >> effect of ALL_FROZEN. >> >> I will add this patch to CF2017-09. > > I would be a bit inclined to somehow show aggressive if it's > aggressive and not insert anything at all otherwise. That'd probably > require two separate translatable strings in each case, but maybe > that's OK. > > What do other people think? FWIW I prefer the Robert's idea; not insert anything if normal vacuum. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Thank you for the opinions. At Tue, 29 Aug 2017 15:00:57 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in <CAD21AoD76_QkYBwu8=NuMv0sp3Dec9X+DufB6-XbQyuuUUHh8g@mail.gmail.com> > On Tue, Aug 29, 2017 at 10:16 AM, Robert Haas <robertmhaas@gmail.com> wrote: > > On Mon, Aug 28, 2017 at 5:26 AM, Kyotaro HORIGUCHI > > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > >> Currently the message shows the '%d skipped-frozen' message but > >> it is insufficient to verify the true effect. This is a patch to > >> show mode as 'aggressive' or 'normal' in the closing message of > >> vacuum. %d frozen-skipped when 'aggressive mode' shows the true > >> effect of ALL_FROZEN. > >> > >> I will add this patch to CF2017-09. > > > > I would be a bit inclined to somehow show aggressive if it's > > aggressive and not insert anything at all otherwise. That'd probably > > require two separate translatable strings in each case, but maybe > > that's OK. > > > > What do other people think? > > FWIW I prefer the Robert's idea; not insert anything if normal vacuum. Though the form is intending to simplify parsing of the message, keeping the previous format if nothing special is convincing. How about the followings? "automatic [agressive ]vacuum of table \"%s..." "[aggressive ]vacuuming \"%s..." regards, -- Kyotaro Horiguchi NTT Open Source Software Center
Hello, At Mon, 28 Aug 2017 20:07:32 -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote in <CAKFQuwY4GBEzjwJwQRUDRD-1ZvUFXPTQOiiH4UrP1jn1sMGp1w@mail.gmail.com> > On Mon, Aug 28, 2017 at 2:26 AM, Kyotaro HORIGUCHI < > horiguchi.kyotaro@lab.ntt.co.jp> wrote: > > > https://www.postgresql.org/docs/devel/static/runtime-config-client.html > > > > > VACUUM performs an aggressive scan > > > > Maybe this should gets its own thread/patch but I'll tack this on here > since it all seems related. > > That paragraph you linked has a couple of typos: > > "Although users can set this value anywhere from zero to two billions, > VACUUM" ... > > should be 'two billion' (i.e., drop the "s") Sure. (and I would make the same mistake..) > "...so that a periodical manual VACUUM has..." > > 'periodic' - though the description in the linked 24.1.5 is somewhat > clearer (and longer) - the gap exists for the benefit of routine vacuum > invocations to detect the need for an aggressive vacuum as part of a normal > operating cycle rather than the last routine vacuum being non-aggressive > and shortly thereafter an auto-vacuum anti-wraparound run is performed. > > Current: > > VACUUM will silently limit the effective value to 95% of > autovacuum_freeze_max_age, so that a periodical manual VACUUM has a chance > to run before an anti-wraparound autovacuum is launched for the table. > > My interpretation: > > VACUUM will silently limit the effective value to 95% of > autovacuum_freeze_max_age so that a normal scan has a window within which > to detect the need to convert itself to an aggressive scan and preempt the > need for an untimely autovacuum initiated anti-wraparound scan. I haven't find the phrase "normal scan" in the documentation. Instaed, it seems to me that it should be "normal VACUUMs" as seen in 24.1.5, which is VACUUM command without FREEZE option. ("normal index scan" is another thing.) So more verbosely, the interpretation would be the following | VACUUM will silently limit the effective value to 95% of | autovacuum_freeze_max_age so that a periodic (manual) VACUUM | without FREEZE option has a window within which to detect the | need to convert itself to an aggressive VACUUM and preempt the | need for an untimely autovacuum initiated anti-wraparound scan. > As noted in the 24.1.5 that "normal scan" can be time scheduled or an > update driven auto-vacuum one. It could be manual (though not really > periodic) if one is monitoring the aging and is notified to run on manually > when the age falls within the gap. > > "Aggressive" sounds right throughout all of this. > > Up-thread: > INFO: vacuuming "public.it" in aggressive mode > > Maybe: > INFO: aggressively vacuuming "public.it" > INFO: vacuuming "public.it" Hmm. I believed that the 'vacuuming' is a noun and the phrase seen in my patch just submitted is 'aggressive vacuuming'. But of course it's highly probable that I'm wrong. > Likewise: > LOG: automatic vacuum of table "postgres.public.pgbench_branches": mode: > aggressive, index scans: 0 > > could be: > LOG: automatic aggressive vacuum of table > "postgres.public.pgbench_branches", index scans: 0 Yeah, this is just the same with mine. > Having read the docs and come to understand what "aggressive" means two > wordings work for me (i.e., leaving non-aggressive unadorned). > David J. regards, -- Kyotaro Horiguchi NTT Open Source Software Center
On Tue, Sep 5, 2017 at 3:41 PM, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > Thank you for the opinions. > > At Tue, 29 Aug 2017 15:00:57 +0900, Masahiko Sawada <sawada.mshk@gmail.com> wrote in <CAD21AoD76_QkYBwu8=NuMv0sp3Dec9X+DufB6-XbQyuuUUHh8g@mail.gmail.com> >> On Tue, Aug 29, 2017 at 10:16 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> > On Mon, Aug 28, 2017 at 5:26 AM, Kyotaro HORIGUCHI >> > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: >> >> Currently the message shows the '%d skipped-frozen' message but >> >> it is insufficient to verify the true effect. This is a patch to >> >> show mode as 'aggressive' or 'normal' in the closing message of >> >> vacuum. %d frozen-skipped when 'aggressive mode' shows the true >> >> effect of ALL_FROZEN. >> >> >> >> I will add this patch to CF2017-09. >> > >> > I would be a bit inclined to somehow show aggressive if it's >> > aggressive and not insert anything at all otherwise. That'd probably >> > require two separate translatable strings in each case, but maybe >> > that's OK. >> > >> > What do other people think? >> >> FWIW I prefer the Robert's idea; not insert anything if normal vacuum. > > Though the form is intending to simplify parsing of the message, > keeping the previous format if nothing special is convincing. > > How about the followings? > > "automatic [agressive ]vacuum of table \"%s..." > "[aggressive ]vacuuming \"%s..." > I've reviewed the latest patch and it looks good to me. I marked it as Ready for Committer. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Kyotaro HORIGUCHI wrote: > How about the followings? > > "automatic [agressive ]vacuum of table \"%s..." > "[aggressive ]vacuuming \"%s..." That form of log message seems acceptable to me (first one is missing a 'g'). In any case, please do not construct the sentence with %s expanding the word, because that is going to cause a problem for translations. Use an 'if' test with two full sentences instead. Yes, as Robert said, it's going to add more strings, but that's okay. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Thank you for the comment.
(Thank you Sawada-san for reviewng, too.)
At Thu, 19 Oct 2017 13:03:38 +0200, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in
<20171019110338.awwzc3y674co7wof@alvherre.pgsql>
> Kyotaro HORIGUCHI wrote:
>
> > How about the followings?
> >
> > "automatic [agressive ]vacuum of table \"%s..."
> > "[aggressive ]vacuuming \"%s..."
>
> That form of log message seems acceptable to me (first one is missing a 'g').
>
> In any case, please do not construct the sentence with %s expanding the
> word, because that is going to cause a problem for translations. Use an
> 'if' test with two full sentences instead. Yes, as Robert said, it's
> going to add more strings, but that's okay.
Thank you. I forgot that point. Changed them so that the messages
are detected as msgids.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
From 7252bfc0fafcf9d4d38067913325cf82c88d1e1e Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Mon, 28 Aug 2017 13:12:25 +0900
Subject: [PATCH] Show "aggressive" or not in vacuum messages
VACUUM VERBOSE or autovacuum emits log message with "%u skipped
frozen" but we cannot tell whether the vacuum was non-freezing (or not
aggressive) vacuum or freezing (or aggressive) vacuum having no tuple
to freeze. This patch adds indication of aggressive (auto)vacuum in
log messages and VACUUM VERBOSE message.
---src/backend/commands/vacuumlazy.c | 21 ++++++++++++++++-----1 file changed, 16 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 30b1c08..1080fcf 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -355,6 +355,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
params->log_min_duration)) { StringInfoData buf;
+ char *msgfmt; TimestampDifference(starttime, endtime, &secs, &usecs);
@@ -373,7 +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"),
+ if (aggressive)
+ msgfmt = _("automatic aggressive vacuum of table \"%s.%s.%s\": index scans: %d\n");
+ else
+ msgfmt = _("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n");
+ appendStringInfo(&buf, msgfmt, get_database_name(MyDatabaseId),
get_namespace_name(RelationGetNamespace(onerel)),
RelationGetRelationName(onerel),
@@ -486,10 +491,16 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats, pg_rusage_init(&ru0);
relname= RelationGetRelationName(onerel);
- ereport(elevel,
- (errmsg("vacuuming \"%s.%s\"",
- get_namespace_name(RelationGetNamespace(onerel)),
- relname)));
+ if (aggressive)
+ ereport(elevel,
+ (errmsg("aggressive vacuuming \"%s.%s\"",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ relname)));
+ else
+ ereport(elevel,
+ (errmsg("vacuuming \"%s.%s\"",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ relname))); empty_pages = vacuumed_pages = 0; num_tuples = tups_vacuumed = nkeep =
nunused= 0;
--
2.9.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Oct 26, 2017 at 10:18 AM, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > Thank you. I forgot that point. Changed them so that the messages > are detected as msgids. Committed, changing "aggressive" to "aggressively" in one place for correct English. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
At Thu, 26 Oct 2017 12:42:23 +0200, Robert Haas <robertmhaas@gmail.com> wrote in <CA+TgmoYf5mvxXh7yo_0nsGPWas_70epwatZ-uB7227TX1rY4WA@mail.gmail.com> > On Thu, Oct 26, 2017 at 10:18 AM, Kyotaro HORIGUCHI > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > > Thank you. I forgot that point. Changed them so that the messages > > are detected as msgids. > > Committed, changing "aggressive" to "aggressively" in one place for > correct English. Thank you for commiting! regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers