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

Collapse
Posted by Dave Bauer on
Restarting AOLserver will not stop the long-running queries.

Start with the PostgreSQL monitoring documentation
http://www.postgresql.org/docs/8.0/static/monitoring.html

In postgresql.conf you want to set

stats_start_collector true

and

stats_command_string true

You need to restart the PG backend for this to take effect.

This should give you useful information.

While a long running query is running

go to psql and type

select * from pg_stat_activity;

This should give you the queries currently running, and how long they have been running including the query text (if stats_command_string is set to true).

You can also get extensive statistical data on every table and index in the system using the views defined. Refer to the documentation for a detailed list.