Re: On-demand running query plans using auto_explain and signals - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: On-demand running query plans using auto_explain and signals
Date
Msg-id CANP8+jJQA4OYCMu-FQeV-zXPFGQN0CUZBXq+cVEC_Pm32vw-1g@mail.gmail.com
Whole thread Raw
In response to Re: On-demand running query plans using auto_explain and signals  ("Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de>)
Responses Re: On-demand running query plans using auto_explain and signals
List pgsql-hackers
On 25 September 2015 at 12:13, Shulgin, Oleksandr <oleksandr.shulgin@zalando.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.

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

pgsql-hackers by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: PATCH: index-only scans with partial indexes
Next
From: Corey Huinker
Date:
Subject: Re: No Issue Tracker - Say it Ain't So!