Skip site navigation (1) Skip section navigation (2)

How to identify long-running queries, not just long-running backends?

From: Jeff Boes <jboes(at)nexcerpt(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to identify long-running queries, not just long-running backends?
Date: 2005-03-31 15:33:07
Message-ID: d2h57f$2451$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
I need a way to identify Pg backends which have been running a given 
query for a long time. What I have so far is to use pg_stat_activity and 
the process table (in my case, via Perl's Proc::ProcessTable) to 
identify processes with a lot of CPU usage and an active query. The 
problem is false positives: since we use a daemon architecture which may 
keep a Pg backend open for a while and run many, many queries, I can't 
tell the difference between a backend which has run one query with 
elapsed time of several minutes, and a backend which has run 1000 
queries each with a sub-second elapsed time.

What I'm really hoping for is a way to get the "start time" for a query 
in pg_stat_activity.

I'm using 7.4.1, by the way.

-- 
Jeff Boes                                         Vox 269-226-9550 x24
Director of Software Development                  Fax 269-349-9076

Exfacto!         Exceptional Online Content     http://www.exfacto.com
Nexcerpt         ...Extend Your Expertise...    http://www.nexcerpt.com

Responses

pgsql-general by date

Next:From: Jeff BoesDate: 2005-03-31 15:35:21
Subject: Re: How to identify long-running queries, not just long-running backends?
Previous:From: Tom LaneDate: 2005-03-31 15:17:13
Subject: Re: truncate/create slowness

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group