Thread: Idle session timeout?
I've got a bunch of users on VB applications connecting to PG 8.2.3 via psqlodbc with SSL. For a variety of reasons, some good, some probably bad, I have the app open a connection and leave it open, using it as needed to run queries back and forth. If I look in my logs, I see loans of little clusters of entries like this: 2007-05-08 08:44:57 PDT-xxxx@yyy.yyy.yyy.yyy LOG: SSL SYSCALL error: Connection reset by peer 2007-05-08 08:44:57 PDT-xxxx@yyy.yyy.yyy.yyy LOG: could not receive data from client: Connection reset by peer 2007-05-08 08:44:57 PDT-xxxx@yyy.yyy.yyy.yyy LOG: unexpected EOF on client connection These seem to correspond to dropped connections for my users, generally if a user with an open app that hasn't used it for a while attempts to use it. I've put some code in to re-establish the connection to mitigate user frustration, but it doesn't do much to mitigate my frustration. I'm WAY out of my depth here, but my impression, based on the circumstances, is that there is some sort of an idle session timeout kicking in (most likely on the client side) and dropping the connection. Judging by the error messages I'd be inclined to say that it's happening in openSSL, not Postgres, but I don't know. I've searched documentation and newsgroups for Postgres, psqlodbc, and openSSL, and haven't found any user-settable timeout parameters. The one thing I did come across that seemed to my clueless eyes to be relevant was an OpenSSL API call: SSL_CTX_set_timeout(). Even assuming this is relevant, wouldn't this be the kind of thing that would need to be called by Postgres code at the point that SSL is integrated? Anyway, I appreciate any help or guidance anyone might be able to give. It would be awesome if there were a user parameter somewhere on the server or client that someone could point out to me to take care of this. If this is a bug, please help me figure out who it belongs to. If this is a feature, please let me know so I can properly account for it in my code.
Sean Murphy <Sean.Murphy@equipoint.com> writes:
> I'm WAY out of my depth here, but my impression, based on the
> circumstances, is that there is some sort of an idle session timeout
> kicking in (most likely on the client side) and dropping the connection.
There's no such timeout in the Postgres server, for sure. I would
actually bet that your problem is in some router between the client and
the server. In particular, routers that do NAT address mapping
typically have a timeout after which they will forget the mapping for an
idle connection. If you've got one of those, see if it'll let you
change the timeout.
If you can't do that, you might think about teaching your client-side
code to send dummy queries every so often.
regards, tom lane
Tom Lane wrote: > Sean Murphy <Sean.Murphy@equipoint.com> writes: >> I'm WAY out of my depth here, but my impression, based on the >> circumstances, is that there is some sort of an idle session timeout >> kicking in (most likely on the client side) and dropping the connection. > > There's no such timeout in the Postgres server, for sure. I would > actually bet that your problem is in some router between the client and > the server. In particular, routers that do NAT address mapping > typically have a timeout after which they will forget the mapping for an > idle connection. If you've got one of those, see if it'll let you > change the timeout. > > If you can't do that, you might think about teaching your client-side > code to send dummy queries every so often. > > regards, tom lane > I've already maxed out the connection timeout at the firewall... and I've been using dummy queries every five minutes, but it just feels like a crutch to do so. Sean
On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: > Tom Lane wrote: > > Sean Murphy <Sean.Murphy@equipoint.com> writes: > >> I'm WAY out of my depth here, but my impression, based on the > >> circumstances, is that there is some sort of an idle session timeout > >> kicking in (most likely on the client side) and dropping the connection. > > > > There's no such timeout in the Postgres server, for sure. I would > > actually bet that your problem is in some router between the client and > > the server. In particular, routers that do NAT address mapping > > typically have a timeout after which they will forget the mapping for an > > idle connection. If you've got one of those, see if it'll let you > > change the timeout. > > > > If you can't do that, you might think about teaching your client-side > > code to send dummy queries every so often. > > > > regards, tom lane > > > > I've already maxed out the connection timeout at the firewall... and > I've been using dummy queries every five minutes, but it just feels like > a crutch to do so. Have you looked into tcp keepalive settings? net.ipv4.tcp_keepalive_intvl = 75 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_time = 500 Not sure if those settings will help with an NAT router or not but it's worth a try. Pgsql 8.2 can set those for you.
Scott Marlowe wrote: > On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: >> Tom Lane wrote: >>> Sean Murphy <Sean.Murphy@equipoint.com> writes: >>>> I'm WAY out of my depth here, but my impression, based on the >>>> circumstances, is that there is some sort of an idle session timeout >>>> kicking in (most likely on the client side) and dropping the connection. >>> There's no such timeout in the Postgres server, for sure. I would >>> actually bet that your problem is in some router between the client and >>> the server. In particular, routers that do NAT address mapping >>> typically have a timeout after which they will forget the mapping for an >>> idle connection. If you've got one of those, see if it'll let you >>> change the timeout. >>> >>> If you can't do that, you might think about teaching your client-side >>> code to send dummy queries every so often. >>> >>> regards, tom lane >>> >> I've already maxed out the connection timeout at the firewall... and >> I've been using dummy queries every five minutes, but it just feels like >> a crutch to do so. > > Have you looked into tcp keepalive settings? > > net.ipv4.tcp_keepalive_intvl = 75 > net.ipv4.tcp_keepalive_probes = 9 > net.ipv4.tcp_keepalive_time = 500 > > Not sure if those settings will help with an NAT router or not but it's > worth a try. Pgsql 8.2 can set those for you. > I may be celebrating prematurely, but resetting the tcp_keepalive parameters seems to have done the trick - I left a pgAdmin connection that *always* drops after inactivity up while I went to lunch and it was still alive when I got back. Is there a way to alter the tcp_keepalive settings on an app-by-app basis rather than for the whole system? Thanks!
On Tue, 2007-05-08 at 15:59, Sean Murphy wrote: > Scott Marlowe wrote: > > On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: > >> Tom Lane wrote: > >>> Sean Murphy <Sean.Murphy@equipoint.com> writes: > >>>> I'm WAY out of my depth here, but my impression, based on the > >>>> circumstances, is that there is some sort of an idle session timeout > >>>> kicking in (most likely on the client side) and dropping the connection. > >>> There's no such timeout in the Postgres server, for sure. I would > >>> actually bet that your problem is in some router between the client and > >>> the server. In particular, routers that do NAT address mapping > >>> typically have a timeout after which they will forget the mapping for an > >>> idle connection. If you've got one of those, see if it'll let you > >>> change the timeout. > >>> > >>> If you can't do that, you might think about teaching your client-side > >>> code to send dummy queries every so often. > >>> > >>> regards, tom lane > >>> > >> I've already maxed out the connection timeout at the firewall... and > >> I've been using dummy queries every five minutes, but it just feels like > >> a crutch to do so. > > > > Have you looked into tcp keepalive settings? > > > > net.ipv4.tcp_keepalive_intvl = 75 > > net.ipv4.tcp_keepalive_probes = 9 > > net.ipv4.tcp_keepalive_time = 500 > > > > Not sure if those settings will help with an NAT router or not but it's > > worth a try. Pgsql 8.2 can set those for you. > > > > I may be celebrating prematurely, Never stopped me :) > but resetting the tcp_keepalive > parameters seems to have done the trick - I left a pgAdmin connection > that *always* drops after inactivity up while I went to lunch and it was > still alive when I got back. > > Is there a way to alter the tcp_keepalive settings on an app-by-app > basis rather than for the whole system? Well, you could set it on individual workstations instead of on the server. I.e. if you set tcp_keepalive on your workstation to 500, but leave Wally and Dilbert set at the default 7200 then they'd still timeout and you wouldn't.
Scott Marlowe wrote: > On Tue, 2007-05-08 at 15:59, Sean Murphy wrote: >> Scott Marlowe wrote: >>> On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: >>>> Tom Lane wrote: >>>>> Sean Murphy <Sean.Murphy@equipoint.com> writes: >>>>>> I'm WAY out of my depth here, but my impression, based on the >>>>>> circumstances, is that there is some sort of an idle session timeout >>>>>> kicking in (most likely on the client side) and dropping the connection. >>>>> There's no such timeout in the Postgres server, for sure. I would >>>>> actually bet that your problem is in some router between the client and >>>>> the server. In particular, routers that do NAT address mapping >>>>> typically have a timeout after which they will forget the mapping for an >>>>> idle connection. If you've got one of those, see if it'll let you >>>>> change the timeout. >>>>> >>>>> If you can't do that, you might think about teaching your client-side >>>>> code to send dummy queries every so often. >>>>> >>>>> regards, tom lane >>>>> >>>> I've already maxed out the connection timeout at the firewall... and >>>> I've been using dummy queries every five minutes, but it just feels like >>>> a crutch to do so. >>> Have you looked into tcp keepalive settings? >>> >>> net.ipv4.tcp_keepalive_intvl = 75 >>> net.ipv4.tcp_keepalive_probes = 9 >>> net.ipv4.tcp_keepalive_time = 500 >>> >>> Not sure if those settings will help with an NAT router or not but it's >>> worth a try. Pgsql 8.2 can set those for you. >>> >> I may be celebrating prematurely, > > Never stopped me :) > >> but resetting the tcp_keepalive >> parameters seems to have done the trick - I left a pgAdmin connection >> that *always* drops after inactivity up while I went to lunch and it was >> still alive when I got back. >> >> Is there a way to alter the tcp_keepalive settings on an app-by-app >> basis rather than for the whole system? > > Well, you could set it on individual workstations instead of on the > server. I.e. if you set tcp_keepalive on your workstation to 500, but > leave Wally and Dilbert set at the default 7200 then they'd still > timeout and you wouldn't. > Unfortunately, my individual workstations are all running Windows...:( any idea if/where this could be set in MS-land?
Sean Murphy wrote: > Scott Marlowe wrote: >> On Tue, 2007-05-08 at 15:59, Sean Murphy wrote: >>> Scott Marlowe wrote: >>>> On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: >>>>> Tom Lane wrote: >>>>>> Sean Murphy <Sean.Murphy@equipoint.com> writes: >>>>>>> I'm WAY out of my depth here, but my impression, based on the >>>>>>> circumstances, is that there is some sort of an idle session timeout >>>>>>> kicking in (most likely on the client side) and dropping the connection. >>>>>> There's no such timeout in the Postgres server, for sure. I would >>>>>> actually bet that your problem is in some router between the client and >>>>>> the server. In particular, routers that do NAT address mapping >>>>>> typically have a timeout after which they will forget the mapping for an >>>>>> idle connection. If you've got one of those, see if it'll let you >>>>>> change the timeout. >>>>>> >>>>>> If you can't do that, you might think about teaching your client-side >>>>>> code to send dummy queries every so often. >>>>>> >>>>>> regards, tom lane >>>>>> >>>>> I've already maxed out the connection timeout at the firewall... and >>>>> I've been using dummy queries every five minutes, but it just feels like >>>>> a crutch to do so. >>>> Have you looked into tcp keepalive settings? >>>> >>>> net.ipv4.tcp_keepalive_intvl = 75 >>>> net.ipv4.tcp_keepalive_probes = 9 >>>> net.ipv4.tcp_keepalive_time = 500 >>>> >>>> Not sure if those settings will help with an NAT router or not but it's >>>> worth a try. Pgsql 8.2 can set those for you. >>>> >>> I may be celebrating prematurely, >> Never stopped me :) >> >>> but resetting the tcp_keepalive >>> parameters seems to have done the trick - I left a pgAdmin connection >>> that *always* drops after inactivity up while I went to lunch and it was >>> still alive when I got back. >>> >>> Is there a way to alter the tcp_keepalive settings on an app-by-app >>> basis rather than for the whole system? >> Well, you could set it on individual workstations instead of on the >> server. I.e. if you set tcp_keepalive on your workstation to 500, but >> leave Wally and Dilbert set at the default 7200 then they'd still >> timeout and you wouldn't. >> > > Unfortunately, my individual workstations are all running Windows...:( > any idea if/where this could be set in MS-land? > You need to alter some settings in the registry. See: http://msdn2.microsoft.com/en-us/library/aa302363.aspx -- Paul Lambert Database Administrator AutoLedgers
On Tue, 2007-05-08 at 17:35, Sean Murphy wrote: > Scott Marlowe wrote: > > Well, you could set it on individual workstations instead of on the > > server. I.e. if you set tcp_keepalive on your workstation to 500, but > > leave Wally and Dilbert set at the default 7200 then they'd still > > timeout and you wouldn't. > > > > Unfortunately, my individual workstations are all running Windows...:( > any idea if/where this could be set in MS-land? Nope. You're much more likely to get a useful answer from google than from me regarding windows. :)