Thread: Re: Idle in transaction help

Thanks scott, but I wrote a cgi to combine all of the process info and allow me to kill errant queries. So I know how to track down the pid. Thanks for trying to help though. :-)

----- Original Message -----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Sent: Fri Jul 10 18:34:14 2009
Subject: Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp<SKreien@la-z-boy.com> wrote:
> Hi everyone,
>
> I need some help with tracking down idle in transaction problems.  We have a
> custom application that is leaving queries in idle in transaction status for
> unknown reasons.  The developers are working on ways to track it down, but
> right now the options on their end are limited and it will be at least 1-2
> months until they have something working.  I am trying to track down the
> problem from the PG end in the meantime.  Is there any way to tell what
> query is hanging in idle in transaction status?  Or what the current or
> previous query was/is, since idle in transaction doesn’t tell me anything?
> I’m kind of at a loss on what if anything I can do from the database end to
> help (read push) the programmers to find and fix this problem.
>
>
>
> My first priority is helping them find and fix the problem if I can.  My
> second priority is finding an automated way to deal with the idle in
> transactions as they are locking tables and rows, causing other transactions
> to hang also.  None of the timeouts appear to affect idle in transactions.

Assuming that tracking down the process that's connected might help,
you can use pg_stat_activity to find the port that the client is
connecting from, then on the client machine, use lsof to hunt down the
process that is connecting via that port.

For instance, I connect from my laptop with two connections.  One I do
a begin; in and in the other I look it up like so:

select * from pg_stat_activity where current_query ilike
'%idle%trans%' and current_query not ilike 'select%';
 datid | datname  | procpid | usesysid | usename  |     current_query
   | waiting |          xact_start           |          query_start
      |         backend_start         | client_addr  | client_port
-------+----------+---------+----------+----------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------
 11511 | postgres |   24893 |    16413 | smarlowe | <IDLE> in
transaction | f       | 2009-07-10 16:20:15.056385-06 | 2009-07-10
16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 |
   48727

The client port is 48727.  Now, on my laptop I can do:

sudo lsof |grep 48727 and I have this line in there:

psql      27964   smarlowe    3u     IPv4            1114765
      TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)

Note that 27964 is the pid of the psql command that's connected to the
server.  Hope that helps a little.

On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp<SKreien@la-z-boy.com> wrote:
> Thanks scott, but I wrote a cgi to combine all of the process info and allow
> me to kill errant queries. So I know how to track down the pid. Thanks for
> trying to help though. :-)

So, what are you looking for, a stack trace dump from java to look
through maybe? (the one that kill -1 or whatever generates? It's been
a few years.)  That'll usually give you the context to find out which
thread is where.  P.s. no need to pat me on the head like the doggie.
:)

We have a java web page that will give us the stack trace of all the
running JDBC connections inside our system.  The problem is that we
currently have no way of relating those stack traces back to a PID so
the programmers can get the stack trace of the hung database connection.
We use the JDBC connection pooling so there's no way to be sure what
stack trace goes to what PID.  I gave the developers the postgres call
to get that backend PID through the JDBC connection a few days ago, but
they don't have the resources to get the additional call built into
their programs for up to 1-2 months.

I'm working on the business side to get priorities changed, but it
hasn't happened yet.  Mostly because I've got Xymon watching for those
conditions so I can correct them before we get calls into the helpdesk.
Sorry, I'm rambling.

Anyway, I'm trying to attack it from the database side out since I am
not a programmer and can't help with that part.  I can do simple CGIs
with bash, but I don't know Java or C or even Perl yet for that matter.
Since you guys are the experts, I'm looking for any way to attack this
problem from the database side.  The tips I've gotten about the JDBC
driver and commits are helpful in that it gives our programmers things
to watch out for that we didn't realize, and I'm open to any suggestions
from the list about how I can help attack this.

Since I'm ultimately responsible for database performance and I don't
like being reduced to sitting on the sidelines I'm trying to see what if
anything else my skills can contribute.

As for patting you on the head, I was being sincere.  And trying not to
come off sounding like a cocky SOB.  :-)

Thanks,



Scot Kreienkamp



-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, July 10, 2009 7:02 PM
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp<SKreien@la-z-boy.com>
wrote:
> Thanks scott, but I wrote a cgi to combine all of the process info and
allow
> me to kill errant queries. So I know how to track down the pid. Thanks
for
> trying to help though. :-)

So, what are you looking for, a stack trace dump from java to look
through maybe? (the one that kill -1 or whatever generates? It's been
a few years.)  That'll usually give you the context to find out which
thread is where.  P.s. no need to pat me on the head like the doggie.
:)

On Fri, Jul 10, 2009 at 08:40:28PM -0400, Scot Kreienkamp wrote:
> Anyway, I'm trying to attack it from the database side out since I am
> not a programmer and can't help with that part.  I can do simple CGIs
> with bash, but I don't know Java or C or even Perl yet for that matter.
> Since you guys are the experts, I'm looking for any way to attack this
> problem from the database side.  The tips I've gotten about the JDBC
> driver and commits are helpful in that it gives our programmers things
> to watch out for that we didn't realize, and I'm open to any suggestions
> from the list about how I can help attack this.

One easy approach, get the database to log all queries, complete with
PID, then when you find an indle-in-transaction, go to the logs to find
the last query executed...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.