Tutorial :Is closing the mysql connection important?



Question:

Is it crucial to close mysql connections efficiency wise, or does it automatically close after php file has run?


Solution:1

From the documentation:

Note: The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().

If your script has a fair amount of processing to perform after fetching the result and has retrieved the full result set, you definitely should close the connection. If you don't, there's a chance the MySQL server will reach it's connection limit when the web server is under heavy usage. If you can't close the MySQL connection until near the end of the script, it's cleaner though unnecessary to do so explicitly.

I'm not certain how fastcgi affects things. One page claims that a build of PHP that supports fastcgi will create persistent connections, even for mysql_connect. This contradicts the documentation in that the connection is closed when the process, rather than the script, ends. Rather than testing it, I'm going to recommend using mysql_close(). Actually, I recommend using PDO, if it's available.


Solution:2

Is it crucial? Not so much

Is it considered to be a good practice to follow? Yes.

I don't see why you wouldn't want to close it.


Solution:3

When using something like cgi, it's completely unnecessary to close your mysql connections since they close automatically at the end of script execution. When using persistent technologies like mod_perl and others, which maintain your connections between requests, then it's important to keep track of connections, global variables, etc..

Basically, for persistent data, clean up after yourself. For trivial, non-persistent data, it'll all go away when the request finishes anyway. Either way, best practice is to always close your connections.


Solution:4

Gets closed as soon as the script completes execution. Unless you've opened a persistent connection. Ideally you should release a resource (a connection here) as soon as you are done with it. Unless there is a good chance that you will be needing it again very soon in the execution.

Connection pooling or using persistent connections (if that's what you meant) is a good idea if you are behind a single database server. However if there are more servers and you are load balancing, it might hurt the distribution of work. Typically some clients run heavy queries while others run lighter ones. So if the same connection is used over n over, some servers would hit heavy load while others would be under utilized. Consider using smaller ttls and variable connection pool size.


Solution:5

Most CMSs close the MySQL connection at the end of the request, which is really meaningless, because PHP will do it anyway.

However, if you have a script where the connection is no longer needed say towards the middle of the script, and then other heavy activities take place, then it's a good idea to explicitly close the connection. This will free some resources.

Now, much has been said about the benefits of closing a connection, but nearly nothing has been said about the benefits of not closing it. Essentially, if you do not close the connection at the end of a script, then you really are saving some resources. Imagine a web application (or any application) receiving 100 pageviews/second. So, every second, you will need to invoke mysqli_close 100 times - which means that in every second, you have 100 unnecessary roundtrips to the database server to close the open connections. From a performance perspective, this is pure overhead, since PHP will check for open connections when the script is finished anyway and will close those connections, and it might be that, because everything happens so quickly, that PHP doesn't see that you have closed those connections and will try to close them again.

Note: the answer above assumes that you are not using persistent connections (persistent connections are not used in any of the major CMSs).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »