Thread: [HACKERS] log_autovacuum_min_duration doesn't log VACUUMs
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. -- Simon Riggs 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
Attachment
On Fri, Feb 10, 2017 at 3:38 AM, Simon Riggs <simon@2ndquadrant.com> 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
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)
On Sun, Feb 12, 2017 at 9:20 PM, Jim Nasby <Jim.Nasby@bluetreble.com> 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
On 13 February 2017 at 17:12, Robert Haas <robertmhaas@gmail.com> wrote: > On Sun, Feb 12, 2017 at 9:20 PM, Jim Nasby <Jim.Nasby@bluetreble.com> 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 Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Feb 13, 2017 at 5:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On 13 February 2017 at 17:12, Robert Haas <robertmhaas@gmail.com> wrote: >> On Sun, Feb 12, 2017 at 9:20 PM, Jim Nasby <Jim.Nasby@bluetreble.com> 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
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)
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
On Wed, Mar 1, 2017 at 6:43 AM, Peter Eisentraut <peter.eisentraut@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
On Wed, Mar 1, 2017 at 11:00 PM, Jeff Janes <jeff.janes@gmail.com> 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