Timeout-related parameter parsing in MySQL _ MySQL

Timeout-related parameter parsing in MySQL bitsCN.com

Preface:

MySQL has two configuration items about connection timeout. They inherit from each other under certain conditions. under what circumstances will these two parameters take effect?

This article will prove the relationship between the two through some test examples.

Parameter introduction:

interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that usesCLIENT_INTERACTIVEOptionmysql_real_connect(). See alsowait_timeout.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it. before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory.

On thread startup, the sessionwait_timeoutValue is initialized from the globalwait_timeoutValue or from the globalinteractive_timeoutValue, depending on the type of client (as defined byCLIENT_INTERACTIVEConnect optionmysql_real_connect()). See alsointeractive_timeout.

CLIENT_INTERACTIVE

Permitinteractive_timeoutSeconds (insteadwait_timeoutSeconds) of inactivity before closing the connection. The client's sessionwait_timeoutVariable is set to the value of the sessioninteractive_timeoutVariable.

In short, interactive is an interactive terminal. for example, if mysql is directly executed in the shell, an interactive connection is displayed after mysql>. Mysql-e 'Select 1' directly returns results in non-interactive connections.

Part 2 test the 2.1 inheritance relationship

Q: Which global timeout will be inherited from when a timeout is connected through a Socket?

A: As shown in the following example, timeout inherits from global. interactive_timeout when logging on through socket;

mysql> set global interactive_timeout =  11111;Query OK, 0 rows affected (0.00 sec)mysql> set global wait_timeout = 22222;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| connect_timeout            | 10       || delayed_insert_timeout     | 300      || innodb_lock_wait_timeout   | 50       || innodb_rollback_on_timeout | OFF      || interactive_timeout        | 11111    || lock_wait_timeout          | 31536000 || net_read_timeout           | 30       || net_write_timeout          | 60       || slave_net_timeout          | 3600     || wait_timeout               | 22222    |+----------------------------+----------+10 rows in set (0.00 sec)mysql -uroot -ppassword -S /usr/local/mysql3310/mysql.sockWelcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 4Server version: 5.5.16-log MySQL Community Server (GPL)Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> show session variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| connect_timeout            | 10       || delayed_insert_timeout     | 300      || innodb_lock_wait_timeout   | 50       || innodb_rollback_on_timeout | OFF      || interactive_timeout        | 11111    || lock_wait_timeout          | 31536000 || net_read_timeout           | 30       || net_write_timeout          | 60       || slave_net_timeout          | 3600     || wait_timeout               | 11111    |+----------------------------+----------+10 rows in set (0.00 sec)

　　

Q: Which global timeout will timeout inherit from when using a TCP/IP client connection?

A: As shown in the following example, wait_timeout after the TCP/IP client connection still inherits from global. interactive_timeout.

mysql -uroot -ppassword -h 127.0.0.1 --port 3310Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 6Server version: 5.5.16-log MySQL Community Server (GPL)Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> show session variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| connect_timeout            | 10       || delayed_insert_timeout     | 300      || innodb_lock_wait_timeout   | 50       || innodb_rollback_on_timeout | OFF      || interactive_timeout        | 11111    || lock_wait_timeout          | 31536000 || net_read_timeout           | 30       || net_write_timeout          | 60       || slave_net_timeout          | 3600     || wait_timeout               | 11111    |+----------------------------+----------+10 rows in set (0.00 sec)

　　

2.2 Effective Relationships

Q: Is the timeout value valid for running statements?

A: The waiting time in the execution status of the SQL statement is not counted into the timeout time.

mysql> set session wait_timeout=10;Query OK, 0 rows affected (0.00 sec)mysql> set session interactive_timeout=10;Query OK, 0 rows affected (0.00 sec)mysql> select 1,sleep(20) from dual;+---+-----------+| 1 | sleep(20) |+---+-----------+| 1 |         0 |+---+-----------+1 row in set (20.00 sec)mysql> mysql> show session variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| connect_timeout            | 10       || delayed_insert_timeout     | 300      || innodb_lock_wait_timeout   | 50       || innodb_rollback_on_timeout | OFF      || interactive_timeout        | 10       || lock_wait_timeout          | 31536000 || net_read_timeout           | 30       || net_write_timeout          | 60       || slave_net_timeout          | 3600     || wait_timeout               | 10       |+----------------------------+----------+

　　

Q: How does wait_timeout interact with interacitve_timeout.

A: Only session. wait_timeout will take effect.

mysql> set session interactive_timeout=10;Query OK, 0 rows affected (0.00 sec)mysql> set session wait_timeout=20;Query OK, 0 rows affected (0.00 sec)---------------------another connection-------------------------mysql> show full processlist;+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+|  1 | system user |                 | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                  |         0 |             0 |         1 ||  2 | system user |                 | NULL | Connect | 103750 | Connecting to master                                                        | NULL                  |         0 |             0 |         1 ||  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 || 10 | root        | localhost:58946 | NULL | Sleep   |     20 |                                                                             | NULL                  |         0 |             0 |        11 |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+4 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+|  1 | system user |           | NULL | Connect | 103749 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                  |         0 |             0 |         1 ||  2 | system user |           | NULL | Connect | 103750 | Connecting to master                                                        | NULL                  |         0 |             0 |         1 ||  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+3 rows in set (0.00 sec)

　　

Q: Which of the global timeout and session timeout functions.

A: Only session timeout works.

Test 1:

mysql> set session interactive_timeout = 10;

Query OK, 0 rows affected (0.00 sec)mysql> set session wait_timeout = 10;Query OK, 0 rows affected (0.00 sec)mysql> show session variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| interactive_timeout        | 10       || wait_timeout               | 10       |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show global variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| interactive_timeout        | 20       || wait_timeout               | 20       |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 || 17 | root        | localhost:60585 | NULL | Sleep   |     10 |                                                                             | NULL                  |        10 |            10 |        11 |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+2 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+1 rows in set (0.00 sec)

Test 2:

mysql> show session variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| interactive_timeout        | 20       || wait_timeout               | 20       |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show global variables like '%timeout%';+----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| interactive_timeout        | 10       |
| wait_timeout               | 10       |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| Id | User        | Host            | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+|  3 | root        | localhost       | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 || 19 | root        | localhost:50276 | NULL | Sleep   |     19 |                                                                             | NULL                  |        10 |            10 |        11 |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+2 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| Id | User        | Host      | db   | Command | Time   | State                                                                       | Info                  | Rows_sent | Rows_examined | Rows_read |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+|  3 | root        | localhost | NULL | Query   |      0 | NULL                                                                        | show full processlist |         0 |             0 |        11 |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+1 rows in set (0.00 sec)

　　

Summary

The following conclusions can be drawn from the above phase tests.

1. the timeout value is only used to calculate connections in the inactive state.

2. the timeout value is effective through session wait_timeout.

3. wait_timeout of interactive connection inherits from global. interactive_timeout

Wait_timeout of non-interactive connection inherits from global. wait_timeout

4. the inheritance relationship and timeout effect on TCP/IP and Socket connections

BitsCN.com