Re: autovacuum logging, part deux.

Rod Taylor wrote:
> I don't know about anyone else, but the only time I look at that mess
> is to find poor tuple/table or tuple/index ratios and other
> indications that vacuum isn't working as well as it should be.
>
> How about this instead:
>
> Log when the actual autovacuum_vacuum_scale_factor (dead space cleaned
> up) was more than 2 times the autovacuum_vacuum_scale_factor listed in
> postgresql.conf. This means autovacuum isn't keeping up to what you
> want it to.
>
> Another interesting case would be a large amount of empty space in the
> index or table (say 3x autovacuum_vacuum_scale_factor). This may
> indicate unnecessary bloat and something to fix.
>
> Aside from that, the raw numbers don't really interest me.
>

Does anyone think we should have a stats view for the last vacuum stats
for each table?

I.E. capture all the verbose info somewhere?

Or, do people just want to increase the logging?

I still don't see a consensus on what needs to come out.

Do we still need the autovacuum_verbosity type change?

LER

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry(dot)Rosenman(at)pervasive(dot)com
Web: www.pervasive.com

On Thu, 2006-05-04 at 11:25 -0500, Larry Rosenman wrote:
> Rod Taylor wrote:
> > I don't know about anyone else, but the only time I look at that mess
> > is to find poor tuple/table or tuple/index ratios and other
> > indications that vacuum isn't working as well as it should be.
> >
> > How about this instead:
> >
> > Log when the actual autovacuum_vacuum_scale_factor (dead space cleaned
> > up) was more than 2 times the autovacuum_vacuum_scale_factor listed in
> > postgresql.conf. This means autovacuum isn't keeping up to what you
> > want it to.
> >
> > Another interesting case would be a large amount of empty space in the
> > index or table (say 3x autovacuum_vacuum_scale_factor). This may
> > indicate unnecessary bloat and something to fix.
> >
> > Aside from that, the raw numbers don't really interest me.
> >
>
> Does anyone think we should have a stats view for the last vacuum stats
> for each table?

This would actually suit me better as it would be trivial to plug into a
monitoring system with home-brew per table thresholds at that point.

--

On Thu, May 04, 2006 at 12:37:48PM -0400, Rod Taylor wrote:
> On Thu, 2006-05-04 at 11:25 -0500, Larry Rosenman wrote:
> > Rod Taylor wrote:
> > > I don't know about anyone else, but the only time I look at that mess
> > > is to find poor tuple/table or tuple/index ratios and other
> > > indications that vacuum isn't working as well as it should be.
> > >
> > > How about this instead:
> > >
> > > Log when the actual autovacuum_vacuum_scale_factor (dead space cleaned
> > > up) was more than 2 times the autovacuum_vacuum_scale_factor listed in
> > > postgresql.conf. This means autovacuum isn't keeping up to what you
> > > want it to.
> > >
> > > Another interesting case would be a large amount of empty space in the
> > > index or table (say 3x autovacuum_vacuum_scale_factor). This may
> > > indicate unnecessary bloat and something to fix.
> > >
> > > Aside from that, the raw numbers don't really interest me.
> > >
> >
> > Does anyone think we should have a stats view for the last vacuum stats
> > for each table?
>
> This would actually suit me better as it would be trivial to plug into a
> monitoring system with home-brew per table thresholds at that point.

+1. But I also think it would be handy to have some means to better
control autovacuum logging, probably via something like
autovacuum_verbosity.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461