pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
JDBC keep alive issue 2+ messages / 2 participants [nested] [flat]
* JDBC keep alive issue @ 2012-08-10 06:56 Haifeng Liu <liuhaifeng@live.com> 0 siblings, 1 reply; 2+ messages in thread From: Haifeng Liu @ 2012-08-10 06:56 UTC (permalink / raw) To: pgsql-admin Hi experts, I have a program running like a daemon, which analyze data and write to postgresql 9.1 on centos 5.8. There is only one connection between my program and the postgresql database, and I hope the connection may keep alive all the time. But I failed, the connection will be reset after idle for about 2 hours. jdbc driver: 9.1-901, connection url has parameter tcpKeepAlive=true; postgresql:9.1, keep alive related settings use default values(commented); centos 5.8 64bit, net.ipv4.tcp_keepalive_intvl = 75, probes = 9, time = 7200. There is no firewall or any other device which behaves force idle connection cleanup. Am I missing some configurations? What's the right way to keep a long time connection? From pgsql-admin-owner@postgresql.org Fri Aug 10 22:22:52 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from <tgl@sss.pgh.pa.us>) id 1SzxbP-0005QF-8o for pgsql-admin@postgresql.org; Fri, 10 Aug 2012 22:22:43 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from <tgl@sss.pgh.pa.us>) id 1Szq3u-0006pL-I3 for pgsql-admin@postgresql.org; Fri, 10 Aug 2012 14:19:39 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q7AEJO7F023655; Fri, 10 Aug 2012 10:19:24 -0400 (EDT) From: Tom Lane <tgl@sss.pgh.pa.us> To: Haifeng Liu <liuhaifeng@live.com> cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> Subject: Re: JDBC keep alive issue In-reply-to: <BLU0-SMTP394B69FD9FE644CD7BA022DB9B30@phx.gbl> References: <BLU0-SMTP394B69FD9FE644CD7BA022DB9B30@phx.gbl> Comments: In-reply-to Haifeng Liu <liuhaifeng@live.com> message dated "Fri, 10 Aug 2012 14:56:02 +0800" Date: Fri, 10 Aug 2012 10:19:24 -0400 Message-ID: <23654.1344608364@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201208/77 X-Sequence-Number: 39898 Haifeng Liu <liuhaifeng@live.com> writes: > I have a program running like a daemon, which analyze data and write to postgresql 9.1 on centos 5.8. There is only one connection between my program and the postgresql database, and I hope the connection may keep alive all the time. But I failed, the connection will be reset after idle for about 2 hours. > jdbc driver: 9.1-901, connection url has parameter tcpKeepAlive=true; > postgresql:9.1, keep alive related settings use default values(commented); > centos 5.8 64bit, net.ipv4.tcp_keepalive_intvl = 75, probes = 9, time = 7200. IIRC, time = 7200 (seconds) means to start sending keepalive packets after 2 hours of idle time. So if you have something in the way that is dropping the connection after 2 hours, these settings will not activate keepalive soon enough to save it. I'd try setting that to 3600. > There is no firewall or any other device which behaves force idle connection cleanup. Seems pretty darn unlikely given these symptoms. Look harder... maybe something you thought was just a bridge has got routing behavior. BTW, in the real world connections drop for all sorts of reasons, and kernel keepalive configurations can't prevent them all. You might be better advised to build some reconnect-after-connection-loss logic into your application, rather than spending time on making this work. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: JDBC keep alive issue @ 2012-08-11 11:26 Craig James <cjames@emolecules.com> parent: Haifeng Liu <liuhaifeng@live.com> 0 siblings, 0 replies; 2+ messages in thread From: Craig James @ 2012-08-11 11:26 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Haifeng Liu <liuhaifeng@live.com>; pgsql-admin On Fri, Aug 10, 2012 at 7:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Haifeng Liu <liuhaifeng@live.com> writes: > > I have a program running like a daemon, which analyze data and write to > postgresql 9.1 on centos 5.8. There is only one connection between my > program and the postgresql database, and I hope the connection may keep > alive all the time. But I failed, the connection will be reset after idle > for about 2 hours. > > > jdbc driver: 9.1-901, connection url has parameter tcpKeepAlive=true; > > postgresql:9.1, keep alive related settings use default > values(commented); > > centos 5.8 64bit, net.ipv4.tcp_keepalive_intvl = 75, probes = 9, time = > 7200. > > IIRC, time = 7200 (seconds) means to start sending keepalive packets > after 2 hours of idle time. So if you have something in the way that is > dropping the connection after 2 hours, these settings will not activate > keepalive soon enough to save it. I'd try setting that to 3600. > I use a much shorter time (300). Why not? The keepalive pings are a trivial amount of data. I've found over the years that router NAT tables are a big offender (probably doesn't apply in this case) ... some drop your internal-to-external IP address mapping after as little as five minutes. Craig > > > There is no firewall or any other device which behaves force idle > connection cleanup. > > Seems pretty darn unlikely given these symptoms. Look harder... > maybe something you thought was just a bridge has got routing behavior. > > BTW, in the real world connections drop for all sorts of reasons, and > kernel keepalive configurations can't prevent them all. You might be > better advised to build some reconnect-after-connection-loss logic into > your application, rather than spending time on making this work. > > regards, tom lane > > - > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-08-11 11:26 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-08-10 06:56 JDBC keep alive issue Haifeng Liu <liuhaifeng@live.com> 2012-08-11 11:26 ` Craig James <cjames@emolecules.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox