MongoDB as a cache for frequent joins and queries from MySQL

views:

answers:

MongoDB as a cache for frequent joins and queries from MySQL

Im thinking about doing the following and need suggestions if it makes sense to approach it this way. Basically since I am able to do queries in MongoDB and MongoDb is wicked fast at these since the hotspots of the data are cached in memory. I was thinking of storing data I would normally do a join from in mysql in mongoDB. While I am using memcached to store simple query results (for example a movie description page), for bigger stuff that requires more realtime/ondemand queries I was thinking about storing this in MongoDB. For example the view count for movies and who saw it, and doing analysis on it.

Hopefully I explained it clearly.

more info:

We dont want to keep writing to our mysql server on every rating like etc, MongoDB seemed like a good option to store the ratings,views of movies etc and then later on be able to do processing on that data. Whereas with Memcached data is not persisted and were unable to do queries

Thanks, Faisal

+1 A:

Memory caching alone is not a good reason to go with MongoDB. Any properly configured RDBMS will cache frequently used data in memory.

What aspect of MySQL is currently limiting your performance? Do you have enough RAM in your server? Are your disks fast enough? Do you have a low latency cache device like an SSD configured appropriately?

Paul McMillan 2010-10-24 21:05:25

We havent actually got to the implementation. Were in the phase where were looking at different options. I was thinking about this because MongoDB is being heavily used at Foursquare to do checkins, and there processing that data algorithmically. Because we dont want to keep writing to our mysql server, MongoDB seemed like a good option to store the ratings,views etc and then later on be able to do processing on that data. Whereas with Memcached data is not persisted and were unable to do queries.

Faisal Abid 2010-10-24 21:20:03

MongoDB is good for doing key/value store type operations. Does your task primarily involve those? A hybrid database setup isn't necessarily bad, but you should be VERY sure that MongoDB brings a significant benefit before you implement anything using it. You REALLY don't want to be trying to duplicate your data between two different DBs. Don't try and store data in both places. You can't easily do a query that crosses both MySQL and MongoDB, and you need to be very sure you won't want to do that before you split your data.

Paul McMillan 2010-10-24 21:33:06

well what we have in mind is in mongoDB (this is for ratings) store the userID, videoID, VideoName, rating. Then when the user gets this data (when hes looking at all his ratings) and acts upon it like looking at the video description for that movie he rated, we can make a call to the videoID in Memcached to get the description, and if that data is not there then go to mysql to fetch it and store it in Memcached then return it to the user.

Faisal Abid 2010-10-24 21:54:18

So you want to use MongoDB to store data for a single row update or retrieval operation? MongoDB doesn't magically make things faster. That operation is the sort of thing ANY database system HAS to be REALLY FAST about. The usage of memcached there is fine, but MongoDB isn't really going to be any better than a properly configured MySQL for that sort of thing. Don't make your life more complicated than it needs to be. If you already have one in the project, stick to a traditional RDBMS for all your data until you run into scaling problems.

Paul McMillan 2010-10-24 22:03:33

I'm not saying that MongoDB is a BAD way to store this data, but you haven't given me any information that indicates you're going to run into scaling problems with MySQL.

Paul McMillan 2010-10-24 22:08:42

There's nothing wrong with using both solutions in your application. As a matter of fact, I'm using mysql to store user sessions as suppose to cookies. In addition, I have another project that utilizes mysql but for certain parts of my application, I will be using MongoDB. Why? It's wicked fast and I hate writing join queries. It's so much easier to pop data in/out of mongo as suppose to having to do join queries in mysql.

i.e.

When saving tags for a particular user, it's so gosh darn eary to save/modify/delete the tag that's stored in mongodb. With MySQL, I would've had to write a query that JOINs multiple tables. For data such as user account, password, city, state - I saved everything into MySQL.

luckytaxi 2010-10-24 23:04:06

ansaurus

tags:

views:

answers:

MongoDB as a cache for frequent joins and queries from MySQL

related questions