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?

Signal

See this question too #336578 – Outfitter

I guess the same applies to ftp_close? – Resoluble

Is closing the mysql connection important? yes , it is like destructor in c++ – Tannie

Answer 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.

Rebane

I guess the same applies to ftp_close? – Resoluble

Does that mean that if user closes the page (for example) early (or there is some error) the connection might stay opened? – Maestricht

@Toni Michel Caubet "The link to the server will be closed as soon as the execution of the script ends." When the user close a page early it is no matter for PHP server. It runs script as always and end script as always. Only user won't see result. – Toni

Answer 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.

Gurtner

Well, considering that even the manual page for mysql_close says "Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution." I wouldn't really consider it bad practice not to close the connection. – Plebiscite

"I don't see why you wouldn't want to close it." You wouldn't want to close it if there's any chance at all that you might need it again. – Chutzpah

if U use ajax then if the connection closed then every time U try to use ajax the connection will opened and closed again and this will be worth than leave the connection open to the server and the client don't care about connection open or not he care that the website is speed – Settle

it indeed can be crucial!!! if the server load is high and you have a lot of unclosed connections as said by someone else farther up that this can lead to an unresponsive server, so I'd call it pretty much crucial! – Predestinarian

Better to close. Easy and problemless. – Shani

Answer 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.

Kalmia

He's talking about PHP, which isn't persistent, so that doesn't apply. – Caudal

Answer 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.

Tripalmitin

Answer 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).

Aplite