MySQL's usage and implementation of various network IO timeouts

2016-04-06 Zhao Wei Database developer

Client C API

Call Mysql_options () in the C API to set the properties of the connection object created by Mysql_init (), using these three options to set the connection time-out and read-write timeouts, in seconds. When the read-write timeout is reached, the query send and result fetch functions of the C API return a time-out error.

Mysql_opt_connect_timeout

Mysql_opt_read_timeout

Mysql_opt_write_timeout

You can also use a configuration file to set connection timeouts and interaction timeouts:

Connect-timeout=seconds

Interactive-timeout=seconds

When the client API does not receive the corresponding MySQL server after initiating a connection to MySQL server for connect-timeout seconds, then the connection is considered unsuccessful.

Interactive-timeout is the interaction timeout used for this client connection. The interaction timeout is used to set the server's session time-out in an interactive interface program (such as the MySQL Connection client program) to assign a value to the wait_timeout session variable, which is usually larger because the interaction is usually done manually by the human, not by the program execution and sending instructions. MySQL server uses the session variable @ @wait_timeout as the timeout for the session by default, unless the CLIENT_INTERACTIVE flag is used in Mysql_real_connect to identify that the connection is for interactive operation. At this point, if the client has interactive-timeout to use it as a session timeout for this session, the server-side interactive-timeout is used as the session timeout to Wait_timeout.

MySQL Server Internal

As you can see, there are a number of server-side variables for timeouts, which remove several timeout values for our Tdsql-specific non-standard mysql/mariadb. Among them the Connect_timeout, Net_read_timeout, Net_write_timeout,slave_net_timeout, Interactive_timeout and Wait_ Timeout is related to network IO.

Among them, Connect_timeout is used during user logon, that is, during the database connection, as the MySQL server side of the network read-write timeout. This is not the same as the document above, but it is true in the code.

Net_read_timeout and Net_write_timeout are the read and write timeouts used by the MySQL server side after the database session is created. If the read or write operation waits for a timeout, the server considers the client connection to be disconnected and performs error handling.

While Slave_net_timeout is the slave IO thread that uses the client C API to connect master, call Mysql_options () to set the Mysql_opt_connect_timeout,mysql_opt_read_ Timeout,mysql_opt_write_timeout the values used by these three timeout options. The IO thread connection master uses standard client C API code and communication protocols.

Finally, Wait_timeout is the default session timeout for Mysqld server, and if a database connection (session) does not have any read and write actions after such a long time, then the connection is closed. Interactive_timeout is the default interactive connection session time-out, which is set to Wait_timeout, and if the client has a custom value, then that value will be used preferentially to set to Wait_timeout.

All of these timeouts can be divided into connection timeouts, read and write timeouts, and session timeouts, and here's how these timeout mechanisms are implemented.

How to implement Timeouts

In system calls such as the Linux Connect (), recv, send, read (), write (), it is not possible to simply block the wait for a specified time before returning an error, instead of setting the file handle to non-blocking (using Fcntl () and O_ The Nonblock flag, or for recv/send () can use msg_dontwait every time it is called) and returns immediately, or blocks waiting. So, to achieve timeouts or a little bit of skill. In addition, the Code for network IO in MySQL, whether it is the network IO function of the client C API or the network communication function used inside the server, is the same code implementation, so the following does not need to differentiate between the client and the server side.

Connection timed out

Related functions: Vio_socket_connect (), vio_io_wait ()

First, if there is a connection timeout, set the socket FD to non-blocking, and then call the standard socket function connect () to initiate the connection, this function will immediately return 1 and set errno to Einprogress or Ealready. Then, call vio_io_wait () to use poll () to block waiting for this connection to be written, using the connection timeout value as the time-out parameter for poll (). This will cause the connection to fail after waiting for a timeout, or the connection will succeed. It does not use the network protocol to time out its own connection, because that would not make it possible to change the timeout at any time without affecting other processes.

Read-write timeout

Related functions: Vio_read (), Vio_write (), vio_socket_io_wait ()

First, call the standard recv ()/send () system to read or write, and if there is a read-write timeout, use msg_dontwait as the last flags parameter, This function returns Socket_eagain or socket_ewouldblock immediately if no data can be read or written (such as network congestion), and calls Vio_socket_io_wait () to block waiting for the socket FD can read or write, the function is to call the poll (), and the read-write timeout value as the poll () waiting time-out.

Session Timeout

The session here is actually a database connection, which corresponds to the THD class within MySQL. The session timeout is the only mechanism on the MySQL server side, not on the client. The implementation is that the MYSQL server internal thread periodically checks the state of all the THD session objects and destroys the session-timed THD object.

MySQL's usage and implementation of various network IO timeouts