Thread: autovacuum daemon question...

Hello,

I have not seen any information so far on this but is there a way to get the autovacuum daemon in PostgreSQL 8.1 to log the tables which it is perfoming vacuum or analyze operation on?

--
Joe Maldonado

I agree...for now while I'm developing and debugging my application I can have the logging be that verbose...I agree that these should be handled as info messages, since they are invaluable in investigating problems.

- Joe




--
Joe Maldonado

Joe Maldonado wrote:
> I have not seen any information so far on this but is there a way to
> get the autovacuum daemon in PostgreSQL 8.1 to log the tables which it
> is perfoming vacuum or analyze operation on?

I think the autovacuum daemon does log it's activity however all it's
output is set at DEBUG2 or DEBUG3, so you would have to turn up the
logging settings for the whole postmaster in order to see it.  I think
perhaps these should be INFO or something a little more common.

Joe Maldonado <joe.maldonado@gmail.com> writes:
> I agree...for now while I'm developing and debugging my application I can
> have the logging be that verbose...I agree that these should be handled as
> info messages, since they are invaluable in investigating problems.

This would be a pretty bad idea IMHO, since it would lead to bloating
the logs with autovacuum progress messages by default --- and whatever
you may think about it, I really doubt that the average DBA will want
those messages there all the time.

I wonder whether it would be practical to let the autovacuum daemon have
its own value of log_min_messages.  The alternative to that seems to be
to invent a new log severity level just for autovacuum, which is pretty
gross (especially since it's not obvious how it should sort relative to
LOG and DEBUG1).

            regards, tom lane

I like the idea of having a autovacuum_log_min_messages.  From my previous experience the pg_autovacuum.log files have been useful in diagnosing problems in the field.

- Joe




--
Joe Maldonado

Tom Lane wrote:
> This would be a pretty bad idea IMHO, since it would lead to bloating
> the logs with autovacuum progress messages by default --- and whatever
> you may think about it, I really doubt that the average DBA will want
> those messages there all the time.
>
> I wonder whether it would be practical to let the autovacuum daemon have
> its own value of log_min_messages.  The alternative to that seems to be
> to invent a new log severity level just for autovacuum, which is pretty
> gross (especially since it's not obvious how it should sort relative to
> LOG and DEBUG1).

While I personally think this would probably be a good idea, I also
recognize that it might be a solution in search of a problem.  Now that
we have integrated autovacuum we should probably wait and see what
feedback we get from the field.  That said, my argument for more
detailed logging of autovacuum activity is that vacuuming is VERY
important part of proper PG maintenance, as such lots of admins like to
keep a proactive eye to make sure there tables are getting the
maintenance they need.

Another thought:  How about adding something to the stats system that an
admin can turn on / off.  Maybe anew relation called
pg_stat_autovacuum_activity this would detail the last vacuum, last
analyze, number of vacuums / analyzes in the last 24 hours, last month
etc...  I dunno, whatever peopel think is relevant.

Thoughts?

Matt

>Another thought:  How about adding something to the stats system
>that an admin can turn on / off.  Maybe anew relation called
>pg_stat_autovacuum_activity this would detail the last vacuum, last
>analyze, number of vacuums / analyzes in the last 24 hours, last
>month etc...  I dunno, whatever peopel think is relevant.
>
>Thoughts?


My 2c ...

I would appreciate an easy way to keep tabs on autovacuum's activity.
A stat table seems like a practical way to have this info readily
available.

No need to keep values across dump/reloads, right?

- Jeff

--
Jeff Bohmer
VisionLink, Inc.

On Thu, 2005-11-10 at 02:04, Jeff Bohmer wrote:
> >Another thought:  How about adding something to the stats system
> >that an admin can turn on / off.  Maybe anew relation called
> >pg_stat_autovacuum_activity this would detail the last vacuum, last
> >analyze, number of vacuums / analyzes in the last 24 hours, last
> >month etc...  I dunno, whatever peopel think is relevant.
> >
> >Thoughts?
>
>
> My 2c ...
>
> I would appreciate an easy way to keep tabs on autovacuum's activity.
> A stat table seems like a practical way to have this info readily
> available.
>
> No need to keep values across dump/reloads, right?

This sounds more and more like a good idea.  I don't think there's any
need to maintain across dump/reload and / or database restart either.

Scott Marlowe wrote:
>> I would appreciate an easy way to keep tabs on autovacuum's activity.
>> A stat table seems like a practical way to have this info readily
>> available.
>>
>> No need to keep values across dump/reloads, right?
>>
>
> This sounds more and more like a good idea.  I don't think there's any
> need to maintain across dump/reload and / or database restart either.

The DBA can elect to keep or discard stats data across DB restart, in
fact I think the default value for this GUC var was set to true for the
8.1 release.

Matt

On Thu, 2005-11-10 at 09:56, Matthew T. O'Connor wrote:
> Scott Marlowe wrote:
> >> I would appreciate an easy way to keep tabs on autovacuum's activity.
> >> A stat table seems like a practical way to have this info readily
> >> available.
> >>
> >> No need to keep values across dump/reloads, right?
> >>
> >
> > This sounds more and more like a good idea.  I don't think there's any
> > need to maintain across dump/reload and / or database restart either.
>
> The DBA can elect to keep or discard stats data across DB restart, in
> fact I think the default value for this GUC var was set to true for the
> 8.1 release.

I thought we were discussing a theoretical / not yet in existence GUC
var...

Scott Marlowe wrote:
> On Thu, 2005-11-10 at 09:56, Matthew T. O'Connor wrote:
>
>> Scott Marlowe wrote:
>>
>>>> I would appreciate an easy way to keep tabs on autovacuum's activity.
>>>> A stat table seems like a practical way to have this info readily
>>>> available.
>>>>
>>>> No need to keep values across dump/reloads, right?
>>>>
>>>>
>>> This sounds more and more like a good idea.  I don't think there's any
>>> need to maintain across dump/reload and / or database restart either.
>>>
>> The DBA can elect to keep or discard stats data across DB restart, in
>> fact I think the default value for this GUC var was set to true for the
>> 8.1 release.
>>
>
> I thought we were discussing a theoretical / not yet in existence GUC
> var...

I assumed (perhaps incorrectly) that you were talking about maintaining
the data in the theoretical / not yet in existence autovacuum stats
table through database restart, the stats system already has a GUC var
that dictates whether or not it dumps it's data upon DB restart.

Matt

>>>>This sounds more and more like a good idea.  I don't think there's any
>>>>need to maintain across dump/reload and / or database restart either.
>>>>
>>>The DBA can elect to keep or discard stats data across DB restart,
>>>in fact I think the default value for this GUC var was set to true
>>>for the 8.1 release.
>>
>>I thought we were discussing a theoretical / not yet in existence GUC
>>var...
>
>I assumed (perhaps incorrectly) that you were talking about
>maintaining  the data in the theoretical / not yet in existence
>autovacuum stats table through database restart, the stats system
>already has a GUC var that dictates whether or not it dumps it's
>data upon DB restart.
>
>Matt


AFAIK, a restart does not affect the VACUUMed-ness of anything.
Keeping these (currently nonexistent autovacuum) stats across
restarts would be helpful if stats_reset_on_server_start=on.

Unless I misunderstand, if stats_reset_on_server_start=off, these
(currently nonexistent autovacuum) stats would only be relevant for
autovacuum's VACUUM activity and not it's ANALYZE activity.  In which
case, it seems ideal to keep autovacuum VACUUM stats regardless of
the GUC setting, while autovacuum ANALYZE stats should follow it.
But if the ideal is impractical, making both ANALYZE and VACUUM stats
follow the GUC would still be real nice.

- Jeff

--

Jeff Bohmer
VisionLink, Inc.

>AFAIK, a restart does not affect the VACUUMed-ness of anything.
>Keeping these (currently nonexistent autovacuum) stats across
>restarts would be helpful if stats_reset_on_server_start=on.


Sorry, got the meaning backwards.  The above should be
stats_reset_on_server_start=OFF.  And the below should be the
opposite, too.


>Unless I misunderstand, if stats_reset_on_server_start=off, these
>(currently nonexistent autovacuum) stats would only be relevant for
>autovacuum's VACUUM activity and not it's ANALYZE activity.


--

Jeff Bohmer
VisionLink, Inc.

Jeff Bohmer wrote:
>> I assumed (perhaps incorrectly) that you were talking about
>> maintaining  the data in the theoretical / not yet in existence
>> autovacuum stats table through database restart, the stats system
>> already has a GUC var that dictates whether or not it dumps it's data
>> upon DB restart.
> AFAIK, a restart does not affect the VACUUMed-ness of anything.
> Keeping these (currently nonexistent autovacuum) stats across restarts
> would be helpful if stats_reset_on_server_start=on.
>
> Unless I misunderstand, if stats_reset_on_server_start=off, these
> (currently nonexistent autovacuum) stats would only be relevant for
> autovacuum's VACUUM activity and not it's ANALYZE activity.  In which
> case, it seems ideal to keep autovacuum VACUUM stats regardless of the
> GUC setting, while autovacuum ANALYZE stats should follow it. But if
> the ideal is impractical, making both ANALYZE and VACUUM stats follow
> the GUC would still be real nice.

I'm confused...  the GUC var stats_reset_on_server_start dictates if the
stats system dumps its data on DB restart.  If we added a new table to
the stats system kept track of autovacuum activity, then that data would
also be dumped on restart if stats_reset_on_server_start=true.

>>Unless I misunderstand, if stats_reset_on_server_start=off, these
>>(currently nonexistent autovacuum) stats would only be relevant for
>>autovacuum's VACUUM activity and not it's ANALYZE activity.  In
>>which case, it seems ideal to keep autovacuum VACUUM stats
>>regardless of the GUC setting, while autovacuum ANALYZE stats
>>should follow it. But if the ideal is impractical, making both
>>ANALYZE and VACUUM stats follow the GUC would still be real nice.
>
>I'm confused...  the GUC var stats_reset_on_server_start dictates if
>the stats system dumps its data on DB restart.  If we added a new
>table to the stats system kept track of autovacuum activity, then
>that data would also be dumped on restart if
>stats_reset_on_server_start=true.


Yep.  I was thinking of a way to muck up the stats system by keeping
autovacuum's VACUUM activity irregardless of
stats_reset_on_server_start.  Because autovacuum's VACUUM activity
data would be relevant across restarts, even if
stats_reset_on_server_start=true.  But I see now that my idea is ugly
and just confuses things.  I agree that having it work the way you
suggest is preferable.

- Jeff


--

Jeff Bohmer
VisionLink, Inc.

Added to TODO for autovacuum:

        o Consider logging activity either to the logs or a system view


---------------------------------------------------------------------------

Jeff Bohmer wrote:
>
> >>Unless I misunderstand, if stats_reset_on_server_start=off, these
> >>(currently nonexistent autovacuum) stats would only be relevant for
> >>autovacuum's VACUUM activity and not it's ANALYZE activity.  In
> >>which case, it seems ideal to keep autovacuum VACUUM stats
> >>regardless of the GUC setting, while autovacuum ANALYZE stats
> >>should follow it. But if the ideal is impractical, making both
> >>ANALYZE and VACUUM stats follow the GUC would still be real nice.
> >
> >I'm confused...  the GUC var stats_reset_on_server_start dictates if
> >the stats system dumps its data on DB restart.  If we added a new
> >table to the stats system kept track of autovacuum activity, then
> >that data would also be dumped on restart if
> >stats_reset_on_server_start=true.
>
>
> Yep.  I was thinking of a way to muck up the stats system by keeping
> autovacuum's VACUUM activity irregardless of
> stats_reset_on_server_start.  Because autovacuum's VACUUM activity
> data would be relevant across restarts, even if
> stats_reset_on_server_start=true.  But I see now that my idea is ugly
> and just confuses things.  I agree that having it work the way you
> suggest is preferable.
>
> - Jeff
>
>
> --
>
> Jeff Bohmer
> VisionLink, Inc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073