Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-03-03 Thread Robert Haas
On Wed, Mar 1, 2017 at 11:00 PM, Jeff Janes  wrote:
>> Because there are already various tools available to log activity of
>> session processes, but there are no other ways to log the activity of
>> autovacuum.  Why are the existing settings not sufficient for this
>> purpose?
>
> I've pretty often been annoyed that the information provided by 'VACUUM
> VERBOSE' and the information provided by log_autovacuum_min_duration are so
> drastically different from each other.  It would be pretty nice to have some
> way to get the same information for both operations in the same format,
> although I don't know if this proposal is the best way of accomplishing it.

Well said!  I actually quite agree with the goal of making it possible
to get that reporting for manual VACUUMs.  I just disagree with the
idea of tying it into this particular GUC.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-03-01 Thread Jeff Janes
On Wed, Mar 1, 2017 at 6:43 AM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 2/10/17 03:38, Simon Riggs wrote:
> > I guess its fairly obvious in the title, but
> > log_autovacuum_min_duration doesn't log VACUUMs only autovacuums.
> >
> > What isn't obvious is why that restruction is useful.
>
> Because there are already various tools available to log activity of
> session processes, but there are no other ways to log the activity of
> autovacuum.  Why are the existing settings not sufficient for this purpose?
>

I've pretty often been annoyed that the information provided by 'VACUUM
VERBOSE' and the information provided by log_autovacuum_min_duration are so
drastically different from each other.  It would be pretty nice to have
some way to get the same information for both operations in the same
format, although I don't know if this proposal is the best way of
accomplishing it.

Cheers,

Jeff


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-03-01 Thread Peter Eisentraut
On 2/10/17 03:38, Simon Riggs wrote:
> I guess its fairly obvious in the title, but
> log_autovacuum_min_duration doesn't log VACUUMs only autovacuums.
> 
> What isn't obvious is why that restruction is useful.

Because there are already various tools available to log activity of
session processes, but there are no other ways to log the activity of
autovacuum.  Why are the existing settings not sufficient for this purpose?

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-02-14 Thread Jim Nasby

On 2/13/17 11:12 AM, Robert Haas wrote:

FWIW, this is a significant problem outside of DDL. Once you're past 1-2
levels of nesting SET client_min_messages = DEBUG becomes completely
useless.

I think the ability to filter logging based on context would be very
valuable. AFAIK you could actually do that for manual logging with existing
plpgsql support, but obviously that won't help for anything else.

>

Well, that's moving the goalposts a lot further and in an unrelated
direction.


Short of someone getting a flash of brilliance, I agree. I tried 
indicating as much with my "FWIW" but obviously that wasn't explicit enough.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-02-13 Thread Robert Haas
On Mon, Feb 13, 2017 at 5:19 PM, Simon Riggs  wrote:
> On 13 February 2017 at 17:12, Robert Haas  wrote:
>> On Sun, Feb 12, 2017 at 9:20 PM, Jim Nasby  wrote:
>>> On 2/10/17 2:33 PM, Robert Haas wrote:
 That having been said, I think it could certainly be useful to have
 more control over what DDL gets logged in foreground processes.
>>>
>>> FWIW, this is a significant problem outside of DDL. Once you're past 1-2
>>> levels of nesting SET client_min_messages = DEBUG becomes completely
>>> useless.
>>>
>>> I think the ability to filter logging based on context would be very
>>> valuable. AFAIK you could actually do that for manual logging with existing
>>> plpgsql support, but obviously that won't help for anything else.
>>
>> Well, that's moving the goalposts a lot further and in an unrelated
>> direction.  I don't think that it's a good idea to change the
>> semantics of log_autovacuum_min_duration in the way Simon is proposing
>> for the reasons I noted, but I think that an acceptable patch could be
>> 100 lines of pretty straightforward code and documentation, like a new
>> GUC that controls this output for the vac-non-autovac case.
>
> If my idea would not log manual ANALYZE, well, we can add that in
> easily. There is no reason to block the patch for such a minor foible.
>
> This is a short patch to address a specific minor issue, not a blue
> sky redesign of logging.
>
> If someone else wants to add more, they can, later. Incremental
> change, just as happens all the time everywhere else.

Please don't ignore the other points in my original response.

Thanks,

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-02-13 Thread Simon Riggs
On 13 February 2017 at 17:12, Robert Haas  wrote:
> On Sun, Feb 12, 2017 at 9:20 PM, Jim Nasby  wrote:
>> On 2/10/17 2:33 PM, Robert Haas wrote:
>>> That having been said, I think it could certainly be useful to have
>>> more control over what DDL gets logged in foreground processes.
>>
>> FWIW, this is a significant problem outside of DDL. Once you're past 1-2
>> levels of nesting SET client_min_messages = DEBUG becomes completely
>> useless.
>>
>> I think the ability to filter logging based on context would be very
>> valuable. AFAIK you could actually do that for manual logging with existing
>> plpgsql support, but obviously that won't help for anything else.
>
> Well, that's moving the goalposts a lot further and in an unrelated
> direction.  I don't think that it's a good idea to change the
> semantics of log_autovacuum_min_duration in the way Simon is proposing
> for the reasons I noted, but I think that an acceptable patch could be
> 100 lines of pretty straightforward code and documentation, like a new
> GUC that controls this output for the vac-non-autovac case.

If my idea would not log manual ANALYZE, well, we can add that in
easily. There is no reason to block the patch for such a minor foible.

This is a short patch to address a specific minor issue, not a blue
sky redesign of logging.

If someone else wants to add more, they can, later. Incremental
change, just as happens all the time everywhere else.

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-02-13 Thread Robert Haas
On Sun, Feb 12, 2017 at 9:20 PM, Jim Nasby  wrote:
> On 2/10/17 2:33 PM, Robert Haas wrote:
>> That having been said, I think it could certainly be useful to have
>> more control over what DDL gets logged in foreground processes.
>
> FWIW, this is a significant problem outside of DDL. Once you're past 1-2
> levels of nesting SET client_min_messages = DEBUG becomes completely
> useless.
>
> I think the ability to filter logging based on context would be very
> valuable. AFAIK you could actually do that for manual logging with existing
> plpgsql support, but obviously that won't help for anything else.

Well, that's moving the goalposts a lot further and in an unrelated
direction.  I don't think that it's a good idea to change the
semantics of log_autovacuum_min_duration in the way Simon is proposing
for the reasons I noted, but I think that an acceptable patch could be
100 lines of pretty straightforward code and documentation, like a new
GUC that controls this output for the vac-non-autovac case.
Fine-grained control over which commands get logged in general is
harder, but it's still only a moderately complex patch (I think).
Filtering based on the context in which the logging is happening
sounds extremely complicated; I'm not sure what sort of design for
that would even be reasonable and it seems like there might be
innumerable requests for additional frammishes culminating in some
sort of mini-language and getting committed right around the time
Zefram Cochrane makes his inaugural flight.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-02-12 Thread Jim Nasby

On 2/10/17 2:33 PM, Robert Haas wrote:

That having been said, I think it could certainly be useful to have
more control over what DDL gets logged in foreground processes.


FWIW, this is a significant problem outside of DDL. Once you're past 1-2 
levels of nesting SET client_min_messages = DEBUG becomes completely 
useless.


I think the ability to filter logging based on context would be very 
valuable. AFAIK you could actually do that for manual logging with 
existing plpgsql support, but obviously that won't help for anything else.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs

2017-02-10 Thread Robert Haas
On Fri, Feb 10, 2017 at 3:38 AM, Simon Riggs  wrote:
> I guess its fairly obvious in the title, but
> log_autovacuum_min_duration doesn't log VACUUMs only autovacuums.
>
> What isn't obvious is why that restruction is useful.
>
> I say that it would be helpful to log all kinds of VACUUM, so we get
> similar output from all methods of submission.
>
> So, changes would be
>
> 1. Allow logging whether or not it is an autovacuum (attached)
>
> 2. Change name of parameter to ...
> a) log_vacuum_min_duration
> b) log_maintenance_min_duration and have it log CLUSTER, CREATE INDEX etc also
> c) log_ddl_min_duration and just time any DDL that takes a long time
> We could do any of those and they are all about as easy as one
> another, though the last one will be a bigger patch, so a) might be
> simpler.
>
> The following patch implements (1), but not yet (2) to allow debate.

Right now, the mission of log_autovacuum_min_duration is not to log
all automatic vacuums, but to log all actions taken by the autovacuum
workers, which include both VACUUM and ANALYZE.  With your proposed
patch, we'd log all VACUUM operations (whether or not performed by
autovacuum) and some ANALYZE operations (only if performed by
autovacuum).  That's not very logical, so -1 for the patch as
proposed.

That having been said, I think it could certainly be useful to have
more control over what DDL gets logged in foreground processes.  Right
now you can say log_statement=ddl, but you can't for example say
log_statement=vacuum,create_index,analyze,truncate.  That might not be
the right interface but certainly I think people would appreciate the
ability to log some DDL without logging all of it.  And maybe it's
useful to have a way to provide the same kind of logging that
log_autovacuum_min_duration does for foreground vacuums (and
analyzes?).  I'd make that a separate GUC, though.

One of the things to keep in mind here is that most people would log
everything if it had no downsides.  But it does - it takes CPU time
and I/O bandwidth and it makes it harder to find things in the logs.
So fine-grained control over what gets logged is important.  It's
probably not wise to just rip out log_autovacuum_min_duration and
replace it with something that has a much broader remit, because then
people who are happy with the amount of logging they're getting now
might suddenly find that they're getting too much.  I think the way
forward is to allow new options that add more logging rather than
making the existing options log more than they do now.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers