Detailed analysis of MySQL and related timeout

When querying the mysql database, the session is always interrupted when the amount of data is relatively large. At first I thought it was a timeout problem related to mysql and connection, but it turned out to be the cause of network instability. This article mainly introduces the detailed information about MySQL and connection-related timeout. This article mainly summarizes the connection-related timeout. Friends who need it can refer to it. I hope it can help everyone.

The following summarizes the timeout related to the connection

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 for N seconds and then disconnects , reconnect 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 to obtain data from a connection before terminating reading; when the service is reading data from the client, net_read_timeout controls when to timeout.

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 the block to the connection before terminating writing; when the service is writing data to the client, net_write_timeout controls when to timeout

wait_timeout

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

The number of seconds the server waits for activity on an interactive connection before closing it.
A connection with no interactive state until forced by the server The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds.

Time to wait for a connection response

connect_timeout:
Function during the connection acquisition phase (authenticate)

interactive_timeout and wait_timeout: work during the connection idle phase (sleep)

net_read_timeout and net_write_timeout: work during the connection busy phase (query).

Obtaining the MySQL connection is the result of multiple handshakes. In addition to the matching verification of the user name and password, there is also IP->HOST->DNS->IP verification. Any step is possible. The thread is blocked due to network problems. In order to prevent threads from wasting unnecessary verification waiting, connection requests that exceed connect_timeout will be rejected.

Even if there is no network problem, the client cannot be allowed to occupy the connection all the time. For clients that remain in sleep state for longer than wait_timeout (or interactive_timeout, depending on the client_interactive flag), MySQL will actively disconnect.

Even if the connection is not in the sleep state, that is, the client is busy calculating or storing data, MySQL also chooses to wait conditionally. During the distribution of data packets, the client may be too late to respond (sending, receiving, or processing data packets 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).

Related recommendations:

Single thread of setTimeout in JavaScript

Example explanation of setTimeout to see js function execution Process

setTimeout() function in js

The above is the detailed content of Detailed analysis of MySQL and related timeout. For more information, please follow other related articles on the PHP Chinese website!