Обсуждение: Query plan for currently executing query?

Hi!

Is it possible to get the plan of a query that's currently running? I have queries which normally take about 15
minutes,but are now at 2+ hours. I forgot to add the code to dump the plan to STDOUT, so I really don't know. 

It might be data volume, but I want to confirm that the plan isn't bogus.

I'm doing batch imports of data on "PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5)4.6.3, 64-bit". 

Thanks,
François Beausoleil

François Beausoleil wrote (on 18/09/2013):
> Hi!
>
> Is it possible to get the plan of a query that's currently running? I have queries which normally take about 15
minutes,but are now at 2+ hours. I forgot to add the code to dump the plan to STDOUT, so I really don't know. 
>
> It might be data volume, but I want to confirm that the plan isn't bogus.
>
> I'm doing batch imports of data on "PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5)4.6.3, 64-bit". 
>
> Thanks,
> François Beausoleil
>

Running an EXPLAIN on the exact query should give you the plan. An
EXPLAIN ANALYZE would have to wait for the query to complete either way,
so you wouldn't be able to get it mid-way through a running process.

If you don't know the exact query running, then if the
stats_command_string config setting is on, you should be able to get it
by querying the pg_stat_activity view.

The only other problem I can think of is if there are temporary tables
or other session-specific objects that would exist only within the
running process.

Another possibility to consider (which would also show up in
pg_stat_activity or similar views) is that the query is waiting on some
kind of lock, rather than just executing slowly.

Regards,
--
Rowan Collins
[IMSoP]

On 18/09/2013 14:44, "Rowan Collins" <rowan.collins@gmail.com> wrote:

>
>Running an EXPLAIN on the exact query should give you the plan. An
>EXPLAIN ANALYZE would have to wait for the query to complete either way,
>so you wouldn't be able to get it mid-way through a running process.
>
>If you don't know the exact query running, then if the
>stats_command_string config setting is on, you should be able to get it
>by querying the pg_stat_activity view.
>
>The only other problem I can think of is if there are temporary tables
>or other session-specific objects that would exist only within the
>running process.
>
>Another possibility to consider (which would also show up in
>pg_stat_activity or similar views) is that the query is waiting on some
>kind of lock, rather than just executing slowly.


I think the OP was hoping for a solution that would allow him to retrieve
the query plan that was generated at execution time.  I've certainly
wished for this type of facility in the past, in the scenario where a
given table may have been ANALYZE in the interim - making it difficult to
perform any useful diagnostics on the problem query.

In cases such as that, it is sometimes better to cancel the currently
running query and restart it in order to take advantage of the newer
statistics and generate a better QEP.


What might be a useful feature addition, is if at the time of the ANALYZE
operation - postgres could identify those currently running queries and
flag them as having begun execution with a dirty/stale QEP.  Possibly this
could be extended to heuristically determine if a query might return
faster if it were cancelled and re-executed under a new QEP?

Tim

On Wed, 18 Sep 2013 16:24:16 +0100
Tim Kane <tim.kane@gmail.com> wrote:

>
>
> On 18/09/2013 14:44, "Rowan Collins" <rowan.collins@gmail.com> wrote:
>
> >
> >Running an EXPLAIN on the exact query should give you the plan. An
> >EXPLAIN ANALYZE would have to wait for the query to complete either
> >way, so you wouldn't be able to get it mid-way through a running
> >process.
> >
> >If you don't know the exact query running, then if the
> >stats_command_string config setting is on, you should be able to get
> >it by querying the pg_stat_activity view.
> >
> >The only other problem I can think of is if there are temporary
> >tables or other session-specific objects that would exist only
> >within the running process.
> >
> >Another possibility to consider (which would also show up in
> >pg_stat_activity or similar views) is that the query is waiting on
> >some kind of lock, rather than just executing slowly.
>
>
> I think the OP was hoping for a solution that would allow him to
> retrieve the query plan that was generated at execution time.  I've
> certainly wished for this type of facility in the past, in the
> scenario where a given table may have been ANALYZE in the interim -
> making it difficult to perform any useful diagnostics on the problem
> query.
>

Maybe auto-explain is the solution?

http://www.postgresql.org/docs/9.1/static/auto-explain.html

Hi,

I use PG 9.2.4 with streaming replication.  What will be the manual procedure to failover from Primary to Standby and
Setthe old Primary as a new standby? 


step 1:  standby's recovery.conf :
# Specifies a trigger file whose presence should cause streaming replication to end (i.e., failover).
trigger_file = '/var/lib/postgresql/main/trigger'

step 2:  To trigger a smart failover, create a trigger file containing the word smart, or just create it and leave it
empty.(e.g.  
vi  /var/lib/postgresql/main/trigger
smart

step 3: wait until the failover is completed, the server is brought up after applying all WAL files available in the
archive. 
How to check if the failover is completed and the new Primary is ready?

step 4: if the failover is done
Do I need to edit the new primary's postgresql.conf and restart postgresql? e.g. comment out the hot_standy = on

step 5: how to quickly set the old primary as a new standby?
    SELECT pg_start_backup('replbackup');
    tar cfP /home/postgres/pg_backup.tar $PG_DATA
    SELECT pg_stop_backup();
    send  /home/postgres/pg_backup.tar to the old primary
    unzip the tar file to $PG_DATA & delete postmaster.pid
    create the recovery.conf
    edit the postgresql.conf to enable "hot_standby = on"
    start postgresql
if the PG_DATA has 600GB data, is there a quicker way to set the old primary as a new standby?


regards

On Thu, Sep 19, 2013 at 11:31 AM, ascot.moss@gmail.com <ascot.moss@gmail.com> wrote:

I use PG 9.2.4 with streaming replication.  What will be the manual procedure to failover from Primary to Standby and Set the old Primary as a new standby?

From what I understand, you start over by setting up the old primary as a new standby from scratch.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Assuming the database hasn't changed much since the failover, doing a
fsync from the new primary back to the old primary should be fairly
quick.
--
Mike Nolan

On 9/19/13, Vick Khera <vivek@khera.org> wrote:
> On Thu, Sep 19, 2013 at 11:31 AM, ascot.moss@gmail.com
> <ascot.moss@gmail.com
>> wrote:
>
>> I use PG 9.2.4 with streaming replication.  What will be the manual
>> procedure to failover from Primary to Standby and Set the old Primary as
>> a
>> new standby?
>>
>
> From what I understand, you start over by setting up the old primary as a
> new standby from scratch.
>

On 9/19/13, John R Pierce <pierce@hogranch.com> wrote:
> On 9/19/2013 1:29 PM, Vick Khera wrote:
>>
>> On Thu, Sep 19, 2013 at 11:31 AM, ascot.moss@gmail.com
>> <mailto:ascot.moss@gmail.com> <ascot.moss@gmail.com
>> <mailto:ascot.moss@gmail.com>> wrote:
>>
>>     I use PG 9.2.4 with streaming replication.  What will be the
>>     manual procedure to failover from Primary to Standby and Set the
>>     old Primary as a new standby?
>>
>>
>> From what I understand, you start over by setting up the old primary
>> as a new standby from scratch.
>
> if you use rsync for the base backup of new master to old, it should go
> fairly quickly as relatively few files should have changed assuming not
> much time has elapsed.

Of course, before you do anything, you should spend some time figuring
out WHY the old master failed.  There could be issues that need to be
resolved before putting it back online, and fixing them could affect
how much work you have to do to get the physical files back in sync.
--
Mike Nolan

Hi,

How about the following two areas?

step 3: wait until the failover is completed, the server is brought up after applying all WAL files available in the
archive. 
Question 1: How to check if the failover is completed and the new Primary is ready?

step 4: if the failover is done
Question 2: Do I need to edit the new primary's postgresql.conf and restart postgresql? e.g. comment out the hot_standy
=on  

thanks

On 20 Sep 2013, at 4:39 AM, Michael Nolan wrote:

> On 9/19/13, John R Pierce <pierce@hogranch.com> wrote:
>> On 9/19/2013 1:29 PM, Vick Khera wrote:
>>>
>>> On Thu, Sep 19, 2013 at 11:31 AM, ascot.moss@gmail.com
>>> <mailto:ascot.moss@gmail.com> <ascot.moss@gmail.com
>>> <mailto:ascot.moss@gmail.com>> wrote:
>>>
>>>    I use PG 9.2.4 with streaming replication.  What will be the
>>>    manual procedure to failover from Primary to Standby and Set the
>>>    old Primary as a new standby?
>>>
>>>
>>> From what I understand, you start over by setting up the old primary
>>> as a new standby from scratch.
>>
>> if you use rsync for the base backup of new master to old, it should go
>> fairly quickly as relatively few files should have changed assuming not
>> much time has elapsed.
>
> Of course, before you do anything, you should spend some time figuring
> out WHY the old master failed.  There could be issues that need to be
> resolved before putting it back online, and fixing them could affect
> how much work you have to do to get the physical files back in sync.
> --
> Mike Nolan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general