"Idle in Transaction" and hung connections

[Gregory S. Williamson]

Dear peoples,

Periodically we are getting runaway postgres processes on our Linux (2.4.21-0.13 on Dell servers), using 7.4 and GIS (0.8 USE_GEOS=1 USE_PROJ=1 USE_STATS=1).

All of the queries come in from remote servers using JDBC/proxool; once every 4 hours we have a process on the client side that cleans out old connections.

All the processes are doing is single queries -- no inserts or updates.

Very occasionally we will see a thread go wild, taking up a huge amount of processor time (the load will climb by "1" for each process -- usual load is around .2, when these hit the load rises to 1.x all the way up to a load of about 40 once). The pg_stat_activit y shows these conections as being old-- much older than any live thread. All such connections are in a state of"IDLE IN TRANSACTION" which seems odd as these are all queries and presumably each query is a complete transaction. My tenative theory is that something is killing the client while the server side still thinks it has data tosend, or some such variant. The client machines don't have a correspondingco nnection to the one on the postgres server.

Killing the runaways with a -15 seems to bring the load back down and all is well, until it happens again.

Does anyone have any ideas what might be triggering this ? It is mostly an annoyance but on a couple of occasions seems to have brought down a server,or at least rendered it non-functional.

Thanks for any advice !

Greg Williamson
DBA
GlobeXplorer LLC

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Gregory S. Williamson" <gs*@globexplor er.com> writes:

Very occasionally we will see a thread go wild, taking up a huge
amount of processor time (the load will climb by "1" for each process
-- usual load is around .2, when these hit the load rises to 1.x all
the way up to a load of about 40 once). The pg_stat_activit y shows
these conections as being old -- much older than any live thread. All
such connections are in a state of "IDLE IN TRANSACTION" which seems
odd
This is not unexpected due to the way JDBC (mis)uses BEGIN/COMMIT.
However it is strange that such a connection would start using
a significant amount of CPU time. It should be waiting for a new
client query.
Does anyone have any ideas what might be triggering this ?

No. Try attaching to a looping backend with gdb so you can get a stack
trace. I would suggest something along the lines of

gdb /path/to/postgres PID
bt
cont
... wait a few seconds, press control-C, and again do:
bt
cont
... lather, rinse, repeat a few times, then control-C and:
quit

Comparison of four or five stack traces obtained this way should make it
fairly clear where the loop is, and then we can determine whether we
need more info to solve it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Gregory S. Williamson" <gs*@globexplor er.com> writes:

Very occasionally we will see a thread go wild, taking up a huge
amount of processor time (the load will climb by "1" for each process
-- usual load is around .2, when these hit the load rises to 1.x all
the way up to a load of about 40 once). The pg_stat_activit y shows
these conections as being old -- much older than any live thread. All
such connections are in a state of "IDLE IN TRANSACTION" which seems
odd
This is not unexpected due to the way JDBC (mis)uses BEGIN/COMMIT.
However it is strange that such a connection would start using
a significant amount of CPU time. It should be waiting for a new
client query.
Does anyone have any ideas what might be triggering this ?

No. Try attaching to a looping backend with gdb so you can get a stack
trace. I would suggest something along the lines of

gdb /path/to/postgres PID
bt
cont
... wait a few seconds, press control-C, and again do:
bt
cont
... lather, rinse, repeat a few times, then control-C and:
quit

Comparison of four or five stack traces obtained this way should make it
fairly clear where the loop is, and then we can determine whether we
need more info to solve it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html