Re: Increasing the length of

I've been using "log_min_duration_statement = 0" to get durations on all
SQL statements for the purposes of performance tuning, because this logs
the duration on the same line as the statement. My reading of this TODO
is that now log_min_duration_statement = 0 would give me the statements
but no total duration?

- DAP

>-----Original Message-----
>From: pgsql-hackers-owner(at)postgresql(dot)org
>[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Bruce Momjian
>Sent: Tuesday, November 30, 2004 1:20 PM
>To: Simon Riggs
>Cc: Andrew Sullivan; pgsql-hackers(at)postgresql(dot)org
>Subject: Re: [HACKERS] Increasing the length of
>
>
>Great idea. Added to TODO:
>
>* Make log_min_duration_statement output when the duration is
>reached rather
> than when the statement completes
>
> This prints long queries while they are running, making
>trouble shooting
> easier. Also, it eliminates the need for log_statement because it
> would now be the same as a log_min_duration_statement of zero.
>
>---------------------------------------------------------------
>------------
>
>Simon Riggs wrote:
>> On Wed, 2004-11-10 at 22:51, Andrew Sullivan wrote:
>> > On Wed, Nov 10, 2004 at 09:52:17PM +0000, Simon Riggs wrote:
>> > > On Wed, 2004-11-10 at 21:48, Richard Huxton wrote:
>> > > >
>> > > > Isn't that:
>> > > > log_min_duration_statement (integer)
>> > >
>> > > That gets written when a statement completes, not during
>execution.
>> >
>> > I've been following this thread, and I was thinking the
>same thing.
>> > I wonder how much work it'd be to have another log setting -- say
>> > log_statement_after_min_duration (integer) -- which did what Simon
>> > wants. That'd more than satisfy my need, for sure. Might
>the cost
>> > of that be too high, though?
>>
>> I think this is a great idea.
>>
>> ...Rather than invent a new GUC, I think this is the solution:
>>
>> log_min_duration_statement writes to log at end of execution, if
>> execution time is greater than that threshold. Let's move that piece
>> of code so it is executed as the query progresses. That way, you get
>> notified that a problem query is occurring NOW, rather than "it has
>> occurred".
>>
>> The code already has such a timer check, for statement_timeout, in
>> backend/storage/lmgr/proc.c. We could reuse this timer to go off at
>> log_min_duration_statement and then log query if still
>executing. (If
>> log_min_duration_statement >= statement_timeout, we would skip that
>> step.) We would then reset the timer so that it then goes
>off at where
>> it does now, at statement_timeout. So, same piece of code,
>used twice...
>>
>> That way you can set up 2 limits, with three bands of actions:
>>
>> Between 0 and log_min_duration_statement
>> - logs nothing
>>
>> Between log_min_duration_statement and statement_timeout
>> - statement written to log, though execution continues...
>>
>> At statement_timeout
>> - statement cancelled
>>
>> We'd just need a small piece of code to set timer correctly first,
>> then another piece to record state change and reset timer
>again. Lift
>> and drop the existing code from end-of-execution.
>>
>> This then:
>> - solves the *problem query* diagnosis issue, as originally
>raised by
>> Sean and seconded by myself and Greg
>> - makes the answer exactly what Tom proposed - look in the logs
>> - doesn't make any changes to the technical innards of UDP and
>> pgstats.c
>> - no administrative interface changes, just slightly changed
>behaviour
>> - existing users mostly wouldn't even notice we'd done it...
>>
>> Thoughts?
>>
>> Easy enough change to be included as a hot fix for 8.0: no
>new system
>> code, no new interface code, just same behaviour at different time.
>>
>>
>> --
>> Best Regards, Simon Riggs
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an
>index scan if your
>> joining column's datatypes do not match
>>
>
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square,
>Pennsylvania 19073
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index
>scan if your
> joining column's datatypes do not match
>