On Wednesday March 23 2005 4:11, Mark Kirkwood wrote:
Is enabling the various postgresql.conf stats* options and taking regular snapshots of pg_stat_activity a possible way to get this?
I don't see how; the duration is the key measurement I'm after, and I don't believe it is available anywhere but the logs.
I was thinking about something like :
- decide on a snapshot interval (e.g. 30 seconds)
- capture pg_stat_activity every interval and save the results in a timestamped copy of this view (e.g. add a column 'snap_time')
You can then query this new table and find all those queries that are taking a long time (you know a query is finished - modulo measurement interval , if its no longer present in the snapshot).
Clearly the granularity limits the accuracy you can achieve, and there could some confusion about when a query is finished if a session repeatedly executes exactly the same query, but it would certainly bring to attention any sudden change in query activity.
cheers
Mark
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
