[ADMIN] JDBC keep alive issue

[Haifeng Liu]

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?

[Tom Lane]

Post by Haifeng Liu
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.

Post by Haifeng Liu
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-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Craig James]

Post by Haifeng Liu

Post by Haifeng Liu
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.

Post by Haifeng Liu
jdbc driver: 9.1-901, connection url has parameter tcpKeepAlive=true;
postgresql:9.1, keep alive related settings use default

values(commented);

Post by Haifeng Liu
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

Post by Haifeng Liu

Post by Haifeng Liu
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
-
http://www.postgresql.org/mailpref/pgsql-admin