Обсуждение: Clearing out old idle connections
Hullo :) We have pg 8.1.3 and for whatever reason (network blips, poor pooling on behalf of the client, etc.) we sometimes see a large number (dozens) of old connections in the idle state which never get reused. Is there a function in postgres similar to MySQL's 'wait_timeout' which automatically closes any connections which have been idle for N seconds? Is this functionality possible to to script/cron by examining the pg catalogs and finding a 'last used' timestamp? I am aware the correct response is 'deal with the cause, not the symptoms', however I assure you that this academic approach wins no friends in the enterprise market that postgres pitches itself at :) Cheers, Gavin.
Hello, > We have pg 8.1.3 and for whatever reason (network blips, poor pooling on > behalf of the client, etc.) we sometimes see a large number (dozens) of > old connections in the idle state which never get reused. It seems that I have more or less the same problem. Sometimes I see in `ps aux` lots of idle connections from web application. They disappear in several minutes but I do not know what the reason of it and how they disappear. I have statement_timeout by it obviously is not related with these clients since they are idle. > Is there a function in postgres similar to MySQL's 'wait_timeout' which > automatically closes any connections which have been idle for N seconds? > Is this functionality possible to to script/cron by examining the pg > catalogs and finding a 'last used' timestamp? It is possible to find out time of backend start and it's state through pg_stat_activity view. But I'd prefer not to kill -QUIT these connections if there's something special for this situation built in PostgreSQL. Regards, Ivan Zolotukhin
Gavin Hamill wrote: > Hullo :) > > > We have pg 8.1.3 and for whatever reason (network blips, poor pooling on > behalf of the client, etc.) we sometimes see a large number (dozens) of > old connections in the idle state which never get reused. They should expire based on your TCP/IP settings. It's a TCP/IP connection timeout issue really, PG never gets to see them. > Is there a function in postgres similar to MySQL's 'wait_timeout' which > automatically closes any connections which have been idle for N seconds? > Is this functionality possible to to script/cron by examining the pg > catalogs and finding a 'last used' timestamp? Hmm - are the times in pg_stat_activity useful to you. > I am aware the correct response is 'deal with the cause, not the > symptoms', however I assure you that this academic approach wins no > friends in the enterprise market that postgres pitches itself at :) Run pgpool in front of PG for real control. That should be simple enough to tweak to provide whatever timeout rules you want. HTH -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Gavin Hamill wrote: >> Hullo :) >> >> >> We have pg 8.1.3 and for whatever reason (network blips, poor pooling >> on behalf of the client, etc.) we sometimes see a large number >> (dozens) of old connections in the idle state which never get reused. > > They should expire based on your TCP/IP settings. It's a TCP/IP > connection timeout issue really, PG never gets to see them. The problem is that tcp doesn't periodically send "keepalive" packets by default, so if the client suddenly disappears (e.g if the machine it's running is is disconnected from the network) postgres has no chance to notice this until it tries to send data to the client, and receives a timeout. But if a connection is idle, then postgres will never try to send data to the client, and so it never notices that the connection is in fact dead. You can (at least on linux, I assume it's the same for BSD) set the "keepalive" flag of a connection. This results in empty packets being sent every 30 seconds or so, and the connection is reported to be dead if no ACK is received within a timeout. I don't know if postgres has a flag to disable/enable this. If not, I'd guess it would be quite trivial to add that. greetings, Florian Pflug
"Florian G. Pflug" <fgp@phlo.org> writes:
> You can (at least on linux, I assume it's the same for BSD) set the
> "keepalive" flag of a connection. This results in empty packets being
> sent every 30 seconds or so, and the connection is reported to be dead
> if no ACK is received within a timeout.
> I don't know if postgres has a flag to disable/enable this. If not, I'd
> guess it would be quite trivial to add that.
Postgres has always (well, for as long as I can remember) enabled
keepalive checks on client TCP connections. However, the default
timeouts on TCP keepalive are not "30 seconds or so", but well over
an hour according to the RFC specifications.
Some platforms allow you to fool with the timeouts, some don't.
If you have one that does, recent PG versions will let you configure
that.
I'm not sure whether this will actually solve the OP's problem, because
if the clients are actually still there but not doing anything, the
connection is not going to time out. This is mainly something that
helps with flaky networks, broken Windoze boxes that forget they have
connections, that sort of thing...
regards, tom lane
On Wed, 2006-05-24 at 14:00, Tom Lane wrote: > "Florian G. Pflug" <fgp@phlo.org> writes: > > You can (at least on linux, I assume it's the same for BSD) set the > > "keepalive" flag of a connection. This results in empty packets being > > sent every 30 seconds or so, and the connection is reported to be dead > > if no ACK is received within a timeout. > > > I don't know if postgres has a flag to disable/enable this. If not, I'd > > guess it would be quite trivial to add that. > > Postgres has always (well, for as long as I can remember) enabled > keepalive checks on client TCP connections. However, the default > timeouts on TCP keepalive are not "30 seconds or so", but well over > an hour according to the RFC specifications. > > Some platforms allow you to fool with the timeouts, some don't. > If you have one that does, recent PG versions will let you configure > that. > > I'm not sure whether this will actually solve the OP's problem, because > if the clients are actually still there but not doing anything, the > connection is not going to time out. This is mainly something that > helps with flaky networks, broken Windoze boxes that forget they have > connections, that sort of thing... Note that we had a problem like this because the router / firewalls between our app servers and postgresql servers had a timeout of 30 minutes, and would drop the idle connections after that time. Setting our tcp_keepalive to 5 minutes or so made the problem of the postgresql clients seeing broken connections go away.