How does performance work on a PHP websocket with DB connection?

Question:

CONTEXT. I have the web server on one IP and the database (BD) on another server with another IP. I have a PHP (WS) websocket built with the Ratchet library. The idea is that the browser shows updated information second by second based on the existing data in the DB. I configured the WS so that every second the client (through JS) sends a message to the server in order to retrieve the most recent data from the DB and display it in the browser:

let conn = new WebSocket("ws://localhost:8080/");
conn.onopen = function (e) {
    setInterval(function () {
        conn.send('enviopeticion');
    }, 1000);
};

Everything works perfectly in local development mode. But I suspect that I am not doing things right and that its transition to production will be a disaster with respect to performance, precisely because of the following.

TROUBLE. Inside the onMessage() method on the server side (in my PHP class) I declare the new connection to the DB and execute the requests to the DB:

public function onMessage(ConnectionInterface $from, $msg) {
    $res = array();
    $pdo = new PDO($dsn,$db_user,$db_pass,$db_attrs);
    $stmt = $pdo->prepare('SELECT id FROM tabla WHERE col=? LIMIT 1');
    if ($stmt->execute([1])) {
        if($stmt->rowCount()>0) {
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $res = array('id'=>$row['id']);
        }
    }
    $stmt->closeCursor();
    $pdo = NULL;
    $from->send(json_encode($res));
}

I assume that I should not open the connection to the DB $pdo = new PDO() inside the onMessage() method, because in that case I would be opening and closing the connection to the DB in each message sent in the WS, and as the messages are sent every second between the client and the server, the connections to the DB would be infinite. This is so, correct?

I suppose that the connection to the DB I have to put it outside the onMessage() method, I assume that in the onOpen() method, with which I would achieve that there is only one connection to the DB, correct? But this causes me two problems:

  1. The site in the browser works fine with this configuration, but if I reload the page, the websocket connection crashes indicating that there is no connection to the DB, and the browser shows a 500 error
  2. I never get rid of the multiple requests to the DB: $stmt->execute() , and since the DB is on another server, that makes me suppose that the websocket is not much use when the web server is on one side and the of BD in another, is that so? If it is, what would be the best way to not affect performance here?

Any comment you can give to clarify my doubts and the functioning of the WS will be of great help to me.

EDIT 1. Just now I just separated the DB connection from the onMessage() method (got it from there), and put it inside the constructor and inside the onOpen() method, which makes it so that there is only one connection to the DB during the life of the WS. That problem is fixed. The problem remains that infinite requests will be made to the DB:

$stmt = $this->pdo->prepare('SELECT ...');
if ($stmt->execute()) {
    if($stmt->rowCount()>0) {
        // ...
    }
}
$stmt->closeCursor();

Answer:

The socket server should only receive and send messages, leaving the client to be in charge of managing the database. I suggest you review the Ratchet Getting Started tutorial again.

Also, the socket will be listening for any changes to the server, so the interval is not needed.

let conn = new WebSocket("ws://localhost:8080/");
conn.addEventListener('open', e => {
    console.log('Conexión establecida');
    // Solo si quieres enviar un mensaje a los usuarios conectados
    conn.send('Un usuario nuevo ha ingresado');

    // Este evento se asigna solo cuando se pudo establecer la conexión
    conn.addEventListener('message', e => {
        console.log('Mensaje recibido: ' + e.data);
    });
});

When you make changes to the database, let's say you have a form with id="datos" and you listen to the submit event:

document.querySelector('#datos').addEventListener('submit', function(e) {
    // Preparas los datos para envío, incluyendo todos los campos del formulario
    let data = new FormData(this);
    // Envías al servidor (no Websocket) con petición AJAX
    fetch('http://localhost/ruta/archivo.php', {
        method: 'POST',
        body: data
    })
    // Convertir la respuesta del servidor en JSON
    .then(res => res.json())
    .then(response => {
        // Si todo salió bien, el servidor ya actualizó la base de datos
        // Y te devolvió información en formato JSON
        // Hay que enviar al socket, para que otros usuarios reciban el mensaje
        // En tu ejemplo solo envías ID
        conn.send(response.id);
     });
});

The Websocket server will receive the message and send it to the rest of the users, leaving the function as in the tutorial:

public function onMessage(ConnectionInterface $from, $msg) {
    $numRecv = count($this->clients) - 1;
    echo sprintf('Connection %d sending message "%s" to %d other connection%s' . "\n"
        , $from->resourceId, $msg, $numRecv, $numRecv == 1 ? '' : 's');

    foreach ($this->clients as $client) {
        // Evitar enviar al autor del mensaje
        if ($from !== $client) {
            $client->send($msg);
        }
    }
}

If you need all the complete data and not just ID, you can modify the shipment:

conn.send(JSON.stringify(response));

And modify the reception of messages:

conn.addEventListener('message', e => {
    let registro = JSON.parse(e.data);
    // Ya tienes los datos
    // Puedes usarlos para modificar el DOM o mostrar en consola
    console.log(registro.id, registro.fecha);
});

Lastly, for a user to receive only messages that belong to them, you may need to use sessions on the Websocket server , but that's another topic.

Post Views: 3
Scroll to Top