Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why are IDLE connections using cpu according to TOP.

Why are IDLE connections using cpu according to TOP.

From
bubba postgres
Date:

I have an overloaded DB and I see several IDLE connections that are using significant CPU.. (Not Idle in transaction)
Why would an idle process be eating so much cpu? Or is it not actually idle?


Here is an example from pg_top:

last pid: 11821;  load avg:  6.11,  6.32,  7.64;       up 1+21:05:31
50 processes: 3 running, 42 sleeping, 5 uninterruptable
CPU states: 21.7% user,  0.0% nice,  7.8% system, 46.9% idle, 23.6% iowait
Memory: 29G used, 149M free, 13M buffers, 27G cached
Swap:

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
 4779 postgres  20    0 4383M  573M disk    3:16  4.79% 39.42% postgres: gpup gpup 10.202.99.5(46391) UPDATE                                                                              
11591 postgres  20    0 4383M  108M sleep   0:12  2.08% 19.61% postgres: gpup gpup 10.202.99.6(52459) idle                                                                                
 4191 postgres  20    0 4384M  709M sleep   4:33  2.50% 19.41% postgres: gpup gpup 10.202.99.6(42288) idle                                                                                
10942 postgres  20    0 4383M  242M sleep   0:42  5.08% 16.86% postgres: gpup gpup 10.202.99.5(58373) idle                                                                                
10930 postgres  20    0 4390M  281M sleep   0:43  1.62% 15.30% postgres: gpup gpup 10.202.99.6(52273) idle                                                                                
11571 postgres  20    0 4390M  210M run     0:25  4.32% 14.51% postgres: gpup gpup 10.202.99.6(52455) SELECT                                                                              
11533 postgres  20    0 4383M  109M run     0:14  2.31% 12.75% postgres: gpup gpup 10.202.99.6(52453) SELECT                                                                              
 7494 postgres  20    0 4384M 1611M disk    2:31  2.44% 12.35% postgres: gpup gpup 10.202.99.6(53620) SELECT        


Re: Why are IDLE connections using cpu according to TOP.

From
Scott Marlowe
Date:
On Fri, Jun 3, 2011 at 3:15 PM, bubba postgres <bubba.postgres@gmail.com> wrote:
>
> I have an overloaded DB and I see several IDLE connections that are using
> significant CPU.. (Not Idle in transaction)
> Why would an idle process be eating so much cpu? Or is it not actually idle?

Because there's often a difference in time between when the process is
sampled for how hard it's working and the output that says what it's
doing.  I.e. it was working hard, then when we looked at the part that
says what it's doing, it's not idle.

Re: Why are IDLE connections using cpu according to TOP.

From
Tom Lane
Date:
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Fri, Jun 3, 2011 at 3:15 PM, bubba postgres <bubba.postgres@gmail.com> wrote:
>> I have an overloaded DB and I see several IDLE connections that are using
>> significant CPU.. (Not Idle in transaction)
>> Why would an idle process be eating so much cpu? Or is it not actually idle?

> Because there's often a difference in time between when the process is
> sampled for how hard it's working and the output that says what it's
> doing.  I.e. it was working hard, then when we looked at the part that
> says what it's doing, it's not idle.

Aside from the fact that top's measurements are time averages not
instantaneous, I believe it's also the case that when an idle backend
receives a new SQL command, it does a fair amount of parsing of the
command before it changes its reported "ps" state.  (This is because
it has to parse the command before it knows what to put in the state.)
So these could be processes that just received complicated commands.

            regards, tom lane