MySQL long connection and short connection issues

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.

View MySQL Connection number

Mysqladmin-uroot-p processlist

In the actual test I found that when the Mysql_opt_reconnect is set to 1 o'clock, and then the processlist is checked out after the timeout, the automatically established connection is not in the list, but the connection is actually established and used.

In MySQL's default settings, if a database connection is not used for more than 8 hours (idle for 8 hours), the server disconnects the connection and subsequent query operations on that connection will fail. The problem is described very much on the network. The corresponding solutions are also provided. I'll mention some of my own ideas here.

Workaround One: Modify the configuration parameters of the MySQL server

The simple truth is that the default setting for MySQL is to disconnect the database after it has not been used for more than 8 hours, and if we change the time to a larger value, then the connection timeout will take longer, which means it is less likely to time out. The modification method provided on the network is generally modified/ETC/MY.CNF, adding a line in this file wait_timeout= the time-out you need to set . There is actually a relatively simple way to modify this parameter:

First log in to MySQL as a superuser, note that you must be a superuser, otherwise you will be prompted not to modify permissions later. And then enter

Show global variables like ' wait_timeout ';

The current time-out is displayed after the Enter execution:

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

| variable_name | Value |

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

| Wait_timeout | 28800 |

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

1 row in Set (0.00 sec)

The default time-out, which is 8 hours (in seconds), is shown above. Now reset the parameter, for example we want to set the time-out to 10 hours, you can enter:

Set global wait_timeout=36000;

Enter execution, display:

Query OK, 0 rows Affected (0.00 sec)

Indicates a successful setup and can be re-validated using show global variables like ' wait_timeout '.

This method is more intuitive and the parameters set are immediately effective. However, if there is no configuration in/etc/my.cnf, the global variable will read the new variable value from/ETC/MY.CNF when the service is restarted.

Below is a sample code:

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;
}

MySQL long connection and short connection issues