"Idle in Transaction" and hung connections
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_activity 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 to send, or some such variant. The client machines don't have a corresponding connection 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
"Gregory S. Williamson" <gsw@globexplorer.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_activity 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
Tom --
Thanks for the suggestion, and the rapid response on something which may not be truely a postgres issue (perhaps more a JDBC thing)!
I'll make sure to try this next time we see this oddness in action. May be hours, may be days...
Greg
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thu 4/29/2004 3:03 PM
To: Gregory S. Williamson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] "Idle in Transaction" and hung connections
"Gregory S. Williamson" <gsw@globexplorer.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_activity 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
Import Notes
Resolved by subject fallback
On Thu, 29 Apr 2004, Gregory S. Williamson wrote:
Tom --
Thanks for the suggestion, and the rapid response on something which may
not be truely a postgres issue (perhaps more a JDBC thing)!
This behavior is fixed in the 7.5 cvs version of the JDBC driver if you'd
like to try it out.
Kris Jurka
Sounds like an excellent suggestion ... we'll get a copy of this release.
Do you know of any incompatabilities with postgres 7.4 ?
We can upgrade a server to 7.5 and the JDBC, put running some servers on 7.4/7.4 JDBC might be, uhm, difficult for me to sell operations (and we have only seen this problem in runtime). We might also be able to look at certain portions of the CVS code and see what changed and make backward patches ? (forwarding a suggestion from our engineering people)
Thanks,
Greg W.
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thu 4/29/2004 7:27 PM
To: Gregory S. Williamson
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] "Idle in Transaction" and hung connections
On Thu, 29 Apr 2004, Gregory S. Williamson wrote:
Tom --
Thanks for the suggestion, and the rapid response on something which may
not be truely a postgres issue (perhaps more a JDBC thing)!
This behavior is fixed in the 7.5 cvs version of the JDBC driver if you'd
like to try it out.
Kris Jurka
Import Notes
Resolved by subject fallback
On Fri, 30 Apr 2004, Gregory S. Williamson wrote:
Sounds like an excellent suggestion ... we'll get a copy of this release.
Do you know of any incompatabilities with postgres 7.4 ?
We can upgrade a server to 7.5 and the JDBC, put running some servers
on 7.4/7.4 JDBC might be, uhm, difficult for me to sell operations (and
we have only seen this problem in runtime). We might also be able to
look at certain portions of the CVS code and see what changed and make
backward patches ? (forwarding a suggestion from our engineering people)
You don't need to upgrade a server to use the latest JDBC driver, it has
support going back to at least 7.1. I've produced a patch and set of
binary drivers for the 7.4 series available here:
http://www.ejurka.com/pgsql/jars/transaction_state/
This code has not gotten a whole lot of testing so I'd be careful just
dropping it into a production situation.
Kris Jurka
I assume this issue has been solved repeatedly, but I cannot find any
information on it...
I have a website that runs reports on data from a postgres database. An
average report takes about 10 minutes. I'd like to load balance it so I
can have multiple people run reports without causing the login request
to take forever. Also, it is rather important that data updated in the
database be updated on all mirrors of it immediately - not with an
hourly or daily update.
What I would most desire is a program that pretends to be a postgres
server. I can log into it with psql, a jdbc driver, or php's
pg_connect. When I do a select (no update) command, it will send that
off to the least loaded mirror. When I do an update/insert/delete, it
hits all the mirrors. Also, it can designate one database (most likely
local to this pretend server) as the master so I can easily clone it to
make more mirrors.
Does anything remotely similar to that exist?
-Chris
On Fri, Apr 30, 2004 at 01:17:16PM -0400, CS Wagner wrote:
I assume this issue has been solved repeatedly, but I cannot find any
information on it...I have a website that runs reports on data from a postgres database. An
average report takes about 10 minutes. I'd like to load balance it so I
can have multiple people run reports without causing the login request
to take forever. Also, it is rather important that data updated in the
database be updated on all mirrors of it immediately - not with an
hourly or daily update.What I would most desire is a program that pretends to be a postgres
server. I can log into it with psql, a jdbc driver, or php's
pg_connect. When I do a select (no update) command, it will send that
off to the least loaded mirror. When I do an update/insert/delete, it
hits all the mirrors. Also, it can designate one database (most likely
local to this pretend server) as the master so I can easily clone it to
make more mirrors.Does anything remotely similar to that exist?
Sounds like dbbalancer, but I've no idea whether that's ready for
production use. I supect not.
http://sqlrelay.sourceforge.net/sqlrelay/ may be worth a look for the
"select" side of things. It's high performance and lightweight, and
supports most database access APIs.
To keep the databases in sync you'll need some form of replication to
keep the data consistent on all the mirrors. There are several
master-slave replication solutions for postgresql. Slony is one I've
seen mentioned a lot recently. If you're careful about the update
queries you make you could also replicate at the application level,
which has some different tradeoffs compared to master-slave database
replication.
Cheers,
Steve
On Fri, 30 Apr 2004, CS Wagner wrote:
I assume this issue has been solved repeatedly, but I cannot find any
information on it...I have a website that runs reports on data from a postgres database. An
average report takes about 10 minutes. I'd like to load balance it so I
can have multiple people run reports without causing the login request
to take forever. Also, it is rather important that data updated in the
database be updated on all mirrors of it immediately - not with an
hourly or daily update.What I would most desire is a program that pretends to be a postgres
server. I can log into it with psql, a jdbc driver, or php's
pg_connect. When I do a select (no update) command, it will send that
off to the least loaded mirror. When I do an update/insert/delete, it
hits all the mirrors. Also, it can designate one database (most likely
local to this pretend server) as the master so I can easily clone it to
make more mirrors.Does anything remotely similar to that exist?
Steve Atkins wrote:
On Fri, Apr 30, 2004 at 01:17:16PM -0400, CS Wagner wrote:
What I would most desire is a program that pretends to be a postgres
server. I can log into it with psql, a jdbc driver, or php's
pg_connect. When I do a select (no update) command, it will send that
off to the least loaded mirror. When I do an update/insert/delete, it
hits all the mirrors. Also, it can designate one database (most likely
local to this pretend server) as the master so I can easily clone it to
make more mirrors.Does anything remotely similar to that exist?
Sounds like dbbalancer, but I've no idea whether that's ready for
production use. I supect not.
Possibly pgpool too. See the mailing-list archives for pg-announce (last
month) for the details.
--
Richard Huxton
Archonet Ltd
Dardo D Kleiner - CONTRACTOR said:
If you're not using java, this may be an interesting alternative solution
which was recently announced (on this list). It seems to meet quite a few
of your requirements.
John Sidney-Woollett
<quote>
pgpool 1.0, yet another open source replication software for
PostgreSQL is now available at:
ftp://ftp.sra.co.jp/pub/cmd/postgres/pgpool/pgpool-1.0.tar.gz
pgpool is a single master/query based/synchronous replication
server. It acts as a proxy server between PostgreSQL client and
PostgreSQL server. No application change is needed to use pgpool.
pgpool's features include:
o connection pooling. This will reduce the connection establishing
overhead.
o pre-forking child processes. Like Apache, pgpool pre-forks child
processes to provide faster service startup.
o degeneration. In the replication mode, if one of PostgreSQL goes
down, it detaches the broken server and continues operation with the
surviving server.
o fail over. In the connection pool server mode, if master PostgreSQL
goes down, it detaches the broken server and continues operation
with the stand-by server.
pgpool can work with PostgreSQL 7.0 to 7.4. It's small and easy to
install. All you need is gcc. You even do not need PostgreSQL source
tree.
Enjoy,
--
Tatsuo Ishii
</quote>