PHP MySQL - When Is the Best Time to Disconnect from the Database

PHP mySQL - When is the best time to disconnect from the database?

As far as I know, unless you are using persistent connections, your MySQL connection will be closed at the end of the page execution.

Therefore, you calling disconnect will add nothing and because you do the lazy connection, may cause a second connection to be created if you or another developer makes a mistake and disconnects at the wrong time.

Given that, I would just allow my connection to close automatically for me. Your pages should be executing quickly, therefore holding the connection for that small amount of time shouldn't cause any problems.

When should I close a database connection in PHP?

Never create a new connection for every query; You don't even have to close it manually.

Just create the connection at the beginning of you page

have a look at: How do you efficiently connect to mysql in php without reconnecting on every query

you can use this:

public function __destruct()
{
   mysql_close($this->connection);
}

it will get called when the page is closed

Is closing the mysql connection important?

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.

How often should I close database connections?

If you have connection pooling on (http://en.wikipedia.org/wiki/Connection_pool) its ok to be grabbing a new connection when you need it. HOWEVER, I'd say to be in the habit of treating any resource as "limited" and if you open the db handle keep it around for as long as possible.

should I close mysql_connection after every query in php cli?

In my experience there is no signifficat difference in resources if you use one connection for one pass of the script or connect+disconnect everytime you need mysql. The "only" difference is in the number of lines of PHP file.

I use function to connect and disconnect:

function mysql_start($mysql_host, $mysql_user, $mysql_pass){
    mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die ('Error #10020_1!');
    mysql_query("SET CHARACTER SET utf8");
    mysql_query("SET NAMES utf8");
}

function mysql_stop(){
    mysql_close();
}

Then I just call the function connect in the beginning of the file and stop at the end..

Also, just to make thing clean I don't use "mysql_select_db" function. I always select the database in FROM part of SQL query like:

SELECT
    A.something AS something,
    B.something_else AS something_else,
    ...
FROM database_name.table_name AS A, other_database_name.other_table_name AS B 
WHERE 
    ....

But to answer your question - after some testing in my environment and in my type of scripts of data handeling there was no difference in resource allocation or in speed..

Why do we have to close the MySQL database after a query command?

1. Yes, you can have multiple database connections. You are not opening a database, you are opening a database connection. The database is 'open' (i.e. running) all of the time, generally speaking, whether you are connected to it or not.
2. Depends... if you only have one open connection on a page, then you don't need to close it because it will automatically close when PHP is done. If you have many, then you could potentially make the database server slower, or make the database server run out of available connections (it can only have a certain number of connections open at the same time). That said, most modern database servers can handle hundreds of concurrent connections.
3. Optional, but recommended. It's not a big deal for small-medium projects (i.e. if you have less than 100 concurrent visitors at any given time, you probably won't have any issues regardless). Since you have many thousand visitors per minute, you should actively close the database connection as soon as you are done with it, to free it up as soon as possible.

Should I keep reconnecting to mysql in PHP?

You could create a single global object of your MySQL class and use that object everywhere. Then your constructor would only be called once.

Or you could create new objects of your MySQL class everywhere. mysql_connect doesn't open new connections if there already is one open:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

Use of closing database connection in php

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.

---

Related Topics