mysql v mongodb - best solution for a complex user focussed site?

I've spent a few days researc开发者_开发问答hing the pros and cons of mysql against nosql solutions (specifically mongodb) for my project.

The project needs to be able to eventually scale to handle tens of thousands of simultaneous users - millions of users in total. The site is heavily user focussed and will interact with the database as much if not more than a site like facebook - it is very relational, all functionality is dependant on the relation to the user and their relationship with other users. It's also data heavy - lots of files, images, audio, messaging, personal news feed etc.

I like the look on mongodb a lot, I like the way it works, and I like how it scales - but can't get my head around how this would work for a site such as I describe. Would all interactions for a specific user have to be stored in a single document?

I am however very comfortable using mysql and like the relational aspect of it. I am just worried without a lot of work there will be scalability issues with this project - although perhaps with memcached and sharding this won't be an issue?

I'd like to know from those with experience with the two databases on large projects, out of mysql and mongodb which is the right tool for this particular job?

If the data is highly relational, use a relational database. If it's not, don't. NoSQL is great, don't get me wrong, but it's not suited to all tasks. It may be suited to your task, but the only way to find out is for you to build some tests for your specific usecase. Add a bunch of dummy data (millions if not hundreds of millions of rows). And then load test it.

As far as scaling, that's more of a component of how you build your application than the backend you choose. Do you have a solid schema? Do you have a strong cache layer with write-through caching? Do you access the backend as efficiently as possible (queries and such)? Can you shard based upon your application?

Those are the kind of questions which are appropriate here. Not "which will scale for me better". And not "which is the right tool". Both can do the job fine. Which is best is up to you...

Obviously, there's no silver bullet here. However, I would like to challenge this one assumption you've made:

... it is very relational, all functionality is dependant on the relation to the user and their relationship with other users...

OK, I'd like you to picture having 100M users in a relational database and start building this model. Let's try something simple, grab the names of a user's friends.

How do you get a user's friends? Well you go to the users_friends table. If each user has even just 10 friends, that table contains a billion rows. If users have a more reasonable 100 friends, you now have 10B rows.

So now you have a user and a list of their friends IDs. How do we get their friend's names? Well you go through the list of 100 IDs and pull down each of the friends. Perfect.

So now, if you want to show one user the names of all of their friends, all you have to do is join the 100M record table to the 10B record table. This is not a simple task. Scaling joins becomes exponentially harder and more expensive as the dataset grows.

So, to make this easier, you're probably going to run a for loop and manually collect the records for each friend. You have to do this because the friends are scattered across multiple servers, so each "lookup" has to be done individually.

Already you've broken your "relational model".

What about the friends list? Is keeping a table of 10B records really practical? Why not just keep a list of friend IDs with each user? Why do an extra query.

If you notice the pattern here, we've basically broken down the "very relational" model into something that's effectively key-value lookups. Of course, the key-value model will scale much better. And so, MongoDB seems like a good fit here.

Don't get me wrong, there are lots of good uses for relational databases. But when you're talking about handling millions of individual key-value style requests, you probably want to look at a NoSQL database.

There is no law that you have to build an application with exactly one database. It is often common practice having dedicated backends for particular tasks. E.g. in the context of a Facebook-like application it may make sense to work with a graph-database for storing relations between users - every database has its pros and cons and only would fools implement large backends with only a RDBMS or only a NoSQL db just because they don't know better.