pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedmaximum for auto_explain.log_min_duration doesn't seem to make sense
6+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2018-02-23 8af87f411c 0222e897d8 be87cd2a0a 753875ed42 3ba503bde9 a7a6051cfd Allow auto_explain.log_min_duration to go up to INT_MAX.
* maximum for auto_explain.log_min_duration doesn't seem to make sense
@ 2018-02-23 18:34 Kevin Bloch <kev@codingthat.com>
2018-02-23 19:02 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Bloch @ 2018-02-23 18:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
According to https://dba.stackexchange.com/a/198429/28774 , this setting
maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or
any other number, it seems it should perhaps just be INT_MAX .
Cheers,
Kev
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: maximum for auto_explain.log_min_duration doesn't seem to make sense
2018-02-23 18:34 maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
@ 2018-02-23 19:02 ` David G. Johnston <david.g.johnston@gmail.com>
2018-02-23 19:07 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
2018-02-23 19:25 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: David G. Johnston @ 2018-02-23 19:02 UTC (permalink / raw)
To: Kevin Bloch <kev@codingthat.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com> wrote:
> According to https://dba.stackexchange.com/a/198429/28774 , this setting
> maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or any
> other number, it seems it should perhaps just be INT_MAX
I suspect that the counter to which that value is being compared also
wants to be an INT and if one checks for "val > INT_MAX" then val cannot be
restricted to an integer (and since we are capturing time we need some
unknown buffer).
As for the post question: What can I do if I want to log even
longer-running queries on a data warehouse?
The answer is "nothing special, anything running longer than the supplied
value will be logged". What you cannot do is choose not to log a subset of
queries that take longer INT_MAX/1,000 and less then infinity - once you
hit INT_MAX/1,000 you must log it.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: maximum for auto_explain.log_min_duration doesn't seem to make sense
2018-02-23 18:34 maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
2018-02-23 19:02 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David G. Johnston <david.g.johnston@gmail.com>
@ 2018-02-23 19:07 ` Kevin Bloch <kev@codingthat.com>
1 sibling, 0 replies; 6+ messages in thread
From: Kevin Bloch @ 2018-02-23 19:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On 02/23/2018 08:02 PM, David G. Johnston wrote:
> On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com
> <mailto:kev@codingthat.com>>wrote:
>
> According to https://dba.stackexchange.com/a/198429/28774
> <https://dba.stackexchange.com/a/198429/28774; , this setting maxes
> out at INT_MAX / 1000, but since it's never multiplied by 1000 or
> any other number, it seems it should perhaps just be INT_MAX
>
>
> I suspect that the counter to which that value is being compared also
> wants to be an INT and if one checks for "val > INT_MAX" then val cannot
> be restricted to an integer (and since we are capturing time we need
> some unknown buffer).
The unknown buffer is key here, it seems. Thanks for clarifying.
> As for the post question: What can I do if I want to log even
> longer-running queries on a data warehouse?
>
> The answer is "nothing special, anything running longer than the
> supplied value will be logged". What you cannot do is choose not to log
> a subset of queries that take longer INT_MAX/1,000 and less then
> infinity - once you hit INT_MAX/1,000 you must log it.
I had phrased that poorly, but you answered my actual meaning in the
end: A higher minimum isn't possible.
Thanks again,
Kev
>
> David J.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: maximum for auto_explain.log_min_duration doesn't seem to make sense
2018-02-23 18:34 maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
2018-02-23 19:02 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David G. Johnston <david.g.johnston@gmail.com>
@ 2018-02-23 19:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-23 19:27 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David G. Johnston <david.g.johnston@gmail.com>
2018-02-23 21:36 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David Gould <daveg@sonic.net>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 2018-02-23 19:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Kevin Bloch <kev@codingthat.com>; pgsql-bugs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com> wrote:
>> According to https://dba.stackexchange.com/a/198429/28774 , this setting
>> maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or any
>> other number, it seems it should perhaps just be INT_MAX
> I suspect that the counter to which that value is being compared also
> wants to be an INT and if one checks for "val > INT_MAX" then val cannot be
> restricted to an integer (and since we are capturing time we need some
> unknown buffer).
No, a look at the code shows that it's being compared to a double.
So there's no practical problem with increasing that GUC's limit to
INT_MAX. As to whether it's worth doing, the existing limit is equivalent
to ~35 minutes if I did the math right. I can barely imagine that anyone
would want to set auto_explain.log_min_duration higher than that, but
maybe in some huge data warehouse environment it'd make sense. Anyway,
I notice that the core backend's log_min_duration GUC, which is also
measured in ms, is allowed to go up to INT_MAX; so it seems a bit
inconsistent that this one isn't.
In short, seems like a valid complaint to me.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: maximum for auto_explain.log_min_duration doesn't seem to make sense
2018-02-23 18:34 maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
2018-02-23 19:02 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David G. Johnston <david.g.johnston@gmail.com>
2018-02-23 19:25 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-02-23 19:27 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2018-02-23 19:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Bloch <kev@codingthat.com>; pgsql-bugs@lists.postgresql.org
On Fri, Feb 23, 2018 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com>
> wrote:
> >> According to https://dba.stackexchange.com/a/198429/28774 , this
> setting
> >> maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or
> any
> >> other number, it seems it should perhaps just be INT_MAX
>
> > I suspect that the counter to which that value is being compared also
> > wants to be an INT and if one checks for "val > INT_MAX" then val cannot
> be
> > restricted to an integer (and since we are capturing time we need some
> > unknown buffer).
>
>
In short, seems like a valid complaint to me.
>
Yeah, I went back and pulled up the code and came to the same conclusion.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: maximum for auto_explain.log_min_duration doesn't seem to make sense
2018-02-23 18:34 maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
2018-02-23 19:02 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense David G. Johnston <david.g.johnston@gmail.com>
2018-02-23 19:25 ` Re: maximum for auto_explain.log_min_duration doesn't seem to make sense Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-02-23 21:36 ` David Gould <daveg@sonic.net>
1 sibling, 0 replies; 6+ messages in thread
From: David Gould @ 2018-02-23 21:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Kevin Bloch <kev@codingthat.com>; pgsql-bugs@lists.postgresql.org
On Fri, 23 Feb 2018 14:25:59 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> INT_MAX. As to whether it's worth doing, the existing limit is equivalent
> to ~35 minutes if I did the math right. I can barely imagine that anyone
> would want to set auto_explain.log_min_duration higher than that, but
> maybe in some huge data warehouse environment it'd make sense.
> In short, seems like a valid complaint to me.
A client has several production queries that commonly run an hour or two.
Sometimes queries are modified infelicitously or the planner is lead astray
due to statistics etc and they can run much longer unexpectedly. It would be
useful to set a threshold of a few hours to catch those as reproducing them
is a slow process.
-dg
--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-02-23 21:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-23 18:34 maximum for auto_explain.log_min_duration doesn't seem to make sense Kevin Bloch <kev@codingthat.com>
2018-02-23 19:02 ` David G. Johnston <david.g.johnston@gmail.com>
2018-02-23 19:07 ` Kevin Bloch <kev@codingthat.com>
2018-02-23 19:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-23 19:27 ` David G. Johnston <david.g.johnston@gmail.com>
2018-02-23 21:36 ` David Gould <daveg@sonic.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox