Re: On-demand running query plans using auto_explain and signals

On Tue, Sep 29, 2015 at 7:01 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On 25 September 2015 at 12:13, Shulgin, Oleksandr <
> oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
>
>
>> I now believe that use of ProcessInterrupts() in the recently proposed
>> design as well as manipulation of proc latch due to use of shared memory
>> queue are major blockers.
>>
>> In order to simplify things up, I've taken a step back and looked again
>> at the auto_explain contrib module. I now propose the most simple thing
>> possible in my opinion: a new GUC option for auto_explain. It will make
>> every backend, in which the module is loaded via *_preload_libraries
>> mechanism or using CREATE EXTENSION/LOAD commands, to actively publish the
>> plans of queries in dynamic shared memory as long as
>> auto_explain.publish_plans is turned on.
>>
>> The greatest part for me, is that this approach doesn't require handling
>> of signals and is isolated in an external module, so it can be readily used
>> with the current server versions, no need to wait for >= 9.6.
>>
>
> This is a major change of direction, so the thread title no longer applies
> at all.
>
> The requirement is to be able to see the output of EXPLAIN ANALYZE for a
> running process. Ideally, we would dump the diagnostic output for any
> process that runs longer than a specific timeout, so we can decide whether
> to kill it, or just save that for later diagnostics.
>
> I'm interested in helping the original goal happen. Dumping an EXPLAIN,
> without ANALYZE info, is not at all interesting since it has no value for
> immediate action or post-facto diagnosis, sorry to say - making it happen
> for every backend just makes it even less useful.
>

This is not a change of the direction, but rather of the approach. Hitting
a process with a signal and hoping it will produce a meaningful response in
all circumstances without disrupting its current task was way too naive.
I'd really want to make this work with ANALYZE, just not as the first
step. I believe this approach can be extended to include instrumentation
support (obviously we will not be able to contain this in the auto_explain
module).

--
Alex