Thread: 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
Attachment
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
How to failover from Primary to Standby and Set the old Primary as a new Standby
From
"ascot.moss@gmail.com"
Date:
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
Re: How to failover from Primary to Standby and Set the old Primary as a new Standby
From
Vick Khera
Date:
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.
Re: How to failover from Primary to Standby and Set the old Primary as a new Standby
From
John R Pierce
Date:
On 9/19/2013 1:29 PM, Vick Khera 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.
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
Re: How to failover from Primary to Standby and Set the old Primary as a new Standby
From
Michael Nolan
Date:
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. >
Re: How to failover from Primary to Standby and Set the old Primary as a new Standby
From
Michael Nolan
Date:
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
Re: How to failover from Primary to Standby and Set the old Primary as a new Standby
From
"ascot.moss@gmail.com"
Date:
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