Asynchronous MySQL queries

In mysqlnd , it became possible to execute queries to MySQL asynchronously, that is, to continue the script without waiting for the query to be executed and the result to be generated. The advantage of this approach is obvious, because you can do a lot of useful work while waiting for a request, but first I will give a slightly different example:

Suppose you have 3 queries ( q1 , q2 , q3 ), each query is executed for a certain time ( t1 , t2 , t3 ), for example:

SELECT 1 AS val, SLEEP(1) AS sleep SELECT 2 AS val, SLEEP(2) AS sleep SELECT 3 AS val, SLEEP(3) AS sleep 

')
In the case of synchronous execution of requests, you can get the results of their execution through t1 + t2 + t3 (ex: 6 seconds), and in the case of asynchronous execution of requests already in max (t1, t2, t3) (ex: 3 seconds)

Examples of working with asynchronous requests, as well as other examples of working with mysqlnd can be found on github

Run asynchronous requests

To perform asynchronous requests, it is sufficient to specify the special flag MYSQLI_ASYNC .

 mysqli_query($link, $query, MYSQLI_ASYNC); 

This constant is declared directly in the extension, so the expressions above will not execute without mysqlnd. To provide the ability to execute a query synchronously, in the absence of mysqlnd, there are several options for executing the query:

 //     ( notice     - !) mysqli_query($link, $query, MYSQLI_ASYNC || MYSQLI_USE_RESULT); //   $flag = defined('MYSQLI_ASYNC') ? MYSQLI_ASYNC : MYSQLI_USE_RESULT; mysqli_query($link, $query, $flag); //   defined('MYSQLI_ASYNC') || define('MYSQLI_ASYNC', MYSQLI_USE_RESULT); mysqli_query($link, $query, MYSQLI_ASYNC); 

Unlike the usual result of this function, in the case of asynchronous execution of a read request (SELECT, etc.), it returns true, instead of mysqli_result.

Verifying asynchronous requests

To check the performance of asynchronous requests, use the mysqli_poll function. Unfortunately, the function is not documented, and the set of parameters is puzzling, so I had to go into the sources and see what happens after all. As a result, it turned out that the function is a wrapper for the select system call, like the stream_select function:



The mysqli_poll function is supplied with three arrays containing mysqli objects that need to be checked and validation timeout values ​​(sec, [usec]). The arrays are converted to the appropriate set of file descriptors, the values ​​for the timeout are converted to the timeval structure and input to the select system call, then the inverse conversion of the descriptors to mysqli objects is performed and the result of the select system call is returned. Examples of checking the results of asynchronous requests can be found here .

Retrieving query results

Getting the results of an asynchronous request occurs through the function mysqli_reap_async_query . The function call is always necessary, since it removes the blocking from the request and without this call, all subsequent requests will fall from “Commands out of sync”. For read requests (SELECT and so on), the function will return mysqli_result, which after processing must be “released »To continue working with the database, for the rest - bool.

Use cases

The main advantage of using asynchronous requests is more efficient use of CPU time. At the beginning of the article there is an example that shows how quickly you can perform multiple asynchronous queries to the database ( code ). In addition, the developer often faces the task of writing migrations to the database. Migrations are usually divided into schema migrations (CREATE, ALTER, DROP) and data migrations (INSERT, UPDATE, DELETE). In the case of working with large tables, the execution of ALTER takes a large amount of time, which can be effectively used to prepare data for UPDATE ( code ).

PS Working with asynchronous requests is not yet completely transparent to me, so I ask for your help:

1. I could not emulate getting errors (the appearance of the mysqli object in the errors array) via mysqli_poll. If you know how to do this, write plz, be sure to add to the article.

2. If I close the connection before calling mysqli_poll, I get segfault (ubuntu 12.04, php 5.3.10). Plz play the following code in your homepage, you may need to report a bug:

 $link = new mysqli('host', 'user', 'password', 'db', 'port'); mysqli_close($link); $read = $error = $reject = array(); $read[] = $error[] = $reject[] = $link; mysqli_poll($read, $error, $reject, 1); 

UPD: Bug is open, quickfix is ​​already there