Re: Logging pg_autovacuum

Martijn van Oosterhout wrote:
> On Fri, Apr 28, 2006 at 04:08:41PM -0400, Robert Treat wrote:
>> The first is to add a column(s) to pg_class to hold last
>> vaccum/analyze time
>> for each table. The upsides would be that this puts the information
>> in a readily accessable place that can be viewed from third party
>> tools and
>> queried against for easier management along with accomplishing what
>> the
>> current logging is giving you.
>
> You know, rather than adding new columns to pg_class, why not extend
> the stats collector to collect this information. This seems to fall
> well within its mandate and it already has the necessary
> infrastructure to store info like this. The people likely to want
> this sort of info are likely to be running the stats collector
> anyway. The GUC then becomes.
>
> stats_autovacuum= on
>
> No more fiddling with log levels or parsing log file, just:
>
> select <the info you want> from pg_stats_autovacuum;
>
> If this table is empty, it isn't running. How often do you want to
> know if it was running 5 hours ago?
>
> Thoughts?
[put just to the list, unless folks still want personal CC's.].

Since both vacuum and autovacuum will be cutting stats records, do we
want to just have the autovacuum
stats record have the fact that it was autovacuum that did the vacuum?

Or, is there a way when vacuum is run by autovacuum that I can get a
flag to set that says this
(vacuum|analyze) was done by the autovacuum daemon?

I agree that the existing stats calls are good, but I'm still reading
code to see whether I can determine
at the time they are cut that this was autovacuum that did it.

Thanks for the nice introductory project :)

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 Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote:
> Since both vacuum and autovacuum will be cutting stats records, do we
> want to just have the autovacuum
> stats record have the fact that it was autovacuum that did the vacuum?
>
> Or, is there a way when vacuum is run by autovacuum that I can get a
> flag to set that says this
> (vacuum|analyze) was done by the autovacuum daemon?
>
> I agree that the existing stats calls are good, but I'm still reading
> code to see whether I can determine
> at the time they are cut that this was autovacuum that did it.

I think noting autovac vacuums/analyzes seperately is 'nice-to-have' but
not all that important. It'd probably be pretty easy to tell the
difference just knowing what (if any) manual vacuums your system runs.

While we're looking at logging, are you going to add stats stuff for the
bgwriter as well, or should we add this to the TODO?
--
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