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

[Patrick Hurrelmann]

On 10.12.2012 10:03, James Hogarth wrote:
> 
>     I've applied the patch yesterday as well, and indeed this does seem
>     to help a lot.
>     I'm only seeing about 35 connections now (with 25 clients), no more
>     hanging queries either.
> 
> 
> So in general with that patch connection counts look better however I'm
> still seeing connections as IDLE in transaction with hanging open
> transactions even if the server didn't outright fall over this time...
> 
> So it looks  like there's still some underlying issue even if this
> mitigates some of it.
> 
> Here's my current pg_stat_activity for IDLE in transaction connections:
> 
> datid |   datname   | procpid | usesysid |  usename  |    
> current_query     | waiting |          xact_start           |         
> query_start          |         backend_start         | client_addr |
> client_port 
> -------+-------------+---------+----------+-----------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
>  16384 | spaceschema |   17267 |    16388 | spaceuser | <IDLE> in
> transaction | f       | 2012-12-07 16:23:51.178637+00 | 2012-12-07
> 16:23:51.178966+00 | 2012-12-07 16:12:34.620533+00 | 127.0.0.1   |      
> 45196
>  16384 | spaceschema |   16102 |    16388 | spaceuser | <IDLE> in
> transaction | f       | 2012-12-07 16:13:19.062683+00 | 2012-12-07
> 16:13:19.063373+00 | 2012-12-07 16:08:49.622536+00 | 127.0.0.1   |      
> 44779
>  16384 | spaceschema |   12847 |    16388 | spaceuser | <IDLE> in
> transaction | f       | 2012-12-07 16:23:51.141108+00 | 2012-12-07
> 16:23:51.14131+00  | 2012-12-07 15:57:34.622143+00 | 127.0.0.1   |      
> 43979
>  16384 | spaceschema |   17468 |    16388 | spaceuser | <IDLE> in
> transaction | f       | 2012-12-07 16:13:19.225653+00 | 2012-12-07
> 16:13:19.226371+00 | 2012-12-07 16:13:19.123346+00 | 127.0.0.1   |      
> 45215
>  16384 | spaceschema |   17471 |    16388 | spaceuser | <IDLE> in
> transaction | f       | 2012-12-07 16:23:51.260104+00 | 2012-12-07
> 16:23:51.260277+00 | 2012-12-07 16:13:19.340865+00 | 127.0.0.1   |      
> 45218
> 
> Checking the locks table shows that these all have locks on something ....

What version of Spacewalk are you running? The patched python-psycopg2
fixed my connection growth and hanging update statements. Updating to
1.8 finally solved my idle in transaction connections.

My connection count varies between 40 and 60 but it is stable.
This is SW18 on CentOS 6.3 with PostgreSQL 8.4 and managing ~40 hosts
(some over a proxy).

Regards
Patrick

-- 
Lobster LOGsuite GmbH, Münchner Straße 15a, D-82319 Starnberg

HRB 178831, Amtsgericht München
Geschäftsführer: Dr. Martin Fischer, Rolf Henrich