mysql的connecttime线上_mysql查看connect_timeout设置

首先通过mysql -uroot -p 登录mysql数据库,然后输入 show variables like '%timeout%';命令可以看到系统的各个timeout设置

mysql> show variables like '%timeout%';

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

| Variable_name | Value |

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

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| slave_net_timeout | 3600 |

| wait_timeout | 28800 |

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

11 rows in set (0.00 sec)

connect_timeout

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds. 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. The maximum number of seconds before connection timeout. The default value is 43200 (12 hours).

interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it.

net_read_timeout

The number of seconds to wait for more data from a connection before aborting the read.

net_write_timeout

The number of seconds to wait for a block to be written to a connection before aborting the write.

delayed_insert_timeout

How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating. As of MySQL 5.6.7, this system variable is deprecated (because DELAYED inserts are deprecated), and will be removed in a future release.

从以上解释可以看出，connect_timeout在获取连接阶段(authenticate)起作用，interactive_timeout和wait_timeout在连接空闲阶段(sleep)起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段(query)起作用。

获取MySQL连接是多次握手的结果，除了用户名和密码的匹配校验外，还有IP->HOST->DNS->IP验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过connect_timeout的连接请求将会被拒绝。

即使没有网络问题，也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout(或interactive_timeout，取决于CLIENT_INTERACTIVE标志)的客户端，MySQL会主动断开连接。

即使连接没有处于sleep状态，即客户端忙于计算或者存储数据，MySQL也选择了有条件的等待。在数据包的分发过程中，客户端可能来不及响应(发送、接收、或者处理数据包太慢)。为了保证连接不被浪费在无尽的等待中，MySQL也会选择有条件(net_read_timeout和net_write_timeout)地主动断开连接。

这么多Timeout足以证明MySQL是多么乐于断开连接。而乐于断开连接的背后，主要是为了防止服务端共享资源被某客户端(mysql、mysqldump、页面程序等)一直占用。