2

We are building a SaaS backend for restaurants using Rails. We integrate directly with POS, so each POS keeps sending customer orders that we store for later processing. we have this POS integration going at about 1,000 locations which send us about 3 Million individual customer orders on monthly basis. for this write-heavy app, we store all orders in redis which is working beautifully. We are growing at incredible pace, we keep adding new restaurants with hundreds of locations that keep sending us crazy amount of data. Except there is one problem -- redis keeps running out of memory every month! As, everything which doesn't have to be in memory is in memory.

This is why we contemplating to switch to mysql. As we really don't need to keep all data in memory. here are we numbers of current redis database:

  used_memory_human:39.83G 
  dbsize: 34706870

Here is what we store in redis as Hash:

id - integer
location_id - integer
stored_at  - timestamp
token - string
transaction_no - integer
menu_items - string(comma seprated list of all menu items that customer ordered along with their price & Qty)
order_amount - decimal
order_subtotal_amount - decimal
order_amount_payable - decimal
order_datetime - timestamp
employee_id - integer
employee_name - string
pos_type - string
post_version - string
restaurant_id - integer

So, looking for some advice on:

  1. moving from redis to mysql is good idea? how will it effect us in long run as we will need to keep updating our indexes & partition scheme to cater to huge demand.

  2. What other databases(relational or non-relational) would be suited for this use case than redis?

  3. Or we are all wrong, as redis is made for storing this type of data. so, we just keep using redis & upgrading our machines every month?

Community
  • 1
  • 1
CuriousMind
  • 33,537
  • 28
  • 98
  • 137

4 Answers4

2

Data on the web is bound to grow. Any long-term project should anticipate this, and have a strategy for scaling.

As your volume of data or volume of traffic increases, you will find that approximately every order of magnitude growth requires changes to your architecture to handle it. Maybe you can be ahead of the curve a bit, but not forever. And you can't predict where your bottlenecks will be very far in advance.

It's common for a small subset of your data to be important for minute-to-minute work of your app, and you can keep this subset in Redis to take advantage of your current code. Then the rest of the data can be available in another data store, perhaps a bit slower to access, but much easier to handle growth.

You could scrap your current code and move everything to MySQL or another datastore, but keep two things in mind:

  • There is no database that will allow you to neglect having a scaling strategy. You could use MySQL, or PostgreSQL, or MongoDB, or Hadoop, or anything else, and you will still have the problem that your data is growing faster than a single database on a single server can handle.

  • It's generally not cost-effective to rewrite your app from the ground up for internal reasons of more efficient development or operations (read Things You Should Never Do, Part I by Joel Spolsky).

I'd recommend keeping your Redis app, but try to move historical data to another datastore.

I think MySQL is a fine choice, I'm sure it would be capable of handling your data. I work with clients regularly who keep terrabytes of data in MySQL, and handle tens of thousands of transactions per second. But since you haven't given any details about your usage of data, I can't offer an opinion about whether MySQL is the best choice. It could be Hadoop would have advantages, for example.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

moving from redis to mysql is good idea? how will it effect us in long run as we will need to keep updating our indexes & partition scheme to cater to huge demand.

My vote is moving off of Redis is probably a good idea if you're concerned about the cost of hosting due to the necessity of keeping all data in memory. This doesn't have to involve moving all the data off of Redis, perhaps just the historical "colder" data where you care less about latency. The other advantage of moving the cold data off Redis is that any bugs that are found during the migration are likely to have a less significant impact.

What other databases(relational or non-relational) would be suited for this use case than redis?

This is a tough question to answer without better understanding your use case. That said I think any number of scalable relational DBs are probably good enough for your workload. A key requirement in my mind would be the ability to easily add/remove machines to scale as needed. A personal favorite is CitusDB but there are various options.

One trade-off to be aware of when moving to a relational database is that you'll potentially have more work to do when managing structured data then you would with Redis key/value store. For example, adding new fields could involve schema changes. PostgreSQL (and CitusDB) have support for some semi-structured data types which make this easier, I'm sure there's other relational databases that have similar features.

Ben
  • 136
  • 4
0
  • If mysql (or any other traditional Database) would suffice why did you go for Redis in the first place?
  • "we store for later processing" is vague. Can you please elaborate on this? I assume, this later processing is an Analysis kind of activity for which latency doesn't really matter and only throughput matters, right? If that's the case Redis was an overkill don't you think?
  • Have you consider compressing the data before dumping it to Redis.

From what I understood from your question is, your data is always structured, your READ is non-real time, "Durability" matters to you than the latency. If all of this assumption is correct, mysql is a safe choice. If you ever hit WRITE bottleneck you can think about Sharding.

This thread will give you a fair idea. Can redis fully replace mysql?

Always keep in mind that most of the NoSQL solutions(including Redis) are fast because they trade ACID properties for speed. But here, in your case, from what I understood, ACID properties matters more.

Community
  • 1
  • 1
KingJulien
  • 303
  • 3
  • 9
0

With the upcoming 3.0 of Redis, the cluster functionality will be ready for production. Have a look a http://redis.io/topics/cluster-tutorial to get an overview. This will not directly help concerning the growing data volumes, but I assume this could make scaling/sharding easier for your setup.

What you also could consider is to move "old" data from Redis to another system, for example ElasticSearch with the help of a Redis River:

Compression using MessagePack could also be an option:

Community
  • 1
  • 1
Tobi
  • 31,405
  • 8
  • 58
  • 90