[Spacewalk-list] spacewalk eats up all connections to postgres

[Yuriy Demchenko]

Hi,

thanks, rebuilt package with that patch, will see in a week if it helps

Yuriy Demchenko

On 12/06/2012 06:20 PM, Patrick Hurrelmann wrote:
> On 06.12.2012 15:10, Yuriy Demchenko wrote:
>> Hi,
>>
>> I'm having an issue with my spacewalk - after a few days of normal
>> operation it hungs - 500+ connections to postgres and all of them either
>> idle or waiting.
>> I've done some digging and it seems deadlocking postgres when doing
>> daily action "Show differences between profiled config files and
>> deployed config files"
>> I've done select from pg_stat_activity and found 2 types of queries
>> (besides idle ones), all of them waiting for lock:
>>> spaceschema | spaceuser |   12482 |             | t       | 2012-12-06
>>> 00:17:12.884536+04 |
>>> :                 update rhnServerAction
>>> :                     set status = 1,
>>> :                         pickup_time = current_timestamp,
>>> :                         remaining_tries = 4 - 1
>>> :                 where action_id = 3503
>>> :                   and server_id = 1000010015
>>>
>>>   spaceschema | spaceuser |   12557 |             | t       |
>>> 2012-12-06 00:04:15.067366+04 |
>>> :                 update rhnServerAction
>>> :                     set status = 1,
>>> :                         pickup_time = current_timestamp,
>>> :                         remaining_tries = 4 - 1
>>> :                 where action_id = 3502
>>> :                   and server_id = 1000010011
>> 250+ of them. action_id 3502/3503 - "Show differences between profiled
>> config files and deployed config files"
>> server_id = 1000010011 / 1000010015 - just usual virtual machine with 11
>> profiled config files, so nothing unusual - i have another 40+ like them.
>> in pg_locks - lots of locks with matching pids, some of them "access
>> share lock", some "exclusive", however i dont really know if it means
>> anything.
>> Restart of spacewalk brings it to life, but doesnt solve the issue - on
>> next day or in 2-3 days it will hung again with similar simptoms.
>> Any tips what's wrong and how to fix it permanently?
>>
>> spacewalk-1.8 on rhel-6.3 with postgresql 8.4.13-1.el6_3
>>
> Hi,
>
> try to patch python-psycopg2 with patch from
> https://bugzilla.redhat.com/show_bug.cgi?id=843723
>
> For me it fixed all problems regarding hanging statements and excessive
> connections (in combination with upgrade to SW 1.8).
>
> I already sent it to this list, but got no feedback if it is the cure
> for others too.
>
> Regards
> Patrick
>