Ratchet Websocket async mysql best practice

Question

I've build a Websocket chat based on ratchet that uses reactphp async mysql and just got a couple of questions to make sure I'm doing things right, as I couldn't really find any examples for that case out there.

The use case of the Socket is a livechat that handles many different pages at the same time, about 20 pages with 1000+ Users. Actually this works for now with 100 users, but I have doubts about the database connection to block or queue too many queries with more than thousand users at the same time. I need to make sure the server can handle everything fast.

Ratchet: https://github.com/ratchetphp/Ratchet

Mysql: https://github.com/friends-of-reactphp/mysql

So, the Socket will be used by different applications, each of them has it's own database where the data has to be stored and published, which means there are multiple Lazy connections created, this is done by the Ratchet Pusher. There is one database locally on the Websocket server that stores all connected interfaces in one table, out of there the connections are distributed.

The push-server starts the event loop and passes the mysql factory to the pusher constructor (push-server.php):

require dirname(__DIR__).'/websocket/vendor/autoload.php';

use React\MySQL\Factory;
use React\MySQL\QueryResult;

$loop = React\EventLoop\Factory::create();
$factory = new Factory($loop);
$pusher = new websocket01\Pusher($factory);

$webSock = new React\Socket\Server('0.0.0.0:8090', $loop);
$webServer = new Ratchet\Server\IoServer(
    new Ratchet\Http\HttpServer(
        new Ratchet\WebSocket\WsServer(
            new Ratchet\Wamp\WampServer(
                $pusher
            )
        )
    ),
    $webSock
);

$loop->run();

The Pusher then creates a lazy connection for each connected Interface in the constructor, and thats my second question: Is it better to create just one async db connection at all and keep it alive (like now), or is is better to reopen the connection at every client interaction ?

(Pusher.php):

protected $connection;
protected $subconnections = array();

public function __construct($factory){
    //Main Websocket db
    $uri = 'localhost...';
    $this->connection = $factory->createLazyConnection($uri);

    //Create array with Connections to Sub dbs for every entry in the interface table
    $stream = $this->connection->queryStream('SELECT * from interfaces');

    $stream->on('data', function ($interface) use ($factory) {

            if (!array_key_exists($interface['interface_dbhost'], $this->subconnections)) {
                $uri = "".$interface['interface_dbuser'].":".$interface['interface_dbpass']."@".$interface['interface_dbhost'].":".$interface['interface_dbport']."/".$interface['interface_dbname']."";

                $this->subconnections[$interface['interface_dbhost']] = $factory->createLazyConnection($uri);
            }
    });
    $stream->on('end', function () {
        echo 'Completed.';
    });
}

The main (local) database will still be used to save clients data, errors, verify tokens etc.

The right database connection will then be chosen in onopen, onsubscribe, onpublish... automatically out of the subconnections array, depending on the application the client comes from, and the action he wants to perfom, for e.g:

public function onPublish(ConnectionInterface $conn, $conversation_id, $event, array $exclude, array $eligible) {
    $obj = json_decode($event);
    $interface = $obj->frontend;
    $channel = $obj->channel;

    $db = $this->selectDatabase($conversation_id, $interface); //Choose right database connection
    $clienthandler = new Clienthandler\Clienthandler($this->connection);

    $action = $this->processRequest($db,$obj, $conversation_id, $clienthandler, $operatorhandler, $channel);
}

I'm also passing the local db connection to the clienthandler too, as I need it there to log actions.

public function selectDatabase($conversation_id, $interface) {
        if(array_key_exists($interface, $this->subconnections)) {
            $dbconn = $this->subconnections[$interface];

            $dbconn->ping()->then(function () {
                echo 'Connection alive' . PHP_EOL;
            }, function (Exception $e) {
                echo 'Error: ' . $e->getMessage() . PHP_EOL;
            });
        }
    }
    return $dbconn;
}

The clienthandler then saves the data and broadcasts it to all subscribers

I'm open for any advice :-)

Thank you for your help

Ratchet Websocket async mysql best practice

0 Answers