Обсуждение: 8.1.3 - autovacuum question

I turned on autovacuum on one of my database servers over the weekend.  I have been looking at the logs trying to see what it is doing.  However, all I see are lines like this:
,7674,,2006-06-26 16:31:37.650 EDT,,1,,,,LOG:  autovacuum: processing database "postgres"

Is there a way to tell if autovacuum is actually doing any work?  As far as I can see, it is running, but has not done any work.  Thanks for any help on tracking what autovacuum is doing.

Chris

Chris Hoover wrote:
> I turned on autovacuum on one of my database servers over the weekend.  I
> have been looking at the logs trying to see what it is doing.  However, all
> I see are lines like this:
> ,7674,,2006-06-26 16:31:37.650 EDT,,1,,,,LOG:  autovacuum: processing
> database "postgres"
>
> Is there a way to tell if autovacuum is actually doing any work?

Yeah, autovacuum is pretty stupid in its logging.  You can put
log_min_messages (or min_log_messages, I always forget which one it is)
DEBUG2 to get more detailed messages.  You'll get a lot other stuff as
well :-(

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

"Chris Hoover" <revoohc@gmail.com> writes:
> I turned on autovacuum on one of my database servers over the weekend.  I
> have been looking at the logs trying to see what it is doing.  However, all
> I see are lines like this:
> ,7674,,2006-06-26 16:31:37.650 EDT,,1,,,,LOG:  autovacuum: processing
> database "postgres"
> Is there a way to tell if autovacuum is actually doing any work?  As far as
> I can see, it is running, but has not done any work.

I think that's all you'll see at the default logging level.  There are
per-table messages at DEBUG2 level ... which will enable a whole lot of
other chatter as well :-(

            regards, tom lane

Are there any plans to fix this so we can see atleast the table activity (i.e. vacuuming table x) any time soon?

Thanks,

Chris

Yes, and I belive they're in HEAD now.

On Tue, Jun 27, 2006 at 12:49:25PM -0400, Chris Hoover wrote:
> Are there any plans to fix this so we can see atleast the table activity (
> i.e. vacuuming table x) any time soon?
>
> Thanks,
>
> Chris
>
> On 6/26/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> >"Chris Hoover" <revoohc@gmail.com> writes:
> >> I turned on autovacuum on one of my database servers over the
> >weekend.  I
> >> have been looking at the logs trying to see what it is doing.  However,
> >all
> >> I see are lines like this:
> >> ,7674,,2006-06-26 16:31:37.650 EDT,,1,,,,LOG:  autovacuum: processing
> >> database "postgres"
> >> Is there a way to tell if autovacuum is actually doing any work?  As far
> >as
> >> I can see, it is running, but has not done any work.
> >
> >I think that's all you'll see at the default logging level.  There are
> >per-table messages at DEBUG2 level ... which will enable a whole lot of
> >other chatter as well :-(
> >
> >                        regards, tom lane
> >

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Any chance of a backport to 8.1?  It sure would be a big help to be able to see what autvacuum is doing and to make sure that life is happy that way.

Thanks,

Chris

Chris Hoover wrote:
> Any chance of a backport to 8.1?  It sure would be a big help to be able to
> see what autvacuum is doing and to make sure that life is happy that way.

No, it is a feature addition.

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


>
> Thanks,
>
> Chris
>
> On 6/28/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> >
> > Yes, and I belive they're in HEAD now.
> >
> > On Tue, Jun 27, 2006 at 12:49:25PM -0400, Chris Hoover wrote:
> > > Are there any plans to fix this so we can see atleast the table activity
> > (
> > > i.e. vacuuming table x) any time soon?
> > >
> > > Thanks,
> > >
> > > Chris
> > >
> > > On 6/26/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > >
> > > >"Chris Hoover" <revoohc@gmail.com> writes:
> > > >> I turned on autovacuum on one of my database servers over the
> > > >weekend.  I
> > > >> have been looking at the logs trying to see what it is
> > doing.  However,
> > > >all
> > > >> I see are lines like this:
> > > >> ,7674,,2006-06-26 16:31:37.650 EDT,,1,,,,LOG:  autovacuum: processing
> > > >> database "postgres"
> > > >> Is there a way to tell if autovacuum is actually doing any work?  As
> > far
> > > >as
> > > >> I can see, it is running, but has not done any work.
> > > >
> > > >I think that's all you'll see at the default logging level.  There are
> > > >per-table messages at DEBUG2 level ... which will enable a whole lot of
> > > >other chatter as well :-(
> > > >
> > > >                        regards, tom lane
> > > >
> >
> > --
> > Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> > Pervasive Software      http://pervasive.com    work: 512-231-6117
> > vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> >

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Wed, Jun 28, 2006 at 10:28:31AM -0400, Bruce Momjian wrote:
> Chris Hoover wrote:
> > Any chance of a backport to 8.1?  It sure would be a big help to be able to
> > see what autvacuum is doing and to make sure that life is happy that way.
>
> No, it is a feature addition.

You could probably dig up the appropriate patch and apply it to 8.1
yourself, though. It should be pretty non-invasive.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461