Simple PHP / SQL chat setup

user_8823168 0
Viewed 1231

I have a basic chat system set up that uses an SQL database and a PHP script -- when the user inputs a message, its sent to the database and then is retrieved and displayed. New messages are displayed every 5 seconds regardless.

All that being said, its fairly easy to just spam messages causing the website to stop responding at which point clicking any links will result in an error page, and no further messages will be input.

Is this a common scenario? How should I improve the chat's performance? Note: I'm really new PHP and JS/Jquery.

Here is the main script that is frequently called to update the html chatbox with new messages for the logged-in user:

Two auto-incremented values are compared to determine "new messages", the value of the last displayed message, and the value of the last message in the database.

<?php

    session_start();
    if (isset($_SESSION['logged_in']) && $_SESSION['logged_in'] == true) {
    $alias = $_SESSION['username'];

    $host = 'localhost';
    $user = 'root';
    $pass = '';
    $database = 'vethergen_db_accounts';
    $table = 'table_messages';
    $user_table = 'table_user_info';
    $last_id_table = 'table_chat_sync';
    $connection = mysqli_connect($host, $user, $pass) or die ("Unable to connect!");
    mysqli_select_db($connection,$database) or die ("Unable to select database!");


    if ($redis->exists("/lastId/$alias")) 
    {
        $last_id = $redis->get("/lastId/$alias"); //Gets the last id from cache...
    } 
    else 
    {
        $last_id_query = "SELECT last_id FROM $last_id_table WHERE alias = '$alias'";
        $last_id_result = mysqli_query($connection,$last_id_query);
        $last_id_rows = mysqli_fetch_array($last_id_result);
        $last_id = $last_id_rows['last_id'];

        // Now that you just read it, create a last_id cache entry for this user
        $redis->set("/lastId/$alias", $last_id);
    }

    $query = "SELECT * FROM $table WHERE text_id > '$last_id'"; //SELECT NEW MESSAGES
    $result = mysqli_query($connection,$query);



    if ($result && mysqli_num_rows($result) > 0)
    {
        while($row = mysqli_fetch_array($result))
        {
            $color_alias = $row['alias'];
            $text_color_query = "SELECT color FROM $user_table WHERE alias = '$color_alias'";
            $text_color_result = mysqli_query($connection,$text_color_query);
            $text_color_rows = mysqli_fetch_array($text_color_result);
            $text_color = $text_color_rows['color'];
            if ($row['alias'] === "Vether")
            {
                echo '<p id = "chat_text" style="color:'.$text_color.'">'.'<b>'.$row['alias'].': '.'</b>'.$row['text']."</p>";
                echo '<p id = "time_stamp">'.$row['time'].'</p>';
                echo '<p id = "chat_number">'.$row['text_id'].'</p>';
            }
            else
            {
                echo '<p id = "chat_text" style="color:'.$text_color.'">'.'<b class = "bold_green">'.$row['alias'].': '.'</b>'.$row['text']."</p>";
                echo '<p id = "time_stamp">'.$row['time'].'</p>';
                echo '<p id = "chat_number">'.$row['text_id'].'</p>';
            }
            echo '<hr class = "chat_line"></hr>';
            $last_row_id = $row['text_id'];

        }

        //UPDATE LAST SYNC ID
        $update_query = "UPDATE $last_id_table SET last_id = '$last_row_id' WHERE alias = '$alias'";
        $redis->delete("/lastId/$alias");
        mysqli_query($connection,$update_query);

    }
    else {echo '';}
 ?>
chat php javascript
2 Answers

You can add a Limit to the end of SELECT * FROM $table WHERE text_id > '$last_id' and that will keep some of the spam messages from slowing down the thread. Also you can prohibit duplicates on the INSERT statement.

user_79495270

There is no specific right answer because your question is very general, but there are a few things that are obvious here. You have built a botteneck in your database where the more users you have, the more updates you are doing on the table_chat_sync.

As an aside, I have no idea why you are putting a constant (the table name) into PHP variables for your queries. At very least these should be php constants but that makes the syntax pretty painful. Your code is simpler and better just using the table names in the SQL.

InnoDB

Are you using InnoDB tables? You should be, given that you are updating a row and with InnoDB you have row level locking.

You also want to make sure that you have enough innodb buffer pool cache allocated to insure that the db is in memory. This will buffer your select activity a lot and buy you some head room.

MySQL EXPLAIN

You also need to do explain plans on your select query and insure that it is properly indexed so that the queries are being returned using indexes and you are not table scanning or having temporary tables created.

SQL queries against mysql are quite slow compared to getting data from cache, and the reality is that the full set of chat messages doesn't change much, and yet your system is repeatedly going to be querying the chat or a subset of it over and over again. For this reason, most sophisticated systems are using some sort of caching system or queuing. There is overlap between these two technologies and they tend to offer better scalability as well as support for concepts like publish/subscribe that fit chat very well.

Redis & Other backends

Redis as an example, could be the back end for chat and completely supplant the actual storage and retrieval of messages. The document database MongoDB is also an alternative option that has in-memory characteristics when the dataset can be controlled.

Using Redis with MySQL Redis is often combined with an RDBMS and in your code there are a few places where it could be a great help. For example, you do this query repeatedly:

$last_id_query = "SELECT last_id FROM $last_id_table WHERE alias = '$alias'";

With redis you could do something like this:

if ($redis->exists("/lastId/$alias")) {
    $last_id = $redis->get("/lastId/$alias");
} else {
    $last_id_query = "SELECT last_id FROM $last_id_table WHERE alias = '$alias'";
    $last_id_result = mysqli_query($connection,$last_id_query);
    $last_id_rows = mysqli_fetch_array($last_id_result);
    $last_id = $last_id_rows['last_id'];
    // Now that you just read it, create a last_id cache entry for this user
    $redis->set("/lastId/$alias", $last_id);
 }

The only other detail is that when you update the last Id you would want to delete the redis key:

 $redis->delete("/lastId/$alias");

Hopefully you can see that this would lower the load on mysql quite a bit, because no query will occur without a new message being added. This will buffer mysql quite a bit, and the same concept can be used to cache the other queries you are doing, such that you never require mysql queries unless you have a new user actively using Redis. I didn't go into this but you can set the expiration of a key to some period of time, so it will clean up old keys from non-active users.

Load Testing to understand your bottlenecks and capacity

Your choice of reliance on MySQL is something you will have to accept as limiting, although again you may be able to tune it so that within your use case and load, it runs acceptably, but that is impossible to predict without detailed configuration analysis and load testing. There are many load testing and stress testing tools that are FOSS, with Apache JMeter being one of the oldest ones, so I'll advise you to start with that.

Websockets

Last but not least, polling is inherently wasteful and most chat systems these days are built using websockets which is just a better fit for the task of having a sustained client-server connection. Websocket is client & Server code, and being that you are a PHP dev, there are a few projects that can help you out here, Ratchet being one that has been around for a while. There's a PHP client lib Pawl that shows you how to make a simple robust websocket connection.

user_8600340
Related