Re: Logging pg_autovacuum

Larry Rosenman wrote:
> Simon Riggs wrote:
>> On Thu, 2006-04-27 at 14:53 -0400, Tom Lane wrote:
>>> "Larry Rosenman" <lrosenman(at)pervasive(dot)com> writes:
>>>> I'd like to see a more concrete definition of what we
>>>> want Autovacuum to output and at what levels.
>>>
>>> autovacuum_verbosity
>>
>> Should we call it autovacuum_messages?
>>
>> In current usage...
>>
>> _verbosity controls how much information each message gives
>> _messages controls what types of messages are logged
>
> That probably works, but I'm not sure about the one to add the VERBOSE
> to the
> VACUUM commands autovacuum.c emits.

does the following options satisfy everyone:

autovacuum_messages=
none silent (nothing output at LOG level)
database we'd output a LOG message processing database <name>
table we'd output a LOG message for each table we actually vacuum /
analyze
verbose we'd add the verbose flag to each command

the lower levels would include the upper (I.E. verbose implies table +
database).

If this is acceptable, I'm going to start working on the code to
implement it.

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 Friday 28 April 2006 12:09, Larry Rosenman wrote:
> Larry Rosenman wrote:
> > Simon Riggs wrote:
> >> On Thu, 2006-04-27 at 14:53 -0400, Tom Lane wrote:
> >>> "Larry Rosenman" <lrosenman(at)pervasive(dot)com> writes:
> >>>> I'd like to see a more concrete definition of what we
> >>>> want Autovacuum to output and at what levels.
> >>>
> >>> autovacuum_verbosity
> >>
> >> Should we call it autovacuum_messages?
> >>
> >> In current usage...
> >>
> >> _verbosity controls how much information each message gives
> >> _messages controls what types of messages are logged
> >
> > That probably works, but I'm not sure about the one to add the VERBOSE
> > to the
> > VACUUM commands autovacuum.c emits.
>
> does the following options satisfy everyone:
>
> autovacuum_messages=
> none silent (nothing output at LOG level)
> database we'd output a LOG message processing database <name>
> table we'd output a LOG message for each table we actually vacuum /
> analyze
> verbose we'd add the verbose flag to each command
>
> the lower levels would include the upper (I.E. verbose implies table +
> database).
>
> If this is acceptable, I'm going to start working on the code to
> implement it.
>

This would certainly be an improvement, but in the intrest of full discussion,
I want to toss out a couple of ideas (they are only partially thought out,
but I think could be useful)

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.

The second is to add a "verbosity level" to pg_autovacuum for each table, to
allow admins to configure specific tables for a more verbose logging. This
way if you have a perticular table that needs additional logging, this could
allow you to have only its vacuums emmitied at whichever log level seemed
appropriate.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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?
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> The first is to add a column(s) to pg_class to hold last vaccum/analyze time
> for each table.

I really don't want us to do that. relpages/reltuples are already an
ugly wart. The fundamental problem with this (or indeed any of the
various proposals for "let's make vacuum store results someplace")
is that vacuum is supposed to *clean up* dead rows. Not make more.
We don't want a pass of autovacuum to leave pg_class in a desperately-
needs-vacuumed-again state.

> The second is to add a "verbosity level" to pg_autovacuum for each table, to
> allow admins to configure specific tables for a more verbose logging.

No fundamental objection, but I can't really see the need either.

regards, tom lane

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> You know, rather than adding new columns to pg_class, why not extend
> the stats collector to collect this information.

+1

regards, tom lane

Should we make the whole postgres logging system configurable, similar to log4j
(or log4perl) rather than special-casing the autovacuum logs? Do we want to see
options added piecemeal to the conf file such as "autovacuum_messages=silent"?

mark

On Sat, Apr 29, 2006 at 10:05:51AM -0700, Mark Dilger wrote:
> Should we make the whole postgres logging system configurable, similar to log4j
> (or log4perl) rather than special-casing the autovacuum logs? Do we want to see
> options added piecemeal to the conf file such as "autovacuum_messages=silent"?

This does sound like a better idea, especially since there's call for
improving bgwriter and stats collector logging as well.
--
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