Archive for 七月 2012

interactive_timeout和wait_timeout参数区别和设置策略

请问mysql的interactive_timeout和wait_timeout这两个参数,如何根据业务的特点来确定?
interactive_timeout是MySQL在等待一个活动连接关闭连接前等待的秒数。
wait_timeout是MySQL在等待一个非活动连接关闭连接前等待的秒数。
活动的连接和非活动的连接的定义如下:活动的连接是指使用mysql_real_connect连接时指定了CLIENT_INTERACTIVE的连接。
MySQL 5.1.41之前wait_timeout只适用于TCP/IP连接,不适用于Unix sock,name pipe,share memory的连接。

实际上,一个MySQL thread连接以后,首先会判断它是一个活动连接还是非活动连接(见上面的定义)。活动连接的话会设置为global interactive_timeout,非活动连接会设置为global wait_timeout。超时就会退出。

根据业务来的话,如果你用的是连接池,并且没有F5(F5可能会设置一个超时,把连接时间过长的session kill掉),那么你的两个timeout时间尽量设置长一些,避免应用连接池不断的重新连接数据库。如果你是短连接,那么你就需要把数据库的连接设置得短一些,否则数据库的最大连接数是有限的,你的很多连接一直不释放掉的话,数据库的资源就被拜拜浪费了。

 

下面是官方的reference

Command-Line Format –interactive_timeout=#

Option-File Format interactive_timeout
Option Sets Variable Yes, interactive_timeout
Variable Name interactive_timeout
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values
Type numeric
Default 28800
Min Value 1
The number of seconds the server waits for activity on an interactive connection before closing it.
The number of seconds the server waits for activity on a noninteractive connection before closing it.
An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.
wait_timeout

Command-Line Format –wait_timeout=#
Option-File Format wait_timeout
Option Sets Variable Yes, wait_timeout
Variable Name wait_timeout
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values
Type numeric
Default 28800
Range 1 .. 31536000
Permitted Values
Type (windows) numeric
Default 28800
Range 1 .. 2147483
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 (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.