MySQL and connection-related timeout

MySQL and connection-related timeout

Today, colleagues asked why the query MySQL library, when the volume of data is large, the session is always broken. At first I thought it was MySQL and connected to the timeout problem, the result is the network instability reasons.

The following summarizes the connection-related timeout

Slave-net-timeout
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the R EAD, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the Master_connect_retry option for the change MASTER to statement, and the Number of reconnection attempts is limited by the--master-retry-count option. The default is 3600 seconds (one hour).

When slave thinks the connection to master is problematic, wait for n seconds, then disconnect and reconnect master

Net_read_timeout:
The number of seconds to wait for more data from a connection before aborting the read. When the server was reading from the client, Net_read_timeout was the timeout value controlling when to abort

The number of seconds to wait for data to be fetched from a connection before terminating the read, and when the service is reading data from the client, net_read_timeout controls when the time-out occurs.

Net_write_timeout:
The number of seconds to wait for a block to is written to a connection before aborting the write. When the server was writing to the client, Net_write_timeout was the timeout value controlling when to abort.

The number of seconds to wait for the block to write to the connection before terminating the write, and when the service is writing the data to the client, Net_write_timeout controls when the timeout occurs

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

The time to wait for a connection that is not interactive with the server until it is forcibly closed by the server side

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

The time to wait for a connection that is not interactive with the server until it is forcibly closed by the server side

Connect_timeout
The number of seconds the MYSQLD server waits for a connect packet before responding with bad handshake. The default value is ten seconds.

Time to wait for a connection response

Connect_timeout: Working in the Get Connection phase (Authenticate)
Interactive_timeout and Wait_timeout: Working in the connection idle phase (sleep)
Net_read_timeout and Net_write_timeout: It works in the connection busy phase (query).

Getting a MySQL connection is the result of multiple handshakes, with IP->HOST->DNS->IP authentication in addition to the user name and password matching checks, which can cause a thread to block due to network problems. To prevent thread wasting on unnecessary check waits, connection requests that exceed connect_timeout will be rejected.

Even if there is no network problem, you cannot allow the client to occupy the connection. For clients that maintain a sleep state that exceeds wait_timeout (or interactive_timeout, depending on the client_interactive flag), MySQL actively disconnects.

MySQL chooses conditional waits even if the connection is not in sleep state, that is, the client is busy computing or storing data. During the distribution of a packet, the client may be too late to respond (send, receive, or process packets too slowly). To ensure that the connection is not wasted in endless waits, MySQL also chooses to have the conditional (Net_read_timeout and Net_write_timeout) active disconnection.

MySQL and connection-related timeout