pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedinvestigating slow queries through pg_stat_activity
4+ messages / 4 participants
[nested] [flat]
* investigating slow queries through pg_stat_activity
@ 2005-06-20 17:55 Dan Harris <fbsd@drivefaster.net>
2005-06-20 19:11 ` Re: investigating slow queries through pg_stat_activity Tom Lane <tgl@sss.pgh.pa.us>
2005-06-20 20:30 ` Re: investigating slow queries through pg_stat_activity George Essig <george.essig@gmail.com>
2005-07-03 22:28 ` Re: investigating slow queries through pg_stat_activity Enrico Weigelt <weigelt@metux.de>
0 siblings, 3 replies; 4+ messages in thread
From: Dan Harris @ 2005-06-20 17:55 UTC (permalink / raw)
To: pgsql-performance
I've got some queries generated by my application that will, for some
reason, run forever until I kill the pid. Yet, when I run the
queries manually to check them out, they usually work fine. To get
more information about these queries, I'm writing a utility to take
snapshots of pg_stat_activity every 5 minutes. If it finds a query
that runs for longer than 15 minutes, it will trap the query so I can
run 'explain analyze' on it and see where the weakness is.
However, the problem I have is that pg_stat_activity only returns the
first n (255?) characters of the SQL as "current_query", so it gets
chopped off at the end. I would very much like to find out how I can
get the *entire* query that is active. Is this possible?
Also, I'm sure some people will respond with "turn on query
logging".. I've explored that option and the formatting of the log
file and the fact that EVERY query is logged is not what I'm after
for this project. The "infinite-running" queries are unpredictable
and may only happen once a week. Logging 24/7 in anticipation of one
of these occurrences is not something I'd like to do.
Thanks,
Dan Harris
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: investigating slow queries through pg_stat_activity
2005-06-20 17:55 investigating slow queries through pg_stat_activity Dan Harris <fbsd@drivefaster.net>
@ 2005-06-20 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-06-20 19:11 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan Harris <fbsd@drivefaster.net> writes:
> However, the problem I have is that pg_stat_activity only returns the
> first n (255?) characters of the SQL as "current_query", so it gets
> chopped off at the end. I would very much like to find out how I can
> get the *entire* query that is active. Is this possible?
I think the limit is ~1000 characters in 8.0 and later. However, you
can't realistically have "unlimited" because of constraints of the stats
messaging mechanism.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: investigating slow queries through pg_stat_activity
2005-06-20 17:55 investigating slow queries through pg_stat_activity Dan Harris <fbsd@drivefaster.net>
@ 2005-06-20 20:30 ` George Essig <george.essig@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: George Essig @ 2005-06-20 20:30 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
On 6/20/05, Dan Harris <fbsd@drivefaster.net> wrote:
> Also, I'm sure some people will respond with "turn on query
> logging".. I've explored that option and the formatting of the log
> file and the fact that EVERY query is logged is not what I'm after
> for this project.
You don't have to log every query. You can set
log_min_duration_statement in postgresql.conf to log only the queries
that exceed a certain amount of time.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: investigating slow queries through pg_stat_activity
2005-06-20 17:55 investigating slow queries through pg_stat_activity Dan Harris <fbsd@drivefaster.net>
@ 2005-07-03 22:28 ` Enrico Weigelt <weigelt@metux.de>
2 siblings, 0 replies; 4+ messages in thread
From: Enrico Weigelt @ 2005-07-03 22:28 UTC (permalink / raw)
To: pgsql-performance
* Dan Harris <fbsd@drivefaster.net> wrote:
Hi,
> I've got some queries generated by my application that will, for some
> reason, run forever until I kill the pid. Yet, when I run the
> queries manually to check them out, they usually work fine.
If you can change your application, you could try to encapsulate the
queries into views - this makes logging and tracking down problems
much easier.
cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-07-03 22:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-20 17:55 investigating slow queries through pg_stat_activity Dan Harris <fbsd@drivefaster.net>
2005-06-20 19:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-20 20:30 ` George Essig <george.essig@gmail.com>
2005-07-03 22:28 ` Enrico Weigelt <weigelt@metux.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox