MySQL persistent connections and short connections

What is persistent connection?

In fact, persistent connections are relative to common short connections, that is, to maintain the connection between the client and the server for a long time.

The common short connection procedure is as follows:

Connection-data transmission-close connection;

The persistent connection is usually:

Connection-data transmission-keep connection... -Close connection;

This requires that long connections send data packets regularly when there is no data communication to maintain the connection status. Short connections can be closed directly when there is no data transmission.

When can I use persistent connections or short connections?

Persistent connections are mainly used inMinorityClient and serverFrequentCommunication, because in this case, if a short connection is used for frequent communication, a socket error will often occur, and frequent creation of socket connections is also a waste of resources.

However, for the server, persistent connections consume certain resources and require dedicated threads (process management can be used in UNIX) to maintain the connection status.

In short, the choice of persistent connection and short connection depends on the situation.

 

First, if a persistent connection is used and no operations are performed on the database for a long time, the MySQL server closes the connection after the timeout value, when the client executes the query, it will get 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 automatically reconnect. In this way, when the MySQL connection is lost, you can use mysql_ping to automatically reconnect to the database. If it is before MySQL 5.1.6, execute mysql_options (& MySQL, mysql_opt_reconnect ,... ), If it is MySQL 5.1.6 +, it is enough to execute one operation before connect.

 

View MySQL connections

Mysqladmin-uroot-P processlist

In actual tests, I found that when mysql_opt_reconnect is set to 1 and processlist is checked after timeout, The automatically established connection is not in the list, but the connection is actually established and used.

 

In the default settings of MySQL, if a database is not used for more than 8 hours (idle for 8 hours), the server will disconnect this connection, subsequent query operations on the connection will fail. This problem is described in many ways on the network. Corresponding solutions are also provided. I would like to give some comments here.

Solution 1: modify the configuration parameters of the MySQL server

The principle is very simple. The default setting of MySQL is to disconnect the database after it is not used for more than 8 hours. If we change this time to a larger value, the connection timeout takes a longer time, which means it is not easy to time out. The modification method provided on the network is generally to modify/etc/My. CNF. Add a line in this file wait_timeout = the timeout time you need to set. In fact, there is a simple method to modify this parameter:

First, log on to MYSQL as a Super User. Be sure to be a Super User. Otherwise, you will be prompted not to modify the permission. Enter

Show global variables like 'wait _ timeout ';

Press enter to display the current Timeout:

+ --------------- + ------- +

| Variable_name | value |

+ --------------- + ------- +

| Wait_timeout | 28800 |

+ --------------- + ------- +

1 row in SET (0.00 Sec)

The above shows the default timeout time, that is, 8 hours (in seconds ). Set this parameter again. For example, to set the timeout time to 10 hours, enter:

Set global wait_timeout = 36000;

Press enter to execute and display:

Query OK, 0 rows affected (0.00 Sec)

The setting is successful. You can use show global variables like 'wait _ timeout' to verify the setting.

This method is intuitive and the set parameters take effect immediately. However, If/etc/My. CNF is not configured, after the service is restarted, the global variable reads the new variable value from/etc/My. CNF.

 

Below is an exampleCode:

If (! Mysql_real_connect (& logdb, my_hostname, my_user, my_password, my_dbname, my_port, my_sock, 0) {
ast_log (log_error, "failed to connect to MySQL database % s on % S. \ n ", my_dbname, my_hostname);
use_mysql = 0;
}else {
char value = 1;
mysql_options (& logdb, mysql_opt_reconnect, (char *) & value);
use_mysql = 1;
}