Thread: viewing currently executing query
Hello everyone, I use an app (developed by someone else) that uses Postgres 8.3 as backend. The app in question starts up for a rather long time, about 10 minutes, during which Postgres is heavily loaded (several processes each eating 50%-100% of CPU on the multicore server). However, when I do "select * from pg_stat_activity" during the startup, all the connections seem to be idle (see below). I have found here info that current query stats have to be explicitly turned on in postgresql.conf - is that true? http://systemsadmin.info/databases/articles/viewing-current-postgresql-queries Is there any other way of viewing what exactly is Postgresql doing, in terms of executed query? I would obviously like to reduce the startup time. postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port -------+-----------+---------+----------+----------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------- 16385 | dlibra_db | 15057 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:43.594439+01 | 2008-11-05 10:42:23.880847+01 | 193.201.137.130 | 58164 16385 | dlibra_db | 15066 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:44:33.45632+01 | 2008-11-05 10:43:51.38554+01 | 193.201.137.130 | 58169 16385 | dlibra_db | 15063 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:29.711821+01 | 2008-11-05 10:43:29.707715+01 | 193.201.137.131 | 49732 16385 | dlibra_db | 15067 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:54.712203+01 | 2008-11-05 10:43:54.708453+01 | 193.201.137.131 | 49734 16385 | dlibra_db | 15062 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:43.460778+01 | 2008-11-05 10:43:26.384412+01 | 193.201.137.130 | 58167 16385 | dlibra_db | 15068 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:44:33.517198+01 | 2008-11-05 10:43:54.710977+01 | 193.201.137.131 | 49735 16385 | dlibra_db | 15064 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:53.531007+01 | 2008-11-05 10:43:29.710728+01 | 193.201.137.131 | 49733 16385 | dlibra_db | 15051 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:12.450003+01 | 2008-11-05 10:41:46.381951+01 | 193.201.137.130 | 58162 16385 | dlibra_db | 15056 | 16384 | dlibra | <IDLE> | f | | 2008-11-05 10:43:23.523071+01 | 2008-11-05 10:42:14.705713+01 | 193.201.137.131 | 49731 11511 | postgres | 15098 | 10 | postgres | select * from pg_stat_activity; | f | 2008-11-05 10:44:41.191526+01 | 2008-11-05 10:44:41.191526+01 | 2008-11-05 10:44:21.141447+01 | | -1 16385 | dlibra_db | 15024 | 16384 | dlibra | <IDLE> in transaction | f | 2008-11-05 10:39:20.351713+01 | 2008-11-05 10:39:20.356763+01 | 2008-11-05 10:38:26.374313+01 | 193.201.137.130 | 58145 (11 rows)
Marcin Krol <mrkafk@gmail.com> writes: > I use an app (developed by someone else) that uses Postgres 8.3 as > backend. The app in question starts up for a rather long time, about 10 > minutes, during which Postgres is heavily loaded (several processes each > eating 50%-100% of CPU on the multicore server). > However, when I do "select * from pg_stat_activity" during the startup, > all the connections seem to be idle (see below). You seem to have missed the load --- most of those connections have been idle for a minute or two. > I have found here info that current query stats have to be explicitly > turned on in postgresql.conf - is that true? As of 8.3 they're on by default, see track_activities. regards, tom lane