MySQL Timeout解析

​

“And God said, Let there be network: and there was timeout”

在使用MySQL的過程中，你是否遇到了衆多讓人百思不得其解的Timeout？

那麼這些Timeout之後，到底是代碼問題，還是不為人知的匠心獨具？

本期Out-man，講述咱們MySQL DBA自己的Timeout。

先看一下比較常見的Timeout參數和相關解釋：

connect_timeout

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.

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.

從以上解釋可以看出，connect_timeout在擷取連接配接階段（authenticate）起作用，interactive_timeout和wait_timeout在連接配接空閑階段（sleep）起作用，而net_read_timeout和net_write_timeout則是在連接配接繁忙階段（query）起作用。

擷取MySQL連接配接是多次握手的結果，除了使用者名和密碼的比對校驗外，還有IP->HOST->DNS->IP驗證，任何一步都可能因為網絡問題導緻線程阻塞。為了防止線程浪費在不必要的校驗等待上，超過connect_timeout的連接配接請求将會被拒絕。