17

We have created a Facebook application and it got a lot of virality. The problem is that our database started getting REALLY FULL (some tables have more than 25 million rows now). It got to the point that the app just stopped working because there was a queue of thousands and thousands of writes to be made.

I need to implement a solution for scaling this app QUICKLY but I'm not sure if I should pursue Sharding or Clustering since I'm not sure what are the pro's and con's of each of them and I was thinking of doing a Partition / Replication approach but I think that doesn't help if the load is on the writes?

Martin Thoma
  • 124,992
  • 159
  • 614
  • 958
albertosh
  • 2,416
  • 7
  • 25
  • 32
  • 2
    Start with the basics - do you have indexes in place? Are they the right indexes for the application? – Oded Jan 04 '11 at 14:38
  • Yes, i do have a lot of indexes in place, and they definitely are the right ones, we are not seeing slow queries but just an incredibly large amount of write instructions. – albertosh Jan 04 '11 at 16:46

4 Answers4

2

Clustering/Sharding/Partitioning comes when single node has reached to the point where its hardware cannot bear the load. But your hardware has still room to expand. This is the first lesson I learnt when I started being hit by such issues

Abdel Hegazi
  • 368
  • 3
  • 14
2

25 million rows is a completely reasonable size for a well-constructed relational database. Something you should bear in mind, however, is that the more indexes you have (and the more comprehensive they are), the slower your writes will be. Indexes are designed to improve query performance at the expense of write speed. Be sure that you're not over-indexed.

What sort of hardware is powering this database? Do you have enough RAM? It's far easier to change these attributes than it is to try to implement complex RDBMS load balancing techniques, especially if you're under a time crunch.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
  • Hey Adam, We are using an c1.xlarge instance in Amazon, it has something around 20 ECUS and 8GB of RAM, keep in mind that those 25 million rows where inserted in a matter of 4 days! :S That's why i think that even if we optimize a lot our database schema (which we are working on right now) we will need to have a solution in place ASAP for handling so many writes. – albertosh Jan 04 '11 at 19:33
1

Well, to understand that, you need to understand how MySQL handles clustering. There are 2 main ways to do it. You can either do Master-Master replication, or NDB (Network Database) clustering.

Master-Master replication won't help with write loads, since both masters need to replay every single write issued (so you're not gaining anything).

NDB clustering will work very well for you if and only if you are doing mostly primary key lookups (since only with PK lookups can NDB operate more efficient than a regular master-master setup). All data is automatically partitioned among many servers. Like I said, I would only consider this if the vast majority of your queries are nothing more than PK lookups.

So that leaves two more options. Sharding and moving away from MySQL.

Sharding is a good option for handling a situation like this. However, to take full advantage of sharding, the application needs to be fully aware of it. So you would need to go back and rewrite all the database accessing code to pick the right server to talk to for each query. And depending on how your system is currently setup, it may not be possible to effectively shard...

But another option which I think may suit your needs best is switching away from MySQL. Since you're going to need to rewrite your DB access code anyway, it shouldn't be too hard to switch to a NoSQL database (again, depending on your current setup). There are tons of NoSQL servers out there, but I like MongoDB. It should be able to withstand your write load without worry. Just beware that you really need a 64 bit server to use it properly (with your data volume).

ircmaxell
  • 163,128
  • 34
  • 264
  • 314
  • 17
    I'm sorry...the "best" option is moving away from a relational database entirely? How can you make a recommendation like that when you don't know anything about what it is that he's storing? – Adam Robinson Jan 04 '11 at 16:11
  • No need to rewrite all the database accessing code for sharing. Just create a distribution table that tells you which user is on which server, then send them over to that server where all the processing is done. In other words, it's only on authentication where you have to make any code changes. – Vincent Feb 25 '19 at 23:13
-2

Replication is for data backup not for performance so its out of question.

Well, 8GB RAM is still not that much you can have many hundred GB RAM with quite big hard disk space and MySQL would still work for you.

Clustering/Sharding/Partitioning comes when single node has reached to the point where its hardware cannot bear the load. But your hardware has still room to expand.

If you don't want to upgrade your hardware then you need to give more information about database design and if there are lot of joins or not so that above named options can be considered deeply.

Gary Lindahl
  • 5,341
  • 2
  • 19
  • 18