Impact of mysql query timeout on PHP Execution

Impact of mysql query timeout on PHP Execution

Recently, I encountered a problem that mysql performance encountered a bottleneck in high concurrency. Because PHP is a weak language, there is no such problem. Therefore, when mysql returns an unexpected result, it may cause a logical error.

1) thread blocking test

When SQL statements are executed too slowly, the number of connections in mysql is exhausted and new requests cannot be processed.

Test Method

Execute the set global max_connections = 1; statement, and enable the persistent connection in another program to occupy the connection. At this time, the mysql service has no connection available.

The PHP code is as follows:

<?php$con=mysql_connect('127.0.0.1','root','');var_dump($con);sleep(10);mysql_select_db('test',$con);$cursor=mysql_query("select * from `timeout_test` where `id`=2 for update");var_dump($cursor);var_dump(mysql_fetch_assoc($cursor));echo "done!";

Returned results: Both mysql_connect and mysql_query return false.

2) Lock blocking test

Run set autocomit = 0 to restore the number of normal connections. I use the innodb engine. The myisam engine does not have the transaction concept. The autocomit value is 1 in myisam.

In the mysql link, run the following statement:

mysql> select * from `timeout_test` where `id`=2 for update;+----+------+| id | name |+----+------+|  2 | kk   |+----+------+1 row in set (0.00 sec)

Next, execute the above PHP and the returned results are as follows:

resource(5) of type (mysql link)bool(false)Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in D:\test\mysql-index\timeout.php on line 7Call Stack:    0.0002     234472   1. {main}() D:\test\mysql-index\timeout.php:0   51.1252     242496   2. mysql_fetch_assoc() D:\test\mysql-index\timeout.php:7NULLdone!PHP Warning:  mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in D:\test\mysql-index\timeout.php on line 7PHP Stack trace:PHP   1. {main}() D:\test\mysql-index\timeout.php:0PHP   2. mysql_fetch_assoc() D:\test\mysql-index\timeout.php:7

Mysql_query returns false. If you use a function such as mysql_fetch_assoc to obtain the result, NULL is returned.

3) Summary

Function Name	Thread Blocking	Lock Blocking
Mysql_connect	If the function returns false, a warning error is thrown and the program is not terminated.	Resource (3, mysql link) is returned. Normal.
Mysql_query	If the function returns false, a warning error is thrown and the program is not terminated. If mysql_connect returns false, mysql_query does not reconnect to mysql even if the connection is released.	Returns false, but does not throw an exception. For normal mysql_query, false is returned if execution fails. The program should judge this error.