Detailed summary of MySQL and connection-related timeouts

MySQL and connection related timeouts

Preface:

Today a colleague asked me why the session was always disconnected when querying the MySQL database when the amount of data was large. At first I thought it was a MySQL connection timeout issue, but it turned out to be due to network instability.

The following summarizes the timeouts related to connections

slave-net-timeout

The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, 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 the slave thinks there is a problem with the connection to the master, it waits N seconds, then disconnects and reconnects to the master.

net_read_timeout :

The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort
The number of seconds to wait for data from a connection before terminating the read; net_read_timeout controls when the server times out while reading data from a client.

net_write_timeout:

The number of seconds to wait for a block to be written to a connection before aborting the write. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort.
How many seconds to wait to write a block to the connection before terminating the write; when the service is writing data to the client, net_write_timeout controls when to time out.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it.
The time it takes for a connection with no interaction to be closed by the server.

interactive_timeout :

The number of seconds the server waits for activity on an interactive connection before closing it.
The time it takes for a connection with no interaction to be closed by the server.

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.

The time to wait for a connection response

connect_timeout: takes effect during the connection acquisition phase (authentication)

interactive_timeout and wait_timeout: work during the connection idle phase (sleep)
net_read_timeout and net_write_timeout: take effect during the busy connection phase (query).

Acquiring a MySQL connection is the result of multiple handshakes. In addition to the matching verification of the username and password, there is 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 that exceed connect_timeout will be rejected.

Even if there are no network problems, you cannot allow the client to occupy the connection all the time. MySQL actively disconnects clients that remain in sleep state for longer than wait_timeout (or interactive_timeout, depending on the client_interactive flag).

Even if the connection is not in sleep state, that is, the client is busy calculating or storing data, MySQL chooses to wait conditionally. During the data packet distribution process, the client may not have time to respond (sending, receiving, or processing the data packet too slowly). In order to ensure that the connection is not wasted in endless waiting, MySQL will also choose to actively disconnect the connection conditionally (net_read_timeout and net_write_timeout).

refer to:

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

The above is a detailed summary of MySQL and connection-related timeouts. If you have any questions, please leave a message or discuss in the community. Thank you for reading and I hope it can help you. Thank you for your support of this site!