Is Closing the MySQL Connection Important

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 important is it close an mysql connection in a website and why?

Every database engine has a limit on a maximum number of simultaneous connections. So if you don't close the connection, mysql can run out of available connections (default max_connections is 100). In addition, each connection you hold consumes server's resources (memory, a thread to listen, might be open filehandles).

CAVEAT

This does NOT hold true if the only things opening connection are web apps from ONE server and they use pooled connections. In such a case, you don't risk opening more and more new connections (since every time your app needs a new one, it picks the ones available from the pool); and closing and re-opening the pool's connections just wastes resources.

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

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

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.

Do I really need to do mysql_close()

In most cases calling mysql_close will not make any difference, performance-wise. But it's always good practice to close out resources (file handles, open sockets, database connections, etc.) that your program is no longer using.

This is especially true if you're doing something that may potentially take a few seconds - for instance, reading and parsing data from a REST API. Since the API call is going over the line, negative network conditions can cause your script to block for several seconds. In this case, the appropriate time to open the database connection is after the REST call is completed and parsed.

To sum up my answer, the two big rules are:

1. Only allocate resources (file handles, sockets, database connections, etc.) when your program is ready to use them.
2. Free up resources immediately after your program is done with them.

What happens if MySQL connections continually aren't closed on PHP pages?

From: http://us3.php.net/manual/en/mysqli.close.php

"Open connections (and similar resources) are automatically destroyed at the end of script execution. However, you should still close or free all connections, result sets and statement handles as soon as they are no longer required. This will help return resources to PHP and MySQL faster."

---

Related Topics