Recently we've been hearing a lot about Redis, I don't believe its anything that's new, at least not in the tech world, but we've recently been noticing it a bit more so have been looking into it for some time, but never found a particular use case for it, at least not in the current services we provide. However, a recent project has made me think, hmm, I wonder if Redis would be better suited here than using a MySQL database, so we decided to do a litle experiment and see what impact it could have on performance. You can find more information about Redis by [clicking here](https://redis.io/topics/introduction)

What is Redis?
==============

Before we get into our little experiment, lets first of all give some brief details about what Redis is.

Redis is an open source in memory data structure used as a database, cache and message broker. It supports the storage of various different data types, and from our understanding, its primarily used as a way of caching content that can be retrieved later quickly and efficiently, therefore offloading some of the workload of your database servers such as MySQL.

Redis is not a replacement for a MySQL database, although Redis does support data persistence its not really designed as a proper database and kind of work together side by side with a normal more convential style database.

What are we trying to solve with Redis?
=======================================

We're currently working on a project, don't want to give too much details yet, but it is something we plan on releasing in the not too distant future as an early access preview, but it consists of multiple different components. It consists of a web frontend, API (used by the web frontend) and a C++ app, we call the engine which also hosts an internal HTTP REST API, all of this is uses a MySQL database backend for data storage and processing. In this experiment, the web frontend and web API isn't really where this comes into, but more the C++ engine and its own internal REST API and data processing that it does.

The idea of the service, is a user initialises using their API key by sending us an initialisation request received by the engines REST API, the API key is queried in the database, and if successful, an OK response is returned, after which the user can keep sending us POST data, that we receive again through the engines REST API, process and store in the DB. When we receive the request data though, we want to check a counter on the user to ensure a monthly quota hasn't been reached, and then increment this counter for every request received. Lets forget about the initialisation side of things, as that's not where the issue is, but from the data processing side, its doing a lot of DB work. Below is the flow it might take:

*   Receive HTTP request
*   Check users monthly quota in DB
*   If monthly quota is below threshold process and store the data in the database
*   Increment the users monthly quota counter via DB.
*   Session expires - nothing to do

The above might not seem like much of an issue, but if we're receiving several thousands requests a second (which is possible) that is a lot of work and overhead on the database to query, store, and update for every single request we received, and we thought, as the service gets more and more users, this is potentially going to slow down more and more as the database grows.

Thinking of this, we thought about the idea of using Redis to store the counter for the user and then the only database work that is required is the storage of the processed data, and updatingthe final request count when the user session expires.

So the process using Redis would be as follows, again excluding the initialisation, at the initialisation stage, we would create a redis key which would be some sort of identifier for the user.

*   Receive HTTP request
*   Check users monthly quota in Redis
*   If below monthly quote store record in DB
*   Increment the quota counter in redis
*   Session expires - flush the redis quota counter to the DB

As you can see above, the number of steps is exactly the same, but instead of querying the DB and updating the quota in the DB for every request, we query Redis instead and only update the quota in the DB when the session expires using the counter from redis.

Testing performance benefits
============================

We decided to use PHP in order to set up a test, just for simplicity, but it shouldn't make any difference as to whether we're accessing MySQL/Redis from PHP or C++, the performance hit is within the DB itself not the language.

First of all, we created two MySQL databases that would sort of replicate the workings of our service. One table that has a column with the user ID (we called OrganisationID) and the counter, and a second table called storage, which contains some random data, such as a record ID, timestamp and the index of what we're inserting.

The MySQL create statements are as follows:

    CREATE TABLE `counter` (
      `OrganisationID` int(11) NOT NULL,
      `counter` int(11) NOT NULL,
      PRIMARY KEY (`OrganisationID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    

    CREATE TABLE `storage` (
      `StorageID` int(11) NOT NULL AUTO_INCREMENT,
      `DateTimeReceived` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `index` int(11) NOT NULL,
      PRIMARY KEY (`StorageID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    

We then created two PHP scripts, one that uses soley the MySQL database, and the other that uses Redis.

The basic idea of both is to check the quota value, if below the threshold (we selected 5000) insert a record to the DB, increment the counter and then exit and show the time it takes for the whole process to run, and the average time it takes to the storage (the storage including the quota check, storing a record, and increment the quota).

Below is MySQL version of the PHP script:

Don't worry about the DBHelper and DateTimeManager classes, they are just internal class function we use as helper methods for things we need on a regular basis in our internal web library

    <?php
    set_time_limit(0);
    require_once 'DBHelper.php';
    require_once 'DateTimeManager.php';
    
    $storageThreshold = 5000;
    
    $dbHelper = new DBHelper();
    $conn = $dbHelper->mysqlIconnectToDB("localhost", "letmein", "changeme", 3306, "redis_test");
    
    resetDB($conn);
    
    $storageResponseTimes = array();
    
    //Get the start time
    $processStartTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
    
    //Check if the current storage counter is over the threshold
    
        for ($i = 0; $i <= $storageThreshold; $i++)
        {
            if (checkIfOverStorageThreshold($conn, $storageThreshold))
            {
                $stopProcessTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
                $timeDifference = $stopProcessTime - $processStartTime;
                echo "Time Taken for whole process to Complete: $timeDifference ms\n";
    
                //Calculate the average response times
                $currentAverage = 0;
                foreach ($storageResponseTimes as $time)
                {
                    $currentAverage += $time;
                }
                $currentAverage /= count($storageResponseTimes);
                echo "Average Storage Response Time: $currentAverage ms\n";
    
                exit();
            }
            else {
                $storageStartTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
                addRecordToDB($conn, $i);
                incrementOrganisationID($conn);
                $storageEndTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
                $storageTimeDifference = $storageEndTime - $storageStartTime;
                $storageResponseTimes[] = $storageTimeDifference;
            }
        }
    
    
    /**
     * @param mysqli $conn
     * @param $storageThreshold
     * @return bool
     * @throws DBException
     */
    function checkIfOverStorageThreshold($conn, $storageThreshold)
    {
        $query = "SELECT * FROM counter WHERE OrganisationID='1'";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    
        $myrow = $result->fetch_array();
        $counter = abs($myrow["counter"]);
        if ($counter >= $storageThreshold)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
    /**
     * @param mysqli $conn
     * @param $index
     * @throws DBException
     */
    function addRecordToDB($conn, $index)
    {
        $query = "INSERT INTO storage VALUES (NULL, default, '$index')";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    }
    
    /**
     * @param mysqli $conn
     * @throws DBException
     */
    function incrementOrganisationID($conn)
    {
        $query = "UPDATE counter SET counter=counter+1 WHERE OrganisationID=1";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    }
    
    /**
     * @param mysqli $conn
     * @throws DBException
     */
    function resetDB($conn)
    {
        $query = "TRUNCATE storage";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    
        $query = "UPDATE counter SET counter=0 WHERE OrganisationID=1";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    }
    

And the Redis version is below

    <?php
    
    set_time_limit(0);
    require_once 'DBHelper.php';
    require_once 'DateTimeManager.php';
    
    $dbHelper = new DBHelper();
    $storageThreshold = 5000;
    
    require 'Predis/autoload.php';
    
    
    
    Predis\Autoloader::register();
    
    $client = new Predis\Client();
    
    $client->set("counter", "0");
    
    $conn = $dbHelper->mysqlIconnectToDB("localhost", "letmein", "changeme", 3306, "redis_test");
    
    resetDB($conn);
    
    $storageResponseTimes = array();
    
    //Get the start time
    $processStartTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
    
    for ($i = 0; $i <= $storageThreshold; $i++)
    {
        if (checkIfOverStorageThreshold($client, $storageThreshold))
        {
            flushCounterToDB($conn, $client);
            $stopProcessTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
            $timeDifference = $stopProcessTime - $processStartTime;
            echo "Time Taken for whole process to Complete: $timeDifference ms\n";
    
            //Calculate the average response times
            $currentAverage = 0;
            foreach ($storageResponseTimes as $time)
            {
                $currentAverage += $time;
            }
            $currentAverage /= count($storageResponseTimes);
            echo "Average Storage Response Time: $currentAverage ms\n";
    
    
            exit();
        }
        else {
            $storageStartTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
            addRecordToDB($conn, $i);
            incrementOrganisationID($client);
            $storageEndTime = DateTimeManager::getEpochFromCurrentTime() * 1000;
            $storageTimeDifference = $storageEndTime - $storageStartTime;
            $storageResponseTimes[] = $storageTimeDifference;
        }
    }
    
    /**
     * @param \Predis\Client $client
     * @param $storageThreshold
     * @return bool
     */
    function checkIfOverStorageThreshold($client, $storageThreshold)
    {
        if (intval($client->get("counter")) >= $storageThreshold)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
    /**
     * @param \Predis\Client $client
     * @throws DBException
     */
    function incrementOrganisationID($client)
    {
        $client->incr("counter");
    }
    
    /**
     * @param mysqli $conn
     * @param $index
     * @throws DBException
     */
    function addRecordToDB($conn, $index)
    {
        $query = "INSERT INTO storage VALUES (NULL, default, '$index')";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    }
    
    /**
     * @param mysqli $conn
     * @param \Predis\Client $client
     * @throws DBException
     */
    function flushCounterToDB($conn, $client)
    {
        $counter = intval($client->get("counter"));
    
        $query = "UPDATE counter SET counter='$counter' WHERE OrganisationID='1'";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    }
    
    /**
     * @param mysqli $conn
     * @throws DBException
     */
    function resetDB($conn)
    {
        $query = "TRUNCATE storage";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    
        $query = "UPDATE counter SET counter=0 WHERE OrganisationID=1";
        $result = $conn->query($query);
        if (!$result)
        {
            throw new DBException(mysqli_error($conn));
        }
    }
    

Hopefully the scripts are fairly straight forward, in the MySQL version, we solely use MySQL to select the quota counter from the counter table, insert a record into the storage table, increment the quota in the counter table. In the redis version the same thing, except we get the quota counter from redis, store the record to the DB and then increment (using the incr method) the quota and then when all records have been added, we flush the quota counter to the counter table in the DB. For the redis library we used the Predis PHP Redis client library which can be found on [GitHub](https://github.com/nrk/predis).

Each script at the start, resets the DB by truncating the storage table, and resetting the counter in the counter table back to 0. We then ran each script 5 times and recorded the total processing time (i.e. time for the script to complete execution) and the average storage processing time which is outputted by the script in an Excel spreadsheet.

Below was the result.

**Whole Processing Time**  
![Whole-Processing-Time-1](/api/images/10)

**Storage Processing Time**  
![Storage-Processing-Time](__GHOST_URL__/content/images/2018/09/Storage-Processing-Time.png)

As you can see from the graphs above, using Redis to store and query the quota for each HTTP request, instead of doing it in the DB is a lot quicker, in fact, on average, over the 5 runs of each script that we did we saw a 51.4% reduction in both the whole process to complete and the time to store each record into the DB.

From this test, I think that means that we'll most likely be using redis for this part of our service, and possibly other areas as well.

If you have any comments about our test or if you have your own insights in to you using Redis for your own projects, we'd love to hear them so leave a comment below.

Small experiment to determine the performance benefits in using redis for a particular use case instead of using a MySQL database. 

Devso Blog

Tagged

software

MySQL Database Vs Redis