How to handle too many sleep threads in MySQL

First say the specific method:

Operate in MySQL first

Set global wait_timeout = 60;
Set global interactive_timeout = 60;

then modify (/etc/my.cnf) in the config file:
[Mysqld]
Interactive_timeout = #<== This parameter is set wait_timeout automatically.
Wait_timeout = 120

Wait_timeout--refers to the number of seconds MySQL waits before closing a non-interactive connection

Interactive_time--refers to the number of seconds MySQL waits before closing an interactive connection (interactive connection such as a connection in the MySQL GUI tool)

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

Wait_timeout and Interactive_timeout summary

(1) Interactive_timeout:
Parameter meaning: The number of seconds that the server waits for activity before closing an interactive connection. The interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect ().
Parameter default value: 28,800 seconds (8 hours)

(2) Wait_timeout:
Parameter meaning: The number of seconds that the server waits for activity before closing a non-interactive connection.
When a thread starts, the session Wait_timeout value is initialized based on the global Wait_timeout value or global interactive_timeout value, depending on the client type (the connection option for the Mysql_real_connect () Client_ Interactive definition).

Parameter default value: 28,800 seconds (8 hours)

Question 1: Why do I have to set interactive_timeout,wait_timeout settings at the same time to take effect?
A: Do not set interactive_timeout,wait_timeout will also take effect.
The value of the question 2:interactive if the setting is different from the wait_timeout, why does interactive_timeout overwrite wait_timeout?
A: In interactive mode (client_interactive), interactive_timeout only takes effect, non-interactive mode, does not take effect.

Issue 3: In MySQL optimization, because interactive_timeout determines the length of time the interaction is connected, Wait_timeout determines the length of the non-interactive connection. If the Mysql_real_connect () last parameter client_flag is not set to client_interactive when making a connection configuration, the value of interactive_timeout is not overwritten wait_timeout ?
A: You can try it on the experiment.

4: In order to reduce the number of long connections, is it possible to set the Interactive_timeout value to a larger size while setting the optimization, while the Wait_timeout value is set smaller? But the description of question 2 doesn't seem to allow this ...

Answer: As described in 2, in interactive mode, Interactive_timeout replaces Wait_timeout. This way, if some clients are interactive mode, connect to MySQL server. Then the client timeout is subject to interactive_timeout. If some clients are in non-interactive mode, long connect to MySQL server. Then the client timeout is subject to wait_timeout. (Whether the connection is interactive mode, determined by the client)

How to handle too many sleep threads in MySQL