Re: More logging for autovacuum

On Tue, Jul 7, 2015 at 11:20 AM, Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
wrote:

> On Sat, Jul 4, 2015 at 2:30 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> > On Thu, Jul 2, 2015 at 4:41 AM, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> >> log_min_messages acts as a single gate for everything headed for the
> >> server logs; controls for per-background process logging do not exist.
> If
> >> one wants to see DEBUG/INFO messages for just the Autovacuum (or
> >> checkpointer, bgwriter, etc.), they have to set log_min_messages to that
> >> level, but the result would be a lot of clutter from other processes to
> >> grovel through, to see the messages of interest.
> >>
> >
> > I think that will be quite helpful. During the patch development of
> > parallel sequential scan, it was quite helpful to see the LOG messages
> > of bgworkers, however one of the recent commits (91118f1a) have
> > changed those to DEBUG1, now if I have to enable all DEBUG1
> > messages, then what I need will be difficult to find in all the log
> > messages.
> > Having control of separate logging for background tasks will serve such
> > a purpose.
> >
> >> The facilities don't yet exist, but it'd be nice if such parameters when
> >> unset (ie NULL) pick up the value of log_min_messages. So by default,
> the
> >> users will get the same behaviour as today, but can choose to tweak per
> >> background-process logging when needed.
> >>
> >
> > Will this proposal allow user to see all the messages by all the
> background
> > workers or will it be per background task. Example in some cases user
> > might want to see the messages by all bgworkers and in some cases one
> > might want to see just messages by AutoVacuum and it's workers.
>

I want the logging to be controlled per background process, so that user
can pick and choose how much detail they want from each process. In absence
of such a setting for a background process, the global log_min_messages
should control the logging.

>
> > I think here designing User Interface is an important work if others also
> > agree
> > that such an option is useful, could you please elaborate your proposal?
>
> +1
>

I would like this feature to be as simple as the current log_min_messages;
currently a superuser can do ALTER USER X SET log_min_messages = Y, and
control logging for specific users or databases or a combination of those.

In the same vein, setting autovacuum.log_min_messages in postgresql.conf,
or a corresponding ALTER SYSTEM should be enough to use a different setting
of log_min_messages inside autovacuum launcher and its worker processes.

I am using autovacuum process as an example, but the same is applicable to
other background processes as well. E.g. checkpointer.log_min_messages.

As for the implementation details, upon startup and after every reload, the
autovacuum launcher process will look for autovacuum.log_min_messages being
defined; if yes, it'd set the global variable log_min_messages in code to
that value, and if not defined in any of the conf files, the global
variable in code would be assigned whatever is assigned to the GUC param
log_min_messages. DefineCustomXXVariable() seems incapable of supporting
this behaviour, so we might have to invent a new one.

This will allow us to keep the current behaviour as default when
autovacuum.log_min_messages is not defined, and provide finer control over
autovacuum's logging by defining this variable, when needed.

Same goes for Checkpointer, BGWriter, WALWriter, and BGWorkers. BTW, the
facility we invent needn't be limited to log_min_messages, but it's just
what we need now.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/