Обсуждение: close inactive client connection
Hello, I am developing a system, using the ZeosLib, which maintains a permanent connection to the database. When the client loses the connection, the server does not recognize this and keeps the connection as if it were active. When the client attempts to reconnect, the server creates a new connection and the previous remains active. This has caused an excessive increase in the number of active connections and hampered the use of the system. Have any way or setting to make postgres close idle connections? -- Luiz Agnaldo BernardiFone 41 36755756 41 99979849
Luiz Bernardi wrote: > I am developing a system, using the ZeosLib, which maintains a > permanent connection to the database. > > When the client loses the connection, the server does not recognize > this and keeps the connection as if it were active. When the client > attempts to reconnect, the server creates a new connection and the > previous remains active. how does this happen ? TCP connections don't just wander off and get lost. > This has caused an excessive increase in the number of active > connections and hampered the use of the system. > > Have any way or setting to make postgres close idle connections? first, you'd have to identify that they were in fact 'lost' and not just idle. we have apps that open a socket to postgres, issue some commands, then sometimes sit for hours before more commands are issued.
On Fri, Sep 25, 2009 at 6:26 AM, Luiz Bernardi <lb@cplinformatica.com> wrote: > Hello, > > I am developing a system, using the ZeosLib, which maintains a permanent > connection to the database. > > When the client loses the connection, the server does not recognize this and > keeps the connection as if it were active. When the client attempts to > reconnect, the server creates a new connection and the previous remains > active. Eventually the server's network stack will check to see if the connection is alive or not by sending a tcp keepalive ping. If it receives no answers after x number of tries, it will close the socket and the pg backend will be terminated. To adjust this time, look at the tcp keepalive parameters in either the postgresql.conf file or system wide on your server. The default timeout is 2 hours. It's not unusual to drop it to 5 or 10 minutes on busy systems.
Thanks, Scott. I change the setting and then find out why they were losing the client connection. -- Luiz Agnaldo Bernardi Fone 41 36755756 41 99979849 On Fri, 25 Sep 2009 11:11:52 -0600 Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Fri, Sep 25, 2009 at 6:26 AM, Luiz Bernardi ><lb@cplinformatica.com> wrote: >> Hello, >> >> I am developing a system, using the ZeosLib, which maintains a >>permanent >> connection to the database. >> >> When the client loses the connection, the server does not recognize >>this and >> keeps the connection as if it were active. When the client attempts >>to >> reconnect, the server creates a new connection and the previous >>remains >> active. > > Eventually the server's network stack will check to see if the > connection is alive or not by sending a tcp keepalive ping. If it > receives no answers after x number of tries, it will close the >socket > and the pg backend will be terminated. > > To adjust this time, look at the tcp keepalive parameters in either > the postgresql.conf file or system wide on your server. The default > timeout is 2 hours. It's not unusual to drop it to 5 or 10 minutes >on > busy systems.
hi John I have no idea of what may be happening. The system opens a connection and communicate normally. But after an idle time, it loses the connection and new transactions return with error. 16/09/2009 13:39:14 - SQL Error: no connection to the server 16/09/2009 13:39:14 - SQL Error: connection not open At this point he reconnects, the server creates a new connection that behaves exactly like the previous one. -- Luiz Agnaldo Bernardi Fone 41 36755756 41 99979849 On Fri, 25 Sep 2009 10:03:20 -0700 John R Pierce <pierce@hogranch.com> wrote: > Luiz Bernardi wrote: >> I am developing a system, using the ZeosLib, which maintains a >>permanent connection to the database. >> >> When the client loses the connection, the server does not recognize >>this and keeps the connection as if it were active. When the client >>attempts to reconnect, the server creates a new connection and the >>previous remains active. > > how does this happen ? TCP connections don't just wander off and >get lost. > >> This has caused an excessive increase in the number of active >>connections and hampered the use of the system. >> >> Have any way or setting to make postgres close idle connections? > > first, you'd have to identify that they were in fact 'lost' and not >just idle. we have apps that open a socket to postgres, issue some >commands, then sometimes sit for hours before more commands are >issued. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Are these machines communicating through a firewall? Often firewalls timeout idle tcp/ip connections. On Fri, Sep 25, 2009 at 12:56 PM, Luiz Bernardi <lb@cplinformatica.com> wrote: > hi John > > > I have no idea of what may be happening. The system opens a connection and > communicate normally. But after an idle time, it loses the connection and > new transactions return with error. > 16/09/2009 13:39:14 - SQL Error: no connection to the server > 16/09/2009 13:39:14 - SQL Error: connection not open > > > At this point he reconnects, the server creates a new connection that > behaves exactly like the previous one. > > > > -- > Luiz Agnaldo Bernardi > Fone 41 36755756 > 41 99979849 > > > > On Fri, 25 Sep 2009 10:03:20 -0700 > John R Pierce <pierce@hogranch.com> wrote: >> >> Luiz Bernardi wrote: >>> >>> I am developing a system, using the ZeosLib, which maintains a permanent >>> connection to the database. >>> >>> When the client loses the connection, the server does not recognize this >>> and keeps the connection as if it were active. When the client attempts to >>> reconnect, the server creates a new connection and the previous remains >>> active. >> >> how does this happen ? TCP connections don't just wander off and get >> lost. >> >>> This has caused an excessive increase in the number of active connections >>> and hampered the use of the system. >>> >>> Have any way or setting to make postgres close idle connections? >> >> first, you'd have to identify that they were in fact 'lost' and not just >> idle. we have apps that open a socket to postgres, issue some commands, >> then sometimes sit for hours before more commands are issued. >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- When fascism comes to America, it will be intolerance sold as diversity.