Thread: "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.8USE_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 sidethat 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" foreach 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 arein 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 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 tohave brought down a server, or at least rendered it non-functional. Thanks for any advice ! Greg Williamson DBA GlobeXplorer LLC
Tom --
Thanks for the suggestion, and the rapid response on something which may not be truely a postgres issue (perhaps more a
JDBCthing)!
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
"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
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 tosell operations (and we have only seen this problem in runtime). We might also be able to look at certain portions of theCVS 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
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, 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? http://sqlrelay.sourceforge.net/
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
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: > http://c-jdbc.objectweb.org/ 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>