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?

Neutralize

1. you are barking the wrong tree. 2. Even this wrong way you overengineered. What you really want is to make your insert as fast as to make you forget all these romantic ideas. – Subtractive

The insert is very small and cannot be optimized more than it is. The step I wish to avoid is waiting for my server to contact the mysql server. – Neutralize

You could use a cron job for this. Also i think @Your Common Sense makes a good point. – Recusant

That's true, but I'd appreciate an answer that avoids a cron job (I'm trying to spare you guys all the details of this project). I can't be sure that what I'm looking for here is even possible, but it seems like a feature that an asynchronous sql service ought to provide - so if there is such a solution, I'd like to find out about it. – Neutralize

if it's really a ping between servers being your problem, then no async thing obviously will help you – Subtractive

yes it will, because the script can asynchronously continue while the query is pushed to the mysql server. if that isn't true, I have no idea what "asynchronous" means. – Neutralize

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.

Coneflower

after all this time - this is exactly what I was looking for! – Neutralize

Be careful "As of MySQL 5.6.6, INSERT DELAYED is deprecated, and will be removed in a future release. Use INSERT (without DELAYED) instead." – Kerriekerrigan

won't work for InnoDB tables, unsupported by engine as feature – Presignify

any replacement ? – Niveous

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.

Jeanninejeans

if seconds = 0, only 1st query is executed. The rest are ignored. – Niveous