run multiple DB queries at once

[mafeijam]

Hi there,
I am trying to use this package to run multiple DB queries at once.
but it makes no difference to run the queries one by one.
both take the same amount of time to finish all queries.
here is the code
can you please let me know how can I use it to run multiple DB queries faster?

<?php

require 'vendor/autoload.php';

use Spatie\Fork\Fork;

function profile($fn) {
    $start = microtime(true);
    $fn();
    dump(microtime(true) - $start);
}

class Db
{
    protected $pdo;

    public function __construct()
    {
        $this->connect();
    }

    public function connect()
    {
        $this->pdo = new PDO('mysql:host=host;dbname=dbname', 'user', 'pw');
    }

    public function q($sql)
    {
        return serialize($this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC));
    }
}

$db = new Db;

profile(fn () => Fork::new()
    ->before(fn () => $db->connect())
    ->run(
        fn () => $db->q('select * from table_1'),
        fn () => $db->q('select * from table_2'),
        fn () => $db->q('select * from table_3'),
        fn () => $db->q('select * from table_4'),
        fn () => $db->q('select * from table_5')
));

profile(function () {
    $db = new Db;
    $db->q('select * from table_1');
    $db->q('select * from table_2');
    $db->q('select * from table_3');
    $db->q('select * from table_4');
    $db->q('select * from table_5');
});

[brendt]

I believe your queries are so small that you don't observe any difference. Keep in mind that running a function as a separate process also comes with an overhead of setting up that process.

[mafeijam]

the actual queries are more complicated
each takes some amount of time to run, around 0.5 ~ 1s each
I was expecting the queries to be finished in 1s instead of 3~4s

[brendt]

3-4 seconds is still very little difference, you should try it out with 20-30 queries.

It could be that something's not working, but you'll have to do some debugging on your machine.

• Is pcntl properly installed?
• Are the processed properly forked?

[mafeijam]

yes, pcntl is installed otherwise I won't be able to run the code
if I try select sleep(1) 5 times, it will take just 1s

it seems like when the queries return large data set, it will get blocked

[RemcoSmitsDev]

This is because there is an issue with code thats inside write, read methods in Connection.php class.

The following check inside write method is the main reason why its blocking when processing large data. There are some more changes but i don't think that you want to make them. Only your socket connection can stay longer open than usual/that you want.

if ($selectResult <= 0) {
   break;
}

I changed the following methods write, read to the code below and it was fixed.

public function write(string $payload): self
    {
        // set nonblock mode
        socket_set_nonblock($this->socket);

        while ($payload !== '') {
            // select socket
            $selectResult = $this->selectSocket();

            // check if there was something wrong when selecting the socket target
            if ($selectResult === false) {
                break;
            }

            // get payload length
            $length = strlen($payload);

            // write socket data to child
            $amountOfBytesSent = socket_write($this->socket, $payload, $length);

            // check if all the payload data was sent
            if ($amountOfBytesSent === $length) {
                break;
            }

            // strip payload until payload is empty
            $payload = substr($payload, $amountOfBytesSent);
        }

        // return self
        return $this;
    }

    public function read(): \Generator
    {
        // set nonblock mode
        socket_set_nonblock($this->socket);

        while (true) {
            // select socket
            $selectResult = $this->selectSocket();

            // check if there was something wrong when selecting the socket target
            if ($selectResult === false || $selectResult <= 0) {
                break;
            }

            // read output from socket connection until all information was readed
            $output = socket_read($this->socket, $this->bufferSize);

            // check if all information was read
            if ($output === '') {
                break;
            }

            // yield output
            yield $output;
        }
    }