Re: <IDLE> connections and cpu consumption

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/