<IDLE> connections and cpu consumption

On 11/09/11 10:35 AM, Gauthier, Dave wrote:

I'm using "selcet procpid,current_query from pg_stat_activity" to
monitor activity during times when "top" is showing many PG procs with
very high cpu usage numbers (all cores at or above 90%). Some of
these are procs that map to PG connections with current_query = <IDLE>.

What scenarios could explain a process identified as IDLE consuming
lots of CPU?

More clues... In parallel with these was a user that was making a
series of insert/delete/update commands that fire off triggers that
generate more DML recursively. Some of the idles are "<IDLE> in
transaction".

perhaps you just happened to sample pg_stat_activity in between queries?
<IDLE> should be just that. <IDLE> in transaction is equally idle, but
there's an open transaction. if these processes STAY idle in
transaction for too long they can cause vacuum to get behind, thats
typically hours-to-days before this is a problem in most scenarios.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Wednesday, November 9, 2011, Gauthier, Dave wrote:

Hi:****

** **

PG v4.8.3 on Linux****

** **

I'm using "selcet procpid,current_query from pg_stat_activity" to monitor
activity during times when "top" is showing many PG procs with very high
cpu usage numbers (all cores at or above 90%). Some of these are procs
that map to PG connections with current_query = <IDLE>.****

** **

What scenarios could explain a process identified as IDLE consuming lots
of CPU?****

** **

More clues... In parallel with these was a user that was making a series
of insert/delete/update commands that fire off triggers that generate more
DML recursively. Some of the idles are "<IDLE> in transaction".

My guess would be something running lots and lots of really short queries.
top will give you an averate CPU usage over an interval, but
pg_stat_activity shows a snapshot. So probably the sessions were running
simple things, but just at the moment pg_stat_activity "hits", it wasn't
doing anything.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Ahhhh... so if the script that has the connection open would only terminate the transaction, then vacuum wouldn't get behind?

I actually made a change in that script to rollback when the script doesn't need the changes in the transaction, hopefully allowing vacuum to do its thing.

Thanks!

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Wednesday, November 09, 2011 2:21 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] <IDLE> connections and cpu consumption

On 11/09/11 10:35 AM, Gauthier, Dave wrote:

I'm using "selcet procpid,current_query from pg_stat_activity" to
monitor activity during times when "top" is showing many PG procs with
very high cpu usage numbers (all cores at or above 90%). Some of
these are procs that map to PG connections with current_query = <IDLE>.

What scenarios could explain a process identified as IDLE consuming
lots of CPU?

More clues... In parallel with these was a user that was making a
series of insert/delete/update commands that fire off triggers that
generate more DML recursively. Some of the idles are "<IDLE> in
transaction".

perhaps you just happened to sample pg_stat_activity in between queries?
<IDLE> should be just that. <IDLE> in transaction is equally idle, but
there's an open transaction. if these processes STAY idle in
transaction for too long they can cause vacuum to get behind, thats
typically hours-to-days before this is a problem in most scenarios.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/09/11 11:35 AM, Gauthier, Dave wrote:

Ahhhh... so if the script that has the connection open would only terminate the transaction, then vacuum wouldn't get behind?

I actually made a change in that script to rollback when the script doesn't need the changes in the transaction, hopefully allowing vacuum to do its thing.

(from memory, I might have details wrong here)...

select now()-xact_start as "Transaction Age", usename, datname,
procpid, application_name
from pg_stat_activity where current_query='<IDLE> in
transaction" order by 1 desc;

that will list the age of those idle-in-transactions, oldest on top.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

ALas, there is no "application_name" column in that table. But I do like the "now()-xact_start" !

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Wednesday, November 09, 2011 2:58 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] <IDLE> connections and cpu consumption

On 11/09/11 11:35 AM, Gauthier, Dave wrote:

Ahhhh... so if the script that has the connection open would only terminate the transaction, then vacuum wouldn't get behind?

I actually made a change in that script to rollback when the script doesn't need the changes in the transaction, hopefully allowing vacuum to do its thing.

(from memory, I might have details wrong here)...

select now()-xact_start as "Transaction Age", usename, datname,
procpid, application_name
from pg_stat_activity where current_query='<IDLE> in
transaction" order by 1 desc;

that will list the age of those idle-in-transactions, oldest on top.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general