Thread: connections and cpu consumption
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".
Thanks for any help !
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
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".
--
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, hopefullyallowing 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, hopefullyallowing 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, hopefullyallowing 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