MySQL timeout Parsing

"And God said, Let there be network: and there was timeout"
During the use of MySQL, have you ever encountered many puzzling timeouts?
After these timeouts, is it a code issue or is it unknown?
In this issue, we will describe the timeout of the MySQL DBA.

Take a look at the common timeout parameters and Related explanations:
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.

From the preceding explanations, we can see that connect_timeout works in the authenticate phase, while interactive_timeout and wait_timeout work in the sleep phase, net_read_timeout and net_write_timeout work in the busy connection phase (query.

The result of multiple handshakes is obtained for MySQL connections. In addition to the username and password verification, there are also IP-> host-> DNS-> ip verification, any step may cause thread blocking due to network problems. To prevent threads from being wasted on unnecessary verification waits, connection requests exceeding connect_timeout will be rejected.

Even if there is no network problem, the client cannot continue to occupy the connection. For clients whose sleep status exceeds wait_timeout (or interactive_timeout depends on the client_interactive flag), MySQL will automatically disconnect.

Even if the connection is not in the sleep status, that is, the client is busy computing or storing data, MySQL selects a conditional wait. During packet distribution, the client may not be able to respond (sending, receiving, or processing data packets is too slow ). To ensure that the connection is not wasted in endless waiting, MySQL will also choose to actively disconnect with conditions (net_read_timeout and net_write_timeout.

So many timeouts are enough to prove how happy MySQL is to disconnect. The main reason behind the willingness to disconnect is to prevent the server from occupying resources shared by a client (MySQL, mysqldump, page programs, and so on.

From: http://ourmysql.com/archives/810 from Taobao
DBA team