PHP Amp\Mysql async slower than native blocking PDO?

Question

I'm doing some testing with Amp and try to see how it could help speeding up SQL Queries by running them async. I think I'm doing something wrong because the results of this test file are very disappointing and not what I would have expected. Is there something I'm doing wrong?

The code below gives me results like this, first number is Amp\Mysql and it is a lot slower for some reason:

0.37159991264343
0.10906314849854

PHP code:

<?php
require 'vendor/autoload.php';
require 'Timer.php';

$runThisManyTimes = 1000;

///////////////////////////////////////////////////////////

use Amp\Mysql\ConnectionConfig;
use Amp\Loop;

Loop::run(function() use ($runThisManyTimes) {
    $timer = Timer::start();

    $config = ConnectionConfig::fromString(
        "host=127.0.0.1 user=test password=test db=test "
    );

    /** @var \Amp\Mysql\Pool $pool */
    $pool = Amp\Mysql\pool($config);

    /** @var \Amp\Mysql\Statement $statement */
    $statement = yield $pool->prepare("SELECT * FROM accounts WHERE id = :id");

    for ($i = 1; $i <= $runThisManyTimes; $i++) {
        /** @var \Amp\Mysql\ResultSet $result */
        $result = yield $statement->execute(['id' => '206e5903-98bd-4af5-8fb1-86a520e9a330']);

        while (yield $result->advance()) {
            $row = $result->getCurrent();

        }
    }

    $timer->stop();
    echo $timer->getSeconds();

    Loop::stop();
});

echo PHP_EOL;

///////////////////////////////////////////////////////////

$timer = Timer::start();

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'test', 'test');

$statement = $pdo->prepare("SELECT * FROM accounts WHERE id = :id");

for ($i = 1; $i <= $runThisManyTimes; $i++) {
    $statement->execute(['id' => '206e5903-98bd-4af5-8fb1-86a520e9a330']);
    $statement->fetch();
}

$timer->stop();
echo $timer->getSeconds();

Answer 1

Parallel execution of MySQL is not productive when each thread takes less than, say, 1 second.

Each thread must use its own connection; establishing the connection takes some time.

Your particular benchmark (like most benchmarks) is not very useful. After the first execution of that single SELECT, all subsequent executions will probably take less than 1ms. It would be better to use a sequence of statements that reflect your app.

Answer 2

Your benchmark doesn't include any concurrency, so it's basically like blocking I/O in the PDO example. amphp/mysql is a full protocol implementation in PHP, so it's somewhat expected to be slower than the C implementation of PDO.

If you want to find out whether non-blocking concurrent I/O has benefits for your application and you're currently using sequential blocking PDO queries, you should benchmark those against non-blocking concurrent queries using amphp/mysql instead of serial ones.

Additionally, amphp/mysql might not be optimized as much as the database drivers behind PDO, but it allows for non-blocking concurrent queries, which isn't supported by PDO. If you do sequential queries, PDO will definitely have better performance for the time being, but amphp/mysql is very useful once concurrency is involved.