主页 > 知识库 > 数据库 > MySQL >

MySQL查询超时的设置方法

来源:中国IT实验室 作者:佚名 发表于:2013-04-19 11:29  点击:
为了优化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

有帮助
(1)
100%
没帮助
(0)
0%