Re: idle in transaction query makes server unresponsive

On 09/25/12 12:23 PM, Scot Kreienkamp wrote:
>
> I have a problem that I've been struggling with for quite some time.
> Every once in a while I will get a connection that goes to idle in
> transaction on an in-house programmed application that connects with
> JDBC.  That happens fairly regularly and the programmers are trying to
> clean that up, but sometimes the idle in transaction connection makes
> the PG server entirely unresponsive.  I'm not getting connection
> refused, nothing.  All connections existing or new, JDBC or psql, just
> hang.  I've already got full query logging on to try to catch the
> problem query or connection so I can give the developers somewhere to
> look to resolve their issue with the application, but since queries
> are logged with runtimes I'm assuming they are only logged after they
> are complete.  And since it's idle in transaction it never completes
> so it never gets logged.  Our application is connecting as an
> unprivileged user named rmstomcat, and the database is limited to 400
> connections out of 512.  I'm not running out of connections as I've
> got reserved connections set, and even connecting as user postgres
> with psql the connection just hangs.  The server doesn't appear to be
> running out of memory when this happens and nothing is printed in the
> log.  The only thing that resolves it is doing a kill on the PID of
> any idle in transaction connections existing at the time causing them
> to roll back. Then everything else picks up right where it left off
> and works again.
>
> Can anyone give me any hints about why PG becomes unresponsive?  Or
> how to fix it so it doesn't?
>


that is a LOT of connections.   you likely should be limiting that with
a connection pooler, and configuring your application to ...

1) get connection from pool
2) execute transaction
3) release connection to pool

then configure the pool to stall the requester when some sane number of
connections has been reached, like no more than 2-3X the number of CPU
cores or hardware threads you have.   you'll likely get better overall
throughput.

if you have jobs that execute long running queries for reporting etc,
have those use a seperate smaller pool.

re: your logging....  <idle in transaction> means that connection has no
query running but started a transaction.  there's no pending query on
that connection.      these are normally only a concern when they go on
for a long time, say 10 minutes or more. however, if that transaction
has gotten locks on resources, and is then sitting on its thumbs doing
nothing, OTHER connections likely will block.   join pg_stat_activity
with pg_locks to find out what all is going on..





--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast