Friday, February 08, 2008

How to check running queries and stats in postgres ?

You want to know what query is slowing the database or want to check what's going on with a query you
just run, well in mysql just need to type 'show processlist;', but in postgres you must enable two variables
to check this and other stats.

Edit your postgres.conf which should be on /var/lib/pgsql/data/postgresql.conf for *nix installations and add or change this:

stats_start_collector = true

This must be set to true for the statistics collector to be launched at all.

stats_command_string = true

This enables monitoring of the current command being executed by any server process. The statistics collector subprocess need not be running to enable this feature.

After restarting postgres the following query will show currently running queries

/etc/init.d/postgresql restart

psql -U postgres template1 -c "select * from pg_stat_activity"

only as user "postgres"

No comments: