Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to analyze interactive_timeout and wait_timeout in MySQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

A preface

This article stems from my own ignorant question, as an old DBA bird, really ashamed. As shown in the figure, the modification of wait_timeout parameters did not take effect in time, so I went to the technical support group to ask. Ps should check g.cn.

In this paper, through the test, we need to figure out two problems.

A which parameter does the inheritance relationship wait_timeout inherit at the session and global levels respectively?

B effective parameters in the session, which parameter determines the survival time of the session?

Introduction of two parameters

First of all, it shows that the two keywords that connect db through MySQL client are interactive sessions, and those that connect db through jdbc and other programs are non-interactive conversations.

Interactive_timeout: the number of seconds to wait before the MySQL server closes the interactive connection. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option in mysql_real_connect (). Default value of parameter: 28800 seconds (8 hours)

Wait_timeout: the number of seconds to wait before the MySQL server closes a non-interactive connection. When a session starts, the session wait_ timeout value is initialized based on the global wait_ timeout value or the global interactive_ timeout value, depending on the client type-- defined by the connection option CLIENT_INTERACTIVE of mysql_real_connect (). Default value of parameter: 28800 seconds (8 hours)

2.1 inheritance relationship

1) set interactive_timeout at global level separately

Set global interactive_timeout = 300

Session1 [RO] 09:34:20 > set global interactive_timeout=300

Query OK, 0 rows affected (0.00 sec)

Session1 [RO] 09:39:15 > select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.00 sec)

Session1 [RO] 09:39:21 > select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.00 sec)

Log in to another session

Session2 [RO] 09:39:35 > select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.00 sec)

Session2 [RO] 09:39:51 > select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 300 | |

+-+ +

2 rows in set (0.00 sec)

Analysis.

In interactive mode, interactive_timeout at the session and global levels inherits the value of interactive_timeout global. While the value of wait_timeout, the session level inherits interactive_timeout. Wait_timeout at the global level is not affected.

2) set interactive_timeout at session level

Session1 [RO] 09:44:07 > set session interactive_timeout=300

Query OK, 0 rows affected (0.00 sec)

Session1 [RO] 09:44:27 > select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 28800 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.00 sec)

Session1 [RO] 09:44:31 > select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.00 sec)

Open another session

Session2 [RO] 09:44:41 > select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 28800 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.01sec)

Session2 [RO] 09:44:44 > select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 28800 | |

| | WAIT_TIMEOUT | 28800 | |

+-+ +

2 rows in set (0.00 sec)

Analysis.

From the example above, wait_timeout is not affected by the value of interactive_timeout at the session level.

3) set the values of both at the same time, and they are different.

Session1 [RO] 09:46:42 >

(none) [RO] 09:46:42 > set global interactive_timeout=300

Query OK, 0 rows affected (0.00 sec)

Session1 [RO] 09:46:55 > set global wait_timeout=360

Query OK, 0 rows affected (0.00 sec)

Open another session

Session2 [RO] 09:47:20 > select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 300 | |

+-+ +

2 rows in set (0.00 sec)

Session2 [RO] 09:47:22 > select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 300 | |

| | WAIT_TIMEOUT | 360 | |

+-+ +

2 rows in set (0.00 sec)

Analysis.

It can be concluded from case 12 that wait_timeout at session level inherits the value of interactive_timeout at global level and session at global level is not affected. Modify the value of interactive_timeout without changing the value of wait_timeout

The result is invalid. There will be the situation I encountered in the preface.

2.2 effective parameters

Use an example to detect which parameters affect the session? The verification method starts another session by setting the global timeout time (note that the two times are different)

Session1 [RO] 10:20:56 > set global interactive_timeout=20

Query OK, 0 rows affected (0.00 sec)

Session1 [RO] 10:23:32 > set global wait_timeout=10

Query OK, 0 rows affected (0.00 sec)

Session 2 makes a query

Mysql > select sleep (5)

+-+

| | sleep (5) | |

+-+

| | 0 |

+-+

1 row in set (5.01 sec)

Then view the show processlist in session1

Analysis.

Interactive timeout time is affected by interactive_timeout at the global level.

2) non-interactive mode

The current test did not live up to expectations, and the test model is set up as follows

Mysql > select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 35 | |

| | WAIT_TIMEOUT | 35 | |

+-+ +

2 rows in set (0.00 sec)

Mysql > select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout')

+-+ +

| | variable_name | variable_value |

+-+ +

| | INTERACTIVE_TIMEOUT | 35 | |

| | WAIT_TIMEOUT | 25 | |

+-+ +

2 rows in set (0.00 sec)

Simulate non-interactive data on the python command line to access the database and view the database timeout parameters. At the same time, execute show processlist in the database to see how long the connection to python will be closed.

View parameters at the session level

Looking at the show processlist, the session connecting to the database through the python program waited for 25 seconds and was interrupted.

Analysis.

1 the timeout parameters obtained through the python command line are not consistent with those obtained interactively. The value of wait_timeout at the session level obtained from the interactive command line is 35, and the value obtained by using non-interactive commands is 25, indicating that wait_timeout inherits the global wait_timeout.

2 when the idle time of the session exceeds the wait_timeout in interactive mode, it will be disconnected immediately.

3) thinking questions

Session1 connects to db through non-interactive commands, and the global wait_timeout value is 28800. Session 2 modifies the global wait_timeout to 30s. Will the session of the question session1 be affected?

Three summaries

1 timeout only has an impact on idle sessions.

2 wait_timeout at session level inherits the value of interactive_timeout at global level. Global-level session is not affected by interactive_timeout.

(3) the timeout time of interactive sessions is affected by interactive_timeout at the global level. So to modify the timeout in non-interactive mode, you must also modify the value of interactive_timeout.

4 in non-interactive mode, the wait_timeout parameter inherits wait_timeout at the global level.

The above is how to analyze interactive_timeout and wait_timeout in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.