Query plan for currently executing query?

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]

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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/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.

--
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general