Re: High CPU load on Postgres Server during Peak times!!!!

Andy Colson wrote:
> Shiva Raman wrote:
>> Hi
>>
>> Today the load observed very high load . I am pasting the top.
>>
>> _*TOP *_
>> top - 12:45:23 up 79 days, 14:42, 1 user, load average: 45.84,
>> 33.13, 25.84
>> Tasks: 394 total, 48 running, 346 sleeping, 0 stopped, 0 zombie
>> Cpu(s): 49.2%us, 0.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi,
>> 0.1%si, 50.0%st
>> Mem: 16133676k total, 14870736k used, 1262940k free, 475484k buffers
>> Swap: 14466492k total, 124k used, 14466368k free, 11423616k cached
>>
>> /usr/bin/wc -l ` -gt 20 && /usr/bin/pkill -o -f 'idle in transaction'
>>
>> and this is the link where the script was provided.
>>
>> http://wiki.dspace.org/index.php/Idle_In_Transaction_Problem
>>
>> I tried it run it as test in the server, but the script is not
>> executing. Even i see many of the "Idle in transaction " PIDs are
>> showing R (RUnning status) , but most of them are showing S(Sleep )
>> status. Please suggest anyway i can resolve this idle transaction issue.
>
> fixing up the code to commit selects will make the "idle in trans." go
> away. I'm with Praveen, fix the code, avoid the scripts.
>
> Is there anything else running on this box? You said previously "The
> PowerPC cpu is having some virtual layer that is shown in the Steal
> value.". I'm not sure what that means. Are you in a virtual machine?
> Or running other vm's? Based on the top you posted (this one and the
> very first one) you are loosing half your cpu to the vm. (unless I'm
> totally reading this wrong... I don't have experience with vm's so
> please someone jump in here and correct me if I'm wrong)
>
"idle in transaction" processes will DESTROY throughput over time.

Don't kill them - find out how they're happening. They should NOT happen.

If you take an exception in an application it is essential that the
application NOT leave pending transactions open. If your middleware
between application and Postgres doesn't take care of this cleanup on
exit on its own (or if it would if you left through an "approved" path
but you're doing something like SEGVing out of a compiled app or calling
exit() without closing open connections, etc) you need to figure out
where you're getting these exceptions from and fix them.

Hacks like killing "idle in transaction" processes will eventually bite
you by killing a process that is TEMPORARILY idle while waiting for some
resource but the check "catches it" at exactly the wrong time, whacking
a perfectly good change. At best this returns an error to the user; at
worst, especially in a web-based application, it can result in a
silently-lost transaction.

-- Karl