Forum OpenACS Q&A: Re: Tracking expensive DB queries

Collapse
Posted by Dave Bauer on
I wrote an additional mode for developer support that can log queries to a database table over a certain time.

I'll check it in in the next couple of days.

I learned you can stop a long running query by sending a SIGINT signal to the backend running the query.

You can learn the PID of the process by looking in pg_stat_activity. That will tell you how long the query has been running by checking the start_time of the query.

I also wrote a handy view that doesn't include the procid but calculates the running time by subtracting from now(). This can be misleading because the query string could be "IDLE" which will tell you how long the backend has been idle.

For this to work you need to turn on the stats collector and command string in postgresql.conf.

stats_start_collector = on
stats_command_string = on

Collapse
Posted by Malte Sussdorff on
What would come in handy if we could look at the current long running queries in OpenACS and then send the sigint over the db connection.

Alternatively it would be great if the xotcl request processor could track the DB execution times, so you could with one click see which queries have been long running *and* what are the DB queries that caused this behaviour