MySQL timeout mechanism

Each time MySQL creates a socket connection (connect), the socket takes up a certain amount of memory. Even when you close the (close) connection, it is not really closed, but it is in the sleep state.

The next time you connect, you can quickly start a socket that is currently asleep. But too many sockets take up a lot of memory, and to solve this problem, MySQL has a timeout mechanism.

You can use this statement to see the time-out length of the current setting:

Show global variables like ' wait_timeout ';

The results are as follows:

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Wait_timeout | 28800 |
+---------------+-------+
1 row in Set (0.00 sec)

The default is 28,800 seconds, which means 8 hours.

The function of the Wait_timeout parameter: When an idle connection exceeds 8 hours, the connection is disconnected.

But sometimes it takes a long time to open the MySQL connection, and you need to change the value of this parameter. Select the appropriate parameter values according to the machine's hardware.

There are two ways to change the value of this parameter:

1. Change the value of this parameter through the statement. This method does not require a restart of MySQL, but will disconnect all current connections and then reconnect.

Statement: Set Global wait_timeout = 100; Execution Result:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:3
Current database: * * * NONE * * *

Query OK, 0 rows affected (1.13 sec)

The last line indicates that the change was successful.

Note: This method is local in nature. The next time you restart the MySQL server, execute show global variables like ' wait_timeout ', when it is still the value before the modification.

2. By modifying the configuration file

In the MySQL installation directory, there is a Mysql.ini file

Add the following settings:

[MYSQLLD]

Wait_timeout = 100

Interactive_timeout = 100

Then restart the MySQL service and the changes will take effect.

Note: The meaning of this parameter:

Wait_timeout:mysql number of seconds to wait for activity before the server closes a non-interactive connection

The number of seconds the Interactive_timeout:mysql server waits for activity before closing an interactive connection

MySQL timeout mechanism