Timeout processing for accessing MySQL using PHP

Currently, you can set options to operate the connection timeout of two client extension libraries, such as mysqli: Copy codeThe Code is as follows: <? Php
// Create an object
$ Mysqli = mysqli_init ();
// Set the timeout Option
$ Mysqli-> options (MYSQLI_OPT_CONNECT_TIMEOUT, 5 );
// Connection
$ Mysqli-> real_connect ('localhost', 'My _ user', 'My _ password', 'World ');
// Print the error message if the connection times out or fails.
If (mysqli_connect_errno ()){
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}
// The connection information is output successfully.
Printf ("Connection: % s \ n.", $ mysqli-> host_info );
$ Mysqli-> close ();
?>

This is the connection timeout, but sometimes we need to query the read/write timeout. For example, if a database is under great pressure or has many connections, the database query will be very slow, however, I hope that some unimportant data, such as the number of clicks in the article, will not be displayed if the query times out, at least to ensure that the subject page is correctly displayed, however, this operation option or function was not found in the PHP manual.

There are only four options in the manual

Tracking the extended source code of mysqli, we found that it calls mysql_options of libmysqlclient at the underlying layer:

Php-5.2.8/ext/mysqli/mysqli_api.c

In addition, only a few variables are exported in the PHP extension of mysqli:

Php-5.2.8/ext/mysqli. c

After reading the libmysqlclient code, I found that it comes with a read/write timeout setting:

Mysql-5.1.30/SQL-common/client. c

Because it defines many operation options, but the php extension does not:

Mysql-5.1.30/include/mysql. h

Let's see how mysql achieves read/write Timeout:

Mysql-5.1.30/SQL-common/client. c

The real operation of read/write timeout. The timeout is retried twice, or the write is dead:

Mysql-5.1.30/SQL/net_serv.cc

Now I have come to the conclusion:

According to the code shown above, currently, PHP has the following restrictions on MySQL query Timeout:

1. The unit of timeout setting is seconds. The minimum value is 1 second.

2. However, the underlying read of mysql will be retried twice, so it will actually be 3 seconds.

Retry twice + itself at one time = 3 times the timeout time.

That is to say, the minimum timeout time is 3 seconds, which is not lower than this value. This is acceptable for most applications, but needs to be optimized for a small number of applications.
Now let's take a look at it. If we want to set the timeout, we can press MYSQL_OPT_READ_TIMEOUT to achieve the read/write timeout effect. Write a piece of code to test it:Copy codeThe Code is as follows: <? Php
// Customize read/write timeout Constants
If (! Defined ('mysql _ OPT_READ_TIMEOUT ')){
Define ('mysql _ OPT_READ_TIMEOUT ', 11 );
}
If (! Defined ('mysql _ OPT_WRITE_TIMEOUT ')){
Define ('mysql _ OPT_WRITE_TIMEOUT ', 12 );
}

// Set timeout
$ Mysqli = mysqli_init ();
$ Mysqli-> options (MYSQL_OPT_READ_TIMEOUT, 3 );
$ Mysqli-> options (MYSQL_OPT_WRITE_TIMEOUT, 1 );

// Connect to the database
$ Mysqli-> real_connect ("localhost", "root", "root", "test ");
If (mysqli_connect_errno ()){
Printf ("Connect failed: % s \ n", mysqli_connect_error ());
Exit ();
}

// Execute the query sleep for 1 second without timeout
Printf ("Host information: % s \ n", $ mysqli-> host_info );
If (! ($ Res = $ mysqli-> query ('select sleep (1 )'))){
Echo "query1 error:". $ mysqli-> error. "\ n ";
} Else {
Echo "Query1: query success \ n ";
}

// Execution of the query sleep will time out in 9 seconds
If (! ($ Res = $ mysqli-> query ('select sleep (9 )'))){
Echo "query2 error:". $ mysqli-> error. "\ n ";
} Else {
Echo "Query2: query success \ n ";
}

$ Mysqli-> close ();
Echo "close mysql connection \ n ";
?>

Check the execution result of the code above and verify the above view. The first query is successful, and the second query connection is disconnected:

If you need to modify the timeout value in seconds, for example, to change the timeout value in milliseconds, you can only modify it in two places:

1. Modify the client, such as the query code of mysqli, and add the timer. If the timeout time is exceeded, return

2. Modify the vio code in Mysql because the underlying network processing of mysql is performed by vio.

MySQL-related vio code:

Poll Timeout:

Setsockopt Timeout:

This basically solves the timeout of PHP read/write query operations on MySQL. I hope it will help you.
Heiyeluren's blog