Ask HN: Going from Redis to MySQL. Good idea?

#1

Post by gary4gar » Sun, Oct 19, 2014, 3:43 PM UTC

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?

Re: Ask HN: Going from Redis to MySQL. Good idea?

#2

Post by justinsb » Sun, Oct 19, 2014, 4:23 PM UTC

Yes, I think it's a great idea.

First of all, 3 million rows a month is not that much from a database point of view - if evenly distributed (though I'm sure it's not), it would be about 1 per second. (It is impressive to me from a business point of view though). You don't need Redis's throughput, and you are hitting Redis's limits with respect to memory.

1) This is _exactly_ the use case which relational databases were built and optimized for (fixed schema, OLTP, presumably some analytic queries). You might want to normalize the data a little (e.g. the menu_items, employee_name, pos_type & pos_version), but you don't have to - though this would enable faster & richer querying.

On your schema changes: I bet you'll be much happier using a relational database which takes care of this stuff for you, then you will be implementing it yourself in Redis. You probably won't need partitioning either. If you do end up needing partitioning, I would guess your DB is "trivial" to partition by restaurant.

I suspect you'll also find that a relational database stores the data more efficiently, particularly if you normalize a few things. Your data will probably fit into RAM again, but a relational database can cope even when it doesn't.

2) Any relational database would be a great match for this, and would allow rich querying (e.g. by employee, by date, by restaurant). My personal bias is to prefer Postgres.

3) I don't see what Redis is getting you here. It doesn't support rich querying, is memory bound, and you don't need the throughput it promises.

Re: Ask HN: Going from Redis to MySQL. Good idea?

#3

Post by rubiquity » Sun, Oct 19, 2014, 4:34 PM UTC

Riak[0] could also potentially be a good fit for your use case. Unlike Redis, Riak is distributed. I've heard of several companies in the POS space using it.

0 - http://basho.com/riak/

Re: Ask HN: Going from Redis to MySQL. Good idea?

#4

Post by hkarthik » Sun, Oct 19, 2014, 4:43 PM UTC

Any relational DB will work but I would look at Postgres before going with MySQL.

Re: Ask HN: Going from Redis to MySQL. Good idea?

#5

Post by bendmorris » Sun, Oct 19, 2014, 5:19 PM UTC

Why did you choose Redis in the first place?

Redis does have its own (official but not at a stable release yet) cluster implementation. (http://redis.io/topics/cluster-spec) I'm not sure how production ready it is - has anyone here tried it?

Re: Ask HN: Going from Redis to MySQL. Good idea?

#6

Post by Oculus » Sun, Oct 19, 2014, 5:47 PM UTC

bendmorris wrote: post #5

Why did you choose Redis in the first place? Redis does have its own (official but not at a stable release yet) cluster implementation. ( http://redis.io/topics/cluster-spec ) I'm not sure how production ready it is - has anyone here tried it?

Twitter probably runs the largest Redis cluster in the world[1]. It's certainly doable, but depends how much engineering resources you're willing to throw at the problem.

1 - http://youtu.be/rP9EKvWt0zo

Edit: I should point out that this was before Redis Clustering was built into core. I'm trying to say the problem is solvable, but difficult.

Re: Ask HN: Going from Redis to MySQL. Good idea?

#7

Post by diminish » Sun, Oct 19, 2014, 5:56 PM UTC

mysql, mariadb or postgresql would be ok.

>> menu_items - string(comma seprated list of all menu items that customer ordered along with their price & Qty)

you may prefer to rework the menu_items as an independent table depending on your uses cases and if you don't have strong reason against.

Re: Ask HN: Going from Redis to MySQL. Good idea?

#8

Post by thinkingkong » Sun, Oct 19, 2014, 5:56 PM UTC

I'll try and answer your 3 questions.

1. As with most things it depends, but I'll make the mistake of assuming you don't need all of this data in memory and you won't be iterating over the entire set of data on a regular (2. Again "It depends". Postgresql is the most popular alternative to MySQL and I'd say at this point is more in vogue with the developer community. It's supported on AWS RDS and Heroku has a great postgres.app for OSX. Your data is so simple that I'd encourage you to start there. In the future when you have specific needs then you can research more appropriate database technologies.

3. From what you've shown us then you should most likely move to a relational database. The size should shrink dramatically, too.

Re: Ask HN: Going from Redis to MySQL. Good idea?

#9

Post by sirdavidhuang » Sun, Oct 19, 2014, 5:58 PM UTC

I think Apache Cassandra DB is what you are looking for.

Apache: The Apache Cassandra database is the right choice when you need scalability and high availability without compromising performance. Linear scalability and proven fault-tolerance on commodity hardware or cloud infrastructure make it the perfect platform for mission-critical data. Cassandra's support for replicating across multiple datacenters is best-in-class, providing lower latency for your users and the peace of mind of knowing that you can survive regional outages.

http://cassandra.apache.org/

http://planetcassandra.org/getting-started-with-apache-cassa...

Re: Ask HN: Going from Redis to MySQL. Good idea?

#10

Post by revelation » Sun, Oct 19, 2014, 6:11 PM UTC

I don't have an opinion re redis to MySQL, but it seems like right now the cheapest options is to simply buy a server with 256+ GB of RAM. These are pretty common by now.