Обсуждение: Applications that leak connections
Does anyone have any useful info about dealing with applications which
"leak" connections, eventually causing "connection limit exceeded for
non superusers?"
Obviously fixing the app is the best choice, but I have a feeling that
this is going to recur again and again since the app is poorly
maintained but still under active development (code base too large for
the developer assigned to it). The developer is likely to accidentally
cause connection leaks in the future.
This problem is also very easy to cause because doing this:
c = sqlconnect("database")
c = sqlconnect("database")
Causes TWO connections to be made, and the first connection made is now
(as far as I can see) impossible to close without closing the
application. The language (Visual Foxpro) does not garbage collect
connections and so any connection whose handle is lost still takes up
one of the slots.
On the server side, I can detect this condition using ps -A or lsof; but
I have not thought of a good solution to use when this is noticed. One
possibility is to just shut down the database and restart it when this
happens. I did some research into finding out if it was possible to
break a specific TCP connection (this is linux 2.6 kernel--platform
specific tools/commands ok). For example, could I use hping to break
the connections?
Does anyone know a safe way to shutdown just one backend (if it is idle)?
Another possibility that I can think of is per ip address connection
limits. How hard would that be?
Paul Tillotson
you are perfectly right, fixing the source of the problem is the best way to
fix this. i always make it a habit to close my connections as soon as i'm
done with it. if foxpro won't do it for you, you have to manage your own
resources, always a good practice for unmanaged applications.
i suggest you parse your code for all lines that opens the connection and
close it where it's not needed anymore.
good luck.
regards,
On Thu, 03 Feb 2005 18:27:46 -0500, Paul Tillotson wrote
> Does anyone have any useful info about dealing with applications
> which "leak" connections, eventually causing "connection limit
> exceeded for non superusers?"
>
> Obviously fixing the app is the best choice, but I have a feeling
> that this is going to recur again and again since the app is poorly
> maintained but still under active development (code base too large
> for the developer assigned to it). The developer is likely to
> accidentally cause connection leaks in the future.
>
> This problem is also very easy to cause because doing this:
>
> c = sqlconnect("database")
> c = sqlconnect("database")
>
> Causes TWO connections to be made, and the first connection made is
> now
> (as far as I can see) impossible to close without closing the
> application. The language (Visual Foxpro) does not garbage collect
> connections and so any connection whose handle is lost still takes
> up one of the slots.
>
> On the server side, I can detect this condition using ps -A or lsof;
> but I have not thought of a good solution to use when this is
> noticed. One possibility is to just shut down the database and
> restart it when this happens. I did some research into finding out
> if it was possible to break a specific TCP connection (this is linux
> 2.6 kernel--platform specific tools/commands ok). For example,
> could I use hping to break the connections?
>
> Does anyone know a safe way to shutdown just one backend (if it is
> idle)?
>
> Another possibility that I can think of is per ip address connection
> limits. How hard would that be?
>
> Paul Tillotson
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
--
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
Paul Tillotson wrote: > Does anyone know a safe way to shutdown just one backend Sending it a SIGTERM via kill(1) should be safe. -Neil
Is there any stronger medicine that's available (for instance, when a backend won't respond to SIGTERM) and has no unfortunate side effects? I just ran into this situation the other day (and made the unfortunate discovery that SIGABRT is as bad as SIGKILL as far as a postmaster is concerned). -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 4, 2005, at 1:57 AM, Neil Conway wrote: > Paul Tillotson wrote: >> Does anyone know a safe way to shutdown just one backend > > Sending it a SIGTERM via kill(1) should be safe. > > -Neil
On Fri, Feb 04, 2005 at 01:44:10PM -0600, Thomas F.O'Connell wrote: > Is there any stronger medicine that's available (for instance, when a > backend won't respond to SIGTERM) and has no unfortunate side effects? > I just ran into this situation the other day (and made the unfortunate > discovery that SIGABRT is as bad as SIGKILL as far as a postmaster is > concerned). As soon as a backend dies a unnatural death, postmaster will rightly consider that it may have corrupted the shared state. In turn postmaster will kill all its children mercilessly so they don't spread the disease. Even SIGTERM can have bad consequences if it arrives at the wrong time. (That's why a function to close a remote connection was rejected.) So, short answer: no. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La virtud es el justo medio entre dos defectos" (Aristóteles)
Which brings up a follow-up question: is it documented anywhere exactly what goes on in recovery mode? If so, I've not found it. When I've experienced this, it has seemed quicker just to stop and restart postgres than to let recovery mode complete. Is that unsafe? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 4, 2005, at 1:56 PM, Alvaro Herrera wrote: > On Fri, Feb 04, 2005 at 01:44:10PM -0600, Thomas F.O'Connell wrote: >> Is there any stronger medicine that's available (for instance, when a >> backend won't respond to SIGTERM) and has no unfortunate side effects? >> I just ran into this situation the other day (and made the unfortunate >> discovery that SIGABRT is as bad as SIGKILL as far as a postmaster is >> concerned). > > As soon as a backend dies a unnatural death, postmaster will rightly > consider that it may have corrupted the shared state. In turn > postmaster will kill all its children mercilessly so they don't spread > the disease. > > Even SIGTERM can have bad consequences if it arrives at the wrong time. > (That's why a function to close a remote connection was rejected.) > > So, short answer: no. > > -- > Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) > "La virtud es el justo medio entre dos defectos" (Aristóteles)
Correct me if I am wrong, but doesn't the postmaster notice that something killed a backend and cause all the other ones to roll back? Paul Tillotson Neil Conway wrote: > Paul Tillotson wrote: > >> Does anyone know a safe way to shutdown just one backend > > > Sending it a SIGTERM via kill(1) should be safe. > > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
>>>>> "TFO" == Thomas F O'Connell <Thomas> writes: TFO> Which brings up a follow-up question: is it documented anywhere TFO> exactly what goes on in recovery mode? If so, I've not found it. TFO> When I've experienced this, it has seemed quicker just to stop and TFO> restart postgres than to let recovery mode complete. Is that unsafe? The recovery has to happen at some point. What it is doing is bringing your DB to a known valid state based on the committed transactions, what's written to disk, and what's written to the pg_xlog files. A vacuum is probably in good order after this happens. Effectively, it does the moral equivalent of unplugging the power cord and restarting itself, without the bother of needing to reboot the whole machine :-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
My point/question is: when I've seen this before and recovery mode is taking on the order of minutes, I find that doing: pg_ctl stop -m i pg_ctl start is faster (on the order of seconds) than letting postgres finish recovery mode. So I wonder: 1. Is this safe from a data integrity point of view? 2. Why is it faster? Maybe the difference in time I've experienced is partially a result of the number of connections that come in (via PHP) during recovery mode, so recovering takes longer because the database is still in multi-user mode and receiving connections even if not fulfulling requests? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 8, 2005, at 2:57 PM, Vivek Khera wrote: > TFO> Which brings up a follow-up question: is it documented anywhere > TFO> exactly what goes on in recovery mode? If so, I've not found it. > > TFO> When I've experienced this, it has seemed quicker just to stop and > TFO> restart postgres than to let recovery mode complete. Is that > unsafe? > > The recovery has to happen at some point. What it is doing is > bringing your DB to a known valid state based on the committed > transactions, what's written to disk, and what's written to the > pg_xlog files. A vacuum is probably in good order after this happens. > > Effectively, it does the moral equivalent of unplugging the power cord > and restarting itself, without the bother of needing to reboot the > whole machine :-)