PHP access to Mysql query timeout processing method _mysql

Source: Internet
Author: User
Tags mysql query
Currently two client-side extension Library connection timeouts can be set to operate with options such as mysqli:
Copy Code code as follows:

<?php
Creating objects
$mysqli = Mysqli_init ();
Set timeout options
$mysqli->options (Mysqli_opt_connect_timeout, 5);
Connection
$mysqli->real_connect (' localhost ', ' my_user ', ' My_password ', ' world ');
Print error message if timeout or other connection fails
if (Mysqli_connect_errno ()) {
printf ("Connect failed:%s\n", Mysqli_connect_error ());
Exit ();
}
Successfully output connection information
printf ("Connection:%s\n.", $mysqli->host_info);
$mysqli->close ();
?>

This is the connection timeout, but sometimes we need to query read-write timeout, for example, we have a database pressure, or a lot of connections, then the database query is very slow, but I hope that some of the unimportant data, such as the number of clicks on the article if the query timed out does not show, at least to ensure that the main page But searching through the PHP manual did not find the Operation option or function.

There are only four options in this manual.

The trace Mysqli's extended source code found that it called the libmysqlclient mysql_options at the bottom:

Php-5.2.8/ext/mysqli/mysqli_api.c

And only a few variables are exported in the mysqli PHP extension:

Php-5.2.8/ext/mysqli/mysqli.c

Probably looked at the Libmysqlclient code, found that it has a read-write timeout set:

Mysql-5.1.30/sql-common/client.c

Because it defines a lot of operational options for itself, it's just not in the PHP extensions:

Mysql-5.1.30/include/mysql.h

See how the read-write timeout in MySQL is implemented:

Mysql-5.1.30/sql-common/client.c

Read-write timeout where the actual operation, timeout processing Here retry two times, or write dead:

mysql-5.1.30/sql/net_serv.cc

Now we have a basic conclusion:

According to the above look at the code, the current PHP for MySQL query timeout the following restrictions:

1. Timeout set to seconds, minimum configuration 1 seconds

2. But the MySQL bottom of read will retry two times, so the actual will be 3 seconds

Retry two times + self once = 3 times times timeout time.

That means the minimum timeout is 3 seconds, not below this value, acceptable for most applications, but optimized for a small number of applications.
Now let's see if we're going to set the timeout ourselves, we can also get the read-write timeout effect by pressing the mysql_opt_read_timeout ourselves, write a code to test:

Copy Code code as follows:

<?php
Define your own read and write Hyper-constant
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);

Connecting to a database
$mysqli->real_connect ("localhost", "root", "root", "test");
if (Mysqli_connect_errno ()) {
printf ("Connect failed:%s\n", Mysqli_connect_error ());
Exit ();
}

Run Query sleep 1 seconds does not 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";
}

Run Query sleep 9 seconds will timeout
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 results of the above code to verify the above view, the first query succeeded, the second query connection was disconnected:

If you need to modify this second-level timeout, such as a millisecond timeout, you can only modify it in two places:

1. Modify the client, such as mysqli 's query code, add the timer, and return the timeout

2. Modify the vio code in MySQL , because MySQL 's network processing is the bottom of the vio operation

MySQL-related vio code:

Poll Timeout:

SetSockOpt timeout:

Basically to here can solve the PHP in the MySQL read and write query operation timeout processing, I hope to help you.
Heiyeluren's Blog

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.