MySQL long connection and short connection problem turn

What is a long connection?

In fact, a long connection is relative to the usual short connection, that is, long time to maintain the client and the server connection state.

The usual short connection operation steps are:

Connect-"data transfer-" close the connection ;

A long connection is usually:

Connect-"data transmission-" Stay connected-"data transmission-" Stay connected-"...-" close the connection ;

This requires a long connection in the absence of data communication, the timing of sending packets to maintain the status of the connection, the short connection in the absence of data transmission directly off the line

When to use long connections, short connections?

Long connections are primarily used for frequent communication between a small number of clients and the server, because at this time the socket error occurs frequently with short connections, and the frequent creation of socket connections is a waste of resources.

However, for the service side, long connections also consume a certain amount of resources, requiring specialized threads (which can be managed under Unix) to maintain the connection state.

In summary, the choice of long connections and short connections is subject to availability.

First, if you use a long connection and do not perform any operations on the database for a long time, after the timeout value, MySQL server closes the connection, and the client executes the query with an error similar to "MySQL server has gone away".

After using Mysql_real_connect to connect to the database, use Mysql_options (&mysql, Mysql_opt_reconnect, ...) to set it to auto-reconnect. This way, when the MySQL connection is lost, you can use mysql_ping to automatically reconnect the database. If it is before MySQL 5.1.6, then the Mysql_options (&mysql, Mysql_opt_reconnect, ...) should be executed after each execution of Real_connect, if it is MySQL 5.1.6+, It's enough to do it once before connect.

Next article:

The first method , of course, is to increase your wait-timeout value, this parameter is set in MY.CNF (under Windows step down is My.ini), my database load is slightly larger, so I set the value to 10, (the unit of this value is the second, This means that when a database connection does not have any action within 10 seconds, it will be forcibly closed, I am not using permanent link (mysql_pconnect), with Mysql_connect, About this wait-timeout effect you can see in the MySQL process list (show processlist), you can set this wait-timeout to larger, such as 300 seconds, hehe, generally speaking 300 seconds enough to use, In fact you can also not set, MySQL default is 8 hours. The situation is determined by your server and site.

The second method :

This is also my personal view of the best method, that is, check the status of MySQL link, so that it relink.

It may be known that there is a mysql_ping such a function, in a lot of information that the Mysql_ping API will check whether the database is linked, if it is disconnected will try to reconnect, but in my test process found that the fact is not so, is conditional, Must be passed mysql_options this C API to pass the relevant parameters, so that MySQL has the option to disconnect auto-link (mysql default is not automatically connected), but I test found in PHP's MySQL API does not carry this function, you re-edit the MySQL bar, hehe. But mysql_ping this function is finally able to use, but in which there is a small operation skills:

This is one of the functions in the middle of my database operations class

function ping () {

if (!mysql_ping ($this->link)) {

Mysql_close ($this->link); Note: Be sure to perform a database shutdown first, which is the key

$this->connect ($this->t_dbhost, $this->t_dbuser, $this->t_dbpw, $this->t_dbname, $this->t_ Pconnect);
}

}

The code that I need to call this function might be like this

for ($i =0; $i <10;i++) {

$str =file_get_contents (' http://www.aol.com ');

}

$db->ping (); After the previous page crawl, or cause the database connection to close, check and reconnect

$db->query (' SELECT * from table ');

Ping () This function first detects whether the data connection is normal, if it is closed, the entire MySQL instance of the current script is closed, and then reconnected.

After this processing, it is very effective to solve the problem of MySQL server has gone away, and it will not cause additional overhead to the system.

MySQL long connection and short connection problem turn