Long connection with MySQL

There's a material that foggy me. Now use your own words to summarize, write words, can deepen their understanding. Also in the process of writing to help themselves to find the understanding of defects, continue to check the data verification.

Disadvantages of short Links: Create a connection, and when the program finishes, it will automatically break the link to the MySQLServer. So how many times PHP executes, how many times this creation and release process. Frequent creation and release of connections, CPU-intensive resources are more expensive.

A long connection avoids the overhead of creating a connection per request, saving time and IO consumption.

Long connections are improved performance. However, there are some details that need to be addressed, that is, when MySQL discovers a link for a long time without executing a query request, it automatically disconnects the connection.

How long does it break off, there is a timeout setting time. Via sql: "Show global variables like '%timeout ';" View.

My.conf in the

wait_timeout=2880000
Interactive_timeout = 2880000

When the link has expired, still go to perform query operation, an obvious manifestation is prompt: MySQL server has gone away

Heuristic:MySQL server has gone away This information is prompted by the MySQL servers? Or is PHP's MySQL extension prompt out?

It is judged that the application server is definitely quoted (PHP). Think about it, if MySQL has already received a request, then there is no link. Clearly have been linked up.

Since the MySQL server is able to accept the request, then how to deal with it.

We go to Baidu Search: MySQL server has gone away. Never see the Java link MySQL in this situation. If the MySQL server is reported. Then the application should be irrelevant. So you should also be able to search for relevant information.

This is the information that PHP throws out. The PHP link is not on MySQL.

http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/

Use the mysql_ping () function to detect if the MySQL server is not a link state. Avoid the presence of MySQL server has gone away.

Use Mysql_ping () to check if the connection is broken before each query executes. If it breaks down. Re-establish the link.

The specific code is:

if (mysql_ping ()!=0) {

Link has been disconnected, need to re-establish link

$this->conn = mysql_connect ($ip, $user _name, $password);

}

Small drawback is: every time to detect the execution of mysql_ping (), the cost of resources.

One way to improve this is to decide if you want to relink based on the return error code of mysql_query ().

$res = mysql_query ($sql, $this-conn);

if ($res ===false) {

if (Mysql_errno ($this->conn) ==2006 | | Mysql_errno ($this->conn) ==2003) {

To check if the link to the MySQL server is valid

if (mysql_ping ()!=0) {

Re-establish links

}

}

}

Note:

2003 the corresponding error message is that Can ' t connect to MySQL

2006 the corresponding error message is MySQL server has gone away


Thinking: The real sense of their own implementation of the connection pool, is a long-term link with the database server link. How to set up a link. is to send a heartbeat packet on a regular basis. Communicates with the server through a heartbeat packet.

If no heartbeat packet is sent, the link will be broken by the database server. Because there is no communication link for a long time, to break off.


Ready to be perfected


MYSQL has gone away explanation:

Http://database.51cto.com/art/201105/261107.htm

Long connection with MySQL