The relationship between interactive_timeout and wait_timeout
Interactive_timeout = 28800
Wait_timeout = 28800
# both parameters default to 28800s, that is, 8 hours
Interactive_timeout refers to the number of seconds that mysql has to wait before closing an interactive connection
Wait_timeout refers to the number of seconds that mysql has to wait before closing a non-interactive connection
Connecting to the database through the mysql client is an interactive connection, while connecting to the database through jdbc is a non-interactive connection.
(1) the modified interactive_timeout=10,wait_timeout at session level remains unchanged by default.
Mysql > set session interactive_timeout=10
Mysql > show variables like'% timeout%'
| | interactive_timeout | 10 | |
| | wait_timeout | 28800 |
After waiting for 10 seconds, this session connection is not disconnected.
(2) the session level modification wait_timeout = 10J interactivetimeout remains the same by default.
Mysql > set session wait_timeout=10
Mysql > show variables like'% timeout%'
| | interactive_timeout | 28800 | |
| | wait_timeout | 10 |
After waiting for 10 seconds, the session is disconnected, enter any command to report an error, and then reconnect, but at this time the connection id number has changed, and the wait_timeout session setting is invalid, returning to the default value.
Mysql > show processlist
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: * * NONE * *
(3) the modified interactive_timeout=10,wait_timeout at global level remains unchanged by default.
Mysql > set global interactive_timeout=10
Mysql > quit
[root@Darren1] # mysql-uroot-p147258
Mysql > show variables like'% timeout%'
| | interactive_timeout | 10 | |
| | wait_timeout | 10 |
After waiting for 10 seconds, the session is disconnected, randomly enter a command to report an error, and then reconnect, but the connection id number has changed.
Mysql > show processlist
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 9
Current database: * * NONE * *
(4) the modified wait_timeout=10,interactive_timeout at global level remains unchanged by default.
Mysql > set wait_timeout=10
Mysql > quit
[root@Darren1] # mysql-uroot-p147258
Mysql > show variables like'% timeout%'
| | interactive_timeout | 28800 | |
| | wait_timeout | 28800 |
The setting is invalid.
Summary:
1. It is the wait_timeout parameter that controls the maximum idle time of the connection.
two。 For interactive connections, similar to mysql client connections, the value of wait_timeout is inherited from the server-side global variable interactive_timeout.
For non-interactive connections, similar to jdbc connections, the value of wait_timeout is inherited from the server-side global variable wait_timeout (not demonstrated).
3. The idle time of a connection can be determined by the time of the Sleep status in the show processlist output
For example, session 19 is the id number of the local show processlist, and id18 is another idle session. You can see that this session has been idle for 61s.
Mysql > show processlist
+-+ +
| | Id | User | Host | db | Command | Time | State | Info |
+-+ +
| | 18 | root | localhost | NULL | Sleep | 61 | | NULL |
| | 19 | root | localhost | NULL | Query | 0 | starting | show processlist | |
+-+ +
4. According to the experience of the production library, set the two parameters the same, both for 5 minutes:
Interactive_timeout = 300
Wait_timeout = 300
