Re: idle in transaction query makes server unresponsive

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of John R Pierce
> Sent: Tuesday, September 25, 2012 3:53 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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..
>
[Scot Kreienkamp]

Hi John,

The application is using a pooler and generally runs around 100 connections, but I've seen it as high as 200 during the day for normal use. It's on a large server; 64 cores total and about 500 gigs of memory. That's one of the reasons I left it at 512 connections. The idle in transaction connections are getting locks and then going idle in transaction causing the queries to be waiting in that database. That I can understand. My problem is that I can't run a query to see what exactly it's doing because the entire Postgres server is unresponsive. I can't even use psql to connect to the postgres user database as user postgres so I can query pg_stat_activity, that hangs also until I kill the idle in transaction query PID. That's what my dilemma is. The server hardware itself is not being stressed when that's happening though, so it doesn't appear to be a resource problem, but I can't check because I can't see what PG is doing.

The problem is how do I investigate this when PG is entirely unresponsive? Why is it becoming unresponsive, and how do I prevent the PG server from becoming unresponsive?

Thanks!

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, please note that you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.