Thread: reading pg_stat_activity view
Hi everybody,
As I look at output from pg_stat_activity view,
I see often <idle> under query column and on the
same row waiting column is always f. I gather
that this is someone sitting at prompt of psql.
My question: What does it mean when a query column
lists a sql statment and waiting column f? Would
this mean something is in a hung state?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
On Dec 13, 2007 1:33 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote: > > Hi everybody, > > As I look at output from pg_stat_activity view, > I see often <idle> under query column and on the > same row waiting column is always f. I gather > that this is someone sitting at prompt of psql. > > My question: What does it mean when a query column > lists a sql statment and waiting column f? Would > this mean something is in a hung state? Not what that means at all. :) It means that the query is not waiting on locks from another query to be released. Here's an example you can do yourself to see it happen: T1: # create table test (a int, b text); T1: # insert into test values (1,'abc'); T1: # insert into test values (2,'def'); T1: # begin; T1: # select * from test where a=1 for update; a | b ---+----- 1 | abc (1 row) T2: # update test set b='xyz' where a=1; (T2 enters a wait state waiting on T1 to commit or rollback) In another psql session, select * from pg_stat_activity where datname='mydb'; 16385 | mydb | 13379 | 16384 | myname | update test set b='xyz' where a=1; | t Make sense?
Hi Scott,
Thank you for an enlightening example! I see
it and I believe it.
My next question:
What would be a good way to tell if some
query is hung?
Regards,
Tena
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thu 12/13/2007 11:47 AM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] reading pg_stat_activity view
On Dec 13, 2007 1:33 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> Hi everybody,
>
> As I look at output from pg_stat_activity view,
> I see often <idle> under query column and on the
> same row waiting column is always f. I gather
> that this is someone sitting at prompt of psql.
>
> My question: What does it mean when a query column
> lists a sql statment and waiting column f? Would
> this mean something is in a hung state?
Not what that means at all. :)
It means that the query is not waiting on locks from another query to
be released. Here's an example you can do yourself to see it happen:
T1: # create table test (a int, b text);
T1: # insert into test values (1,'abc');
T1: # insert into test values (2,'def');
T1: # begin;
T1: # select * from test where a=1 for update;
a | b
---+-----
1 | abc
(1 row)
T2: # update test set b='xyz' where a=1;
(T2 enters a wait state waiting on T1 to commit or rollback)
In another psql session, select * from pg_stat_activity where datname='mydb';
16385 | mydb | 13379 | 16384 | myname | update test set b='xyz'
where a=1; | t
Make sense?
On Dec 13, 2007 3:43 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote: > > My next question: > What would be a good way to tell if some > query is hung? That would really depend on what you mean by hung. Just running a really long time, or waiting for a lock that some other session is not will to commit / rollback like above? I take it you just mean long running queries. You can do a couple of things. You can set the value for statement_timeout and any statement that takes over that amount of time will generate a timeout and you then log it in the logs. You can use pg_stat_activity to see how long a query's been running. Something like select datname, usename, current_query, waiting, now() - query_start from pg_stat_activity order by query_start; can show you how long each query has been running.
Hi Scott,
Thanks for the sql statement with pg_stat_activity.
> You can set the value for statement_timeout and any statement
> that takes over that amount of time will generate a timeout and you
> then log it in the logs.
I like this idea, but according to what I read, it looks
like the query will be aborted if it goes beyond the
time specified, which is not what I want. There wouldn't
be any way to just log it and still keep the query alive,
would there?
Is a scenario like below possible/probable/likely? A user
starts a query, after a few minutes decides to abort it via
control-C, he/she gets a notion that it is aborted, but
as far as postgres is concerned the query keeps running for
days and days? Is there any way to detect such has indeed
taken place?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Scott Marlowe
Sent: Thu 12/13/2007 1:57 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] reading pg_stat_activity view
On Dec 13, 2007 3:43 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> My next question:
> What would be a good way to tell if some
> query is hung?
That would really depend on what you mean by hung. Just running a
really long time, or waiting for a lock that some other session is not
will to commit / rollback like above?
I take it you just mean long running queries. You can do a couple of
things. You can set the value for statement_timeout and any statement
that takes over that amount of time will generate a timeout and you
then log it in the logs.
You can use pg_stat_activity to see how long a query's been running.
Something like
select datname, usename, current_query, waiting, now() - query_start
from pg_stat_activity order by query_start;
can show you how long each query has been running.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Hi,
Thank you. Fascinating query!
Aside from those 9 lines that are generated from
my own query, I get a bunch of lines that indicate
ongling/live query, with all the same pid. When
I examine processes from unix side (via 'ps -ef)
I see the same process (with ppid of postgres)
running for 2+ days.
But question still remains in my mind: is this a
strayed process or is this simply a long running
(and meaningful) process?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: brahma tiwari [mailto:prakashr2n5@yahoo.com]
Sent: Fri 12/14/2007 4:21 AM
To: Tena Sakai
Subject: Re: [ADMIN] reading pg_stat_activity view
HI,
Plz run below qry u can detect the no of locks and after that chose them and kill or abort the process
(By default 9 process will be there means ideal state/no locks)
SELECT dbu.usename as locker, l.mode as locktype,
pg_stat_get_backend_pid(S.backendid) as pid,
db.datname||'..'||n.nspname||'.'||r.relname as relation, l.mode,
pg_stat_get_backend_activity(S.backendid ) as query
FROM pg_user dbu,
(SELECT pg_stat_get_backend_idset() AS backendid) AS S,
pg_database db, pg_locks l, pg_class r, pg_namespace n
WHERE db.oid = pg_stat_get_backend_dbid(S.backendid)
AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid)
AND l.pid = pg_stat_get_backend_pid(S.backendid)
AND l.relation = r.oid
AND l.database = db.oid
AND r.relnamespace = n.oid
AND l.granted
ORDER BY locktype, db.datname, n.nspname, r.relname, l.mode;
Prakash
Noida,India
----- Original Message ----
From: Tena Sakai <tsakai@gallo.ucsf.edu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Friday, 14 December, 2007 4:09:18 AM
Subject: Re: [ADMIN] reading pg_stat_activity view
Hi Scott,
Thanks for the sql statement with pg_stat_activity.
> You can set the value for statement_timeout and any statement
> that takes over that amount of time will generate a timeout and you
> then log it in the logs.
I like this idea, but according to what I read, it looks
like the query will be aborted if it goes beyond the
time specified, which is not what I want. There wouldn't
be any way to just log it and still keep the query alive,
would there?
Is a scenario like below possible/probable/likely? A user
starts a query, after a few minutes decides to abort it via
control-C, he/she gets a notion that it is aborted, but
as far as postgres is concerned the query keeps running for
days and days? Is there any way to detect such has indeed
taken place?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Scott Marlowe
Sent: Thu 12/13/2007 1:57 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] reading pg_stat_activity view
On Dec 13, 2007 3:43 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> My next question:
> What would be a good way to tell if some
> query is hung?
That would really depend on what you mean by hung. Just running a
really long time, or waiting for a lock that some other session is not
will to commit / rollback like above?
I take it you just mean long running queries. You can do a couple of
things. You can set the value for statement_timeout and any statement
that takes over that amount of time will generate a timeout and you
then log it in the logs.
You can use pg_stat_activity to see how long a query's been running.
Something like
select datname, usename, current_query, waiting, now() - query_start
from pg_stat_activity order by query_start;
can show you how long each query has been running.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Why delete messages? Unlimited storage is just a click away. Go to http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html
On Dec 13, 2007 4:39 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote: > > Hi Scott, > > Thanks for the sql statement with pg_stat_activity. > > > You can set the value for statement_timeout and any statement > > that takes over that amount of time will generate a timeout and you > > then log it in the logs. > > I like this idea, but according to what I read, it looks > like the query will be aborted if it goes beyond the > time specified, which is not what I want. There wouldn't > be any way to just log it and still keep the query alive, > would there? Yeah, it's pretty much a brick wall setting. anything over it gets stopped, period. > Is a scenario like below possible/probable/likely? A user > starts a query, after a few minutes decides to abort it via > control-C, he/she gets a notion that it is aborted, but > as far as postgres is concerned the query keeps running for > days and days? Is there any way to detect such has indeed > taken place? That's not what will happen. If the user hits ctrl-C from psql, it will send a cancel command to the backend, unlike a certain database a lot of people use **COUGHmysqlCOUGH** which will kill the client side app and leave the query running against the backend. Now, should the user initiate a query through a we interface it is quite possible for them to close the web page and for the query to keep on running for quite some time. There are scenarios where querys run for days and days, and it's a good idea to keep track of the long running queries to make sure they're not scramming your db server. Figure out what your absolutely longest running query time should be, and set the timeout to that. For me, on a reporting server at work, that's several hours. On an OLTP machine it would likely be minutes. Now, the cool thing is, you can set different max execution times per database and per user. not just for the whole db cluster. Once you've set something like 4 hours for the whole server, you can then do something like: alter database xyzdb set statement_timeout=300; alter user stanley set statement_timeout=1200; etc... If you just want to see what statements have been running a long time, you can use the pg_stat_activity view to see that. It's pretty easy to write a simple bash script that tosses a query at the db and pulls the numbers out and sends you an alert if it sees a query running too long. Shout at me if you want an example.
There wouldn't
be any way to just log it and still keep the query alive,
would there?
--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
On Dec 16, 2007 1:37 PM, Usama Dar <munir.usama@gmail.com> wrote: > > > > > > > > > There wouldn't > > be any way to just log it and still keep the query alive, > > would there? > > > I think it will be a useful feature to add to postgres, for the benefit of > DBAs, the ability to log slow queries, the queries which take more than x > amount of time, maybe in a separate slow query log. i am sure it sounds > familiar to a lot of people ;) You can already log slow queries, they just go into the pgsql logs.
"Usama Dar" <munir.usama@gmail.com> writes:
> I think it will be a useful feature to add to postgres, for the benefit of
> DBAs, the ability to log slow queries, the queries which take more than x
> amount of time, maybe in a separate slow query log. i am sure it sounds
> familiar to a lot of people ;)
See log_min_duration_statement
regards, tom lane
Hi,
> the qry running with same pid are another queries
> assined same pid, this is the reason why they are
> loking like running from two days
Given the load of this computer, that is a bit
hard for me to swallow...
I had no overwhelming reasons to kill them and I
left them alone and after 4 or 5 days they seem
to have finished running. So the problem does not
really exist anymore, except that I take a bit of
academic interest in what's going on and how to
cope with it in the similar situations in the future.
Thanks for your insight and kindness.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: brahma tiwari [mailto:prakashr2n5@yahoo.com]
Sent: Mon 12/17/2007 12:48 AM
To: Tena Sakai
Subject: Re: [ADMIN] reading pg_stat_activity view
Hi
You are right there but the qry running with same pid are another queries assined same pid,this is the reason why they are loking like running from two days, so don't get confussed at all.
Pegards!
Prakash
Noida ,India
----- Original Message ----
From: Tena Sakai <tsakai@gallo.ucsf.edu>
To: brahma tiwari <prakashr2n5@yahoo.com>
Cc: pgsql-admin@postgresql.org
Sent: Friday, 14 December, 2007 11:22:31 PM
Subject: RE: [ADMIN] reading pg_stat_activity view
Hi,
Thank you. Fascinating query!
Aside from those 9 lines that are generated from
my own query, I get a bunch of lines that indicate
ongling/live query, with all the same pid. When
I examine processes from unix side (via 'ps -ef)
I see the same process (with ppid of postgres)
running for 2+ days.
But question still remains in my mind: is this a
strayed process or is this simply a long running
(and meaningful) process?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: brahma tiwari [mailto:prakashr2n5@yahoo.com]
Sent: Fri 12/14/2007 4:21 AM
To: Tena Sakai
Subject: Re: [ADMIN] reading pg_stat_activity view
HI,
Plz run below qry u can detect the no of locks and after that chose them and kill or abort the process
(By default 9 process will be there means ideal state/no locks)
SELECT dbu.usename as locker, l.mode as locktype,
pg_stat_get_backend_pid(S.backendid) as pid,
db.datname||'..'||n.nspname||'.'||r.relname as relation, l.mode,
pg_stat_get_backend_activity(S.backendid ) as query
FROM pg_user dbu,
(SELECT pg_stat_get_backend_idset() AS backendid) AS S,
pg_database db, pg_locks l, pg_class r, pg_namespace n
WHERE db.oid = pg_stat_get_backend_dbid(S.backendid)
AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid)
AND l.pid = pg_stat_get_backend_pid(S.backendid)
AND l.relation = r.oid
AND l.database = db.oid
AND r.relnamespace = n.oid
AND l.granted
ORDER BY locktype, db.datname, n.nspname, r.relname, l.mode;
Prakash
Noida,India
----- Original Message ----
From: Tena Sakai <tsakai@gallo.ucsf.edu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Friday, 14 December, 2007 4:09:18 AM
Subject: Re: [ADMIN] reading pg_stat_activity view
Hi Scott,
Thanks for the sql statement with pg_stat_activity.
> You can set the value for statement_timeout and any statement
> that takes over that amount of time will generate a timeout and you
> then log it in the logs.
I like this idea, but according to what I read, it looks
like the query will be aborted if it goes beyond the
time specified, which is not what I want. There wouldn't
be any way to just log it and still keep the query alive,
would there?
Is a scenario like below possible/probable/likely? A user
starts a query, after a few minutes decides to abort it via
control-C, he/she gets a notion that it is aborted, but
as far as postgres is concerned the query keeps running for
days and days? Is there any way to detect such has indeed
taken place?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Scott Marlowe
Sent: Thu 12/13/2007 1:57 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] reading pg_stat_activity view
On Dec 13, 2007 3:43 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> My next question:
> What would be a good way to tell if some
> query is hung?
That would really depend on what you mean by hung. Just running a
really long time, or waiting for a lock that some other session is not
will to commit / rollback like above?
I take it you just mean long running queries. You can do a couple of
things. You can set the value for statement_timeout and any statement
that takes over that amount of time will generate a timeout and you
then log it in the logs.
You can use pg_stat_activity to see how long a query's been running.
Something like
select datname, usename, current_query, waiting, now() - query_start
from pg_stat_activity order by query_start;
can show you how long each query has been running.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Why delete messages? Unlimited storage is just a click away. Go to http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html
Save all your chat conversations. Find them online at http://in.messenger.yahoo.com/webmessengerpromo.php