How to Migrate Write Queries Away From MySQL to Redis In a High-Traffic Application?

Question

I'm developing a high traffic ad serving platform for some years now, using a master-master Maria DB cluster with an HAProxy in front for balancing relational data queries (read queries go to all of the servers, but writes only go to one, to prevent the servers from going out of sync). By relational data I mean things like campaign settings, user details, payments. I'm also using Redis for caching some of the less dynamic MySQL information, but I believe there are a lot of opportunities to make better use of it, since as soon as the traffic increases, I'm frequently hitting bottlenecks like:

• too many connections to MySQL
• deadlocks (possibly because writes start coming on multiple servers when the main one gets overloaded).

My goal is to move as much of the writes away from MySQL and into Redis, but I'm having a hard time filtering MySQL data based on the counts/budgets stored in Redis, especially in places where a traditional JOIN would be used.

A simplified example of such MySQL query that would get the campaign with the highest bid within the user's budget:

SELECT campaigns.id, campaigns.url FROM campaigns
JOIN users ON campaigns.user_id = users.id
ORDER BY LEAST(users.credits, campaigns.bid) DESC
LIMIT 1;

After a click is delivered to that campaign, a budget reduction is immediately needed. Of course, reducing the credits in MySQL is trivial, but as soon as a user starts sending multiple clicks per second, the problems start appearing (mainly deadlocks in a cluster or reaching the maximum number of connections).

Applying a credit reduction in Redis would be preferred, but I have troubles connecting the dots between a bunch of credit records in Redis and filtering and sorting MySQL records based on that.

What would be a good approach to this problem that will allow me to touch MySQL as little as possible? Or maybe there is a fully different approach I need to take for this to happen.

Any advice or links will be much appreciated.

Answer 1

I would not recommend to move all write requests to Redis, especially for data with strong consistency(like payments).

Redis is a in-memory database, which do not have ACID transaction guarantee like MySQL. So you data still have some chances to be lost after write to Redis even if you have AOF enabled, which can make your data inconsistent.

For you case I thing you can integrate message queue(Kafka, rabbitMQ) to avoid connection issues and deadlocks:

1. When transaction occurred, serialize the request with data to write and send to message queue.
2. MySQL will listen on MQ with a fixed consume rate(based on your need), and write the data into MySQL sequentially(and rewrite to Redis if you need cache)
3. For client side, you can have a thread to query the result in an infinite loop until write finished. This will make the async write performs like sync.

In this case, you will avoid resouces compete(like deadlocks), and will also smooth the write rate by a fixed consuming rate.