mysql查询超时设置_MySQL查询超时的设置方法

为了优化OceanBase的query timeout设置方式，特调研MySQL关于timeout的处理，记录如下。 [plain] mysql show variables like %time%; +----------------------------+-------------------+ | Variable_name | Value | +----------------------------+--------

为了优化OceanBase的query timeout设置方式，特调研MySQL关于timeout的处理，记录如下。

[plain]

mysql> show variables like '%time%';

+----------------------------+-------------------+

| Variable_name              | Value             |

+----------------------------+-------------------+

| connect_timeout            | 10                |

| datetime_format            | %Y-%m-%d %H:%i:%s |

| delayed_insert_timeout     | 300               |

| flush_time                 | 1800              |

| innodb_lock_wait_timeout   | 50                |

| innodb_old_blocks_time     | 0                 |

| innodb_rollback_on_timeout | OFF               |

| interactive_timeout        | 28800             |

| lc_time_names              | en_US             |

| lock_wait_timeout          | 31536000          |

| long_query_time            | 10.000000         |

| net_read_timeout           | 30                |

| net_write_timeout          | 60                |

| slave_net_timeout          | 3600              |

| slow_launch_time           | 2                 |

| system_time_zone           |                   |

| time_format                | %H:%i:%s          |

| time_zone                  | SYSTEM            |

| timed_mutexes              | OFF               |

| timestamp                  | 1366027807        |

| wait_timeout               | 28800             |

+----------------------------+-------------------+

21 rows in set, 1 warning (0.00 sec)

重点解释其中几个参数：

connect_timeout:

The number of seconds that the mysqld server waits for a connect packet before respondingwith Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that.  Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.

解释：在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。

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 uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See alsowait_timeout.

解释：一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态，执行完Query后成为interactive状态，重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程，意义是一样的。

MySQL可以配置连接的超时时间，这个时间如果做得太长，甚至到了10min，那么很可能发生这种情况，3000个链接都被占满而且sleep在哪，新链接进不来，导致无法正常服务。因此这个配置尽量配置一个符合逻辑的值，60s或者120s等等。

说人话：

命令行下面敲一个命令后，直至下一个命令到来之前的时间间隔为interactive_time，如果这个时间间隔超过了 interactive_timeout，则连接会被自动断开，下一个命令失败。不过一般的mysql客户端都有自动重连机制，下一个命令会在重连后执 行。

[sql]

mysql> set interactive_timeout = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like '%timeout%';

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| connect_timeout            | 10       |

| interactive_timeout        | 1        |

| wait_timeout               | 28800    |

+----------------------------+----------+

10 rows in set (0.00 sec)

=====

[sql]

mysql> set wait_timeout = 1;

Query OK, 0 rows affected (0.00 sec)

【去泡杯茶，等会儿】

mysql> show session variables like '%timeout%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    7

Current database: *** NONE ***

+----------------------------+----------+

| Variable_name              | Value    |

+----------------------------+----------+

| connect_timeout            | 10       |

| interactive_timeout        | 28800    |

| wait_timeout               | 28800    |

+----------------------------+----------+

10 rows in set (0.01 sec)

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 session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client