Обсуждение: Hung JDBC connections
Hi,
Yesterday we experienced a problem whereby hundreds of JDBC client
connections remained in a hung state, despite the fact that the
PostgreSQL server was actually rebooted. This is disconcerting because
our client applications have a db failover facility, but that feature
won't work unless existing JDBC connections (most of which are pooled)
fail in the first place. We had to instead manually restart all client
applications to eliminate all the hung connections.
Here are the details:
* server: PostgreSQL v8.3 on CentOS 5.4
* clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x
releases
The strange thing is that on the client side, according to "netstat" the
TCP connections between the JDBC clients and PostgreSQL were still in
the ESTABLISHED state (even though the server was rebooted). A thread
dump of the a typical hung client (one performing DB updates) looked
like this, it was stuck in a socket read. I was further surprised that
some test clients (which I did not restart yesterday) are STILL in this
hung state this morning, 18 hours after the incident!
"DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145)
at
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114)
at
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
- locked <0xb243db20> (a org.postgresql.core.v3.QueryExecutorImpl)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401)
at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59)
at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199)
Is there a way this hang can be addressed by the JDBC driver? Again,
this is very disconcerting that the clients did not detect the server
failure. Does the connection use any sort of timeout mechanism? Thank
you for your help.
Mike
Mike, The latest version of the driver does have setTimeout implemented. Curious who's java implementation are you using ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, Jun 8, 2012 at 10:20 AM, Mike Charnoky <noky@nextbus.com> wrote: > Hi, > > Yesterday we experienced a problem whereby hundreds of JDBC client > connections remained in a hung state, despite the fact that the PostgreSQL > server was actually rebooted. This is disconcerting because our client > applications have a db failover facility, but that feature won't work unless > existing JDBC connections (most of which are pooled) fail in the first > place. We had to instead manually restart all client applications to > eliminate all the hung connections. > > Here are the details: > * server: PostgreSQL v8.3 on CentOS 5.4 > * clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x > releases > > The strange thing is that on the client side, according to "netstat" the TCP > connections between the JDBC clients and PostgreSQL were still in the > ESTABLISHED state (even though the server was rebooted). A thread dump of > the a typical hung client (one performing DB updates) looked like this, it > was stuck in a socket read. I was further surprised that some test clients > (which I did not restart yesterday) are STILL in this hung state this > morning, 18 hours after the incident! > > "DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000] > java.lang.Thread.State: RUNNABLE > at java.net.SocketInputStream.socketRead0(Native Method) > at java.net.SocketInputStream.read(SocketInputStream.java:129) > at > org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145) > at > org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114) > at > org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) > at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274) > at > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660) > at > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407) > - locked <0xb243db20> (a org.postgresql.core.v3.QueryExecutorImpl) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725) > at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401) > at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59) > at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199) > > Is there a way this hang can be addressed by the JDBC driver? Again, this is > very disconcerting that the clients did not detect the server failure. Does > the connection use any sort of timeout mechanism? Thank you for your help. > > > Mike > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
Thanks for replying Dave. Is this setTimeout new in the 9.1 version of the driver? We have been using the latest 9.0 driver. Does the user of the JDBC API need to set this timeout manually somehow? Does the driver maintain some sort of keepalive communication with the server? Again, it is very strange that the OS (for all client applications) did not detect that the socket closed despite the fact that the server rebooted. That's why I was wondering if an application level keepalive was being used. We are using Sun's JVM, 1.6.0_30. Mike On 06/08/2012 02:18 PM, Dave Cramer wrote: > Mike, > > The latest version of the driver does have setTimeout implemented. > > Curious who's java implementation are you using ? > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > On Fri, Jun 8, 2012 at 10:20 AM, Mike Charnoky<noky@nextbus.com> wrote: >> Hi, >> >> Yesterday we experienced a problem whereby hundreds of JDBC client >> connections remained in a hung state, despite the fact that the PostgreSQL >> server was actually rebooted. This is disconcerting because our client >> applications have a db failover facility, but that feature won't work unless >> existing JDBC connections (most of which are pooled) fail in the first >> place. We had to instead manually restart all client applications to >> eliminate all the hung connections. >> >> Here are the details: >> * server: PostgreSQL v8.3 on CentOS 5.4 >> * clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x >> releases >> >> The strange thing is that on the client side, according to "netstat" the TCP >> connections between the JDBC clients and PostgreSQL were still in the >> ESTABLISHED state (even though the server was rebooted). A thread dump of >> the a typical hung client (one performing DB updates) looked like this, it >> was stuck in a socket read. I was further surprised that some test clients >> (which I did not restart yesterday) are STILL in this hung state this >> morning, 18 hours after the incident! >> >> "DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000] >> java.lang.Thread.State: RUNNABLE >> at java.net.SocketInputStream.socketRead0(Native Method) >> at java.net.SocketInputStream.read(SocketInputStream.java:129) >> at >> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145) >> at >> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114) >> at >> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) >> at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274) >> at >> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660) >> at >> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407) >> - locked<0xb243db20> (a org.postgresql.core.v3.QueryExecutorImpl) >> at >> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725) >> at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401) >> at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59) >> at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199) >> >> Is there a way this hang can be addressed by the JDBC driver? Again, this is >> very disconcerting that the clients did not detect the server failure. Does >> the connection use any sort of timeout mechanism? Thank you for your help. >> >> >> Mike >> >> >> -- >> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-jdbc
Mike, setTimeout is definitely in the 9.1 driver. It is part of the API. The client does need to set it. This http://javaeesupportpatterns.blogspot.ca/2011/04/javanetsocketinputstreamsocketread0.html looks like the same problem Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, Jun 8, 2012 at 2:57 PM, Mike Charnoky <noky@nextbus.com> wrote: > Thanks for replying Dave. > > Is this setTimeout new in the 9.1 version of the driver? We have been using > the latest 9.0 driver. Does the user of the JDBC API need to set this > timeout manually somehow? Does the driver maintain some sort of keepalive > communication with the server? > > Again, it is very strange that the OS (for all client applications) did not > detect that the socket closed despite the fact that the server rebooted. > That's why I was wondering if an application level keepalive was being used. > We are using Sun's JVM, 1.6.0_30. > > > Mike > > > On 06/08/2012 02:18 PM, Dave Cramer wrote: >> >> Mike, >> >> The latest version of the driver does have setTimeout implemented. >> >> Curious who's java implementation are you using ? >> >> Dave Cramer >> >> dave.cramer(at)credativ(dot)ca >> http://www.credativ.ca >> >> >> On Fri, Jun 8, 2012 at 10:20 AM, Mike Charnoky<noky@nextbus.com> wrote: >>> >>> Hi, >>> >>> Yesterday we experienced a problem whereby hundreds of JDBC client >>> connections remained in a hung state, despite the fact that the >>> PostgreSQL >>> server was actually rebooted. This is disconcerting because our client >>> applications have a db failover facility, but that feature won't work >>> unless >>> existing JDBC connections (most of which are pooled) fail in the first >>> place. We had to instead manually restart all client applications to >>> eliminate all the hung connections. >>> >>> Here are the details: >>> * server: PostgreSQL v8.3 on CentOS 5.4 >>> * clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x >>> releases >>> >>> The strange thing is that on the client side, according to "netstat" the >>> TCP >>> connections between the JDBC clients and PostgreSQL were still in the >>> ESTABLISHED state (even though the server was rebooted). A thread dump of >>> the a typical hung client (one performing DB updates) looked like this, >>> it >>> was stuck in a socket read. I was further surprised that some test >>> clients >>> (which I did not restart yesterday) are STILL in this hung state this >>> morning, 18 hours after the incident! >>> >>> "DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000] >>> java.lang.Thread.State: RUNNABLE >>> at java.net.SocketInputStream.socketRead0(Native Method) >>> at java.net.SocketInputStream.read(SocketInputStream.java:129) >>> at >>> >>> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145) >>> at >>> >>> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114) >>> at >>> >>> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) >>> at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274) >>> at >>> >>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660) >>> at >>> >>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407) >>> - locked<0xb243db20> (a org.postgresql.core.v3.QueryExecutorImpl) >>> at >>> >>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725) >>> at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401) >>> at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59) >>> at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199) >>> >>> Is there a way this hang can be addressed by the JDBC driver? Again, this >>> is >>> very disconcerting that the clients did not detect the server failure. >>> Does >>> the connection use any sort of timeout mechanism? Thank you for your >>> help. >>> >>> >>> Mike >>> >>> >>> -- >>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-jdbc
Just to be clear, I'm assuming that you're referring to java.sql.Statement.setQueryTimeout(). Is that correct? Is there any other mechanism used for setting timeouts? It sounds like we need to set this timeout with a value that is as low as possible, but not too low such that a long-running query/update would be terminated. Thanks again for your help. Mike On 06/08/2012 03:29 PM, Dave Cramer wrote: > Mike, > > setTimeout is definitely in the 9.1 driver. It is part of the API. The > client does need to set it. > > > This http://javaeesupportpatterns.blogspot.ca/2011/04/javanetsocketinputstreamsocketread0.html > looks like the same problem > > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > On Fri, Jun 8, 2012 at 2:57 PM, Mike Charnoky<noky@nextbus.com> wrote: >> Thanks for replying Dave. >> >> Is this setTimeout new in the 9.1 version of the driver? We have been using >> the latest 9.0 driver. Does the user of the JDBC API need to set this >> timeout manually somehow? Does the driver maintain some sort of keepalive >> communication with the server? >> >> Again, it is very strange that the OS (for all client applications) did not >> detect that the socket closed despite the fact that the server rebooted. >> That's why I was wondering if an application level keepalive was being used. >> We are using Sun's JVM, 1.6.0_30. >> >> >> Mike >> >> >> On 06/08/2012 02:18 PM, Dave Cramer wrote: >>> Mike, >>> >>> The latest version of the driver does have setTimeout implemented. >>> >>> Curious who's java implementation are you using ? >>> >>> Dave Cramer >>> >>> dave.cramer(at)credativ(dot)ca >>> http://www.credativ.ca >>> >>> >>> On Fri, Jun 8, 2012 at 10:20 AM, Mike Charnoky<noky@nextbus.com> wrote: >>>> Hi, >>>> >>>> Yesterday we experienced a problem whereby hundreds of JDBC client >>>> connections remained in a hung state, despite the fact that the >>>> PostgreSQL >>>> server was actually rebooted. This is disconcerting because our client >>>> applications have a db failover facility, but that feature won't work >>>> unless >>>> existing JDBC connections (most of which are pooled) fail in the first >>>> place. We had to instead manually restart all client applications to >>>> eliminate all the hung connections. >>>> >>>> Here are the details: >>>> * server: PostgreSQL v8.3 on CentOS 5.4 >>>> * clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x >>>> releases >>>> >>>> The strange thing is that on the client side, according to "netstat" the >>>> TCP >>>> connections between the JDBC clients and PostgreSQL were still in the >>>> ESTABLISHED state (even though the server was rebooted). A thread dump of >>>> the a typical hung client (one performing DB updates) looked like this, >>>> it >>>> was stuck in a socket read. I was further surprised that some test >>>> clients >>>> (which I did not restart yesterday) are STILL in this hung state this >>>> morning, 18 hours after the incident! >>>> >>>> "DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000] >>>> java.lang.Thread.State: RUNNABLE >>>> at java.net.SocketInputStream.socketRead0(Native Method) >>>> at java.net.SocketInputStream.read(SocketInputStream.java:129) >>>> at >>>> >>>> org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145) >>>> at >>>> >>>> org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114) >>>> at >>>> >>>> org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) >>>> at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274) >>>> at >>>> >>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660) >>>> at >>>> >>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407) >>>> - locked<0xb243db20> (a org.postgresql.core.v3.QueryExecutorImpl) >>>> at >>>> >>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725) >>>> at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401) >>>> at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59) >>>> at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199) >>>> >>>> Is there a way this hang can be addressed by the JDBC driver? Again, this >>>> is >>>> very disconcerting that the clients did not detect the server failure. >>>> Does >>>> the connection use any sort of timeout mechanism? Thank you for your >>>> help. >>>> >>>> >>>> Mike >>>> >>>> >>>> -- >>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-jdbc
On 06/08/2012 10:20 PM, Mike Charnoky wrote: > Hi, > > Yesterday we experienced a problem whereby hundreds of JDBC client > connections remained in a hung state, despite the fact that the > PostgreSQL server was actually rebooted. From the stack trace below, that would seem to be clients that were actively in a query and receiving data from the server at the time. > The strange thing is that on the client side, according to "netstat" > the TCP connections between the JDBC clients and PostgreSQL were still > in the ESTABLISHED state (even though the server was rebooted). Presumably your server wasn't rebooted cleanly, or at least PostgreSQL wasn't able to shut down cleanly, so no RST or FIN packets were sent for connections. If a tcp client doesn't receive a FIN or RST from its peer, it won't know the peer is going away and will continue waiting indefinitely. The wait will only terminate if: - The application using the tcp socket has set a timeout or alarm that'll interrupt the read(), allowing it to decide that the remote peer might've gone away and unilaterally close the connection; or - The operating system has tcp keepalives configured, and concludes after several missed keepalives that the remote peer is dead. The operating system will close the socket and return control to the client with an error flag set, allowing the client to continue. If you don't want the default behaviour of the TCP stack, you need to set a tcp keepalive globally for the OS, get your program (in this case Java + PgJDBC) to set a keepalive flag on its sockets, or get the client program to set an alarm to interrupt blocked socket reads after a timeout. PgJDBC didn't offer the latter option until 9.1, so you had to use keepalives. If you didn't use keepalives, connections would stay stuck until whatever supervising tool decided they weren't going to respond and terminated them. If nothing like a transaction manager was supervising ,they'd just stay open. > Is there a way this hang can be addressed by the JDBC driver? Again, this is very disconcerting that the clients did not detect the server failure. Does the connection use any sort of timeout mechanism? Thank you for your help. TCP can't tell the difference between "peer dead" and "peer unreachable due to sustained connection issues or congestion", so it assumes the peer is alive and will respond ... eventually. The wisdom of this decision may be questionable, but at least keepalives provide a solution. Of course, It's not very friendly for the JDBC driver to wait indefinitely without periodically checking and seeing if the connection was progressing, so I'm glad to see that's changed in 9.1. -- Craig Ringer
On 06/09/2012 11:39 PM, Craig Ringer wrote:
>
> Of course, It's not very friendly for the JDBC driver to wait
> indefinitely without periodically checking and seeing if the
> connection was progressing, so I'm glad to see that's changed in 9.1.
>
I took a look at the 9.1 JDBC driver source code, very enlightening. I
see that Statement.setQueryTimeout() doesn't actually do anything! The
method sets a member variable which is never accessed locally or by any
subclasses or via getQueryTimeout(). There is even the following comment
in AbstractJdbc2Statement:
/** Timeout (in seconds) for a query (not used) */
protected int timeout = 0;
I see that ConnectionFactoryImpl does allow the underlying Socket's
so_timeout and so_keepalive options to be set using the connection
parameters string:
http://jdbc.postgresql.org/documentation/91/connect.html (socketTimeout
and tcpKeepAlive). Didn't realize these options existed, I will
definitely be taking advantage of these.
However, I see these options were available as far back as 8.4. What
exactly are these driver changes in 9.1 that you mentioned???
Mike
Mike, It was implemented in the 9_2 code Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Jun 11, 2012 at 11:02 AM, Mike Charnoky <noky@nextbus.com> wrote: > > On 06/09/2012 11:39 PM, Craig Ringer wrote: >> >> >> Of course, It's not very friendly for the JDBC driver to wait indefinitely >> without periodically checking and seeing if the connection was progressing, >> so I'm glad to see that's changed in 9.1. >> > I took a look at the 9.1 JDBC driver source code, very enlightening. I see > that Statement.setQueryTimeout() doesn't actually do anything! The method > sets a member variable which is never accessed locally or by any subclasses > or via getQueryTimeout(). There is even the following comment in > AbstractJdbc2Statement: > /** Timeout (in seconds) for a query (not used) */ > protected int timeout = 0; > > I see that ConnectionFactoryImpl does allow the underlying Socket's > so_timeout and so_keepalive options to be set using the connection > parameters string: http://jdbc.postgresql.org/documentation/91/connect.html > (socketTimeout and tcpKeepAlive). Didn't realize these options existed, I > will definitely be taking advantage of these. > > However, I see these options were available as far back as 8.4. What exactly > are these driver changes in 9.1 that you mentioned??? > > > Mike > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
On 06/11/2012 11:02 PM, Mike Charnoky wrote: > > On 06/09/2012 11:39 PM, Craig Ringer wrote: >> >> Of course, It's not very friendly for the JDBC driver to wait >> indefinitely without periodically checking and seeing if the >> connection was progressing, so I'm glad to see that's changed in 9.1. >> > I took a look at the 9.1 JDBC driver source code, very enlightening. I > see that Statement.setQueryTimeout() doesn't actually do anything! The > method sets a member variable which is never accessed locally or by > any subclasses or via getQueryTimeout(). There is even the following > comment in AbstractJdbc2Statement: > /** Timeout (in seconds) for a query (not used) */ > protected int timeout = 0; > > I see that ConnectionFactoryImpl does allow the underlying Socket's > so_timeout and so_keepalive options to be set using the connection > parameters string: > http://jdbc.postgresql.org/documentation/91/connect.html > (socketTimeout and tcpKeepAlive). Didn't realize these options > existed, I will definitely be taking advantage of these. > > However, I see these options were available as far back as 8.4. What > exactly are these driver changes in 9.1 that you mentioned??? Sorry, as Dave points out, 9.2. -- Craig Ringer