Re: Have you heard about a MySQL connection leak?

Jerry Stuckle wrote:
The Natural Philosopher wrote:
Jerry Stuckle wrote:
The Natural Philosopher wrote:
Jerry Stuckle wrote:
The Natural Philosopher wrote:
Jerry Stuckle wrote:
The Natural Philosopher wrote:
laredotornado@xxxxxxxxxxx wrote:
On Oct 5, 10:04 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
laredotorn...@xxxxxxxxxxx wrote:
Hi,
We're using PHP 5 and MySQL 5. Earlier today, we were seeing these
errors ...
Warning: mysql_connect() [function.mysql-connect]: Too many
connections in /mnt/stor1-wc1-dfw1/379838/www.ourdomain.com/web/
content/db.php on line 19
Can't connect to MySQL: 'Too many connections'
Our hosting company tells us we have max_connections = 500. We don't
have an immense amount of traffic, so I'm trying to figure out how
this could happen. The hosting company said our code has a
"connection leak". How can I figure out if that's true? Below is how
we connect to the db (script included on every page) ...
$db_hostname = "server.com";
$db_socket = "";
$mysql_host = $db_hostname;
if (!empty($db_socket))
$mysql_host .= ":$db_socket";
$db_name = "dbname";
$db_user = "user";
$db_password = "password";
// Connect and select the appropriate db.
mysql_connect($mysql_host, $db_user, $db_password) or die("Can't
connect to MySQL: '" . mysql_error() . "'");
mysql_select_db($db_name) or die("Failed to select db: '" . mysql_error
() . "'");
Any advice is appreciated. We never call "mysql_close()", so maybe
that's the problem. Thanks , - Dave
Definitely a potential problem. If you don't call mysql_close(), PHP
will call it eventually, but not until the garbage collector runs. In a
busy system, this could be some time.

It's ALWAYS a good programming practice in ANY language to release any
resources you allocate as soon as reasonable after you are finished with
them. This doesn't change with scripting languages like PHP.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@xxxxxxxxxxxxx
==================- Hide quoted text -

- Show quoted text -

Thanks guys. I will definitely work on closing out the connections.

But a follow-up quesiton. On the PHP site (http://us.php.net/
mysql_close), they say, "Using mysql_close() isn't usually necessary,
as non-persistent open links are automatically closed at the end of
the script's execution." Jerry, how does this jibe with what you
mentioned above about garbage collection?


It means jerry is talking crap as usual.

- Dave

ROFLMAO! From the known troll who knows even less about programming than he does engineering.

Go back to your ditches, TNP. They need to be dug deeper.

Oh dear. and there was me thinking you had at least read the manual.

Anyway, the reason a TCP socket connection stays open, is because neither end has closed it.

It has NOTHING to do with garbage collection, which is a term applied to memory allocation.

Normally a PHP script closes all open connections when it exits. I would suspect that's part of the PHP close down priocess.

Assuming its e.g. within an apache process, the apache process that is forked, will also have any connections it makes closed when it exits, but persistent connections to and from the browser may keep that alive long after the browser person has ceased to click on links within the site. Only when he clicks away will the connection be junked, and, if he simply cease to use the browser, that's a possible way to leave a dangling session, which will show up as a forked apache process on the server. Ultimately these WILL time out and go, but it could be a fair time. HOWEVER the php scripts will have finished, so the SQLD connections should in any case have closed.

the final possibility is that you are handing out large amounts of data. And these scripts are therefore staying up a long time, trying to deliver it. Possibly several minutes if either the data is big, or the user gets fed up waiting for the page to fill (consider using compressed HTML).

This is the single case where explicitly closing the link to the database will improve matters. Once you have done the enquiry, and are prepping the data to hand it down, close the link to the database with a mysql_close();

Its good practice to close the sql connection, and plays nice with other users, but normally its not NECESSARY. Because php does it for you.

Another possible area to investigate is whether due to inefficient queries, or possibly table locking, enquiries are being held up and taking a long time to complete.






You really have no idea what you're talking about. But you put up a good front!

Process cleanup is part of garbage collection.

Closing a socket is nothing to do with process cleanup.Or garbage collection.

Closing a TCP socket is a simple matter of sending a FIN packet to it.
http://en.wikipedia.org/wiki/Transmission_Control_Protocol#Connection_termination

-----------------------------------------------------------------------
Connection termination
+++++++++++++++++++++++
The connection termination phase uses, at most, a four-way handshake, with each side of the connection terminating independently. When an endpoint wishes to stop its half of the connection, it transmits a FIN packet, which the other end acknowledges with an ACK. Therefore, a typical tear-down requires a pair of FIN and ACK segments from each TCP endpoint.

A connection can be "half-open", in which case one side has terminated its end, but the other has not. The side that has terminated can no longer send any data into or receive any data from the connection, but the other side can (but generally if it tries, this should result in no acknowledgment and therefore a timeout, or else result in a positive RST, and either way thereby the destruction of the half-open socket).

It is also possible to terminate the connection by a 3-way handshake, when host A sends a FIN and host B replies with a FIN & ACK (merely combines 2 steps into one) and host A replies with an ACK.[13] This is perhaps the most common method.

It is possible for both hosts to send FINs simultaneously then both just have to ACK. This could possibly be considered a 2-way handshake since the FIN/ACK sequence is done in parallel for both directions.

Some host TCP stacks may implement a "half-duplex" close sequence, as Linux or HP-UX do. If such a host actively closes a connection but still has not read all the incoming data the stack already received from the link, this host will send a RST instead of a FIN (Section 4.2.2.13 in RFC 1122). This allows a TCP application to be sure that the remote application has read all the data the former sent - waiting the FIN from the remote side when it will actively close the connection. Unfortunately, the remote TCP stack cannot distinguish between a Connection Aborting RST and this Data Loss RST - both will cause the remote stack to throw away all the data it received, but the application still didn't read.

---------------------------
The script does not necessarily terminate as soon as it ends; it is just scheduled for cleanup as time permits. This allows other processes which are performing work to continue processing, rather than wait for the old script to get cleaned up, improving performance.


Oh dear oh dear.

All PHP has to do is to send a close to any open sockets, and free up any memory its taken from the operating system.

And as I discovered, actually write disk data and close any open files it has.

Since its ceasing to exist as a process, it wont need to do any garbage collection..garbage collection as you don't seem to understand, is what you need to do to reallocate more blocks for reuse. It has no meaning if the memory is not to be re-used by the collecting process..


That will be done by the underlying OS when it gets a free() call. Not by PHP. Nothing in PHP will make any difference to that process.

And the actual PROCESS is not PHP anyway, its an apache process typically. That only needs cleaning up when THAT exits, and THAT will be when the socket connection to the browser closes.

The ONLY way your statement could *possibly* make sense is if PHP is so badly written that it doesn't close its sockets on exiting a script, and leaves them open forever.

Now I have seen some bugs in PHP, but that one would not last 5 minutes before the entire user base would be screaming at the developers to fix it.

Its conceivably possible that some other bug in PHP could cause it to crash and exit given a particular script. But that would show up somewhere in the web server logs..




You are wrong on many counts. For instance, you're assuming it is running under a separate process. Depending on your web server and setup, the server may only start a new thread - not a new process.


Nit picking at semantics when you have failed to adress every one ofg tyour gross errors, makes you look even stuoider Jerry.


ROFLMAO! Not nit picking - showing the world you don't even understand basic concepts such as thread vs. process.

And yes, the cleanup "only has to send a mysql_close()...". However, that may not be done immediately, as I previously stated.


Mau not? why not? teher is nothing else TO dfo, asd I said.



Because the cleanup is scheduled - not necessarily completed immediately - as I previously stated.

You are (again) way off on your understanding about how REAL programming works!


And you are way off in your understanding of how TCP networking works.,.

Not unusual is self aggrandising DB programmers IME.


Oh, I understand how TCP networking works. But you obviously don't even understand that terminating a process or thread is a system function - and has NOTHING to do with TCP networking.

No Jerry, that was YOU when you confused 'too many sockets'(a strict TCP level issue) with processes, and garbage collection.


You really know how to make yourself look stupid don't you? you are so easy to put in a position where you end up contradicting yourself, because you never ever admit when you are wrong.

Its scarcely worth the slender amusement it brings. Its more challenging cheating at poker with a 2 year old.
..


You've proven you're not the engineer you claim to be, and once again, you've proven you have no concept of even basic programming concepts.


I'll leave that in for everyone to see Jerry. Calling black white when you lose an argument you should never have started, is about as low as it gets really.
.

Relevant Pages

  • Re: Have you heard about a MySQL connection leak?
    ... PHP ... Anyway, the reason a TCP socket connection stays open, is because neither end has closed it. ... When an endpoint wishes to stop its half of the connection, it transmits a FIN packet, which the other end acknowledges with an ACK. ...
    (comp.lang.php)
  • Re: Have you heard about a MySQL connection leak?
    ... This doesn't change with scripting languages like PHP. ... Anyway, the reason a TCP socket connection stays open, is because neither end has closed it. ... Normally a PHP script closes all open connections when it exits. ... When an endpoint wishes to stop its half of the connection, it transmits a FIN packet, which the other end acknowledges with an ACK. ...
    (comp.lang.php)
  • Re: Have you heard about a MySQL connection leak?
    ... PHP ... Anyway, the reason a TCP socket connection stays open, is because neither end has closed it. ... Normally a PHP script closes all open connections when it exits. ... When an endpoint wishes to stop its half of the connection, it transmits a FIN packet, which the other end acknowledges with an ACK. ...
    (comp.lang.php)
  • Re: Have you heard about a MySQL connection leak?
    ... PHP ... Anyway, the reason a TCP socket connection stays open, is because neither end has closed it. ... Normally a PHP script closes all open connections when it exits. ... When an endpoint wishes to stop its half of the connection, it transmits a FIN packet, which the other end acknowledges with an ACK. ...
    (comp.lang.php)
  • Re: Post with redirect?
    ... So far you haven't shown us how do to it in PHP. ... You are backpeddling, troll. ... And you're also afraid we will find out you're not a programmer with 25 years of experience. ... the only thing jerry got right is that it would be a lot of work. ...
    (comp.lang.php)