Using redis as intermediary before MySQL insert [closed]

Question

We're writing a chat application, partially in node.js. We're expecting 1000s of inserts (messages) per second, and so our approach has been to write to redis (using an lrange for each room), and also have a list in redis called not_saved_messages that another process loops through and writes to MySQL. Is this an anti pattern? Should we just be writing to MySQL and hope it holds up?

Answer 1

I don't think it is necessarily an antipattern if it is well done. The devil of course is in the details.

What you are trying to do is use mysql for logging and redis for live information, right? It seems to me that might work. The question is how to make the pipeline as a whole function.

BTW, a number of big data users use something very close to this result. CERN for example uses a number of NoSQL solutions during their data processing before the data goes into an rdbms (Oracle, PostgreSQL, or MySQL). But it is definitely advanced design territory.

Answer 2

Instead of inserting the data directly, you can first store it in Redis. Then second process can pick it up from Redis and insert it into the database, one "data portion" at a time. Drawback here is you will need to have enought memory for Redis.

// PHP program code
//...
// assuming $r is already connected Redis() instance.
$r->sadd("data_pipe", serialize($data));

Then,

// Open connection to the database
open_data_connection();

$r = new Redis();
$r->connect("127.0.0.1", "6379");

while(true){
    while($xdata = $r->spop("data_pipe")){
        $data = unserialize($xdata);

        // store the data
        store_data($data);
    }

    // No more data
    sleep(30);
}

If database can not keep up, e.g. insert data as fast as new data come in, you can always start the pick up process twice or more, or to do some kind of sharding, by importing the data in 2-3 databases.

http://redis4you.com/code.php?id=016