Best way to call and "forget about" an async mysqlnd INSERT query

Question

Setup

I'm working with php and mysql in this situation.

Let's say I have my mysqli connection like this:

$link = new mysqli('localhost', 'root', 'password', 'A_Database');

I have installed mysqlnd to perform asynchronous mysql queries using the 'MYSQL_ASYNC' parameter:

$link->query("INSERT INTO `A_Table` VALUES('stuff!')", MYSQLI_ASYNC);

Goal

I just want to insert a record, and I don't need to retrieve it until the distant future so I'm not concerned about how long it takes for the asynchronous query to finish up, and I don't need to perform some final action when I know the query is complete. I do need to perform other unrelated mysql queries once I'm past the section of the code where the insert queries occur.

Problem

Performing a query like this will block other queries later in the script with out-of-sync errors. In order to deal with that, I had to add something like the following code after every async query:

$links = $errors = $reject = array($link);
if ($link->poll($links, $errors, $reject, 1)) {
    foreach ($links as $resultLink) {
        if ($result = $resultLink->reap_async_query()) {
            if (is_object($result)) {
                $result->free();
            }
        }
    }
}

This effectively stops the out-of-sync errors and my code works fine.
Two things still trouble me though:

1. I don't want to have to bother with this, because I'm only performing insert queries and I don't care about having some response in my code when I know the inserts are complete.

2. The polling code runs really really slowly on my server; far slower than performing a regular query and synchronously get the results back.

Recap

I want to run the insert query with two requirements; the query is non-blocking (asynchronous), and I can still perform other mysql queries later on. I just want to insert-query and 'forget about it', and just move on with my code.

Any suggestions as to what the best way to do this is?

Answer 1

If your goal is to insert a record and continue executing code without worrying about the result you can use INSERT DELAYED syntax without the need for the ASYNC flag:

$link->query("INSERT DELAYED INTO `A_Table` VALUES('stuff!')" );

It returns right away and you can continue executing new querys on the same connection. We use this on our logs system.

Answer 2

If you want to be able to fire-and-forget your queries, your current approach is mostly correct, although I would suggest that the slowness you are experiencing is possibly because you have '1' set as the 'secs' parameter in $link->poll($links, $errors, $reject, 1). This means the poll will wait up to 1 second for the query to return. If you set this to '0' then it will not wait, it will return much faster with an answer of whether the query is complete.

Secondly, if you don't need the answer from this query, you can be doing other things while this query is executing, you don't have to sit there waiting for it to return in an async-poll loop if you have other things to be getting on with. This is what ASYNC queries were designed for. I would suggest you add checks to your query execute method to check if there is an outstanding ASYNC query already being executed, so whenever you need to fire off a new query, only then will it poll the server and wait for any previous query to finish and become available.

Alternatively, if you want to run multiple queries that don't have any impact or dependency on each other, there's nothing to stop you setting up multiple database connections and running ASYNC queries on all of them in parallel, or setting up new connections and executing queries as and when you need to.

If you want to entirely outsource your SQL to another process (without cron), one way would be to setup a persistent monitoring script, using Supervisor/Gearman for example, so you can send queries to it and have it process the queries in a process (or even server) different from the local one. This has the benefit that the remote script executes as a CLI application, so has more ability to use forks and multi-threaded processing, and can operate in a less exposed environment.

Another approach (optionally using cron) is to use a write-optimised (maybe in-memory) local database table, execute your inserts quickly into that table, then have another process that reads this table intermittently (or have triggers setup to watch for new entries), and have that process take the new inserted rows from that table and move them into the slower main table.