CODEWITHSUNDEEP
phpmysqlredis
kouton
kouton

Reputation: 1961

Redis as a cache for MySQL writes

I'm looking to add a runtime config for my app which when toggled on will redirect all writes from MySQL to Redis. A different script will poll Redis to make these inserts to MySQL at a later time, manually.

I need this for times when we don't want any updates to the MySQL DB (a write request on a Slave machine, DB downtime, etc).

A typical request to our app would result in ~ 3 insert queries and contain data such as User submitted data, IP address, date/time.

I've considered 3 options, listed below in the order of simplicity (for me):

1.Store raw SQL for the insert in a Redis list, and 2nd script will pop these out and carry out inserts to MySQL

INSERT INTO DB.TABLE (col1, col2) VALUES (val1, val2)

2.Store JSON encoded array of variables necessary for insert along with data (columns => values), DB and Table name. The 2nd script just needs to explode these values as columns and values and carry out insert.

{
"db":"DB",
"table":"table",
"data":[
  {
  "col1":"val1",
  "col2":"val2"
  }
 ]
}

3.Store all variables required from the app in a Hash object in Redis. 2nd script goes through the complete logic of getting an insert query.

{
"ipAddress":"127.0.0.1",
"requestType":"hmm",
"variable2": "a"
...
}

While the 1st seems the least painful, it doesn't seem right in that it appears too hacky and..unsafe. Will there be lost / malformed data by storing it this way?

What do you recommend I do?

Upvotes: 2

Views: 3377

Answers (1)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230521

When I need my writes to tolerate temporary DB downtime, I pass them through a background job queue.

  1. Frontend receives a request from user and figures out that it needs to make a write.
  2. That write is pushed into a queue
  3. Later on, a background worker pops data about that write and tries to execute it. Now, if the DB is down, it'll simply re-schedule the write (or not ACK it, depending on capabilities of the queue).

This most closely resembles your option #3, I think.

Note that this scenario means that all writes always go through the queue. Alleviates the need for manual intervention. When main DB goes down at 4 AM, you don't expect to wake up and change app config, all in under a second?

Upvotes: 2

Related Questions