Use of closing database connection in php

Question

I was always in assumption that it is always a good practice to close database connection, regardless of database/ORM, like mysql_close(), Propel::close() etc.

With reference to one of my other question and some other research on Internet, I came to know a surprising face that most people recommends it doesn't really matter if you close connection as connection always gets closed after the request.

However I'm finding those answers little difficult to digest. Reason is, why all DB lib, ORM provide close method? If it is there, in every ORM/lib, there must be some good use of it.

Can someone please shed some light on under what circumstances, we should use close method to close DB connection? & if these methods are not useful at all, why they are present there in all db libs/ORM?

EDIT

My Conclusion

It was a good discussion between Bondye and Fluffeh and it cleared my doubts about use of connection closing. Thanks to both of them.

• If your script is expected to last of less than 100 ms, dont bother closing connection.
• BUT: if script is expected to last longer and there is some time between last DB operation and close of script, free connection for others by calling *close().

It is really very difficult for me to accept one answer as both answer are correct on its place. Just accepting answer with all comments so that it remain on top. But +1 to both correct answers.

Yonatan

Answer 1

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

Freeing resources

Thanks to the reference-counting system introduced with PHP 4's Zend Engine, a resource with no more references to it is detected automatically, and it is freed by the garbage collector. For this reason, it is rarely necessary to free the memory manually.

Hope this helps you more.

(source)

edit:

The purpose of mysql_close() is also to save computer resources, but another key reason for using it is because there is a limited number of connections that a MySQL server can accept, and if you have several clients holding connections open for no reason then the server may well need to turn away other, waiting clients. Naturally this is a bad thing, so, as with mysql_free_result(), it is good to call mysql_close() if you think there will be some time between your last database use and your script ending.

Frederic

What if it is referenced - but conditions (such as an if statement that isn't determined yet) cause it to never get used? What if that check is still half a second of execution time away? Or five seconds away even? Not saying you are wrong, but it can be good to utilize all the tools given to you to their best. – Kokoruda

#881385 & #336578 – Frederic

From the first (+16) answer: 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. This is exactly what I am saying mate. – Kokoruda

Do you want to close the database or de-allocate memory that was used to store the query results? tuxradar.com/practicalphp/9/4/3 – Frederic

The OP was asking about connections, I just used an analogy of unsetting a variable as needed. – Kokoruda

Answer 2

It is always good practice to close a database connection when you no longer need it. Even if it gets closed automatically after the script ends - that might be another second or a number of split seconds later. If you no longer need it, one user hitting a page and wasting the database connection for half a second won't make a difference - but twenty doing it at once is suddenly 10 seconds of open connection - and that does make a difference.

At the same time, re-using a connection can be a good practise - making and opening the connection normally takes at least a few milliseconds - and if you are for example inserting a few hundred thousand rows, that few milliseconds each time adds up really fast.

In a way, it is no different to setting a variable to NULL or unsetting it. You don't have to do it, but clean elegant code and resource management is always a good thing.

Kokoruda

Well I never gave lot of thought on how things are happening internally in PHP/Zend but I guess it is now necessary for my projects too. Can someone shed some light on what is more expensive among (two connection close operation; manually & by zend) and (let connection open for few more milliseconds/seconds). I guess, but not sure, its about CPU(1 extra close operation) vs RAM(one extra open connection-object)? – Yonatan

@KapilSharma I haven't run tests except for a few really obvious cases in some code I was writing, but at the same time it will likely be different for each particular server and the resources it has. You could run a few tests using microtime to see how long things take, but generally, if you aren't going to call any more mysql, close it. If your code takes a while to run between statements, it might be an opportunity to close - but best to check. – Kokoruda

Answer 3

Database connections are not unlimited. Commercial database software, especially, often have licenses that limit the number of simultaneous connections to a relatively small number. In such a situation, you definitely want to close the connection when your script is no longer actively using. While PHP does automatically close database connection when a script terminates, it doesn't do so until the visitor has finished downloading the page. If his connection is slow (dial-up or mobile), that could take ten, twenty seconds for all you know.

Brandiebrandise

Thanks, that is new point to me that script completion is considered as download of all contents. – Yonatan

Answer 4

Well developed ORM's like Doctrine and Propel are good at closing MySQL connections. But if you are using straight php, I've seen a lot of database problems tracked back to unclosed connections. It's wise to close all db connections at the end of each script.

Allpurpose