pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum daemon question...
16+ messages / 6 participants
[nested] [flat]
* autovacuum daemon question...
@ 2005-11-09 15:20 Joe Maldonado <joe.maldonado@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Joe Maldonado @ 2005-11-09 15:20 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-09 16:47 Matthew T. O'Connor <matthew@zeut.net>
parent: Joe Maldonado <joe.maldonado@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Matthew T. O'Connor @ 2005-11-09 16:47 UTC (permalink / raw)
To: Joe Maldonado <joe.maldonado@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-09 19:34 Joe Maldonado <joe.maldonado@gmail.com>
parent: Joe Maldonado <joe.maldonado@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Joe Maldonado @ 2005-11-09 19:34 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@tocr.com>; pgsql-admin
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
On 11/9/05, Matthew T. O'Connor <matthew@tocr.com> wrote:
>
> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-09 21:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joe Maldonado <joe.maldonado@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Tom Lane @ 2005-11-09 21:25 UTC (permalink / raw)
To: Joe Maldonado <joe.maldonado@gmail.com>; +Cc: Matthew T. O'Connor <matthew@tocr.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-09 21:34 Joe Maldonado <joe.maldonado@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Joe Maldonado @ 2005-11-09 21:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@tocr.com>; pgsql-admin
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
On 11/9/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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
>
--
Joe Maldonado
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-09 22:46 Matthew T. O'Connor <matthew@zeut.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Matthew T. O'Connor @ 2005-11-09 22:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 08:04 Jeff Bohmer <jdblist@visionlink.org>
parent: Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 16+ messages in thread
From: Jeff Bohmer @ 2005-11-10 08:04 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
>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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 15:18 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Jeff Bohmer <jdblist@visionlink.org>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2005-11-10 15:18 UTC (permalink / raw)
To: Jeff Bohmer <jdblist@visionlink.org>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 15:56 Matthew T. O'Connor <matthew@zeut.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 16+ messages in thread
From: Matthew T. O'Connor @ 2005-11-10 15:56 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Jeff Bohmer <jdblist@visionlink.org>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 16:03 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2005-11-10 16:03 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Jeff Bohmer <jdblist@visionlink.org>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 19:38 Matthew T. O'Connor <matthew@zeut.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 16+ messages in thread
From: Matthew T. O'Connor @ 2005-11-10 19:38 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Jeff Bohmer <jdblist@visionlink.org>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 20:06 Jeff Bohmer <jdblist@visionlink.org>
parent: Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 2 replies; 16+ messages in thread
From: Jeff Bohmer @ 2005-11-10 20:06 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Jeff Bohmer <jdblist@visionlink.org>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
>>>>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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 20:21 Jeff Bohmer <jdblist@visionlink.org>
parent: Jeff Bohmer <jdblist@visionlink.org>
1 sibling, 0 replies; 16+ messages in thread
From: Jeff Bohmer @ 2005-11-10 20:21 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
>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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 20:41 Matthew T. O'Connor <matthew@zeut.net>
parent: Jeff Bohmer <jdblist@visionlink.org>
1 sibling, 1 reply; 16+ messages in thread
From: Matthew T. O'Connor @ 2005-11-10 20:41 UTC (permalink / raw)
To: Jeff Bohmer <jdblist@visionlink.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-11-10 20:50 Jeff Bohmer <jdblist@visionlink.org>
parent: Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 16+ messages in thread
From: Jeff Bohmer @ 2005-11-10 20:50 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
>>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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: autovacuum daemon question...
@ 2005-12-04 04:03 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jeff Bohmer <jdblist@visionlink.org>
0 siblings, 0 replies; 16+ messages in thread
From: Bruce Momjian @ 2005-12-04 04:03 UTC (permalink / raw)
To: Jeff Bohmer <jdblist@visionlink.org>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Scott Marlowe <smarlowe@g2switchworks.com>; Joe Maldonado <joe.maldonado@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2005-12-04 04:03 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-09 15:20 autovacuum daemon question... Joe Maldonado <joe.maldonado@gmail.com>
2005-11-09 16:47 ` Matthew T. O'Connor <matthew@zeut.net>
2005-11-09 19:34 ` Joe Maldonado <joe.maldonado@gmail.com>
2005-11-09 21:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-09 21:34 ` Joe Maldonado <joe.maldonado@gmail.com>
2005-11-09 22:46 ` Matthew T. O'Connor <matthew@zeut.net>
2005-11-10 08:04 ` Jeff Bohmer <jdblist@visionlink.org>
2005-11-10 15:18 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-10 15:56 ` Matthew T. O'Connor <matthew@zeut.net>
2005-11-10 16:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-10 19:38 ` Matthew T. O'Connor <matthew@zeut.net>
2005-11-10 20:06 ` Jeff Bohmer <jdblist@visionlink.org>
2005-11-10 20:21 ` Jeff Bohmer <jdblist@visionlink.org>
2005-11-10 20:41 ` Matthew T. O'Connor <matthew@zeut.net>
2005-11-10 20:50 ` Jeff Bohmer <jdblist@visionlink.org>
2005-12-04 04:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox