Alvaro Herrera <alvherre@commandprompt.com> writes:
> I've tinkered with this patch a bit. Sample output:

> LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0
> pages: removed 0, 11226 remain
> tuples: 1300683 removed, 1096236 remain
> system usage: CPU 0.29s/0.38u sec elapsed 2.56 sec

Please be a bit more consistent about whether numbers precede or follow
their descriptions. "x removed, y remain" might work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I've tinkered with this patch a bit. Sample output:
>
> > LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0
> > pages: removed 0, 11226 remain
> > tuples: 1300683 removed, 1096236 remain
> > system usage: CPU 0.29s/0.38u sec elapsed 2.56 sec
>
> Please be a bit more consistent about whether numbers precede or follow
> their descriptions. "x removed, y remain" might work.

Fixed.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Simon Riggs wrote:
> On Tue, 2007-04-17 at 14:06 -0400, Alvaro Herrera wrote:

> > I've tinkered with this patch a bit. Sample output:
> >
> > LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0
> > pages: removed 0, 11226 remain
> > tuples: 1300683 removed, 1096236 remain
> > system usage: CPU 0.29s/0.38u sec elapsed 2.56 sec
> >
> > Please comment.
>
> Well, 'tis great except when you have very very frequent autovacuums.
> That was why I wanted it in 1 log line.
>
> Perhaps we need this as an integer, so we can log all vacuums that last
> for longer in seconds than the setting, 0 logs all. That would
> significantly reduce the volume if we set it to 5, say. That way you
> would get your readability and I would get my reasonable size logs.

It kinda smells funny to have a setting like that. Do we have a
precedent? If other people is OK with it, I'll do that.

Would it work to add a separate GUC var to control the minimum autovac
time? Also, why do it by time and not by amount of tuples/pages
removed?

> Presumably you mean to have both removeds in the same order?
> > pages: 0 removed, 11226 remain
> > tuples: 1300683 removed, 1096236 remain

Right, fixed.

Also, here is the patch.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Simon Riggs wrote:
>> Perhaps we need this as an integer, so we can log all vacuums that last
>> for longer in seconds than the setting, 0 logs all. That would
>> significantly reduce the volume if we set it to 5, say. That way you
>> would get your readability and I would get my reasonable size logs.

> It kinda smells funny to have a setting like that. Do we have a
> precedent? If other people is OK with it, I'll do that.

Sure, log_min_duration_statement. Applying the same concept to autovac
logging makes sense to me. In fact, what you've got now is equivalent
to an unconditional log_statement boolean, which is about where we were
three or four releases ago on statement logging. History suggests that
we'd soon want it to be more flexible than that. It seems sane to me
to skip the boolean stage and go straight to a threshold parameter.

> Also, why do it by time and not by amount of tuples/pages removed?

One, it's consistent with the way we do thresholded statement logging.
Two, time is a better indicator of effort expended --- consider
vacuuming a very large table that has only a few dead tuples.

We might want to add logging thresholds for pages/tuples later, but
for now I'd vote for just one parameter: a time threshold.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Tue, 2007-04-17 at 14:45 -0400, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > On Tue, 2007-04-17 at 14:06 -0400, Alvaro Herrera wrote:
>
> > > I've tinkered with this patch a bit. Sample output:
> > >
> > > LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0
> > > pages: removed 0, 11226 remain
> > > tuples: 1300683 removed, 1096236 remain
> > > system usage: CPU 0.29s/0.38u sec elapsed 2.56 sec
> > >
> > > Please comment.
> >
> > Well, 'tis great except when you have very very frequent autovacuums.
> > That was why I wanted it in 1 log line.
> >
> > Perhaps we need this as an integer, so we can log all vacuums that last
> > for longer in seconds than the setting, 0 logs all. That would
> > significantly reduce the volume if we set it to 5, say. That way you
> > would get your readability and I would get my reasonable size logs.
>
> It kinda smells funny to have a setting like that. Do we have a
> precedent? If other people is OK with it, I'll do that.

log_temp_files works a bit like that, as does log_min_duration_statement

perhaps log_min_duration_vacuum?
that would be easier to understand

> Would it work to add a separate GUC var to control the minimum autovac
> time? Also, why do it by time and not by amount of tuples/pages
> removed?

....because you only want to know about slow vacuums.

Time is a more natural unit than tuples removed.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

In response to Alvaro Herrera <alvherre@commandprompt.com>:

> Simon Riggs wrote:
> > On Tue, 2007-04-17 at 14:06 -0400, Alvaro Herrera wrote:
>
> > > I've tinkered with this patch a bit. Sample output:
> > >
> > > LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0
> > > pages: removed 0, 11226 remain
> > > tuples: 1300683 removed, 1096236 remain
> > > system usage: CPU 0.29s/0.38u sec elapsed 2.56 sec
> > >
> > > Please comment.
> >
> > Well, 'tis great except when you have very very frequent autovacuums.
> > That was why I wanted it in 1 log line.
> >
> > Perhaps we need this as an integer, so we can log all vacuums that last
> > for longer in seconds than the setting, 0 logs all. That would
> > significantly reduce the volume if we set it to 5, say. That way you
> > would get your readability and I would get my reasonable size logs.
>
> It kinda smells funny to have a setting like that. Do we have a
> precedent? If other people is OK with it, I'll do that.
>
> Would it work to add a separate GUC var to control the minimum autovac
> time? Also, why do it by time and not by amount of tuples/pages
> removed?

When I submitted the log_temp_files stuff, there was considerable
discussion on the topic of how the GUC vars should be done. IIRC, the
eventual decision was to have a single GUC var, where -1 equated to
off, 0 equated to log all, and numbers higher than 0 were a size limit
on when things get logged.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

************************************************** **************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
************************************************** **************

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

BTW, shouldn't the log entry distinguish whether this was VACUUM,
ANALYZE, or both?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> BTW, shouldn't the log entry distinguish whether this was VACUUM,
> ANALYZE, or both?

We don't actually log anything for ANALYZE (the logging code is in
lazy_vacuum_rel).

Maybe it should be in autovacuum.c.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Tue, 2007-04-17 at 16:41 -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > BTW, shouldn't the log entry distinguish whether this was VACUUM,
> > ANALYZE, or both?
>
> We don't actually log anything for ANALYZE (the logging code is in
> lazy_vacuum_rel).

When ANALYZE starts taking some time, we can log it.

> Maybe it should be in autovacuum.c.

Well, I figured if it was the right place for VACUUM it was good enough
for autovacuum too.


--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera wrote:
> Tom Lane wrote:
> > BTW, shouldn't the log entry distinguish whether this was VACUUM,
> > ANALYZE, or both?
>
> We don't actually log anything for ANALYZE (the logging code is in
> lazy_vacuum_rel).
>
> Maybe it should be in autovacuum.c.

The only problem with this is that we would have to export struct
LVRelStats definition from vacuumlazy.c and pass the struct back ...
which wouldn't directly work on ANALYZE. What we could do is pass back
a "void *" pointer, which lazyvacuum.c or the analyze code knows how to
turn into a string.

I have no problem both ways. Here is the current patch (logging code
still in lazy_vacuum_rel, log_autovacuum morphed into an integer
variable).

Opinions?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match