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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
