Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual Machine. I want the application to be easily scalable, I want to be able to add new nodes and make the system perform better as easily as possible when needed.

I have been reading about MySQL Cluster and MySQL Replication, but I haven't been able to decide on which one to use. You can imagine that (a popular) url shortener will be both write and read intensive. What is the structure that you would use in such a case? Would you go for cluster or replication?

Then based on the choice of cluster or replication, what is the infrastructure/configuration that I am supposed to have in order to be able to expand from a single innoDB engined Database to a cluster or replication structure? I want to start correctly, I don't want to be stucked in a situation where I can not expand/improve the database system when I need to.

Thanks A lot

sr.

share|improve this question
    
What related data do you store with the urls being shortened? –  Luc Franken May 20 '13 at 9:26
    
For starters I have just one table and this table has 1)a column holds link(url) text values, 2)a column holds unique ids which is auto incremented by 1, 3)a column holds MD5 of the link text values. There is a good chance that in the future I will add other tables for some analytics data storage. –  sr. May 20 '13 at 10:39
2  
There is also Galera cluster to consider. –  ypercube May 20 '13 at 11:03
    
In this case I would suggest looking at non relational databases. Because it is so simple in database terms look at the NoSQL databases. That looks like a better fit. If you are not a database expert but need the scaling I would suggest taking a look at for example the Amazon offerings: aws.amazon.com/nosql which scale out of the box so you can focus on the application. –  Luc Franken May 21 '13 at 15:48
add comment

2 Answers

MongoDB - it has automatic sharding and you will be able to add nodes one after another. Otherwise, to scale writes you will need to shard mysql one way or another and cluster will not help you.

share|improve this answer
    
Seems like MongoDB has some problems with auto-incremented IDs which is something I can not sacrifice. "Warning Generally in MongoDB, you would not use an auto-increment pattern for the _id field, or any field, because it does not scale for databases with larger numbers of documents. Typically the default value ObjectId is more ideal for the _id." docs.mongodb.org/manual/tutorial/… –  sr. May 23 '13 at 6:43
    
PLUS: If in the future I want to add more tables, and construct relations between them for statistics generation mongoDB would not be sufficient right? –  sr. May 23 '13 at 6:50
add comment

MySQL Cluster possibly a good fit for you because of your key:value lookup traffic pattern. NDB is traditionally poor with joins or compex queries. I would say however that it is not the same as MySQL and has caveats that you need to understand before jumping into bed with it. It's shared nothing architecture means that you should operate it at scale with decent (and inclusive redundancy) network infrastructure. It is perhaps worth a proof of concept project to fit it out and test it. The guys at severalnines have configuration tools to get you up and running.

Mongo isn't a bad fit either and as you state you cannot give up A_I, why not? What about values like (1902jdd,"/page/id") needs and auto inc? Mongo documents are schemaless and you add superfluous data to a document where you see fit and query in a key:value style too. Statistics moving forward would put you into the map reduce world which could be fun.

Keeping with traditional RDBMS and requiring performance you should look at memcached and the mysql memcached api for rapid key:value access to the data which would retain the relational aspect for aggregation queries (select [count|max|avg]... group by order by blah, blah...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.